VB.NET RSS VB.NET RSS

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

Please support our VB.NET advertiser: Intel Parallel Studio Home
Reply

•
•
Join Date: Jan 2007
Posts: 15
Reputation: quiptro is an unknown quantity at this point 
Solved Threads: 0
quiptro quiptro is offline Offline
Newbie Poster

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

 
0
  #1
Apr 19th, 2007
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
Reply With Quote Quick reply to this message  
•
•
Join Date: Jan 2007
Posts: 15
Reputation: quiptro is an unknown quantity at this point 
Solved Threads: 0
quiptro quiptro is offline Offline
Newbie Poster

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

 
0
  #2
Apr 20th, 2007
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) 
  1. Dim myConnectionString As String = "Data Source=rna040256\sqlexpress;Integrated Security=True"
  2.         Dim myConnection As SqlClient.SqlConnection = New SqlClient.SqlConnection(myConnectionString)
  3.         myConnection.Open()
  4.         Try
  5.             Dim command As SqlClient.SqlCommand = New SqlClient.SqlCommand("SpecialtyInsert", myConnection)
  6.             command.CommandType = CommandType.StoredProcedure
  7.             command.Parameters.AddWithValue("@Skill_ID", TextBox1.Text)
  8.             command.Parameters.AddWithValue("@Skill_Descriptoin", TextBox2.Text)
  9.             command.Parameters.AddWithValue("@Pay_Rate_Per_Hour", TextBox3.Text)
  10.         Finally
  11.             myConnection.Close()
  12.         End Try
Reply With Quote Quick reply to this message  
•
•
Join Date: Oct 2006
Posts: 216
Reputation: jamello is an unknown quantity at this point 
Solved Threads: 6
jamello's Avatar
jamello jamello is offline Offline
Posting Whiz in Training

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

 
0
  #3
Apr 20th, 2007
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) 
  1.  
  2. Public Function ScheduledFlights(ByVal strFlightDate As String) As DataTable
  3. Dim paramSql As New SqlClient.SqlParameter
  4. dtSchedule = New DataTable
  5. 'sets the sproc
  6. commFlights = New SqlClient.SqlCommand("procFlightSchedule")
  7. commFlights.CommandType = CommandType.StoredProcedure
  8. commFlights.Connection = connFlight
  9. paramSql.ParameterName = "@flightDate"
  10. 'pay attention to this detail
  11. paramSql.SqlDbType = SqlDbType.Char
  12. paramSql.Size = 10
  13. paramSql.Value = strFlightDate
  14. commFlights.Parameters.Add(paramSql)
  15. 'runs the sproc
  16. 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.
•
•
•
•
Originally Posted by quiptro View Post
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) 
  1. Dim myConnectionString As String = "Data Source=rna040256\sqlexpress;Integrated Security=True"
  2.         Dim myConnection As SqlClient.SqlConnection = New SqlClient.SqlConnection(myConnectionString)
  3.         myConnection.Open()
  4.         Try
  5.             Dim command As SqlClient.SqlCommand = New SqlClient.SqlCommand("SpecialtyInsert", myConnection)
  6.             command.CommandType = CommandType.StoredProcedure
  7.             command.Parameters.AddWithValue("@Skill_ID", TextBox1.Text)
  8.             command.Parameters.AddWithValue("@Skill_Descriptoin", TextBox2.Text)
  9.             command.Parameters.AddWithValue("@Pay_Rate_Per_Hour", TextBox3.Text)
  10.         Finally
  11.             myConnection.Close()
  12.         End Try
Reply With Quote Quick reply to this message  
•
•
Join Date: Feb 2005
Posts: 1,181
Reputation: hollystyles will become famous soon enough hollystyles will become famous soon enough 
Solved Threads: 67
hollystyles's Avatar
hollystyles hollystyles is offline Offline
Veteran Poster

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

 
0
  #4
Apr 20th, 2007
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.
==========================================
Yadda yadda yadda...
Web junky, fevered monkey
Reply With Quote Quick reply to this message  
•
•
Join Date: Jan 2007
Posts: 15
Reputation: quiptro is an unknown quantity at this point 
Solved Threads: 0
quiptro quiptro is offline Offline
Newbie Poster

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

 
0
  #5
Apr 20th, 2007
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.
Reply With Quote Quick reply to this message  
•
•
Join Date: Jan 2007
Posts: 15
Reputation: quiptro is an unknown quantity at this point 
Solved Threads: 0
quiptro quiptro is offline Offline
Newbie Poster

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

 
0
  #6
Apr 23rd, 2007
•
•
•
•
Originally Posted by quiptro View Post
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.
Reply With Quote Quick reply to this message  
Reply

Quick Reply to Thread
This thread is more than three months old.
Perhaps start a new thread instead?
•
•
•
•
•
•
•
•
Message:
•
•
•
•



Similar Threads
Other Threads in the VB.NET Forum
Thread Tools Search this Thread



.net .net2008 2008 access account add advanced application array basic beginner browser button buttons click code combo cpu cuesent data database datagrid datagridview date datetimepicker designer dissertation dissertations dissertationtopic employees excel exists fade filter forms generatetags html images input intel internet listview mobile module monitor mysql net number objects open panel pdf picturebox picturebox2 port position print printing printpreview problem regex reuse right-to-left save search searchvb.net select serial settings shutdown socket sqldatbase sqlserver storedprocedure survey temperature textbox timer timespan transparency txttoxmlconverter update user usercontol vb vb.net vb.netformclosing()eventpictureboxmessagebox vba vbnet vista visual visualbasic.net visualstudio.net visualstudio2008 web winforms wpf wrapingcode xml year
About Us | Contact Us | Advertise | DaniWeb | Acceptable Use Policy | RSS Feed

©2003 - 2009 DaniWeb® LLC