Run a SQL script from asp

[Mych]

I have the following in my asp page

[code]
strCrLf = System.Environment.Newline

strSQL = "DECLARE " & strCrLf
strSQL = strSQL & "@txtPval binary(16), " & strCrLf
strSQL = strSQL & "@insertStr VARCHAR(8000), " & strCrLf
strSQL = strSQL & "@curID INT " & strCrLf
strSQL = strSQL & "SET @curID = " & InCaseRef & strCrLf
strSQL = strSQL & "SET @insertStr = " & newupdate & strCrLf
strSQL = strSQL & "SELECT @txtPval = TEXTPTR(Updates) FROM D_INCIDENTS WHERE IncidentRef=@curID " & strCrLf
strSQL = strSQL & "UPDATETEXT D_INCIDENTS.Updates @txtPval Null 0 @insertStr"

my_Conn.Execute (strSQL)
[code]

Basically I need to brake the script into lines and I have tried

vbCr
vbLf
vbCRLf
chr$(13) & chr$(10)
char(13) + char(10)
ControlChars.Cr
ControlChars.Lf
ControlChars.Newline

for strCRLf ane non work.

Any Ideas? Any help appreciated

[aspdaddy]

What language/datbase is the Query written in?

Have you tried running the SQL in the database directly, to see if it compiles OK.

[Mych]

I'm using SQL7 and I did run code directly and it compiled ok.

In SQL I had each section on a newline hence trying to get the newline in the string. You will probably gather I'm new to SQL and asp. What I'm trying to do is port over from Access to ASP and SQL an Incident Diary that I wrote using ACCESS.

[TheNige]

Why not just create a stored procedure? It would be much much easier.

[Mych]

Not sure how to create a stored proc...

Meanwhile I solved the problem...

First I didn't need the line feeds and second, (the reason why the code didn't work), was that I forgot to wrap the newupdate string between single quotes!

so my code now is

strSQL = "DECLARE "
strSQL = strSQL & "@txtPval binary(16), "
strSQL = strSQL & "@insertStr VARCHAR(8000), "
strSQL = strSQL & "@curID INT "
strSQL = strSQL & "SET @curID = " & InCaseRef
strSQL = strSQL & "SET @insertStr = '" & newupdate & "'"
strSQL = strSQL & "SELECT @txtPval = TEXTPTR(Updates) FROM D_INCIDENTS WHERE IncidentRef=@curID "
strSQL = strSQL & "UPDATETEXT D_INCIDENTS.Updates @txtPval Null 0 @insertStr"
 my_Conn.Execute (strSQL)

Is there anywhere I can get some basic knowledge about creating stored procs and then 'calling/using' then from asp pages?