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

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.comSorry... 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@.dr
optable.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

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.sqlmonster.comSorry... 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|||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 SQLMonster.com" <forum@.SQLMonster.com> wrote in message
news:cbbeac9230f74b41929c9ef12396c26a@.SQLMonster.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

CONVERT Error Message

Greetings,
I have a "newbie" question in relation to dates. I am trying to append the
year, month, and day to one another in order to create a new date. The SQL
I
am using is below. The error message I am receiving is "The conversion of a
char data type to a datetime data type resulted in an out-of-range datetime
value." I have also tried using "CAST", but I receive a similar error
message on that as well. Any idea as to what I am doing wrong?
Convert(varchar(12),(month(ebm.Hire_Date + 30) + 1)) + '/' +
Convert(varchar(12),day(ebm.Hire_Date + 30)) + '/' +
Convert(varchar(12),year(ebm.Hire_Date + 30))
Thanks in advance!
--
SherwoodSherwood (Sherwood@.discussions.microsoft.com) writes:
> I have a "newbie" question in relation to dates. I am trying to append
> the year, month, and day to one another in order to create a new date.
> The SQL I am using is below. The error message I am receiving is "The
> conversion of a char data type to a datetime data type resulted in an
> out-of-range datetime value." I have also tried using "CAST", but I
> receive a similar error message on that as well. Any idea as to what I
> am doing wrong?
> Convert(varchar(12),(month(ebm.Hire_Date + 30) + 1)) + '/' +
> Convert(varchar(12),day(ebm.Hire_Date + 30)) + '/' +
> Convert(varchar(12),year(ebm.Hire_Date + 30))
There are two safe date formats in SQL Server (three in SQL 2005). The
most commonly used is YYYYMMDD. When you use delimited formats, it's up
to the settings how the date will be interpreted.
Anyway, I am not really sure what you want to do, but you should have a
look at the dateadd() function, that may simplify your problem.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||I was able to resolve it by using the code below. The reason it initially
failed was due to the fact that I was using it in one of the conditions of a
CASE statement. The other condition had a different format and apparently
that executed first. At any rate, the code below seems to be working.
Convert(varchar(15),year(ebm.Hire_Date)) + '/' +
Convert(varchar(15),(month(ebm.Hire_date) + 1)) + '/' +
Convert(varchar(15),day(ebm.Hire_Date))
Thanks.
--
Sherwood
"Erland Sommarskog" wrote:

> Sherwood (Sherwood@.discussions.microsoft.com) writes:
> There are two safe date formats in SQL Server (three in SQL 2005). The
> most commonly used is YYYYMMDD. When you use delimited formats, it's up
> to the settings how the date will be interpreted.
> Anyway, I am not really sure what you want to do, but you should have a
> look at the dateadd() function, that may simplify your problem.
> --
> Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
> Books Online for SQL Server 2005 at
> http://www.microsoft.com/technet/pr...oads/books.mspx
> Books Online for SQL Server 2000 at
> http://www.microsoft.com/sql/prodin...ions/books.mspx
>|||Sherwood (Sherwood@.discussions.microsoft.com) writes:
> I was able to resolve it by using the code below. The reason it
> initially failed was due to the fact that I was using it in one of the
> conditions of a CASE statement. The other condition had a different
> format and apparently that executed first. At any rate, the code below
> seems to be working.
> Convert(varchar(15),year(ebm.Hire_Date)) + '/' +
> Convert(varchar(15),(month(ebm.Hire_date) + 1)) + '/' +
> Convert(varchar(15),day(ebm.Hire_Date))
Hire someone in December, and you will get a nasty surprise.
This does work:
dateadd(MONTH, 1, ebm.Hire_date)
And this is what you should use.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx