update record in sql server db using asp

I need to be able to update a record about 200 characters with a click
of a button.
The user will enter the new text in a textarea called NewsText and then
click on submit button. Right now when clicking on the submit button
the exsisting data gets overwritten with a null or blank. Here is my
code.

<%
Dim oConn
Dim oRS
Set oConn = Server.CreateObject("ADODB.Connection")
Set oRS = Server.CreateObject("ADODB.Recordset")
oConn.Open = "Driver={SQL
Server};Server=W2S01;Database=soccerforum;Uid=dave;Pwd=****; "
%>

name="form2" method="post" action="submit.asp">

<%

strSQL = "SELECT * FROM newstble;"
oRS.Open strSQL, oConn,1,1
'If Not oRS.EOF Then
'oRS.MoveFirst
'strSQL = "UPDATE newstble SET news = " & Request.Form("NewsText")
oConn.Execute strSQL

' end if

' oRS.Close
'oRS = Nothing
'oConn.Close
' oConn = Nothing
%>

What am I doing wrong?

Re: update record in sql server db using asp

I forgot to leave out the comments ('). In my program they are
uncommented

Re: update record in sql server db using asp

besides the code being commented out... meaning it wont run (hopefully you
know that)

you need single quotes around the value in the SQL statement because its a
text value

like so
strSQL = "UPDATE newstble SET news = '" & Request.Form("NewsText") & "'"

even so... you have no (where) information so how is the database suppost to
know what row to enter the data into ????

example "where ID = 1"

or anything so it knows what to do



"novice_techy" wrote in message
news:1143068357.942724.321760@i40g2000cwc.googlegroups.com.. .
>I need to be able to update a record about 200 characters with a click
> of a button.
> The user will enter the new text in a textarea called NewsText and then
> click on submit button. Right now when clicking on the submit button
> the exsisting data gets overwritten with a null or blank. Here is my
> code.
>
> <%
> Dim oConn
> Dim oRS
> Set oConn = Server.CreateObject("ADODB.Connection")
> Set oRS = Server.CreateObject("ADODB.Recordset")
> oConn.Open = "Driver={SQL
> Server};Server=W2S01;Database=soccerforum;Uid=dave;Pwd=****; "
> %>
>
> > name="form2" method="post" action="submit.asp">
>
> <%
>
> strSQL = "SELECT * FROM newstble;"
> oRS.Open strSQL, oConn,1,1
> 'If Not oRS.EOF Then
> 'oRS.MoveFirst
> 'strSQL = "UPDATE newstble SET news = " & Request.Form("NewsText")
> oConn.Execute strSQL
>
> ' end if
>
> ' oRS.Close
> 'oRS = Nothing
> 'oConn.Close
> ' oConn = Nothing
> %>
>
> What am I doing wrong?
>

Re: update record in sql server db using asp

"Kyle Peterson" wrote in message
news:%23ZA5NKkTGHA.792@TK2MSFTNGP10.phx.gbl...
> besides the code being commented out... meaning it wont run (hopefully you
> know that)
>
> you need single quotes around the value in the SQL statement because its a
> text value
>
> like so
> strSQL = "UPDATE newstble SET news = '" & Request.Form("NewsText") & "'"
>
> even so... you have no (where) information so how is the database suppost
to
> know what row to enter the data into ????
>
> example "where ID = 1"
>
> or anything so it knows what to do
>
>

It knows exactly what to do. Set the news field of ALL records in the
newstble to the NewsText form value.

Unless the user entered this text:-

'; DELETE newstble; --

In which case all news fields will be set to an empty string, promptly
followed by all the records being deleted entirely. Ouch

Using:-

Replace(Request.Form("NewsText"), "'", "''")

