CODEWITHSUNDEEP
vb.netms-access-2007
user2178307
user2178307

Reputation:

OleDB INSERT STATEMENT Error

I'm working on a project where I'm going to add records in database. My big problem is the "INSERT INTO" statement, after searching and debugging still no success. This is a part of the code:

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


    Dim sqlinsert As String = "INSERT INTO tblList([Access Number],[Book Title],Subject,Author,Edition,Page,Publisher,Years,Copy)" & "VALUES(@Access Number,@Book Title,@Subject,@Author,@Edition,@Page,@Publisher,@Years,@Copy)"

    Dim cmd As New OleDbCommand(sqlinsert, con)

    cmd.Parameters.Add(New OleDbParameter("@Access Number", txtan.Text))
    cmd.Parameters.Add(New OleDbParameter("@Book Title", txtbt.Text))
    cmd.Parameters.Add(New OleDbParameter("@Subject", txtsub.Text))
    cmd.Parameters.Add(New OleDbParameter("@Author", txtau.Text))
    cmd.Parameters.Add(New OleDbParameter("@Edition", txted.Text))
    cmd.Parameters.Add(New OleDbParameter("@Page", txtpg.Text))
    cmd.Parameters.Add(New OleDbParameter("@Publisher", txtpub.Text))
    cmd.Parameters.Add(New OleDbParameter("@Years", txtyr.Text))
    cmd.Parameters.Add(New OleDbParameter("@Copy", txtco.Text))



    cmd.ExecuteNonQuery()

    MsgBox("One Record Added")

    Refreshlist()
    clear()

End Sub

Help me please? I am so confused. Thanks a lot. (A am using ms access and vb.net in visual studio 2008)

Upvotes: 0

Views: 799

Answers (2)

Ruben_PH
Ruben_PH

Reputation: 1812

Upon digging on some of my old programs, this is how I pass parameters to ms access: 

Dim sqlinsert As String= "INSERT INTO tblList([Access Number],[Book Title],Subject,Author,Edition,Page,Publisher,Years,Copy)" & _
                         "VALUES(?,?,?,?,?,?,?,?,?)"  
        Dim cmd as new OleDbCommand(sqlinsert, con)
        cmd.Connection.Open
        With cmd.Parameters
           .AddWithValue("access_number", txtan.text)
           .AddWithValue("book_title", txtbt.text)
           .AddWithValue("subject", txtsub.text)
           .AddWithValue("author", txtau.text)
           .AddWithValue("edition", txted.text)
           .AddWithValue("page", txtpg.text)
           .AddWithValue("publisher", txtpub.text)
           .AddWithValue("years", txtyr.text)
           .AddWithValue("copy", txtco.text)
        End With
        cmd.ExecuteNonQuery()

Also, I'd be wary of using spaces on your column names,
specifically on Access Number and Book Title

P.S. 

.AddWithValue("a", b)

a = this can be anything but as a personal rule of mine, i tend to name it based on the column name
b = the value you want to pass

Upvotes: 1

Bjørn-Roger Kringsjå
Bjørn-Roger Kringsjå

Reputation: 9981

Try change the name of these parameters from

  • "@Access Number" to: "@Access_Number"
  • "@Book Title" to: "@Book_Title"

Upvotes: 1

Related Questions