ExamGuru IT/.Net Guru : ERROR Procedure 'ABC' expects parameter '@abc', which was not supplied. on microsoft.public.dotnet.framework.odbcnet

.NETGURU

ERROR Procedure 'ABC' expects parameter '@abc', which was not supplied.

Messages

Related Types

This message was discovered on microsoft.public.dotnet.framework.odbcnet.
Responses highlighted in red are from those people who are likely to be able to contribute good, authoratitive information to this discussion. They include Microsoft employees, MVP's and others who IMHO contribute well to these kinds of discussions.

Post a new message to this list...

Chris Hayes

Greetings,

I am getting this error when I try to populate a DataTable by executing a
stored procedure on a SQL Server 2000 database through an ODBC in a VB.NET
application: ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL
Server]Procedure 'ABC' expects parameter @abc, which was not supplied.

'cmd is passed into a helper function that excutes this code
conn = New System.Data.Odbc.OdbcConnection(connectionString)
cmd.Connection = conn
da = New System.Data.Odbc.OdbcDataAdapter(cmd)
da.Fill(dt)

I have already seen and implemented the fix suggested in Knowledge Base
Article 286359 and it has not solved the problem. I've checked the three
files that this article suggested for their current versions (Odbcbcp.dll,
sqlsrv32.dll, sqlsrv32.rll) and they are all version 2000.85.1022.0 which is
greater than the fix versions.

Going through a System.Data.SqlClient connection is not the preferred option
as the client I am developing this for prefers ODBC connections.

Are there any other causes or fixes that I have missed?

Thank you,

Chris

Reply to this message...

Kevin Yu [MSFT] (VIP)

Hi Chris,

Thank you for posting in the community!

First of all, I would like to confirm my understanding of your issue. From
your description, I understand that you are executing a stored procedure in
C# using ODBC data provider. An exception was thrown when filling the
DataTable. If there is any misunderstanding, please feel free to let me
know.

From the description of the error, we can see that the stored procedure
'ABC' requires parameter 'abc'. Since you haven't add the parameter to
cmd's parameter collection, an error is generated by SQL server. We can add
parameters with cmd.Parameters.Add() method. Please try to add the
following code to the program before your code.

    cmd.CommandType = CommandType.StoredProcedure;
    OdbcParameter p = cmd.Parameters.Add("@abc", OdbcType.Int,4);
    p.Value = 1;

For more information about OdbcParameters, please check the following links:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/
frlrfsystemdataodbcodbccommandclassparameterstopic.asp

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/cpref/html/
frlrfsystemdataodbcodbcparameterclasstopic.asp

Hope this helps. If anything is unclear, please feel free to reply to the
post.

Kevin Yu
=======
"This posting is provided "AS IS" with no warranties, and confers no
rights."

Reply to this message...

Chris Hayes

and I just noticed I pasted a flawed copy of the code... @_spGet is actually
_spGet...I'm really having a bad week...

"Kevin Yu [MSFT]" <Click here to reveal e-mail address> wrote in message
news:Click here to reveal e-mail address...
[Original message clipped]

Reply to this message...

Chris Hayes

Doh, you ever have one of those weeks where you forget things and leave out
details...I'm having one...

By the way, heres the error message that I am getting:
ERROR [42000] [Microsoft][ODBC SQL Server Driver][SQL Server]Must declare
the variable '@_spGet'.
SQLSRV32.DLL
at System.Data.Odbc.OdbcConnection.HandleError(HandleRef hrHandle,
SQL_HANDLE hType, RETCODE retcode)
at System.Data.Odbc.OdbcCommand.ExecuteReaderObject(CommandBehavior
behavior, String method)
at System.Data.Odbc.OdbcCommand.ExecuteReader(CommandBehavior behavior)
at
System.Data.Odbc.OdbcCommand.System.Data.IDbCommand.ExecuteReader(CommandBeh
avior behavior)
at System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32
startRecord, Int32 maxRecords, String srcTable, IDbCommand command,
CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable, IDbCommand
command, CommandBehavior behavior)
at System.Data.Common.DbDataAdapter.Fill(DataTable dataTable)

"Kevin Yu [MSFT]" <Click here to reveal e-mail address> wrote in message
news:Click here to reveal e-mail address...
[Original message clipped]

