VERY strange issue with SQL 2000 + ADO Command object

Hi,

I am running into an extremely frustrating problem and after hours of
troubleshooting have not made any progress. When I try to use the ADO
Command object to update a table, I receive the following error message:

Microsoft OLE DB Provider for ODBC Drivers error '80040e10'
No value given for one or more required parameters.


Here is the entirety of the code that is running:

Set Conn = Server.CreateObject("ADODB.Connection")
Conn.Open Application("App_ConnectionString")

Set Cmd = CreateObject("ADODB.Command")
Cmd.ActiveConnection = Conn
Cmd.CommandType = adCmdText
Cmd.Prepared = True

Cmd.CommandText = "UPDATE tblFile SET UserID = ?"

Set var1 = Cmd.CreateParameter("UserID", adInteger, adParamInput)
var1.Value = 1
Cmd.Execute

Set Cmd = Nothing
Conn.Close
Set Conn = Nothing


I use this syntax all over the site with much more complicated queries, and
have never come across this error before. Here is where it gets really
funky..... I've logged var1.Value right before executing the command, and
that is returning the correct value. However the "no value given" error
message persists! If I take the exact same SQL statement and run it from SQL
Management Studio, it runs without a hitch. Additionally if I change the ?
to the value itself, then it also runs no problem.


Now when I comment out the line "Cmd.Prepared = True" then I receive a
slightly different error message:

Microsoft OLE DB Provider for ODBC Drivers error '80040e10'
[Microsoft][ODBC SQL Server Driver]COUNT field incorrect or syntax error


When I run a database trace, I can see that the statement is not actually
run on the server. So this looks to be some kind of driver problem. With
"Cmd.Prepared = True" the only thing run on the server is "SET FMTONLY ON
select UserID from tblFile where 1=2 SET FMTONLY OFF"


I have dropped every DDL dependencies (all two of them) so that should not
be a problem. I can change the CommandText to something crazy like
"UzzzzPDATE tblzzzzFile zzzz SEzzzT UszzzzerID = ?" and still get the same
two error messages. Heck, I've even gone so far as to remove the entire
TABLE from the database and I am *still* getting the same two error messages
complaining that I am missing a parameter. Only when I hardcode the UserID
value in the CommandText does it tell me the table does not exist.

Please help..... Any idea as to what is going on? This is killing me.

Thanks in advance.

Regards,
Mike

Re: VERY strange issue with SQL 2000 + ADO Command object

Sorry, I forgot to mention a couple of details:

I am running IIS/ASP on Windows XP SP 2 with MDAC 2.8 SP1. SQL Server 2000
Standard SP4 is running on a network machine that is running Windows 2003
Server Standard SP1.

The error is occurring on the "Cmd.Execute" line, though that was probably
obvious.

Thanks,
Mike M



"George Shawn" wrote in message
news:%23R0rGVdOHHA.3872@TK2MSFTNGP06.phx.gbl...
> Hi,
>
> I am running into an extremely frustrating problem and after hours of
> troubleshooting have not made any progress. When I try to use the ADO
> Command object to update a table, I receive the following error message:
>
> Microsoft OLE DB Provider for ODBC Drivers error '80040e10'
> No value given for one or more required parameters.
>
>
> Here is the entirety of the code that is running:
>
> Set Conn = Server.CreateObject("ADODB.Connection")
> Conn.Open Application("App_ConnectionString")
>
> Set Cmd = CreateObject("ADODB.Command")
> Cmd.ActiveConnection = Conn
> Cmd.CommandType = adCmdText
> Cmd.Prepared = True
>
> Cmd.CommandText = "UPDATE tblFile SET UserID = ?"
>
> Set var1 = Cmd.CreateParameter("UserID", adInteger, adParamInput)
> var1.Value = 1
> Cmd.Execute
>
> Set Cmd = Nothing
> Conn.Close
> Set Conn = Nothing
>
>
> I use this syntax all over the site with much more complicated queries,
> and have never come across this error before. Here is where it gets really
> funky..... I've logged var1.Value right before executing the command, and
> that is returning the correct value. However the "no value given" error
> message persists! If I take the exact same SQL statement and run it from
> SQL Management Studio, it runs without a hitch. Additionally if I change
> the ? to the value itself, then it also runs no problem.
>
>
> Now when I comment out the line "Cmd.Prepared = True" then I receive a
> slightly different error message:
>
> Microsoft OLE DB Provider for ODBC Drivers error '80040e10'
> [Microsoft][ODBC SQL Server Driver]COUNT field incorrect or syntax error
>
>
> When I run a database trace, I can see that the statement is not actually
> run on the server. So this looks to be some kind of driver problem. With
> "Cmd.Prepared = True" the only thing run on the server is "SET FMTONLY ON
> select UserID from tblFile where 1=2 SET FMTONLY OFF"
>
>
> I have dropped every DDL dependencies (all two of them) so that should not
> be a problem. I can change the CommandText to something crazy like
> "UzzzzPDATE tblzzzzFile zzzz SEzzzT UszzzzerID = ?" and still get the same
> two error messages. Heck, I've even gone so far as to remove the entire
> TABLE from the database and I am *still* getting the same two error
> messages complaining that I am missing a parameter. Only when I hardcode
> the UserID value in the CommandText does it tell me the table does not
> exist.
>
> Please help..... Any idea as to what is going on? This is killing me.
>
> Thanks in advance.
>
> Regards,
> Mike
>
>

