Convert SQL in ASP 3.0 site to UPDATE ntext

Greetings...
I have an ASP 3.0 website that I am usizing from an MS Access DB to MS SQL
server 2000. While most of my SQL seems to be working perfectly, my memo
fields (ntext) it appears require updating using WRITETEXT.
The following is the sub I tried to use, however, was asked to declare the
pointer variable. After many attempts I'm stuck...
sub db_update_add_note
sql ="SELECT @.ptrval = TEXTPTR(ProjectDesc) " & _
"FROM pmProjects " & _
"WHERE ProjectId = " & ProjectId & " " & _
"WRITETEXT newnote @.ptrval"
response.write sql
on error resume next
cn.execute(sql)
if err.number <> 0 then
b_error = true
error_list.add "db_update_add_note" & err.Number ,"The database update
failed: " & err.Description
else
end if
on error goto 0
end sub
As I'm new to the differences between MS Access SQL and T-SQL I could use
some help.
Thanks...
Message posted via http://www.droptable.com
Sorry... This is the code I attempted to use:
sub db_update_add_note
sql = "DECLARE @.ptrval CURSOR " & _
"SELECT @.ptrval = TEXTPTR(ProjectDesc) " & _
"FROM pmProjects " & _
"WHERE ProjectId = " & ProjectId & " " & _
"WRITETEXT newnote @.ptrval"
response.write sql
on error resume next
cn.execute(sql)
if err.number <> 0 then
b_error = true
error_list.add "db_update_add_note" & err.Number ,"The database update
failed: " & err.Description
else
end if
on error goto 0
end sub
I know I'm missing something here...
Kev
Message posted via http://www.droptable.com
|||There are a number of problems with your SQL script. @.ptrval needs to be a
binary(16) rather than CURSOR. Also, you have not specified a column value
in the WRITETEXT statement.
If your ntext data are reasonably sized, you might consider using a regular
UPDATE statement instead like the example below. In any case, use command
parameters to prevent SQL injection.
myCommand.CommandText = "UPDATE pmProjects SET ProjectDescription = ? WHERE
ProjectId = ?"
Set projectDescriptionParameter = myCommand.CreateParameter( _
"@.ProjectDescription", _
adLongVarWChar, _
adParamInput, _
Len(ProjectDescription))
myCommand.Parameters.Append projectDescriptionParameter
projectDescriptionParameter.Value = ProjectDescription
Set projectIdParameter = myCommand.CreateParameter( _
"@.ProjectIdParameter", _
adInteger, _
adParamInput)
myCommand.Parameters.Append projectIdParameter
projectIdParameter.Value = ProjectId
myCommand.Execute
Hope this helps.
Dan Guzman
SQL Server MVP
"Kevin Dearinger via droptable.com" <forum@.droptable.com> wrote in message
news:cbbeac9230f74b41929c9ef12396c26a@.droptable.co m...
> Sorry... This is the code I attempted to use:
> sub db_update_add_note
> sql = "DECLARE @.ptrval CURSOR " & _
> "SELECT @.ptrval = TEXTPTR(ProjectDesc) " & _
> "FROM pmProjects " & _
> "WHERE ProjectId = " & ProjectId & " " & _
> "WRITETEXT newnote @.ptrval"
> response.write sql
> on error resume next
> cn.execute(sql)
> if err.number <> 0 then
> b_error = true
> error_list.add "db_update_add_note" & err.Number ,"The database update
> failed: " & err.Description
> else
> end if
> on error goto 0
> end sub
> I know I'm missing something here...
> Kev
> --
> Message posted via http://www.droptable.com