why is this sql command executed twice?

Discussion in 'ASP .Net' started by Bob, Feb 21, 2007.

  1. Bob

    Bob Guest

    Hi,

    i wrote code for inserting data into a table, but it runs twice. If i remove
    the line: "comd.ExecuteNonQuery()", then it runs once; but i thought that
    line was necessary for executing the sql command ... if i remove "
    oConnection.Open()", then error: "connection is not open".

    see my code:
    Dim oConnection As SqlConnection
    Dim comd As SqlCommand
    Dim sConnectionString As String
    Dim sql, na As String
    Dim iden As Integer

    sConnectionString =
    ConfigurationManager.ConnectionStrings("myconn").ConnectionString.ToString()
    sql = "INSERT INTO table(...) VALUES (...);"
    oConnection = New SqlConnection(sConnectionString)
    comd = New SqlCommand(sql, oConnection)
    comd.Parameters.Add("@var1", SqlDbType.NVarChar, 10).value="ok"
    oConnection.Open()
    comd.ExecuteNonQuery()

    Thanks
    Bob
    Bob, Feb 21, 2007
    #1
    1. Advertising

  2. Bob wrote:
    > Hi,
    >
    > i wrote code for inserting data into a table, but it runs twice. If i remove
    > the line: "comd.ExecuteNonQuery()", then it runs once; but i thought that
    > line was necessary for executing the sql command ... if i remove "
    > oConnection.Open()", then error: "connection is not open".
    >
    > see my code:
    > Dim oConnection As SqlConnection
    > Dim comd As SqlCommand
    > Dim sConnectionString As String
    > Dim sql, na As String
    > Dim iden As Integer
    >
    > sConnectionString =
    > ConfigurationManager.ConnectionStrings("myconn").ConnectionString.ToString()
    > sql = "INSERT INTO table(...) VALUES (...);"
    > oConnection = New SqlConnection(sConnectionString)
    > comd = New SqlCommand(sql, oConnection)
    > comd.Parameters.Add("@var1", SqlDbType.NVarChar, 10).value="ok"
    > oConnection.Open()
    > comd.ExecuteNonQuery()
    >
    > Thanks
    > Bob
    >
    >


    That code only runs the query once. What does the rest of the code do?

    Also, I see that you edited out part of the SQL query. Anything else you
    edited out?

    --
    Göran Andersson
    _____
    http://www.guffa.com
    =?ISO-8859-1?Q?G=F6ran_Andersson?=, Feb 22, 2007
    #2
    1. Advertising

  3. Bob

    Bob Guest

    Thanks for replying.

    There is indeed more code: here is the whole code:
    --------------------------------------------------
    Dim trans As SqlTransaction = Nothing 'new
    Dim oConnection As SqlConnection
    Dim comd As SqlCommand
    Dim sConnectionString As String
    Dim sql, na As String
    Dim iden As Integer
    sConnectionString =
    ConfigurationManager.ConnectionStrings("myconn").ConnectionString.ToString()
    sql = "INSERT INTO table(...) VALUES (...); SELECT
    SCOPE_IDENTITY();"
    oConnection = New SqlConnection(sConnectionString)
    comd = New SqlCommand(sql, oConnection)
    comd.Parameters.Add("@var1", SqlDbType.NVarChar, 10).value="ok"

    Try 'new

    oConnection.Open()
    comd.ExecuteNonQuery()

    trans = connection.BeginTransaction 'new
    comd.Transaction = trans 'new

    'here another insert sqlcommand but even if i remove this part, it still
    executes twice ...

    trans.Commit() 'new
    Catch sqlEx As SqlException
    If trans IsNot Nothing Then
    trans.Rollback()
    End If
    Throw New Exception("error!", sqlEx)
    Return

    Finally
    If connection IsNot Nothing Then
    connection.Close()
    End If
    End Try
    Response.Redirect("fin.aspx")
    End Sub







    "Göran Andersson" <> schreef in bericht
    news:u$5k%...
    > Bob wrote:
    >> Hi,
    >>
    >> i wrote code for inserting data into a table, but it runs twice. If i
    >> remove the line: "comd.ExecuteNonQuery()", then it runs once; but i
    >> thought that line was necessary for executing the sql command ... if i
    >> remove " oConnection.Open()", then error: "connection is not open".
    >>
    >> see my code:
    >> Dim oConnection As SqlConnection
    >> Dim comd As SqlCommand
    >> Dim sConnectionString As String
    >> Dim sql, na As String
    >> Dim iden As Integer
    >>
    >> sConnectionString =
    >> ConfigurationManager.ConnectionStrings("myconn").ConnectionString.ToString()
    >> sql = "INSERT INTO table(...) VALUES (...);"
    >> oConnection = New SqlConnection(sConnectionString)
    >> comd = New SqlCommand(sql, oConnection)
    >> comd.Parameters.Add("@var1", SqlDbType.NVarChar, 10).value="ok"
    >> oConnection.Open()
    >> comd.ExecuteNonQuery()
    >>
    >> Thanks
    >> Bob

    >
    > That code only runs the query once. What does the rest of the code do?
    >
    > Also, I see that you edited out part of the SQL query. Anything else you
    > edited out?
    >
    > --
    > Göran Andersson
    > _____
    > http://www.guffa.com
    Bob, Feb 22, 2007
    #3
  4. Bob

    Bob Guest

    I think it has to do with the second part of the query: SELECT
    SCOPE_IDENTITY()"

    How can i then make that the insert only executes once?

    "Göran Andersson" <> schreef in bericht
    news:u$5k%...
    > Bob wrote:
    >> Hi,
    >>
    >> i wrote code for inserting data into a table, but it runs twice. If i
    >> remove the line: "comd.ExecuteNonQuery()", then it runs once; but i
    >> thought that line was necessary for executing the sql command ... if i
    >> remove " oConnection.Open()", then error: "connection is not open".
    >>
    >> see my code:
    >> Dim oConnection As SqlConnection
    >> Dim comd As SqlCommand
    >> Dim sConnectionString As String
    >> Dim sql, na As String
    >> Dim iden As Integer
    >>
    >> sConnectionString =
    >> ConfigurationManager.ConnectionStrings("myconn").ConnectionString.ToString()
    >> sql = "INSERT INTO table(...) VALUES (...);"
    >> oConnection = New SqlConnection(sConnectionString)
    >> comd = New SqlCommand(sql, oConnection)
    >> comd.Parameters.Add("@var1", SqlDbType.NVarChar, 10).value="ok"
    >> oConnection.Open()
    >> comd.ExecuteNonQuery()
    >>
    >> Thanks
    >> Bob

    >
    > That code only runs the query once. What does the rest of the code do?
    >
    > Also, I see that you edited out part of the SQL query. Anything else you
    > edited out?
    >
    > --
    > Göran Andersson
    > _____
    > http://www.guffa.com
    Bob, Feb 22, 2007
    #4
  5. Bob wrote:
    > Thanks for replying.
    >
    > There is indeed more code: here is the whole code:
    > --------------------------------------------------
    > Dim trans As SqlTransaction = Nothing 'new
    > Dim oConnection As SqlConnection
    > Dim comd As SqlCommand
    > Dim sConnectionString As String
    > Dim sql, na As String
    > Dim iden As Integer
    > sConnectionString =
    > ConfigurationManager.ConnectionStrings("myconn").ConnectionString.ToString()
    > sql = "INSERT INTO table(...) VALUES (...); SELECT
    > SCOPE_IDENTITY();"
    > oConnection = New SqlConnection(sConnectionString)
    > comd = New SqlCommand(sql, oConnection)
    > comd.Parameters.Add("@var1", SqlDbType.NVarChar, 10).value="ok"
    >
    > Try 'new
    >
    > oConnection.Open()
    > comd.ExecuteNonQuery()
    >
    > trans = connection.BeginTransaction 'new
    > comd.Transaction = trans 'new
    >
    > 'here another insert sqlcommand but even if i remove this part, it still
    > executes twice ...
    >
    > trans.Commit() 'new
    > Catch sqlEx As SqlException
    > If trans IsNot Nothing Then
    > trans.Rollback()
    > End If
    > Throw New Exception("error!", sqlEx)
    > Return
    >
    > Finally
    > If connection IsNot Nothing Then
    > connection.Close()
    > End If
    > End Try
    > Response.Redirect("fin.aspx")
    > End Sub
    >


    I see that you use the same command object for the second query. It
    still contains the query and parameters from the first call, do you
    replace them?

    Although unlikely to be the cause, you can try to remove the "select
    scope_identity()" part of the query. You are not using that anyway.

    --
    Göran Andersson
    _____
    http://www.guffa.com
    =?ISO-8859-1?Q?G=F6ran_Andersson?=, Feb 22, 2007
    #5
  6. Bob

    Bob Guest

    the parameters are replaced, and i use the SCOPE_IDENTITY() for the second
    insert


    "Göran Andersson" <> schreef in bericht
    news:%23h2l%...
    > Bob wrote:
    >> Thanks for replying.
    >>
    >> There is indeed more code: here is the whole code:
    >> --------------------------------------------------
    >> Dim trans As SqlTransaction = Nothing 'new
    >> Dim oConnection As SqlConnection
    >> Dim comd As SqlCommand
    >> Dim sConnectionString As String
    >> Dim sql, na As String
    >> Dim iden As Integer
    >> sConnectionString =
    >> ConfigurationManager.ConnectionStrings("myconn").ConnectionString.ToString()
    >> sql = "INSERT INTO table(...) VALUES (...); SELECT
    >> SCOPE_IDENTITY();"
    >> oConnection = New SqlConnection(sConnectionString)
    >> comd = New SqlCommand(sql, oConnection)
    >> comd.Parameters.Add("@var1", SqlDbType.NVarChar, 10).value="ok"
    >>
    >> Try 'new
    >>
    >> oConnection.Open()
    >> comd.ExecuteNonQuery()
    >>
    >> trans = connection.BeginTransaction 'new
    >> comd.Transaction = trans 'new
    >>
    >> 'here another insert sqlcommand but even if i remove this part, it still
    >> executes twice ...
    >>
    >> trans.Commit() 'new
    >> Catch sqlEx As SqlException
    >> If trans IsNot Nothing Then
    >> trans.Rollback()
    >> End If
    >> Throw New Exception("error!", sqlEx)
    >> Return
    >>
    >> Finally
    >> If connection IsNot Nothing Then
    >> connection.Close()
    >> End If
    >> End Try
    >> Response.Redirect("fin.aspx")
    >> End Sub
    >>

    >
    > I see that you use the same command object for the second query. It still
    > contains the query and parameters from the first call, do you replace
    > them?
    >
    > Although unlikely to be the cause, you can try to remove the "select
    > scope_identity()" part of the query. You are not using that anyway.
    >
    > --
    > Göran Andersson
    > _____
    > http://www.guffa.com
    Bob, Feb 22, 2007
    #6
  7. How do you use the result from scope_identity in the second insert, as
    you don't accept any result from the first query?

    Bob wrote:
    > the parameters are replaced, and i use the SCOPE_IDENTITY() for the second
    > insert
    >
    >
    > "Göran Andersson" <> schreef in bericht
    > news:%23h2l%...
    >> Bob wrote:
    >>> Thanks for replying.
    >>>
    >>> There is indeed more code: here is the whole code:
    >>> --------------------------------------------------
    >>> Dim trans As SqlTransaction = Nothing 'new
    >>> Dim oConnection As SqlConnection
    >>> Dim comd As SqlCommand
    >>> Dim sConnectionString As String
    >>> Dim sql, na As String
    >>> Dim iden As Integer
    >>> sConnectionString =
    >>> ConfigurationManager.ConnectionStrings("myconn").ConnectionString.ToString()
    >>> sql = "INSERT INTO table(...) VALUES (...); SELECT
    >>> SCOPE_IDENTITY();"
    >>> oConnection = New SqlConnection(sConnectionString)
    >>> comd = New SqlCommand(sql, oConnection)
    >>> comd.Parameters.Add("@var1", SqlDbType.NVarChar, 10).value="ok"
    >>>
    >>> Try 'new
    >>>
    >>> oConnection.Open()
    >>> comd.ExecuteNonQuery()
    >>>
    >>> trans = connection.BeginTransaction 'new
    >>> comd.Transaction = trans 'new
    >>>
    >>> 'here another insert sqlcommand but even if i remove this part, it still
    >>> executes twice ...
    >>>
    >>> trans.Commit() 'new
    >>> Catch sqlEx As SqlException
    >>> If trans IsNot Nothing Then
    >>> trans.Rollback()
    >>> End If
    >>> Throw New Exception("error!", sqlEx)
    >>> Return
    >>>
    >>> Finally
    >>> If connection IsNot Nothing Then
    >>> connection.Close()
    >>> End If
    >>> End Try
    >>> Response.Redirect("fin.aspx")
    >>> End Sub
    >>>

    >> I see that you use the same command object for the second query. It still
    >> contains the query and parameters from the first call, do you replace
    >> them?
    >>
    >> Although unlikely to be the cause, you can try to remove the "select
    >> scope_identity()" part of the query. You are not using that anyway.
    >>
    >> --
    >> Göran Andersson
    >> _____
    >> http://www.guffa.com

    >
    >



    --
    Göran Andersson
    _____
    http://www.guffa.com
    =?ISO-8859-1?Q?G=F6ran_Andersson?=, Feb 22, 2007
    #7
  8. Bob

    Bob Guest

    i forgot this line which gets the value of the scope_identity:
    iden = Convert.ToInt32(comd.ExecuteScalar())



    "Göran Andersson" <> schreef in bericht
    news:...
    > How do you use the result from scope_identity in the second insert, as you
    > don't accept any result from the first query?
    >
    > Bob wrote:
    >> the parameters are replaced, and i use the SCOPE_IDENTITY() for the
    >> second insert
    >>
    >>
    >> "Göran Andersson" <> schreef in bericht
    >> news:%23h2l%...
    >>> Bob wrote:
    >>>> Thanks for replying.
    >>>>
    >>>> There is indeed more code: here is the whole code:
    >>>> --------------------------------------------------
    >>>> Dim trans As SqlTransaction = Nothing 'new
    >>>> Dim oConnection As SqlConnection
    >>>> Dim comd As SqlCommand
    >>>> Dim sConnectionString As String
    >>>> Dim sql, na As String
    >>>> Dim iden As Integer
    >>>> sConnectionString =
    >>>> ConfigurationManager.ConnectionStrings("myconn").ConnectionString.ToString()
    >>>> sql = "INSERT INTO table(...) VALUES (...); SELECT
    >>>> SCOPE_IDENTITY();"
    >>>> oConnection = New SqlConnection(sConnectionString)
    >>>> comd = New SqlCommand(sql, oConnection)
    >>>> comd.Parameters.Add("@var1", SqlDbType.NVarChar, 10).value="ok"
    >>>>
    >>>> Try 'new
    >>>>
    >>>> oConnection.Open()
    >>>> comd.ExecuteNonQuery()
    >>>>
    >>>> trans = connection.BeginTransaction 'new
    >>>> comd.Transaction = trans 'new
    >>>>
    >>>> 'here another insert sqlcommand but even if i remove this part, it
    >>>> still executes twice ...
    >>>>
    >>>> trans.Commit() 'new
    >>>> Catch sqlEx As SqlException
    >>>> If trans IsNot Nothing Then
    >>>> trans.Rollback()
    >>>> End If
    >>>> Throw New Exception("error!", sqlEx)
    >>>> Return
    >>>>
    >>>> Finally
    >>>> If connection IsNot Nothing Then
    >>>> connection.Close()
    >>>> End If
    >>>> End Try
    >>>> Response.Redirect("fin.aspx")
    >>>> End Sub
    >>>>
    >>> I see that you use the same command object for the second query. It
    >>> still contains the query and parameters from the first call, do you
    >>> replace them?
    >>>
    >>> Although unlikely to be the cause, you can try to remove the "select
    >>> scope_identity()" part of the query. You are not using that anyway.
    >>>
    >>> --
    >>> Göran Andersson
    >>> _____
    >>> http://www.guffa.com

    >>
    >>

    >
    >
    > --
    > Göran Andersson
    > _____
    > http://www.guffa.com
    Bob, Feb 22, 2007
    #8
  9. Oh, so you execute the query again to get the identity?

    Guess why it's executed twice? ;)

    That line will add another record, but it will not get the identity of
    either of the records added. The result of the query contains two record
    sets. The first set is returned by the insert query and is empty. The
    second set contains the identity returned by the select query.

    Bob wrote:
    > i forgot this line which gets the value of the scope_identity:
    > iden = Convert.ToInt32(comd.ExecuteScalar())
    >
    >
    >
    > "Göran Andersson" <> schreef in bericht
    > news:...
    >> How do you use the result from scope_identity in the second insert, as you
    >> don't accept any result from the first query?
    >>
    >> Bob wrote:
    >>> the parameters are replaced, and i use the SCOPE_IDENTITY() for the
    >>> second insert
    >>>
    >>>
    >>> "Göran Andersson" <> schreef in bericht
    >>> news:%23h2l%...
    >>>> Bob wrote:
    >>>>> Thanks for replying.
    >>>>>
    >>>>> There is indeed more code: here is the whole code:
    >>>>> --------------------------------------------------
    >>>>> Dim trans As SqlTransaction = Nothing 'new
    >>>>> Dim oConnection As SqlConnection
    >>>>> Dim comd As SqlCommand
    >>>>> Dim sConnectionString As String
    >>>>> Dim sql, na As String
    >>>>> Dim iden As Integer
    >>>>> sConnectionString =
    >>>>> ConfigurationManager.ConnectionStrings("myconn").ConnectionString.ToString()
    >>>>> sql = "INSERT INTO table(...) VALUES (...); SELECT
    >>>>> SCOPE_IDENTITY();"
    >>>>> oConnection = New SqlConnection(sConnectionString)
    >>>>> comd = New SqlCommand(sql, oConnection)
    >>>>> comd.Parameters.Add("@var1", SqlDbType.NVarChar, 10).value="ok"
    >>>>>
    >>>>> Try 'new
    >>>>>
    >>>>> oConnection.Open()
    >>>>> comd.ExecuteNonQuery()
    >>>>>
    >>>>> trans = connection.BeginTransaction 'new
    >>>>> comd.Transaction = trans 'new
    >>>>>
    >>>>> 'here another insert sqlcommand but even if i remove this part, it
    >>>>> still executes twice ...
    >>>>>
    >>>>> trans.Commit() 'new
    >>>>> Catch sqlEx As SqlException
    >>>>> If trans IsNot Nothing Then
    >>>>> trans.Rollback()
    >>>>> End If
    >>>>> Throw New Exception("error!", sqlEx)
    >>>>> Return
    >>>>>
    >>>>> Finally
    >>>>> If connection IsNot Nothing Then
    >>>>> connection.Close()
    >>>>> End If
    >>>>> End Try
    >>>>> Response.Redirect("fin.aspx")
    >>>>> End Sub
    >>>>>
    >>>> I see that you use the same command object for the second query. It
    >>>> still contains the query and parameters from the first call, do you
    >>>> replace them?
    >>>>
    >>>> Although unlikely to be the cause, you can try to remove the "select
    >>>> scope_identity()" part of the query. You are not using that anyway.
    >>>>
    >>>> --
    >>>> Göran Andersson
    >>>> _____
    >>>> http://www.guffa.com
    >>>

    >>
    >> --
    >> Göran Andersson
    >> _____
    >> http://www.guffa.com

    >
    >



    --
    Göran Andersson
    _____
    http://www.guffa.com
    =?ISO-8859-1?Q?G=F6ran_Andersson?=, Feb 22, 2007
    #9
  10. Bob

    Bob Guest

    That's what i suspected in my earlier message in this thread.
    The only solution i found is removing "comd.ExecuteNonQuery()".
    Is that a good thing? If not, what can i do?
    Thanks



    "Göran Andersson" <> schreef in bericht
    news:...
    > Oh, so you execute the query again to get the identity?
    >
    > Guess why it's executed twice? ;)
    >
    > That line will add another record, but it will not get the identity of
    > either of the records added. The result of the query contains two record
    > sets. The first set is returned by the insert query and is empty. The
    > second set contains the identity returned by the select query.
    >
    > Bob wrote:
    >> i forgot this line which gets the value of the scope_identity:
    >> iden = Convert.ToInt32(comd.ExecuteScalar())
    >>
    >>
    >>
    >> "Göran Andersson" <> schreef in bericht
    >> news:...
    >>> How do you use the result from scope_identity in the second insert, as
    >>> you don't accept any result from the first query?
    >>>
    >>> Bob wrote:
    >>>> the parameters are replaced, and i use the SCOPE_IDENTITY() for the
    >>>> second insert
    >>>>
    >>>>
    >>>> "Göran Andersson" <> schreef in bericht
    >>>> news:%23h2l%...
    >>>>> Bob wrote:
    >>>>>> Thanks for replying.
    >>>>>>
    >>>>>> There is indeed more code: here is the whole code:
    >>>>>> --------------------------------------------------
    >>>>>> Dim trans As SqlTransaction = Nothing 'new
    >>>>>> Dim oConnection As SqlConnection
    >>>>>> Dim comd As SqlCommand
    >>>>>> Dim sConnectionString As String
    >>>>>> Dim sql, na As String
    >>>>>> Dim iden As Integer
    >>>>>> sConnectionString =
    >>>>>> ConfigurationManager.ConnectionStrings("myconn").ConnectionString.ToString()
    >>>>>> sql = "INSERT INTO table(...) VALUES (...); SELECT
    >>>>>> SCOPE_IDENTITY();"
    >>>>>> oConnection = New SqlConnection(sConnectionString)
    >>>>>> comd = New SqlCommand(sql, oConnection)
    >>>>>> comd.Parameters.Add("@var1", SqlDbType.NVarChar,
    >>>>>> 10).value="ok"
    >>>>>>
    >>>>>> Try 'new
    >>>>>>
    >>>>>> oConnection.Open()
    >>>>>> comd.ExecuteNonQuery()
    >>>>>>
    >>>>>> trans = connection.BeginTransaction 'new
    >>>>>> comd.Transaction = trans 'new
    >>>>>>
    >>>>>> 'here another insert sqlcommand but even if i remove this part, it
    >>>>>> still executes twice ...
    >>>>>>
    >>>>>> trans.Commit() 'new
    >>>>>> Catch sqlEx As SqlException
    >>>>>> If trans IsNot Nothing Then
    >>>>>> trans.Rollback()
    >>>>>> End If
    >>>>>> Throw New Exception("error!", sqlEx)
    >>>>>> Return
    >>>>>>
    >>>>>> Finally
    >>>>>> If connection IsNot Nothing Then
    >>>>>> connection.Close()
    >>>>>> End If
    >>>>>> End Try
    >>>>>> Response.Redirect("fin.aspx")
    >>>>>> End Sub
    >>>>>>
    >>>>> I see that you use the same command object for the second query. It
    >>>>> still contains the query and parameters from the first call, do you
    >>>>> replace them?
    >>>>>
    >>>>> Although unlikely to be the cause, you can try to remove the "select
    >>>>> scope_identity()" part of the query. You are not using that anyway.
    >>>>>
    >>>>> --
    >>>>> Göran Andersson
    >>>>> _____
    >>>>> http://www.guffa.com
    >>>>
    >>>
    >>> --
    >>> Göran Andersson
    >>> _____
    >>> http://www.guffa.com

    >>
    >>

    >
    >
    > --
    > Göran Andersson
    > _____
    > http://www.guffa.com
    Bob, Feb 22, 2007
    #10
  11. Yes, if you only want to execute the query once, you should only execute
    the query once.

    I get the feeling that you just pasted together some code from different
    examples, not knowing what the code really does. If so, I suggest that
    you look up the commands in the documentation and read what they really
    do. Guessing is not really a good way to write stable code.

    Bob wrote:
    > That's what i suspected in my earlier message in this thread.
    > The only solution i found is removing "comd.ExecuteNonQuery()".
    > Is that a good thing? If not, what can i do?
    > Thanks
    >
    >
    >
    > "Göran Andersson" <> schreef in bericht
    > news:...
    >> Oh, so you execute the query again to get the identity?
    >>
    >> Guess why it's executed twice? ;)
    >>
    >> That line will add another record, but it will not get the identity of
    >> either of the records added. The result of the query contains two record
    >> sets. The first set is returned by the insert query and is empty. The
    >> second set contains the identity returned by the select query.
    >>
    >> Bob wrote:
    >>> i forgot this line which gets the value of the scope_identity:
    >>> iden = Convert.ToInt32(comd.ExecuteScalar())
    >>>
    >>>
    >>>
    >>> "Göran Andersson" <> schreef in bericht
    >>> news:...
    >>>> How do you use the result from scope_identity in the second insert, as
    >>>> you don't accept any result from the first query?
    >>>>
    >>>> Bob wrote:
    >>>>> the parameters are replaced, and i use the SCOPE_IDENTITY() for the
    >>>>> second insert
    >>>>>
    >>>>>
    >>>>> "Göran Andersson" <> schreef in bericht
    >>>>> news:%23h2l%...
    >>>>>> Bob wrote:
    >>>>>>> Thanks for replying.
    >>>>>>>
    >>>>>>> There is indeed more code: here is the whole code:
    >>>>>>> --------------------------------------------------
    >>>>>>> Dim trans As SqlTransaction = Nothing 'new
    >>>>>>> Dim oConnection As SqlConnection
    >>>>>>> Dim comd As SqlCommand
    >>>>>>> Dim sConnectionString As String
    >>>>>>> Dim sql, na As String
    >>>>>>> Dim iden As Integer
    >>>>>>> sConnectionString =
    >>>>>>> ConfigurationManager.ConnectionStrings("myconn").ConnectionString.ToString()
    >>>>>>> sql = "INSERT INTO table(...) VALUES (...); SELECT
    >>>>>>> SCOPE_IDENTITY();"
    >>>>>>> oConnection = New SqlConnection(sConnectionString)
    >>>>>>> comd = New SqlCommand(sql, oConnection)
    >>>>>>> comd.Parameters.Add("@var1", SqlDbType.NVarChar,
    >>>>>>> 10).value="ok"
    >>>>>>>
    >>>>>>> Try 'new
    >>>>>>>
    >>>>>>> oConnection.Open()
    >>>>>>> comd.ExecuteNonQuery()
    >>>>>>>
    >>>>>>> trans = connection.BeginTransaction 'new
    >>>>>>> comd.Transaction = trans 'new
    >>>>>>>
    >>>>>>> 'here another insert sqlcommand but even if i remove this part, it
    >>>>>>> still executes twice ...
    >>>>>>>
    >>>>>>> trans.Commit() 'new
    >>>>>>> Catch sqlEx As SqlException
    >>>>>>> If trans IsNot Nothing Then
    >>>>>>> trans.Rollback()
    >>>>>>> End If
    >>>>>>> Throw New Exception("error!", sqlEx)
    >>>>>>> Return
    >>>>>>>
    >>>>>>> Finally
    >>>>>>> If connection IsNot Nothing Then
    >>>>>>> connection.Close()
    >>>>>>> End If
    >>>>>>> End Try
    >>>>>>> Response.Redirect("fin.aspx")
    >>>>>>> End Sub
    >>>>>>>
    >>>>>> I see that you use the same command object for the second query. It
    >>>>>> still contains the query and parameters from the first call, do you
    >>>>>> replace them?
    >>>>>>
    >>>>>> Although unlikely to be the cause, you can try to remove the "select
    >>>>>> scope_identity()" part of the query. You are not using that anyway.
    >>>>>>
    >>>>>> --
    >>>>>> Göran Andersson
    >>>>>> _____
    >>>>>> http://www.guffa.com
    >>>> --
    >>>> Göran Andersson
    >>>> _____
    >>>> http://www.guffa.com
    >>>

    >>
    >> --
    >> Göran Andersson
    >> _____
    >> http://www.guffa.com

    >
    >



    --
    Göran Andersson
    _____
    http://www.guffa.com
    =?ISO-8859-1?Q?G=F6ran_Andersson?=, Feb 23, 2007
    #11
    1. Advertising

Want to reply to this thread or ask your own question?

It takes just 2 minutes to sign up (and it's free!). Just click the sign up button to choose a username and then you can ask your own questions on the forum.
Similar Threads
  1. Alberto

    Event executed twice

    Alberto, Jan 12, 2004, in forum: ASP .Net
    Replies:
    1
    Views:
    358
    Steve C. Orr [MVP, MCSD]
    Jan 12, 2004
  2. =?Utf-8?B?TWFnZXNo?=
    Replies:
    0
    Views:
    364
    =?Utf-8?B?TWFnZXNo?=
    Aug 25, 2004
  3. Alfons Puig
    Replies:
    0
    Views:
    333
    Alfons Puig
    Jan 5, 2005
  4. Mr. SweatyFinger
    Replies:
    2
    Views:
    1,850
    Smokey Grindel
    Dec 2, 2006
  5. DrKen
    Replies:
    8
    Views:
    1,865
    Denis McMahon
    Jun 24, 2011
Loading...

Share This Page