Selecting from different tables. Pls Help..Thanx

[sand]

Hi,

I have a doubt. I am using microsoft access as my database. I want to extract various information from different tables based on one primary key, the user id. What i want is to extract information from the two tables based on the user id.

I also want deatils displayed only if present date corresponds to the date in another table. The matchNo corresponding to that date should also be the same as that of corresponding to the userid.

I have written the following code but i am not sure if i am correct. I hope someone can guide me. Thanx

P

rivate Sub btnSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
        Dim dbCon As New OleDbConnection(ConnectionString)
        Dim dbCommand As OleDbCommand
        Dim MatricNo As String
        Dim day As Date
        Dim MatchNo As New Integer
        MatricNo = txtPMatricNo.Text
        day = day.Date
        dbCon.Open()
        dbCommand = New OleDbCommand("SELECT * FROM MatchesWSinglesRound1,MatchesMDoublesRound1,Matches WHERE MatchesMDoublesRound1.MatricNo = '" & MatricNo & "', MatchesWSinglesRound1 = '" & MatricNo & "',Matches.MatchDay = '" & day & "',Matches.MatchNo = '" & MatchesWSinglesRound1.MatchNo & "',Matches.MatchNo = '" & MatchesMDoublesRound1.MatchNo & "'", dbCon)
        dbCommand.ExecuteNonQuery()
        dbCommand.Dispose()
        dbCon.Close()
    End Sub

[Zakary]

Hi Sand !

First of all, your query is not good. You may use MS Access Query builder to be assisted in building it or event the Configuation wizard on the ADO.NET DataAdapter

In the WHERE clause, separate each Critera with the AND or OR keyword
you may also look on MSDN web site to learn how it work.

And you should'nt use ExecuteNonQuery to run a query, ExecuteNonQuery is for UPDATE, DELETE, or INSERT query, everything that do non return data

here is a function that i've build to return a DataSet from a SQL Query, note that this fonction is for SQL Server database, but it do not change much for other DB

Public Function GetDataSet(ByVal pCommandText As String, ByVal pCommandType As CommandType, ByVal pParamArray As ArrayList) As DataSet
 
        Dim cmd As SqlCommand = New SqlCommand
        Dim Param As SqlParameter = New SqlParameter
        Dim ds As DataSet = New DataSet
        Dim intBoucle As Integer
 
        Dim dad As SqlDataAdapter
 
        For intBoucle = 0 To pParamArray.Count - 1
            cmd.Parameters.Add(pParamArray(intBoucle))
        Next
 
        cmd.Connection = New SqlClient.SqlConnection(conConnection.ConnectionString)
 
        cmd.CommandText = pCommandText
        cmd.CommandType = pCommandType
 
        dad = New SqlDataAdapter(cmd)
 
        dad.Fill(ds)
 
        Return ds
 
    End Function

[sand]

Hi,

I dun quite understand your code. Could you pls explain. Thanx

[sand]

I believe i figured out what you actually meant, you are trying to say the following?

Private Sub btnSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
        Dim dbCon As New OleDbConnection(ConnectionString)
        Dim dbAdapter As New OleDbDataAdapter
        Dim MatricNo As String
        Dim day As Date
        Dim MatchNo As New Integer
        MatricNo = txtPMatricNo.Text
        day = day.Date
        dbAdapter.SelectCommand = New OleDbCommand("SELECT * FROM MatchesWSinglesRound1,MatchesMDoublesRound1,Matches WHERE MatchesMDoublesRound1.MatricNo = '" & MatricNo & "', MatchesWSinglesRound1 = '" & MatricNo & "',Matches.MatchDay = '" & day & "',Matches.MatchNo = '" & MatchesWSinglesRound1.MatchNo & "'", dbCon)
    End Sub

However, I can't figure out how to extract only the data 'MatchNo' in the 'Matches' table corresponds to that of the 'MatchesWSinglesRound1' and 'MatchesMDoublesRound1' tables?

[Zakary]

Sure ! No problem!
Here a version simplified


If you need more info on how ADO.NET work i'll suggest you to visit this thread

