How can i save 2 or more item in one field of database

[jez9]

ms access [tblorder - code[text], items[text], total[text]]
code = lblcode [label]
items= x.SubItems(1) [in listview]
total = lbltotal [label]

as of now i can only save 1 item in the field 'items' , but i want to save 2 or more items,how can i do that?

For Each x As ListViewItem In lvorder.Items
sql = "insert into tblorder (code,items,total) values ('" _
           & lblcode.Text & "','" _
           & x.subitems(1) & "','" _
           & lbltotal.Text & "')" 

        Dim cmd = New OleDb.OleDbCommand(sql, con)
        cmd.ExecuteNonQuery()
        cmd.Dispose()
        Next
        MsgBox("save")

[Santanu.Das]

At Line No. 7 the declaration of command object would be

Dim cmd As OleDb.OleDbCommand = New OleDb.OleDbCommand(sql, con)

And your SQL Statement would be

sql = "insert into tblorder (code,items,total) values ('" _
           & lblcode.Text & "','" _
           & x.subitems(1).Text & "','" _
           & lbltotal.Text & "')" 

Use parameterised query to avoid SQL injection attac. Hope it can help you.

[jez9]

@shark 1. ok so i tried this one

Dim sql As String = "INSERT INTO table1(code, item,total) VALUES(?, ?, ?)"
        cmd = New OleDbCommand(sql.ToString, con)
        cmd.Parameters.AddWithValue("@code", lblreceiptnum.Text)
        cmd.Parameters.AddWithValue("@item", lvorder.subitem(1).text) 'i got error here
        cmd.Parameters.AddWithValue("@total", lbltotal)
        cmd.ExecuteNonQuery()

[Santanu.Das]

cmd.Parameters.AddWithValue("@item", lvorder.subitem(1).text) 'i got error here

Why are you using lvorder. It is the name of the listview object.
You already start a loop

For Each x As ListViewItem In lvorder.Items

It would be a listviewitem object. Here x is the ListViewItem. So your codes would be

cmd.Parameters.AddWithValue("@item", x.subitem(1).text)

Hope it can help you.

[jez9]

oh yeah, i forgot. the output of the code that ive used is like this :

code      |   item            |total
1111111   |  steak            | 200
1111111   | adobo             | 100

but, i dont want to duplicate the code at the sam time the item , i want somethin like this:
code      |   item            |total
1111111   |  steak, adobo     | 300

[jez9]

the data keep duplicating,please help :(

[Santanu.Das]

Check first the code No does exist or not. If doesn't exist Insert new data else
modify existing data.

[jez9]

i already tried to check wether the data exist or not, but the problem is how to save like this in item
[steak , adobo ][i want to use ' , ' to join the two item. how can i do that?

the steak and adobo is separate in listview but i want them to be in the same field only by using ' , '

[Santanu.Das]

You can use Parameterised SQL Query to do it. You never insert directly (from textbox or assigning value or by variable) any spacial character into the feild, but by using Parameter you can do it.

[jez9]

@Shark 1, ok i understand now, i will try it, thank you :)

[jez9]

I already tried in checkin wether the item exist or not, and its workin when i use the update but, what i really want is, i want to Insert the two items instead of update. And heres my code.

For Each x As ListViewItem In lvorder.Items
            Dim sql As String = "insert into tblorder(receiptnum,nod) VALUES (@receiptnum,@nod"
            cmd = New OleDbCommand(sql.ToString, con)

            cmd.Parameters.AddWithValue("@nod", x.SubItems(1).Text)
            cmd.Parameters.AddWithValue("@receiptnum", lblday.Text)
            cmd.ExecuteNonQuery()
            MsgBox("ok")

please help

[jez9]

I already tried in checkin wether the item exist or not, and its workin when i use the update but, what i really want is, i want to Insert the two items instead of update. And heres my code.

For Each x As ListViewItem In lvorder.Items
            Dim sql As String = "insert into tblorder(receiptnum,nod) VALUES (@receiptnum,@nod"
            cmd = New OleDbCommand(sql.ToString, con)

            cmd.Parameters.AddWithValue("@receiptnum", x.SubItems(1).Text)
            cmd.Parameters.AddWithValue("@nod", lblday.Text)
            cmd.ExecuteNonQuery()
            MsgBox("ok")

please help

[Santanu.Das]

What are receiptnum and nod ?
Is receiptnum the total order qty in a day ? If it is simply initiate a loop and add the subitems values first then try to update the database. The codes are like

Dim totqty As Integer = 0

For Each x As ListViewItem In lvorder.Items
    totqty += Val(x.SubItems(1).Text)
Next

Dim sql As String = "insert into tblorder(receiptnum,nod) VALUES (?, ?)"
con.Open()
cmd = New OleDbCommand(sql, con)
cmd.Parameters.AddWithValue("@receiptnum", cStr(totqty))
cmd.Parameters.AddWithValue("@nod", lblday.Text)
cmd.ExecuteNonQuery()
cmd.Parameters.Dispose()
cmd.Dispose()
con.Close()

MsgBox("ok")

Preassuming that receiptnum,nod are Text Type Field.
It should help you.

[jez9]

receiptnum is like the invoice, its the primary key in database.the nod is name of dish, its where i save the 2 or more items

[Santanu.Das]

Sorry, my assumption is wrong.
To save the multiple Items in a single row like (item1, item2, item3, ...) is as equal as the previous. Here is the following one, but this codes are effective when you try to save finally the entire receipt after adding all items to the listview by clicking a button to save the receipt and starts a new blank receipt.

Dim itmnm As String = ""
For i As Integer = 0 to ListView1.Items.Count - 1
    itmnm &= ListView1.Items(i).SubItems(1).Text
    If (i < (ListView1.Items.Count - 1)) Then
        itmnm &= ", 
    End If
Next
Dim sql As String = "insert into tblorder(receiptnum,nod) VALUES (?, ?)"
con.Open()
cmd = New OleDbCommand(sql, con)
cmd.Parameters.AddWithValue("@receiptnum", lblRcpt.Text)
cmd.Parameters.AddWithValue("@nod", itmnm)
cmd.ExecuteNonQuery()
cmd.Parameters.Dispose()
cmd.Dispose()
con.Close()

MsgBox("ok")

Hope it can help you

[jez9]

@Shark_1, o my god, i really thank you, it works now, after how many days ive research and look for the answer,and now you give me the exact answer. as i thought you really is the best , thank you :D