VB.Net code to Insert Data into MS Access database with Parameters

[kipslem]

Hello,

My sincere apology, I have been posting almost 4 questions now where I have received 2 answers being solved. These are simple questions which any one familar with VB.Net can solve easily. Please understand, I'm a newbie to VB.Net.

Now my next issue is; I have a a form which has text boxes from where the user has to enter the data for the MS Access backend. I am getting the error as INSERT SYNTAX Error when entering the data and clicking the Save button. What is wrong with my insert statemnet below which is at the click even of the Save button? Please help ASAP as I desperately need to get this application working.

Thanking you in advance.

The code is as shown below:

Private Sub btnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAdd.Click

    Dim conn As New OleDbConnection("PROVIDER=Microsoft.Jet.OLEDB.4.0;Data Source = C:\Program Files\PNGATSA Database\PNGATSA Database.mdb;Persist Security Info=True")


Dim insertsql As String


        Try
            insertsql = "INSERT INTO tblConvention" & _
                        "(ConventionYear, DateOfConvention, ConventionVenue, Convenor, Patron, FundraisingChairPerson, President, VicePresident, Treasurer, Secretary, GuestSpeaker) " & _
                        "(Qualification, OtherSpeaker1, OtherSpeaker1Qualification, OtherSpeaker2, OtherSpeaker2Qualification, Theme, ThemeSong, KeyText, TotalStudentsAttended) " & _
                        "(TotalTertiaryStudents, TotalSecondaryStudents, ChurchParents, OtherInfor) " & _
                        " VALUES(@ConventionYear, @DateOfConvention, @ConventionVenue, @Convenor, @Patron, @FundraisingChairPerson, @President, @VicePresident, @Treasurer, @Secretary, @GuestSpeaker)" & _
                        "(@Qualification, @OtherSpeaker1, @OtherSpeaker1Qualification, @OtherSpeaker2, @OtherSpeaker2Qualification, @Theme, @ThemeSong, @KeyText, @TotalStudentsAttended) " & _
                        "(@TotalTertiaryStudents, @TotalSecondaryStudents, @ChurchParents, @OtherInfor)"

            Dim cmd As OleDbCommand = New OleDbCommand(insertsql, conn)

            cmdUsers1.Parameters.AddWithValue("@ConventionYear", txtConventionYear.Text)
            'cmdUsers1.Parameters.AddWithValue("@DateOfConvention", CType(txtConventionDate.Text, DateTime))
            cmdUsers1.Parameters.AddWithValue("@DateOfConvention", txtConventionDate.Text)
            cmdUsers1.Parameters.AddWithValue("@ConventionVenue", txtConventionVenue.Text)
            cmdUsers1.Parameters.AddWithValue("@Convenor", txtConvenor.Text)
            cmdUsers1.Parameters.AddWithValue("@Patron", txtPatronMatron.Text)
            cmdUsers1.Parameters.AddWithValue("@FundraisingChairPerson", txtFundChair.Text)
            cmdUsers1.Parameters.AddWithValue("@President", txtPresident.Text)
            cmdUsers1.Parameters.AddWithValue("@VicePresident", txtVicePresident.Text)
            cmdUsers1.Parameters.AddWithValue("@Treasurer", txtTreasurer.Text)
            cmdUsers1.Parameters.AddWithValue("@Secretary", txtSecretaryGen.Text)
            cmdUsers1.Parameters.AddWithValue("@GuestSpeaker", txtGuestSpeaker.Text)
            cmdUsers1.Parameters.AddWithValue("@Qualification", txtQualification.Text)
            cmdUsers1.Parameters.AddWithValue("@OtherSpeaker1", txtotherSpeaker1.Text)
            cmdUsers1.Parameters.AddWithValue("@OtherSpeaker1Qualification", txtQualification1.Text)
            cmdUsers1.Parameters.AddWithValue("@OtherSpeaker2", txtotherSpeaker2.Text)
            cmdUsers1.Parameters.AddWithValue("@OtherSpeaker2Qualification", txtQualification2.Text)
            cmdUsers1.Parameters.AddWithValue("@Theme", txtTheme.Text)
            cmdUsers1.Parameters.AddWithValue("@ThemeSong", txtThemeSong.Text)
            cmdUsers1.Parameters.AddWithValue("@KeyText", txtKeyText.Text)
            cmdUsers1.Parameters.AddWithValue("@TotalStudentsAttended", CInt(txtTotalStd.Text))
            'cmdUsers1.Parameters.AddWithValue("@TotalStudentsAttended", txtTotalStd.Text)
            cmdUsers1.Parameters.AddWithValue("@TotalTertiaryStudents", CInt(txtTotalTerTStd.Text))
            'cmdUsers1.Parameters.AddWithValue("@TotalTertiaryStudents", txtTotalTerTStd.Text)
            cmdUsers1.Parameters.AddWithValue("@TotalSecondaryStudents", CInt(txtTotalSecStd.Text))
            'cmdUsers1.Parameters.AddWithValue("@TotalSecondaryStudents", txtTotalSecStd.Text)
            cmdUsers1.Parameters.AddWithValue("@ChurchParents", CInt(txtchurchParent.Text))
            'cmdUsers1.Parameters.AddWithValue("@ChurchParents", txtchurchParent.Text)
            cmdUsers1.Parameters.AddWithValue("@OtherInfor", txtOtherInformation.Text)

            conn.Open()
            Dim rowsaffected As Integer = cmd.ExecuteNonQuery
            MessageBox.Show(rowsaffected.ToString & "rows added")
            Clear()
            conn.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try

    End Sub

[sandeepparekh9]

can u post the error please...

[kipslem]

Hi sandeepparekh9,

Thanks for the reply. I actually managed to solve the problem, and forgot to mark as solved.

So thanks once again.

[ling_tj]

could you like to post the answer of that problem?

Thanks

[Reverend Jim]

The solution was to replace the "@" values in the query with "?" as in the following example

ListView1.Items.Clear()

Dim con As New OleDbConnection("Provider=SQLNCLI10;Server=.\SQLEXPRESS;Database=PUBS;Trusted_Connection=Yes;Connect Timeout=15;")
Dim cmd As New OleDbCommand("", con)

cmd.CommandText = "SELECT au_lname,au_fname,zip FROM authors WHERE au_lname LIKE ?"
cmd.Parameters.AddWithValue("@parm", "D%")

con.Open()
Dim rdr As OleDbDataReader = cmd.ExecuteReader()

Do While rdr.Read
    ListView1.Items.Add(New ListViewItem({rdr.GetString(0), rdr.GetString(1), rdr.GetString(2)}))
Loop

rdr.Close()
con.Close()