-1

I'm having trouble inserting an item from two different tables using a vb.net and MySql

This is Item table and Stocks Table in a single query:

Dim rsitem As New ADODB.Recordset

    rsitem.CursorLocation = ADODB.CursorLocationEnum.adUseClient
    rsitem.CursorType = ADODB.CursorTypeEnum.adOpenDynamic
    rsitem.Open("Insert Into items,stocks itemno = '" & txtItemNo.Text & "', Description = '" & txtDescription.Text & "', Price = '" & txtPrice.Text & "', Brand = '" & txtBrand.Text & "', stocks_on_hand = '" & txtStocks.text &"'",cn)

My problem is when I type in inputbox the txtStocks.text doesn't show what I input from my vb.net into mysql db. I think I have a wrong query. Can someone help me. ?

Max
  • 64
  • 2
  • 13
  • 1
    You are right. Your query is so wrong. Please look up the syntax in the docs. – juergen d Oct 08 '14 at 15:44
  • You'll also want to be **extremely** careful when inserting data like that and use the proper quoting mechanism available. Prepared statements with placeholders make this much easier to get right. – tadman Oct 08 '14 at 15:49
  • 1
    Do you really have two tables with identical columns? That sounds like a broken data model. Either way, generally inserting into two tables is achieved by executing two `INSERT` statements. Also, be aware that you have a wide open SQL injection vulnerability. Your query may not work, but whatever query the user enters for you can work just fine. – David Oct 08 '14 at 15:49
  • i know my query is wrong but when just inserting a item from table item is working , but now that i have a diff two table my code doesn't work, and can you give some link? or some related problem like that? if that is ok? – Max Oct 08 '14 at 15:52
  • @divid yes i have the itemNo. and i think i miss some thing here don't know ? Insert Into items,stocks itemno = '" & txtItemNo.Text & " – Max Oct 08 '14 at 15:54
  • Please don't use values taken directly from user entered controls (`TextBox`). Validate and normalize the values and then use a parameterized statement or stored procedure. Also http://stackoverflow.com/questions/5178697/mysql-insert-into-multiple-tables-database-normalization may be of use to you. – Michael McGriff Oct 08 '14 at 16:19

1 Answers1

0
  1. Firstly you need to make sure that the textboxes are not empty
  2. Then concerning your rsitem.Open, I saw you Open it twice.
  3. Thirdly, your query is wrong! whats the name of your Table you wish to insert into? I guess it's items?

You can try the following;

If not (String.IsNullOrEmpty(txtItemNo.Text) Orelse String.IsNullOrEmpty(txtDescription.Text) Orelse _
        String.IsNullOrEmpty(txtPrice.Text) Orelse String.IsNullOrEmpty(txtBrand.Text) Orelse _
        String.IsNullOrEmpty(txtStocks.Text) Then

    rsitem.CursorLocation = ADODB.CursorLocationEnum.adUseClient
    rsitem.CursorType = ADODB.CursorTypeEnum.adOpenDynamic
    rsitem.Open(String.format( _
                "Insert Into items (itemno, Description, Price, Brand, stocks_on_hand) values ('{0}','{1}','{2}','{3}','{4}') ", _
    txtItemNo.Text, txtDescription.Text, txtPrice.Text, txtBrand.Text, txtStocks.text), connection) 

End If
Nadeem_MK
  • 7,533
  • 7
  • 50
  • 61
  • Hello @Nadeem_MK im new in vb.net and also in mysql i also have a code to check if the textboxes is empty. but can i suggest can i use this code? rsitem.Open("Insert Into items , stocks itemno = '" & txtItemNo.Text & "', Description = '" & txtDescription.Text & "', Price = '" & txtPrice.Text & "', Brand = '" & txtBrand.Text & "', STOCKS_ON_HAND = '" & txtStock.Text & "", cn) i know the query is wrong but can i use that and make it work? – Max Oct 08 '14 at 16:15
  • Ohh! How can you check for the textboxes in mySQL? No your query is wrong. – Nadeem_MK Oct 08 '14 at 16:17
  • thats why I mentionned, you should check your textboxes values before. You can do the following `rsitem.Open("Insert Into items ( itemno,Description,Price,Brand,STOCKS_ON_HAND) values ('" & txtItemNo.Text & "', '" & txtDescription.Text & "', '" & txtPrice.Text & "', '" & txtBrand.Text & "', '" & txtStock.Text & "')", cn)` – Nadeem_MK Oct 08 '14 at 16:19