Using SP's inside VB.NET

[rzamith]

Im trying to manipulate my SQL Server database inside VB.net.. never done it, and im having some troubles!

The only example I have is this one:

Dim ds_sp As DataSet
Dim conexao As SqlClient.SqlConnection
Dim da_sp As SqlClient.SqlDataAdapter
 
conexao = New SqlClient.SqlConnection("server=(local); ... )
 
da_sp = New SqlClient.SqlDataAdapter("SP_Name", conexao)
da_sp.SelectCommand.CommandType = CommandType.StoredProcedure
 
da_sp.SelectCommand.Parameters.Add(New SqlClient.SqlParameter("@code", SqlDbType.SmallInt))
 
da_sp.SelectCommand.Parameters("@codigo").Value = TextBox4.Text
 
ds_sp = New DataSet
 
da_sp.Fill(ds_sp, "SP_Name")
da_sp.Dispose()
conexao.Close()

im trying to use this on a "saving" SP, using the textbox4.text and transfer it to a database field.. not working. Need some help, never used this way of connection between sql and vb.net, but also dont like the dataset option..

Thanks!

 

[cjard]

why would you add a parameter named "@code":
da_sp.SelectCommand.Parameters.Add(New SqlClient.SqlParameter("@code", SqlDbType.SmallInt))

and then refer to a parameter called "@codigo":
da_sp.SelectCommand.Parameters("@codigo").Value = TextBox4.Text

and then why (o why) would you put a string (textbox4.Text) into an int? Convert the text, or use a numericupdown control!

 

[BrainSlugs83]

if you're inserting to the database, why are you using a DataSet?

Try something like this:
Dim cxn As SqlClient.SqlConnection
Dim cmd As SqlClient.SqlCommand
cxn = New SqlClient.SqlConnection("server=(local); ... ")
cmd = new SqlClient.SqlCommand("SP_NAME", cxn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@code", SqlDbType.SmallInt).Value = CInt(TextBox4.Text)
cxn.Open()
cmd.ExecuteNonQuery()
cxn.Close()
cxn.Dispose()
cmd.Dispose()