Insert into (SQL Svr syntax) problem?

[Cole3c]

I am having a problem with my insert values syntax.
Using MS SQL SVR 2K, ASP

Specifically, I have an single/double quote, and end of statement error. The below code gets me close, but inserts something wrong into the table (a single quote?) that causes an error when I attempt to view the data via a web page (asp):

oConn1.execute "insert into crimcivdetail (orderid, state) values ('"& orderid & "', '" & Request.form("State1") & "'"

And this causes similar errors:

oConn1.Execute "Insert into orderdetail (SubjectNumber, ClientID, ServiceNumber, "_
& "daterequested) values (" & subjectNumber & "', '" & Request.Form("COMPID")_
& "', 22 ,'" & "', GetDate())'"

Suggestions?

TIA,
Cole

 

[DotNetGnat]

use replace() function to solve the single quote problems...

oConn1.execute "insert into crimcivdetail (orderid, state) values ('"& Replace(orderid,"'","''") & "', '" & Replace(Request.form("State1"),"'","''") & "'"

oConn1.Execute "Insert into orderdetail (SubjectNumber, ClientID, ServiceNumber, "_
                    & "daterequested) values (" & subjectNumber & "', '" & Request.Form("COMPID")_
                    & "', 22 ,[s]'" & "'[/s], GetDate())'"

-DNG

 

[Cole3c]

I'm not having a problem with the inserted info containing single quotes that I can't allow, I'm having problems with MY syntax having the single quotes and commas and double quotes in the wrong places. The user doesn't get to put in anything-only select-and then I have to process the order.

The entry form feeds the processorder.asp page, and it contains the afore mentioned code that I have twisted.

I don't need Replace here, only help with the correct syntax to feed from ASP to the SQL DB.

 

[DotNetGnat]

ok let me see if i understand you here...

here is the thumb rule...

1. if the variable is a string then you need single quotes...

2. if the variable is an integer then you DONT need single quotes...

now lets look at your queries...

oConn1.execute "insert into crimcivdetail (orderid, state) values ("& orderid & ", '" & Request.form("State1") & "'[red])[/red]"

-> you were missing the end braces
-> assuming orderid to be an integer you dont need single quotes...

next one...

oConn1.Execute "Insert into orderdetail (SubjectNumber, ClientID, ServiceNumber, "_
                    & "daterequested) values ('" & subjectNumber & "', '" & Request.Form("COMPID")_
                    & "', 22 , GetDate())'"

you were missing a single quote...if your subjectnumber is an integer then you dont need single quotes...

does that help...

-DNG

 

[Cole3c]

Everything you send is helpful. And I appreciate it!

I now get this error:
Expected statement

/testing/order/bin/processorder.asp, line 98

& "', 22 , GetDate()')"
^

the part of line 97/98 that matters:
values ('" & subjectNumber & "', '" & Request.Form("COMPID")_
& "', 22 , GetDate()')"

 

[DotNetGnat]

ok lets try this:

oConn1.Execute "Insert into orderdetail (SubjectNumber, ClientID, ServiceNumber,"&_
" daterequested) values ('" & subjectNumber & "',&_ 
" '" & Request.Form("COMPID")& "',&_ 
" 22 , GetDate() )"

-DNG

 

[Cole3c]

Unterminated string constant

/testing/order/bin/processorder.asp, line 96

" daterequested) values ('" & subjectNumber & "', &_
----------------------------------------------------^

I'm sorry!

 

[DotNetGnat]

oops...my mistake...

try this:

oConn1.Execute "Insert into orderdetail (SubjectNumber, ClientID, ServiceNumber,"&_
" daterequested) values ('" & subjectNumber & "',"&_ 
" '" & Request.Form("COMPID")& "',"&_ 
" 22 , GetDate() )"

-DNG

 

[Cole3c]

Yeah! Progress!

It nows inputs the info into the two tables (names and such into the OrderSubjects, order info into OrderDetail), and the order appears in the pending orders list. But when I try to view the order (to fill it) I get this error:

error '80020009'

Exception occurred.

/admin/bin/header.asp, line 14

The appropriate code from Header.ASP (line 14 is first):

dim fullname
fullname = ""
if Session("agentID") <> "0" then
dim objRst

set objRst = server.CreateObject("ADODB.Recordset")
objRst.ActiveConnection = Application("DBConnString")

objRst.Source = "select lname,fname from agents where AgentID=" & Session("agentID")
objRst.Open
objRst.movefirst
fullname = objRst("fname") & " " & objRst("lname")
objRst.close
set objRst=nothing
end if
Dim myFontColor1,myFontColor2,myBgColor
if Application("CompID") = "I-NEX" then
myFontColor1 = "maroon"
myFontColor2 = "black"
myBgColor = "white"
else
myFontColor1 = "yellow"
myFontColor2 = "silver"
myBgColor = "maroon"
end if
%>

 

[DotNetGnat]

ok..good to hear that we are progressing...which is line 14??

response.write your sql statement to see if everything is getting passed correctly...

make sure you dont have any typos in your field, variable or table names...

-DNG

 

[Cole3c]

It's old code that has run thousands of time a day for 3 years and works for all other orders. Although we are putting the info into the DB, something is causing it to error when we try to display the info so that we can fill the order.

Line 14 is:
Dim fullname

The full first 14 lines:

<%
'**************************************************************************************************
'Date: 3/20/02
'Customer:
'File Name: index.asp
'Author:
'Purpose:
'Stored Procedures Used:
'**************************************************************************************************
if isEmpty(Session("LogedIn")) then
response.redirect("login.asp")
end if

dim fullname

 

[Cole3c]

The problem is somewhere else. Thanks to DNG, we have it submitting the order into the DB, and it is not retrieving properly for some other reason. I have 7 possible orders selected via check box, and 5 of them work with the same code used, but the 2 that don't work must be ???- I don't know why yet.

 

[DotNetGnat]

i thought so...because your code did not reveal anything suspicious...

-DNG