Safe data Update and Insert

[pitaridis]

I have a project which allow the user to insert and update the records of the database. I use the following SQL Command in order to insert a new record in the database.

Dim SQLInsertCommand As String = "Insert into Students (Surname, Address) values('" & txtSurname.Text & "','" & txtAddress.Text & "')"

The problem is that when a user types the ' character, this has as a result the progam to stop because of an SQL syntax error.

I read that this problem can be solved if I change the ' character with two of them. I changed my code to be like the one below.

Dim SQLInsertCommand As String = "Insert into Students (Surname, Address) values('" & FixDatabaseString(txtSurname.Text) & "','" & FixDatabaseString(txtAddress.Text) & "')"

Private Function FixDatabaseString(ByVal TheString As String) As String
    Dim result As String = TheString

    result = result.Replace(CStr("'"), CStr("''"))

    Return result
End Function

The problem is that the program keep stop when I use the ' character. Can someone suggest a working function which solves this problem?

 

[cjard]

Read the PQ link in my signature

 

[pitaridis]

Thanks cjard. I solved the problem.