We help IT Professionals succeed at work.

Execute SQL stored procedure from Button Click Event

bretbel
bretbel asked
on
1,100 Views
Last Modified: 2013-11-26
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"
Comment
Watch Question

Guy Hengel [angelIII / a3]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION
CERTIFIED EXPERT
Commented:
This one is on us!
(Get your first solution completely free - no credit card required)
UNLOCK SOLUTION

Author

Commented:
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]Billing Engineer
CERTIFIED EXPERT
Most Valuable Expert 2014
Top Expert 2009

Commented:
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.
CERTIFIED EXPERT

Commented:
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

Unlock the solution to this question.
Join our community and discover your potential

Experts Exchange is the only place where you can interact directly with leading experts in the technology field. Become a member today and access the collective knowledge of thousands of technology experts.

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.