Re: VERY strange issue with SQL 2000 + ADO Command object

Sorry, I forgot to mention a couple of details:

I am running IIS/ASP on Windows XP SP 2 with MDAC 2.8 SP1. SQL Server 2000
Standard SP4 is running on a network machine that is running Windows 2003
Server Standard SP1.

The error is occurring on the "Cmd.Execute" line, though that was probably
obvious.

Thanks,
Mike M



"George Shawn" wrote in message
news:%23R0rGVdOHHA.3872@TK2MSFTNGP06.phx.gbl...
> Hi,
>
> I am running into an extremely frustrating problem and after hours of
> troubleshooting have not made any progress. When I try to use the ADO
> Command object to update a table, I receive the following error message:
>
> Microsoft OLE DB Provider for ODBC Drivers error '80040e10'
> No value given for one or more required parameters.
>
>
> Here is the entirety of the code that is running:
>
> Set Conn = Server.CreateObject("ADODB.Connection")
> Conn.Open Application("App_ConnectionString")
>
> Set Cmd = CreateObject("ADODB.Command")
> Cmd.ActiveConnection = Conn
> Cmd.CommandType = adCmdText
> Cmd.Prepared = True
>
> Cmd.CommandText = "UPDATE tblFile SET UserID = ?"
>
> Set var1 = Cmd.CreateParameter("UserID", adInteger, adParamInput)
> var1.Value = 1
> Cmd.Execute
>
> Set Cmd = Nothing
> Conn.Close
> Set Conn = Nothing
>
>
> I use this syntax all over the site with much more complicated queries,
> and have never come across this error before. Here is where it gets really
> funky..... I've logged var1.Value right before executing the command, and
> that is returning the correct value. However the "no value given" error
> message persists! If I take the exact same SQL statement and run it from
> SQL Management Studio, it runs without a hitch. Additionally if I change
> the ? to the value itself, then it also runs no problem.
>
>
> Now when I comment out the line "Cmd.Prepared = True" then I receive a
> slightly different error message:
>
> Microsoft OLE DB Provider for ODBC Drivers error '80040e10'
> [Microsoft][ODBC SQL Server Driver]COUNT field incorrect or syntax error
>
>
> When I run a database trace, I can see that the statement is not actually
> run on the server. So this looks to be some kind of driver problem. With
> "Cmd.Prepared = True" the only thing run on the server is "SET FMTONLY ON
> select UserID from tblFile where 1=2 SET FMTONLY OFF"
>
>
> I have dropped every DDL dependencies (all two of them) so that should not
> be a problem. I can change the CommandText to something crazy like
> "UzzzzPDATE tblzzzzFile zzzz SEzzzT UszzzzerID = ?" and still get the same
> two error messages. Heck, I've even gone so far as to remove the entire
> TABLE from the database and I am *still* getting the same two error
> messages complaining that I am missing a parameter. Only when I hardcode
> the UserID value in the CommandText does it tell me the table does not
> exist.
>
> Please help..... Any idea as to what is going on? This is killing me.
>
> Thanks in advance.
>
> Regards,
> Mike
>
>

Re: VERY strange issue with SQL 2000 + ADO Command object

