CODEWITHSUNDEEP
sql.netsql-servervb.netado.net
neeko
neeko

Reputation: 2000

Execute two INSERT statements

I have two seperate INSERT statements that I want to run one after the other, the first one runs successfully but it seems like it isn't even getting to the second statement any idea why?

Try
    con.Open()
    cmd.ExecuteNonQuery()
    resultSQL.ForeColor = Drawing.Color.Green
    resultSQL.Text = "Successfully Saved"

Catch ex As Exception
    Response.Write(ex.Message)
    resultSQL.ForeColor = Drawing.Color.Red
    resultSQL.Text = ex.Message

Finally

End Try

INSERT 2 which is not running:

strQuery = "INSERT INTO [SD_EPOS_Entry]" _
    & " ([Trade_Activity_Code] " _
    & " VALUES " _
    & " (@Trade_Activity_Code1)"

cmd = New SqlCommand(strQuery)

cmd.Parameters.AddWithValue("@Trade_Activity_Code1", tcodeVar)

Try

    con.Open()
    cmd.ExecuteNonQuery()
    resultSQL.ForeColor = Drawing.Color.Green
    resultSQL.Text = "Successfully Saved2"

Catch ex As Exception

    Response.Write(ex.Message)
    resultSQL.ForeColor = Drawing.Color.Red
    resultSQL.Text = ex.Message

Finally

Upvotes: 0

Views: 120

Answers (2)

Crono
Crono

Reputation: 10478

Right off the bat there seems to be a missing parenthesis in your second query:

strQuery = "INSERT INTO [SD_EPOS_Entry]" _
        & " ([Trade_Activity_Code] " _
        & " VALUES " _
        & " (@Trade_Activity_Code1)"

It should be:

strQuery = "INSERT INTO [SD_EPOS_Entry]" _
        & " ([Trade_Activity_Code]) " _
        & " VALUES " _
        & " (@Trade_Activity_Code1)"

I would also advise for proper disposal of any SqlCommand and SqlConnection instances you create, either by using the Dispose() method or declaring them in a Using statement.

If as you say your second query isn't even reached, follow your code at runtime with a debugger and look at which point it stops. That ougth to provide some clue.

UPDATE:

This should execute both your INSERT statements in a single operation:

strQuery = "INSERT INTO [SD_T_Code]" _
    & " ([Trade_Activity_Code]) " _
    & " VALUES " _
    & " (@Trade_Activity_Code)" _
    & ";INSERT INTO [SD_EPOS_Entry]" _
    & " ([Trade_Activity_Code]) " _
    & " VALUES " _
    & " (@Trade_Activity_Code)"

Dim strMessage As String = "Successfully saved"
Dim resultColor As Drawing.Color = Drawing.Color.Green

Using cmd = New SqlCommand(strQuery, con)
    cmd.Parameters.AddWithValue("@Trade_Activity_Code", tcodeVar)

    Try
        con.Open()
        cmd.ExecuteNonQuery()
        resultSQL.ForeColor = Drawing.Color.Green
        resultSQL.Text = "Successfully Saved"
    Catch ex As SqlException
        strMessage = ex.Message
        resultColor = Drawing.Color.Red
    Finally
        con.Close()
    End Try
End Using

// Write results outside of db operation
Response.Write(strMessage)
resultSQL.ForeColor = resultColor
resultSQL.Text = strMessage

Of course the above assumes there's an instanciated, unopen con connection variable present.

Notice that it also only catches SqlException. Catching the basic Exception here seems wrong to me.

SECOND UPDATE:

If the above code isn't working, the issue may be caused by an Sql Trigger.

Upvotes: 1

Hobbes
Hobbes

Reputation: 147

Somewhere not shown in your code, you are assigning the connection to the command object (this is for the first query).

When you get to the second query, you're redefining the cmd object as a new object, which is also removing any reference to the connection as established earlier.

Try this for your second block of code:

strQuery = "INSERT INTO [SD_EPOS_Entry]" _
    & " ([Trade_Activity_Code] " _
    & " VALUES " _
    & " (@Trade_Activity_Code1)"

cmd = New SqlCommand(strQuery, cnn) 'Define the connection to be used for the command.

Upvotes: 0

Related Questions