ASP Form to SQL Server

[wezhogan]

I am trying to insert data from a form into a SQL Server 2005 database. The only code examples I can find are for Access databases. I am having a problem at the execute command. I have tried three different ways to execute but the page goes to an error page instead of to the submitted form page after the submit button is pressed with all three tries. I left all three different ways I tried it in the code and they are all commented out. Can anyone tell me where I am going wrong with this? Thanks so much:

<%
dim txtFirstname 
txtFirstname = Request.Form("txtFirstname")& "<BR /><BR />"
dim txtLastname 
txtLastname = Request.Form("txtLastname")& "<BR /><BR />"
dim txtEmail 
txtEmail = Request.Form("txtEmail")& "<BR /><BR />"
dim txtPhone 
txtPhone = Request.Form("txtPhone")& "<BR /><BR />"
dim txtAddress 
txtAddress = Request.Form("txtAddress")& "<BR /><BR />"
dim txtCity 
txtCity = Request.Form("txtCity")& "<BR /><BR />"
dim txtState 
txtState = Request.Form("txtState")& "<BR /><BR />"
dim txtZipcode
txtZipcode = Request.Form("txtZipcode")& "<BR /><BR />"
dim txtComments
txtComments = Request.Form("txtComments")& "<BR /><BR />"
dim imsg
dim iconf
dim flds
dim strSQL
dim conn
dim comm
dim db

dept = "[e-mail]"
strSQL = "INSERT INTO Requests (CATEGORY, FIRSTNAME, LASTNAME, EMAIL, PHONE, ADDRESS, CITY, STATE, ZIPCODE, COMMENTS) VALUES ('" & dept & "', '" & txtFirstname & "', '" & txtLastname & "', '" & txtEmail & "', '" & txtPhone & "', '" & txtAddress & "', '" & txtCity & "', '" & txtState & "', '" & txtZipcode & "', '" & txtComments & "');"

set conn = Server.CreateObject("ADODB.Connection")
conn.open "Provider=SQLOLEDB; Data Source = SQL0; Initial Catalog = RequestForm; User Id = formuser; Password= dilbert;"
set comm = server.CreateObject("ADODB.Command")


'With comm
'	.activeconnection = conn
'	.commandtext = strSQL
	'.commandtype = adCmdText
	'.execute strSQL
'end with

'set db = conn.Execute(strSQL)
'conn.Execute(strSQL)
conn.close





response.redirect("request.asp?submitted=yes")
%>

[WebspoonUK]

Firstly, it is bad practice to use inline SQL like you have used above. It leaves the page open to SQL Injection attacks.

you need to create a function that replaces any single quote with two single quotes before passing through any parameter as follows: -

function makeSQL(str)
  str = replace(str, "'", "''")
  return str
end function

I would then create a stored procedure called "sp_AddRequest" to do this and code it as follows: -

dim oCmd, iLeadID,iOutput,Connection,dept
dept = makeSQL(request("dept"))
Connection = "Provider=SQLOLEDB; Data Source = SQL0; Initial Catalog = RequestForm; User Id = formuser; Password= dilbert;"
set cn = server.CreateObject("ADODB.Connection")
cn.connectionstring = Connection
cn.open

set oCmd = Server.CreateObject("ADODB.Command")
with oCmd
	.ActiveConnection = cn
	.CommandText = "sp_AddRequest"
	.CommandType = adCmdStoredProc
	.Parameters.Append .CreateParameter("@dept", adVarchar, adParamInput, 100, dept)
'add further parameters here
	.Execute
end with
set oCmd = nothing
cn.close
set cn = nothing

NB. adParamInput and adCmdStoredProc are standard ado constants that can be defined in an include file (adovbs.inc)

If you're still having trouble, try adding some response.write, response.end comments after the connection is made to see whether a connection to your DB has been established. E.g.

response.write cn.State
response.end

The above should output '1' or 'true'

Hope this helps