Unable to write to access db in VB.Net 2005 (migrating from VB6)

Hello all, I am recently in the process of migrating from VB6 to VB.Net 2005 B2. I so far love everything I see, except for this problem. (I am jumping from 6 to 8, skipping .NET 2003).

The only problem I have is writing to an access database. The following is the code I am using to read from my database... which works flawlessly...

I am referencing (the same as I did in VB6):

Microsoft ActiveX Data Objects 2.5 Library
Microsoft ActiveX Data Objects Recordset 2.8 Library

[code]
Private Sub tvVehicles_AfterSelect(ByVal sender As System.Object, ByVal e As System.Windows.Forms.TreeViewEventArgs) Handles tvVehicles.AfterSelect
        Dim nodsel As String
        Dim lItem As ListViewItem
        Dim rs As ADODB.Recordset
        Dim i As Integer
        Dim com As Object
        Dim dbPath As String
        Dim cnConnection As ADODB.Connection
        Dim String2 As String

        nodsel = tvVehicles.SelectedNode.Text
        com = New ADODB.Command
        lvFuel.Items.Clear()
        dbPath = Application.StartupPath & "\Data\fuel.mdb"
        cnConnection = New ADODB.Connection
        With cnConnection
            .Open("Provider=Microsoft.Jet.OLEDB.4.0; Data source ='" & dbPath & "'")
        End With
        rs = New ADODB.Recordset
        String2 = "SELECT ID, Vehicle, Date, Litres, Cost, PerLitre, KM_Start, KM_End, KM_Total, Mileage, Octane, Brand, Location, City FROM fuel WHERE Vehicle = """ & nodsel & """ ORDER by Date"
        rs.Open(String2, cnConnection, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockOptimistic)
        If rs.RecordCount = "0" Then
            lvFuel.Items.Clear()
            Me.Text = "" & My.Application.Info.Title & " : " & nodsel
            Exit Sub
        End If
        rs.MoveFirst()
        For i = 1 To rs.RecordCount
            If lvFuel.Items.Count() = 1 Or 3 Or 4 Or 6 Or 8 Or 10 Or 12 Then
                lItem = lvFuel.Items.Add("" & Trim(rs.Fields("ID").Value) & "")
                lItem.BackColor = Color.White
                lItem.SubItems.Add("" & Trim(rs.Fields("Date").Value) & "")
                lItem.SubItems.Add("" & Trim(rs.Fields("Litres").Value) & "")
                lItem.SubItems.Add("" & Trim(rs.Fields("Cost").Value) & "")
                lItem.SubItems.Add("" & Trim(rs.Fields("PerLitre").Value) & "")
                lItem.SubItems.Add("" & Trim(rs.Fields("KM_Start").Value) & "")
                lItem.SubItems.Add("" & Trim(rs.Fields("KM_End").Value) & "")
                lItem.SubItems.Add("" & Trim(rs.Fields("KM_Total").Value) & "")
                lItem.SubItems.Add("" & Trim(rs.Fields("Mileage").Value) & "")
                lItem.SubItems.Add("" & Trim(rs.Fields("Octane").Value) & "")
                lItem.SubItems.Add("" & Trim(rs.Fields("Brand").Value) & "")
                lItem.SubItems.Add("" & Trim(rs.Fields("Location").Value) & "")
                lItem.SubItems.Add("" & Trim(rs.Fields("City").Value) & "")
                rs.MoveNext()
            Else
                lItem = lvFuel.Items.Add("" & Trim(rs.Fields("ID").Value) & "")
                lItem.BackColor = Color.LightGray
                lItem.SubItems.Add("" & Trim(rs.Fields("Date").Value) & "")
                lItem.SubItems.Add("" & Trim(rs.Fields("Litres").Value) & "")
                lItem.SubItems.Add("" & Trim(rs.Fields("Cost").Value) & "")
                lItem.SubItems.Add("" & Trim(rs.Fields("PerLitre").Value) & "")
                lItem.SubItems.Add("" & Trim(rs.Fields("KM_Start").Value) & "")
                lItem.SubItems.Add("" & Trim(rs.Fields("KM_End").Value) & "")
                lItem.SubItems.Add("" & Trim(rs.Fields("KM_Total").Value) & "")
                lItem.SubItems.Add("" & Trim(rs.Fields("Mileage").Value) & "")
                lItem.SubItems.Add("" & Trim(rs.Fields("Octane").Value) & "")
                lItem.SubItems.Add("" & Trim(rs.Fields("Brand").Value) & "")
                lItem.SubItems.Add("" & Trim(rs.Fields("Location").Value) & "")
                lItem.SubItems.Add("" & Trim(rs.Fields("City").Value) & "")
                rs.MoveNext()
            End If
        Next i
        Me.Text = "" & My.Application.Info.Title & " : " & nodsel
        rs.Close()
    End Sub
[/code]

And here is the code I am trying to use for adding a new entry to the database, but does not work at all. I have been trying new things for 4 days now, and have not been able to get anything to work. It is becoming very frustrating, and I may be forced to move back to VB6, which is something I don't really want to do.

[code]
    Private Sub buttonSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim com As Object
        Dim dbPath As String
        Dim strCata As String
        Dim cnConnection As ADODB.Connection
        com = New ADODB.Command
        dbPath = Application.StartupPath & "\Data\automaintain.mdb"
        cnConnection = New ADODB.Connection
        With cnConnection
            .Open("Provider=Microsoft.Jet.OLEDB.4.0; Data source ='" & dbPath & "'")
        End With
        Dim nvrs As New ADODB.Recordset
        nvrs.Open("SELECT Vehicle, Year, Make, Model, Trim, VIN, Body_Style, Color, Purch_Date, Purch_From, Purch_Odometer, Purch_Price, Purch_City, Purch_Address, Purch_Region, Purch_Country, Purch_Code, Owner FROM vehicles", cnConnection, ADODB.CursorTypeEnum.adOpenStatic, ADODB.LockTypeEnum.adLockBatchOptimistic)
        MsgBox(nvrs.RecordCount)
        strCata = "" & cboYear.Text & " " & cboMake.Text & " " & cboModel.Text & " " & txtTrim.Text & ""
        nvrs.AddNew()
        nvrs.Fields("Vehicle").Value = Trim(strCata)
        nvrs.Fields("Year").Value = Trim(cboYear.Text)
        nvrs.Fields("Make").Value = Trim(cboMake.Text)
        nvrs.Fields("Model").Value = Trim(cboModel.Text)
        nvrs.Fields("Trim").Value = Trim(txtTrim.Text)
        nvrs.Fields("VIN").Value = Trim(txtVIN.Text)
        nvrs.Fields("Body_Style").Value = Trim(cboBodyStyle.SelectedIndex)
        nvrs.Fields("Color").Value = Trim(txtColor.Text)
        nvrs.Fields("Purch_Date").Value = Trim(dtpDate.Value)
        nvrs.Fields("Purch_From").Value = Trim(cboSeller.Text)
        nvrs.Fields("Purch_Odometer").Value = Trim(txtOdometer.Text)
        nvrs.Fields("Purch_Price").Value = Trim(txtPrice.Text)
        nvrs.Fields("Purch_City").Value = Trim(txtCity.Text)
        nvrs.Fields("Purch_Address").Value = Trim(txtAddress.Text)
        nvrs.Fields("Purch_Region").Value = Trim(cboRegion.Text)
        nvrs.Fields("Purch_Country").Value = Trim(cboCountry.Text)
        nvrs.Fields("Purch_Code").Value = Trim(txtCode.Text)
        nvrs.Fields("Owner").Value = Trim(cboOwners.Text)
        nvrs.Update()
        nvrs.Close()
    End Sub
[/code]

There is no way that something so easy to do in VB6 can be this difficult in VB.Net 2005. Can someone PLEASE tell me what I need to do to write to this database

Answer this question

Unable to write to access db in VB.Net 2005 (migrating from VB6)

firedog

Hi,

Since your using VB.Net you should make use of the new database approach. Instead of using the plain ADO try using ADO.net. Here a sample code on how to connect and Update a database (search the MSDN docs for the objects being used):

Imports System.Data.OleDb
Imports System.Data

Public Class Form1
    Dim adapter As OleDbDataAdapter
    Dim dt As DataTable
    Dim con As OleDbConnection

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim constr As String = "PROVIDER=Microsoft.Jet.Oledb.4.0; Data Source=D:\test.mdb"
        con = New OleDbConnection(constr)
        dt = New DataTable()
        adapter = New OleDbDataAdapter("SELECT * FROM Table1", con)
        Dim tmp As New OleDbCommandBuilder(adapter)
        con.Open()
        adapter.Fill(dt)
        con.Close()
        DataGridView1.DataSource = dt
    End Sub

    Private Sub btnUpdate_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdate.Click
        con.Open()
        adapter.Update(dt)
        con.Close()
    End Sub
End Class

BTW, add the references needed. System.Data into your reference...

cheers,

Paul June A. Domag
Unable to write to access db in VB.Net 2005 (migrating from VB6)

Answer this question