Reply to this message...

Chris Hayes

AAARRRGGGHHH!!!

Hi Kevin,

I actually found out what I was doing wrong! I was stuck in the mindset of
SqlCommand instead of OdbcCommand and I've got it working (now that I've
re-re-read the information on how to use OdbcParameters and OdbcCommands).

Thanks for your help...I'll go away now...

Chris

"Kevin Yu [MSFT]" <Click here to reveal e-mail address> wrote in message
news:Click here to reveal e-mail address...
[Original message clipped]

Reply to this message...

DotNetJunkies User

Hi Chris,
Could you please tell me as to how you fixed this problem,. I am having the same problem for which I am unaware of a solution. An early reply in this regard to the forum would be appreciated.

Thx
Sreeram.
------------------------------------------------------------------------
AAARRRGGGHHH!!!

Hi Kevin,

I actually found out what I was doing wrong! I was stuck in the mindset of
SqlCommand instead of OdbcCommand and I've got it working (now that I've
re-re-read the information on how to use OdbcParameters and OdbcCommands).

Thanks for your help...I'll go away now...

Chris

---
Posted using Wimdows.net NntpNews Component -

Post Made from http://www.DotNetJunkies.com/newsgroups Our newsgroup engine supports Post Alerts, Ratings, and Searching.

Reply to this message...

Chris Hayes

Hello Kevin,

Oops I guess I edited out too much code in the snippet I posted (which is
slimmed down and simplier version of what I am trying to do). I actually do
add parameters to the odbc command and when I enter into debug mode I can
see that the collection is properly populated.

Here is a simpler code snippet that creates the same results:
Dim dt As New DataTable
Dim conn As New System.Data.Odbc.OdbcConnection(connectionString)
Dim cmd As New System.Data.Odbc.OdbcCommand("@_spGet", conn)
Dim da As New System.Data.Odbc.OdbcDataAdapter(cmd)
Dim param As New System.Data.Odbc.OdbcParameter("@active",
System.Data.Odbc.OdbcType.Bit)
param.Value = True
cmd.Parameters.Add(param)
da.Fill(dt)

It's eerily similar to the Knowledge Base Article 286359 (from which I
implemented the fixes outlined by updating the MDAC install on my
development station).

I'm at a total loss...

Thanks for your help,

Chris

"Kevin Yu [MSFT]" <Click here to reveal e-mail address> wrote in message
news:Click here to reveal e-mail address...
[Original message clipped]

Reply to this message...

	System.Data.CommandBehavior
	System.Data.CommandType
	System.Data.Common.DbDataAdapter
	System.Data.DataTable
	System.Data.IDbCommand
	System.Data.Odbc.OdbcCommand
	System.Data.Odbc.OdbcConnection
	System.Data.Odbc.OdbcDataAdapter
	System.Data.Odbc.OdbcParameter
	System.Data.Odbc.OdbcType
	System.Data.SqlClient.SqlCommand
	System.Runtime.InteropServices.HandleRef

ExamGuru IT Solutions - .Net Guru is owned and operated by ExamGuru, Inc., the man behind .Net Guru. If you're in the market for bespoke software or software consultancy, why not get him and his highly trained team to help? - www.examguru.net/ITCertification

Need Dot Net Interview Questions?
Ask ExamGuru, Inc. for advice and help on Passing .Net Interviews

.Net Projects
Best-of-breed application framework for .NET projects, developed by ExamGuru, Inc. and ExamGuru IT

Free .net Help
Commission ExamGuru, Inc. and his team for your next bespoke software project

FogBUGZ
The only bug tracking system carefully crafted with one goal in mind: helping teams create great software.

Awesome Tools
If you don't know about these, you're missing out... IT Certification Questions
IT Interview Questions
Free Oracle 10g Training
MCSE Boortcamp
Cisco Study Guides
Cheap Study Guides
Exact Questions
Dot Net Interview Questions
Oracle OCP
Cheap Travel
Designer Perfumes - Wholesale Prices
Free Programming Tutorials

	Copyright © ExamGuru, Inc. 2001-2006
	Contact Us - Terms of Use - Privacy Policy - www.dot-net-guru.com - www.examguru.net - www.oraclesource.net - www.itinterviews.net - www.examguru.net/ITCertification