November 18, 2003 at 11:26 am
I am running Active Server Pages (ASP) and SQL Server 2000, need to input a single double quote for the designation (inches) and a single quote (') for (feet), is there a way to input this into the database without asp changing the input into the database. Example ( 8" Opening) become (8) leaving the " and Opening out of the database field.
Any help would be appreciated
November 18, 2003 at 1:31 pm
I'm a little confused as to what you mean. Do you need to strip out the numerical value (8) from the input? Or do you need to figure out how to get 8" into the db?
Steve Jones
http://qa.sqlservercentral.com/columnists/sjones
The Best of SQL Server Central.com 2002 - http://qa.sqlservercentral.com/bestof/
Follow me on Twitter: http://www.twitter.com/way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
November 18, 2003 at 1:41 pm
As an asp / sql dev myself I only see a few ways:
+Use a Stored proc and manipulate the string in SQL before inserting.
+Create a User Defined SQL function to parse these strings, then use function from SP (Have never try this though).
Pretty much your sql will have to do what asp could do before it comes in. If this is just one record at a time then not too bad performance wise, but if you're inserting a large number at a time I have found SQL is significantly slower at parsing than ASP is.
How is the data coming into SQL. If it is a file DTS can be handy for these conversions
November 19, 2003 at 12:23 am
Don't know if I really understand, but I would change theä underlying table structure, if possible and use separate columns for inches and feet. This way you don't run into conversion problems at all and you still able to do calculations
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
November 19, 2003 at 1:34 am
We need a commonmans section in our community ,
There is no hitch in putting a single quote inside a string
Say for exmple in asp
<%
Dim x
X= "this 'quote will not create problem" '
'In asp this will not create trouble but in sql it will ,we will deal this too
%>
But
<%
Dim x
X= "this "quote will create problem"
%>in this case you have to prefix the double quote with another double quote,
<%
Dim x
X= "now this""quote will not create problem"
%>But single quote will create problem in SQL statement to avoid this you can use the asp replace fuction say
<%
dim sql
name="john’s "
name=replace(name,"'","''") ‘replece a single quote with two single quotes
sql="select * from employees where name ='" & name & "'"
%>
regards
john
November 19, 2003 at 1:47 am
quote:
We need a commonmans section in our community ,
what about the 'Anything that....' forum or the 'General' forums?
Frank
--
Frank Kalis
Microsoft SQL Server MVP
Webmaster: http://www.insidesql.org/blogs
My blog: http://www.insidesql.org/blogs/frankkalis/[/url]
November 19, 2003 at 5:30 am
Agree. Posting in the wrong section isnt that severe of an offense!
Andy
November 19, 2003 at 7:57 am
Can you post your ASP code?
November 19, 2003 at 10:19 am
Use a stored procedure pass the strings as variables and then you can handle them with:
QuoteName(@VariableWithQuotesInIt ,'''')
* Noel
December 11, 2003 at 4:47 am
To solve this problem, just replace the single quote with double singel quote thats it.
(ie) Replace(varName, "'", "''")
It is better to use this way for all types of string data handling in the query.
since at any time the string value may have a single quote in its value.
ex: myVar = "Saravana's brother"
In this case the sinele quote has to be replace to 2 single quotes and insert in to the table.
ie) myVar = Replace("Saravana's brother","'","''")
give: myVar = "Saravana''s brother"
eventhough we have 2 single quote in the data, the data stored in the DB is just "Saravana's brother"
quote:
I am running Active Server Pages (ASP) and SQL Server 2000, need to input a single double quote for the designation (inches) and a single quote (') for (feet), is there a way to input this into the database without asp changing the input into the database. Example ( 8" Opening) become (8) leaving the " and Opening out of the database field.
Any help would be appreciated
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply