retrieving data from database via VB.net 2

[smithbr]

I am trying to set up a VB.net program that will allow the user to enter an invoice number into a textbox, then click a retrieve command button, from this I want to populate 4 labels with the corresponding data relevant to the invoice number. I have the database linked using the OleDbConnection, OleDbDataAdapter, and DsIvoice. I have only programed in VB 6.0 but now I am using VB.Net so I am kinda lost as to all the new ways of doing things. Does anyone know what code I will need to put behind the Retrieve command button including how to define the variables. Later, I want this data to be carried over so that I can use a Select Case procedure to determine a commission rate based on the Salesrep, product, and vendor (all things I want to get pulled up when you retrieve the data associated witht he invoice number). Any help would be great..I just don't even know where to start. Thanks

 

[bsimbeck]

I am going to assume that you have all of that done at design time and it works (All linked together and sql statments generated...)

if you want to use labels you have to do it programaticly

so under the click action for the button you will want to do everything but the connection programaticly

'--open the connection
oledbconnection.open

'--create the data adapter and fill it
dim sql as string == "Insert SQL STATMENT HERE"
dim daInvoice as oledbdataadapter = new oledbdataadapter(sql, cn)

'--create and fill the dataset
dim dsInvoice as dataset = new dataset
daInovice.fill(dsInvoice, "INVOICE")

'--create the table to access the items
dim dt as datatable = dsInvoice.Tables("INVOICE")

'---assign fields to the labels
label1.text = dt.rows(0).item("ColName")
oledbconnection.open

Hope this helps

 

[smithbr]

What would i have to declare for that to work...?

 

[bsimbeck]

Well you can set up the oledb connection at design time and do the rest programaticly as above.

The things you would need to declare programaticly are everything I delcared with the dim statment.

 

[smithbr]

ok..i went in and put that code behind the click button...when i did...it said that "type "OleDbDataadapter' is not defined" for Dim daInvoice As OleDbdataadapter = New oledbdataadapter(sql, cn)

Also, it says that "fill is not a member of 'System.Data.DataSet'" for
Dim dsInvoice As DataSet = New DataSet
dsInvoice.fill(dsInvoice, "INVOICE&quot

I am not sure what I need to do to fix this.

 

[bsimbeck]

I will write a little tutorial and post it a little later tonight

 

[smithbr]

you are amazing...I appreciate all your help with this.

 

[bsimbeck]

Okay sorry about those mistakes couple of things I forgot to mention so I am going to repost.

First things first on the very top of the code page you need

Imports System.Data.OleDb

This next section assumes you have a form with an oledbconnection setup, a textbox, button, and a label (Please note that I did not change the name on any of the form componants).

in the button's click event you might have the following

'open the db connection
OleDbConnection1.Open()

'write the query to get the data you want
Dim strsql As String = "SELECT * FROM TABLE1 WHERE ID = " & TextBox1.Text

'setup the data adapter and pass to it the sql statment and the connection
Dim da As OleDbDataAdapter = New OleDbDataAdapter(strsql, OleDbConnection1)

'set up the dataset and fill it with data
Dim ds As DataSet = New DataSet()
da.Fill(ds, "TABLE1")

'set up the datatable 
Dim dt As DataTable = ds.Tables("TABLE1")

'get the data from the first row of the datatable(there is only one row) and output it to the label

Label1.Text = dt.Rows(0).Item("DESCP")

That should fix the errors, sorry about that sometimes my fingers get ahead of my brain.

Branden

 

[smithbr]

wow...that helped more than you will ever know.....one more questions for you (don't hold me to that one more question actually... When I run my query it returns 6 columns of data..only one row but 6 different entities. I was wondering exactly how I get each of those entities into the six different labels on the form...

 

[benlinkknilneb]

Try this:

Labeln.Text = ds.Tables("TABLE1&quot.Rows(0)("[yourcolumnname here]&quot

 

[bsimbeck]

It is really pretty easy. Just like benlinkknilneb said or in the form designer add the number of labels you need then just

NameOfLabel.Text = dt.Rows(0).Item("ColName&quot

Where NameOfLabel is the name of the label on the form and ColName is the name of the database column.

 

[smithbr]

One more question for you guys...I have all the code in and thought it should work but then when i run the program it gives me an error message refering to "da.Fill(ds, "AR4_OpenInvoice&quot" in the code with a message that says "An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll" I am not sure what this means or what I need to do to fix it...thanks for all the help!

 

[bsimbeck]

Check your sql statment and make sure that the column and table names are vaild that is usually what causes it for me if you post your code for the button I might be able to give you a better idea.

 

[smithbr]

Imports System.Data.OleDb



Private Sub cmdretrieve_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdretrieve.Click

'open the db connection
OleDbConnection1.Open()

'write the query to get the data you want
Dim strsql As String = "SELECT InvoiceNumber, Division, CustomerNumber, SalespersonCode, NonTaxableAmount FROM AR4_OpenInvoice WHERE InvoiceNumber = " & txtinvnum.Text

'setup the data adapter and pass to it the sql statment and the connection
Dim da As OleDbDataAdapter = New OleDbDataAdapter(strsql, OleDbConnection1)

'set up the dataset and fill it with data
Dim ds As DataSet = New DataSet()
da.Fill(ds, "AR4_OpenInvoice&quot

'set up the datatable
Dim dt As DataTable = ds.Tables("AR4_OpenInvoice&quot

'get the data from the first row of the datatable(there is only one row) and output it to the label

lbldivdisp.Text = dt.Rows(0).Item("Division&quot



End Sub

The name of the dataset i generated when I set up the oledbdataadapter is "Invoices"...am i supposed to change the ds above to "invoices"?

 

[bsimbeck]

The only thing I think of is that if txtinvnum.Text is not a number and InvoiceNumber is a number (you can use cint(txtinvnum.Text) to convert it to an integer.

or

InvoiceNumber is a text field and if that is the case you have to put single quotes around txtinvnum.text

I dont think you have to have table name the same as in the database but you could try that too and see if it helps.

 

[smithbr]

invoicenumber is set up as text is access...thaat fixed it...thank you for all your help

 

[smithbr]

do you know if it is possible to include the quotes in the code that whenthe user enters the invoice number they do not have to type the quotes....i would just switch the properties of the invoice number in access to integer but it is a linked table from MAS90 (accounting software) and I can not change the setting.

 

[bsimbeck]

Yeah it can get a little confusing though but here is your sql query done with the single quotes added.

Dim strsql As String = "SELECT InvoiceNumber, Division, CustomerNumber, SalespersonCode, NonTaxableAmount FROM AR4_OpenInvoice WHERE InvoiceNumber = '" & txtinvnum.Text & "'"

 

[smithbr]

when i did that it gave me the same error it gave when i was not typing the quotes myself in the textbox