Missing parameter error, when parameter IS there

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Development Tools

Other Development Tools

Missing parameter error, when parameter IS there

Author

Topic

VanessaEHL
Starting Member

3 Posts

Posted - 2005-05-16 : 12:09:09

I have a stored procedure that I am trying to call from VB6 to SQL Server. The stored proc takes several parameters. In my VB code, I've created the parameters, set the values, and appended them to the command object. However, when I execute the command, I get "SP_Whatever expected parameter '@a' which was not supplied."

The procedure runs successfully if I remove the scoundrel parameter from the sp. I've checked the command object and it does have the parameter. I've checked spelling.

Could it be a problem with the data type? I've tried several, just in case. Here's the code I'm currently using for adding the parameter:
oComAddNewRecord.Parameters.Append oComAddNewRecord.CreateParameter("@EB_KRONOS", adBoolean, adParamInput, 1, CStr(chkKronos.Value))
The sp is expecting @EB_KRONOS to be a bit data type.

Any ideas?

monkeybite
Posting Yak Master

152 Posts

Posted - 2005-05-16 : 12:30:23

Make sure the CommandType on your command object is set to StoredProcedure before you execute it.

~ monkey

jsmith8858
Dr. Cross Join

7423 Posts

Posted - 2005-05-16 : 13:03:58

if it's a bit or boolean, why are you converting it to a string?

CStr(chkKronos.Value)

??

- Jeff

VanessaEHL
Starting Member

3 Posts

Posted - 2005-05-16 : 13:13:26

The commandtype is set to storedprocedure. I forgot to mention that it runs perfectly if I remove the @EB_KRONOS parameter from the sp.

As for the CStr - that was leftover from the original programmer and I neglected to remove it. I have taken that out now, but still have the same problem.

Thanks for your replies. Any more ideas?

spirit1
Cybernetic Yak Master

11752 Posts

Posted - 2005-05-17 : 11:45:17

i think you should remove the @ from the parameter name.

Go with the flow & have fun! Else fight the flow

VanessaEHL
Starting Member

3 Posts

Posted - 2005-05-17 : 11:50:28

To clarify, the error message says parameter '@EB_KRONOS' was expected, not '@a'. I'm fairly new to SQL, I thought the @ was necessary. Is it just standard, but not necessary?

Also, I changed the order in which I create parameters to match the order they are listed in the sp. That fixed the problem. I didn't realize it mattered. I thought it keyed off of the parameter name, but I guess it wants them in order just like any function, sub, or procedure.

Thanks all for your suggestions.

monkeybite
Posting Yak Master

152 Posts

Posted - 2005-05-19 : 13:28:54

quote:
Originally posted by VanessaEHL
Also, I changed the order in which I create parameters to match the order they are listed in the sp. That fixed the problem.

As a rule, you should always include the "@" in the parameter name, and the name should match the parameter name as specified in the SPROC. This will become necessary when you transition to ADO.NET.

In ADO:
- Order of parameters in your command object IS important
- Name of the parameters ARE NOT important

In ADO.NET:
- Order of the parameters in your command object IS NOT important
- Name of the parameter (include the "@") IS important

Best to start combining the rules for both, put the params in order, and start using the "@".

~ monkey

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources