Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

ADO in VB.NET

Posted on 2002-05-20
9
Medium Priority
?
331 Views
Last Modified: 2013-11-23
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.
0
Comment
Question by:xorcrack
  • 5
  • 4
9 Comments
 
LVL 17

Expert Comment

by:inthedark
ID: 7023332
what happens if you don't use a command object try:

conn.execute sql


0
 
LVL 1

Author Comment

by:xorcrack
ID: 7023339
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
 
LVL 17

Expert Comment

by:inthedark
ID: 7023368
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
Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
LVL 1

Author Comment

by:xorcrack
ID: 7023369
could you give me some sample code that you know works for you, that inserts a record into an access database??
0
 
LVL 17

Expert Comment

by:inthedark
ID: 7023401
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
 
LVL 17

Accepted Solution

by:
inthedark earned 800 total points
ID: 7023413
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
 
LVL 1

Author Comment

by:xorcrack
ID: 7023432
that did the trick! thank you so much for your time. i knew it had to be some dumb syntax quirk
0
 
LVL 17

Expert Comment

by:inthedark
ID: 7023438
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
 
LVL 1

Author Comment

by:xorcrack
ID: 7023448
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

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction While answering a recent question (http://www.experts-exchange.com/Q_27402310.html) in the VB classic zone, I wrote some VB code in the (Office) VBA environment, rather than fire up my older PC.  I didn't post completely correct code o…
I was working on a PowerPoint add-in the other day and a client asked me "can you implement a feature which processes a chart when it's pasted into a slide from another deck?". It got me wondering how to hook into built-in ribbon events in Office.
Get people started with the process of using Access VBA to control Outlook using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Microsoft Outlook. Using automation, an Access applic…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

783 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