dataset to array help

emiliosil

New member
Joined
Feb 28, 2011
Messages
3
Programming Experience
Beginner
hi,

This is driving me mad, I'm new to programming anyway and I havent been able to solve this for weeks. Im programming a Windows form application using Visual studio 2008 and I was making a custom query form where using a record ID and description and selecting a table to search from (items, customer, manufacturer...) the records that match would show on a text box. First I started by just bringing a whole table back to the text box and then I would refine the SQL query with search terms but got stuck there. The code:

Dim dsQuery AsNew DataSet
Dim daQuery As OleDb.OleDbDataAdapter
Dim Query(Numberofrows, Numberofitems) AsString
Dim Numberofrows AsInteger
Dim Numberofitems AsInteger

PrivateSub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click
Dim QueryConnection AsNew OleDb.OleDbConnection
Dim sqlQuery AsString
Dim table AsString
Dim Numberofrows AsInteger
Dim Numberofitems AsInteger
Provider = "PROVIDER=Microsoft.ACE.OLEDB.12.0;"
dbSource = "Data Source = E:\nutriespana\database.accdb"
QueryConnection.ConnectionString = Provider & dbSource
table = ""
SelectCase cboTable.SelectedIndex()
Case 0
table = "tblCustomers"
Case 1
table = "tblManufacturers"
Case 2
table = "tblItems"
Case 3
table = "tblOrders"
CaseElse
MsgBox("Select a table please.")
EndSelect
MsgBox(table)
'Open the database
QueryConnection.Open()
'Use sql to get the data from the database table
sqlQuery = "SELECT * FROM " & table
'Set up data adapter
daQuery = New OleDb.OleDbDataAdapter(sqlQuery, QueryConnection)
'Use the data adapter to put the data into the dataset
daQuery.Fill(dsQuery, "Query")
'Close the connection
QueryConnection.Close()
'Count the number of rows in the dataset table
Numberofrows = dsQuery.Tables("Query").Rows.Count
Numberofitems = dsQuery.Tables("Query").Columns.Count
MsgBox(Numberofitems)
MsgBox(Numberofrows)
'Set the row number to 0 so the first record will be displayed
'Call the displaydata subroutine which will put data into the text boxes
DisplayData()
BtnSave.Visible = True
BtnSave.Enabled = True
BtnPrint.Visible = True
BtnPrint.Enabled = True
EndSub

PublicSub DisplayData()
'Put query data into the textbox on the form from the dataset
Dim ItemNumber AsInteger
Dim RowNumber AsInteger
ItemNumber = 1
RowNumber = 0
DoUntil RowNumber = Numberofrows
DoUntil ItemNumber = Numberofitems
Query(RowNumber, ItemNumber - 1) = dsQuery.Tables("Query").Rows(RowNumber).Item(ItemNumber)
ItemNumber = ItemNumber + 1
If ItemNumber = Numberofitems Then
RowNumber = RowNumber + 1
ItemNumber = 1
EndIf
Loop
Loop
TxtQuery.Text = Query(0, 1) & " " & Query(0, 2) & " " & " " & Query(0, 4)
EndSub


I am using an array to assign variables and that is where the errors appear, i used breakpoints and message boxes and outcome is fine until the loop, i need to use the array because the size of the tables is different depending on which one is selected on the combo box, also i wanted to do it in an "elegant way" rather than just assigning every piece of data 1 by 1 Anyway, the code isn't finished i was planning of using another loop to display the assigned data in the textbox.
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,496
Location
Sydney, Australia
Programming Experience
10+
Please use the Code button provided in the advanced editor to wrap your code snippets in formatting tags or else type them in yourself. Long code snippets with no indenting are very unpleasant to read.
 

emiliosil