will help (replacing each apostrophe with two apostrophes. However I prefer
to use a command object.

Anthony.

Re: update record in sql server db using asp

I cannot seem to get the replace function to work This is what I am
putting in.
88 strSQL = "UPDATE newstble SET news ='" &
Replace(Request.Form("NewsText"), "''", "''")
89 oConn.Execute(strSQL)

I have Replace(Request.Form("NewsText"), quote apost apost quote, quote
apost apost quote)

Error Type:
Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
[Microsoft][ODBC SQL Server Driver][SQL Server]Unclosed quotation mark
before the character string ''.
/submit.asp, line 89

Re: update record in sql server db using asp

"novice_techy" wrote in message
news:1143068357.942724.321760@i40g2000cwc.googlegroups.com.. .
>I need to be able to update a record about 200 characters with a click
> of a button.
> The user will enter the new text in a textarea called NewsText and then
> click on submit button. Right now when clicking on the submit button
> the exsisting data gets overwritten with a null or blank. Here is my
> code.
>
> <%
> Dim oConn
> Dim oRS
> Set oConn = Server.CreateObject("ADODB.Connection")
> Set oRS = Server.CreateObject("ADODB.Recordset")
> oConn.Open = "Driver={SQL
> Server};Server=W2S01;Database=soccerforum;Uid=dave;Pwd=****; "
> %>
>
> > name="form2" method="post" action="submit.asp">
>
> <%
>
> strSQL = "SELECT * FROM newstble;"
> oRS.Open strSQL, oConn,1,1
> 'If Not oRS.EOF Then
> 'oRS.MoveFirst
> 'strSQL = "UPDATE newstble SET news = " & Request.Form("NewsText")
> oConn.Execute strSQL
>
> ' end if
>
> ' oRS.Close
> 'oRS = Nothing
> 'oConn.Close
> ' oConn = Nothing
> %>
>
> What am I doing wrong?

1. Where is the closing tag for your form?
2. Are you sure Request.Form("NewsText") is being posted?
3. What is the purpose of the recordset object? (Appears to be mostly
unused.)
4. The [news] column in every row in your table will be updated -- unless
there are no rows, in which case the update statement would be a no-op, if
it was executed.
5. Position of your recordset has absolutely nothing to do with what will be
affected by the update statement (beyond your conditional logic that
determines whether or not the update statement will run.)
6. String value needs single-quotes/danger of SQL injection (as has been
pointed out already.)
7. Overall seems to be a very confused code fragment; your exact intent is
non-obvious. If the SQL syntax was fixed, this would be the pseudo-code:

Select all columns in all rows into a server-side keyset r/o recordset
If one or more rows exist
Move the record position to the first row (which is where it will
already be when the recordset is opened)
Execute dynamic SQL to update one column in all rows to the same
value
otherwise
Do nothing

The same thing could be done without the recordset at all, just by executing
the update statement.

-Mark

Re: update record in sql server db using asp

novice_techy wrote:
> I cannot seem to get the replace function to work This is what I am
> putting in.
> 88 strSQL = "UPDATE newstble SET news ='" &
> Replace(Request.Form("NewsText"), "''", "''")
> 89 oConn.Execute(strSQL)
>
> I have Replace(Request.Form("NewsText"), quote apost apost quote, quote
> apost apost quote)
>
> Error Type:
> Microsoft OLE DB Provider for ODBC Drivers (0x80040E14)
> [Microsoft][ODBC SQL Server Driver][SQL Server]Unclosed quotation mark
> before the character string ''.
> /submit.asp, line 89

If you reply using google groups, click the Show Options link next to
the posters name. then the Reply link that gets revealed there. The
other (pointless IMO) Reply link at the bottom of the post clears out
the original text of the post you are replying to. Then people who are
using newsreaders that don't thread messages won't have a clue what you
are talking about.

If you think about the logic of your replace function, you will see
that it is working pefectly. Your function is looking to two
apostophes and then placing any instance with two apostophes. But
that's not what you want to do, is it? Your function should be looking
for instances of ONE apostrophe, and replacing that with two, so it
should read:

Replace(Request.Form("NewsText"),"'","''")

But, that is not the cause of your problem. The error arises becasue
you haven't delimited the text value in your SQL correctly. strSQL
should read like this:

strSQL = "UPDATE newstble SET news ='" & _
Replace(Request.Form("NewsText"),"'","''") & "'"

--
Mike Brind