SQL statement issue

[Mdiaz]

I am having a heck of a time trying to figure out what is wrong with the following chunk of code. Maybe a new look at it from someone else might see the issue. Thanks...

>>>>>>>>> CODE <<<<<<<<<<<<<<<

Dim loConn, lsSQL, loRs
Set loConn = CreateObject("ADODB.Connection")

' Open a connection to the database
loConn.Open ConnectionString()

' Insert a new record into the table
lsSQL = "SET NOCOUNT ON;" &_
"INSERT INTO WorkOrders" &_
"(Issue,IssueDate,IssueBy,Milage,Priority,UnitID,DeptID,Status,OilWaterFuel,Lights,Mirrors,Breakaway,Wheels,Body,Service,Horn,Steering,WarningGauges,LowAir,Brakes,Engine)" &_
"VALUES ('" & varIssue & "','" & varDate & "'," & varUser & "," & varMilage & "," &_
"'" & varPriority & "'," & varUnitID & "," & varDeptID & ",'" & varStatus & "'," &_
"'" & varOil & "','" & varLights & "','" & varMirrors & "','" & varBreakaway & "'," &_
"'" & varWheels & "','" & varBody & "','" & varService & "','" & varHorn & "'," &_
"'" & varSteering & "','" & varWarning & "','" & varLowAir & "','" & varBrakes & "','" & varEngine & "');" &_
"SELECT @@IDENTITY AS NewID;"

' Execute the SQL statement
Set loRs = loConn.Execute(lsSQL)

' Get the inserted ID
intNewID = loRs.Fields("NewID").value

' Close the connection
loConn.Close()
Set loConn = Nothing

>>>>>>>>>>>>>>>>> END Code <<<<<<<<<<<<<<<<<

the connection string which is located in an include file (.asp) looks like:

ConnectionString = "DSN=NoneYaBusiness;UID=someID;PWD=somePass;"


also, DeptID, UnitID, IssueBy and milage are integers


Thanks for your help,
Mike Diaz...

 

[SQLSister]

The easiest way to check something like this is to print out the SQL statement to see where it is going wrong. MOre than likely it is something to do with your use of the " and '.

BTW, @@idnetity tis the wrong command to use, you will eventually have data integrity problems if you use it. USe scope_identity() instead. The reason why is that @@identity will return the last identity fromthe provess including any identity field created from a trigger on the table.