You forgot to append the new parameter to the collection.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"George Shawn" wrote in message
news:%23R0rGVdOHHA.3872@TK2MSFTNGP06.phx.gbl...
> Hi,
>
> I am running into an extremely frustrating problem and after hours of
> troubleshooting have not made any progress. When I try to use the ADO
> Command object to update a table, I receive the following error message:
>
> Microsoft OLE DB Provider for ODBC Drivers error '80040e10'
> No value given for one or more required parameters.
>
>
> Here is the entirety of the code that is running:
>
> Set Conn = Server.CreateObject("ADODB.Connection")
> Conn.Open Application("App_ConnectionString")
>
> Set Cmd = CreateObject("ADODB.Command")
> Cmd.ActiveConnection = Conn
> Cmd.CommandType = adCmdText
> Cmd.Prepared = True
>
> Cmd.CommandText = "UPDATE tblFile SET UserID = ?"
>
> Set var1 = Cmd.CreateParameter("UserID", adInteger, adParamInput)
> var1.Value = 1
> Cmd.Execute
>
> Set Cmd = Nothing
> Conn.Close
> Set Conn = Nothing
>
>
> I use this syntax all over the site with much more complicated queries,
> and have never come across this error before. Here is where it gets really
> funky..... I've logged var1.Value right before executing the command, and
> that is returning the correct value. However the "no value given" error
> message persists! If I take the exact same SQL statement and run it from
> SQL Management Studio, it runs without a hitch. Additionally if I change
> the ? to the value itself, then it also runs no problem.
>
>
> Now when I comment out the line "Cmd.Prepared = True" then I receive a
> slightly different error message:
>
> Microsoft OLE DB Provider for ODBC Drivers error '80040e10'
> [Microsoft][ODBC SQL Server Driver]COUNT field incorrect or syntax error
>
>
> When I run a database trace, I can see that the statement is not actually
> run on the server. So this looks to be some kind of driver problem. With
> "Cmd.Prepared = True" the only thing run on the server is "SET FMTONLY ON
> select UserID from tblFile where 1=2 SET FMTONLY OFF"
>
>
> I have dropped every DDL dependencies (all two of them) so that should not
> be a problem. I can change the CommandText to something crazy like
> "UzzzzPDATE tblzzzzFile zzzz SEzzzT UszzzzerID = ?" and still get the same
> two error messages. Heck, I've even gone so far as to remove the entire
> TABLE from the database and I am *still* getting the same two error
> messages complaining that I am missing a parameter. Only when I hardcode
> the UserID value in the CommandText does it tell me the table does not
> exist.
>
> Please help..... Any idea as to what is going on? This is killing me.
>
> Thanks in advance.
>
> Regards,
> Mike
>
>

Re: VERY strange issue with SQL 2000 + ADO Command object

You forgot to append the new parameter to the collection.

--
Sylvain Lafontaine, ing.
MVP - Technologies Virtual-PC
E-mail: sylvain aei ca (fill the blanks, no spam please)


"George Shawn" wrote in message
news:%23R0rGVdOHHA.3872@TK2MSFTNGP06.phx.gbl...
> Hi,
>
> I am running into an extremely frustrating problem and after hours of
> troubleshooting have not made any progress. When I try to use the ADO
> Command object to update a table, I receive the following error message:
>
> Microsoft OLE DB Provider for ODBC Drivers error '80040e10'
> No value given for one or more required parameters.
>
>
> Here is the entirety of the code that is running:
>
> Set Conn = Server.CreateObject("ADODB.Connection")
> Conn.Open Application("App_ConnectionString")
>
> Set Cmd = CreateObject("ADODB.Command")
> Cmd.ActiveConnection = Conn
> Cmd.CommandType = adCmdText
> Cmd.Prepared = True
>
> Cmd.CommandText = "UPDATE tblFile SET UserID = ?"
>
> Set var1 = Cmd.CreateParameter("UserID", adInteger, adParamInput)
> var1.Value = 1
> Cmd.Execute
>
> Set Cmd = Nothing
> Conn.Close
> Set Conn = Nothing
>
>
> I use this syntax all over the site with much more complicated queries,
> and have never come across this error before. Here is where it gets really
> funky..... I've logged var1.Value right before executing the command, and
> that is returning the correct value. However the "no value given" error
> message persists! If I take the exact same SQL statement and run it from
> SQL Management Studio, it runs without a hitch. Additionally if I change
> the ? to the value itself, then it also runs no problem.
>
>
> Now when I comment out the line "Cmd.Prepared = True" then I receive a
> slightly different error message:
>
> Microsoft OLE DB Provider for ODBC Drivers error '80040e10'
> [Microsoft][ODBC SQL Server Driver]COUNT field incorrect or syntax error
>
>
> When I run a database trace, I can see that the statement is not actually
> run on the server. So this looks to be some kind of driver problem. With
> "Cmd.Prepared = True" the only thing run on the server is "SET FMTONLY ON
> select UserID from tblFile where 1=2 SET FMTONLY OFF"
>
>
> I have dropped every DDL dependencies (all two of them) so that should not
> be a problem. I can change the CommandText to something crazy like
> "UzzzzPDATE tblzzzzFile zzzz SEzzzT UszzzzerID = ?" and still get the same
> two error messages. Heck, I've even gone so far as to remove the entire
> TABLE from the database and I am *still* getting the same two error
> messages complaining that I am missing a parameter. Only when I hardcode
> the UserID value in the CommandText does it tell me the table does not
> exist.
>
> Please help..... Any idea as to what is going on? This is killing me.
>
> Thanks in advance.
>
> Regards,
> Mike
>
>

