ASP Parameterized Update

[gregarican]

I have a basic ASP form that is meant to update a VFP back-end DB's field values. I am using the VFP 6 ODBC driver on my IIS 6 box that my client would work against.

In order to have good coding habits I tried to perform the updates via parameterized SQL commands. But the one date field I'm trying to update on the VFP back-end isn't liking things. So the only way I could accomplish the update is to hard-code the update date value into the SQL statement. Like this:

dduecollec = Request("dduecollec")
corderno = Request("corderno")

Set Connection = Server.CreateObject("ADODB.Connection")
Set Cmd = Server.CreateObject("ADODB.Command")

Connection.Open "DSN=Touch_live"
Set Cmd.ActiveConnection = Connection

SQLStmt = "update customerordersrepairsheader set dduecollec = {d '" & dduecollec & "'}, dlastedit = datetime(), cloperator = 'ARMS' where corderno = ?; "

Cmd.CommandText = SQLStmt
Cmd.CommandType = adCmdText

<!--Set Param1 = Cmd.CreateParameter("@dduecollec", adDBDate, adParamInput, 8, dduecollec)-->
Set Param2 = Cmd.CreateParameter("@corderno", adVarChar, adParamInput, 200, corderno)
<!--Cmd.Parameters.Append Param1-->
Cmd.Parameters.Append Param2
Cmd.Execute
%>

In this case, my dduecollec variable can't work with the date parameter I commented out. I tried including it in the SQL statement by using

SQLStmt = "update customerordersrepairsheader set dduecollec = ctod([?]), dlastedit = datetime(), cloperator = 'ARMS' where corderno = ?; "

and uncommenting the Param1 lines. But the date got updated to be the bogus 1899-12-31 placeholder. Then I only received a "Function argument value, type, or count is invalid" (Error 11) in return when trying things like removing the brackets from the ctod() function.

Other failures were trying to something like {d ?} or {d '?'} or ctod(['?']). I really would like cleaner code by using SQL command parameters. I know it must be a simple tweak, unless the ODBC driver I'm using is buggy/outdated?

 

[Olaf Doschke]

When you want to use an ADODB.Parameter, you pass ? only in the SQL, like you do for the second parameter, no CTOD() or any braces etc. ADODB cares for the type conversion.

The SQL would simply be SQLStmt = "update customerordersrepairsheader set dduecollec = ?, dlastedit = datetime(), cloperator = 'ARMS' where corderno = ?; "

The question also is, what type is dduecollec? As you add it to your statement via ... & dduecollec & ... it seems to be a string. The type needs to be a date type from the ASP side too.

So try

If IsDate(Request("dduecollec")) Then
	myTrueDate = CDate(Request("dduecollec"))
Else
	Response.Write("Bad date formatting!")
End If

Additional to the different SQLStmt, both changes together should fix the problem.

Bye, Olaf.