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

[slappy901]

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 :)

USE [EMP_RMS_POS]
GO
/****** Object:  StoredProcedure [dbo].[Serial_Lookup]    Script Date: 12/05/2013 17:51:18 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Serial_Lookup] 
@serialnumber as varchar(20)
AS
BEGIN
    SET NOCOUNT ON;
select  [Transaction].TransactionNumber , Serial.SerialNumber
FROM         Serial INNER JOIN
                      [Transaction] ON Serial.TransactionEntryID = [Transaction].TransactionNumber
where SerialNumber = @serialnumber
END

Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
        Dim cn As New SqlConnection("server=localhost;database=emp_rms_pos;integrated security=true")
        Dim cmd As New SqlCommand
        cmd.Connection = cn
        cn.Open()
        cmd.CommandText = "serial_lookup"
        cmd.CommandType = CommandType.StoredProcedure
        cmd.Parameters.Add(New SqlParameter("transactionnumber", SqlDbType.Int))
        cmd.Parameters("transactionnumber").Direction = ParameterDirection.ReturnValue
        cmd.Parameters.Add(New SqlParameter("@serialnumber", SqlDbType.VarChar, 20)).Value = ComboBox1.Text
        cmd.Parameters("@serialnumber").Direction = ParameterDirection.Input
        Dim result As SqlDataReader = cmd.ExecuteReader
        cn.Close()
        TextBox1.Text = cmd.Parameters("transactionnumber").Value
    End Sub
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.

[Frinavale]

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.

Using cn As New SqlConnection("server=localhost;database=emp_rms_pos;integrated security=true")
  Using cmd As New SqlCommand(cn)
      cmd.CommandText = "serial_lookup"
      cmd.CommandType = CommandType.StoredProcedure
      cmd.Parameters.Add(New SqlParameter("transactionnumber", SqlDbType.Int))
      cmd.Parameters("transactionnumber").Direction = ParameterDirection.ReturnValue
      cmd.Parameters.Add(New SqlParameter("@serialnumber", SqlDbType.VarChar, 20)).Value = ComboBox1.Text
      cmd.Parameters("@serialnumber").Direction = ParameterDirection.Input
 
      cn.Open()
      Dim result As SqlDataReader = cmd.ExecuteReader()
 
      If result.HasRows Then
        While reader.Read()
          TextBox1.Text = result("TransactionNumber")
        End While
      End If
 
  End Using
End Using