[noobydoods]

Hi!

table definition:

ORDER_ID (PK)| PRODUCT_ID | QTY | SUBTOTAL

Im trying to insert into table (ORDER_ID) values (aValue)

Code :

Dim mytransaction As OleDb.OleDbTransaction
Dim orderid As String
orderid = txt_orderid.Text
myconnection2.Open()
mytransaction = myconnection2.BeginTransaction

Try
     Dim insertitem As String = "INSERT INTO TBL_ORDERITEM_A154287 (FLD_ORDER_ID) VALUES ('" & orderid & "')"
     Dim mywriter2 As New OleDb.OleDbCommand(insertitem, myconnection2, mytransaction)
     mywriter2.ExecuteNonQuery()

     mytransaction.Commit()
     myconnection2.Close()

     Beep()
     MsgBox("New order with Order Number : '" & orderid & "' has been successfully added.")
Catch ex As Exception
     Beep()
     MsgBox("Problem with Transaction : " & vbCrLf & vbCrLf & ex.Message)

     mytransaction.Rollback()
     myconnection2.Close()
End Try

i get "Problem with Transaction : You must eneter a value in the 'TABLE1234.PRODDUCT_ID' field" but i only want orderid. and i can't figure out why. is it because of the transaction? its a pk column? I did try adding extra fields and inserting those together with orderid, thought it was a one column issue thing.

the product's are actually in a listbox so im using for loop to insert into the table where orderid=orderid unless i can do that together with inserting orderid? im not sure how. coz i right now i have it setup as insert order id then update the table with the products. im sure that it the most complicated way but i dont know another way.

This post has been edited by noobydoods: 11 December 2016 - 12:37 AM

[Atli]

Hey.

I'm guessing the problem is that the table definition makes the PRODUCT_ID mandatory; not nullable, without a default, or part of a primary key.

However I think that might not be your biggest problem...

Can you tell us why the table is named "TBL_ORDERITEM_A154287"? What is the "A154287" at the end?
Is there a table somewhere that stores the orders, other than your "TBL_ORDERITEM" tables?

To me, that looks like the table name has actual data in it - and is perhaps even being generated dynamically? - which is a huge fault in any RDBMS design, and can cause all sorts of unwanted side-effects, like the one you are describing.

[noobydoods]

The "A154287" at the end is my studetn id. its mandatory for us naming our tables with our student id at the end like TBL_ORDER_A154287 : TABLE_NAME_ID.

Okay, im using ms access 2013, so in the Default Value field for PRODUCT_ID currently it is empty. Should I put something in it then? Like NULL? or isnt an empty box the same thing?

Oh. nvm. thanks for pointing out about the table definition part. i modified the field properties for PRODUCT_ID. there was a filed Required, default was Yes changed it to No.

[andrewsw]

You are using MS Access, not MySQL. Moving to correct forum.