Updating an SQL database

Cinnamon

06-27-2002, 09:48 AM

Ok guys... I've included all of the project code this time. The goal of this project is to:
1) enter a record number
2) display the record image
**** 1 & 2 work great
3) enter index (a sentence) information
****I can enter text and click submit... but the record is not getting updated
4) update the record with the new index information
****this should happen in the "Sub SaveNow"

I've included the suggestions from the "editing a record" post. I really appreciate the help and comments. I'm learning alot and it's great to have people out there that push me in the right direction.

At this point I'm not getting any error messages. However, the database is not updating either. Also, I have a "Response.Write(newIndex)" line and nothing appears when executed. Here is my code... try not to laugh too hard!

Cinnamon

<%@ Language=VBScript %>
<%
Option Explicit
Dim cnnDB, strQuery, newIndex
Dim cnnDatabase, rstDatabase, dtbDatabase, SearchThis, ImagePath

If Request.QueryString("bSave")="True" Then
SaveNow
else
Dim strSomePic
strSomePic = Preview
response.write(strSomepic)
End if
%>
<%
Sub SaveNow()
On Error Resume Next

newIndex = Request.QueryString("Cindex")
Response.Write(newIndex)

set cnnDB=Server.CreateObject("ADODB.Connection")
cnnDB.Open "Driver=SQL Server;server=DIGITAL;database=gen_cards;uid=cinnamon;pwd=sinnamon"
strQuery = "UPDATE Cards SET entry='" & newIndex & "' WHERE [unique]=" & SearchThis & ";"
cnnDB.Execute(strQuery)
end sub
%>

<%
Function Preview()

SearchThis = CLng(Request.QueryString("iRecordId"))
Response.Write(SearchThis)
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("SQLpath")
Response.Write rstDatabase.Fields("entry")
Preview = Imagepath

Else
Response.Write "Record not found"
End If
Else
Response.Write "Invalid input"
End If
End Function
%>
<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>

<img
src = ".<%= strSomePic %>"
width = "575"
height = "425"
/>
<br>
<form action="new_page_2.asp?bSave=True" method="post">
Enter index information:
<input type="text" name="Cindex"><br><br>
<input type="submit" value="Submit">
</form>
<p>

</body>
</html>

Aspen2K

06-27-2002, 10:38 AM

I'm actually having the same problem but I'm adding new record and not updating. Do you get an error if you comment out the "On Error Resume Next" statement in your SaveNow() sub?

Aspen2K

Robby

06-27-2002, 10:39 AM

I would remove the 'on error resume next' for now.

Is 'Response.Write(newIndex)' giving you any value?

Cinnamon

06-27-2002, 10:45 AM

With the "on error resume next " taken out I am getting an syntax error on line 25:
Incorrect syntax near ';'.

This line...

strQuery = "UPDATE Cards SET entry='" & newIndex & "' WHERE [unique]=" & SearchThis & ";"

The newIndex is not display anything.

Thinker

06-27-2002, 08:11 PM

I think it has been suggested to you in a number of your other
threads to leave out that extra ; at the end of your SQLs. It
might not fix this problem, but it is always unnecessary so it can't
hurt to try.

Cinnamon

06-28-2002, 07:31 AM

You're correct that it has been suggested several times. However, every time that I remove it then I get even more syntax errors. For example, the line works fine like this

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

But as soon as I change it to this it doesn't work

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

Right now I'm getting a syntax error on the ";" in the following line and I don't understand why. When I take out the ";" then I get a syntax error on the "=".

strQuery = "UPDATE Cards SET entry='" & newIndex & "' WHERE [unique]=" & SearchThis & ";"

I'm quite frustrated with it!

Thinker

06-28-2002, 09:06 AM

Ok, does SearchThis actually contain any value? I see where it is
assigned a value in Preview() but once the form has been
submitted back with bsave=true, I don't see how that value gets
set before calling SaveNow. Wouldn't you have to persist that
value between pages somehow? Like including it in a hidden
form element, or in some Session variable?

Cinnamon

06-28-2002, 09:46 AM

I see what you mean. In VB I could set a variable so that I could access it's value/text throughout my project. Is there anyway to do that in ASP? I'd like to be able to use the "SearchThis" and "newIndex" values/text on various pages?

Thinker

06-28-2002, 10:00 AM

The internet is a stateless connection. I *think* you can store
values between pages in a session variable, but the normal way
it is done is to either use a cookie, or include the value in a hidden
element in the form.