Ad:

VB.NET Discussion Thread
Unsolved
Views: 13875

Apr 19th, 2007

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

Expand Post »

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

Similar Threads

How to display a table on windows application in c# in C#
Simple ASP.Net Login Page (Using VB.Net) in ASP.NET
Stored procedure call with ADO in C

quiptro

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

18 posts
since Jan 2007

Permalink

Apr 20th, 2007

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

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:

VB.NET Syntax (Toggle Plain Text)
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
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

quiptro

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

18 posts
since Jan 2007

Permalink

Apr 20th, 2007

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

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

VB.NET Syntax (Toggle Plain Text)
 
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)
 
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.

Click to Expand / Collapse Quote originally posted by 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:
VB.NET Syntax (Toggle Plain Text)
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
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

Reputation Points: 215

Solved Threads: 6

Posting Whiz in Training

Offline

219 posts
since Oct 2006

Permalink

Apr 20th, 2007

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

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.

hollystyles

Reputation Points: 262

Solved Threads: 68

Veteran Poster

Offline

1,181 posts
since Feb 2005

Permalink

Apr 20th, 2007

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

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

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

18 posts
since Jan 2007

Permalink

Apr 23rd, 2007

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

Click to Expand / Collapse Quote originally posted by 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.

quiptro

Reputation Points: 10

Solved Threads: 0

Newbie Poster

Offline

18 posts
since Jan 2007

This thread is more than three months old

No one has posted to this discussion for at least three months. Please let old threads die and do not reply to them unless you feel you have something new and valuable to contribute that absolutely must be added to make the discussion complete. Otherwise, please start a new thread in this forum instead.

Previous Thread in VB.NET Forum Timeline: Searching through a Directory including all subs

Next Thread in VB.NET Forum Timeline: Q&A VB.NET and Truncated Email - 2 Examples

DaniWeb Message

Cancel Changes

User Name
Password