Help Debugging Insert Query

[ejrhodes]

Here is my DB Schema
Column_name Type Comp Length Prec Scale Nullable
ID int no 4 10 0 no
Project Status nvarchar no 80 yes
JCO Status nvarchar no 510 yes
Cost Center float no 8 53 NULL yes
JCO Totals money no 8 19 4 no
JCO Amt 5 money no 8 19 4 no
JCO Amt 4 money no 8 19 4 no
JCO Amt 3 money no 8 19 4 no
JCO Amt 2 money no 8 19 4 no
Acct Mgr nvarchar no 200 yes
Bus Unit nvarchar no 200 yes
Bus Unit Liason nvarchar no 200 yes
Bill Date smalldatetime no 4 yes
Actual Cost money no 8 19 4 no
FIn Comp float no 8 53 NULL yes
Financial Status ntext no 16 yes
Ticket Number nvarchar no 510 yes
Proposal int no 4 10 0 yes
Project Number nvarchar no 10 no
Project Title nvarchar no 510 yes
Project Description ntext no 16 yes
Requested by nvarchar no 510 yes
Project Manager nvarchar no 510 yes
Engineer nvarchar no 510 yes
Approval Date smalldatetime no 4 yes
Opened Date smalldatetime no 4 yes
Original Est Comp nvarchar no 510 yes
Date in Prod nvarchar no 510 yes
Current Est Comp smalldatetime no 4 yes
Value money no 8 19 4 no
Project Type nvarchar no 510 yes
Activity nvarchar no 510 yes
Status ntext no 16 yes
Impl Resource nvarchar no 510 yes
JCO Amt money no 8 19 4 no
JCO Approval Date nvarchar no 510 yes
Projected Hours float no 8 53 NULL yes
Actual Hours float no 8 53 NULL yes
Status Date smalldatetime no 4 yes
Location nvarchar no 510 yes
Close Date nvarchar no 510 yes
Internal Comments nvarchar no 510 yes
COC Recd smalldatetime no 4 yes
Field1 nvarchar no 510 yes
Closed bit no 1 no
BudgetEngineerHours money no 8 19 4 yes
BudgetTechHours money no 8 19 4 yes
BudgetPMHours money no 8 19 4 yes
Group char no 10 yes
Archived int no 4 10 0 yes
COCSent smalldatetime no 4 yes
PMSurveySent smalldatetime no 4 yes
PMSurveyReceived smalldatetime no 4 yes
Complexity int no 4 10 0 yes
Estlabor nvarchar no 40 yes
oldenumber nvarchar no 60 yes



Here is my code:

Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.open "SELECT * FROM projects", conn2, 1, 3
objRS.AddNew
objRS("Proposal") = Request("eNumber")
objRS("Project Number") = Request("ProjectNumber")
objRS("Ticket Number") = Request("ticketnumber")
objRS("Project Manager") = Request("ProjectManager")
objRS("Acct Mgr") = Request("AccountManager")
objRS("bus unit liason") = Request("CustomerBul")
objRS("bus unit") = request("businessunit")
objRS("Project Title") = Request("Title")
objRS("Approval Date") =Request("ApprovalDate")
objRS("Engineer") = Engineer
objRS("Opened Date") = Request("OpenedDate")
objRS("Project Description") = Request("projectdescription")
objRS("Project Status") = Request("progress")
objRS("Project Type") = Request("ProjectType")
objRS("Location") = Request("Location")
objRS("Value") = Request("EstimatedValue")
objRS("Requested By") = Request("Requestor")
objRS("Cost Center") = trim(Request("CostCenter"))
objRS("BudgetPMHours") = Request("pmhours")
objRS("Field1") = Request("PercentageCompletion")
objRS("Internal Comments") = Request("InternalComments")
objRS("Group") = trim(request("group"))
objRS.Update
objRS.Close
Set objRS = Nothing


Here is the error


[PHP]enumber 2292
add Submit
ticketnumber WC08260970
ProjectManager April Bourquin
Group Projects
projectnumber 99998
costcenter 123456
ApprovedDate 06/01/2003
accountmanager John Kunkel
PercentageCompletion 0
action addproject
customerbul Roger Swanson
Location Aranda
OpenedDate 06/01/2003
businessunit Corp Telecom
estimatedvalue 12345
Title test
requestor Edward Rhodes
internalcomments Test
projectdescription 12345 Test
Progress 1
ProjectType Application Deployment
pmhours 99

