Call MS SQL Server stored procedure from VB.NET

By Allen Stoner

Using a connection and command object to call a MS SQL Server stored procedure is often used to retrieve information from a database. This example calls a stored procedure and supplies several parameters to it. Retrives the value of the "ID" field from the first record retrieved.

    Dim connection As New SqlConnection(connectionString)
    Dim reader As SqlDataReader

    connection.Open()
    Try
      Dim command As New SqlCommand("usp_UpdateDeal", connection)
      command.CommandType = Data.CommandType.StoredProcedure

      With command.Parameters
        .Clear()
        .AddWithValue("@ID", ID)
        .AddWithValue("@WorkStationIP", IP)
        .AddWithValue("@Lastname", LastName)
        .AddWithValue("@Firstname", FirstName)
        .AddWithValue("@MiddleName", MiddleName)
        .AddWithValue("@Data", DealData.OuterXml)
      End With
      reader = command.ExecuteReader()
      reader.Read()
      UpdateDeal = Val(reader("ID").ToString)
      reader.Close()

    Finally
      connection.Close()
    End Try

Call MS SQL Server stored procedure from VB.NET  (1681 Views)