Add items to Access database using SQL in Visual Basic 2010

Question

a little help needed.

I'm currently using this code to add items to an Access database in Visual Basic 2010

Imports System
Imports System.Data
Imports System.Collections
Imports System.Windows.Forms
Imports System.Resources
Imports System.Data.OleDb
Public Class Form2
Dim myConnection = New System.Data.OleDb.OleDbConnection()
Dim SqlCommand As OleDb.OleDbCommand = New OleDb.OleDbCommand
Private Sub Form2_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    'connects to the database when the form loads
    myConnection = New System.Data.OleDb.OleDbConnection()
    myConnection.ConnectionString = "Provider =Microsoft.ACE.OLEDB.12.0; Data Source =project.accdb; Persist Security Info =False;"
    Try
        myConnection.Open()

    Catch ex As Exception
        MsgBox("Error Please Go Back And Try Again")
        Console.WriteLine(ex.Message)

    End Try
    'adds items to the dropdown menu

    Dim result As OleDb.OleDbDataReader
    Dim sqlText As String
    Dim add1 As Integer = 0

    If myConnection.State <> ConnectionState.Open Then
        MsgBox("Connection is not open")
        Exit Sub
    End If

    sqlText = "select * from groups"
    SqlCommand = New OleDbCommand(sqlText, myConnection)
    result = SqlCommand.ExecuteReader()

    Do While result.Read()
        ComboBox1.Items.Insert(add1, result.GetValue(0))
        add1 = add1 + 1
    Loop

    result = Nothing
    SqlCommand = Nothing
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
    'Insert button
    Dim item As String = ""
    Dim price As String = ""
    Dim group As String = ""
    Dim dateandtime As Double
    Dim dateandtime2 As String = ""
    Dim sqlInsert As String = ""
    Dim result As Integer = -1
    Dim SqlCommand As OleDb.OleDbCommand = New OleDb.OleDbCommand

    If TextBox1.Text = "" Or TextBox2.Text = "" Then
        MsgBox("Item and Price Required!")
        Exit Sub
    End If

    item = TextBox1.Text
    price = TextBox2.Text.Trim
    group = ComboBox1.Text.Trim
    dateandtime = DateTimePicker1.Value.ToOADate()
    dateandtime2 = dateandtime.ToString
    sqlInsert = "INSERT INTO purchases (item, price, groupName, datetime) VALUES ('" + item + "', '" + price + "', '" + group + "', '" + dateandtime2 + "')"

    Try

        SqlCommand.Connection = myConnection
        SqlCommand.CommandText = sqlInsert
        result = SqlCommand.ExecuteNonQuery()

        If result = 0 Then
            MsgBox("No records inserted!")
        Else
            MsgBox(CStr(result) & " record inserted!")
        End If

        TextBox1.Clear()
        TextBox2.Clear()
        ComboBox1.Text = ""

    Catch ex As Exception
        MsgBox(ex.ToString)

    End Try

    result = -1
    SqlCommand = Nothing
End Sub

But I get this exception every time I try to add anything View here: https://i.sstatic.net/itd39.jpg Any thoughts?

Have a look at the value for sqlInsert and check it is valid SQL syntax — sasfrog
– sasfrog, Commented Nov 30, 2012 at 12:36
Thanks for replying! I've taken a look at this link w3schools.com/sql/sql_insert.asp and the syntax looks about right. The only thing I'm missing is the row number which I can't specify as I will need to program to continue adding rows in the future, but this same code with different variables works in another form so I have no idea why it isn't working. — MystPhysX
– MystPhysX, Commented Nov 30, 2012 at 12:46
do you know how to use sql profiler? if you can run a trace when you run the insert you'll prob see what the issue is. my bet is that your string concatination is flaking somehow based on an inserted value eg .... VALUES ('d'adario', ..... — Rohan Büchner
– Rohan Büchner, Commented Nov 30, 2012 at 12:51
Can you stick MessageBox.Show( sqlInsert ) before executing a command? I suspect there's an error in date formats, but I could be wrong. Normally I recommend using SqlParameter objects rather than concatenating sql string on the fly. — b0rg
– b0rg, Commented Nov 30, 2012 at 12:52
Here you go i.imgur.com/qTFiK.jpg it looks right. I've used the Datetime format for Access — MystPhysX
– MystPhysX, Commented Nov 30, 2012 at 12:55

Anthony · Accepted Answer · 2014-09-02 14:27:05Z

2

MystPhysX,

I realize that this answer is a couple of years too late for your problem, however, for anyone who is searching the internet with a similar problem (which is how I found this thread), I'd like to provide the solution that worked for my problem.

You're using the "+" operator to concatenate the sqlInsert string in Visual Basic. I believe using the "&" operator will resolve the error that is being thrown. The link below will provide more information.

http://msdn.microsoft.com/en-us/library/te2585xw.aspx

Here is the edited line of code:

sqlInsert = "INSERT INTO purchases (item, price, groupName, datetime) VALUES ('" & item & "', '" & price & "', '" & group & "', '" & dateandtime2 & "')"

answered Sep 2, 2014 at 14:27

Anthony

362 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

MystPhysX Over a year ago

Thanks man. I'm sure other people will appreciate finding your answer as well.

Collectives™ on Stack Overflow

Add items to Access database using SQL in Visual Basic 2010

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related