Error in updating memo field

S N · S N

I have an access table with fields including a Memo field. When I try to
update that table using ASP page (using OLEDB for ODBC driver) the record
after the update command is updated alright. But the memo field in the
recordset after updation does not contain the value from the record just
updated. It contains value of memo field of some other record.
The values of the fields other than the memo field in the recordset are
displayed alright.
This error goes away when I use Jet4.0 OLEDB driver.

Please explain if this is the correct solution to the problem. I dont think
OLEDB from ODBC is so error prone. Please tell if I am also committing some
other error.

Aaron Bertrand [MVP] · Aaron Bertrand [MVP]

> This error goes away when I use Jet4.0 OLEDB driver.

Patient: "It hurts when I do this."

Doctor: "So, don't do that."

Since you didn't bother to include any of the code that causes this problem,
I'll have to suggest simply using the OLEDB driver. ODBC is deprecated and
should be avoided in any case.

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]

Bob Barrows [MVP] · Bob Barrows [MVP]

S N wrote:

> I have an access table with fields including a Memo field. When I try
> to update that table using ASP page (using OLEDB for ODBC driver) the
> record after the update command is updated alright. But the memo
> field in the recordset after updation does not contain the value from
> the record just updated. It contains value of memo field of some
> other record.
> The values of the fields other than the memo field in the recordset
> are displayed alright.
> This error goes away when I use Jet4.0 OLEDB driver.
>
> Please explain if this is the correct solution to the problem. I dont
> think OLEDB from ODBC is so error prone. Please tell if I am also
> committing some other error.

Are you using a SQL UPDATE statement (recommended)? or a recordset (ughh!)

Give us some details: table structure, primary key

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Aaron Bertrand [MVP] · Aaron Bertrand [MVP]

> Give us some details: table structure, primary key

, CODE!

S N · S N

As desired I am including the whole code for my ASP file. Please help.

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE>~~~ WELCOME TO IRSME Website ~~~</TITLE>
<META content="text/html; charset=iso-8859-1" http-equiv=Content-Type>

<SCRIPT language=JavaScript>

</SCRIPT>

<STYLE type=text/css>BODY {
COLOR: #000000; textalign: justify; scrollbar-arrow-color: #cccc00;
scrollbar-base-color: #ffffff; scrollbar-dark-shadow-color: #ffffff;
scrollbar-face-color: #000000; scrollbar-highlight-color: #000000;
scrollbar-track-color: #000000
}
A:active {
TEXT-DECORATION: none
}
..link {
TEXT-DECORATION: none
}
A:hover {
TEXT-DECORATION: none
}
A:link {
TEXT-DECORATION: none
}
A:visited {
TEXT-DECORATION: none
}
</STYLE>

</HEAD>
<BODY aLink=#FF0000 bgColor=#FFFFFF leftMargin=0 link=#003399
onload=""
text=#000000 topMargin=0 vLink=#666666 marginheight="0" marginwidth="0">
<div align="left">
<SCRIPT language=JavaScript>

</SCRIPT>
<font face="Verdana, Arial, Helvetica, sans-serif"><b><font face="Verdana,
Arial, Helvetica, sans-serif" size="2"><%
name=""
pass=""
name = session("user")
pass = session("pass")

if ((name="")or(pass="")) or Session("floggedin")<>True or
Session("loggedin")<>True then
response.write("<h2><left>NOT AUTHORIZED!!!")
response.write("<h3>You need to login first. <br><br><br><a
href=login.asp>Go to Admin Login</a>")
%> <%
else
%> <font face="Verdana, Arial, Helvetica, sans-serif"><b><font
face="Verdana, Arial, Helvetica, sans-serif" size="2"></font></b></font>
<%
Dim adoCon
Dim rsNews
Dim strSQL
'Dbase="fpdb/admin.mdb"
Set adoCon = Server.CreateObject("ADODB.Connection")
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)};uid=;pwd=; DBQ=" &
Server.MapPath(DBase)

Set rsnews = Server.CreateObject("ADODB.Recordset")

'Time Zone Please Change to correct for your servers time.

