System.Data.SqlClient.SqlException Unclosed quotation mark after the character string

Question

Every time I run this code for putting some Student information, when I click to save it, there are always appear messsage in cmd.Executenonquery()..please help of this..

Imports System.Collections.ObjectModel
Imports System.Data.SqlClient
Imports System.Data


Public Class SI
Dim con As New SqlConnection With {.ConnectionString = "Server=Danica-pc; database=SI;user=dandan;pwd=danica;"}
Dim cmd As New SqlCommand
Dim query As String

Dim stuid, i As Integer
Dim studentID As Integer
Dim StudentBindingSource As Object
Dim TableAdapterManager As Object

Private Sub StudentBindingNavigatorSaveItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs)
    Me.Validate()
    Me.StudentBindingSource.EndEdit()
    Me.TableAdapterManager.UpdateAll(Me.SIDataSet)
End Sub

Private Sub Label4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Kasarian.Click

End Sub

Private Sub SI_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    'TODO: This line of code loads data into the 'SIDataSet.Studentinfo' table. You can move, or remove it, as needed.
    Me.StudentinfoTableAdapter.Fill(Me.SIDataSet.Studentinfo)

End Sub

Private Sub getData()
    i = DataGridView1.CurrentCell.RowIndex()
    studentID = i

End Sub
Private Sub dataReload()
    familynem.Clear()
    middlenem.Clear()
    givennem.Clear()
    usename.Clear()
    accpass.Clear()
    confirmpass.Clear()


    Try
        Dim sql As String = "Select * from Studentinfo"
        Dim myAdapter As New SqlDataAdapter(sql, con)
        con.Open()
        Dim myDataset As New DataSet()
        myAdapter.Fill(myDataset, "SI")
        DataGridView1.DataSource = myDataset
        DataGridView1.DataMember = "SI"
        DataGridView1.SelectionMode = DataGridViewSelectionMode.FullRowSelect
        con.Close()
    Catch ex As Exception
        MessageBox.Show(ex.Message)
    End Try
End Sub
Private Sub famliynem_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles familynem.TextChanged

End Sub

Private Sub stat_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles stat.SelectedIndexChanged

End Sub

Private Sub HomeToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles HomeToolStripMenuItem.Click
    Home.Show()
    Me.Hide()
End Sub

Private Sub EventsToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles EventsToolStripMenuItem.Click
    EventsForm.Show()
    Me.Hide()
End Sub

Private Sub ProductsToolStripMenuItem_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles ProductsToolStripMenuItem.Click
    Products.Show()
    Me.Hide()
End Sub

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cancel.Click
    Home.Show()
    Me.Close()
    End
End Sub

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles save.Click
    Dim genderval As String
    Dim birthdate As String
    birthdate = bday.Value.ToString()
    If babae.Checked = True Then
        genderval = "Female"
    Else
        genderval = "Male"
    End If
    query = "insert into studentinfo(Lastname,Firstname,middlename,birthdate,gender,username)""values('" & familynem.Text & "','" & givennem.Text & "','" & middlenem.Text & "','" & birthdate & "','" & genderval & "','" & usename.text & "')"
    con.Open()
    cmd = New SqlCommand(query, con)
    *cmd.ExecuteNonQuery()*
    con.Close()
    dataReload()
    user.Show()
    Me.Hide()


End Sub
End Class

You have )""values a double double quote here, essentially injecting a double quote into the SQL string. — Charleh
thanks for answering for my question, so should i remove the one quotation mark? — user3428268

Andrew Morton Andrew Morton · Accepted Answer · 2014-03-17T10:12:21

You have an unwanted "" in this line:

    query = "insert into studentinfo(Lastname,Firstname,middlename,birthdate,gender,username)""values('" & familynem.Text & "','" & givennem.Text & "','" & middlenem.Text & "','" & birthdate & "','" & genderval & "','" & usename.text & "')"

Also, I recommend that you look into using SQL parameters to pass the values:

Edit: You can use SQL parameters by replacing this code:

query = "insert into studentinfo(Lastname,Firstname,middlename,birthdate,gender,username)""values('" & familynem.Text & "','" & givennem.Text & "','" & middlenem.Text & "','" & birthdate & "','" & genderval & "','" & usename.text & "')"
con.Open()
cmd = New SqlCommand(query, con)
*cmd.ExecuteNonQuery()*
con.Close()

with:

Using conn As New SqlConnection("YOUR CONNECTION STRING")
    Dim query = "INSERT INTO studentinfo(Lastname,Firstname,middlename,birthdate,gender,username) VALUES(@familynem, @givennem, @middlenem, @birthdate, @genderval, @usename)"
    Using cmd As New SqlCommand(query, conn)
        cmd.Parameters.AddWithValue("@familynem", familynem.Text)
        cmd.Parameters.AddWithValue("@givennem", givennem.Text)
        cmd.Parameters.AddWithValue("@middlenem", middlenem.Text)
        cmd.Parameters.AddWithValue("@birthdate", birthdate)
        cmd.Parameters.AddWithValue("@genderval", genderval.Text)
        cmd.Parameters.AddWithValue("@usename", usename.Text)
        conn.Open()
        cmd.ExecuteNonQuery()
        conn.Close()
    End Using
End Using

The Using constructs take care of calling .Dispose() for you, and you should not have connections hanging around. SQL parameters help prevent SQL injection attacks, and stop the query from breaking if you have a name like O'Reilly, where the apostrophe would be a problem.

System.Data.SqlClient.SqlException Unclosed quotation mark after the character string

1 Answers