Error when insert table in ASP code -----Resolved----

[learnervb]

My project is when user enter data on form, I will check if customer is already existing in database, don't insert customer's information, just select Customer table to get CustomerID to insert into Order table. But if that customer is new, insert customer's information into Customer table and and pull out CustomerID too. I got this error "Microsoft OLE DB Provider for ODBC Drivers (0x80004005)Transaction cannot start while in firehose mode"and I guess because I use many cn.BeginTrans command. Anyone know how to fix this ? Thanks
Here is my code
StrSQL = "SELECT * FROM Customer WHERE LastName = '" & sLastName & "' AND FirstName = '" & sFirstName & "' " & _
"AND Birthday =" & dDateOfBirth & " AND Email = '" & sEmail & "'"
rs.Open StrSQL, cn,0, 1
If rs.EOF Then
cn.BeginTrans
StrInsertCust = "INSERT INTO Customer(FirstName,LastName,Birthday,Email,Phone,CellPhone,Street," & _
"City, County,State,Zip )" & _
"VALUES('" & sFirstName & "','" & sLastName & "','" & dDateOfBirth & "'," & _
"'" & sEmail & "','" & sPhone & "','" & sCell & "','" & sAddress & "'," & _
"'" & sCity & "','" & sCounty & "','" & sState & "','" & sZip & "')"
cn.Execute StrInsertCust
cn.CommitTrans

StrSelectCust = "SELECT * FROM Customer WHERE FirstName ='" & sFirstName & "' AND " & _
"LastName ='" & sLastName & "' AND Birthday = " & dDateOfBirth & " AND Email ='" & sEmail & "'"
rs1.Open StrSelectCust, cn, 1
If Not rs1.EOF Then
nCustomerID = rs1("CustomerID")
rs1.Close
End If
Else
nCustomerID = rs("CustomerID")
End If
'cn.BeginTrans
StrInsertOrder = "INSERT INTO Orders (CustomerID,Book) VALUES('" & nCustomerID & ",'" & sBookType & "')"
cn.Execute StrInsertOrder
cn.CommitTrans

[techgnome]

Don't use Transaction.... based on your post, I can't see any reason to use transactions.

[learnervb]

I use transaction when it's met condition then insert. I don't want insert data that not met conditions yet. As what you said, when people will use transaction ? Is it for testing when we develop ? I'm new about that stuff, please explain. Thanks.

[andreys]

In your case I would use .AddNew method, because you're need to get CostomerID to insert new record in 'Orders' table.

StrSQL = "SELECT * FROM Customer WHERE LastName = '" & sLastName & "' AND FirstName = '" & sFirstName & "' " & _
"AND Birthday =" & dDateOfBirth & " AND Email = '" & sEmail & "'"
rs.Open StrSQL, cn,0, 1

If rs.EOF Then

    rs.AddNew
    nCustomerID = rs("CustomerID")
    rs("FirstName")=sFirstName
    rs("LastName")=sLastName
    rs("Birthday")=dDateOfBirth 
    rs("Phone")=sPhone 
    rs("Email")=sEmail 
    rs("CellPhone")=sCell 
    rs("Street")=sAddress 
    rs("City")=sCity 
    rs("County")=sCounty
    rs("State")=sState
    rs("Zip")=sZip
    rs.update

Else

    nCustomerID = rs("CustomerID")

End If

StrInsertOrder = "INSERT INTO Orders (CustomerID,Book) VALUES('" & nCustomerID & ",'" & sBookType & "')"
cn.Execute StrInsertOrder
cn.CommitTrans

[learnervb]

So you mean it's not good if use transaction for my case right ? Thanks anyway.

[andreys]

Originally posted by learnervb
So you mean it's not good if use transaction for my case right ? Thanks anyway.

Right, not in your case.

[techgnome]

Usually you use transactions when you are updating/inserting into multiple tables and it has to be an all or nothing.
The typical example is in banking. If you want to move money from savings into checking, it requires two updates. The first to pull the money out of savings and a second to put it into checking. If you don't use transactions and something fails between taking money out of savings & puting it into checking, you will be short (because it was taken from savings, but not credited to checking.) With transactions, if the "put in checking" command fails, the whole thing is rolled back and the money goes back into savings.
Since it doesn't appear that you are doing anything of the sort, transactions in this case are not necessary and quite possibly be hurting performance.