Wrox Programmer Forums
Go Back   Wrox Programmer Forums > .NET > Other .NET > General .NET
|
General .NET For general discussion of MICROSOFT .NET topics that don't fall within any of the other .NET forum subcategories or .NET language forums.  If your question is specific to a language (C# or Visual Basic) or type of application (Windows Forms or ASP.Net) try an applicable forum category. ** PLEASE BE SPECIFIC WITH YOUR QUESTION ** When posting here, provide details regarding the Microsoft .NET language you are using and/or what type of application (Windows/Web Forms, etc) you are working in, if applicable to the question. This will help others answer the question without having to ask.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the General .NET section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old December 5th, 2007, 11:16 AM
Authorized User
 
Join Date: Dec 2007
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default Selecting Two Tables in Access Database

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

 
Old December 5th, 2007, 11:21 AM
Wrox Author
 
Join Date: Oct 2005
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

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
================================================== =========
 
Old December 5th, 2007, 02:24 PM
planoie's Avatar
Friend of Wrox
 
Join Date: Aug 2003
Posts: 5,407
Thanks: 0
Thanked 16 Times in 16 Posts
Default

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
 
Old December 6th, 2007, 12:10 AM
Authorized User
 
Join Date: Dec 2007
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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)
 
Old December 6th, 2007, 12:12 AM
Authorized User
 
Join Date: Dec 2007
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Each sub procedure works independently but not together.:(

 
Old December 6th, 2007, 12:59 AM
Wrox Author
 
Join Date: Oct 2005
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

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
================================================== =========
 
Old December 6th, 2007, 08:36 AM
Authorized User
 
Join Date: Dec 2007
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old December 6th, 2007, 09:36 AM
Authorized User
 
Join Date: Dec 2007
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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

 
Old December 6th, 2007, 09:52 AM
Wrox Author
 
Join Date: Oct 2005
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

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
================================================== =========
 
Old December 6th, 2007, 11:04 AM
Authorized User
 
Join Date: Dec 2007
Posts: 10
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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.











Similar Threads
Thread Thread Starter Forum Replies Last Post
creating tables within tables in access??? carswelljr Access 3 August 23rd, 2006 01:21 PM
Chapter 6 Selecting Data in Access ,, Help Me zaidqais BOOK: Beginning Visual Basic 2005 Databases ISBN: 978-0-7645-8894-5 2 May 5th, 2006 03:48 AM
update the database when selecting a chk box ..... urindian ASP.NET 1.x and 2.0 Application Design 1 February 5th, 2005 04:39 PM
selecting records from multiple tables cygnusx04 Classic ASP Databases 1 October 28th, 2004 05:36 AM
Deleting tables in Access Database, if they exist mckly Pro VB Databases 1 August 25th, 2004 11:10 PM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.