Update querys Again

[dawilis]

the first SQL query here fails no matter what I do to it, I have looked at it over and over sideways, re writen it but still it refuses to work, it simply says
Microsoft][ODBC Microsoft Access Driver] Too few parameters. Expected 1. now thats rubbish its exactly right I even made a table specificaly with this in field in it.
the second one works a treat

<%@LANGUAGE="VBSCRIPT"%>
<%Response.Buffer=TRUE%>
<%

Faddress = request.form ("Address")
Ftown = request.form ("Town")
Fpostcode = request.form ("Postcode")
FHphone = request.form ("Hphone")
FWphone = request.form ("Wphone")
FMobile = request.form ("Mobile")
Femail = request.form ("Email")
FcontactDate = request.form ("ContactDate")
FstaffID = request.form ("StaffID")
FAction = request.form ("Action")
Fdesc = request.form ("Desc")
FAlert = request.form ("Alert")

Set MyConn=Server.CreateObject("ADODB.Connection")
MyConn.Open "Driver={Microsoft Access Driver (*.mdb)};DBQ=" & Server.MapPath("../db/cts.mdb") & ";"

'update the record with the modified values.
SQL="Update tblcontact Set Action = '"&FAction&"' Where ID1 =" & request.querystring("QSmod") & ""
'response.write sql
'response.end
MyConn.Execute(SQL)

SQL="Update Master_Roll Set [Postal Address] = '"&Faddress&"', Town = '"&Ftown&"', Postcode = '"&Fpostcode&"',[Home Ph]= '"&FHphone&"', [Work Ph]= '"& FWphone&"', Mobile= '"&FMobile&"', Email= '"&FEmail &"'Where ID1 =" & request.querystring("QSmod") & ""
'response.write sql
'response.end

MyConn.Execute(SQL)

'send the user back.
Response.Redirect "showCon.asp"

'clean up.
MyConn.Close
Set MyConn = Nothing
%>

[oracleguy]

Which statement is failing? The first or second one?

I also notice your missing a space in the second query:


Email= '"&FEmail &"'Where ID1 =" & r
-------------------^

[dawilis]

the first query is failing the second works great

[glenngv]

how does the sql statement look like when you response.write it?

'update the record with the modified values.
SQL="Update tblcontact Set Action = '"&FAction&"' Where ID1 =" & request.querystring("QSmod") & ""
response.write sql
response.end

[dawilis]

It looks exactly as its supposed to look with the updated info from the form and the id number passed through the URL

[glenngv]

can you post the output of:

response.write sql

?

[dawilis]

the output from the sql statment

Update tblcontact Set Action = 'stuff' Where ID1 =210

[raf]

Sometimes whe don't see the problem cause the thing that is causing the problem isn't visible.
Maybe there is something we don't see here, like the querystring or the link you click to request this page.

Anyway, your statement ends with & ""
SQL="Update tblcontact Set Action = '"&FAction&"' Where ID1 =" & request.querystring("QSmod") & ""
Why ? All this does is add a space to the sql statement. ?

Also, the connectionstring looks weird to me. But you say your other query works --> sure about that ?
Are you sure there is always a value in the querystring for that variable? Cause i see all the other values are pulled from the forms-collection.

Maybe try it like this (you never know)

dim SQL, numupdated
SQL="Update tblcontact Set Action='" & FAction & "' Where ID1=" & CStr(request.querystring("QSmod"))

MyConn.Execute SQL,numupdated


I removed the spaces and did an explicit stringconversion (don't know if querystring values are always stringls like in the forms-collection). Shouldn't matter but ...
Sure about that connectionstring ?

[dawilis]

The page works except for the first query string, the connection string is ok to, its a curly one isnt it, if i make another tabel with id = autonumber and action the same thing happens

[raf]

Maybe

SQL="Update tblcontact Set [Action]='" & FAction & "' Where [ID1]=" & CStr(request.querystring("QSmod"))

?
You checked the querystring of the link in your satus bar (when you hoover over it), right ?

[miranda]

if the datatype in the database is of the type integar then you need to use CInt not CStr so the statement should look like this
SQL="Update tblcontact Set [Action]='" & FAction & "' Where [ID1]=" & CInt(request.querystring("QSmod")) & ""

if it is not an integar then add the single quote around the string
SQL="Update tblcontact Set [Action]='" & FAction & "' Where [ID1]='" & CStr(request.querystring("QSmod")) & "'"

[glenngv]

You don't have to use CInt or CStr. This is enough:

SQL="Update tblcontact Set [Action]='" & FAction & "' Where [ID1]=" & request.querystring("QSmod")

sample output would be:

Update tblcontact Set [Action]='stuff' Where [ID1]=210

request.querystring always returns string but you don't have to typecast it when forming the SQL statement even though the field is integer. The important thing is there is no quotes in the field value in the resulting SQL statement.

[miranda]

could be i always use a variable name and declare these variables so my sequel statements would never say request.querystring in them. because of this i have had instances where the variable needed to be converted to an integar from a variant to work in my sequel query where the datatype of the field was number or integer.

as i think about his problem though it has nothing to do with the where statement at all. since he tried a response.write on the sql statement and can see that it shows a value for the variant FAction then my question would be is the datatype of Action possibly a numerical value? if that is the case try CInt(FAction) and see if that works.

[dawilis]

I thought that as well, and made them text to be safe,
I also made a seperate table with only the Action, and ID filelds, and guess what the same problem, I have moved onto another coarse of action as this was to hard, I apreciate all your help, its a shame though that I did not learn the reason why with this error

[whammy]

This error should be easy to solve... I agree with raf and GlennGV in the probable cause.

Did you just try:

SQL="Update tblcontact Set Action='" & FAction & "' Where ID1=" & request.querystring("QSmod")

MyConn.Execute(SQL)

?!?

I notice you Dim numupdated but never give it a value. That could be the error right there...

[dawilis]

thansk whammy that sql is what I originaly had, Raf suggested the Dim numupdated in a previous post, thanks every one for your help.
regards
Daryl

[raf]

thansk whammy that sql is what I originaly had
Yeah, i thought so. And it looks more a less correct but since that doesn't work, we thought up things like the [] and conversionsetc which (as mentioned) probably aren't necessary, but you never know.
Raf suggested the Dim numupdated in a previous post
Yes. When i run an update or delete statement, i add a variable as parameter to the connection.execute. The RDBM then returns the number of deleted/updated records as the value for that variabel. So when you execute the command, this variable might be Empty. After you executed the command, this variable is never empty.
With this variable you can then check to see if or how many records were deleted or updated. Like

MyConn.Execute SQL,numupdated
dim message
select case numupdated
case 0
message="No records updated. Possible problem."
case 1
message="1 record updated."
case > 1
message= CStr(numupdated) & " records updated"
case else
message= CStr(numupdated) & "records updated. Possible problem."
end select
response.write message


Or you can use the variable to decide if you need to commit or roll back (if you use transaction) or ....