TimeDiff = (DateAdd("h","+0", Now))
TimeSplit = Split(TimeDiff, " ")
'response.Write timesplit(0) & " " & len(timesplit(0))
'response.Write timesplit(1) & " " & len(timesplit(1))
'response.Write(TimeSplit(2))
strSQL = "SELECT postings.order_no, postings.issue_date, postings.headline,
postings.comment, postings.file, postings.date, postings.time FROM
postings;"
rsNews.CursorType = 2
rsNews.LockType = 3
rsNews.Open strSQL, adoCon
rsNews.AddNew

rsNews.Fields("order_no") = request.form("order_no")
rsNews.Fields("issue_date") = request.form("issue_date")
rsNews.Fields("headline") = request.form("headline")
rsNews.Fields("comment") = request.form("comment")
rsNews.Fields("file") = request.form("file")
rsNews.Fields("date") = (TimeSplit(0))
rsNews.Fields("time") = (TimeSplit(1))
'for each i in request.Form
'response.write request.Form(i)
'response.write "<BR>"
'next
'response.write len(rsNews.Fields("comment")) &"<BR> before <BR>"
'response.Write request.form("comment") &"<BR>"
rsNews.Update
'response.write len(rsNews.Fields("comment")) & " <BR> after " & "<BR>"

%></font></b></font></div>
<TABLE border=0 cellPadding=0 cellSpacing=0 width="550" height="503">
<TBODY>
<TR align="left">
<TD height=537 vAlign=top width="500">
<div align="left">
<TABLE border=0 cellPadding=0 cellSpacing=0 width="550" align="left"
vspace="0" hspace="0">
<TBODY>
<TR bgColor=#ffffff>
<TD colSpan=2 height=0 vAlign=top>
<TABLE border=0 cellPadding=10 cellSpacing=0 width="100%">
<TBODY>
<TR valign="top">
<TD width="63%" align="left">
<table width="550" border="0" vspace="0" hspace="0"
cellpadding="10" cellspacing="0">
<tr>
<td valign="top" width="47%">
<p><font face="Verdana, Arial, Helvetica,
sans-serif"><b></b></font><font face="Verdana, Arial, Helvetica,
sans-serif"><b><font size="4" color="#0066CC">Order
has been succesfully published!</font><font
face="Verdana, Arial, Helvetica, sans-serif"><b></b></font></b></font></p>
</td>
</tr>
<tr>
<td valign="top" align="left">
<p><font face="Verdana, Arial, Helvetica,
sans-serif" size="2">You
have submitted the following on <%
response.write("<b>")
response.write(rsNews("date"))
response.write("</b>")
%> at <%
response.write("<b>")
response.write(rsNews("time"))
response.write("</b>")
%>:</font></p>
<table width="550" border="0" cellpadding="0"
hspace="0" vspace="0" name="fields">
<tr>
<td width="159" valign="top"><font
face="verdana, arial, helvetica"
size=2><strong>Order No.</strong></font></td>
<td width="385" valign="top"><font
face="verdana, arial, helvetica"
size=2> </font><font face="verdana, arial, helvetica"
size=2><b>:</b></font> <font face="Verdana, Arial, Helvetica,
sans-serif" size="2"><%

response.write(rsNews("order_no"))

%></font><font face="verdana, arial, helvetica"
size=2> </font></td>
</tr>
<tr>
<td width="159" valign="top"><font
face="verdana, arial, helvetica"
size=2><strong>Issue Date</strong></font></td>
<td width="385" valign="top"><font
face="verdana, arial, helvetica"
size=2><b>:</b></font> <font face="Verdana, Arial, Helvetica,
sans-serif" size="2"><%

response.write(rsNews("issue_date"))

%></font><font face="verdana, arial, helvetica"
size=2> </font></td>
</tr>
<tr>
<td width="159" valign="top"><font
face="verdana, arial, helvetica"
size=2><strong>Headline</strong></font></td>
<td width="385" valign="top"><font
face="verdana, arial, helvetica"
size=2><b>:</b></font> <font face="Verdana, Arial, Helvetica,
sans-serif" size="2"><%

