3 Replies - 560 Views - Last Post: 11 December 2016 - 02:58 AM

#1 noobydoods  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 111
  • Joined: 21-May 14

Insert into 1 table 1 column using transaction

Posted 10 December 2016 - 11:37 PM

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


Is This A Good Question/Topic? 0
  • +

Replies To: Insert into 1 table 1 column using transaction

#2 Atli  Icon User is offline

  • Enhance Your Calm
  • member icon

Reputation: 4238
  • View blog
  • Posts: 7,216
  • Joined: 08-June 10

Re: Insert into 1 table 1 column using transaction

Posted 11 December 2016 - 01:35 AM

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.
Was This Post Helpful? 0
  • +
  • -

#3 noobydoods  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 111
  • Joined: 21-May 14

Re: Insert into 1 table 1 column using transaction

Posted 11 December 2016 - 01:51 AM

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.
Was This Post Helpful? 0
  • +
  • -

#4 andrewsw  Icon User is online

  • lashings of ginger beer
  • member icon

Reputation: 6341
  • View blog
  • Posts: 25,593
  • Joined: 12-December 12

Re: Insert into 1 table 1 column using transaction

Posted 11 December 2016 - 02:58 AM

You are using MS Access, not MySQL. Moving to correct forum.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1