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

[ninjaimp]

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:

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

[Jenner]

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.

[Tom Sawyer]

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

[ninjaimp]

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:

 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

[Tom Sawyer]

Change:

Command = New OleDbCommand(sql, MyCn)

to:

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

[ninjaimp]

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

[nmadd]

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

[Jenner]

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!

[ninjaimp]

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

[Jenner]

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?

[ninjaimp]

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

[ninjaimp]

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

[Jenner]

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.

        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