CODEWITHSUNDEEP
vb.netexcelms-accessinsert
Shane LeBlanc
Shane LeBlanc

Reputation: 2633

VB.NET Access DB INSERT Not Inserting Data?

I'm trying to insert a row into a table I have in Access. The table is named Site and it has two columns, SiteId (AutoNumber) and SiteName (Text). For testing purposes, I grabbed a value from an Excel sheet and attempted to insert the value into the SiteName column with the following code...

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    Dim App As New Excel.Application
    Dim WB As Excel.Workbook = App.Workbooks.Open("C:\MyFile.xlsx")
    Dim WS As Excel.Worksheet = DirectCast(WB.Worksheets(2), Excel.Worksheet)
    App.Visible = False

    Dim Range As Excel.Range
    Range = WS.Range("A8")
    Dim cnn As New OleDbConnection(My.Settings.cnn)
    Dim cmd As New OleDbCommand("INSERT INTO Site(SiteName) VALUES(@SiteName)", cnn)
    cmd.Parameters.Add("@SiteName", OleDbType.Char).Value = Range.Value.ToString

    Try
        Using cmd.Connection
            cmd.Connection.Open()
            cmd.ExecuteNonQuery()
        End Using
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try
End Sub

Even if I remove the cmd.Parameters line and just put a straight value like Values('Test') it doesn't make an insert. I get no errors in the code as I perform the command. Why is the value not working? I have verified the data returning from the Excel sheet and that works fine. I just can't get it to insert into the table in Access. The connection works fine as well because if I use this Insert...

Dim cmd As New OleDbCommand("INSERT INTO Site VALUES(@SiteName)", cnn)

it will tell me that the number of query values does not match the field count or something of the sort.

Upvotes: 0

Views: 1798

Answers (1)

igrimpe
igrimpe

Reputation: 1785

The value IS inserted (at least most of the times) ;) Have you ever checked the content of the mdb (in the output folder!) manually?

You have added the mdb file to your project? Go to the properties window of the file and set "Copy to output directory" to "NEVER".

NORMALLY when you add the file, it is set to "copy if newer" or "copy always". That means whenever you run your app in the debugger the mbd file from the project directory is copied to the debug/release folder and OVERWRITES any file that is already inside (and where you have stored your records).

Upvotes: 2

Related Questions