View Single Post
Old September 11th, 2006, 02:25 AM
Scripts82 Scripts82 is offline
Authorized User
Join Date: Feb 2006
Location: , , Singapore.
Posts: 53
Thanks: 0
Thanked 0 Times in 0 Posts
Default Writing to Database


Need some help with writing data to the database.
I'm familiar with the basic syntax...
Currently, I have a textbox (txtComments) that captures my comments and update the database.
My code is as follows:

Dim strSQL as string = "INSERT INTO dbo.myTable(comments, name) VALUES('myName', '" & txtComments.text & "')"
Dim strCon As String = "Server=myServer; database=myDB"
Dim con As SqlConnection
Dim cmd As SqlCommand

    con = New SqlConnection(strCon)
    cmd = New SqlCommand(strSql.ToString, con)
    if cmd.ExecuteNonQuery() = 0 then
        response.write("Not Updated")
    end if

Catch ex as exception

end try
The above code works fine until I try to insert messages with Apostrophe(')
For example the following comments will throw an exception: My Cousin's Photos

I've stepped through the codes and realised that because of the apostrophe, my SQL command is actually truncated.

Are there any simple solutions to solve this ? Encoding? Built in functions? Because I don't want to specially write a function to handle these types situation.

Thanks in advance!