Results 1 to 8 of 8
  1. #1
    Join Date
    Aug 2012
    Posts
    32

    Unanswered: I need to know how to execute a stored procedure with multiple parameters in VB

    Here is my stored procedure:


    ALTER PROCEDURE dbo.SP_UpdateFixedRev
    /*
    (
    @parameter1 int = 5,
    @parameter2 datatype OUTPUT
    )
    */
    (
    @Number int,
    @FixedRev money
    )
    AS
    BEGIN
    /* SET NOCOUNT ON */

    Update Ticket set FixedRev = @FixedRev where Number = @Number;



    End


    Here is my code:

    Dim dbConn As New OleDbConnection
    Dim dbComm As OleDbCommand

    dbConn.ConnectionString = connStr 'connStr is class-level vrbl
    dbConn.Open()

    dbComm = dbConn.CreateCommand


    dbComm.Parameters.Add("@Number", OleDbType.Integer).Value = txtDatabaseTicketNo.Text
    dbComm.Parameters.Add("@FixedRev", OleDbType.Currency).Value = txtFixedRev.Text


    dbComm.CommandText = "SP_UpdateFixedRev"
    dbComm.CommandType = CommandType.StoredProcedure
    dbComm.ExecuteNonQuery()
    dbConn.Close()



    However its not updating my database when I run the app from a button click event. I appreciate any help

  2. #2
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    you are probably getting an error on the execute, or connect statements. use Profiler to see exactly what commands are sent to the database, or if this is vb.net, you can use try-catch blocks to captre the errors. If this is plain old VB, you would need to check dbConn.Errors.Count to see if you have errors, and cycle through dbConn.Errors.Items to spit out the error messages.

  3. #3
    Join Date
    Aug 2012
    Posts
    32
    Thanks for the fast reply! I've tested my stored procedure and i know it works properly therefore Im deducing that it is must be somewhere within the vb code. I put in a try catch to try and identify any errors but none are appearing.

  4. #4
    Join Date
    Aug 2012
    Posts
    32
    here is what it looks like now...
    Try
    Dim dbConn As New OleDbConnection
    Dim dbComm As OleDbCommand

    dbConn.ConnectionString = connStr
    dbConn.Open()

    dbComm = dbConn.CreateCommand


    dbComm.Parameters.Add("@Number", OleDbType.Integer).Value = txtDatabaseTicketNo.Text
    dbComm.Parameters.Add("@FixedRev", OleDbType.Currency).Value = txtFixedRev.Text


    dbComm.CommandText = "SP_UpdateFixedRev"
    dbComm.CommandType = CommandType.StoredProcedure
    dbComm.ExecuteNonQuery()
    dbConn.Close()
    Catch exceptionObject As Exception
    MessageBox.Show(exceptionObject.Message)
    End Try



    as far as the connStr goes, i know the connection string is correct because im using the same connection string to search for values within my database and that feature is working properly

  5. #5
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    Do you have access to Profiler, to see what the program is actually sending to the database server?

    On a side note (I am not a .NET developer, so this may be a dumb question). I am not sure you can add a parameter with a value in that way. If I recall, there is a method called AddWithValue for the parameters collection. Also, I am not sure if the OleDbType datatypes could be feeding into this. I am suprised that VB.NET is allowing you to pass the Text values without first having to cast them to Integer and Currency types.

    All of that may work, but seeing what the database server is receiving by looking in Profiler would prove it out.

  6. #6
    Join Date
    Aug 2012
    Posts
    32
    I was finally able to get it working. Thanks for the replies!

  7. #7
    Join Date
    Jan 2003
    Location
    Massachusetts
    Posts
    5,800
    Provided Answers: 11
    So, what finally worked?

  8. #8
    Join Date
    Aug 2012
    Posts
    32
    the last segment of code that I pasted did it. I wasnt realizing that my database was updating, if i remember correctly the issue was with the updates to my textboxes. Which is why I didnt realize the database was updating

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •