Error when insert table in ASP code -----Resolved----
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