Prepared statements in ASP

I am trying to create a prepared statement in ASP, but am having
problems with creating the parameter object. I do the following

Set fnParam = peopleUpdate.CreateParameter("@firstname", adVarChar,
adParamInput, 50, peopleSourceRS("firstname"))

But this gives the following error on the browser:

Error Type:
ADODB.Command (0x800A0BB9)
Arguments are of the wrong type, are out of acceptable range, or are in
conflict with one another.

The server doesn't seem to like adVarChar and adParamInput. I did try
to create the parameter without arguments and then assign the
properties, but it gives the same error for .Type and .Direction. What
am I doing incorrectly here?

thanks

re: Prepared statements in ASP

No one wrote:[color=blue]
> I am trying to create a prepared statement in ASP, but am having
> problems with creating the parameter object. I do the following
>
> Set fnParam = peopleUpdate.CreateParameter("@firstname", adVarChar,
> adParamInput, 50, peopleSourceRS("firstname"))
>
> But this gives the following error on the browser:
>
> Error Type:
> ADODB.Command (0x800A0BB9)
> Arguments are of the wrong type, are out of acceptable range, or are
> in conflict with one another.
>
> The server doesn't seem to like adVarChar and adParamInput. I did try
> to create the parameter without arguments and then assign the
> properties, but it gives the same error for .Type and .Direction. What am
> I doing incorrectly here?
>
> thanks[/color]
http://www.aspfaq.com/show.asp?id=2112

