editing and updating a DB field via the web

Cinnamon

06-19-2002, 08:49 AM

I finally made it through the "displaying images" problem and it works great.

My pages now do the following things:

1) Prompts the user for a record number
2) Displays the record and image
3) Prompts the user to "Enter Index Information"
The user is supposed to be able to enter some text and click submit to update the "entry" field.

This is when the problem occurs. I am getting the error message:

ADODB.Recordset error '800a0cb3'

Current Recordset does not support updating. This may be a limitation of the provider, or of the selected locktype.

/myweb/new_page_2.asp, line 50

I have administrator user permissions on SQL and the database. When I add "adLockOptimistic" (or any other locktype for that matter) to the open line I get another error message:

ADODB.Recordset error '800a0bb9'

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

/myweb/new_page_2.asp, line 26

Does anyone have any ideas? Could it be the way that my pages are accessed? Thanks for your help.

Cinnamon

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<meta name="GENERATOR" content="Microsoft FrontPage 4.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<title>New Page 2</title>
</head>

<body>

<form action="new_page_3.asp" method="get">
Enter index information:
<input type="text" name="Cindex"><br><br>
<input type="submit" value="Submit">
</form>
<p>
<%
Dim cnnDatabase, rstDatabase, dtbDatabase, SearchThis, ImagePath, CardIndex

SearchThis = Request.QueryString("iRecordId")
If IsNumeric(SearchThis) Then

cnnDatabase = "Driver=SQL Server;server=DIGITAL;database=gen_cards;uid=cinnamon;pwd=sinnamon"
Set rstDatabase=Server.CreateObject("ADODB.Recordset")
dtbDatabase = "SELECT * FROM Cards WHERE [unique]=" & SearchThis & ";"
rstDatabase.Open dtbDatabase, cnnDatabase, adOpenStatic

If NOT rstDatabase.EOF Then
ImagePath = rstDatabase.Fields("SQLpath")
%>
<img
src = ".<%= ImagePath %>"
width = "575"
height = "425"
/>
<br>
<%

Response.Write rstDatabase.Fields("entry")
Else
Response.Write "Record not found"
End If

CardIndex = Request.QueryString("Cindex")
rstDatabase.Fields("entry")=CardIndex

rstDatabase.Close
Set rstDatabase = Nothing
Else
Response.Write "Invalid input"
End If
%>
</body>
</html>

Road Runner

06-19-2002, 08:54 AM

I reaised it wont work

Cinnamon

06-19-2002, 09:19 AM

Well, I tried adUseClient and got the follwoing error message:

ADODB.Recordset error '800a0bb9'

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

/myweb/new_page_2.asp, line 26

But thanks for the suggestion anyway. I'll try anything to get this working.

Cinnamon

Road Runner

06-19-2002, 10:00 AM

I think you need to put a rs.edit then rs.update with adOpenDynamic, adLockPessimistic.

I have done this before but unfortunately its been a while
It goes something like this

rs.Edit
rs!ColumnName = variable
rs.Update

Look up google for more information

Or alternatively

you could go something like

coon.Exeute "UPDATE Cards Set entry = " & CarIndex & "WHERE [unique]=" & SearchThis & ";""

Cinnamon

06-19-2002, 12:00 PM

Thanks for the information. I will keep working on it.

Cinnamon

Thinker

06-19-2002, 12:49 PM

I was under the impression that enumerated constants like
adOpenStatic or adLockOptimistic weren't available in VBScript
(including ASP). I would try using the actual numbers these
enums represent instead. For instance,

adOpenForwardOnly = 0
adOpenKeyset = 1
adOpenDynamic = 2
adOpenStatic = 3

adLockReadOnly = 1
adLockPessimistic = 2
adLockOptimistic = 3
adLockBatchOptimistic = 4

Cinnamon

06-19-2002, 03:09 PM

Thankyou, Thinker, for that last tip. It helped me get past some error messages. Now I have some new ones to deal with.

This is what happens when I open the web page:

1) Displays an "Enter Record Number" input box.
2) I enter "1" and click submit
3) new_page_2.asp opens with the input box "Enter index information"...
underneath this is the image that corresponds to record number 1.
(In other words everything is working up to this point.)

However, at the bottom of the screen there is an error message:
ADODB.Recordset error '800a0c93'

Operation is not allowed in this context.

/myweb/new_page_2.asp, line 46

4)If I go ahead and enter information into the "Enter Index information" input box and click submit I get the following error. The only reason I mention this error now is that I have run across it numerous time throughout the day!

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near ';'.

/myweb/new_page_2.asp, line 26

Everytime I turn around I'm getting this error and can not figure out why.

All I want to do is be able to display a record by record number, edit, and then save the record. But I'm starting to go cross eyed. I've been working on this "editing a field" problem since yesterday. Thanks for your continued help.

Cinnamon

This is the first page (new_page_1.asp). It gets the second page (new_page_2.asp).

<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<meta name="GENERATOR" content="Microsoft FrontPage 4.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<title>New Page 1</title>
</head>

<body>

<form action="new_page_2.asp" method="get">
Enter record number:
<input type="text" name="iRecordId"><br><br>
<input type="submit" value="Submit">
</form>

</body>

</html>

This is the second page (new_page_2.asp).

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<meta name="GENERATOR" content="Microsoft FrontPage 4.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<title>New Page 2</title>
</head>

<body>

