Error in code?

[John]

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(tmpSQL.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(tmpSQL.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

 

[Kevin Spencer]

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]

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