VB.Net writing to MS Access


DevX Home    Today's Headlines   Articles Archive   Tip Bank   Forums   

Results 1 to 10 of 10

Thread: VB.Net writing to MS Access

  1. #1
    Join Date
    Jun 2006
    Posts
    69

    VB.Net writing to MS Access

    I am trying to update a database using VB.net I have been able to read the file just not update any information in it. After searching the net it seems like there are many different ways to read / write to access. I was hoping that someone knew of a way to write to access without using command builder. The command builder has been a giant hassle and I want to avoid it whenever possible.

    This is the code that I am using to read information from Access:
    Dim con As New OleDb.OleDbConnection
    Dim ds As New DataSet
    Dim da As OleDb.OleDbDataAdapter
    Dim sql As String

    Dim intCounter As Integer = 0
    Dim con As New OleDb.OleDbConnection
    Dim ds As New DataSet
    Dim da As OleDb.OleDbDataAdapter
    Dim sql As String
    con.ConnectionString = "PROVIDER=Microsoft.jet.oledb.4.0;Data Source = F:\Visual Basic Files\Magic\CardList.mdb"
    sql = "SELECT * FROM CardName"
    con.Open()
    da = New OleDb.OleDbDataAdapter(sql, con)
    da.Fill(ds, "CardName")
    TextBox1.Text = ds.Tables("CardName").Rows(1).Item(1)
    con.Close()

  2. #2
    Join Date
    Aug 2006
    Posts
    43
    try
    Code:
    da.update(ds,"CardName")
    that is the way i go about updating my access database.

    i hope this helps
    Trains58554

  3. #3
    Join Date
    Jun 2006
    Posts
    69
    To run the command da.update you have to use command builder though dont you?
    Thanks in advance

  4. #4
    Join Date
    Aug 2006
    Posts
    43
    Try this it works for me

    Code:
    Try
    
                Dim CmdUpdate As New OleDbCommandBuilder(da)
    
                da.Fill(ds, "CardName")
    
                ds.Tables("CardName").Rows(1).Item(1) = Me.TextBox1.Text
    
                da.Update(ds, "CardName")
    
            Catch ex As Exception
    
                MsgBox(ex.ToString)
    
            End Try
    Trains58554

  5. #5
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    Running an SQL UPDATE query always works for me regardless of what database I'm using.
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  6. #6
    Join Date
    Jun 2006
    Posts
    69
    I created a sql query but its posting an error. This is the code that I added:
    sql = "UPDATE CardName SET CardName = 'test' WHERE PrimaryKey = '1'"
    Dim cmd As New OleDb.OleDbCommand(sql, con)
    cmd.ExecuteNonQuery()

    I receive this error "Data type mismatch in criteria expression."

  7. #7
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    Probably because primary key is not a string.

    Remove the single quotes.
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  8. #8
    Join Date
    Jun 2006
    Posts
    69
    Hack, that worked perfect. I am new to SQL so my syntax is rough.
    My thanks again to Hack and Trains for offering ideas / fixes.

  9. #9
    Join Date
    Apr 2007
    Location
    Sterling Heights, Michigan
    Posts
    8,666
    No problem and this is something that has a tendency to trip up a lot of folks that are new to SQL (i.e., strings need single quotes - non strings do not)

    Just remember to review what you have in these terms if you wind up with type mismatch errors again.
    Last edited by Hack; 04-25-2008 at 12:47 PM.
    I don't answer coding questions via PM or Email. Please post a thread in the appropriate forum section.
    Please use [Code]your code goes in here[/Code] tags when posting code.
    Before posting your question, did you look here?
    Got a question on Linux? Visit our Linux sister site.
    Modifications Required For VB6 Apps To Work On Vista

  10. #10
    Join Date
    Jun 2006
    Posts
    69
    Will do
    Thanks Again

Similar Threads

  1. ms access database question using vb.net 2003
    By trains58554 in forum Database
    Replies: 3
    Last Post: 10-27-2007, 07:00 AM
  2. Replies: 1
    Last Post: 10-02-2006, 01:52 PM
  3. Can VB.net work with Access 97 ?
    By psd in forum VB Classic
    Replies: 1
    Last Post: 10-19-2002, 05:52 PM
  4. VB.NET and Microsoft Access
    By Frank Oquendo in forum .NET
    Replies: 1
    Last Post: 12-06-2001, 04:30 PM
  5. Replies: 0
    Last Post: 03-28-2001, 12:29 PM

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
HTML5 Development Center
 
 
FAQ
Latest Articles
Java
.NET
XML
Database
Enterprise
Questions? Contact us.
C++
Web Development
Wireless
Latest Tips
Open Source


   Development Centers

   -- Android Development Center
   -- Cloud Development Project Center
   -- HTML5 Development Center
   -- Windows Mobile Development Center