CODEWITHSUNDEEP
sqlvb.net
phpdrivesmemad
phpdrivesmemad

Reputation: 29

insert and delete query in visual studio

I can't seem to get my insert query to work in VB, it was working before and i tried to add a delete query and now the insert doesn't work.

I am searching for a customer in the customers table using their email (This works fine), the 3 fields from the customers table are then passed to another form where the user then adds more data to new fields, which are then used to insert into the members table. (I would also like to delete the customer from the customers table once they have been added to the members table using their email but can't seem to get it working).

This is the error I am getting 'Incorrect syntax near the keyword 'VALUES'.' Here is the code for the insert query, any help would be appreciated. I am very new to Visual Basic.

This code is inside my SQLControl.vb

     Public Sub Addmember(member_fname As String, member_sname As String, member_gender As String, member_dob As String,
                      member_address As String, member_postcode As String, member_email As String, member_contact_number As String,
                      member_registration As String, member_discount_rate As Integer)
    Try
        Dim strinsert As String = "INSERT INTO members (member_fname,member_sname,member_gender,member_dob,member_address,member_postcode,member_email,member_contact_number,member_registration,member_discount_rate " & _
                                   "VALUES(" & _
                                   "'" & member_fname & "'," & _
                                   "'" & member_sname & "'," & _
                                   "'" & member_gender & "'," & _
                                   "'" & member_dob & "'," & _
                                   "'" & member_address & "'," & _
                                   "'" & member_postcode & "'," & _
                                   "'" & member_email & "'," & _
                                   "'" & member_contact_number & "'," & _
                                   "'" & member_registration & "'," & _
                                   "'" & member_discount_rate & "')"

        MsgBox(strinsert)


        SQLCon.Open()

        SQLCmd = New SqlCommand(strinsert, SQLCon)

        SQLCmd.ExecuteNonQuery()

        SQLCon.Close()

    Catch ex As Exception

        MsgBox(ex.Message)

    End Try
End Sub

And this is where the sub is being called on the button in the form

Private Sub addmember_Click(sender As Object, e As EventArgs) Handles addmember.Click
    Try
        sql.Addmember(memberupdate_firstname.Text, memberupdate_surname.Text, membergender.Text, memberdob.Text, memberaddress.Text, memberpostcode.Text, memberemail.Text, membercontactnumber.Text, memberregisterationdate.Text, membersdiscountrate.Text)
        MsgBox("Member added")


    Catch ex As Exception
        MsgBox(ex.Message)
    End Try




End Sub

Upvotes: 1

Views: 2275

Answers (2)

Chalumeau
Chalumeau

Reputation: 101

Protect your code from Sql injection :

Public Sub Addmember(member_fname As String, member_sname As String, member_gender As String, member_dob As String,
                member_address As String, member_postcode As String, member_email As String, member_contact_number As String,
                member_registration As String, member_discount_rate As Integer)
    Try
        Dim queryInsert As String = "INSERT INTO members (member_fname,member_sname,member_gender,member_dob,member_address,member_postcode,member_email,member_contact_number,member_registration,member_discount_rate) " & _
                                    " VALUES (@fname,@sname,@gender,@dob,@address,@postcode,@email,@contact_number,@registration,@discount_rate) "

        Using sqlCon As New SqlConnection("MySqlConnectionString")
            sqlCon.Open()
            Using sqlCmd As New SqlCommand(queryInsert, sqlCon)
                Dim fnameParam As SqlParameter = sqlCmd.Parameters.Add("@fname", SqlDbType.NVarChar, 10)
                fnameParam.Value = member_fname

                Dim snameParam As SqlParameter = sqlCmd.Parameters.Add("@sname", SqlDbType.NVarChar, 10)
                snameParam.Value = member_sname

                'etc. for all your parameters..

                sqlCmd.ExecuteNonQuery()
            End Using
        End Using

    Catch ex As Exception
        MsgBox(ex.Message)
    End Try
End Sub

Upvotes: 1

Mureinik
Mureinik

Reputation: 311508

You are missing a ) character between the end of the column list and the keyword values:

    Dim strinsert As String = "INSERT INTO members (member_fname,member_sname,member_gender,member_dob,member_address,member_postcode,member_email,member_contact_number,member_registration,member_discount_rate " & _
                               ")VALUES(" & _
                               "'" & member_fname & "'," & _
                               "'" & member_sname & "'," & _
                               "'" & member_gender & "'," & _
                               "'" & member_dob & "'," & _
                               "'" & member_address & "'," & _
                               "'" & member_postcode & "'," & _
                               "'" & member_email & "'," & _
                               "'" & member_contact_number & "'," & _
                               "'" & member_registration & "'," & _
                               "'" & member_discount_rate & "')"

Upvotes: 1

Related Questions