response.write(rsNews("headline"))

%></font><font face="verdana, arial, helvetica"
size=2></font></td>
</tr>
<tr>
<td width="159" valign="top"><font
face="verdana, arial, helvetica"
size=2><strong>Order Text</strong></font></td>
<td width="385" valign="top"><font
face="verdana, arial, helvetica"
size=2> </font><font face="verdana, arial, helvetica"
size=2><b>:</b></font> <font face="Verdana, Arial, Helvetica,
sans-serif" size="2"><%

response.write(rsNews("comment"))

%></font><font face="verdana, arial, helvetica"
size=2> </font></td>
</tr>
<tr>
<td width="159" valign="top"><font
face="verdana, arial, helvetica"
size=2><strong>File link</strong></font></td>
<td width="385" valign="top"><font
face="verdana, arial, helvetica"
size=2> </font><font face="verdana, arial, helvetica"
size=2><b>:</b></font> <font face="Verdana, Arial, Helvetica,
sans-serif" size="2"><%

response.write("<a href=files/"&rsNews("file")&">")
response.write(rsNews("file"))
response.write("</a>")
rsnews.close
adoCon.close
%></font><font face="verdana, arial, helvetica"
size=2> </font></td>
</tr>
</table>
<p><a href="update_transfers.htm"><font
face="Verdana, Arial, Helvetica, sans-serif" size="2"><b>Click
here to publish another order</b></font></a></p>
</td>
</tr>
</table>
<p align="center"><a href="default_guest.asp"
target="_self"><font size="3" face="Verdana, Arial, Helvetica,
sans-serif"><b><font face="Arial, Helvetica, sans-serif">Click
here for Guest Book</font></b></font></a></p>
</TD>
</TR>
<TR valign="top">
<TD width="63%" align="left">
<div align="right"><font face="Verdana, Arial,
Helvetica, sans-serif" size="2"><b>Site

</b></font><b>
<font face="Verdana, Arial, Helvetica, sans-serif"
size="2">
Hosted By National Informatics Centre</font></b></div>
</TD>
</TR>
</TBODY>
</TABLE>
</TD>
</TR>
</TBODY>
</TABLE>
</div>
</TD>
</TR>
</TBODY>
</TABLE>
<%end if%>
<%

%>
</BODY></HTML>

"Aaron Bertrand [MVP]" <aaron@TRASHaspfaq.com> wrote in message
news:OWSat6HLEHA.3664@TK2MSFTNGP10.phx.gbl...

> > Give us some details: table structure, primary key

>
> , CODE!
>
>

S N · S N

As desired I am including the whole code for the ASP file
Please help. The problematic code is marked in red and in bold.

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD><TITLE>~~~ WELCOME TO IRSME Website ~~~</TITLE>
<META content="text/html; charset=iso-8859-1" http-equiv=Content-Type>

<SCRIPT language=JavaScript>

</SCRIPT>

<STYLE type=text/css>BODY {
COLOR: #000000; textalign: justify; scrollbar-arrow-color: #cccc00; scrollbar-base-color: #ffffff; scrollbar-dark-shadow-color: #ffffff; scrollbar-face-color: #000000; scrollbar-highlight-color: #000000; scrollbar-track-color: #000000
}
A:active {
TEXT-DECORATION: none
}
..link {
TEXT-DECORATION: none
}
A:hover {
TEXT-DECORATION: none
}
A:link {
TEXT-DECORATION: none
}
A:visited {
TEXT-DECORATION: none
}
</STYLE>

</HEAD>
<BODY aLink=#FF0000 bgColor=#FFFFFF leftMargin=0 link=#003399
onload=""
text=#000000 topMargin=0 vLink=#666666 marginheight="0" marginwidth="0">
<div align="left">
<SCRIPT language=JavaScript>

</SCRIPT>
<font face="Verdana, Arial, Helvetica, sans-serif"><b><font face="Verdana, Arial, Helvetica, sans-serif" size="2"><%
name=""
pass=""
name = session("user")
pass = session("pass")

