[2008] INSERT sql runs fine but no data is entered into the DB

HI

I am trying to write data to an access table from vb.net. The code all seems to run fine but when i check the db no new data has been entered!

the code i have got is:

Code:

---

Dim itemcount As Integer
        Dim MyCn As OleDbConnection
        Dim sql As String
        'Dim Value As String
        Dim Command As OleDbCommand
        Try

            con.Open()
            MyCn = New OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\BillAnalyst\ReferenceData.mdb")
            For Each ListView In lstErrors.Items
                itemcount = itemcount + 1

                sql = "INSERT INTO CAllRates (CallRates.Destination) VALUE (" & ListView & ")"
                Command = New OleDbCommand(sql, MyCn)
            Next

        Catch ex As Exception
            MessageBox.Show(ex.Message & " - " & ex.Source)
            con.Close()
        End Try

        lblcount.Text = itemcount
Dim itemcount As Integer
        Dim MyCn As OleDbConnection
        Dim sql As String
        'Dim Value As String
        Dim Command As OleDbCommand
        Try

            con.Open()
            MyCn = New OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\BillAnalyst\ReferenceData.mdb")
            For Each ListView In lstErrors.Items
                itemcount = itemcount + 1

                sql = "INSERT INTO CAllRates (CallRates.Destination) VALUE (" & ListView & ")"
                Command = New OleDbCommand(sql, MyCn)
            Next

        Catch ex As Exception
            MessageBox.Show(ex.Message & " - " & ex.Source)
            con.Close()
        End Try

        lblcount.Text = itemcount

---

I wondered if anyone had ideas what i was doing wrong

many thanks

You're not opening your connection (con is not the connection you are using, MyCn is).

You're not executing your command.

You're not closing your connection.

Also, I believe it should be VALUES, not VALUE and, if it's a string, you'll need single quotes.

my bad - i changed the conenction string just in case it was that and forgot to change the other bit . but corrected it and still the same result. it appears to run fine without error but adds no records to the db. my adjusted code is:

Code:

---

Dim itemcount As Integer
        Dim MyCn As OleDbConnection
        Dim sql As String
        'Dim Value As String
        Dim Command As OleDbCommand
        Try


            MyCn = New OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\BillAnalyst\ReferenceData.mdb")
            MyCn.Open()
            For Each ListView In lstErrors.Items
                itemcount = itemcount + 1

                sql = "INSERT INTO CallRates VALUES ('" & ListView & "')"
                Command = New OleDbCommand(sql, MyCn)
            Next

        Catch ex As Exception
            MessageBox.Show(ex.Message & " - " & ex.Source)
            MyCn.Close()
        End Try

        lblcount.Text = itemcount

---

Change:

Command = New OleDbCommand(sql, MyCn)

to:

Command = New OleDbCommand(sql, MyCn)
Command.ExecuteNonQuery

that seems to have sorted the connection thing - many thanks

but i am now getting errors with the location of the db, even though that path works fine on another connection. will updated with the error now

Quote:

---

Originally Posted by ninjaimp

it appears to run fine without error but adds no records to the db.

---

Perhaps this:
http://msdn2.microsoft.com/en-us/library/ms246989.aspx

You're still not closing your connection. Get that MyCn.Close() OUT of the Catch block! If you're not closing your connections properly, you will have problems later when they get used up!

it is now connecting, or sort of but i get the error:


could not find file: c:\documents and settings\Simon\My Documents\Visual Studio 2008\Projects\BillAnalyst\BillAnalyst\bin\Debug\Callrates.mdb

but the connection string points to:

C:\BillAnalyst\ReferenceData.mdb


cant understand why the path is different and it cant see it where im pointing it to

Where in the code does it give you this error? Somewhere, the code is trying to open a connection to "Callrates.mdb".

I'm suspecting you're using a DataConnection through the GUI that is pointed to this database. What does your "Server Explorer" show?

it gives the error on the very first cycle as it hits the Command.ExecuteNonQuery().


the connection string value for the con connection reads: "PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\BillAnalyst\ReferenceData.mdb"

so that appears correct

Im not sure what my Server Explorer is?

many thanks for your help

UPDATE!

It appeared to be an error with the SQL. Changed it to:

INSERT INTO CallRates (Destination) VALUES ('" & ListView & "')


and it all worked fine.

many thanks for everyones help

Regards

Before you go... it's good that you got it to work, but here's a better way of doing it. Using a Parameter is not only faster, but doesn't mess up your SQL if someone puts a ' or a \ in the name you're inserting.

It's smaller code too.

Code:

---

        Dim MyCn As New OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\BillAnalyst\ReferenceData.mdb")
        Dim sql As String = "INSERT INTO CallRates (Destination) VALUES (@CR)"
        Dim Command As OleDbCommand = New OleDbCommand(sql, MyCn)
        Try
            Command.Parameters.Add("CR", OleDbType.VarChar, 50)

            MyCn.Open()
            For Each li As ListViewItem In lstErrors.Items
                Command.Parameters("CR").Value = li.Text
                Command.ExecuteNonQuery()
            Next
            MyCn.Close()

            lblCount.Text = lstErrors.Items.Count.ToString
        Catch ex As Exception
            MessageBox.Show(ex.Message & " - " & ex.Source)
            If Not MyCn.State.Equals(ConnectionState.Closed) Then MyCn.Close()
        End Try

---