p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   ASP.NET 1.0 and 1.1 Professional (http://p2p.wrox.com/forumdisplay.php?f=57)
-   -   Writing to Database (http://p2p.wrox.com/showthread.php?t=47637)

Scripts82 September 11th, 2006 02:25 AM

Writing to Database
 
Hi,

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:

Code:

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

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

Catch ex as exception

Finally
    con.dispose
    cmd.dispose
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!

Scripts82

mike_remember September 11th, 2006 06:44 AM

Hi

This won't be possible if you use text command to insert the data, i.e. "insert into ........". This can be done if you use a stored procedure to insert the data.

So the suggestion is to create a simple stored procedure and insert the data with apostrophe.

Regards
Mike

Regards
Mike

dparsons September 11th, 2006 06:49 AM

Quote:

quote:Originally posted by mike_remember
 Hi

This won't be possible if you use text command to insert the data, i.e. "insert into ........". This can be done if you use a stored procedure to insert the data.

So the suggestion is to create a simple stored procedure and insert the data with apostrophe.

Regards
Mike

Regards
Mike
Thats an incorrect statement, you can pass SQL commands such as scripts is describing without creating a Stored Procedure. The problem here is the ('), that is a reserved character in TSQL as it defines the bounds for data that is being placed in X column (where strings are concerned).

The Answer here is to replace the (') with a special character (e.g. the ASCII equivelant) and that will correct the problem.

"The one language all programmers understand is profanity."

SriRamaKrishna September 11th, 2006 07:30 AM

Hi ,
  U cant use quotations in variables which is included in query forming statement.It always gives problem.
Better try to give values as parameters.
eg.
  string sql = "insert into (col1,col1) values (@parameter1 ,@parameter1)
   sqlcommand cmd = new sqlcommand(sql,con);
cmd.parameters.add(@parameter1,value)
cmd.parameters.add(@parameter2,value)

now excecute and try






mike_remember September 11th, 2006 08:09 AM

Quote:

quote:Originally posted by dparsons

Thats an incorrect statement, you can pass SQL commands such as scripts is describing without creating a Stored Procedure. The problem here is the ('), that is a reserved character in TSQL as it defines the bounds for data that is being placed in X column (where strings are concerned).

The Answer here is to replace the (') with a special character (e.g. the ASCII equivelant) and that will correct the problem.

"The one language all programmers understand is profanity."
Come on

How can you say that it is possible to insert an apostrophe using text command........ In any scenario, you won't be able to insert that single quote to the database except procedure.

The only alternate is to replace the single quote with space or something other character.

Regards
Mike

dparsons September 11th, 2006 08:30 AM

I wasn't saying its possible to INSERT an (') in a text command, from the way you phrased your reply it sounded as though you were saying it is not possible to call INSERT statements in text commands.

As you can see, my reply mirrors your last sentence: "replace the ' with a special character."

"The one language all programmers understand is profanity."

mike_remember September 11th, 2006 08:37 AM

Yup

You are right....guess confusions all over. :)

Regards
Mike

dparsons September 11th, 2006 08:55 AM

No worries =]

"The one language all programmers understand is profanity."

Imar September 15th, 2006 07:47 AM

 
Quote:

quote:Come on
Quote:


How can you say that it is possible to insert an apostrophe using text command........ In any scenario, you won't be able to insert that single quote to the database except procedure.

The only alternate is to replace the single quote with space or something other character.
What?? How can you say that? It *is* possible, but you just didn't know how to do it.... ;)

Replacing an apostrophe with a space or another character sounds like a bad thing to do. If you need to present the data later again, how do you distinguish between real spaces and the one you added to replace the apostrophe? You can't, so you end up with corrupt data.

But since database vendors got sick and tired of complaints from all the O'Brien families, they came up with a simple and successful solution: replace every ' with two single ' characters:

INSERT INTO MyTable(Name) VALUES('O''Brien')

successfully inserts a single apostrophe in the database.

So the code example could be rewritten as this:
Code:

Dim comments As String
comments = txtComments.Text.Replace("'", "''")
Dim strSQL as string = "INSERT INTO dbo.myTable(comments, name) VALUES('myName', '" & comments & "')"

That said, I agree that it's much better to use parameterized queries as they protect you better against "SQL injection".

Cheers,

Imar
---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.
Author of ASP.NET 2.0 Instant Results and Beginning Dreamweaver MX / MX 2004
While typing this post, I was listening to: Insight by Joy Division (Track 6 from the album: Heart And Soul (CD 1)) What's This?


All times are GMT -4. The time now is 08:55 PM.

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.