Help with VB.NET ADO and Access

Hi Experts,
I have a routine to do using VB.NET 2003 and ADO that is a little unusual (at least I think it is unusual). I have been struggling with this all week and need to show my boss some progress very soon.

I have two tables in a MS Access Database: MainTable and SubTable. MainTable contains about 20 columns, SubTable contains 5 of the columns from the MainTable plus 2 additional columns one for a raw number (RAW) that is generated and the other for a scaled number (SCALED) based on a calculation done to the RAW number. So the SubTable is not just a subset of MainTable.

The user can make a lot of changes to the MainTable before this routine runs so I need to do a few things in a certain order:

1. I need to query the MainTable to get just the columns I want for every record in the MainTable
2. I need to delete all of the records in the SubTable because the user may have changed the MainTable since the last time this was run.
3. I need to add new records to the SubTable based on the query of the MainTable in Step 1
4. I need to add the data for the RAW number to every record in the SubTable
5. I need to add the SCALED number to every record in the SubTable
6. I need to display the contents of the SubTable in a DataGrid.

I think all of this could be done in a single form code but I am LOST!

Please help - Examples would be valuable

Thanks,
Charlie
charlieb01Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

SanclerCommented:
This is a pretty wide question.  It's difficult to know how much you already know and what, in detail, you need help with.  So have a read of what follows and then come back if you need to.

You'll need to connect to the Access database.  I'll assume for the moment that you know how to do that.  Then, using your numbering

1)  Set up a dataadapter, using the connection, with a Select statement on these lines

   Dim strSql As String = "SELECT FROM MainTable <thisField>, <thatField>, <theOtherField> WHERE <queryField> = <queryValue>"

and fill a datatable with it.  You fill in the bits in <>.  You will need (at least) the five fields that appear in both tables.

2)  Set up a command, using the connection, with CommandText on these lines

   Dim strSql As String = "DELETE * FROM SubTable"

and run ExecuteNonQuery on it.  With ExecuteNonQuery - unlike with a DataAdapter - you will need explicitly to Open and Close the Connection.

3)  Set up a different dataadapter, using the connection, with a Select statement on these lines

   Dim strSql As String = "SELECT * FROM SubTable"

and fill a datatable with it.  Cycle through the records in the Main datatable and add new records to the new Sub datatable.  On these lines

   For Each dr As DataRow In myMainDataTable
       Dim drn As DataRow = mySubDataTable.NewRow
       'transfer the five fields that are the same in the two tables
       For i As Integer = 0 To 4
           drn.Item(i) = dr.Item(i)
       Next
       mySubTable.Rows.Add(drn)
   Next

That assumes that the Main datatable contains only, and in the right order, the fields that have to go in the Sub datatable.  If that assumption is wrong, the code will need changing.

4)  Cycle through the rows in the Sub datatable adding the raw values.

5)  Cycle through the rows in the Sub datatable adding the scaled values.

6)  Bind the Sub datatable to your datagrid, on these lines

   myDataGrid.DataSource = mySubDataTable.

You might be able to combine steps 3, 4 and 5 - filling in the Raw and the Scaled Items at the same time as you create the Sub datatable row and copy the Main datatable values to it.

Although you don't actually say that you eventually want to save the new data in the Sub datatable, if you do you will need to configure the relevant dataadapter to do that.  You could use a commandbuilder to do that (assuming it has a recognisable unique key) on these lines

   Dim cb As New OleDbCommandBuilder(<mySubTableDataAdapter>)

And then, when you want to update

   <mySubTableDataAdapter>.Update(<mySubDataTable>)

Finally, I should stress that I've coded all the above "onto the screen", so it's not tested.

Roger
0
SanclerCommented:
Correction.  This

Dim strSql As String = "SELECT FROM MainTable <thisField>, <thatField>, <theOtherField> WHERE <queryField> = <queryValue>"

should read

Dim strSql As String = "SELECT <thisField>, <thatField>, <theOtherField> FROM MainTable WHERE <queryField> = <queryValue>"

Roger
0
charlieb01Author Commented:
Hello Roger,

I have put together some code based on your example and also some info I got from a book. I put several datagrids on the form to see what datatables were holding and all of the datagrids appear to have the correct information. I addition, the existing data in the sub-table (Inst Check Sensors EMU) does get deleted.

I am having a problem with the FOR loop in the code below. When I entered this code the environment tells me:

'Expression is of type "System.data.data.table" which is not a collection type'
Here is my code; (Please excuse the strange coding as I was getting confused and just started giving vars numeric names to try to keep track of what was done first.)

Any Suggestions on what I am missing or doing wrong?

