Can not enter ' in the string to store into sql server database using vb.net
Hello all,
i am using vb.net to enter some information in sql server databse. using below code everthig works fine. but if we enter single inverted coma (') in the tex box or in rich text box then it is giving me sql error "SQL error Unclosed quotation mark before the character string" Please help me out to find solution for this one.
Dim cnSQL = New SqlClient.SqlConnection
cnSQL.ConnectionString = "workstation id=STATH26;packet size=4096;user id=sysop;data source=ACAD-IMAGE; persist security info=True; initial catalog=AcadAdmin;password
Dim cmSQL As SqlClient.SqlCommand
Dim strSQL As String
cnSQL.Open()
Try
strSQL = "INSERT INTO dbo.TechProject(Name, [Project Name], Priority, Description, [Start Date], [Est End Date], [Date Completed], Completed)VALUES('" & TName.Text & "', '" & TProjectName.Text & "' , '" & CBPriority.SelectedItem & " ','" & RTDescription.Text & "', '" & DTStartDate.Value & "','" & DTEstEndDate.Value & "','" & DTDateCompleted.Value & "','" & CBCompleted.SelectedItem & "')"
cmSQL = New SqlClient.SqlCommand(strSQ
cmSQL.ExecuteNonQuery()
' Close and Clean up objects
MsgBox("Data Saved Successfully")
cnSQL.Close()
cmSQL.Dispose()
cnSQL.Dispose()
Me.Close()
Catch Exp As SqlClient.SqlException
MsgBox(Exp.Message, MsgBoxStyle.Critical, "SQL Error")
Catch Exp As Exception
MsgBox(Exp.Message, MsgBoxStyle.Critical, "General Error")
End Try
i am using vb.net to enter some information in sql server databse. using below code everthig works fine. but if we enter single inverted coma (') in the tex box or in rich text box then it is giving me sql error "SQL error Unclosed quotation mark before the character string" Please help me out to find solution for this one.
Dim cnSQL = New SqlClient.SqlConnection
cnSQL.ConnectionString = "workstation id=STATH26;packet size=4096;user id=sysop;data source=ACAD-IMAGE; persist security info=True; initial catalog=AcadAdmin;password
Dim cmSQL As SqlClient.SqlCommand
Dim strSQL As String
cnSQL.Open()
Try
strSQL = "INSERT INTO dbo.TechProject(Name, [Project Name], Priority, Description, [Start Date], [Est End Date], [Date Completed], Completed)VALUES('" & TName.Text & "', '" & TProjectName.Text & "' , '" & CBPriority.SelectedItem & " ','" & RTDescription.Text & "', '" & DTStartDate.Value & "','" & DTEstEndDate.Value & "','" & DTDateCompleted.Value & "','" & CBCompleted.SelectedItem & "')"
cmSQL = New SqlClient.SqlCommand(strSQ
cmSQL.ExecuteNonQuery()
' Close and Clean up objects
MsgBox("Data Saved Successfully")
cnSQL.Close()
cmSQL.Dispose()
cnSQL.Dispose()
Me.Close()
Catch Exp As SqlClient.SqlException
MsgBox(Exp.Message, MsgBoxStyle.Critical, "SQL Error")
Catch Exp As Exception
MsgBox(Exp.Message, MsgBoxStyle.Critical, "General Error")
End Try
When executing an INSERT statement as a string, any internal single quotes used as data must be doubled up, so
O'Leary
would be in the string as
O''Leary
To handle this in most programming languages, I use a function, usually named something like SQLSafe, with a parameter of the value to be appended to the SQL String, something like the code below.
HOWEVER!! Making this a stored procedure, and passing the parameters as parts of the stored procedure, would make your coding much safer from SQL injection attacks.
O'Leary
would be in the string as
O''Leary
To handle this in most programming languages, I use a function, usually named something like SQLSafe, with a parameter of the value to be appended to the SQL String, something like the code below.
HOWEVER!! Making this a stored procedure, and passing the parameters as parts of the stored procedure, would make your coding much safer from SQL injection attacks.
Function SQLSafe(InVal As String) As String
SQLSafe = Replace(InVal, "'", "''")
END FUNCTION
--------------------------------------
Your code would look like:
strSQL = "INSERT INTO dbo.TechProject(Name, [Project Name], Priority, Description, [Start Date], [Est End Date], [Date Completed], Completed)VALUES('" & SQLSafe(TName.Text) & "', '" & SQLSafe(TProjectName.Text) & "' , '" & SQLSafe(CBPriority.SelectedItem) & " ','" & SQLSafe(RTDescription.Text) & "', '" & DTStartDate.Value & "','" & DTEstEndDate.Value & "','" & DTDateCompleted.Value & "','" & SQLSafe(CBCompleted.SelectedItem) & "')"
Premium Content
You need an Expert Office subscription to comment.Start Free Trial