if ((name="")or(pass="")) or Session("floggedin")<>True or Session("loggedin")<>True then
response.write("<h2><left>NOT AUTHORIZED!!!")
response.write("<h3>You need to login first. <br><br><br><a href=login.asp>Go to Admin Login</a>")
%> <%
else
%> <font face="Verdana, Arial, Helvetica, sans-serif"><b><font face="Verdana, Arial, Helvetica, sans-serif" size="2"></font></b></font>
<%
Dim adoCon
Dim rsNews
Dim strSQL
'Dbase="fpdb/admin.mdb"
Set adoCon = Server.CreateObject("ADODB.Connection")
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)};uid=;pwd=; DBQ=" & Server.MapPath(DBase)

Set rsnews = Server.CreateObject("ADODB.Recordset")

'Time Zone Please Change to correct for your servers time.

TimeDiff = (DateAdd("h","+0", Now))
TimeSplit = Split(TimeDiff, " ")

'response.Write(TimeSplit(2))
strSQL = "SELECT postings.order_no, postings.issue_date, postings.headline, postings.comment, postings.file, postings.date, postings.time FROM postings;"
rsNews.CursorType = 2
rsNews.LockType = 3
rsNews.Open strSQL, adoCon
rsNews.AddNew

rsNews.Fields("order_no") = request.form("order_no")
rsNews.Fields("issue_date") = request.form("issue_date")
rsNews.Fields("headline") = request.form("headline")
rsNews.Fields("comment") = request.form("comment")
rsNews.Fields("file") = request.form("file")
rsNews.Fields("date") = (TimeSplit(0))
rsNews.Fields("time") = (TimeSplit(1))

rsNews.Update
'response.write len(rsNews.Fields("comment")) & " <BR> after " & "<BR>"

%></font></b></font></div>
<TABLE border=0 cellPadding=0 cellSpacing=0 width="550" height="503">
<TBODY>
<TR align="left">
<TD height=537 vAlign=top width="500">
<div align="left">
<TABLE border=0 cellPadding=0 cellSpacing=0 width="550" align="left" vspace="0" hspace="0">
<TBODY>
<TR bgColor=#ffffff>
<TD colSpan=2 height=0 vAlign=top>
<TABLE border=0 cellPadding=10 cellSpacing=0 width="100%">
<TBODY>
<TR valign="top">
<TD width="63%" align="left">
<table width="550" border="0" vspace="0" hspace="0" cellpadding="10" cellspacing="0">
<tr>
<td valign="top" width="47%">
<p><font face="Verdana, Arial, Helvetica, sans-serif"><b></b></font><font face="Verdana, Arial, Helvetica, sans-serif"><b><font size="4" color="#0066CC">Order
has been succesfully published!</font><font face="Verdana, Arial, Helvetica, sans-serif"><b></b></font></b></font></p>
</td>
</tr>
<tr>
<td valign="top" align="left">
<p><font face="Verdana, Arial, Helvetica, sans-serif" size="2">You
have submitted the following on <%
response.write("<b>")
response.write(rsNews("date"))
response.write("</b>")
%> at <%
response.write("<b>")
response.write(rsNews("time"))
response.write("</b>")
%>:</font></p>
<table width="550" border="0" cellpadding="0" hspace="0" vspace="0" name="fields">
<tr>
<td width="159" valign="top"><font face="verdana, arial, helvetica"
size=2><strong>Order No.</strong></font></td>
<td width="385" valign="top"><font face="verdana, arial, helvetica"
size=2> </font><font face="verdana, arial, helvetica"
size=2><b>:</b></font> <font face="Verdana, Arial, Helvetica, sans-serif" size="2"><%

response.write(rsNews("order_no"))

%></font><font face="verdana, arial, helvetica"
size=2> </font></td>
</tr>
<tr>
<td width="159" valign="top"><font face="verdana, arial, helvetica"
size=2><strong>Issue Date</strong></font></td>
<td width="385" valign="top"><font face="verdana, arial, helvetica"
size=2><b>:</b></font> <font face="Verdana, Arial, Helvetica, sans-serif" size="2"><%