Thanks,
Charlie

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim constr As String = "Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDB:Database L" & _
                "ocking Mode=1;Jet OLEDB:Database Password=;Data Source=""c:\test\test101\database\tescor.mdb"";Password=;Jet OLEDB:Engine Type=5;Jet" & _
                " OLEDB:Global Bulk Transactions=1;Provider=""Microsoft.Jet.OLEDB.4.0"";Jet OLEDB:S" & _
                "ystem database=;Jet OLEDB:SFP=False;Extended Properties=;Mode=Share Deny None;Je" & _
                "t OLEDB:New Database Password=;Jet OLEDB:Create System Database=False;Jet OLEDB:" & _
                "Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=Fals" & _
                "e;User ID=Admin;Jet OLEDB:Encrypt Database=False"
       
        Dim conn As New OleDb.OleDbConnection(constr)
        Dim strSql As String
        Dim strSql101 As String
        Dim dataSet101 As New DataSet
        dataSet101 = New DataSet("dataSet101")

        strSql = "SELECT * FROM AnalogInputDefinition"
        strSql101 = "SELECT SensorID, NameLong, Unit, LowRange, HighRange FROM AnalogInputDefinition ORDER BY OrderNum"

        Dim da As New OleDb.OleDbDataAdapter(strSql, conn)
        Dim dt As New DataTable
        da.Fill(dt)

        DataGrid1.DataSource = dt
        DataGrid1.Update()

        Dim Connection101 As OleDb.OleDbConnection = New OleDb.OleDbConnection(constr)
        Dim Command101 As OleDb.OleDbCommand = New OleDb.OleDbCommand(strSql101)
        Command101.CommandType = CommandType.Text

        Connection101.Open()
        Command101.Connection = Connection101


        Dim OleDbDataAdapter101 As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter
        OleDbDataAdapter101.SelectCommand = Command101
        OleDbDataAdapter101.Fill(dataSet101, "AnalogInputDefinition")
        Dim dt101 As New DataTable
        Dim ds505 As New DataSet("ds505")
        OleDbDataAdapter101.Fill(dt101)
        OleDbDataAdapter101.Fill(ds505)
        DataGrid2.DataSource = dt101

        DataGrid4.DataSource = ds505
        DataGrid4.Update()



        DataGrid3.SetDataBinding(dataSet101, "AnalogInputDefinition")

        Dim strSql202 = "SELECT * FROM [Inst Check Sensors EMU]"
        Dim da202 As New OleDb.OleDbDataAdapter(strSql202, conn)
        Dim dt202 As New DataTable
        Dim ds202 As New DataSet("ds202")

        Dim strSql303 = "DELETE * FROM [Inst Check Sensors EMU]"
        Dim Connection303 As OleDb.OleDbConnection = New OleDb.OleDbConnection(constr)
        Dim Command303 As OleDb.OleDbCommand = New OleDb.OleDbCommand(strSql303)
        Command101.CommandType = CommandType.Text

        Connection303.Open()
        Command303.Connection = Connection303
        Command303.ExecuteNonQuery()
        Connection303.Close()

        Dim strSql404 = "SELECT * FROM [Inst Check Sensors EMU]"
        Dim da404 As New OleDb.OleDbDataAdapter(strSql404, conn)
        Dim dt404 As New DataTable
        Dim ds404 As New DataSet("ds404")
        da404.Fill(dt404)

        'dt101 holds the info from the selective query of the AINDEF Table  (SO DOES DS505)
        'the info in dt101 now needs to get put into the [Inst Check Sensors EMU] table


        'THIS CODE DOES NOT WORK - IT DOESN'T LIKE dt101
        For Each dr As DataRow In dt101
            Dim drn As DataRow = dt404.NewRow
            'transfer the five fields that are the same in the two tables
            For i As Integer = 0 To 4
                drn.Item(i) = dr.Item(i)
            Next
        Next

    End Sub
0
JavaScript Best Practices

Save hours in development time and avoid common mistakes by learning the best practices to use for JavaScript.

SanclerCommented:
Sorry, my fault.  In my example

   For Each dr As DataRow In myMainDataTable

should have been

   For Each dr As DataRow In myMainDataTable.Rows

So your line

        For Each dr As DataRow In dt101

should be

        For Each dr As DataRow In dt101.Rows

Roger

0
charlieb01Author Commented:
Hi Roger,

Thanks for the feedback. I put in your change and the that much of the code works. I have put various datagrids and then set breakpoints to see that the data is correct. I did have to add a line (see below):

dt404.Rows.Add(drn) ' <- ADDED THIS LINE

