asked on

Execute SQL stored procedure from Button Click Event

1) Please I need to execute a SQL stored procedure using the "Button_Click" event in asp vb code page. The stored procedure has no parameters, it just updates SQL tables. The connection string is already contained in the web.config file.
2) I also want to display in text box that process was completed successfully. ie: "Completed"

SOLUTION

Guy Hengel [angelIII / a3]

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER CERTIFIED SOLUTION

TheMegaLoser

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

bretbel

ASKER

related question to your solutions above. I want to capture the MAX value from a SELECT statement from the table that was UPDATED in the code you've provided. How would I capture that and display it in textbox? Looks like I can use the oCMD to do so but dont know the syntax.

thanks again.

Guy Hengel [angelIII / a3]

the procedure has to either return a recordset, or provide a output parameter so you can get the value.
in the first case, use oCmd.ExecuteReader() with then returns the SqlReader object, in the second case, you have to set up a SqlParameter object (before calling ExecuteNonQuery()), and collect the value after the Execute.

TheMegaLoser

If you change the stored procedure to select just one value you can do this easily.

For example, if we have this proc:
CREATE PROCEDURE [dbo].[testproc]
AS
SELECT MAX(PRICE) FROM items

You can then just do this in .net:
Try
Dim oConn As New SqlClient.SqlConnection
Dim oCmd As New SqlClient.SqlCommand
oConn.ConnectionString = ConfigurationManager.ConnectionStrings("myconnstring").ConnectionString
oConn.Open()
oCmd.Connection = oConn
oCmd.CommandText = "mystoredproc"
oCmd.CommandType = CommandType.StoredProcedure
textbox1.text = "Success: " + oCmd.ExecuteScalar
oConn.Close()
Catch ex As Exception
textbox1.text = "Failure: " + ex.Message
End Try