Solved

ADO in VB.NET

Posted on 2002-05-20
9
326 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Revamp Your Training Process

Drastically shorten your training time with WalkMe's advanced online training solution that Guides your trainees to action.

 
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 200 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

Enroll in July's Course of the Month

July's Course of the Month is now available! Enroll to learn HTML5 and prepare for certification. It's free for Premium Members, Team Accounts, and Qualified Experts.

Question has a verified solution.

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

Enums (shorthand for ‘enumerations’) are not often used by programmers but they can be quite valuable when they are.  What are they? An Enum is just a type of variable like a string or an Integer, but in this case one that you create that contains…
In my programming career I have only very rarely run into situations where operator overloading would be of any use in my work.  Normally those situations involved math with either overly large numbers (hundreds of thousands of digits or accuracy re…
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
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
Course of the Month3 days, 19 hours left to enroll

630 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