locked
Call a stored procedure RRS feed

  • Question

  • Hi,

    I have written the stored procedure below which works fine when executed. My question is, how do I call this from a Windows application and let the user enter the parameter?

    I am using VB.Net

    Thanks.

    Mark

    CREATE PROCEDURE spReverseMoves

    @RepWeek varchar(4)

    AS

    INSERT INTO Control

    (Client, EnteredDate, RepWeek, IssuedDate, PalletType, LocationCode, SupplierCode, Reference1, Reference2, PalletsOut, PalletsIn, ReverseMove)

    SELECT Client, EnteredDate, RepWeek, IssuedDate, PalletType, LocationCode, SupplierCode, Reference1, Reference2, PalletsIn, PalletsOut, 'r'

    FROM Control AS Control_1

    WHERE RepWeek = @RepWeek
    Friday, July 20, 2007 10:36 AM

Answers

  • HI,

     

    Have the database connection ready. Create a method which will open the DB Connection.

    Make the CommandType as Text. This will allow u to accept the sql command either as Stored procedure / normal direct inline sql command.

    Now use the ExecuteNonQuery and pass the name of the stored procedure and ur parameters to the is method.

     This code snippet provided has been done using C#.

     

    public virtual object ExecuteQueryNonQuery(string queryString)
            {
                int RowsAffected = 0;
                SqlConnection SqlCon = null;
                SqlCommand SqlCmd = null;

                try
                {
                    SqlCon = OpenConnection;
                    SqlCmd = new SqlCommand(queryString, SqlCon);
                    SqlCmd.CommandType = CommandType.Text;
                    SqlCmd.Connection.Open();
                    RowsAffected = SqlCmd.ExecuteNonQuery();
                    return RowsAffected;

                } catch (Exception e)
                {
                    throw e;
                }
                finally
                {
                    if (null != SqlCmd)
                    {
                        SqlCmd.Cancel();
                        SqlCmd = null;
                    }
                    if (null != SqlCon)
                    {
                        SqlCon.Close();
                        SqlCon = null;
                    }
                }
            }

     

    ========================

     SqlDataReader readerParts = (SqlDataReader)ExecuteDataReader("Store Proc Name'" + ProductId + "'");
                    if (readerParts.HasRows)
                    {
                        return readerParts;
                    }
                    else
                    {
                        return null;
                    }

    this code is only for understanding and not for execution / addition in your project.

    Check ADO.NET connection also in wrox .

    Friday, July 20, 2007 11:31 AM
  • Hi,

     

    Here's some code to try:

     

    Code Snippet

    Dim cmd As New SqlClient.SqlCommand

    Using conn As New SqlClient.SqlConnection("ConnectionString")

        

         Try

     

              conn.Open()

              cmd.Connection = conn

              

              cmd.CommandType = CommandType.StoredProcedure

              cmd.CommandText = "StoredProcedureName"

     

             cmd.Parameters.AddWithValue("@Param1", var1)

             cmd.Parameters.AddWithValue("@Param2", var2)        

             cmd.Parameters.AddWithValue("@Param3", var3)

             cmd.Parameters.AddWithValue("@Param4", var4) 

     

             cmd.ExecuteNonQuery()

     

        Catch ex As Exception

     

        End Try

     

       cmd.Dispose()

     

    End Using

     

    Hope this helps.

     

    Matt

    Friday, July 20, 2007 11:36 AM

All replies

  • HI,

     

    Have the database connection ready. Create a method which will open the DB Connection.

    Make the CommandType as Text. This will allow u to accept the sql command either as Stored procedure / normal direct inline sql command.

    Now use the ExecuteNonQuery and pass the name of the stored procedure and ur parameters to the is method.

     This code snippet provided has been done using C#.

     

    public virtual object ExecuteQueryNonQuery(string queryString)
            {
                int RowsAffected = 0;
                SqlConnection SqlCon = null;
                SqlCommand SqlCmd = null;

                try
                {
                    SqlCon = OpenConnection;
                    SqlCmd = new SqlCommand(queryString, SqlCon);
                    SqlCmd.CommandType = CommandType.Text;
                    SqlCmd.Connection.Open();
                    RowsAffected = SqlCmd.ExecuteNonQuery();
                    return RowsAffected;

                } catch (Exception e)
                {
                    throw e;
                }
                finally
                {
                    if (null != SqlCmd)
                    {
                        SqlCmd.Cancel();
                        SqlCmd = null;
                    }
                    if (null != SqlCon)
                    {
                        SqlCon.Close();
                        SqlCon = null;
                    }
                }
            }

     

    ========================

     SqlDataReader readerParts = (SqlDataReader)ExecuteDataReader("Store Proc Name'" + ProductId + "'");
                    if (readerParts.HasRows)
                    {
                        return readerParts;
                    }
                    else
                    {
                        return null;
                    }

    this code is only for understanding and not for execution / addition in your project.

    Check ADO.NET connection also in wrox .

    Friday, July 20, 2007 11:31 AM
  • Hi,

     

    Here's some code to try:

     

    Code Snippet

    Dim cmd As New SqlClient.SqlCommand

    Using conn As New SqlClient.SqlConnection("ConnectionString")

        

         Try

     

              conn.Open()

              cmd.Connection = conn

              

              cmd.CommandType = CommandType.StoredProcedure

              cmd.CommandText = "StoredProcedureName"

     

             cmd.Parameters.AddWithValue("@Param1", var1)

             cmd.Parameters.AddWithValue("@Param2", var2)        

             cmd.Parameters.AddWithValue("@Param3", var3)

             cmd.Parameters.AddWithValue("@Param4", var4) 

     

             cmd.ExecuteNonQuery()

     

        Catch ex As Exception

     

        End Try

     

       cmd.Dispose()

     

    End Using

     

    Hope this helps.

     

    Matt

    Friday, July 20, 2007 11:36 AM