Solved

Filling a DB table with info from checked listbox

Posted on 2007-11-18
23
277 Views
Last Modified: 2010-04-21
First il explain my program a little

I have 2 checked listboxes, 1 for customers and 1 for goods, i also have a textbox which i need to fill in the quantity of goods that customer needs ordered

When i have checked 1 customer, and a item and quantity i have a button that is called make invoice, when this is pressed, a invoice should be made with the information from the checked listboxes in a database called invoice (SQL)

The code i think has a good start is below, but im stuck, i have a hard time figuring out how to go on, im struggling with the understanding from here, please help me.
Dim choicecustomer As System.Windows.Forms.CheckedListBox.CheckedItemCollection = Me.chdlistkunde.CheckedItems
        Dim choicegoods As System.Windows.Forms.CheckedListBox.CheckedItemCollection = Me.chkdlistvare.CheckedItems
        Dim SQLsentence As String
 
        Dim drv As DataRowView
 
        For Each drv In choicecustomer
            Dim customerfname As String = drv.Item("csm_fname")
            Dim customerlname As String = drv.Item("csm_lname")
            Dim customeradress As String = drv.Item("csm_adress")
            Dim customerpostnr As Integer = drv.Item("post_nr")
 
            'Need to place values from the goods listbox too, im stuck here :(
            
 
            'sqlsentence = insert into order values(” & kundeid & ”,” & k ….. )??? please help me
 
          'Run command against database here i guess?
 
        Next

Open in new window

0
Comment
Question by:leeds2000
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 14
  • 9
23 Comments
 
LVL 7

Expert Comment

by:nogovoia
ID: 20310871
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

Open in new window

0
 
LVL 3

Author Comment

by:leeds2000
ID: 20310907
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.

0
 
LVL 7

Expert Comment

by:nogovoia
ID: 20311017
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
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 3

Author Comment

by:leeds2000
ID: 20311033
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)
0
 
LVL 7

Expert Comment

by:nogovoia
ID: 20311090
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?
0
 
LVL 3

Author Comment

by:leeds2000
ID: 20311101
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()

Open in new window

0
 
LVL 7

Accepted Solution

by:
nogovoia earned 500 total points
ID: 20311310
could work?
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        'assuming you're connecting via OLEDB
        Dim transaction As OleDbTransaction = conn.BeginTransaction()
        Try
 
            Dim rowCustomer As DataRowView
            Dim rowItem As DataRowView
            Dim customersChecked As CheckedListBox.CheckedItemCollection = CheckedListBox1.CheckedItems
            Dim itemsChecked As CheckedListBox.CheckedItemCollection = CheckedListBox2.CheckedItems
            'assuming you'll handle one customer at a time this external loop will execute only one time
            For Each rowCustomer In customersChecked
                Dim customerId As Integer = Convert.ToInt32(rowCustomer("id"))
                Dim customerAddress As String = rowCustomer("address").ToString()
                'this internal loop will run for each item checked
                For Each rowItem In itemsChecked
                    Dim itemId As Integer = Convert.ToInt32(rowItem("id"))
                    Dim itemQuantity As Integer = Convert.ToInt32(TextBoxQuantity.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()
                Next
            Next
            'commit changes to db, everything worked right!
            transaction.Commit()
        Catch ex As Exception
            If Not transaction Is Nothing Then
                'something went wrong, rollback changes so you won't have corrupted data in your db
                transaction.Rollback()
            End If
            MessageBox.Show(ex.ToString())
        End Try
    End Sub

Open in new window

0
 
LVL 3

Author Comment

by:leeds2000
ID: 20311321
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 :)
0
 
LVL 3

Author Comment

by:leeds2000
ID: 20311528
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 :)
0
 
LVL 7

Expert Comment

by:nogovoia
ID: 20311571
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

Open in new window

0
 
LVL 3

Author Comment

by:leeds2000
ID: 20311576
Ok i will try this right away, thanks again for the help :)
0
 
LVL 3

Author Comment

by:leeds2000
ID: 20311690
Im getting a error on this line, it says "oracleconnection is not defined"  any ideas?

Using connection As New OracleConnection(connectionString)

Open in new window

0
 
LVL 7

Expert Comment

by:nogovoia
ID: 20311732
didn't you already declare the "Imports System.Data.OracleClient" to connect to the Oracle DB?
0
 
LVL 3

Author Comment

by:leeds2000
ID: 20311765
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()

Open in new window

0
 
LVL 7

Expert Comment

by:nogovoia
ID: 20311921
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
0
 
LVL 3

Author Comment

by:leeds2000
ID: 20311993
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()

Open in new window

0
 
LVL 3

Author Comment

by:leeds2000
ID: 20312000
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

Open in new window

0
 
LVL 7

Expert Comment

by:nogovoia
ID: 20312101
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()

Open in new window

0
 
LVL 3

Author Comment

by:leeds2000
ID: 20312268
A error on this line:

conn is error, "name conn is not declared" :/
                        Dim cmd As New OracleCommand(sql, conn)

Open in new window

0
 
LVL 3

Author Comment

by:leeds2000
ID: 20312349
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

Open in new window

0
 
LVL 7

Expert Comment

by:nogovoia
ID: 20312389
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()
0
 
LVL 3

Author Comment

by:leeds2000
ID: 20312443
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?
0
 
LVL 3

Author Closing Comment

by:leeds2000
ID: 31409895
A tip in the right direction :)
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Article by: jpaulino
XML Literals are a great way to handle XML files and the community doesn’t use it as much as it should.  An XML Literal is like a String (http://msdn.microsoft.com/en-us/library/system.string.aspx) Literal, only instead of starting and ending with w…
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…

734 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question