response.write(rsNews("issue_date"))

%></font><font face="verdana, arial, helvetica"
size=2> </font></td>
</tr>
<tr>
<td width="159" valign="top"><font face="verdana, arial, helvetica"
size=2><strong>Headline</strong></font></td>
<td width="385" valign="top"><font face="verdana, arial, helvetica"
size=2><b>:</b></font> <font face="Verdana, Arial, Helvetica, sans-serif" size="2"><%

response.write(rsNews("headline"))

%></font><font face="verdana, arial, helvetica"
size=2></font></td>
</tr>
<tr>
<td width="159" valign="top"><font face="verdana, arial, helvetica"
size=2><strong>Order Text</strong></font></td>
<td width="385" valign="top"><font face="verdana, arial, helvetica"
size=2> </font><font face="verdana, arial, helvetica"
size=2><b>:</b></font> <font face="Verdana, Arial, Helvetica, sans-serif" size="2"><%

response.write(rsNews("comment"))

%></font><font face="verdana, arial, helvetica"
size=2> </font></td>
</tr>
<tr>
<td width="159" valign="top"><font face="verdana, arial, helvetica"
size=2><strong>File link</strong></font></td>
<td width="385" valign="top"><font face="verdana, arial, helvetica"
size=2> </font><font face="verdana, arial, helvetica"
size=2><b>:</b></font> <font face="Verdana, Arial, Helvetica, sans-serif" size="2"><%

response.write("<a href=files/"&rsNews("file")&">")
response.write(rsNews("file"))
response.write("</a>")
rsnews.close
adoCon.close
%></font><font face="verdana, arial, helvetica"
size=2> </font></td>
</tr>
</table>
<p><a href="update_transfers.htm"><font face="Verdana, Arial, Helvetica, sans-serif" size="2"><b>Click
here to publish another order</b></font></a></p>
</td>
</tr>
</table>
<p align="center"><a href="default_guest.asp" target="_self"><font size="3" face="Verdana, Arial, Helvetica, sans-serif"><b><font face="Arial, Helvetica, sans-serif">Click
here for Guest Book</font></b></font></a></p>
</TD>
</TR>
<TR valign="top">
<TD width="63%" align="left">
<div align="right"><font face="Verdana, Arial, Helvetica, sans-serif" size="2"><b>Site

</b></font><b>
<font face="Verdana, Arial, Helvetica, sans-serif" size="2">
Hosted By National Informatics Centre</font></b></div>
</TD>
</TR>
</TBODY>
</TABLE>
</TD>
</TR>
</TBODY>
</TABLE>
</div>
</TD>
</TR>
</TBODY>
</TABLE>
<%end if%>
<%

%>
</BODY></HTML>
"Aaron Bertrand [MVP]" <aaron@TRASHaspfaq.com> wrote in message news:eNjFBbHLEHA.1156@TK2MSFTNGP09.phx.gbl...

> > This error goes away when I use Jet4.0 OLEDB driver.

>
> Patient: "It hurts when I do this."
>
> Doctor: "So, don't do that."
>
> Since you didn't bother to include any of the code that causes this problem,
> I'll have to suggest simply using the OLEDB driver. ODBC is deprecated and
> should be avoided in any case.
>
> --
> Aaron Bertrand
> SQL Server MVP
> [url]http://www.aspfaq.com/[/url]
>
>

S N · S N

The error as mentioned in my previous mail occurs after updating the field
named comment as mentioned in the code.
This field called comment is a Memo field in the Access database. I am
including the whole ASP file code.
Please help.

"Aaron Bertrand [MVP]" <aaron@TRASHaspfaq.com> wrote in message
news:OWSat6HLEHA.3664@TK2MSFTNGP10.phx.gbl...

> > Give us some details: table structure, primary key

>
> , CODE!
>
>

Aaron Bertrand [MVP] · Aaron Bertrand [MVP]

> adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)};uid=;pwd=; DBQ=" &

> Server.MapPath(DBase)

Stop doing this. Use an OLEDB connection.
[url]http://www.aspfaq.com/2126[/url]