You may be interested in my command object code generator available here
(it's free):

http://www.thrasherwebdesign.com/ind...asp&c=&a=clear

If your procedure has no output parameters, and you aren't interested in
reading the Return parameter, then you don't need an explicit command
object. See http://tinyurl.com/jyy0

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

re: Prepared statements in ASP

Bob Barrows [MVP] wrote:
[color=blue]
> No one wrote:
>[color=green]
>>I am trying to create a prepared statement in ASP, but am having
>>problems with creating the parameter object. I do the following
>>
>>Set fnParam = peopleUpdate.CreateParameter("@firstname", adVarChar,
>>adParamInput, 50, peopleSourceRS("firstname"))
>>
>>But this gives the following error on the browser:
>>
>>Error Type:
>>ADODB.Command (0x800A0BB9)
>>Arguments are of the wrong type, are out of acceptable range, or are
>>in conflict with one another.
>>
>>The server doesn't seem to like adVarChar and adParamInput. I did try
>>to create the parameter without arguments and then assign the
>>properties, but it gives the same error for .Type and .Direction. What am
>>I doing incorrectly here?
>>
>>thanks[/color]
>
> http://www.aspfaq.com/show.asp?id=2112[/color]

Ugh....how do I know which MDAC I have?
[color=blue]
>
> You may be interested in my command object code generator available here
> (it's free):
>
> http://www.thrasherwebdesign.com/ind...asp&c=&a=clear[/color]

All I see here is how to call a stored procedure. I'm not calling a
stored proc, I want to make a prepared statement. Something like this:

UPDATE FOO_TABLE SET foName = @name, foPhone = @phone WHERE foId = @id

And then fill the parameters in at run time.

If I missed something on the page, please let me know.

re: Prepared statements in ASP

No one wrote:[color=blue]
> Bob Barrows [MVP] wrote:
>[color=green]
>> No one wrote:
>>[color=darkred]
>>> I am trying to create a prepared statement in ASP, but am having
>>> problems with creating the parameter object. I do the following
>>>
>>> Set fnParam = peopleUpdate.CreateParameter("@firstname", adVarChar,
>>> adParamInput, 50, peopleSourceRS("firstname"))
>>>
>>> But this gives the following error on the browser:
>>>
>>> Error Type:
>>> ADODB.Command (0x800A0BB9)
>>> Arguments are of the wrong type, are out of acceptable range, or are
>>> in conflict with one another.
>>>
>>> The server doesn't seem to like adVarChar and adParamInput. I did
>>> try to create the parameter without arguments and then assign the
>>> properties, but it gives the same error for .Type and .Direction.
>>> What am I doing incorrectly here?
>>>
>>> thanks[/color]
>>
>> http://www.aspfaq.com/show.asp?id=2112[/color]
>
> Ugh....how do I know which MDAC I have?[/color]

I suggest creating a simple asp page with the following two lines of code:

<%
set cn=createobject("adodb.connection")
response.write cn.Version
%>

Alternatively, you can #include the adovbs.inc file, but as Aaron's article
states, that file has many more constants than you will likely need.
[color=blue]
>[color=green]
>>
>> You may be interested in my command object code generator available
>> here (it's free):
>>
>> http://www.thrasherwebdesign.com/ind...asp&c=&a=clear[/color]
>
> All I see here is how to call a stored procedure. I'm not calling a
> stored proc, I want to make a prepared statement. Something like
> this:
> UPDATE FOO_TABLE SET foName = @name, foPhone = @phone WHERE foId = @id
>
> And then fill the parameters in at run time.
>
> If I missed something on the page, please let me know.[/color]

I thought you were talking about a stored procedure. I think this is what
you are asking about:
http://groups-beta.google.com/group/...e36562fee7804e

You don't need to build the parameters collection.

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

re: Prepared statements in ASP

Bob Barrows [MVP] wrote:
[color=blue]
> No one wrote:
>[color=green]
>>Bob Barrows [MVP] wrote:
>>
>>[color=darkred]
>>>No one wrote:
>>>
>>>
>>>>I am trying to create a prepared statement in ASP, but am having
>>>>problems with creating the parameter object. I do the following
>>>>
>>>>Set fnParam = peopleUpdate.CreateParameter("@firstname", adVarChar,
>>>>adParamInput, 50, peopleSourceRS("firstname"))
>>>>
>>>>But this gives the following error on the browser:
>>>>
>>>>Error Type:
>>>>ADODB.Command (0x800A0BB9)
>>>>Arguments are of the wrong type, are out of acceptable range, or are
>>>>in conflict with one another.
>>>>
>>>>The server doesn't seem to like adVarChar and adParamInput. I did
>>>>try to create the parameter without arguments and then assign the
>>>>properties, but it gives the same error for .Type and .Direction.
>>>>What am I doing incorrectly here?
>>>>
>>>>thanks
>>>
>>>http://www.aspfaq.com/show.asp?id=2112[/color]
>>
>>Ugh....how do I know which MDAC I have?[/color]
>
>
> I suggest creating a simple asp page with the following two lines of code:
>
> <%
> set cn=createobject("adodb.connection")
> response.write cn.Version
> %>
>
> Alternatively, you can #include the adovbs.inc file, but as Aaron's article
> states, that file has many more constants than you will likely need.
>
>[color=green][color=darkred]
>>>You may be interested in my command object code generator available
>>>here (it's free):
>>>
>>>http://www.thrasherwebdesign.com/ind...asp&c=&a=clear[/color]
>>
>>All I see here is how to call a stored procedure. I'm not calling a
>>stored proc, I want to make a prepared statement. Something like
>>this:
>>UPDATE FOO_TABLE SET foName = @name, foPhone = @phone WHERE foId = @id
>>
>>And then fill the parameters in at run time.
>>
>>If I missed something on the page, please let me know.[/color]
>
>
> I thought you were talking about a stored procedure. I think this is what
> you are asking about:
> http://groups-beta.google.com/group/...e36562fee7804e
>
> You don't need to build the parameters collection.
>
> Bob Barrows[/color]

I guess ODBC for MS SQL server doesn't support named parameters under
ADO. Ok.

re: Prepared statements in ASP

Bob Barrows [MVP] wrote:
[color=blue]
> No one wrote:
>[color=green]
>>Bob Barrows [MVP] wrote:
>>
>>[color=darkred]
>>>No one wrote:
>>>
>>>
>>>>I am trying to create a prepared statement in ASP, but am having
>>>>problems with creating the parameter object. I do the following
>>>>
>>>>Set fnParam = peopleUpdate.CreateParameter("@firstname", adVarChar,
>>>>adParamInput, 50, peopleSourceRS("firstname"))
>>>>
>>>>But this gives the following error on the browser:
>>>>
>>>>Error Type:
>>>>ADODB.Command (0x800A0BB9)
>>>>Arguments are of the wrong type, are out of acceptable range, or are
>>>>in conflict with one another.
>>>>
>>>>The server doesn't seem to like adVarChar and adParamInput. I did
>>>>try to create the parameter without arguments and then assign the
>>>>properties, but it gives the same error for .Type and .Direction.
>>>>What am I doing incorrectly here?
>>>>
>>>>thanks
>>>
>>>http://www.aspfaq.com/show.asp?id=2112[/color]
>>
>>Ugh....how do I know which MDAC I have?[/color]
>
>
> I suggest creating a simple asp page with the following two lines of code:
>
> <%
> set cn=createobject("adodb.connection")
> response.write cn.Version
> %>
>
> Alternatively, you can #include the adovbs.inc file, but as Aaron's article
> states, that file has many more constants than you will likely need.
>
>[color=green][color=darkred]
>>>You may be interested in my command object code generator available
>>>here (it's free):
>>>
>>>http://www.thrasherwebdesign.com/ind...asp&c=&a=clear[/color]
>>
>>All I see here is how to call a stored procedure. I'm not calling a
>>stored proc, I want to make a prepared statement. Something like
>>this:
>>UPDATE FOO_TABLE SET foName = @name, foPhone = @phone WHERE foId = @id
>>
>>And then fill the parameters in at run time.
>>
>>If I missed something on the page, please let me know.[/color]
>
>
> I thought you were talking about a stored procedure. I think this is what
> you are asking about:
> http://groups-beta.google.com/group/...e36562fee7804e
>
> You don't need to build the parameters collection.
>
> Bob Barrows[/color]

I changed the SQL to use this instead of the named params:

peopleUpdateSQL = "UPDATE People SET FirstName = ?, surname = ? WHERE id
= ?"


I create an array of my values like so:

updatedFirstName = peopleSourceRS("firstname")
updatedSurName = peopleSourceRS("surname")
whereId = peopleSourceRS("id")

Dim params
params = array(updatedFirstName, updatedSurName, whereId)

and I execute like so:

peopleUpdate.Execute , array(updatedFirstName, updatedSurName, whereId),
adExecuteNoRecords

I get this error on the browser:

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E57)
[Microsoft][ODBC SQL Server Driver]String data, right truncation
/RepLocator/ProcessPeopleFile.asp, line 113

------------------------------------------
I'm not sure what is getting truncated.

re: Prepared statements in ASP

Bob Barrows [MVP] wrote:
[color=blue]
> No one wrote:
>[color=green]
>>Bob Barrows [MVP] wrote:
>>
>>[color=darkred]
>>>No one wrote:
>>>
>>>
>>>>I am trying to create a prepared statement in ASP, but am having
>>>>problems with creating the parameter object. I do the following
>>>>
>>>>Set fnParam = peopleUpdate.CreateParameter("@firstname", adVarChar,
>>>>adParamInput, 50, peopleSourceRS("firstname"))
>>>>
>>>>But this gives the following error on the browser:
>>>>
>>>>Error Type:
>>>>ADODB.Command (0x800A0BB9)
>>>>Arguments are of the wrong type, are out of acceptable range, or are
>>>>in conflict with one another.
>>>>
>>>>The server doesn't seem to like adVarChar and adParamInput. I did
>>>>try to create the parameter without arguments and then assign the
>>>>properties, but it gives the same error for .Type and .Direction.
>>>>What am I doing incorrectly here?
>>>>
>>>>thanks
>>>
>>>http://www.aspfaq.com/show.asp?id=2112[/color]
>>
>>Ugh....how do I know which MDAC I have?[/color]
>
>
> I suggest creating a simple asp page with the following two lines of code:
>
> <%
> set cn=createobject("adodb.connection")
> response.write cn.Version
> %>
>
> Alternatively, you can #include the adovbs.inc file, but as Aaron's article
> states, that file has many more constants than you will likely need.
>
>[color=green][color=darkred]
>>>You may be interested in my command object code generator available
>>>here (it's free):
>>>
>>>http://www.thrasherwebdesign.com/ind...asp&c=&a=clear[/color]
>>
>>All I see here is how to call a stored procedure. I'm not calling a
>>stored proc, I want to make a prepared statement. Something like
>>this:
>>UPDATE FOO_TABLE SET foName = @name, foPhone = @phone WHERE foId = @id
>>
>>And then fill the parameters in at run time.
>>
>>If I missed something on the page, please let me know.[/color]
>
>
> I thought you were talking about a stored procedure. I think this is what
> you are asking about:
> http://groups-beta.google.com/group/...e36562fee7804e
>
> You don't need to build the parameters collection.
>
> Bob Barrows[/color]


I looked at this code, but it seems, for update at least, that the error
code trap is backwards. WHen the update happened fine, err contained a
0. When the update didn't occure, the error code was -2147217833.

re: Prepared statements in ASP

No one wrote:[color=blue]
> updatedFirstName = peopleSourceRS("firstname")
> updatedSurName = peopleSourceRS("surname")
> whereId = peopleSourceRS("id")
>
> Dim params
> params = array(updatedFirstName, updatedSurName, whereId)
>
> and I execute like so:
>
> peopleUpdate.Execute , array(updatedFirstName, updatedSurName,
> whereId), adExecuteNoRecords[/color]

You already had an array. Why create a new one?

My normal practice is to be explicit about my datatypes. Is whereid numeric?
If so, explicitly convert it to the appropriate numeric subtype. Assuming
int:

params = array(updatedFirstName, updatedSurName, _
clng(whereId))

peopleUpdate.Execute , params, _
adExecuteNoRecords + adCmdText


[color=blue]
>
> I get this error on the browser:
>
> Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E57)
> [Microsoft][ODBC SQL Server Driver]String data, right truncation
> /RepLocator/ProcessPeopleFile.asp, line 113
>[/color]
This is usually a warning. The update should have occurred. You should
probably use Left() to make sure the string you send to this statement is
not too long for the field into which it is going to be put.

You should be using the sqloledb OLE DB provider instead of the ODBC driver:
http://www.aspfaq.com/show.asp?id=2126


--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

re: Prepared statements in ASP

Maybe could you have a read on this, it's in french but the sample code
might help you:
http://dotnetjunkies.com/WebLog/afon.../29/27120.aspx