Selecting Two Tables in Access Database

hollertrek · December 5th, 2007, 11:16 AM

Is it best or possible to select both tables (SELECT * Tables) on the class level or should they be selected separately on sub procedure levels. I was trying to avoid declaring variables again in subs. Can you write something like SELECT * tblCustomer and tblOrder?
Thx,
hollertrek

dparsons · December 5th, 2007, 11:21 AM

In SQL Server you would do:

SELECT * from <table>, <table>

I imagine the Syntax for access isn't much different.

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
.: Wrox Technical Editor :.
Wrox Books 24 x 7
================================================== =========

planoie · December 5th, 2007, 02:24 PM

Danger Will Robinson!

If you do this query: SELECT * FROM Table1, Table2
you will get EVERY record from the first table TIMES every record from the second table. I.E. if you have 1000 records in each table you'll get a result set of 1,000,000 records. I don't think this is what you're looking for.

In SQL Server you can do something like this:

SELECT * FROM Table1; SELECT * FROM Table2

Note the ; between the queries. I am pretty sure that if you execute this query using a DataAdapter and a DataSet, the DataSet will have two DataTables in it, one for each independent result set.

-Peter

hollertrek · December 6th, 2007, 12:10 AM

Please take a look at my code. My logic is suspect because I can't get the 2nd sub procedure to execute.

Code:

Class Level
   Dim strConn As String = "Provider = Microsoft.Jet.OLEDB.4.0;" & _
    "Data Source =" & Application.StartupPath & "\..\Pizza.MDB"
    Dim strSql As String = "SELECT * FROM tblOrder: SELECT * FROM tblCustomer"
    Dim DatCust As New DataTable

Local Level
Private Sub btnOrder_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnOrder.Click

        intOrderNumber = intOrderNumber + 1
        lblOrderNumber.Text = intOrderNumber.ToString

        CustomerTableFill()

        OrderTableFill()
End Sub

Two Sub Procedures
Private Sub CustomerTableFill()

        Dim cnCust As New OleDb.OleDbConnection()
        Dim cmdCust As New OleDb.OleDbCommand()
        Dim intResult As Integer
        cnCust.ConnectionString = strConn
        cnCust.Open()
        cmdCust = cnCust.CreateCommand()
        'Insert INTO table (fld1, fld2, fld3) values('A', 'B', 'C')
        strSql = "INSERT INTO tblCustomer (fldTelephone, fldName,  fldAddr1,   fldAddr2,   fldCity,    fldState,   fldZip) "
        strSql = strSql & "VALUES ('" & txtTelephone.Text & "'"
        strSql = strSql & ",'" & txtName.Text & "'"
        strSql = strSql & ",'" & txtAddress1.Text & "'"
        strSql = strSql & ",'" & txtAddress2.Text & "'"
        strSql = strSql & ",'" & txtCity.Text & "'"
        strSql = strSql & ",'" & txtState.Text & "'"
        strSql = strSql & ",'" & txtZipCode.Text & "')"

        'Execute the insert command
        cmdCust.CommandText = strSql
        Try
            intResult = cmdCust.ExecuteNonQuery()
            MessageBox.Show("Customer Added!", "Successful Save")
            fillDataTable()         'refresh the dataTable after insert
        Catch ex As Exception
            MessageBox.Show("Customer Not Added!  Possible duplicate customer number.", "Save Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try

        cmdCust.Dispose()
        cnCust.Dispose()

    End Sub

Private Sub OrderTableFill()

        Dim tmpPhone As String = Me.txtTelephone.Text
        tmpPhone = tmpPhone.Replace("(", "")
        tmpPhone = tmpPhone.Replace(")", "")
        tmpPhone = tmpPhone.Replace("-", "")

        Dim cnCust As New OleDb.OleDbConnection()
        Dim cmdCust As New OleDb.OleDbCommand()
        Dim intResult As Integer
        cnCust.ConnectionString = strConn
        cnCust.Open()
        cmdCust = cnCust.CreateCommand()
        Dim tmpDate As String = Now.ToShortDateString
        'Insert INTO table (fld1, fld2, fld3) values('A', 'B', 'C')
        strSql = "INSERT INTO tblOrder (fldPhoneNo, fldOrderNo, fldOrderDate) "
        strSql = strSql & "VALUES ('" & tmpPhone & "'"
        strSql = strSql & ",'" & lblOrderNumber.Text & "'"
        strSql = strSql & ",'" & tmpDate & "')"

        'Execute the insert command
        cmdCust.CommandText = strSql

        Try
            intResult = cmdCust.ExecuteNonQuery()
            MessageBox.Show("Order Placed!", "Successful Save")
            fillDataTable()         'refresh the dataTable after insert
        Catch ex As Exception
            MessageBox.Show("Order Not Added!  Possible duplicate customer number.", "Save Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
        End Try

        cmdCust.Dispose()
        cnCust.Dispose()

    End Sub

Last Sub Procedure
  Private Sub fillDataTable()
        Dim odaCust As OleDb.OleDbDataAdapter
        odaCust = New OleDb.OleDbDataAdapter(strSql, strConn)
        DatCust.Clear()         'clear contents of datatable
        odaCust.Fill(DatCust)
        odaCust.Dispose()
    End Sub

Thx,
Hollertrek (Jon)

hollertrek · December 6th, 2007, 12:12 AM

Each sub procedure works independently but not together.:(

dparsons · December 6th, 2007, 12:59 AM

Have you stepped through the code to see exactly what is going on?

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
.: Wrox Technical Editor :.
Wrox Books 24 x 7
================================================== =========

hollertrek · December 6th, 2007, 08:36 AM

Please allow me a stupid question, but have been afraid to ask. I've not have much success at stepping through and I understand the process of stepping through the code sequentially, but I've never understood when it's telling me I have a problem. Does the error suddenly appear when errant line of code is reached? Please illuminate.

Jon

hollertrek · December 6th, 2007, 09:36 AM

Also, does my approach appear correct with respect to using sub procedures and the sequence of components to fill separate tables? If it does then I can concentrate on logistical errors and syntax on the detail level. Thanks

dparsons · December 6th, 2007, 09:52 AM

First about stepping through code: If you are using one of the MS IDE's (Visual Studio or any of the express products) to begin stepping through your code you need to set a breakpoint. This is done by moving your mouse to the far left portion of your code editor and clicking in this margin (a red dot will appear.) Start your code in debug mode (press F5) and when your code reaches the breakpoint, your IDE will take focus away from your app and allow you to step through your code line by line so that you can see what is going on. (When stepping through press F11 to advance from line to to line)
In the case of your code above I would place a breakpoint where you call your button's click event so that you can see the entire execution of both of your routines.

The only thing that I do not agree with is that you call fillDataTable() in both of your routines which effectively fills your dataset twice everytime the Click event is raised. I would remove this line from your routines and alter your method like this:

CustomerTableFill()
OrderTableFill()
fillDataTable()

hth.

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
.: Wrox Technical Editor :.
Wrox Books 24 x 7
================================================== =========

hollertrek · December 6th, 2007, 11:04 AM

Ok, this narrows it down. I tried your suggestion and received a new error message, an exception in the following:

Private Sub fillDataTable()

odaCust.Fill(DatCust) ' this line

Error: The field is too small to accept the amount of data you attempted to add. Try inserting or pasting less data.

This is a odd error because both routines worked independently, but at least its a start.