469,625 Members | 1,086 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,625 developers. It's quick & easy.

Error in code?

I am having a weird error and maybe the synatax is different or something. I
use a SQL Stored Proc and pass it one param and get a return to either a
datareader or dataset. The code works fine for a datareader but the dataset
gives me a weird error. I even tried using the same SP with the two code
snippets, one works the other doesn't.
tmpSQL.SQLDB.Open()
tmpSQL.SQLCmd.CommandType = CommandType.StoredProcedure
tmpSQL.SQLCmd.CommandText = "SP_GETRECORDTEST"
SqlClient.SqlCommandBuilder.DeriveParameters(tmpSQ L.SQLCmd) <<THIS SHOWS OK
PARAMS IN DEBUG WNDW
tmpSQL.SQLCmd.Parameters(1).Value = paramNbr << SEEMS TO IGNORE THIS
Dim DS As DataSet = New DataSet
Dim DA As SqlDataAdapter = New SqlDataAdapter(tmpSQL.SQLCmd.CommandText,
tmpSQL.SQLDB)
DA.Fill(DS) <<<<<<<<<BOOM says the SP expects param and it was not passed

but this code works....
tmpSQL.SQLDB.Open()
tmpSQL.SQLCmd.CommandType = CommandType.StoredProcedure
tmpSQL.SQLCmd.CommandText = "SP_GETRECORDAGAIN"
SqlClient.SqlCommandBuilder.DeriveParameters(tmpSQ L.SQLCmd)
tmpSQL.SQLCmd.Parameters(1).Value = paramNBR
dReader = tmpSQL.SQLCmd.ExecuteReader()
dReader.Read()

I tried calling the SP_GETRECORDTEST with the datareader code and it works,
so I know the SP is OK..
I need the dataset to work for a datagrid I have, but can't get it to see
the param,
even tried a tmpSQL.SQLCmd.Parameters.Add("@tmpParam",2) with the DS
code..
did not make any difference..
Any Ideas?
Thanks JOHN


Nov 19 '05 #1
2 1260
Take a look at the constructor for your DataAdapter. You're passing the
CommandText to it. What IS the CommandText? Hint:
tmpSQL.SQLCmd.CommandText = "SP_GETRECORDTEST"
See any parameters in there?

Instead, assign the Connection to the Command, and use the Command instance
in the constructor for the DataAdapter.

--
HTH,

Kevin Spencer
Microsoft MVP
..Net Developer
Everybody picks their nose,
But some people are better at hiding it.

"John" <Jo****@aol.com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...I am having a weird error and maybe the synatax is different or something.
I
use a SQL Stored Proc and pass it one param and get a return to either a
datareader or dataset. The code works fine for a datareader but the
dataset
gives me a weird error. I even tried using the same SP with the two code
snippets, one works the other doesn't.
tmpSQL.SQLDB.Open()
tmpSQL.SQLCmd.CommandType = CommandType.StoredProcedure
tmpSQL.SQLCmd.CommandText = "SP_GETRECORDTEST"
SqlClient.SqlCommandBuilder.DeriveParameters(tmpSQ L.SQLCmd) <<THIS SHOWS
OK
PARAMS IN DEBUG WNDW
tmpSQL.SQLCmd.Parameters(1).Value = paramNbr << SEEMS TO IGNORE THIS
Dim DS As DataSet = New DataSet
Dim DA As SqlDataAdapter = New SqlDataAdapter(tmpSQL.SQLCmd.CommandText,
tmpSQL.SQLDB)
DA.Fill(DS) <<<<<<<<<BOOM says the SP expects param and it was not passed

but this code works....
tmpSQL.SQLDB.Open()
tmpSQL.SQLCmd.CommandType = CommandType.StoredProcedure
tmpSQL.SQLCmd.CommandText = "SP_GETRECORDAGAIN"
SqlClient.SqlCommandBuilder.DeriveParameters(tmpSQ L.SQLCmd)
tmpSQL.SQLCmd.Parameters(1).Value = paramNBR
dReader = tmpSQL.SQLCmd.ExecuteReader()
dReader.Read()

I tried calling the SP_GETRECORDTEST with the datareader code and it
works,
so I know the SP is OK..
I need the dataset to work for a datagrid I have, but can't get it to see
the param,
even tried a tmpSQL.SQLCmd.Parameters.Add("@tmpParam",2) with the DS
code..
did not make any difference..
Any Ideas?
Thanks JOHN


Nov 19 '05 #2
Thanks Kevin... I had to add
DA.SelectCommand.Parameters.Add("@tmpParam", paramNBR)

DA.SelectCommand.CommandType = CommandType.StoredProcedure

to make it work for the dataadapter but not the datareader, it worked fine
with the original code

"Kevin Spencer" <ke***@DIESPAMMERSDIEtakempis.com> wrote in message
news:eS**************@TK2MSFTNGP12.phx.gbl...
Take a look at the constructor for your DataAdapter. You're passing the
CommandText to it. What IS the CommandText? Hint:
tmpSQL.SQLCmd.CommandText = "SP_GETRECORDTEST"
See any parameters in there?

Instead, assign the Connection to the Command, and use the Command

instance in the constructor for the DataAdapter.

--
HTH,

Kevin Spencer
Microsoft MVP
.Net Developer
Everybody picks their nose,
But some people are better at hiding it.

"John" <Jo****@aol.com> wrote in message
news:%2****************@TK2MSFTNGP09.phx.gbl...
I am having a weird error and maybe the synatax is different or something.I
use a SQL Stored Proc and pass it one param and get a return to either a
datareader or dataset. The code works fine for a datareader but the
dataset
gives me a weird error. I even tried using the same SP with the two code
snippets, one works the other doesn't.
tmpSQL.SQLDB.Open()
tmpSQL.SQLCmd.CommandType = CommandType.StoredProcedure
tmpSQL.SQLCmd.CommandText = "SP_GETRECORDTEST"
SqlClient.SqlCommandBuilder.DeriveParameters(tmpSQ L.SQLCmd) <<THIS SHOWS
OK
PARAMS IN DEBUG WNDW
tmpSQL.SQLCmd.Parameters(1).Value = paramNbr << SEEMS TO IGNORE THIS
Dim DS As DataSet = New DataSet
Dim DA As SqlDataAdapter = New SqlDataAdapter(tmpSQL.SQLCmd.CommandText,
tmpSQL.SQLDB)
DA.Fill(DS) <<<<<<<<<BOOM says the SP expects param and it was not passed
but this code works....
tmpSQL.SQLDB.Open()
tmpSQL.SQLCmd.CommandType = CommandType.StoredProcedure
tmpSQL.SQLCmd.CommandText = "SP_GETRECORDAGAIN"
SqlClient.SqlCommandBuilder.DeriveParameters(tmpSQ L.SQLCmd)
tmpSQL.SQLCmd.Parameters(1).Value = paramNBR
dReader = tmpSQL.SQLCmd.ExecuteReader()
dReader.Read()

I tried calling the SP_GETRECORDTEST with the datareader code and it
works,
so I know the SP is OK..
I need the dataset to work for a datagrid I have, but can't get it to see the param,
even tried a tmpSQL.SQLCmd.Parameters.Add("@tmpParam",2) with the DS
code..
did not make any difference..
Any Ideas?
Thanks JOHN



Nov 19 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

5 posts views Thread by Tony Wright | last post: by
2 posts views Thread by Gregory | last post: by
6 posts views Thread by Peter Frost | last post: by
16 posts views Thread by Steve Jorgensen | last post: by
7 posts views Thread by p | last post: by
4 posts views Thread by Eugene Anthony | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.