Solved

Filling a DB table with info from checked listbox

Posted on 2007-11-18
23
254 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
  • 14
  • 9
23 Comments
 
LVL 7

Expert Comment

by:nogovoia
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 3

Author Comment

by:leeds2000
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
Ok i will try this right away, thanks again for the help :)
0
What Should I Do With This Threat Intelligence?

Are you wondering if you actually need threat intelligence? The answer is yes. We explain the basics for creating useful threat intelligence.

 
LVL 3

Author Comment

by:leeds2000
Comment Utility
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
Comment Utility
didn't you already declare the "Imports System.Data.OracleClient" to connect to the Oracle DB?
0
 
LVL 3

Author Comment

by:leeds2000
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
A tip in the right direction :)
0

Featured Post

Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

Join & Write a Comment

Suggested Solutions

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…
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

762 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

Need Help in Real-Time?

Connect with top rated Experts

5 Experts available now in Live!

Get 1:1 Help Now