this stored procedure in sql query analyzer works correctly. but!

  • Thread starter Thread starter William Ryan eMVP
  • Start date Start date
W

William Ryan eMVP

I'm not familiar with SqlHelper but the likely causes are that @ID isn't
being supplied, either the Value is null or wrong or it isn't being added to
the collection. Not specifiying the commandType property is often a problem
when things run in QA but not in ADO.NET but that exception is pretty
specific and I'm make sure that both the paramter is being added and that it
has a value.

HTH,

Bill

--
W.G. Ryan MVP Windows - Embedded

http://forums.devbuzz.com
http://www.knowdotnet.com/dataaccess.html
http://www.msmvps.com/williamryan/
 
i have a stored procedure :
CREATE PROCEDURE [AddBuyer]
(
@ID [int],
@Name [varchar](50),
@Address [varchar](50),
@Phone [int]
)
AS ...

this procedure in sql query analyzer works correctly.
but:
i had created a app and test these lines of code:

SqlConnection sqlCon = new SqlConnection(Connection.String);
SqlCommand insertCommand = SqlHelper.CreateCommand(sqlCon,"AddBuyer",
"@ID","@Name","@Address","@Phone");
SqlCommand updateCommand =
SqlHelper.CreateCommand(sqlCon,"ChangeBuyerInfo",
"@ID","@Name","@Address","@Phone");
SqlCommand deleteCommand = SqlHelper.CreateCommand(sqlCon,"DeleteBuyer",
"@ID");

SqlHelper.UpdateDataset(insertCommand,deleteCommand,updateCommand,dsBus,tabl
eName);
sqlCon.Close();

this code raises an error and error message is:
procedure 'AddBuyer' expects parameter '@ID', which was not supplied.
 
i had traced the program and saw that all commands have correct parameters but no value (DBNull
do you think that thay must have values because dataset has mor than one record that should be added
how can i add values to commands.
 
i had traced the program and saw that the commands has parameters and has not value (DBNull)
do you think thay must have value. beacause sqlhelper updates the dataset and dataset has mor than on record that should be added.
 
it would be better if you try sending the paramaters using SqlParameter[]
where you can define the datatypes clearly. try this out
 
If the proc signature specifies Parameters, you need to supply something. If
you want Null to be inserted intentionally, use DbNull.Value . Otherwise,
specify a Default param for the parameter in the procedure
http://www.knowdotnet.com/articles/defaultparameters.html or the proc will
think you forgot about it.

BTW, did I read you right in that all of the parameters have no values?
This sounds like a problem unless you are doing it on purpose...and if you
are, you may want to reconsider that strategy. If they are all null,
hopefully that's just with one record b/c if they will always be null, then
you don't really need the params right?

Anyway, try adding the default and let me know if that fixes it. If not,
let me know too and we'll walk through it.

HTH,

Bill

--
W.G. Ryan MVP Windows - Embedded

http://forums.devbuzz.com
http://www.knowdotnet.com/dataaccess.html
http://www.msmvps.com/williamryan/
Hossein Margani said:
i had traced the program and saw that the commands has parameters and has not value (DBNull).
do you think thay must have value. beacause sqlhelper updates the dataset
Click to expand...
and dataset has mor than on record that should be added.
 
i have traced the program. and found that all parameters are correct but when executing the UpdateDataSet it throws exception, i mention that all parameters have value DBNull.
 
no, i have added the DBNull.Value and the problem exist
i added the default values but added records have default values and not the meaningful records the i added to dataset.
 
no, i have added the DBNull.Value and the problem exist
i added the default values but added records have default values and not the meaningful records the i added to dataset
i mention that your article was very good and it was very usefull.
 
Back
Top