leeds2000
asked on
Filling a DB table with info from checked listbox
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.
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
ASKER
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.
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
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
ASKER
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)
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?
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?
ASKER
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
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()
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
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 :)
ASKER
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 :)
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
ASKER
Ok i will try this right away, thanks again for the help :)
ASKER
Im getting a error on this line, it says "oracleconnection is not defined" any ideas?
Using connection As New OracleConnection(connectionString)
didn't you already declare the "Imports System.Data.OracleClient" to connect to the Oracle DB?
ASKER
So far i have only retreived information from DB.
The only way i know to connect to the DB is using this commmand
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()
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.Check edListBox. CheckedIte mCollectio n = Me.chdlistkunde.CheckedIte ms
could be
Dim choicecustomer As CheckedListBox.CheckedItem Collection = Me.chdlistkunde.CheckedIte ms
if you add an import statement for the System.Windows.Forms namespace at the beginning of your code
Imports System.Window.Forms
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.Check
could be
Dim choicecustomer As CheckedListBox.CheckedItem
if you add an import statement for the System.Windows.Forms namespace at the beginning of your code
Imports System.Window.Forms
ASKER
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 :/
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()
ASKER
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
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()
ASKER
A error on this line:
conn is error, "name conn is not declared" :/
conn is error, "name conn is not declared" :/
Dim cmd As New OracleCommand(sql, conn)
ASKER
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 :(
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
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.ConnectionStrin g = ........
connection.Open()
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.ConnectionStrin
connection.Open()
ASKER
okei, that worked :)
Although when trying to make a invoice i get this error message:
system.invalidcastexceptio n: cannot use the objecttype system.string as system.data.datarowview
Any idea?
Although when trying to make a invoice i get this error message:
system.invalidcastexceptio
Any idea?
ASKER
A tip in the right direction :)
Open in new window