New member
Joined
Feb 28, 2011
Messages
3
Programming Experience
Beginner
VB.NET:
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] dsQuery [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] DataSet[/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] daQuery [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] OleDb.OleDbDataAdapter[/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] Query(Numberofrows, Numberofitems)[/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] Numberofrows [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Integer[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] Numberofitems [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Integer[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]-------------------------------------------
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] btnSearch_Click([/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] sender [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] System.Object, [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] e [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] System.EventArgs) [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Handles[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] btnSearch.Click[/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] QueryConnection [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] OleDb.OleDbConnection[/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] sqlQuery [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] table [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] Numberofrows [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Integer[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] Numberofitems [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Integer[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2]Provider = [/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"PROVIDER=Microsoft.ACE.OLEDB.12.0;"[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2]dbSource = [/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"Data Source = E:\nutriespana\database.accdb"[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2]QueryConnection.ConnectionString = Provider & dbSource[/SIZE]
[SIZE=2]table = [/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]""[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Select[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Case[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] cboTable.SelectedIndex()[/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Case[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] 0[/SIZE]
[SIZE=2]table = [/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"tblCustomers"[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Case[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] 1[/SIZE]
[SIZE=2]table = [/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"tblManufacturers"[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Case[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] 2[/SIZE]
[SIZE=2]table = [/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"tblItems"[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Case[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] 3[/SIZE]
[SIZE=2]table = [/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"tblOrders"[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Case[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Else[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2]MsgBox([/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"Select a table please."[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2])[/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Select[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]'Open the database[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2]QueryConnection.Open()[/SIZE]
[SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]'Use sql to get the data from the database table[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2]sqlQuery = [/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"SELECT * FROM "[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] & table[/SIZE]
[SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]'Set up data adapter[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2]daQuery = [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] OleDb.OleDbDataAdapter(sqlQuery, QueryConnection)[/SIZE]
[SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]'Use the data adapter to put the data into the dataset[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2]daQuery.Fill(dsQuery, [/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"Query"[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2])[/SIZE]
[SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]'Close the connection[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2]QueryConnection.Close()[/SIZE]
[SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]'Count the number of rows in the dataset table[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2]Numberofrows = dsQuery.Tables([/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"Query"[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]).Rows.Count[/SIZE]
[SIZE=2]Numberofitems = dsQuery.Tables([/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"Query"[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]).Columns.Count[/SIZE]
[SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]'Set the row number to 0 so the first record will be displayed[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]'Call the displaydata subroutine which will put data into the text boxes[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2]DisplayData()[/SIZE]
[SIZE=2]BtnSave.Visible = [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]True[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2]BtnSave.Enabled = [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]True[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2]BtnPrint.Visible = [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]True[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2]BtnPrint.Enabled = [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]True[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][/COLOR][/SIZE]
[SIZE=2][COLOR=#0000ff]-------------------------
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Public[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] DisplayData()[/SIZE]
[SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]'Put query data into the textbox on the form from the dataset[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] ItemNumber [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Integer[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] RowNumber [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Integer[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2]ItemNumber = 1[/SIZE]
[SIZE=2]RowNumber = 0[/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Do[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Until[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] RowNumber = Numberofrows[/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Do[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Until[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] ItemNumber = Numberofitems[/SIZE]
[SIZE=2]Query(RowNumber, ItemNumber - 1) = dsQuery.Tables([/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"Query"[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]).Rows(RowNumber).Item(ItemNumber)[/SIZE]
[SIZE=2]ItemNumber = ItemNumber + 1[/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] ItemNumber = Numberofitems [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Then[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2]RowNumber = RowNumber + 1[/SIZE]
[SIZE=2]ItemNumber = 1[/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Loop[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Loop[/COLOR][/SIZE]
[/COLOR][/SIZE][SIZE=2]TxtQuery.Text = Query(0, 1) & [/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]" "[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] & Query(0, 2) & [/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]" "[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] & [/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]" "[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] & Query(0, 4)[/SIZE]
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE]
[/COLOR][/SIZE]
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,496
Location
Sydney, Australia
Programming Experience
10+
That's still no good because there's no indenting. Syntax highlighting is nice but indenting is significantly more important when it comes to readability. Also, the use of blanks lines is very important too. You haven't got a single blank line in your code snippet and that makes it harder to read, especially when there's no indenting.
 

emiliosil

New member
Joined
Feb 28, 2011
Messages
3
Programming Experience
Beginner
VB.NET:
[SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] dsQuery [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] DataSet[/SIZE]
 [SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] daQuery [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] OleDb.OleDbDataAdapter[/SIZE]
 [SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] Query(Numberofrows, Numberofitems) [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE]
 [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] Numberofrows [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Integer[/COLOR][/SIZE]
 [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] Numberofitems [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Integer[/COLOR][/SIZE][/COLOR][/SIZE]
 [SIZE=2][COLOR=#0000ff]'---------------------------------------------------------------------
 [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Private[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] btnSearch_Click([/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] sender [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] System.Object, [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]ByVal[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] e [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] System.EventArgs) [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Handles[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] btnSearch.Click[/SIZE]
 [SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] QueryConnection [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] OleDb.OleDbConnection[/SIZE]
 [SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] sqlQuery [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE]
 [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] table [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]String[/COLOR][/SIZE]
 [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] Numberofrows [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Integer[/COLOR][/SIZE]
 [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] Numberofitems [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Integer

[/COLOR][/SIZE] [/COLOR][/SIZE][SIZE=2]Provider = [/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"PROVIDER=Microsoft.ACE.OLEDB.12.0;"[/COLOR][/SIZE]
 [/COLOR][/SIZE][SIZE=2]dbSource = [/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"Data Source = E:\nutriespana\database.accdb"[/COLOR][/SIZE]
 [/COLOR][/SIZE][SIZE=2]QueryConnection.ConnectionString = Provider & dbSource[/SIZE]
 [SIZE=2]table = [/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]""[/COLOR][/SIZE]
 [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]
Select[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Case[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] cboTable.SelectedIndex()[/SIZE][INDENT][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Case[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] 0[/SIZE][SIZE=2]
table = [/SIZE][SIZE=2][COLOR=#a31515]"tblCustomers"[/COLOR][/SIZE]
 [/INDENT][INDENT][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Case[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] 1[/SIZE][SIZE=2]
table = [/SIZE][SIZE=2][COLOR=#a31515]"tblManufacturers"[/COLOR][/SIZE]
 [/INDENT][INDENT][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Case[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] 2[/SIZE][SIZE=2]
table = [/SIZE][SIZE=2][COLOR=#a31515]"tblItems"[/COLOR][/SIZE]
 [/INDENT][INDENT][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Case[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] 3[/SIZE][SIZE=2]
table = [/SIZE][SIZE=2][COLOR=#a31515]"tblOrders"[/COLOR][/SIZE]
 [/INDENT][INDENT][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Case[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Else[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]
MsgBox([/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"Select a table please."[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2])[/SIZE]
[/INDENT][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Select
[/COLOR][/SIZE][/COLOR][/SIZE]
 [SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]'Open the database[/COLOR][/SIZE]
 [/COLOR][/SIZE][SIZE=2]QueryConnection.Open()[/SIZE]
 [SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]'Use sql to get the data from the database table[/COLOR][/SIZE]
 [/COLOR][/SIZE][SIZE=2]sqlQuery = [/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"SELECT * FROM "[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] & table[/SIZE]
 [SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]'Set up data adapter[/COLOR][/SIZE]
 [/COLOR][/SIZE][SIZE=2]daQuery = [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]New[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] OleDb.OleDbDataAdapter(sqlQuery, QueryConnection)[/SIZE]
 [SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]'Use the data adapter to put the data into the dataset[/COLOR][/SIZE]
 [/COLOR][/SIZE][SIZE=2]daQuery.Fill(dsQuery, [/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"Query"[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2])[/SIZE]
 [SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]'Close the connection[/COLOR][/SIZE]
 [/COLOR][/SIZE][SIZE=2]QueryConnection.Close()[/SIZE]
 [SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]'Count the number of rows in the dataset table[/COLOR][/SIZE]
 [/COLOR][/SIZE][SIZE=2]Numberofrows = dsQuery.Tables([/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"Query"[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]).Rows.Count[/SIZE]
 [SIZE=2]Numberofitems = dsQuery.Tables([/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"Query"[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]).Columns.Count[/SIZE]
 
 [SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]'Set the row number to 0 so the first record will be displayed[/COLOR][/SIZE]
 [/COLOR][/SIZE][SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]'Call the displaydata subroutine which will put data into the text boxes

[/COLOR][/SIZE] [/COLOR][/SIZE][SIZE=2]DisplayData()[/SIZE]

 [SIZE=2]BtnSave.Visible = [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]True[/COLOR][/SIZE]
 [/COLOR][/SIZE][SIZE=2]BtnSave.Enabled = [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]True[/COLOR][/SIZE]
 [/COLOR][/SIZE][SIZE=2]BtnPrint.Visible = [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]True[/COLOR][/SIZE]
 [/COLOR][/SIZE][SIZE=2]BtnPrint.Enabled = [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]True[/COLOR][/SIZE]
 [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE]
 [/COLOR][/SIZE]'-----------------------------------------------------------
 [SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Public[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Sub[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] DisplayData()[/SIZE]
 [SIZE=2][COLOR=#008000][SIZE=2][COLOR=#008000]'Put query data into the textbox on the form from the dataset[/COLOR][/SIZE]
 [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] ItemNumber [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Integer[/COLOR][/SIZE]
 [/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Dim[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] RowNumber [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]As[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Integer[/COLOR][/SIZE]
 [/COLOR][/SIZE][SIZE=2]ItemNumber = 1[/SIZE]
 [SIZE=2]RowNumber = 0[/SIZE]

 [SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Do[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Until[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] RowNumber = Numberofrows[/SIZE][INDENT][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Do[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Until[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] ItemNumber = Numberofitems[/SIZE][SIZE=2]
Query(RowNumber, ItemNumber - 1) = dsQuery.Tables([/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]"Query"[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]).Rows(RowNumber).Item(ItemNumber)[/SIZE][SIZE=2]
ItemNumber = ItemNumber + 1[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]
[/COLOR][/SIZE][/COLOR][/SIZE][INDENT][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] ItemNumber = Numberofitems [/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Then[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2]
RowNumber = RowNumber + 1[/SIZE][SIZE=2]
ItemNumber = 1[/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]
End[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]If[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]
[/COLOR][/SIZE][/INDENT][/INDENT][INDENT][SIZE=2][COLOR=#0000ff]Loop[/COLOR][/SIZE][SIZE=2][COLOR=#0000ff]
[/COLOR][/SIZE][/INDENT][SIZE=2][COLOR=#0000ff]Loop[/COLOR][/SIZE]
[SIZE=2]TxtQuery.Text = Query(0, 1) & [/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]" "[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] & Query(0, 2) & [/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]" "[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] & [/SIZE][SIZE=2][COLOR=#a31515][SIZE=2][COLOR=#a31515]" "[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2] & Query(0, 4)[/SIZE]
 [SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]End[/COLOR][/SIZE][/COLOR][/SIZE][SIZE=2][COLOR=#0000ff][SIZE=2][COLOR=#0000ff]Sub
[/COLOR][/SIZE][/COLOR][/SIZE]
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,496
Location
Sydney, Australia
Programming Experience
10+
Much better. Now, with that out of the way, is there a particular reason that you chose to use a TextBox? If you want to display tabular data then there are significantly better options. My first choice would be a DataGridView. You populate a DataTable and then assign it to the grid's DataSource. In most cases, that's it. Everything else is done for you. In more complex cases you will still have to a bit of extra work but it's not too hard and the end result will be much more pleasant to look at than a TextBox.
 
Top Bottom