Can't save data to SQL Server

Discussion in 'ASP .Net' started by =?Utf-8?B?TWljaGFlbA==?=, Mar 17, 2006.

  1. Hi Everyone,
    I have a simple page right now to save an PO info to the server. I get the
    following error:
    "ExecuteNonQuery requires the command to have a transaction when the
    connection assigned to the command is in a pending local transaction. The
    Transaction property of the command has not been initialized."
    Theres no error number. I'm using the following code:
    Protected Sub cmdSave_Click(ByVal sender As Object, ByVal e As
    System.EventArgs) Handles cmdSave.Click
    Dim mCommand As SqlCommand
    Dim cnSQL as SqlConnection
    Dim rtn as Integer
    Dim result as Boolean = True
    Dim SaveTrans As SqlTransaction
    try
    cmdAddnew.Enabled = True
    cnSQL = OpenConnection()
    SaveTrans = cnSQL.BeginTransaction
    mCommand = New SqlCommand
    mCommand.CommandType = CommandType.StoredProcedure
    mCommand.Connection = cnSQL
    mCommand.CommandText = "Admin_AddPO"
    mCommand.Parameters.add("@ProgramId", SqlDbType.Int ).Value =
    cmbPrograms.SelectedValue
    mCommand.Parameters.add("@OrderDate", SqlDbType.DateTime ).Value =
    format(Now(), "mm/dd/yyyy")
    mCommand.Parameters.add("@VendorName", SqlDbType.VarChar , 250 ).Value =
    txtVendorName.Text
    mCommand.Parameters.add("@ReturnValue", SqlDbType.int).Direction =
    ParameterDirection.ReturnValue
    mCommand.ExecuteNonQuery
    rtn = mCommand.Parameters("@ReturnValue").Value.ToString

    mCommand = New SqlCommand
    mCommand.CommandType = CommandType.StoredProcedure
    mCommand.CommandText = "Admin_AddPOItems"
    mCommand.Parameters.add("@POrderId", SqlDbType.Int ).Value = rtn
    mCommand.Parameters.add("@CatalogeId", SqlDbType.VarChar , 20 ).Value =
    txtCatalogID.Text
    mCommand.Parameters.add("@ItemDescription", SqlDbType.VarChar , 250
    ).Value = txtDescription.Text
    mCommand.Parameters.add("@Qty", SqlDbType.Int ).Value = txtQty.Text
    mCommand.Parameters.add("@UnitPrice", SqlDbType.Money ).Value =
    txtUnitPrice.Text
    mCommand.Parameters.add("@Page", SqlDbType.VarChar , 10 ).Value =
    txtPage.Text
    mCommand.ExecuteNonQuery

    Catch ex As Exception
    msgbox(ex.Message )
    result = False
    finally
    if result then
    SaveTrans.Commit
    SqlDataPOs.DataBind
    else
    SaveTrans.Rollback
    End If
    cnSQL.Dispose
    mCommand.Dispose
    SaveTrans.Dispose
    End Try

    End Sub

    Public Function OpenConnection() as SqlConnection
    try
    NetCon = New SqlConnection
    With NetCon
    .ConnectionString = "packet size=4096;integrated security=SSPI;data
    source=Backupsvr\bkupexec;persist security info=False;initial
    catalog=PurchaseOrders"
    .Open()
    End With
    return NetCon
    Catch ex As Exception
    msgbox(ex.Message )
    return nothing
    End Try
    End Function

    I hope someone can help. Thanks for any info that you can provide.
    Michael
    =?Utf-8?B?TWljaGFlbA==?=, Mar 17, 2006
    #1
    1. Advertising

  2. Sorry about this, I found my problem. I forgot to assign the transaction to
    the command object
    mCommand.Transation = SaveTrans
    Thanks for the look.
    Michael

    "Michael" wrote:

    > Hi Everyone,
    > I have a simple page right now to save an PO info to the server. I get the
    > following error:
    > "ExecuteNonQuery requires the command to have a transaction when the
    > connection assigned to the command is in a pending local transaction. The
    > Transaction property of the command has not been initialized."
    > Theres no error number. I'm using the following code:
    > Protected Sub cmdSave_Click(ByVal sender As Object, ByVal e As
    > System.EventArgs) Handles cmdSave.Click
    > Dim mCommand As SqlCommand
    > Dim cnSQL as SqlConnection
    > Dim rtn as Integer
    > Dim result as Boolean = True
    > Dim SaveTrans As SqlTransaction
    > try
    > cmdAddnew.Enabled = True
    > cnSQL = OpenConnection()
    > SaveTrans = cnSQL.BeginTransaction
    > mCommand = New SqlCommand
    > mCommand.CommandType = CommandType.StoredProcedure
    > mCommand.Connection = cnSQL
    > mCommand.CommandText = "Admin_AddPO"
    > mCommand.Parameters.add("@ProgramId", SqlDbType.Int ).Value =
    > cmbPrograms.SelectedValue
    > mCommand.Parameters.add("@OrderDate", SqlDbType.DateTime ).Value =
    > format(Now(), "mm/dd/yyyy")
    > mCommand.Parameters.add("@VendorName", SqlDbType.VarChar , 250 ).Value =
    > txtVendorName.Text
    > mCommand.Parameters.add("@ReturnValue", SqlDbType.int).Direction =
    > ParameterDirection.ReturnValue
    > mCommand.ExecuteNonQuery
    > rtn = mCommand.Parameters("@ReturnValue").Value.ToString
    >
    > mCommand = New SqlCommand
    > mCommand.CommandType = CommandType.StoredProcedure
    > mCommand.CommandText = "Admin_AddPOItems"
    > mCommand.Parameters.add("@POrderId", SqlDbType.Int ).Value = rtn
    > mCommand.Parameters.add("@CatalogeId", SqlDbType.VarChar , 20 ).Value =
    > txtCatalogID.Text
    > mCommand.Parameters.add("@ItemDescription", SqlDbType.VarChar , 250
    > ).Value = txtDescription.Text
    > mCommand.Parameters.add("@Qty", SqlDbType.Int ).Value = txtQty.Text
    > mCommand.Parameters.add("@UnitPrice", SqlDbType.Money ).Value =
    > txtUnitPrice.Text
    > mCommand.Parameters.add("@Page", SqlDbType.VarChar , 10 ).Value =
    > txtPage.Text
    > mCommand.ExecuteNonQuery
    >
    > Catch ex As Exception
    > msgbox(ex.Message )
    > result = False
    > finally
    > if result then
    > SaveTrans.Commit
    > SqlDataPOs.DataBind
    > else
    > SaveTrans.Rollback
    > End If
    > cnSQL.Dispose
    > mCommand.Dispose
    > SaveTrans.Dispose
    > End Try
    >
    > End Sub
    >
    > Public Function OpenConnection() as SqlConnection
    > try
    > NetCon = New SqlConnection
    > With NetCon
    > .ConnectionString = "packet size=4096;integrated security=SSPI;data
    > source=Backupsvr\bkupexec;persist security info=False;initial
    > catalog=PurchaseOrders"
    > .Open()
    > End With
    > return NetCon
    > Catch ex As Exception
    > msgbox(ex.Message )
    > return nothing
    > End Try
    > End Function
    >
    > I hope someone can help. Thanks for any info that you can provide.
    > Michael
    >
    =?Utf-8?B?TWljaGFlbA==?=, Mar 17, 2006
    #2
    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. =?Utf-8?B?UmV6YQ==?=
    Replies:
    3
    Views:
    17,924
    Carlos Barini
    Jun 7, 2004
  2. Jason
    Replies:
    3
    Views:
    4,004
    Sudsy
    May 25, 2004
  3. teo
    Replies:
    1
    Views:
    459
  4. Steve Kershaw
    Replies:
    5
    Views:
    5,912
    Steve Kershaw
    Aug 29, 2006
  5. Bhuwan Bhaskar
    Replies:
    5
    Views:
    574
    Bhuwan Bhaskar
    Oct 21, 2007
Loading...

Share This Page