Passing SQL Command -> Stored Procedure

[Joe]

Hey guys,

My first post here, so ill do my best to be clear and brief.

I am trying to pass a SQL command in .NET to execute a stored procedure in SQL. I already have my DB connection created in the GUI along with my data adapter and dataset.

I know this has to be something small I am missing so maybe another pair of eyes can see it.

Data Adapter = dbAdapter
connDB = DB connection
dsDataSet = data set
frmAdd is the name of the new form to pull the data from to pass to the stored procedure.

I think I am somehow missing some way to execute the command, but I cant seem to find a particular one for dataAdapter (already tried all the options in Help). I can't figure out how to get the command passed into SQL...

Ok here's the code:

PrivateSub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click
Dim addForm AsNew frmAdd
Dim vbTitle AsString
Dim vbLength AsInteger
Dim vbRentalRate AsDecimal
Dim vbCost AsDecimal
Dim vbPurchaseDate AsString
Dim vbRating AsString
Dim vbFormat AsString
Dim saveCommand As SqlCommand

addForm.ShowDialog()

vbTitle = addForm.txtTitle.Text
vbLength = Val(addForm.txtLength.Text)
vbRentalRate = Val(addForm.txtRentalRate.Text)
vbCost = Val(addForm.txtCost.Text)
vbPurchaseDate = addForm.txtPurchaseDate.Text
vbRating = addForm.txtRating.Text
vbFormat = addForm.txtFormat.Text

saveCommand = New SqlCommand("EXEC addRecord vbTitle, vbLength, vbRentalRate, vbCost, vbPurchaseDate, vbRating, vbFormat", connDB)
'* Call to stored procedure in SQL, yes it works

daAdapter.Fill(dsDataSet)
EndSub

Thanks to anyone who takes a quick look!

 

[BadgerByte]

Hey Joe,

You can use the ExecuteNonQuery or ExecuteScalar Methods of the Command (saveCommand)

Hope that helps.

 

[Joe]

Yep, tried that.. it crashes when I try to fill my data set

 

[Bob Langlade]

If you really want to try the data adapter, you could try this, but I am not sure it will work :

 [SIZE=2]
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] Cstring [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE][SIZE=2] = "integrated security=SSPI;data source=Your Server;persist security info=False;initial catalog=Your Database"
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] DsDataSet [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] DataSet
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] DaAdapter [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlDataAdapter("EXEC addRecord vbTitle, vbLength, vbRentalRate, vbCost, vbPurchaseDate, " _
& "vbRating, vbFormat", Cstring)
 

[/SIZE][SIZE=2][COLOR=#0000ff]Try[/COLOR][/SIZE][INDENT][SIZE=2]DaAdapter.Fill(DsDataSet)
[/SIZE]
[/INDENT][SIZE=2]

[/SIZE][SIZE=2][COLOR=#0000ff]Catch[/COLOR][/SIZE][SIZE=2] ex [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] Exception[/SIZE][INDENT][SIZE=2]MessageBox.Show(ex.Message)
[/SIZE]
[/INDENT]
[SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Try[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]
[/COLOR][/SIZE]

As suggested by BadgerByte the ExecuteNonQuery Method seems a better choice :

[SIZE=2]
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] ConnDB [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlConnection("integrated security=SSPI;data source=Your Server;persist security info=False;initial catalog=Your Database")
[/SIZE][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][SIZE=2] SaveCommand [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][SIZE=2] SqlCommand("EXEC addRecord vbTitle, vbLength, vbRentalRate, vbCost, vbPurchaseDate, " _
& "vbRating, vbFormat", ConnDB)

[/SIZE][SIZE=2][COLOR=#0000ff]Try[/COLOR][/SIZE][INDENT][SIZE=2]SaveCommand.ExecuteNonQuery()[/SIZE]

[/INDENT][SIZE=2][COLOR=#0000ff]Catch[/COLOR][/SIZE][SIZE=2] ex [/SIZE][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][SIZE=2] Exception[/SIZE][INDENT][SIZE=2]MessageBox.Show(ex.Message)
[/SIZE]
[/INDENT][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]Try
 
[/COLOR][/SIZE]

 

[Joe]

Ok, ill try that thanks!