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?

I know this might be more of a VFP question, but it does interrelate to ASP in that I'm using that as the delivery mechanism. Any suggestions???

 

[Kendel]

Why do you want to make it complicate? You already have set the 2 variables dduecollec = Request("dduecollec") and corderno = Request("corderno").
Just put these into your SQLStmt:

SQLStmt = "update customerordersrepairsheader set dduecollec = '" & dduecollec & "', dlastedit = now(), cloperator = 'ARMS' where corderno = '" & corderno & "'"
Cmd.Execute(SQLStmt)

 

[gregarican]

That is the point of my question. I want to be able to sanitize the ASP form entry so that malicious/accidental values don't hit my database. That's why I wanted to employ SQL command parameters. That's why I want to make it complicated

 

[ggriffit]

have you tried printing your SQL Statement to a log file and then running that in VFP it may show you where the error is ?

Greg Griffiths
Livelink Certified Developer & ECM Global Star Champion 2005 & 2006

http://www.greggriffiths.org/livelink/

 

[gregarican]

Good idea. That seems to be where I am getting stuck. When I print to screen using Response.Write or try to log it I am not getting the parameters to display. They are still just the ? placeholders.

I mean, my code works now, and is internally exposed. It's not on Internet facing servers. My company isn't that large or technically adept so the odds of so anyone throwing in an SQL injection is rather slim. It's more of a housekeeping chore I'd like to do so that my code is more sanitized.

I'll keep plugging away

 

[tsuji]

[0] "<!-- -->" is not comment. Comment is plain old apostrophe.

[1] If you set parameter dduecollec of adDBDate type, make sure you have it in the format yyyymmdd.
[tt]
dduecollec = Request("dduecollec")
dduecollec=cdate(dduecollec)
dduecollec=year(dduecollec) & right("0" & month(dduecollec),2) & right("0" & day(dduecollec),2)
[/tt]
And then proceed with the createParameter().

 

[gregarican]

I will take your tip regarding the adDBDate type formatting. My using / or - date delimiters likely threw off the script.

Regarding the comment line, I'm not using VB. I am using VBScript embedded within an ASP form. So that indeed is the correct comment pre- and post-fix syntax.

 

[tsuji]

>Regarding the comment line, I'm not using VB. I am using VBScript embedded within an ASP form. So that indeed is the correct comment pre- and post-fix syntax.
Not really.

 

[gregarican]

The adDBDate parameter indeed looks for yyyymmdd formatting, but unfortunately the CTOD() VFP method looks for mm/dd/yyyy formatting. So the script still failed. Here's what I did below. I just set the ADODB.Command parameter to look for adVarChar input and then used that for getting the job done...

<%
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 = ctod(?), dlastedit = datetime(), cloperator = 'ARMS' where corderno = ?; "

Cmd.CommandText = SQLStmt
Cmd.CommandType = adCmdText

Set Param1 = Cmd.CreateParameter("@dduecollec", adVarChar, adParamInput, 10, dduecollec)
Set Param2 = Cmd.CreateParameter("@corderno", adVarChar, adParamInput, 200, corderno)
Cmd.Parameters.Append Param1
Cmd.Parameters.Append Param2
Cmd.Execute
%>

 

[gregarican]

Yeah, I had indeed forgotten to do a CDate() against the incoming values being Request()'ed. Now this works as expected. Thanks for your help!

<%
dduecollec = CDate(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 = ?, 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
%>

And you're right about the ' VBScript comment syntax that can nested within ASP code. I'm more of a C# and Ruby programmer so my VB stuff is kind of rusty :-/