Public Function GetDataSet(ByVal pSQLCommand As String) As DataSet
        '**********************************************
        ' Parameter
        '**********************************************
        ' pSQLCommand -> The SQL Query to run
        ' 
 
        ' Define a new command object
        Dim cmd As SqlCommand = New SqlCommand
 
        ' define the DataSet that will be return
        Dim ds As DataSet = New DataSet
 
        ' Define de DataAdapter that will handle the command
        Dim dad As SqlDataAdapter
 
 
        ' Set the BD connection to the Command object
        cmd.Connection = New SqlClient.SqlConnection(conConnection.ConnectionString)
 
        ' Set the SQL Query to run to the command  object
        cmd.CommandText = pSQLCommand
        ' Set the type of command to be run; DirectTable, Text, or StoreProc
        cmd.CommandType = CommandType.Text
 
        'Set the command to the DataAdapter so , the DataADatpetr will know witch SELECT command to run 
        dad = New SqlDataAdapter(cmd)
 
        ' Lanch the Fill method to fill the DataSet
        dad.Fill(ds)
 
        ' return the DataSet to the Calling procedure
        Return ds
 
    End Function

[Zakary]

I believe i figured out what you actually meant, you are trying to say the following?

Private Sub btnSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
        Dim dbCon As New OleDbConnection(ConnectionString)
        Dim dbAdapter As New OleDbDataAdapter
        Dim MatricNo As String
        Dim day As Date
        Dim MatchNo As New Integer
        MatricNo = txtPMatricNo.Text
        day = day.Date
        dbAdapter.SelectCommand = New OleDbCommand("SELECT * FROM MatchesWSinglesRound1,MatchesMDoublesRound1,Matches WHERE MatchesMDoublesRound1.MatricNo = '" & MatricNo & "', MatchesWSinglesRound1 = '" & MatricNo & "',Matches.MatchDay = '" & day & "',Matches.MatchNo = '" & MatchesWSinglesRound1.MatchNo & "'", dbCon)
    End Sub

However, I can't figure out how to extract only the data 'MatchNo' in the 'Matches' table corresponds to that of the 'MatchesWSinglesRound1' and 'MatchesMDoublesRound1' tables?

You're neer the true with this, but the SQL Statement is but good ... what a sec i'll be back with a corrected one shortly

[Zakary]

Here is with the SQL Corrected, i can't tell if it bug free, cause i can't test it

but it is a idea ...

Private Sub btnSubmit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSubmit.Click
        Dim dbCon As New OleDbConnection(ConnectionString)
        Dim dbAdapter As New OleDbDataAdapter
        Dim ds As DataSet = New DataSet
 
        Dim MatricNo As String
        Dim day As Date
        Dim MatchNo As New Integer
        Dim SQL As String
        
        MatricNo = txtPMatricNo.Text
        day = day.Date
 
        SQL = "SELECT * "        ' Here you should specifie exaclty witch field you wich
        SQL &= " FROM "
        SQL &= "    MatchesWSinglesRound1 ,"
        SQL &= "    MatchesMDoublesRound1,"
        SQL &= "    Matches "
        SQL &= " WHERE "
        SQL &= "    MatchesMDoublesRound1.MatricNo = MatchesWSinglesRound1.MatricNo "
        SQL &= "    AND Matches.MatchNo = MatchesWSinglesRound1.MatchNo"
        SQL &= "    AND MatchesMDoublesRound1.MatricNo = '" & MatricNo & "'"
        SQL &= "    AND Matches.MatchDay = '" & day & "'"
 
        dbAdapter.SelectCommand = New OleDbCommand(SQL, dbCon)
        dbAdapter.fill(ds)
' Now you may use ds to handle the data return by the query
 
    End Sub

[sand]

Hi,
Sorry for the late reply.

So if i want to display a field from any one of the tables stated in the sql statement i just write as follows?

txtMatchDate.Text = Matches.MatchDay

The above should display the match day based on the select statement right? However the word in red is highlighted. Is there another way of displaying the data in textbox instead of table?

[Zakary]

You may access the data in the DataSet called "ds" by using Item

ds.Tables(0).Item("FieldName")


I realy suggest you to look at this tread to figure a bit how ADO.NET work

[sand]

Hi,

I read the thread as suggested. I installed the examples, but i can't find it in my comp after installing it. How do i access the files after installing it?