<form action="new_page_2.asp" method="get">
Enter index information:
<input type="text" name="Cindex"><br><br>
<input type="submit" value="Submit">
</form>
<p>
<%
Dim cnnDatabase, rstDatabase, dtbDatabase, SearchThis, ImagePath, CardIndex

SearchThis = Request.QueryString("iRecordId")
If IsNumeric(SearchThis) Then

cnnDatabase = "Driver=SQL Server;server=DIGITAL;database=gen_cards;uid=cinnamon;pwd=sinnamon"
Set rstDatabase=Server.CreateObject("ADODB.Recordset")
dtbDatabase = "SELECT * FROM Cards WHERE [unique]=" & SearchThis & ";"
rstDatabase.Open dtbDatabase, cnnDatabase, 3, 3

If NOT rstDatabase.EOF Then
ImagePath = rstDatabase.Fields("SQLpath")
%>
<img
src = ".<%= ImagePath %>"
width = "575"
height = "425"
/>
<br>
<%
CardIndex = Request.QueryString("Cindex")
rstDatabase.Fields("entry")=CardIndex

Response.Write rstDatabase.Fields("entry")
Else
Response.Write "Record not found"
End If

rstDatabase.Close
Set rstDatabase = Nothing
Else
Response.Write "Invalid input"
End If
%>
</body>
</html>

Thinker

06-19-2002, 03:37 PM

I would try taking this, & ";" off the end of your SQL. Only Access
with DAO seems to need it, and it might be causing the error.

Cinnamon

06-19-2002, 04:44 PM

Ok, slowly but surely we're getting there. My search and display page contains no errors. To accomplish this I had to delete the line that closed the database.

However, when I enter text into my "Enter index information" input box and click submit I get the following error:

Microsoft OLE DB Provider for ODBC Drivers error '80040e14'

[Microsoft][ODBC SQL Server Driver][SQL Server]Line 1: Incorrect syntax near '='.

/myweb/new_page_2.asp, line 26

The line is:

dtbDatabase = "SELECT * FROM Cards WHERE [unique]=" & SearchThis

So the quest for updating the field continues. Again, thanks for the help.

Cinnamon

Robby

06-19-2002, 04:54 PM

I was pretty sure that I pointed this out on your other thread... (convert to long)

SearchThis = cLng(Request.QueryString("iRecordId"))

Also, I know I mentioned the ; at the end of the SELECT. It's ok if you prefer to listen to Thinker and not me. (just kidding).

Just for the sake of a test.....

....comment out this line and everything past it,

rstDatabase.Open dtbDatabase, cnnDatabase, 3, 3

And place this on the page....

document.write(SearchThis)

What is the value of SearchThis?

Cinnamon

06-19-2002, 05:11 PM

I think I'm on to something. I think the problem stems from how I'm attempting to update the field. While playing with the code I made the following discovery.

I copied the line that displays the contents of the field "entry" so that it would appear above the image that gets loaded. When I open the page there is was! However when I attempt to edit the field is when I run into trouble. I'm using the following lines to attempt to update the field:

CardIndex = Request.QueryString("Cindex")
rstDatabase.Fields("entry")=CardIndex

Response.Write rstDatabase.Fields("entry")

The last line is too just show me the results of the update. Which displays nothing no matter what I do. I think the problem is with how I have the pages setup in conjunction with the input boxes and submit statements. I just don't know how to fix it.

I basically want to display the record (including) the image BEFORE editing the "entry" field. The "entry" field is to be a text description of the image. Therefore the image needs to display so that we can look at it to determine what the description should be.

I bet that made no sense! Feel free to ask questions. I reallly appreciate the help. Once again, here is the project code. I sure hope someone has and idea because I am lost.

Cinnamon

The first ASP page that "gets" the second one.

<html>

<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<meta name="GENERATOR" content="Microsoft FrontPage 4.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<title>New Page 1</title>
</head>

<body>

<form action="new_page_2.asp" method="get">
Enter record number:
<input type="text" name="iRecordId"><br><br>
<input type="submit" value="Submit">
</form>

</body>

</html>

The second ASP page.

<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=windows-1252">
<meta name="GENERATOR" content="Microsoft FrontPage 4.0">
<meta name="ProgId" content="FrontPage.Editor.Document">
<title>New Page 2</title>
</head>

<body>

<form action="new_page_2.asp" method="get">
Enter index information:
<input type="text" name="Cindex"><br><br>
<input type="submit" value="Submit">
</form>
<p>
<%
Dim cnnDatabase, rstDatabase, dtbDatabase, SearchThis, ImagePath, CardIndex

SearchThis = Request.QueryString("iRecordId")
If IsNumeric(SearchThis) Then

cnnDatabase = "Driver=SQL Server;server=DIGITAL;database=gen_cards;uid=cinnamon;pwd=sinnamon"
Set rstDatabase=Server.CreateObject("ADODB.Recordset")
dtbDatabase = "SELECT * FROM Cards WHERE [unique]=" & SearchThis
rstDatabase.Open dtbDatabase, cnnDatabase, 3, 3

If NOT rstDatabase.EOF Then
ImagePath = rstDatabase.Fields("SQLpath")
Response.Write rstDatabase.Fields("entry")
%>
<img
src = ".<%= ImagePath %>"
width = "575"
height = "425"
/>
<br>
<%
CardIndex = Request.QueryString("Cindex")
rstDatabase.Fields("entry")=CardIndex

Response.Write rstDatabase.Fields("entry")
Else
Response.Write "Record not found"
End If

Else
Response.Write "Invalid input"
End If
%>
</body>
</html>