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.
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.
