Filling a DB table with info from checked listbox

this code sample should help you retrieving what you need

Imports System.Text
 
Public Class Form1
    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        For n As Integer = 0 To 9
            Me.CheckedListBox1.Items.Add("Client " & n)
            Me.CheckedListBox2.Items.Add("Item " & n)
        Next
    End Sub
 
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim checkedListBox1Values As CheckedListBox.CheckedItemCollection = Me.CheckedListBox1.CheckedItems
        Dim sb As StringBuilder = New StringBuilder()
        For n As Integer = 0 To checkedListBox1Values.Count - 1
            sb.Append("item: " & checkedListBox1Values.Item(n).ToString() & vbCrLf)
        Next
        MessageBox.Show(sb.ToString())
    End Sub
End Class

Select allOpen in new window

Im not sure if you understood my problem, il try again :)

I already have 2 checklistboxes that is getting values from the database tables customer and items

i need a code in the button "make invoice" that takes these values from the checkedlistboxes and place them in another table in the database called "invoice"

Example the table order consist of "order id" "customer" "customer adress" "item" "quantity" and "order date"

Customer table consists of "customer id" "adress" "first name" "last name" "phone number" etc etc

And items (goods) table consists of "item_id" "item name" "item description" etc

I need button "make invoice" to take these values from customer "customer id" "customer first and last name" "customer adress" and these values from item "item_id" "item name" and place them inside the "order" table.

just clarify this point please:

as you're specifying a new invoice you do this one customer at a time? from your code snippet it seems that you need to retrieve multiple customer on each "make invoice" click

Yes only 1 customer at each invoice, but i need to retrieve mulitiple queries from the customer database table.

But can only choose 1 customer from the checkedlistbox to make the invoice too. (altough first of all i just need to be able to create this order query with values from the customer and items table)

assuming the user can check only one customer at a time you can retrieve the selected customer details, save them to some variables and then loop through the checked items listbox and insert the new order row into the database.

use a transaction to make all these queries so you can catch any exception and rollback what you've inserted so far.

how do you fill the checkedlistboxes?

Im filling the checklistboxes from the database, the customer goes into 1 checkedlistbox and the items goes into another checkedlistbox, when the user then checks both a customer and a item, the info should be taken to the "order" table.

the code is how i am filling the checkedlistboxes with data from the database

        checkedlistbox.DataSource = Me.ITEMBindingSource
        checkedlistbox.DisplayMember = Me.Dataset.ITEM.ITEM_NAMEColumn.ToString()

Select allOpen in new window

Im using Oracle, but i will see if i manage to convert this to oracle and test it, thanks for the fast and great reply :)

Im struggling a bit with the "transaction command" what is this converted to oracle DB? It was hard converting this since i have never used OLEDB, and i am a newbie at VB.net, sorry for this but do you have any idea how to convert this to oracle sql?

If not i understand, the help i have goten so far was far beyond expectations :)

use the OracleTransaction instead. code snippet from MSDN below

Public Sub RunOracleTransaction(ByVal connectionString As String)
    Using connection As New OracleConnection(connectionString)
        connection.Open()
 
        Dim command As OracleCommand = connection.CreateCommand()
        Dim transaction As OracleTransaction
 
        ' Start a local transaction
        transaction = connection.BeginTransaction(IsolationLevel.ReadCommitted)
        ' Assign transaction object for a pending local transaction
        command.Transaction = transaction
 
        Try
            command.CommandText = _
                "INSERT INTO Dept (DeptNo, Dname, Loc) values (50, 'TECHNOLOGY', 'DENVER')"
            command.ExecuteNonQuery()
            command.CommandText = _
                "INSERT INTO Dept (DeptNo, Dname, Loc) values (60, 'ENGINEERING', 'KANSAS CITY')"
            command.ExecuteNonQuery()
            transaction.Commit()
            Console.WriteLine("Both records are written to database.")
        Catch e As Exception
            transaction.Rollback()
            Console.WriteLine(e.ToString())
            Console.WriteLine("Neither record was written to database.")
        End Try
    End Using
End Sub

Select allOpen in new window

Ok i will try this right away, thanks again for the help :)

Im getting a error on this line, it says "oracleconnection is not defined"  any ideas?

Using connection As New OracleConnection(connectionString)

Select allOpen in new window

didn't you already declare the "Imports System.Data.OracleClient" to connect to the Oracle DB?

So far i have only retreived information from DB.