Now my last problem is to save the sub table information back to the Access databas table (named "Inst Check Sensors EMU"
I am getting an error on the last line before the 'End Sub' The error is:

An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll

I appreciate any assistance you can provide.

Thanks,
Charlie

Here is all of the code as of now.

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Dim constr As String = "Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDB:Database L" & _
                "ocking Mode=1;Jet OLEDB:Database Password=;Data Source=""c:\test\test101\database\tescor.mdb"";Password=;Jet OLEDB:Engine Type=5;Jet" & _
                " OLEDB:Global Bulk Transactions=1;Provider=""Microsoft.Jet.OLEDB.4.0"";Jet OLEDB:S" & _
                "ystem database=;Jet OLEDB:SFP=False;Extended Properties=;Mode=Share Deny None;Je" & _
                "t OLEDB:New Database Password=;Jet OLEDB:Create System Database=False;Jet OLEDB:" & _
                "Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=Fals" & _
                "e;User ID=Admin;Jet OLEDB:Encrypt Database=False"
        Dim conn As New OleDb.OleDbConnection(constr)
        Dim strSql As String
        Dim strSql101 As String
        Dim dataSet101 As New DataSet
        dataSet101 = New DataSet("dataSet101")

        strSql = "SELECT * FROM AnalogInputDefinition"
        strSql101 = "SELECT SensorID, NameLong, Unit, LowRange, HighRange FROM AnalogInputDefinition ORDER BY OrderNum"

        Dim da As New OleDb.OleDbDataAdapter(strSql, conn)
        Dim dt As New DataTable
        da.Fill(dt)

        DataGrid1.DataSource = dt
        DataGrid1.Update()

        Dim Connection101 As OleDb.OleDbConnection = New OleDb.OleDbConnection(constr)
        Dim Command101 As OleDb.OleDbCommand = New OleDb.OleDbCommand(strSql101)
        Command101.CommandType = CommandType.Text

        Connection101.Open()
        Command101.Connection = Connection101

        Dim OleDbDataAdapter101 As OleDb.OleDbDataAdapter = New OleDb.OleDbDataAdapter
        OleDbDataAdapter101.SelectCommand = Command101
        OleDbDataAdapter101.Fill(dataSet101, "AnalogInputDefinition")
        Dim dt101 As New DataTable
        Dim ds505 As New DataSet("ds505")
        OleDbDataAdapter101.Fill(dt101)
        OleDbDataAdapter101.Fill(ds505)
        DataGrid2.DataSource = dt101

        DataGrid4.DataSource = ds505
        DataGrid4.Update()

        DataGrid3.SetDataBinding(dataSet101, "AnalogInputDefinition")

        Dim strSql202 = "SELECT * FROM [Inst Check Sensors EMU]"
        Dim da202 As New OleDb.OleDbDataAdapter(strSql202, conn)
        Dim dt202 As New DataTable
        Dim ds202 As New DataSet("ds202")

        Dim strSql303 = "DELETE * FROM [Inst Check Sensors EMU]"
        Dim Connection303 As OleDb.OleDbConnection = New OleDb.OleDbConnection(constr)
        Dim Command303 As OleDb.OleDbCommand = New OleDb.OleDbCommand(strSql303)
        Command101.CommandType = CommandType.Text

        Connection303.Open()
        Command303.Connection = Connection303
        Command303.ExecuteNonQuery()
        Connection303.Close()

        Dim strSql404 = "SELECT * FROM [Inst Check Sensors EMU]"
        Dim da404 As New OleDb.OleDbDataAdapter(strSql404, conn)
        Dim dt404 As New DataTable
        Dim ds404 As New DataSet("ds404")
        da404.Fill(dt404)

        'dt101 holds the info from the selective query of the AINDEF Table  (SO DOES DS505)
        'the info in dt101 now needs to get put into the [Inst Check Sensors EMU] table

        For Each dr As DataRow In dt101.Rows
            Dim drn As DataRow = dt404.NewRow
            'transfer the five fields that are the same in the two tables
            For i As Integer = 0 To 4
                drn.Item(i) = dr.Item(i)
            Next
            dt404.Rows.Add(drn) ' <- ADDED THIS LINE
        Next

        'for now just put zero's in the last two columns
        For Each dr As DataRow In dt404.Rows
            dr.Item(5) = 0
            dr.Item(6) = 0
        Next

        DataGrid5.DataSource = dt404
        DataGrid5.Update()

        ds404.Tables.Add(dt404)

        Dim cb As New OleDb.OleDbCommandBuilder(da404)
        da404.Update(dt404)     ' <- GOT ERROR ON THIS LINE

    End Sub
0
SanclerCommented:
We need to know some more details of the error "An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll"

Change this

        da404.Update(dt404)     ' <- GOT ERROR ON THIS LINE

to this

        Try
            da404.Update(dt404)     ' <- GOT ERROR ON THIS LINE
        Catch ex As Exception
            Debug.WriteLine(ex.ToString)
        End Try

The debug output should give more indication of what is going wrong.  If that is not sufficient for you to correct it, can you please post the message here.

Roger
0
charlieb01Author Commented:
Roger,

I put the Try-Catch as you suggested - below is the output from Debug. I'm not sure what this means.

Thanks,
Charlie


'DefaultDomain': Loaded 'c:\windows\microsoft.net\framework\v1.1.4322\mscorlib.dll', No symbols loaded.
'test101': Loaded 'C:\Test\test101\bin\test101.exe', Symbols loaded.
'test101.exe': Loaded 'c:\windows\assembly\gac\system.windows.forms\1.0.5000.0__b77a5c561934e089\system.windows.forms.dll', No symbols loaded.
'test101.exe': Loaded 'c:\windows\assembly\gac\system\1.0.5000.0__b77a5c561934e089\system.dll', No symbols loaded.
'test101.exe': Loaded 'c:\windows\assembly\gac\system.drawing\1.0.5000.0__b03f5f7f11d50a3a\system.drawing.dll', No symbols loaded.
'test101.exe': Loaded 'c:\windows\assembly\gac\system.data\1.0.5000.0__b77a5c561934e089\system.data.dll', No symbols loaded.
'test101.exe': Loaded 'c:\windows\assembly\gac\system.xml\1.0.5000.0__b77a5c561934e089\system.xml.dll', No symbols loaded.
'test101.exe': Loaded 'c:\windows\assembly\gac\microsoft.visualbasic\7.0.5000.0__b03f5f7f11d50a3a\microsoft.visualbasic.dll', No symbols loaded.
System.Data.OleDb.OleDbException: Syntax error in INSERT INTO statement.
   at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
   at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)
   at test101.Form1.Button1_Click(Object sender, EventArgs e) in C:\Test\test101\Form1.vb:line 255
