By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
444,219 Members | 2,242 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 444,219 IT Pros & Developers. It's quick & easy.

Sending Parameter to Stored Procedure from ComboBox and getting the returned data fro

P: 1
I have written a little vb code to try to run a stored procedure, take the parameter @serialnumber and feed it to the S-proc. Then take the field transactionnumber and populate textbox1.text with it on the form. Simple task, but I am a newb to this. Any help is appreciated. please find Sproc and vb form code below. Currently, it returns a 0 in the text box. I've tested against sql running the sproc and get expected data. So the sproc isn't the issue. It's my gimpy ability to write code lol. If there is a better language to do this in, please feel free to chime in. I'm not a sissy when it comes to criticism :)

Expand|Select|Wrap|Line Numbers
  1. USE [EMP_RMS_POS]
  2. GO
  3. /****** Object:  StoredProcedure [dbo].[Serial_Lookup]    Script Date: 12/05/2013 17:51:18 ******/
  4. SET ANSI_NULLS ON
  5. GO
  6. SET QUOTED_IDENTIFIER ON
  7. GO
  8. ALTER PROCEDURE [dbo].[Serial_Lookup] 
  9. @serialnumber as varchar(20)
  10. AS
  11. BEGIN
  12.     SET NOCOUNT ON;
  13. select  [Transaction].TransactionNumber , Serial.SerialNumber
  14. FROM         Serial INNER JOIN
  15.                       [Transaction] ON Serial.TransactionEntryID = [Transaction].TransactionNumber
  16. where SerialNumber = @serialnumber
  17. END
Expand|Select|Wrap|Line Numbers
  1. Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
  2.         Dim cn As New SqlConnection("server=localhost;database=emp_rms_pos;integrated security=true")
  3.         Dim cmd As New SqlCommand
  4.         cmd.Connection = cn
  5.         cn.Open()
  6.         cmd.CommandText = "serial_lookup"
  7.         cmd.CommandType = CommandType.StoredProcedure
  8.         cmd.Parameters.Add(New SqlParameter("transactionnumber", SqlDbType.Int))
  9.         cmd.Parameters("transactionnumber").Direction = ParameterDirection.ReturnValue
  10.         cmd.Parameters.Add(New SqlParameter("@serialnumber", SqlDbType.VarChar, 20)).Value = ComboBox1.Text
  11.         cmd.Parameters("@serialnumber").Direction = ParameterDirection.Input
  12.         Dim result As SqlDataReader = cmd.ExecuteReader
  13.         cn.Close()
  14.         TextBox1.Text = cmd.Parameters("transactionnumber").Value
  15.     End Sub
  16. End Class
This has to be something silly and stupid :) Any help is much appreciated guys. My background is infrastructure (Servers/Networking for 20+ years) So this is new ground for me.
Dec 6 '13 #1
Share this Question
Share on Google+
1 Reply


Frinavale
Expert Mod 5K+
P: 9,731
You called the ExecuteReader command but you failed to actually read the results.

Check out the MSDN documentation for the ExecuteReader method.

You should also consider using "using blocks" to manage the cleanup of your connections etc.
Expand|Select|Wrap|Line Numbers
  1. Using cn As New SqlConnection("server=localhost;database=emp_rms_pos;integrated security=true")
  2.   Using cmd As New SqlCommand(cn)
  3.       cmd.CommandText = "serial_lookup"
  4.       cmd.CommandType = CommandType.StoredProcedure
  5.       cmd.Parameters.Add(New SqlParameter("transactionnumber", SqlDbType.Int))
  6.       cmd.Parameters("transactionnumber").Direction = ParameterDirection.ReturnValue
  7.       cmd.Parameters.Add(New SqlParameter("@serialnumber", SqlDbType.VarChar, 20)).Value = ComboBox1.Text
  8.       cmd.Parameters("@serialnumber").Direction = ParameterDirection.Input
  9.  
  10.       cn.Open()
  11.       Dim result As SqlDataReader = cmd.ExecuteReader()
  12.  
  13.       If result.HasRows Then
  14.         While reader.Read()
  15.           TextBox1.Text = result("TransactionNumber")
  16.         End While
  17.       End If
  18.  
  19.   End Using
  20. End Using
Dec 10 '13 #2

Post your reply

Sign in to post your reply or Sign up for a free account.