Parameterised Update Problem

[PaulHInstincticve]

I am using a parameterised update command instead of an SQL command because I have long character fields captured in a form that I need to store to Visual FoxPro variable length memo fields that can exceed 255 characters which in turn will break the SQL command. This has been working fine if I have values in these notes fields but I now hit an error if the notes field being passed is empty. The error is

Parameter object is improperly defined. Inconsistent or incomplete information was provided

Do I need a different syntax in the createparameter method or am I simply restricted so that I must pass a value even if only a single space character (I would like to avoid that if possible to avoid adding unecessary data to the database)

dim cmd
Set cmd = Server.CreateObject( "ADODB.Command" )
cmd.ActiveConnection = con
cmd.CommandType = adCmdText
cmd.CommandText = "insert into mydatabase " &_
"(cref, mnotes1, mnotes2, mnotes3 " &_
"values "('00001', ?, ?, ?) "

set Param = cmd.CreateParameter("notes1", adLongVarChar, adParamInput, len(lcnotes1) , lcnotes1)
cmd.Parameters.Append(Param)
set Param = cmd.CreateParameter("notes2", adLongVarChar, adParamInput, len(lcnotes2) , lcnotes2)
cmd.Parameters.Append(Param)
set Param = cmd.CreateParameter("notes3", adLongVarChar, adParamInput, len(lcnotes3) , lcnotes3)
cmd.Parameters.Append(Param)
cmd.Execute()

set param = nothing
set cmd = nothing

Any help with the syntax required here will be gratefully received. Thanks

Paul

 

[BigRed1212]

What kind of volume are you running through this and how many parameters are you talking?

Would it feasible to go the inelegant brute force approach and have (in the case of the above data) 4 different stored procedures, one of which looks for four parameters, one for three, one for two, etc, and then use the corresponding procedure based on the actual data you have?

That might be too ugly.

 

[PaulHInstincticve]

Thanks for that. Yes it did occur to me. Unfortunately, however, although at this point there are only 3 large variable length fields that could go over 255 characters each being written to the database, 1 of those fields contains the concatenated values from 2 other large fields and I have around 40 other fields being updated in the record at the same time. The code is therefore already looking quite ugly and if I look at the different options with just these 3 fields it gets quite messy and simple maintenance options like adding another field or two to the database start to get quite messy as I have to replicate the changes identically in each of the procedures and am bound to get unstuck at some point. I tried to work with an alternative as follows but this failed on the execute command with

Command contains unrecognized phrase/keyword

Perhaps this is a better way to deal with it if I can get the right syntax if the parameterised solution cannot handle empty values?

dim cmd
Set cmd = Server.CreateObject( "ADODB.Command" )
cmd.ActiveConnection = con
cmd.CommandType = adCmdText
cmd.CommandText = "insert into mydatabase " &_
"(cref, mnotes1, mnotes2, mnotes3 " &_
"values "('00001', iif(lcnotes1="",lcnotes1,?), iif(lcnotes2="",lcnotes2,?), iif(lcnotes3="",lcnotes3,?)) "

if lcnotes1<>"" then
set Param = cmd.CreateParameter("notes1", adLongVarChar, adParamInput, len(lcnotes1) , lcnotes1)
cmd.Parameters.Append(Param)
end if
if lcnotes2<>"" then
set Param = cmd.CreateParameter("notes2", adLongVarChar, adParamInput, len(lcnotes2) , lcnotes2)
cmd.Parameters.Append(Param)
end if
if lcnotes3<>"" then
set Param = cmd.CreateParameter("notes3", adLongVarChar, adParamInput, len(lcnotes3) , lcnotes3)
cmd.Parameters.Append(Param)
end if

cmd.Execute()

set param = nothing
set cmd = nothing

Thanks

Paul

 

[BigRed1212]

Hmmm.

So the first thing I thought of:

dim cmd 

dim thing1, thing2, thing3, text1, text2, text3, comma1, comma2, comma3

thing1 = ""
thing2 = ""
thing3 = ""
value1 = 0
value2 = 0
value3 = 0
comma1 = ""
comma2 = ""
comma3 = ""

Set cmd = Server.CreateObject( "ADODB.Command" )
cmd.ActiveConnection = con
cmd.CommandType = adCmdText
cmd.CommandText = "insert into mydatabase (cref"

if lcnotes1<>"" then
thing1 = ", lcnotes1"
comma1 = ", "
value1 = lcnotes1
   set Param = cmd.CreateParameter("notes1", adLongVarChar, adParamInput, len(lcnotes1) , lcnotes1)
   cmd.Parameters.Append(Param)
end if

if lcnotes2<>"" then
thing2 = ", lcnotes2"
comma2 = ", "
value2 = lcnotes2
   set Param = cmd.CreateParameter("notes2", adLongVarChar, adParamInput, len(lcnotes2) , lcnotes2)
   cmd.Parameters.Append(Param)
end if

if lcnotes3<>"" then
thing3 = ", lcnotes3"
comma3 = ", "
value3 = lcnotes3
   set Param = cmd.CreateParameter("notes3", adLongVarChar, adParamInput, len(lcnotes3) , lcnotes3)
   cmd.Parameters.Append(Param)
end if

cmd.CommandText = cmd.CommandText & thing1 & thing2 & thing3 & ") values ('00001'" & comma1 & value1 & comma2 & value2 & comma3 & value3

cmd.Execute()

is probably really goofy.

I better shut up and let somebody who knows better answer this one. Sorry.

 

[BigRed1212]

Oops. Forgot to close the goofy parentheses.

cmd.CommandText = cmd.CommandText & thing1 & thing2 & thing3 & ") values ('00001'" & comma1 & value1 & comma2 & value2 & comma3 & value3 & ")"

 

[PaulHInstincticve]

No not at all. I think I must have misinterpretted what you were saying earlier. I think this is the way to go here if empty paramters cannot be handled by the system. Its not as elegant as it might be otherwise but I have come to understand about ASP that it is the simplest of things that take up 90% of your time. I am hoping for a break in the workload over the coming months to get up to speed with ASP.NET and convert my application in the hope that such a simple task of inputting a field over 255 characters from a form and storing it in a data table is considered 'routine' (don't destroy my hopes if it isn't though!). Thanks for the help

Paul