The only way i know to connect to the DB is using this commmand

Dim myconnection As New System.Data.OracleClient.OracleConnection
 
        myconnection.ConnectionString = "user id=*****;password=*****;data source=oracle.***.***.**"
        myconnection.Open()

Select allOpen in new window

ok, this is right. you can access the OracleConnection and OracleTransaction classes by using the

Imports System.Data.OracleClient

statement on top of your vb code file. this keyword, Imports, let you access and use many classes directly.
the code you use previously

Dim choicecustomer As System.Windows.Forms.CheckedListBox.CheckedItemCollection = Me.chdlistkunde.CheckedItems

could be

Dim choicecustomer As CheckedListBox.CheckedItemCollection = Me.chdlistkunde.CheckedItems

if you add an import statement for the System.Windows.Forms namespace at the beginning of your code

Imports System.Window.Forms

Oki, i understand now :) logic and smart :)

There is still a bit struggle from my side, please bear with me as i am a noob, and it can take a while before this sinks in :)

This part of the OLEDB code is for me difficult to manage to convert to oracle, i dont understand how :/

                    For Each rowItem In itemsChecked
                        Dim itemId As Integer = Convert.ToInt32(rowItem("knd_nr"))
                        Dim itemQuantity As Integer = Convert.ToInt32(Me.txtantall.Text)
                        Dim sql As String = "insert into orders (customerid,customeraddress,itemid,itemquantity) values (?,?,?,?)"
                        Dim cmd As New OleDbCommand(sql, conn)
                        'assign transaction to avoid corrupted data
                        cmd.Transaction = transaction
                        cmd.Parameters.Add(New OleDbParameter("customerid", customerId))
                        cmd.Parameters.Add(New OleDbParameter("customeraddress", customerAddress))
                        cmd.Parameters.Add(New OleDbParameter("itemid", itemId))
                        cmd.Parameters.Add(New OleDbParameter("itemquantity", itemQuantity))
                        cmd.ExecuteNonQuery()

Select allOpen in new window

correction, from this part:

'assign transaction to avoid corrupted data
                        cmd.Transaction = transaction
                        cmd.Parameters.Add(New OleDbParameter("customerid", customerId))
                        cmd.Parameters.Add(New OleDbParameter("customeraddress", customerAddress))
                        cmd.Parameters.Add(New OleDbParameter("itemid", itemId))
                        cmd.Parameters.Add(New OleDbParameter("itemquantity", itemQuantity))
                        cmd.ExecuteNonQuery()
                    Next

Select allOpen in new window

don't have an oracle db to test it but it should work this way

                        Dim sql As String = "insert into orders (customerid,customeraddress,itemid,itemquantity) values (:customerid,:customeraddress,:itemid,:itemquantity)"
                        Dim cmd As New OracleCommand(sql, conn)
                        'assign transaction to avoid corrupted data
                        cmd.Transaction = transaction
                        cmd.Parameters.Add(New OracleParameter("customerid", customerId))
                        cmd.Parameters.Add(New OracleParameter("customeraddress", customerAddress))
                        cmd.Parameters.Add(New OracleParameter("itemid", itemId))
                        cmd.Parameters.Add(New OracleParameter("itemquantity", itemQuantity))
                        cmd.ExecuteNonQuery()

Select allOpen in new window

A error on this line:

conn is error, "name conn is not declared" :/

                        Dim cmd As New OracleCommand(sql, conn)

Select allOpen in new window

Im getting a error here too, i dont seem to manage to make a connectionstring inside the button, and i dont manage to call the subroutine i made from the example you gave.

Il tried calling it to the button, but no luck, there is something wrong with my connection string, i dont understand why im getting this error now :(

        Dim conn As System.Data.OracleClient.OracleConnection
        conn.ConnectionString = "user id=***;password=*****;data source=oracle.********"
        Using connection As New OracleConnection()
            connection.Open()
 
            Dim command As OracleCommand = connection.CreateCommand()
            Dim transaction As OracleTransaction

Select allOpen in new window

you're creating a new OracleConnection through the using directive so the connectionstring is not set

if you want to put all your code inside the using directive just move the statement that set the connection string inside it and change the variable name

Using connection As New OracleConnection()
   connection.ConnectionString = ........
   connection.Open()

okei, that worked :)

Although when trying to make a invoice i get this error message:

system.invalidcastexception: cannot use the objecttype system.string as system.data.datarowview

Any idea?

A tip in the right direction :)