Microsoft OLE DB Provider for ODBC Drivers error '80004005'

[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near 'Status'.

/development/etracking/booked.asp, line 79
/PHP]


Any of you know why this is failing?

[khaki]

hi ejrhodes...

you might want to post this question in the PHP forum so that it gets a chance to be seen by the proper group who may be able to solve it for you.

;) k

[ejrhodes]

Originally posted by khaki
hi ejrhodes...

you might want to post this question in the PHP forum so that it gets a chance to be seen by the proper group who may be able to solve it for you.

;) k

But it is an ASP page running on SQL Server 7 :)
Looks like the [/php] tag is what made you think it was php :)_

[khaki]

yeah.... sorry

I guess I jumped the gun.
(I see "PHP" and I bail-out immediately :rolleyes: )

pay no attention to me and my phobias :)

;) k

[ejrhodes]

Well this way works:

sql="Insert into projects (proposal, [project number], [ticket number], [project manager], [acct mgr], [bus unit liason], [bus unit], [project title], [approval date], [engineer], [opened date], [project description], [project status], [project type], [location], [value], [requested by], [cost center], [budgetpmhours], field1, [internal comments], [group])"
sql= sql & " values (" & Request("eNumber")
sql= sql & ", '" & Request("ProjectNumber")
sql= sql & "', '" & Request("ticketnumber")
sql= sql & "', '" & Request("ProjectManager")
sql= sql & "', '" & Request("AccountManager")
sql= sql & "', '" & Request("CustomerBul")
sql= sql & "', '" & request("businessunit")
sql= sql & "', '" & Request("Title")
sql= sql & "', '" & Request("ApprovedDate")
sql= sql & "', '" & Engineer
sql= sql & "', '" & Request("OpenedDate")
sql= sql & "', '" & Request("projectdescription")
sql= sql & "', '" & "New"
sql= sql & "', '" & Request("ProjectType")
sql= sql & "', '" & Request("Location")
sql= sql & "', " & Request("EstimatedValue")
sql= sql & ", '" & Request("Requestor")
sql= sql & "', '" & trim(Request("CostCenter"))
sql= sql & "', " & Request("pmhours")
sql= sql & ", '" & Request("PercentageCompletion")
sql= sql & "', '" & Request("InternalComments")
sql= sql & "', '" & trim(request("group"))
sql= sql & "')"

set qry= conn2.execute (sql)



but this does not:

Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.open "SELECT * FROM projects", conn2, 1, 3
objRS.AddNew
objRS("Proposal") = Request("eNumber")
objRS("Project Number") = Request("ProjectNumber")
objRS("Ticket Number") = Request("ticketnumber")
objRS("Project Manager") = Request("ProjectManager")
objRS("Acct Mgr") = Request("AccountManager")
objRS("bus unit liason") = Request("CustomerBul")
objRS("bus unit") = request("businessunit")
objRS("Project Title") = Request("Title")
objRS("Approval Date") =Request("ApprovalDate")
objRS("Engineer") = Engineer
objRS("Opened Date") = Request("OpenedDate")
objRS("Project Description") = Request("projectdescription")
objRS("Project Status") = "New"
objRS("Project Type") = Request("ProjectType")
objRS("Location") = Request("Location")
objRS("Value") = Request("EstimatedValue")
objRS("Requested By") = Request("Requestor")
objRS("Cost Center") = trim(Request("CostCenter"))
objRS("BudgetPMHours") = Request("pmhours")
objRS("Field1") = Request("PercentageCompletion")
objRS("Internal Comments") = Request("InternalComments")
objRS("Group") = trim(request("group"))
objRS.Update
objRS.Close
Set objRS = Nothing


anyone know why method 2 does not?

[cmelnick]

I have looked over this quite a bit, and tried my own examples, but mine works every time. Would it be possible for you to post your code (maybe with your connection string left out if that has a password in it) so I can see exactly where the error is occuring?

Chris