SQL Parameters using VB.net

Question

So I'm trying to add a name to a database that's gotten from a inputbox and stored in a variable. I've tried many things suggested here. Currently this code puts nothing into the database.

Private Sub BTNadd_Click(sender As Object, e As EventArgs) Handles BTNadd.Click
    Dim i As String
    Dim j As Integer = 0
    'asks user for a name
    'loop to check the name is not a space, re-asks the question
    Do While j = 0
        i = InputBox("Please enter Member's name", "Need More Input")
        If i = " " Then
            MessageBox.Show("You must enter a name to continue.")
        ElseIf i = "" Then
            Exit Sub
        Else
            j = 1
        End If
    Loop
    'check for duplicates
    If LBmembers.Items.Contains(i) Then
        MessageBox.Show(i & " is already on the list.", "Error")
    Else
        Dim dbProvider = "PROVIDER=Microsoft.Jet.OLEDB.4.0;"
        Dim dbSource = "Data Source= C:\members.mdb "
        Dim SqlQuery As String =
            "INSERT INTO tblMembers (Member) VALUES ('@ID');"
        'add member to database
        Using con = New OleDb.OleDbConnection(dbProvider & dbSource)
            Using cmd = New OleDb.OleDbCommand(SqlQuery, con)
                con.Open()
                cmd.Parameters.AddWithValue("@ID", OleDb.OleDbType.Variant).Value = i
                con.Close()
            End Using
        End Using
        LBmembers.Items.Add(i)
    End If
End Sub

The following code was used within the nested Usings and it inputed @ID into the members column

con.Open() 
cmd.Parameters.Add("@ID", OleDb.OleDbType.Variant) 
cmd.Parameters("@ID").Value = i 
cmd.ExecuteNonQuery() 
con.Close()

Any help with this would be appreciated.

ekad · Accepted Answer · 2014-10-04 03:10:12Z

2

I see two problems. The first is your query syntax, @ID is inside quotes

Dim SqlQuery As String =
    "INSERT INTO tblMembers (Member) VALUES ('@ID');"

You should remove the quotes

Dim SqlQuery As String =
    "INSERT INTO tblMembers (Member) VALUES (@ID);"

The second problem is you never execute the query, add cmd.ExecuteNonQuery() after adding the parameters

Using cmd = New OleDb.OleDbCommand(SqlQuery, con)
    con.Open()
    cmd.Parameters.AddWithValue("@ID", OleDb.OleDbType.Variant).Value = i
    cmd.ExecuteNonQuery()
    con.Close()
End Using

edited Oct 4, 2014 at 3:10

answered Oct 4, 2014 at 2:52

ekad

14.7k26 gold badges46 silver badges49 bronze badges

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

2 Comments

Gotrek65 Over a year ago

Yeah and now it inserts a @ID into the table

Gotrek65 Over a year ago

Ah hah thank you ekad such a simple fix now I feel kinda silly

Collectives™ on Stack Overflow

SQL Parameters using VB.net

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related