Wrox Programmer Forums

Need to download code?

View our list of code downloads.

| FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
ASP.NET 1.0 and 1.1 Professional For advanced ASP.NET 1.x coders. Beginning-level questions will be redirected to other forums. NOT for "classic" ASP 3 or the newer ASP.NET 2.0 and 3.5
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ASP.NET 1.0 and 1.1 Professional section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
 
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old September 11th, 2006, 02:25 AM
Authorized User
 
Join Date: Feb 2006
Location: , , Singapore.
Posts: 53
Thanks: 0
Thanked 0 Times in 0 Posts
Default 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
__________________
Scripts82
  #2 (permalink)  
Old September 11th, 2006, 06:44 AM
Friend of Wrox
Points: 1,935, Level: 17
Points: 1,935, Level: 17 Points: 1,935, Level: 17 Points: 1,935, Level: 17
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2004
Location: United Kingdom
Posts: 550
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
  #3 (permalink)  
Old September 11th, 2006, 06:49 AM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

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."
  #4 (permalink)  
Old September 11th, 2006, 07:30 AM
Authorized User
 
Join Date: Sep 2006
Location: , , India.
Posts: 66
Thanks: 0
Thanked 0 Times in 0 Posts
Default

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





  #5 (permalink)  
Old September 11th, 2006, 08:09 AM
Friend of Wrox
Points: 1,935, Level: 17
Points: 1,935, Level: 17 Points: 1,935, Level: 17 Points: 1,935, Level: 17
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2004
Location: United Kingdom
Posts: 550
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
  #6 (permalink)  
Old September 11th, 2006, 08:30 AM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

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."
  #7 (permalink)  
Old September 11th, 2006, 08:37 AM
Friend of Wrox
Points: 1,935, Level: 17
Points: 1,935, Level: 17 Points: 1,935, Level: 17 Points: 1,935, Level: 17
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Aug 2004
Location: United Kingdom
Posts: 550
Thanks: 0
Thanked 1 Time in 1 Post
Default

Yup

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

Regards
Mike
  #8 (permalink)  
Old September 11th, 2006, 08:55 AM
Wrox Author
Points: 13,255, Level: 49
Points: 13,255, Level: 49 Points: 13,255, Level: 49 Points: 13,255, Level: 49
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Oct 2005
Location: Ohio, USA
Posts: 4,104
Thanks: 1
Thanked 64 Times in 64 Posts
Send a message via AIM to dparsons
Default

No worries =]

"The one language all programmers understand is profanity."
  #9 (permalink)  
Old September 15th, 2006, 07:47 AM
Imar's Avatar
Wrox Author
Points: 72,055, Level: 100
Points: 72,055, Level: 100 Points: 72,055, Level: 100 Points: 72,055, Level: 100
Activity: 100%
Activity: 100% Activity: 100% Activity: 100%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,086
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

 
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?
 


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Trouble writing database MDrumm Classic ASP Databases 5 August 4th, 2005 03:27 PM
Writing to Database - Please Help Dwizz VB.NET 2002/2003 Basics 12 May 11th, 2005 08:37 AM
Writing data to database cdenequolo Classic ASP Databases 2 August 17th, 2004 08:00 PM
writing to a database charliejamesuk MySQL 1 March 25th, 2004 11:10 AM
Writing to an external database scottiegirl MySQL 4 March 2nd, 2004 04:02 PM



All times are GMT -4. The time now is 07:21 AM.


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