Sql Server Stored Procedure and Execute in VB.NET

SQL Server stored procedure and execute in VB.NET

Supposing you have this sproc in sqlserver

CREATE PROCEDURE GetNameAddress(@custID nvarchar(10))
as
BEGIN
SELECT NAME,ADDRESS FROM CUSTOMER WHERE IDCUSTOMER = @custID;
END

you call it and get the result in the standard way

' GetConnection is a method that creates and return the '
' SqlConnection used here according to your connection string'
Using cn = GetConnection()
   cn.Open()

   ' Create the command with the sproc name and add the parameter required'
   Dim cmd As SqlCommand = new SqlCommand("GetNameAddress", cn)
   cmd.CommandType = CommandType.StoredProcedure
   cmd.Parameters.AddWithValue("@custID", "DS212")

   ' Ask the command to create an SqlDataReader on the result of the sproc'
   Using r = cmd.ExecuteReader()

       ' If the SqlDataReader.Read returns true then there is a customer with that ID'
       if r.Read() then

           ' Get the first and second field frm the reader'
           lblName.Text = r.GetString(0)
           lblAddress.Text = r.GetString(1)
       end if
   End Using
End using

Notice that this is the standard approach when you expect zero or one record returned by the sproc. If you have more than one record then you use a while loop over the SqlDataReader.Read method and you should provide the controls where store the returned records.

How to Execute a Stored Procedure on VB.NET

You need the following code for executing a stored procedure

Imports System.Data.SqlClient