The program '[3776] test101.exe' has exited with code 0 (0x0).
0
SanclerCommented:
The important bit in all that guff is "Syntax error in INSERT INTO statement".  It tells us that the problem is with the Insert statement or its parameters.  So we need to have a look at what the CommandText and the Parameters are for the InsertCommand that the CommandBuilder has generated.

So, after this line

       Dim cb As New OleDb.OleDbCommandBuilder(da404)

put this code

       Dim cmd As OleDbCommand = cb.GetInsertCommand
       Debug.WriteLine(cmd.CommandText)
       For Each p As OleDbParameter In cmd.Parameters
           Debug.WriteLine("====")
           Debug.WriteLine(p.ParameterName)
           Debug.WriteLine(p.DBType)
           Debug.WriteLine(p.OleDbType)
       Next

And then post the results here.

Roger
0
charlieb01Author Commented:
Roger,

Here is the bottom portion of the debug output window for the GetInsertCommand


'test101.exe': Loaded 'c:\windows\assembly\gac\microsoft.visualbasic\7.0.5000.0__b03f5f7f11d50a3a\microsoft.visualbasic.dll', No symbols loaded.
INSERT INTO Inst Check Sensors EMU( ID , Name , Units , LoLimit , HiLimit , Volts , Value ) VALUES ( ? , ? , ? , ? , ? , ? , ? )
====
@p1
String
VarWChar
====
@p2
String
VarWChar
====
@p3
String
VarWChar
====
@p4
String
VarWChar
====
@p5
String
VarWChar
====
@p6
Single
Single
====
@p7
Double
Double
System.Data.OleDb.OleDbException: Syntax error in INSERT INTO statement.
   at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
   at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)
   at test101.Form1.Button1_Click(Object sender, EventArgs e) in C:\Test\test101\Form1.vb:line 270
The program '[840] test101.exe' has exited with code 0 (0x0).
0
SanclerCommented:
'Name' and 'Value' are both reserved words in Access.  They need to be 'escaped' with [] so that the parser recognises them as field names rather than having their 'reserved' meanings.  After this line

       Dim cb As New OleDb.OleDbCommandBuilder(da404)

put

       cb.QuotePrefix = "["
       cb.QuoteSuffix = "]"

and try again.

Roger
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
charlieb01Author Commented:
Roger,

That was it. Thanks for all of your help. I think I will probably rename the fields in the database to avoid this issue but keep this information handy for future reference.

Thanks again,
Charlie
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Visual Basic.NET

From novice to tech pro — start learning today.