none
How Do I pass value in a text box to a SQL Stored Procedure?

    Pregunta

  • I posted this in the WinForm section, but then thought it might do better here;
     Hello Everyone,
     I'm using VS2008 pro on xp pro sp2. I'm new to developing.

    I am creating a winform for creating serial numbers.
    Someone else in the sql forum has already given me code to create a stored procedure to generate the numbers. The stored procedure is named fsp_create_sn and is on our sql 2005 server.

    I want the user to be able to put a number into a textbox- say 100- and then click a button that will create 100 serial numbers.
    The code behind the button would actually take the 100 in the text box, pass that value to the stored procedure parameter, then execute the stored procedure which generates the 100 serial numbers.

    Thanks.
    Damon
    lunes, 05 de enero de 2009 17:20

Respuestas

  • Hi Damon.  The following link shoudl help you understand the topic better...

    http://msdn.microsoft.com/en-us/library/yy6y35y8.aspx


    Based on the example there your code might look something like this...

     
        Public Function GetSerialNumbers(ByVal numberRequested As StringAs List(Of String)  
     
            Dim comm As System.Data.SqlClient.SqlCommand = Nothing 
            Dim reader As System.Data.SqlClient.SqlDataReader = Nothing 
            Dim SerialNumbers As New List(Of String)  
     
            Using conn As New System.Data.SqlClient.SqlConnection("<your connection string here>")  
     
                'Assumes the stored proc returns a recordset with a single column of text datatype   
                'and the proc has a single parameter called @AmountRequested.  
     
                comm = New System.Data.SqlClient.SqlCommand("fsp_create_sn", conn)  
                comm.CommandType = CommandType.StoredProcedure  
     
                Dim param1 As New System.Data.SqlClient.SqlParameter()  
                param1.ParameterName = "@AmountRequested" 
                param1.SqlDbType = SqlDbType.Int  
                param1.Direction = ParameterDirection.Input  
                param1.Value = numberRequested  
     
                Try 
                    conn.Open()  
     
                    reader = comm.ExecuteReader  
                    While reader.Read  
                        SerialNumbers.Add(reader.Item(0).ToString)  
                    End While 
     
                Catch ex As Exception  
                    MessageBox.Show(ex.Message)  
                Finally 
                    comm.Dispose()  
                    reader = Nothing 
                End Try 
                conn.Open()  
     
            End Using  
     
            Return SerialNumbers  
        End Function 
     
     

    I hope this helps.
    • Marcado como respuesta Xingwei Hu lunes, 12 de enero de 2009 8:05
    lunes, 05 de enero de 2009 18:51