Syntax error or access violation

[J W]

I have an ASP page in which there is embedded SQL. I am trying to use variables in the SQL but am getting the error 'syntax error or access violation'.
In the past, I have successfully used variables for table names and within WHERE clauses in SQL in an ASP page. This time, I am trying to use variables in the SELECT portion of an INSERT statement.

(1) The following code works in a SQL stored procedure:
DECLARE @BatchId uniqueidentifier
SELECT @BatchId = NEWID()

INSERT INTO sgCustomASPBatch
SELECT @BatchId,e.EngagementId,GETDATE()
FROM Engagement e
WHERE e.Name like '%ASP'

(2) Here is the same code in ASP which gets the syntax error:
dim BatchId

' get new id for batch
sSQL2 = "SELECT NEWID() 'NewId' "
set rst2 = oUtility2.Execute(Session("ReportsDSN"),sSQL2)
BatchId = rst2("NewId")

set rst2 = nothing

sSQL2 = "INSERT INTO sgCustomASPBatch " & _
"SELECT " & BatchId & _
" , EngagementId, GETDATE() " & _
"FROM Engagement e " & _
"WHERE e.EngagementId in ('" & pEngId & "') "
set rst2 = oUtility2.Execute(Session("ReportsDSN"),sSQL2)

(3) Without the variable for BatchId, this code works in ASP:
sSQL2 = "INSERT INTO sgCustomASPBatch " & _
"SELECT NEWID(), EngagementId, GETDATE() " & _
"FROM Engagement e " & _
"WHERE e.EngagementId in ('" & pEngId & "') "
set rst2 = oUtility2.Execute(Session("ReportsDSN"),sSQL2)


What do I need to change in the syntax regarding the variable BatchId to make example 2 above work in ASP?

Thanks.
Judy

[shweta123]

write the sql as this
sSQL2 = "INSERT INTO sgCustomASPBatch " & _
"SELECT " & '" & BatchId & "' & _
" , EngagementId, GETDATE() " & _
"FROM Engagement e " & _
"WHERE e.EngagementId in ('" & pEngId & "') "
set rst2 = oUtility2.Execute(Session("ReportsDSN"),sSQL2)


just a thought

bye
Shweta

[gateshosting]

It looks like you are going to want to get into stored procedures. You can't use DECLARE @ANYTHING in a SQL statement from ASP. At least, I don't think so.

But if you have a stored procedure that is:

CREATE stored procedure sp_MySP
  @name varchar,
  @id int
AS
 
UPDATE MyTable
SET [Name] = @name
WHERE ID = @id
 

You could call it like this:

conn.execute("exec sp_MySP 'Michael', 10") and it would work...

Hope this helps,

Michael C. Gates