Results 1 to 8 of 8

Thread: Multiple sql statements

  1. #1
    Join Date
    Jul 2006
    Posts
    276
    Rep Power
    0

    Default Multiple sql statements

    Guys is their a way for you to place multiple update statements in one sql statement so that a sqlcommand object can execute it once.

    Here is the code:
    Code:
    Try
    sqlString = "Update tblRates SET Cost = 0.99 WHERE Carrier = 'C&W Land' AND TimeType = 'Peak' "
    
    sqlstring2 = "Update tblRates SET Cost = 0.65 WHERE Carrier = 'C&W Land' AND TimeType = 'OffPeak' "
    
    cmdRates = New OleDbCommand(sqlstring, Conn)
    
    Conn.Open()rowsaffected = cmdRates.ExecuteNonQuery
    
    If rowsaffected <> 1 Then
                    MsgBox("Update Failed", MsgBoxStyle.Critical, "Update")
    Else
                    MsgBox("Update Successful!", MsgBoxStyle.OKOnly, "Update")
    End If
    
    Conn.Close()
    
    Catch ex As Exception
       
    MsgBox(ex.Message, MsgBoxStyle.Critical Or MsgBoxStyle.OKOnly, "Warning")
    
     End Try
    Is their a way to pass the 2 sql strings to cmdrates at the same time.

    What i tried is to see if both statements could be in one?

    Need help!
    Last edited by Arch_Angel; Aug 25, 2007 at 12:00 AM. Reason: added CODE tags
    The greatest discovery of all men through lifetime is that a man can alter his life by altering his attitude!

  2. #2
    keroed1 Guest

    Default

    creating a store procedure that does both and u jus call the store procedure is not an option?

    this was taken from a post done by icymint

    Quote Originally Posted by icymint3 View Post

    Code:
            Dim cmd As New Data.SqlClient.SqlCommand("SELECT COUNT(*) AS RecordCount FROM table1;SELECT * FROM table1;", cnnStr)
    
            Dim reader As Data.SqlClient.SqlDataReader = cmd.ExecuteReader()
    
            If Not reader.Read() Then Return ' no count ... probably should throw exception
    
            Dim recordcount As Integer = reader("RecordCount")
    
            If Not reader.NextResult() Then Return ' no data ... probably should throw exception
    
            ' read the data just as before
    in that sql statement he should how he used two different select statements seperating them by; u should be able to jus do the same for update statements
    Last edited by Arch_Angel; Aug 25, 2007 at 12:06 AM. Reason: merged multiple posts

  3. #3
    Join Date
    Feb 2005
    Posts
    85
    Rep Power
    0

    Default

    Quote Originally Posted by norminator View Post
    Guys is their a way for you to place multiple update statements in one sql statement so that a sqlcommand object can execute it once.
    If you are using MS access then stored procedures wont work. Icymint3 suggesstion should work.

    An alternative is to treat multiple sql statements as one transaction. Therefore all should go through or none should go through. Here is an example:

    Code:
     Dim thisConnection As New OleDBConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;" _
            & "DATA SOURCE=someDatabase")
    
          ' OleDB  Commands
          Dim OleDB1 As String = "Update tblRates SET Cost = 0.99 WHERE Carrier = 'C&W Land' AND TimeType = 'Peak' "
     
    
          Dim OleDB2 As String = "Update tblRates SET Cost = 0.65 WHERE Carrier = 'C&W Land' AND TimeType = 'OffPeak' "
    
    
          ' Create command
          Dim thisCommand As New OleDBCommand(OleDB1, thisConnection)
    
          ' Create Transaction
          Dim thisTransaction As OleDBTransaction
    
          Try
             ' Open Connection
             thisConnection.Open()
    
             ' Begin transaction and attach it to command
             thisTransaction = thisConnection.BeginTransaction()
             thisCommand.Transaction = thisTransaction
    
             ' Run first command
             thisCommand.ExecuteNonQuery()
    
             ' Setup and run second command
             thisCommand.CommandText = OleDB2
             thisCommand.ExecuteNonQuery()
    
             ' Commit transaction
             thisTransaction.Commit()
    
             Console.WriteLine("Transaction Committed.")
    
          Catch ex As Exception
             ' Roll back transaction
             thisTransaction.Rollback()
    
             Console.WriteLine("Transaction rolled back : " & ex.Message)
    
          Finally
             thisConnection.Close()
    
          End Try
    Last edited by Arch_Angel; Aug 25, 2007 at 12:01 AM. Reason: added CODE tags
    Code King aka Code WizZzard: Motivated By The Challenge, Driven By The Will To Succeed.

    In The Land Of Programmers, The Code WizZzard Is KING. Sen on anything VB

  4. #4
    Join Date
    Jul 2006
    Posts
    276
    Rep Power
    0

    Default solution error

    thanx for icymint3's suggestion but that doesn't work. Would the fact that i'm using an Access database be the problem. The statement returns the error characters found after SQL statement
    The greatest discovery of all men through lifetime is that a man can alter his life by altering his attitude!

  5. #5
    Join Date
    Feb 2005
    Posts
    85
    Rep Power
    0

    Default

    Quote Originally Posted by norminator View Post
    thanx for icymint3's suggestion but that doesn't work. Would the fact that i'm using an Access database be the problem. The statement returns the error characters found after SQL statement
    I dont think you can put multiple SQL statements under one named query (with the exception of UNION queries. Or queries within queries) in Ms Access unless you use ODBC (bypassing the MS libraries). The ODBC driver itself will execute each statement in turn and throw query results back at you in the order in which they are produced.

    Try using Transactions as I suggested previously
    Last edited by ToxXxic; Aug 24, 2007 at 11:58 AM.
    Code King aka Code WizZzard: Motivated By The Challenge, Driven By The Will To Succeed.

    In The Land Of Programmers, The Code WizZzard Is KING. Sen on anything VB

  6. #6
    Join Date
    Jul 2006
    Posts
    276
    Rep Power
    0

    Default

    Quote Originally Posted by ToxXxic
    Try using Transactions as I suggested previously
    Worked fine, not exactly what I was expecting but its ok. I wonder why icymints suggestion (sql injection) doesn't work in MS Access!
    Last edited by Arch_Angel; Aug 25, 2007 at 12:02 AM. Reason: fixed quote tag
    The greatest discovery of all men through lifetime is that a man can alter his life by altering his attitude!

  7. #7
    keroed1 Guest

    Default

    Quote Originally Posted by norminator View Post
    Worked fine, not exactly what I was expecting but its ok. I wonder why icymints suggestion (sql injection) doesn't work in MS Access!
    i think the problem is the ; i guess Microsoft Access does not recognize that while sql server does
    Last edited by Arch_Angel; Aug 25, 2007 at 12:02 AM. Reason: fixed quote tag

  8. #8
    Join Date
    Dec 2002
    Posts
    500
    Rep Power
    0

    Default

    Access only allows one statement per query (it may be complex... but only one). but try this instead

    Code:
    Dim OleDB1 As String = "Update tblRates SET Cost = IIf(TimeType = 'Peak' ,0.99, 0.65) WHERE Carrier = 'C&W Land' AND (TimeType = 'Peak' or TimeType = 'OffPeak' )"
    and please dont call the code sql injection, it confuses people who dont know what it is.
    Last edited by icymint3; Sep 11, 2007 at 05:43 PM.
    Cultured in Aggression and Koding like a Warrior!!
    “Common sense is instinct. Enough of it is genius.” - George Bernard Shaw.
    "The significant problems we face cannot be solved by the same level of thinking that created them." - Albert Einstein

Posting Permissions

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