Help with VB.NET ADO and Access

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

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

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

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

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

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

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).

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

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).

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