Apostrophe in SQL 2000 1

[Djbell]

Hi all

I have a VB.net frontend that writes to a SQL Server 2000 database, one of my fields on my form is just a text box that a user type whatever into. I am using just the basic INSERT INTO SQl command to post to the SQL 2000 Database, unfortuantly I get SQL errors if a user uses any apostrophes in the text, how do I get it so if an apostrophe is used it inserts it into the SQL field like normal text.

Regards

Douglas Bell

 

[bborissov]

Double them when you built query:

Let say your user input [Some text for O'Mara]
You should double the single quote so the text you sent should look like this:
[Some text for O''Mara]
And this [''] is NOT a single double quote but double single quotes [' '] (w/o space between them)

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[Djbell]

Hi

Thanks for the reply.

Sorry if I have misunderstood you, but are you saying the user should use two '' instead of ' when using apostrophes?

Regards

Douglas Bell

 

[chrissie1]

No you should be using the paramters collection of the command object instead of concatenation. That will take away all you problems without doing anything.

This question BTW was better asked in the VB.Net forum.

Christiaan Baes
Belgium

My Blog

 

[bborissov]

No the user should use whatever He/She wants. It is your job to make single apostrophe to double.
I am not familiar with VB.NET but I am sure that there is a string manipulation function where you can replace some string with other. But I agree with chrissie1, better use parameters. That way you could protect yourself from SQL Injections.

Borislav Borissov
VFP9 SP2, SQL Server 2000/2005.
Microsoft MVP VFP

 

[Djbell]

Hi Christiaan

Thanks for the reply, sorry as I thought this was an SQL error thats why I posted here.

I am only really learning VB.NET, at the moment my form is really basic witha few combo boxes and a few text boxes, a button for save wich then takes the information in the boxes and inserts them into the SQL database.

Could you explain a little on the paramters collection of the command object for me please.

Regards

Douglas Bell

 

[chrissie1]

alright then

dim con as new sqlconnection()
dim com as new sqlcommand
con.connectionstring = "" 'be creative
con.open
com.connection = con
com.commandtext = "insert into tbl_table (field1, field2) VALUES (@field1, @field2)"
com.parameters.addwithvalue("@field1", textbox1.text)
com.parameters.addwithvalue("@field2", textbox2.text)
com.executenonquery
con.dispose
com.dispose

and all this of the top of my head.


Christiaan Baes
Belgium

My Blog

 

[macleod1021]

and all this of the top of my head"

Think Head & Shoulders could get rid of it???


Sorry...it's early and i couldn't resist

 

[Djbell]

Hi Christiaan

Thanks for the reply, wish I knew how to do that ealier.

Regards

Douglas Bell