Dim conn as New SqlConnection(YourConnectionString)
Dim cmd as SqlCommand = conn.createcommand
conn.open()
cmd.CommandType = CommandType.StoreProcedure
cmd.Parameters.Add(New SqlParameter("@OE", YourValue)
cmd.CommandText = DeletetblOfficeEquipmentProfileRecord
cmd.ExecuteNonQuery() 
conn.close()

Visual Basic 2019: call SQL Server stored procedure and store return value

Assuming the stored procedure has a single output parameter and returns no resultsets. Just run

Dim respondent_count_Command = New SqlCommand
respondent_count_Command.Connection = conn
respondent_count_Command.CommandType = CommandType.StoredProcedure

' Declare output parameter
Dim param_count = New SqlParameter()
param_count.ParameterName = "@Count"
param_count.SqlDbType = SqlDbType.Int
param_count.Direction = ParameterDirection.Output
respondent_count_Command.Parameters.Add(param_count)

' Execute the procedure
respondent_count_Command.CommandText = "[target_db].dbo.[p_XPR_ExcelExporting_Ansbooks_Count]"

respondent_count_Command.ExecuteNonQuery()

AnsBookCount = param_count.Value

Execute a stored procedure using VB.NET

The code and stored procedure you posted appear to be in sync.

That strongly suggests that the stored procedure in the Database is out of sync with what you expect in your code. Suggest you check what you posted is actually the stored procedure in DB.

Also Note: you should not prefix your proc with 'sp_' unless it natually resides in master.

Executing a Stored Procedure from SQL Server in VB.NET?

Dim Command As New SQLCommand("usp_MyStoredProc", connection)
Command.CommandType = CommandType.StoredProcedure
Command.ExecuteNonQuery()

Calling SQL Server stored procedure from VB.NET application. Output parameter is coming back blank

You missed '@' in the parameter name in the SP. It should be

select @numberPotentialMatches = count(*) ...

What you did was selecting count(*) and setting numberPotentialMatches as column alias.

Also, don't access the SqlCmd after it's disposed.

Return values from SQL Server stored procedure

You didn't show us your stored procedure code - but most likely, it's something like this:

CREATE PROCEDURE dbo.dbo.TWEEPush_ValidateCO
    @CoFilter VARCHAR(100),   -- just guessing here!
    @TDate DATE               -- again - just guessing 
AS
BEGIN
    DECLARE @TempCOID INT;
    DECLARE @TempDate DATE;

    -- do some calculations here that define @TempCOID and @TempDate
    -- this is just for demo purposes - I'm sure your calculation is a bit
    -- more complex and involved...
    SET @TempCOID = 1502;
    SET @TempDate = '20170910';

    -- return the values in a SELECT statement
    SELECT 
        @TempCOID AS N'@TempCOID',  
        @TempDate AS N'@TempDate'
END

In this case, you're returning a RESULT SET - not two parameters!

You need to get your values like this:

static void Main(string[] args)
{
    string connectionString = ConfigurationManager.ConnectionStrings["YourConnectionString"].ConnectionString;

    int tempCoid;
    DateTime tempDate;

    using (SqlConnection conn = new SqlConnection(connectionString))
    using (SqlCommand cmd = new SqlCommand("dbo.dbo.TWEEPush_ValidateCO", conn))
    {
        cmd.CommandType = CommandType.StoredProcedure;

        cmd.Parameters.AddWithValue("@CoFilter", Filter);
        cmd.Parameters.AddWithValue("@TDate", DateTime.Now);

        conn.Open();

        using (var rdr = cmd.ExecuteReader())
        {
            while (rdr.Read())
            {
                tempCoid = rdr.GetFieldValue<int>(0);
                tempDate = rdr.GetFieldValue<DateTime>(1);
            }
        }

        conn.Close();
    }
}

(I'm sure you get the idea - and you can easily translate this into VB.NET, too).

Hope that helps!

Marc

VB.net getting SQL Server stored procedure results

Dim accntStat As Integer
Dim loginCnt As Integer
Dim sqlConnct As New classSQLConnection
Using sqlConnct.MSSQLConn, _
      cmd As New SqlCommand("csp_LoginAuthentication", sqlConnct.MSSQLConn)         

    cmd.CommandType = CommandType.StoredProcedure
    cmd.Parameters.Add("@employeeid", SqlDbType.Int).Value = txtEmployeeID.Text
    cmd.Parameters.Add("@password", SqlDbType.NVarChar, 40).Value = txtPassword.Text

    cmd.Parameters.Add("@accountstatus", SqlDbType.Int).Direction = ParameterDirection.InputOutput
    cmd.Parameters.Add("@logincount", SqlDbType.Int).Direction = ParameterDirection.InputOutput

    sqlConnct.MSSQLConn.Open()
    cmd.ExecuteNonQuery()

    accntStat = CInt(cmd.Parameters("@accountstatus").Value)
    loginCnt = CInt(cmd.Parameters("@logincount").Value)

End Using
MsgBox(accntStat)

I think that fixes several issues, including: output parameters and setting their direction, a password parameter set as an integer that's obviously a string of some kind, and failing to close the connection in the case of an exception.

---

I also have a serious concern about the original code: it sure looks like there's a plain-text password there. You just don't do that. This is not okay, and needs to be fixed immediately.

Execute stored procedure with vb.net

You should have something like this

Private Sub btnSearch_Click(sender As Object, e As EventArgs) Handles btnSearch.Click
            cn.Open()

            Using cmd As New SqlClient.SqlCommand("dbo.uspSearch", cn)
                cmd.Parameters.AddWithValue("@SurName", txtSearch.Text)
                cmd.CommandType=CommandType.StoredProcedure
                dt.Clear()
                da.Fill(dt)
                dgv1.RowTemplate.Height = 50
                dgv1.DataSource = dt
                For i As Integer = 0 To dgv1.Columns.Count - 1
                    If TypeOf dgv1.Columns(i) Is DataGridViewImageColumn Then
                        DirectCast(dgv1.Columns(i), DataGridViewImageColumn).ImageLayout = DataGridViewImageCellLayout.Stretch
                    End If
                Next
            End Using
            cn.Close()
       End Sub

UPDATE

You can rewrite

Dim cmd As New SqlCommand()
Dim da As New SqlDataAdapter()
Dim dt As New DataTable()
Try
    cn.Open()
    cmd = New SqlCommand("uspSearch", cn)
    cmd.Parameters.AddWithValue("@SurName", txtSearch.Text)
    cmd.CommandType = CommandType.StoredProcedure
    da.SelectCommand = cmd
    da.Fill(dt)
    dgv1.DataSource = dt
Catch x As Exception
    MessageBox.Show(x.GetBaseException().ToString(), "Error", MessageBoxButtons.OK, MessageBoxIcon.[Error])
Finally
    cmd.Dispose()
    cn.Close()
End Try

---

Related Topics