473,231 Members | 1,952 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,231 software developers and data experts.

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
Jan 17 '07 #1
4 5125
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" <ge****@nospam.comwrote in message
news:%2****************@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


Jan 17 '07 #2
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" <ge****@nospam.comwrote in message
news:%2****************@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


Jan 17 '07 #3
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"
Jan 17 '07 #4
Wow, I cannot believe I didn't catch that.... crazy.

Thanks to you and Bob for the very prompt reply!

Thanks,
Mike
"Sylvain Lafontaine" <sylvain aei ca (fill the blanks, no spam please)>
wrote in message news:uC**************@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" <ge****@nospam.comwrote in message
news:%2****************@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



Jan 17 '07 #5

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

4
by: Stefano | last post by:
Hi everibody, it's the first time i post on this newsgroup. I'm Stefano from Milano, italy. I'm a beginners with Sql2000. My problem is this. I run a View using enterprise manager and after less...
24
by: LineVoltageHalogen | last post by:
Greetings All, I was hoping that someone out there has run into this issue before and can shed some light on it for me. I have a stored procedure that essentially does a mini ETL from a source...
2
by: Neil | last post by:
I have a strange situation. I have a stored procedure that is hanging upon execution, but only some machines and not others. The db is an Access 2000 MDB using ODBC linked tables and a SQL 7 back...
25
by: Neil Ginsberg | last post by:
I have a strange situation with my Access 2000 database. I have code in the database which has worked fine for years, and now all of a sudden doesn't work fine on one or two of my client's...
9
by: Vito DeCarlo | last post by:
I've been having this problem for a few weeks. PLEASE read this post before responding with some simple reason that has nothing to do with my problem. If you need more information, please request...
10
by: Trapulo | last post by:
Why Now.Date.Subtract(New Date(2000, 1, 1)).Days returns 731529?? It is a too big value a think! I aspect something as 1030-1100....
1
by: Raziq Shekha | last post by:
Hi Folks, SQL Server 2000 SP3 on Windows 2000. I have a database on which I ran the command : dbcc dbreindex ('tablename') go for all tables in the database. Then I compared the dbcc...
11
by: Mike C# | last post by:
Hi all, I keep getting a strange error and can't pin it down. The message is: This application has requested the Runtime to terminate it in an unusual way. Please contact the application's...
3
by: Max Vit | last post by:
I have come across a strange issue whilst trying to use a shell command call from Access and have spent some time trying to figure this out but can't find the cause as yet. The issue is: I need...
0
by: VivesProcSPL | last post by:
Obviously, one of the original purposes of SQL is to make data query processing easy. The language uses many English-like terms and syntax in an effort to make it easy to learn, particularly for...
0
by: jianzs | last post by:
Introduction Cloud-native applications are conventionally identified as those designed and nurtured on cloud infrastructure. Such applications, rooted in cloud technologies, skillfully benefit from...
0
by: abbasky | last post by:
### Vandf component communication method one: data sharing ​ Vandf components can achieve data exchange through data sharing, state sharing, events, and other methods. Vandf's data exchange method...
0
by: fareedcanada | last post by:
Hello I am trying to split number on their count. suppose i have 121314151617 (12cnt) then number should be split like 12,13,14,15,16,17 and if 11314151617 (11cnt) then should be split like...
0
by: stefan129 | last post by:
Hey forum members, I'm exploring options for SSL certificates for multiple domains. Has anyone had experience with multi-domain SSL certificates? Any recommendations on reliable providers or specific...
0
Git
by: egorbl4 | last post by:
Скачал я git, хотел начать настройку, а там вылезло вот это Что это? Что мне с этим делать? ...
1
by: davi5007 | last post by:
Hi, Basically, I am trying to automate a field named TraceabilityNo into a web page from an access form. I've got the serial held in the variable strSearchString. How can I get this into the...
0
by: MeoLessi9 | last post by:
I have VirtualBox installed on Windows 11 and now I would like to install Kali on a virtual machine. However, on the official website, I see two options: "Installer images" and "Virtual machines"....
0
by: Aftab Ahmad | last post by:
So, I have written a code for a cmd called "Send WhatsApp Message" to open and send WhatsApp messaage. The code is given below. Dim IE As Object Set IE =...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.