MS SQL Server - Convert SQL in ASP 3.0 site to UPDATE ntext

Author

Convert SQL in ASP 3.0 site to UPDATE ntext

Kevin Dearinger via SQLMonster.com

2005-04-24, 9:23 am

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.sqlmonster.com

Kevin Dearinger via SQLMonster.com

2005-04-24, 11:23 am

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.sqlmonster.com

Dan Guzman

2005-04-24, 11:23 am

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 projectDescriptionPa
rameter = myCommand.CreateParameter( _
"@ProjectDescription", _
adLongVarWChar, _
adParamInput, _
Len(ProjectDescripti
on))
myCommand.Parameters.Append projectDescriptionPa
rameter
projectDescriptionPa
rameter.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 SQLMonster.com" <forum@SQLMonster.com> wrote in message
news:cbbeac9230f74b4
1929c9ef12396c26a@SQ
LMonster.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.sqlmonster.com

Sponsored Links