Execute Stored Procedure From Form

[MJV]

I have created and tested a stored procedure that works in sql. This stored procedure has one parameter that it requires to run "quoteno" . I already have the database attached to the form. Can anyone help me with the code for this. The name of my stored procedure is EstimateApproval and my database name is Estimator.
Thanks. Ive looked for answers on the net but not having any luck

[LizR]

Please search this forum, this has been answered a number of times before.

[rapture]

There and you can check here
http://support.microsoft.com/kb/320916

[MJV]

Please search this forum, this has been answered a number of times before.

This is what i have tried. I get no errors but the database is not changing as per the stored procedure which does work when run within sql.

[MJV]

Please search this forum, this has been answered a number of times before.

This is what i have tried. I get no errors but the database is not changing as per the stored procedure which does work when run within sql.

private void approveestBN_Click(object sender, EventArgs e)
{

int @estno;
int.TryParse(startestnoCB.Text,out estno);


SqlCommand cmd = new SqlCommand();
cmd.CommandText = "EstimateApproval";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@estno", SqlDbType.Int);
}

[rapture]

I guess my replies are either completely wrong or not worth looking at - seems to me on that link I posted was this

//Create a connection to the SQL Server; modify the connection string for your environment
			//SqlConnection MyConnection = new SqlConnection("server=(local);database=pubs;Trusted_Connection=yes");
			SqlConnection MyConnection = new SqlConnection("server=(local);database=pubs;UID=myUser;PWD=myPassword;");

			// Create a Command object, and then set the connection.
			// The following SQL statements check whether a GetAuthorsByLastName  
			// stored procedure already exists.
			SqlCommand MyCommand = new SqlCommand("select * from sysobjects where id = object_id(N'GetAuthorsByLastName')" +
			"  and OBJECTPROPERTY(id, N'IsProcedure') = 1", MyConnection);

			// Set the command type that you will run.
			MyCommand.CommandType = CommandType.Text;

			// Open the connection.
			MyCommand.Connection.Open();

			// Run the SQL statement, and then get the returned rows to the DataReader.
			SqlDataReader MyDataReader = MyCommand.ExecuteReader();

maybe you have some of this stuff other places, like the connection etc.

If I'm way off, please ignore me as I'm still fairly new.

Did you step through it to see what happens?

[JerryShaw]

This is what i have tried. I get no errors but the database is not changing as per the stored procedure which does work when run within sql.

private void approveestBN_Click(object sender, EventArgs e) {

int @estno; int.TryParse(startestnoCB.Text,out estno); SqlCommand cmd = new SqlCommand(); cmd.CommandText = "EstimateApproval"; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@estno", SqlDbType.Int); }

Where is your SqlConnection assignment to the SqlCommand ?
Where is your value assignment to the cmdParameter ?
Where is your Execute command on cmd ?

[MJV]

If the form is attached to the database through table adapters do I have to make another SqlConnection assignment?

[MJV]

I have tried this but Im now getting an error saying that "Format of the initialization string does not conform to specification starting at index 0."

private void approveestBN_Click(object sender, EventArgs e)
{

int @estno;
int.TryParse(startestnoCB.Text,out estno);

SqlCommand cmd = new SqlCommand();
SqlConnection conn = new SqlConnection();
conn.ConnectionString = "estimateDataSet";
cmd.Connection = conn;
cmd.CommandText = "EstimateApproval";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.Add("@estno", SqlDbType.Int);
cmd.ExecuteReader();
}

[JerryShaw]

Okay, I see you are getting nowhere, so I will give you the code to do this:

private void DoSomething()
        {
            string ConnectionString = "Data Source={0};Initial Catalog={1};Integrated Security=True";

            int estno; 
            if( int.TryParse(startestnoCB.Text,out estno) )
            {
                SqlConnection conn = new SqlConnection( 
                    string.Format(ConnectionString,".//SqlExpress" // Your Sql Server
                    , "EstimateDataSet" // Your SQL Database
                    ));

                SqlCommand cmd = new SqlCommand("EstimateApproval", conn);
                cmd.CommandType = CommandType.StoredProcedure;
                cmd.Parameters.AddWithValue("@Estno", estno);
                try
                {
                    conn.Open();
                    #if WantSomethingBack
                    DataTable result = new DataTable();
                    SqlDataAdapter adapter = new SqlDataAdapter(cmd);
                    adapter.Fill(result);
                    foreach(DataRow row in result.Rows)
                    {

                    }
                    result.Dispose()
                    #else
                    cmd.ExecuteNonQuery();  // If you do not need a return;
                    #endif
                }
                catch (SqlException err)
                {
                    MessageBox.Show(err.Message);
                }
                finally
                {
                    if(conn.State == ConnectionState.Open)
                        conn.Close();
                    conn.Dispose();
                }
            }
        }

[MJV]

Thanks so much for hanging in with me. I now get an error that I cannot log into the database. I know that the solution already logs in with integrated security and I'm able to manipulate data in the data base on the form. Now when I try to execute the stored procedure through the database connection you gave me I'm not able to connect. Could it be because im already connected???

[JerryShaw]

No you have have many connections.
Can you provide your exact connection string that you are using.

// Jerry
Sorry for the delay, I tried sending you a reply hours ago from a different PC, but for whatever reason, it did not show up here.

[MJV]

Jerry I have been fumbling with this code you gave me for days and I cannot figure out how to change it to get the return value @quotenum from the sql stored procedure. Can you help me please.

[JerryShaw]

Are talking about the @quotenum as an Sql Parameter to the stored procedure ?
The stored procedure must declare this as an OUTPUT parameter.
Then you set your SqlParameter Direction so that it also knows it is an OUTPUT type and will store the value in the parameter.Value upon return.

Hope that helps