ADO in VB.NET

im writing this very simple application in vb.net and i am trying to insert a record into an MS Access table using ADO.NET (2.7) and for the life of me can't figure out why this code gets an "unhandled error":

        Dim conn As New OleDb.OleDbConnection()

        Dim sql As String

        conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
        "Data Source=" & System.Windows.Forms.Application.StartupPath & _
        "\hotlist.mdb;"

        conn.Open()

        sql = "INSERT INTO hotlist (LAST) VALUES ('SMITH')"

        Dim cmd As New OleDb.OleDbCommand(sql, conn)
        cmd.ExecuteNonQuery() '<----error occurs here

        conn.Close()


i'm not sure im even coding this write. if anyone has a better solution to insert a record to an access database through code, let me know. thanks.
LVL 1
xorcrackAsked:
Who is Participating?
 
inthedarkConnect With a Mentor Commented:
So here is the solution Last is not allowed as a field name unless you use [Last]

Try this:

SQL = "Create Table [hotlist3] (" _
      + "MyID Int Identity NOT NULL," _
      + "[Last] VARCHAR(20) NULL)"
     
cn.Execute SQL

SQL = "INSERT INTO hotlist3 ([LAST]) VALUES ('SMITH')"

cn.Execute SQL

It works for me.....best of luck with ADO...
0
 
inthedarkCommented:
what happens if you don't use a command object try:

conn.execute sql


0
 
xorcrackAuthor Commented:
the oleDBConnection object doesnt support an "Execute" method. and when i try this with the standard Connection object, i get a different error stating that the "INSERT" statement is wrong.

i usually have no trouble working with sql server databases using the standard Connection object. i dont know why it is proving so difficult to do the same thing with a ms acess database.
0
Cloud Class® Course: Microsoft Windows 7 Basic

This introductory course to Windows 7 environment will teach you about working with the Windows operating system. You will learn about basic functions including start menu; the desktop; managing files, folders, and libraries.

 
inthedarkCommented:
I don't know VB.NET but in VB with ADO you need to use the ADODB.Connection object.  I have never seen OleDb. objects. I thought that ADO was a seemless wrapper for OleDb.

???????????

0
 
xorcrackAuthor Commented:
could you give me some sample code that you know works for you, that inserts a record into an access database??
0
 
inthedarkCommented:
In VB 6 I would say:

dim CN as ADODB.Connection

CN.Connection string="...."
CN.Open

SQL = "Insert Into [MyTable] ([Field1], [Field2]) Values('Data1', 'Data2');"

CN.Execute SQL

CN.Close

Set CN = Nothing


1) Try using [Last] incase Last is a keyword or defined as a function, or something.

2) ADO only likes tables that have a primary key defined (or an Autonumber)

3) Make sure that you don't have any fields where "allow nulls" = no and have not been supplied with a value. Or a numeric field has been defined as required.


0
 
xorcrackAuthor Commented:
that did the trick! thank you so much for your time. i knew it had to be some dumb syntax quirk
0
 
inthedarkCommented:
The problem with ADO and access I have never found some good documentation for this.  I suspect that Microsoft created ADo without any spec. or documentation.....but this could be fantasy.
0
 
xorcrackAuthor Commented:
i agree. microsoft's documentation for ADO that comes with vb.net contains sample code that uses illegal commands in vb.net and won't even run. it's very frustrating when they make *you* feel like the fool, when you cant figure out how to get there *advanced* technologies to do the simplest things.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.