> rsNews.AddNew

Stop doing this. Use an INSERT statement.
[url]http://www.aspfaq.com/2191[/url]

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]

Bob Barrows [MVP] · Bob Barrows [MVP]

S N wrote:

> As desired I am including the whole code for my ASP file. Please help.

Arggh! Overkill! :-)
Look, you are having a problem with vbscript code, not html. There is no
need to show us all this html. Nobody requested the "whole code" for your
asp file. :-)

Snip out the bit of code used to update the database and show that to us.
I'm not going to try and wade through this to find it. What we're trying to
find out is what method you are using to update the database, nothing else

And please provide the database details I asked for.:-)

<snip>

Bob Barrows
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

S N · S N

I wonder if ODBC connection is so error prone?

"Aaron Bertrand [MVP]" <aaron@TRASHaspfaq.com> wrote in message
news:e8ro2bILEHA.3428@TK2MSFTNGP09.phx.gbl...

> > adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)};uid=;pwd=; DBQ=" &
> > Server.MapPath(DBase)

>
> Stop doing this. Use an OLEDB connection.
> [url]http://www.aspfaq.com/2126[/url]
>

> > rsNews.AddNew

>
> Stop doing this. Use an INSERT statement.
> [url]http://www.aspfaq.com/2191[/url]
>
> --
> Aaron Bertrand
> SQL Server MVP
> [url]http://www.aspfaq.com/[/url]
>
>

Aaron Bertrand [MVP] · Aaron Bertrand [MVP]

ODBC has been deprecated for many reasons. Your method, however, is what is
error-prone. A recordset is for *RETRIEVING* data. Just because you *can*
hack it this way does not mean you should. USE AN INSERT STATEMENT.

--
Aaron Bertrand
SQL Server MVP
[url]http://www.aspfaq.com/[/url]

"S N" <uandme72@yahoo.com> wrote in message
news:u8JB0fILEHA.2704@TK2MSFTNGP10.phx.gbl...

>I wonder if ODBC connection is so error prone?

S N · S N

I am including only the ASP script portion that is creating problem.
Here rsNews.Fields("comment") is a memo field.

Set adoCon = Server.CreateObject("ADODB.Connection")
adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)};uid=;pwd=; DBQ=" &
Server.MapPath(DBase)
Set rsnews = Server.CreateObject("ADODB.Recordset")

'Time Zone Please Change to correct for your servers time.
TimeDiff = (DateAdd("h","+0", Now))
TimeSplit = Split(TimeDiff, " ")

strSQL = "SELECT postings.order_no, postings.issue_date,
postings.headline, postings.comment, postings.file, postings.date,
postings.time FROM postings;"
rsNews.CursorType = 2
rsNews.LockType = 3
rsNews.Open strSQL, adoCon
rsNews.AddNew

rsNews.Fields("order_no") = request.querystring("order_no")
rsNews.Fields("issue_date") = request.querystring("issue_date")
rsNews.Fields("headline") = request.querystring("headline")
rsNews.Fields("comment") = request.querystring("comment")
rsNews.Fields("file") = request.querystring("file")
rsNews.Fields("date") = (TimeSplit(0))
rsNews.Fields("time") = (TimeSplit(1))

rsNews.Update

*** Sent via Developersdex [url]http://www.developersdex.com[/url] ***
Don't just participate in USENET...get rewarded for it!

Bob Barrows [MVP] · Bob Barrows [MVP]

What is the primary key for the
S N wrote:

> I am including only the ASP script portion that is creating problem.
> Here rsNews.Fields("comment") is a memo field.
>
> Set adoCon = Server.CreateObject("ADODB.Connection")
> adoCon.Open "DRIVER={Microsoft Access Driver (*.mdb)};uid=;pwd=;
> DBQ=" & Server.MapPath(DBase)

Change this to:
adoCon.Open "Provider=Microsoft.Jet.OLEDB.4.0" & _
"data source=" & Server.MapPath(DBase)

