Solved

Help with VB.NET ADO and Access

Posted on 2007-03-17
11
675 Views
Last Modified: 2008-01-09
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
0
Comment
Question by:charlieb01
  • 6
  • 5
11 Comments
 
LVL 34

Expert Comment

by:Sancler
ID: 18742136
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
 
LVL 34

Expert Comment

by:Sancler
ID: 18743119
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
 

Author Comment

by:charlieb01
ID: 18745666
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
 
LVL 34

Expert Comment

by:Sancler
ID: 18746669
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
 

Author Comment

by:charlieb01
ID: 18753328
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
6 Surprising Benefits of Threat Intelligence

All sorts of threat intelligence is available on the web. Intelligence you can learn from, and use to anticipate and prepare for future attacks.

 
LVL 34

Expert Comment

by:Sancler
ID: 18754859
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
 

Author Comment

by:charlieb01
ID: 18755527
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
 
LVL 34

Expert Comment

by:Sancler
ID: 18756246
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
 

Author Comment

by:charlieb01
ID: 18756303
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
 
LVL 34

Accepted Solution

by:
Sancler earned 500 total points
ID: 18756457
'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
 

Author Comment

by:charlieb01
ID: 18756615
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

Featured Post

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

1.0 - Introduction Converting Visual Basic 6.0 (VB6) to Visual Basic 2008+ (VB.NET). If ever there was a subject full of murkiness and bad decisions, it is this one!   The first problem seems to be that people considering this task of converting…
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Polish reports in Access so they look terrific. Take yourself to another level. Equations, Back Color, Alternate Back Color. Write easy VBA Code. Tighten space to use less pages. Launch report from a menu, considering criteria only when it is filled…

744 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

8 Experts available now in Live!

Get 1:1 Help Now