0

How to Run two Update Sql Queries using this Sql Snippet ?

The code mentioned below is updating values only in one table .... i want to update data in two different tables using the code mentioned below :

can anybody reedit this code ?

Try
    Using conn = New SqlConnection(constr)
        Using cmd = conn.CreateCommand()
            conn.Open()
            Dim sql As String = 
               "UPDATE a1_ticket 
                  SET Ticket_no =@ticketNo, 
                  BANK = @bank, 
                  PAID = @paid, 
                  BID = @bid 
                WHERE ITC = @ticketNo"
            cmd.CommandText = sql
            cmd.Parameters.AddWithValue("@bank", Literal20.Text)
            cmd.Parameters.AddWithValue("@paid", Label1.Text)
            cmd.Parameters.AddWithValue("@bid", Literal21.Text)
            cmd.Parameters.AddWithValue("@ticketNo", Literal3.Text)
            cmd.ExecuteNonQuery()
        End Using
    End Using
Catch ex As Exception
    Response.Write(ex.Message)
End Try
1
  • How are these two tables related? Please provide more information on the schema. Are you wanting to update two tables with one UPDATE statement? Commented Jun 1, 2011 at 6:58

5 Answers 5

3

Create a Stored Procedure that updates the two tables and execute it using a StoredProcedure Command...

command.CommandType = CommandType.StoredProcedure;
command.CommandText = "UpdateTheTwoTables";
....
Sign up to request clarification or add additional context in comments.

Comments

0

Modify the SQL statement to update the two tables.

Comments

0

Using a Stored Procedure is the cleanest way code wise. If you don't feel comfortable doing it like that, I'm sure you can do it like this:


Try
    Using conn = New SqlConnection(constr)
        Using cmd = conn.CreateCommand()
            conn.Open()
            Dim sql As String = "UPDATE a1_ticket SET Ticket_no =@ticketNo, BANK = @bank, PAID = @paid, BID = @bid WHERE ITC = @ticketNo"
            cmd.CommandText = sql
            cmd.Parameters.AddWithValue("@bank", Literal20.Text)
            cmd.Parameters.AddWithValue("@paid", Label1.Text)
            cmd.Parameters.AddWithValue("@bid", Literal21.Text)
            cmd.Parameters.AddWithValue("@ticketNo", Literal3.Text)
            cmd.ExecuteNonQuery()
        End Using
//
        Using cmd = conn.CreateCommand()
            conn.Open()
            Dim sql As String = "UPDATE a2_ticket SET Ticket_no =@ticketNo, BANK = @bank, PAID = @paid, BID = @bid WHERE ITC = @ticketNo"
            cmd.CommandText = sql
            cmd.Parameters.AddWithValue("@bank", Literal20.Text)
            cmd.Parameters.AddWithValue("@paid", Label1.Text)
            cmd.Parameters.AddWithValue("@bid", Literal21.Text)
            cmd.Parameters.AddWithValue("@ticketNo", Literal3.Text)
            cmd.ExecuteNonQuery()
        End Using
    End Using
Catch ex As Exception
    Response.Write(ex.Message)
End Try

It's a sketch of what I'm trying to say, you may want to change a few things here and there, but the point is you can just update your two tables one after the other. It's not possible in one update statement afaik.

Comments

0

you can also use

Dim sql As String = @ "Query for first update;

                       Query for second update;";   

Comments

0

Well as you havent said anything about the second table, or the data you're sending it. I havent put this through the compiler to verify it, but the concept I'd suggest would be

You could do:

void UpdateDB(String sql, String[][] params)
{
Try
{
    SqlConnection conn = New SqlConnection(constr);
        SqlCommand cmd = conn.CreateCommand();
            conn.Open();
            cmd.CommandText = sql;
            for(int i=0; i<params.length; i++)
            {
                cmd.Parameters.AddWithValue(params[i,0] params[i,1]);
            }
            cmd.ExecuteNonQuery();
}
Catch (Exception ex)
{
    Response.Write(ex.Message);
}
}

eg send the SQL and the parameters to the function and have it do all the work..

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.