Solved

ADO in VB.NET

Posted on 2002-05-20
9
317 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
 
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
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
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

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

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…
Background What I'm presenting in this article is the result of 2 conditions in my work area: We have a SQL Server production environment but no development or test environment; andWe have an MS Access front end using tables in SQL Server but we a…
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…
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…

760 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

Need Help in Real-Time?

Connect with top rated Experts

21 Experts available now in Live!

Get 1:1 Help Now