I am trying to insert data using sql query in vb.net as follows. name = corp int'l poc = 1
When I tried to insert, I get an error ("Unclosed Quotation Mark after the character String '"). This happens when I tried to insert name with only 1 single quote.
Therefore I added a replace function to replace 1 single quote with 2 single quote to escape the symbol. There was no error but when I look into my database, 2 single quotes are added instead of 1.
Can anyone advise me how I can escape the single quote with my parameterized query? Thanks!
Public Function InsertData(ds As DataSet) As Boolean
Dim cmd As New SqlCommand
Dim cmd1 As New SqlCommand
Dim status As Boolean
Dim name As String
Dim poc As String
Dim id_p As New SqlParameter("id", SqlDbType.VarChar)
Dim name_p As New SqlParameter("name", SqlDbType.VarChar)
cmd.Parameters.Add(id_p)
cmd.Parameters.Add(name_p)
For i = 0 To ds.Tables(0).Rows.Count - 1
If checkExists(ds.Tables(0).Rows(i)(1).ToString(), ds.Tables(0).Rows(i)(2).ToString(), ds.Tables(0).Rows(i)(3).ToString()) = True Then
name = ds.Tables(0).Rows(i)(1).ToString()
poc = ds.Tables(0).Rows(i)(2).ToString()
If name.Contains("'") Then
name = name.Replace("'", "''")
End If
If poc.Contains("'") Then
poc = poc.Replace("'", "'")
End If
name_p.SqlValue = name
id_p.SqlValue = poc
cmd.CommandText = "INSERT INTO Code (Name,ID)" _
& " VALUES (@name,@id)"
status = ExecuteNonQuerybySQLCommand(cmd)
End If
Next
Return status
End Function
Dim strcon As String = "Data Source=x.x.x.x,1433;Network Library=DBMSSOCN;Initial Catalog=code_DB;User ID=xxx;Password=xxx;"
Public Function ExecuteNonQuerybySQLCommand(ByVal cmd As SqlCommand) As Boolean
Dim sqlcon As New SqlConnection
Dim i As Integer = 0
sqlcon.ConnectionString = strcon
cmd.Connection = sqlcon
Try
sqlcon.Open()
i = cmd.ExecuteNonQuery()
sqlcon.Close()
If i > 0 Then
Return True
Else
Return False
End If
Catch ex As Exception
Console.Write(ex)
Return False
End Try
End Function