Re: VERY strange issue with SQL 2000 + ADO Command object

George Shawn wrote:
>
> Set var1 = Cmd.CreateParameter("UserID", adInteger, adParamInput)
> var1.Value = 1
> Cmd.Execute
>
> Set Cmd = Nothing
> Conn.Close
> Set Conn = Nothing
>

You failed to append the parameter object to the Command's Parameters
collection before executing the Command:

Cmd.Parameters.Append var1
Cmd.Execute


Actually, in this case, you don't even need to bother with CreateParameter.
Use the second argument of the Command's Execute method to pass a variant
array containing the parameter values to the query engine:

arParms = Array(1)
Cmd.Execute ,arParms, 1 '1=adCmdText

You can pass as many parameter values as needed.

--
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: VERY strange issue with SQL 2000 + ADO Command object

George Shawn wrote:
>
> Set var1 = Cmd.CreateParameter("UserID", adInteger, adParamInput)
> var1.Value = 1
> Cmd.Execute
>
> Set Cmd = Nothing
> Conn.Close
> Set Conn = Nothing
>

You failed to append the parameter object to the Command's Parameters
collection before executing the Command:

Cmd.Parameters.Append var1
Cmd.Execute


Actually, in this case, you don't even need to bother with CreateParameter.
Use the second argument of the Command's Execute method to pass a variant
array containing the parameter values to the query engine:

arParms = Array(1)
Cmd.Execute ,arParms, 1 '1=adCmdText

You can pass as many parameter values as needed.

--
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: VERY strange issue with SQL 2000 + ADO Command object

Wow, I cannot believe I didn't catch that.... crazy.

Thanks to you and Bob for the very prompt reply!

Thanks,
Mike


"Sylvain Lafontaine"
wrote in message news:uC93FudOHHA.4104@TK2MSFTNGP06.phx.gbl...
> You forgot to append the new parameter to the collection.
>
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
>
> "George Shawn" wrote in message
> news:%23R0rGVdOHHA.3872@TK2MSFTNGP06.phx.gbl...
>> Hi,
>>
>> I am running into an extremely frustrating problem and after hours of
>> troubleshooting have not made any progress. When I try to use the ADO
>> Command object to update a table, I receive the following error message:
>>
>> Microsoft OLE DB Provider for ODBC Drivers error '80040e10'
>> No value given for one or more required parameters.
>>
>>
>> Here is the entirety of the code that is running:
>>
>> Set Conn = Server.CreateObject("ADODB.Connection")
>> Conn.Open Application("App_ConnectionString")
>>
>> Set Cmd = CreateObject("ADODB.Command")
>> Cmd.ActiveConnection = Conn
>> Cmd.CommandType = adCmdText
>> Cmd.Prepared = True
>>
>> Cmd.CommandText = "UPDATE tblFile SET UserID = ?"
>>
>> Set var1 = Cmd.CreateParameter("UserID", adInteger, adParamInput)
>> var1.Value = 1
>> Cmd.Execute
>>
>> Set Cmd = Nothing
>> Conn.Close
>> Set Conn = Nothing
>>
>>
>> I use this syntax all over the site with much more complicated queries,
>> and have never come across this error before. Here is where it gets
>> really funky..... I've logged var1.Value right before executing the
>> command, and that is returning the correct value. However the "no value
>> given" error message persists! If I take the exact same SQL statement and
>> run it from SQL Management Studio, it runs without a hitch. Additionally
>> if I change the ? to the value itself, then it also runs no problem.
>>
>>
>> Now when I comment out the line "Cmd.Prepared = True" then I receive a
>> slightly different error message:
>>
>> Microsoft OLE DB Provider for ODBC Drivers error '80040e10'
>> [Microsoft][ODBC SQL Server Driver]COUNT field incorrect or syntax error
>>
>>
>> When I run a database trace, I can see that the statement is not actually
>> run on the server. So this looks to be some kind of driver problem. With
>> "Cmd.Prepared = True" the only thing run on the server is "SET FMTONLY ON
>> select UserID from tblFile where 1=2 SET FMTONLY OFF"
>>
>>
>> I have dropped every DDL dependencies (all two of them) so that should
>> not be a problem. I can change the CommandText to something crazy like
>> "UzzzzPDATE tblzzzzFile zzzz SEzzzT UszzzzerID = ?" and still get the
>> same two error messages. Heck, I've even gone so far as to remove the
>> entire TABLE from the database and I am *still* getting the same two
>> error messages complaining that I am missing a parameter. Only when I
>> hardcode the UserID value in the CommandText does it tell me the table
>> does not exist.
>>
>> Please help..... Any idea as to what is going on? This is killing me.
>>
>> Thanks in advance.
>>
>> Regards,
>> Mike
>>
>>
>
>