> Set rsnews = Server.CreateObject("ADODB.Recordset")
>
> 'Time Zone Please Change to correct for your servers time.
> TimeDiff = (DateAdd("h","+0", Now))
> TimeSplit = Split(TimeDiff, " ")
>
>
> strSQL = "SELECT postings.order_no, postings.issue_date,
> postings.headline, postings.comment, postings.file, postings.date,
> postings.time FROM postings;"

You're pulling ALL the records from your database so you can add a
record???? Grossly inefficient.

At the very least, if you insist on the inefficient and resource-wasting
recordset, you should tell it not to return any records: Plus, you've made
the mistake of using reserved keywords, "date" and "time",
([url]http://www.aspfaq.com/show.asp?id=2080[/url]) for your field names. If you cannot
change those field names for some reason

strSQL = "SELECT postings.order_no, postings.issue_date,
postings.headline, postings.comment, postings.file, postings.date,
postings.time FROM postings WHERE 1=2;"

> rsNews.CursorType = 2

Why do you need a dynamic cursor!? Use a forwardonly cursor:

rsNews.CursorType = 0

> rsNews.LockType = 3
> rsNews.Open strSQL, adoCon
> rsNews.AddNew
>
> rsNews.Fields("order_no") = request.querystring("order_no")
> rsNews.Fields("issue_date") = request.querystring("issue_date")
> rsNews.Fields("headline") = request.querystring("headline")
> rsNews.Fields("comment") = request.querystring("comment")
> rsNews.Fields("file") = request.querystring("file")
> rsNews.Fields("date") = (TimeSplit(0))
> rsNews.Fields("time") = (TimeSplit(1))
>
> rsNews.Update

Instead of the recordset, you should use an INSERT statement. Without
knowing the details of your table structure (datatypes) I can't guarantee
that this will work, but i will make some guesses and give it a try. Here
are my assumptions:

- order_no is a Number column so I will not use a delimiter around the value
- issue_date, date and time are Date/Time fields so I will delimit the
values with hash marks (#)
- comment is the Memo field and headline and file are Text so I will delimt
those values with ', and I will use Replace to double up any apostrophes
that are in the data so they will be treated as literal apostrophes instead
of delimiters (this does NOT cause double apostrophes to be inserted into
your table)

strSQL="INSERT INTO postings (order_no, issue_date, " & _
"headline, comment, file, [date],[time]) " &
"VALUES (" & request.querystring("order_no") & "," & _
"#" & request.querystring("issue_date") & "#," & _
"'" & Replace(request.querystring("headline"), "'", "''") & "'," & _
"'" & Replace(request.querystring("comment"), "'", "''") & "'," & _
"'" & Replace(request.querystring("file"), "'", "''") & "'," & _
"#" & request.querystring("date") & "#," & _
"#" & request.querystring("time") & "#)

'the following line is for debugging. Comment it out when you have
'it working
Response.Write strSQL & "<BR>"

adoCon.Execute strSQL,,129

The 129 tells ADO that you are executing a query passed as a string (1 =
adCmdText) and that you do not expect to get any records back from the query
(128=adExecuteNoRecords). 1+128=129

The reason for the response.write is to allow you to make sure the statement
you've constructed is correct. If it is correct, you should be able to copy
the statement from the browser window, open your database in Access, create
a new query in Design View without selecting any tables, switch to SQL View,
paste the statement in and run it without modification.

If any of my assumptions about your datatypes is incorrect, you should
adjust this example accordingly.

HTH,
Bob BArrows

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.

Error in updating memo field

Thread Tools

Display

Error in updating memo field

Similar Questions and Discussions

updating access memo fields

Query error saving to Memo field ...

Exception error querying Access database from ASP page... Memo field type?

Memo Field vs. OLE Obj

Working with a Memo Field

Re: Error in updating memo field

Re: Error in updating memo field

Re: Error in updating memo field

Re: Error in updating memo field

Re: Error in updating memo field

Re: Error in updating memo field

Re: Error in updating memo field

Re: Error in updating memo field

Re: Error in updating memo field

Re: Error in updating memo field

Re: Error in updating memo field

Re: Error in updating memo field

Posting Permissions