Hi Guys,
I find myself needing a little advice again,
Im trying to pull records from my database, I want to pass a string value in my query that will return a record for me (This I can manage)
But the problem I have is,
Each record in the database has a Primary Key (MainId) and also has another 2 fields that get pulled they are (MFatherID) & (MMotherID), these 2 fields match the values held in the (MainId (Primary Key)) colum.
So when I pull my first record I want to look at the values in the other 2 colums (MFatherID) & (MMotherID) look at their value and go back and do another query to pull the records they match from searching the (MainId (Primary Key)) colum.
Now I want this to be done a few times up to 70 records or more would be wanted, the actual amount of records pulled would hopefully be up to the user and I had hoped I could put in a listBox or something to allow the user the choice of how many records to return.
The records I would like to display in a couple of ways, initially if I can just return the records of the recursive query to a datagrid that would be fine, but I would also like to put the record into a report of somekind that will allow me to lay the data out in a way I want to show it.
Initially I started trying to build the querys in the database itsef but what I want seems to complex for access?? I did post on the Access boards first to get some support there but the lads on there thought I needed some recursive code to do this for me.
To see what my database is like the link to the access threads are here
Sorry to be posting a great long post I hope I dont put anyone off from helping me out here.
I have a TestProject at the moment with a form and some code that is doing a basic query and returning a single record or just displaying all the records in a grid, I will post this below in the hope im heading in the right direction and perhaps someone can assist me in the adaptation I need.
Thanks again
Sb
I find myself needing a little advice again,
Im trying to pull records from my database, I want to pass a string value in my query that will return a record for me (This I can manage)
But the problem I have is,
Each record in the database has a Primary Key (MainId) and also has another 2 fields that get pulled they are (MFatherID) & (MMotherID), these 2 fields match the values held in the (MainId (Primary Key)) colum.
So when I pull my first record I want to look at the values in the other 2 colums (MFatherID) & (MMotherID) look at their value and go back and do another query to pull the records they match from searching the (MainId (Primary Key)) colum.
Now I want this to be done a few times up to 70 records or more would be wanted, the actual amount of records pulled would hopefully be up to the user and I had hoped I could put in a listBox or something to allow the user the choice of how many records to return.
The records I would like to display in a couple of ways, initially if I can just return the records of the recursive query to a datagrid that would be fine, but I would also like to put the record into a report of somekind that will allow me to lay the data out in a way I want to show it.
Initially I started trying to build the querys in the database itsef but what I want seems to complex for access?? I did post on the Access boards first to get some support there but the lads on there thought I needed some recursive code to do this for me.
To see what my database is like the link to the access threads are here
Sorry to be posting a great long post I hope I dont put anyone off from helping me out here.
I have a TestProject at the moment with a form and some code that is doing a basic query and returning a single record or just displaying all the records in a grid, I will post this below in the hope im heading in the right direction and perhaps someone can assist me in the adaptation I need.
Thanks again
Sb
Code:
Imports System.Data
Imports System.Data.OleDb
Public Class Form1
'---Form level variables
Private strConnectionString As String = _
"Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source =C:\Documents and Settings\webstaff\My Documents\Visual Studio 2005\Projects\Recursive Test1\main.mdb;"
Private objConnection As OleDbConnection
Private objCommand As OleDbCommand
Private objDataAdaptor As OleDbDataAdapter
Private objDataSet As DataSet
Private Sub PopulateGrid()
'--- Initialise a new instance of the OleDBDataAdaptor class
objDataAdaptor = New OleDbDataAdapter
'---Initialise a new instance of the DataSet class
objDataSet = New DataSet
'--- Set the SelectCommand for the OleDbDataAdaptor
objDataAdaptor.SelectCommand = objCommand
Try
'---Populate the DataSet
objDataAdaptor.Fill(objDataSet, "Generations")
'---Bind the dataset to the datagrid
grdResults.DataSource = objDataSet
grdResults.DataMember = "Generations"
'---Set the AlternatingRowsDefaultCellStyle.BackColor property
grdResults.AlternatingRowsDefaultCellStyle.BackColor = Color.WhiteSmoke
'---Set the CellBorderStyle property
grdResults.CellBorderStyle = DataGridViewCellBorderStyle.None
'---Set the SelctionMode property
grdResults.SelectionMode = DataGridViewSelectionMode.FullRowSelect
'--- Set the AutoSizeColumnsMode property
grdResults.AutoSizeColumnsMode = DataGridViewAutoSizeColumnsMode.AllCells
Catch OleDbException As OleDbException
MessageBox.Show(OleDbException.Message, "Find Generations")
End Try
'---CleanUp
objCommand.Dispose()
objCommand = Nothing
objDataAdaptor.Dispose()
objDataAdaptor = Nothing
objDataSet.Dispose()
objDataSet = Nothing
objConnection.Dispose()
objConnection = Nothing
End Sub
Private Sub btnParimeterQuery_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnParimeterQuery.Click
'---Initialise a new instance of the OleDbConnection class
objConnection = New OleDbConnection(strConnectionString)
'---Initialise a new instance of the OleDbCommand class
objCommand = New OleDbCommand
'---Set the objCommand object properties
objCommand.CommandText = "usp_SelectUser"
objCommand.CommandType = CommandType.StoredProcedure
objCommand.Connection = objConnection
'--- Add the required parimeter for the query
objCommand.Parameters.Add("@MName", OleDbType.VarChar, 50).Value = Replace(txtProjectID.Text, "'", "")
'---Populate the DataGridView
Call PopulateGrid()
End Sub
Private Sub btnSelectAll_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSelectAll.Click
'---Initialise a new instance of the OleDbConnection class
objConnection = New OleDbConnection(strConnectionString)
'---Initialise a new instance of the OleDbCommand class
objCommand = New OleDbCommand
'---Set the objCommand object properties
objCommand.CommandText = "Select * From Main"
objCommand.CommandType = CommandType.Text
objCommand.Connection = objConnection
'---Populate the DataGridView
Call PopulateGrid()
End Sub
End Class