Re: VERY strange issue with SQL 2000 + ADO Command object

Wow, I cannot believe I didn't catch that.... crazy.

Thanks to you and Bob for the very prompt reply!

Thanks,
Mike


"Sylvain Lafontaine"
wrote in message news:uC93FudOHHA.4104@TK2MSFTNGP06.phx.gbl...
> You forgot to append the new parameter to the collection.
>
> --
> Sylvain Lafontaine, ing.
> MVP - Technologies Virtual-PC
> E-mail: sylvain aei ca (fill the blanks, no spam please)
>
>
> "George Shawn" wrote in message
> news:%23R0rGVdOHHA.3872@TK2MSFTNGP06.phx.gbl...
>> Hi,
>>
>> I am running into an extremely frustrating problem and after hours of
>> troubleshooting have not made any progress. When I try to use the ADO
>> Command object to update a table, I receive the following error message:
>>
>> Microsoft OLE DB Provider for ODBC Drivers error '80040e10'
>> No value given for one or more required parameters.
>>
>>
>> Here is the entirety of the code that is running:
>>
>> Set Conn = Server.CreateObject("ADODB.Connection")
>> Conn.Open Application("App_ConnectionString")
>>
>> Set Cmd = CreateObject("ADODB.Command")
>> Cmd.ActiveConnection = Conn
>> Cmd.CommandType = adCmdText
>> Cmd.Prepared = True
>>
>> Cmd.CommandText = "UPDATE tblFile SET UserID = ?"
>>
>> Set var1 = Cmd.CreateParameter("UserID", adInteger, adParamInput)
>> var1.Value = 1
>> Cmd.Execute
>>
>> Set Cmd = Nothing
>> Conn.Close
>> Set Conn = Nothing
>>
>>
>> I use this syntax all over the site with much more complicated queries,
>> and have never come across this error before. Here is where it gets
>> really funky..... I've logged var1.Value right before executing the
>> command, and that is returning the correct value. However the "no value
>> given" error message persists! If I take the exact same SQL statement and
>> run it from SQL Management Studio, it runs without a hitch. Additionally
>> if I change the ? to the value itself, then it also runs no problem.
>>
>>
>> Now when I comment out the line "Cmd.Prepared = True" then I receive a
>> slightly different error message:
>>
>> Microsoft OLE DB Provider for ODBC Drivers error '80040e10'
>> [Microsoft][ODBC SQL Server Driver]COUNT field incorrect or syntax error
>>
>>
>> When I run a database trace, I can see that the statement is not actually
>> run on the server. So this looks to be some kind of driver problem. With
>> "Cmd.Prepared = True" the only thing run on the server is "SET FMTONLY ON
>> select UserID from tblFile where 1=2 SET FMTONLY OFF"
>>
>>
>> I have dropped every DDL dependencies (all two of them) so that should
>> not be a problem. I can change the CommandText to something crazy like
>> "UzzzzPDATE tblzzzzFile zzzz SEzzzT UszzzzerID = ?" and still get the
>> same two error messages. Heck, I've even gone so far as to remove the
>> entire TABLE from the database and I am *still* getting the same two
>> error messages complaining that I am missing a parameter. Only when I
>> hardcode the UserID value in the CommandText does it tell me the table
>> does not exist.
>>
>> Please help..... Any idea as to what is going on? This is killing me.
>>
>> Thanks in advance.
>>
>> Regards,
>> Mike
>>
>>
>
>