I created the webpage and database and it is working well.  However, 
anytime someone puts in a single quote, the database errors out.

ie: Robert's, '99, it's, etc...

Anything with the single ' and I get an error.  How can I overcome this?

Robert

Hi Robert

I assume you are using an SQL query to insert the data.  Any apostrophes
will cause errors in SQL as you can see if you look at the statement:
INSERT INTO table (whatever, something, number) VALUES ('string value's',
'blah', 10);

You can easily solve this by inserting another apostrophe as such: (string
value''s, 'blah', 10)

To do this in ASP you will need to use the replace function to add an extra
apostrophe if needed as follows:

strText = Replace(Request.form("TextFromUser"), "'", "''")

strText can now safely be passed to your SQL insert statement.

Cheers
Darrell

-----Original Message-----
From: Robert Harrigan [mailto:rharrigan@f...]
Sent: 16 July 2002 16:56
To: Access ASP
Subject: [access_asp] Adding content to database through webpage


I created the webpage and database and it is working well.  However,
anytime someone puts in a single quote, the database errors out.

ie: Robert's, '99, it's, etc...

Anything with the single ' and I get an error.  How can I overcome this?

Robert

Where would I add that to this string?

*********************

	'Start building the SQL strings with the required fields
	strInsert = "Insert into FAQ (Question,Answer"
	strValues = "Values('" & CStr(Request.Form("Question")) & _
		"','" & CStr(Request.Form("Answer")) & "'"

	'Add Category
	If Len(Request.Form("Category")) > 0 Then
		'Add the column name to the insert string
		strInsert = strInsert & ",S_Category"
		'Add the value to the value string
		strValues = strValues & ",'" & Cstr(Request.Form
("Category")) & "'"
	End If

	'Create and open the database object
	Set objConn = Server.CreateObject("ADODB.Connection")
	objConn.Open "DSN=FAQ"

	'Create the command object
	Set objCmd = Server.CreateObject("ADODB.Command")

	'Set the command object properties
	Set objCmd.ActiveConnection = objConn
	objCmd.CommandText = strInsert & ") " & strValues & ")"
	objCmd.CommandType = adCmdText

	'Execute the command
	objCmd.Execute

*********************
Thanks!
Robert

Hi Robert

You could do it before you actually execute your SQL statement and populate
a couple of variables like:

strQuestion = CStr(Replace(Request.form("Question"), "'", "''"))
strAnswer = CStr(Replace(Request.form("Answer"), "'", "''"))
strCategory = CStr(Replace(Request.form("Category"), "'", "''"))

Which you would then call as:

strInsert = "Insert into FAQ (Question,Answer"
	strValues = "Values('" & strQuestion & "','" & strAnswer & "'" etc etc.

Cheers
Darrell


-----Original Message-----
From: Robert Harrigan [mailto:rharrigan@f...]
Sent: 17 July 2002 19:13
To: Access ASP
Subject: [access_asp] RE: Adding content to database through webpage


Where would I add that to this string?

*********************

	'Start building the SQL strings with the required fields
	strInsert = "Insert into FAQ (Question,Answer"
	strValues = "Values('" & CStr(Request.Form("Question")) & _
		"','" & CStr(Request.Form("Answer")) & "'"

	'Add Category
	If Len(Request.Form("Category")) > 0 Then
		'Add the column name to the insert string
		strInsert = strInsert & ",S_Category"
		'Add the value to the value string
		strValues = strValues & ",'" & Cstr(Request.Form
("Category")) & "'"
	End If

	'Create and open the database object
	Set objConn = Server.CreateObject("ADODB.Connection")
	objConn.Open "DSN=FAQ"

	'Create the command object
	Set objCmd = Server.CreateObject("ADODB.Command")

	'Set the command object properties
	Set objCmd.ActiveConnection = objConn
	objCmd.CommandText = strInsert & ") " & strValues & ")"
	objCmd.CommandType = adCmdText

	'Execute the command
	objCmd.Execute

*********************
Thanks!
Robert