How to connect a SQL stored procedure to a windows application?

[quiptro]

Hi,

I have created a stored procedure that would insert new records in one of my database tables. I have tested the procedure in SQL Server 2005 and it works all right. What I want to do next is to connect the stored procedure to my form in VB.Net so that I can input new records using the form. In the form I have 3 text boxes (for the 3 atributes in the table) and a button that would call the procedure (therefore assigning the values from the text boxes as a new record with 3 atributes in my table). I have already created a BindingSource and DataSet that refer to the stored procedure. How can I call the procedure and make it take the values of the 3 textboxes by clicking the button?

Thanks in advance,
Robert

[quiptro]

Hi,

I did some reading and I came up with this code that would connect to the SQL stored procedure (SpecialtyInsert) from my VB.Net form, however, it doesn't seem to work. It doesn't give me any errors, it just does not assign the parameters as values for the new record. Could somebody help me locate the problem? Thanks.

Here is the code:

Dim myConnectionString As String = "Data Source=rna040256\sqlexpress;Integrated Security=True"
        Dim myConnection As SqlClient.SqlConnection = New SqlClient.SqlConnection(myConnectionString)
        myConnection.Open()
        Try
            Dim command As SqlClient.SqlCommand = New SqlClient.SqlCommand("SpecialtyInsert", myConnection)
            command.CommandType = CommandType.StoredProcedure
            command.Parameters.AddWithValue("@Skill_ID", TextBox1.Text)
            command.Parameters.AddWithValue("@Skill_Descriptoin", TextBox2.Text)
            command.Parameters.AddWithValue("@Pay_Rate_Per_Hour", TextBox3.Text)
        Finally
            myConnection.Close()
        End Try

[jamello]

howdy there!
The code listing you rendered did not take cognisance of the data type being sent to the sprocs.
See the example below direct from my code and it works and I hope it helps you.

This function takes one string parameter and returns a datatable

Public Function ScheduledFlights(ByVal strFlightDate As String) As DataTable
Dim paramSql As New SqlClient.SqlParameter
dtSchedule = New DataTable
'sets the sproc
commFlights = New SqlClient.SqlCommand("procFlightSchedule")
commFlights.CommandType = CommandType.StoredProcedure
commFlights.Connection = connFlight
paramSql.ParameterName = "@flightDate"
'pay attention to this detail
paramSql.SqlDbType = SqlDbType.Char
paramSql.Size = 10
paramSql.Value = strFlightDate
commFlights.Parameters.Add(paramSql)
'runs the sproc
adpt_Flight2.Fill(dtSchedule)

The datatype being set in your form must be equal to the datatype of the parameter of the sproc. It is fundamental or else it won't work.
Try it and get back to me.

Hi,

I did some reading and I came up with this code that would connect to the SQL stored procedure (SpecialtyInsert) from my VB.Net form, however, it doesn't seem to work. It doesn't give me any errors, it just does not assign the parameters as values for the new record. Could somebody help me locate the problem? Thanks.

Here is the code:

Dim myConnectionString As String = "Data Source=rna040256\sqlexpress;Integrated Security=True"
        Dim myConnection As SqlClient.SqlConnection = New SqlClient.SqlConnection(myConnectionString)
        myConnection.Open()
        Try
            Dim command As SqlClient.SqlCommand = New SqlClient.SqlCommand("SpecialtyInsert", myConnection)
            command.CommandType = CommandType.StoredProcedure
            command.Parameters.AddWithValue("@Skill_ID", TextBox1.Text)
            command.Parameters.AddWithValue("@Skill_Descriptoin", TextBox2.Text)
            command.Parameters.AddWithValue("@Pay_Rate_Per_Hour", TextBox3.Text)
        Finally
            myConnection.Close()
        End Try

[hollystyles]

Havung set up your command object and added the parameters, you have neglected to call command.ExecuteNonQuery() to actually execute the procedure.

Do take note on Jamello about datatypes. The data in your textboxes are all strings. We don't know what your Sql Server table columns datatype is (you don't tell us) but if they're not char, varchar or nvarchar you are going to get an error.

[quiptro]

Hi,

Thanks for the help. I added command.ExecuteNonQuery() to the code and, surprisingly, it works. I guess the conversion of the data types is made automatically (the datatypes of the atributes are char(10), varchar(50) and int). However, it doesnot update the table with the new record until I restart the form. I tried using DataGridView1.Update() but it doesn't do the job, the record doesn't show in the table until I restart the form (I am also refreshing the datagrid, nothing happens). How can I update the table without restarting the form? ex. Just loading the table into the datagrid again during runtime (I have a button for that).

Thanks for the help so far.

[quiptro]

Hi,

Thanks for the help. I added command.ExecuteNonQuery() to the code and, surprisingly, it works. I guess the conversion of the data types is made automatically (the datatypes of the atributes are char(10), varchar(50) and int). However, it doesnot update the table with the new record until I restart the form. I tried using DataGridView1.Update() but it doesn't do the job, the record doesn't show in the table until I restart the form (I am also refreshing the datagrid, nothing happens). How can I update the table without restarting the form? ex. Just loading the table into the datagrid again during runtime (I have a button for that).

Thanks for the help so far.

I solved the problem by adding TableAdapter.Fill() (for the table I was trying to update) in the Button code and it works great.