By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
435,519 Members | 2,295 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 435,519 IT Pros & Developers. It's quick & easy.

Syntax error or access violation

P: 1
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
Dec 4 '06 #1
Share this Question
Share on Google+
2 Replies


shweta123
Expert 100+
P: 692
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
Dec 5 '06 #2

gateshosting
P: 25
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:

Expand|Select|Wrap|Line Numbers
  1. CREATE stored procedure sp_MySP
  2.   @name varchar,
  3.   @id int
  4. AS
  5.  
  6. UPDATE MyTable
  7. SET [Name] = @name
  8. WHERE ID = @id
  9.  
You could call it like this:

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

Hope this helps,

Michael C. Gates
Dec 6 '06 #3

Post your reply

Sign in to post your reply or Sign up for a free account.