edit-update record- help

TnTandyO · September 10th, 2003, 11:22 AM

Can some one pleaseeeeee help me look at this code and help me get it to update... (hope this enough info..)
ok here are the details:
page has a form that the text box values came database..

body tag

Dim Connection,connStatus,objRS,strSQL,intID, SCRIPT_NAME

SCRIPT_NAME = Request.ServerVariables("SCRIPT_NAME")

intID=Request.Form("ID")

SET Connection = Server.CreateObject("ADODB.Connection")
Connection.Open strStatus

  intID = Request.QueryString("ID")
  If Len(intID) > 0 Then

strSQL = "SELECT * FROM [tblIThelp] WHERE [ID] =" & intID

End If

Set objRS=Connection.Execute(strSQL)

Select Case LCase(Trim(Request.QueryString("action")))
Case "editsave"
iRecordId = Request.Form("id")
        iRecordId = Replace(iRecordId, "'", "''")

strSQL = "UPDATE tblIThelp SET " _
   & "Fname = '" & (Request.Form("Name")) & "', " _
   & "WHERE (id = " & iRecordId & ")"

<form name="form1" method="post" action=" yes its this bad ">

<input type="text" name="Fname" value=<%= objRS("Fname")%>>

<input type="submit" name="Submit" value="Submit">

</form>

End Select

close body tag.................
Tandy :(

TnTandyO · September 10th, 2003, 12:10 PM

ok guys I guess I had to much there..
now here's my error;
Command text was not set for the command object.
/asp/rod/Display2.asp, line 34

line 34
Set objRS=Connection.Execute(strSQL)

and I think this may help too:
strSQL = "SELECT * FROM [tblIThelp] WHERE [ID] =" & intID

Imar · September 10th, 2003, 12:15 PM

Hi,

Would this help?? I haven't tested it, just typed it in here, so it may not run right away, but I think you'll catch the general idea.

Cheers,

Imar

=====================================

<%
' It's good practice to put most of your
' Business Logic at the top of the page.
' Saves a lot of clutter in the page

Dim Connection
Dim connStatus
Dim objRS
Dim strSQL
Dim intID
Dim sFName

Set Connection = Server.CreateObject("ADODB.Connection")

Select Case LCase(Trim(Request.QueryString("action")))
Case "editsave"
    ' Updating, so take ID from Form
    intID = Request.Form("ID")
    intID = Replace(intID, "'", "''")
    If Len(intID) > 0 Then

     ' You shouldn't just use this check for creating
     ' the SQL statement, but for the entire update as well
     ' If intID is a zero length string, there is no
     ' point in executing the query
     ' Move all actions that consume time to before the .Open statement
     ' An open connection is a scarce resource, so use it as
     ' briefly as possible
     ' You can't use the comma in your SQL statement
     ' as you are updating just one field
     ' I changed it to space, so it doesn't conflict with the WHERE clause
     ' I also changed Request.Form("Name") to Request.Form("Fname")

     strSQL = "UPDATE tblIThelp SET " _
        & "Fname = '" & (Request.Form("Fname")) & "' " _
        & "WHERE (id = " & intID & ")"

     Connection.Open strStatus
     Connection.Execute(strSQL)
     Connection.Close()

     Set Connection = Nothing

     ' If the update is successful, redirect away. You could do something else here
     ' as well, like sing a song, go shopping, or show the changed stuff
     ' to the user again. ;)

    End If
Case "select"

    ' Selecting, so take ID from querystring

    intID = Request.QueryString("ID")
    If Len(intID) > 0 Then
     strSQL = "SELECT * FROM [tblIThelp] WHERE [ID] =" & intID
     Connection.Open strStatus
     Set objRS = Connection.Execute(strSQL)

     ' By using a variable, you don't run the risk of having
     ' an error in the form when objRS is EOF
     sFName = objRS("Fname")
     Connection.Close
     Set Connection = Nothing
    End If
Case else
    Response.Write("Sorry, but you are supposed to pass a valid querystring" & _
     " to this page.")
End Select

' Left Overs....
' No need for iRecordID, as you have that in intID
' iRecordId = Request.Form("id")
%>

<html>
<head>
</head>
<body>

<form name="form1" method="post" action="<%=Request.ServerVariables("SCRIPT_NAME")% >?action=editsave">
<input type="hidden" name="ID value="<%=intID%>">
<input type="text" name="Fname" value="<%=sFname%>">
<input type="submit" name="Submit" value="Submit">
</form>
</body>
</html>

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

TnTandyO · September 10th, 2003, 02:09 PM

Imar, what would I do with out you?
ok that got me a little farther (ok a lot farther), but
when the rec is sent over from the prev page it fills the text boxes with the current data,
<%= objRS("Fname")%>
then I'm changing it to
<%= objRS("sFname")%>

Microsoft VBScript runtime (0x800A000D)
Type mismatch
/asp/rod/Display2.asp, line 161

Imar · September 10th, 2003, 02:21 PM

Nonono, that was yesterday's mistake. You're supposed to make a new one today ;) See here for details.

Here's the naming convention I made based on your code

1. Your database has a field calld FName.
2. If you want a variable in a page to hold this name, you can call it sFName, to indicate it is a string that holds a first name. Note that the s is not a requirement. (In fact, it's getting a bit old fashioned these days but old habits die hard)

To retrieve the first name from the database, use this:

sFName = objRS("FName")

This retrieves the first name from the recordset and assigns it to the variable sFName. In your code, you're trying to retrieve sFName from the recordset which won't exist.

As I explained in the comments, it's easier to assign your stuff to variables at the top of your page, close all recordsets and connections and then use the variables in ASP like this:

<%=sFName%>

This will output the value of the variable sFName. If you do need the recordset value in your HTML, you could use this:

<%=objRS("FName")%>

but like I said, it's better not to do that.

If this doesn't help, I suggest you post the code for page 1 and 2 here, so I can look at it. Maybe, just like yesterday, you're making things more difficult than absolutely necessary.

Cheers,

Imar

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

TnTandyO · September 10th, 2003, 03:05 PM

I'm sorry!! I'm just not quite as good at this as I'd loke to be :(

Ok here is the code from both pages, I tried to weed all the tables and junk out to make it a little clearer but....
================================================== ===
page1
<%
Option Explicit
Dim strStatus
%>

<html>
<body>
<%
Dim strOutputString

Dim objRS, intNoOfRecords, intID, sImageName
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open "tblIThelp",strStatus,adOpenStatic,adLockReadOnly, adCmdTable
intNoOfRecords = objRS.RecordCount

intID = objRS("ID")
sImageName = objRS("solved")
Response.Write "<CENTER>"
strOutPutString = "<TABLE BORDER=1 cellpadding=0 cellspacing=0 Width=""100%"">" & _
    "<TR><TD colspan=7 background=images/BGtabPPL.gif><B><center>Help Desk Open</B></TD></TR>" & _
     "<TD><B> </B></TD>" & _
    "<TD><B>Priority</B></TD>" & _
    "<TD><B>Technician</B></TD>" & _
    "<TD><B>Notes</B></TD>" & _
    "<TD width="& "14%" & "><B>Date</B></TD>" & _
    "<TD width="& "14%" & "><B>Time</B></TD></TR>"
DO Until objRS.EOF

     strOutPutString = strOutPutString & "<TR>" & _
    "<TD valign=top><a href=""Display2.asp?ID=" & objRS("ID") & """><img src=images/" & objRS("solved") & ".gif></a>" & _
    "</TD><TD valign=top>" & objRS("priority") & "</TD>" & _
    "<TD valign=top>" & objRS("tech") & "</TD>" & _
    "<TD valign=top>" & objRS("Notes") & "</TD>" & _
    "<TD valign=top>" & objRS("date") & "</TD>" & _
    "<TD valign=top>" & objRS("time") & "</TD></TR>"

objRS.MoveNext
Loop

strOutPutString = strOutPutString & "</TABLE>"
objRS.Close
Set objRS = Nothing
Response.Write strOutPutString
%>
</body>
</html>
================================================== ===============
page 2 Display2.asp
<%
Option Explicit
Dim strStatus
%>

<%
  Dim Connection
  Dim connStatus
  Dim objRS
  Dim strSQL
  Dim intID
  Dim sFName, sLName, sEmail, sPhone, sRepeat, sPriority, sComment, sTech, sNotes, sDept, sDate, sTime, sSolved
  Dim SCRIPT_NAME
SCRIPT_NAME = Request.ServerVariables("SCRIPT_NAME")

  Set Connection = Server.CreateObject("ADODB.Connection")

  Select Case LCase(Trim(Request.QueryString("action")))
  Case "editsave"
    ' Updating, so take ID from Form
    intID = Request.Form("ID")
    intID = Replace(intID, "'", "''")
    If Len(intID) > 0 Then

      ' You shouldn't just use this check for creating
      ' the SQL statement, but for the entire update as well
      ' If intID is a zero length string, there is no
      ' point in executing the query
      ' Move all actions that consume time to before the .Open statement
      ' An open connection is a scarce resource, so use it as
      ' briefly as possible
      ' You can't use the comma in your SQL statement
      ' as you are updating just one field
      ' I changed it to space, so it doesn't conflict with the WHERE clause
      ' I also changed Request.Form("Name") to Request.Form("Fname")

      strSQL = "UPDATE tblIThelp SET " _
        & "Fname = '" & (Request.Form("Fname")) & "' " _
        & "Lname = '" & (Request.Form("Lname")) & "' " _
        & "Email = '" & (Request.Form("email")) & "' " _
        & "phone = '" & (Request.Form("phone")) & "' " _
        & "dept = '" & (Request.Form("dept")) & "' " _
        & "repeat = '" & (Request.Form("repeat")) & "' " _
        & "priority = '" & (Request.Form("priority")) & "' " _
        & "Comment = '" & (Request.Form("comment")) & "' " _
        & "notes = '" & (Request.Form("notes")) & "' " _
        & "tech = '" & (Request.Form("tech")) & "' " _
        & "date = '" & (Request.Form("date")) & "' " _
        & "time = '" & (Request.Form("time")) & "' " _
        & "solved = '" & (Request.Form("solved")) & "' " _
        & "WHERE (id = " & intID & ")"

      Connection.Open strStatus
      Connection.Execute(strSQL)
      Connection.Close()

      Set Connection = Nothing
      Response.Redirect("ViewIThelp.asp")

      ' If the update is successful, redirect away

    End If
  Case "select"

    ' Selecting, so take ID from querystring

    intID = Request.QueryString("ID")
    If Len(intID) > 0 Then
      strSQL = "SELECT * FROM [tblIThelp] WHERE [ID] =" & intID
      Connection.Open strStatus
      Set objRS = Connection.Execute(strSQL)

      ' By using a variable, you don't run the risk of having
      ' an error in the form when objRS is EOF
      sFName = objRS("Fname")
      sLName = objRS("Fname")
      sEmail = objRS("Fname")
      sPhone = objRS("Fname")
      sPriority = objRS("Fname")
      sRepeat = objRS("Fname")
      sComment = objRS("Fname")
      sTech = objRS("Fname")
      sNotes = objRS("Fname")
      sDept = objRS("Fname")
      sDate = objRS("Fname")
      sTime = objRS("Fname")
      sSolved = objRS("Fname")
      Connection.Close
      Set Connection = Nothing
    End If
  Case else
    Response.Write("Sorry, but you are supposed to pass a valid querystring" & _
      " to this page.")
End Select
%>
<html>
<body>
<%
        strSQL = "UPDATE tblIThelp SET " _
            & "Fname = '" & (Request.Form("Fname")) & "', " _
            & "Lname = '" & (Request.Form("Lname")) & "', " _
            & "email = '" & (Request.Form("email")) & "', " _
            & "phone = '" & (Request.Form("phone")) & "' " _
            & "repeat = '" & (Request.Form("repeat")) & "' " _
            & "priority = '" & (Request.Form("priority")) & "' " _
            & "comment = '" & (Request.Form("comment")) & "' " _
            & "tech = '" & (Request.Form("tech")) & "' " _
            & "date = '" & (Request.Form("date")) & "' " _
            & "time = '" & (Request.Form("time")) & "' " _
            & "notes = '" & (Request.Form("notes")) & "' " _
            & "dept = '" & (Request.Form("dept")) & "' " _
            & "solved = '" & (Request.Form("solved")) & "' " _
            & "WHERE (id = " & intID & ")"
%>
<form name="form1" method="post" action="<%=Request.ServerVariables("SCRIPT_NAME")% >?action=editsave">
<input type="text" name="Fname" value=<%= objRS("sFname")%>>
<input type="text" name="Lname" value=<%= objRS("sLname")%>>
<img src="images/<%= objRS("solved")%>.gif">
<input type="text" name="email"value=<%= objRS("sEmail")%>>
<input type="text" name="phone" value=<%= objRS("sPhone")%>>
<input type="text" name="Dept" value=<%= objRS("sDept")%>>
<select name="repeat">
    <option value="yes">Yes</option>
    <option value="no">No</option>
    <option selected><%= objRS("sRepeat")%></option>
</select>
<select name="priority">
    <option value="high">Yes</option>
    <option value="low">No</option>
    <option selected><%= objRS("sPriority")%></option>
</select>
<textarea name="comment" cols="54"><%= objRS("sComment")%></textarea>

<input type="text" name="tech" value=<%= objRS("sTech")%>>
<input type="text" name="date" value=<%= objRS("sDate")%>>
<input type="text" name="time" value=<%= objRS("sTime")%>>
<textarea name="notes" cols="54"><%= objRS("sNotes")%></textarea>
<input type="submit" name="Submit" value="Submit">
</form>
</body>
</html>

Imar · September 10th, 2003, 03:33 PM

Right. It seems to me page1 is OK, except for one thing. Page two will display the record when you pass select as the action, so you need to change the following in your Recordset loop:

Code:

strOutPutString = strOutPutString & "<TR>" & _
    "<TD valign=top><a href=""Display2.asp?ID=" & objRS("ID") & "
&action=select""><img src=images/" & objRS("solved") 
& ".gif></a>" & _

This way, you'll pass action=select to page2, where it can be used to retrieve the record.

Page 2 should look like below. Notice that I changed all the refs to objRs(FieldName) to StringFieldName. Since you stuffed the values from the recordset in the variable, and then closed the recordset, you shouldn't refer to the recordset anymore, but use the variable instead. I highlighted a few to show you what I mean.

<shameless plug>
You could consider getting a copy of Beginning Dreamweaver MX. Besides introducing Dreamweaver, we also discuss all these kind of issues. Retrieving, inserting, updating data from database, displaying them on ASP pages, protecting your web site using a login-mechanism etc.
</shameless plug>

Hope this helps. Step by step, remember.... ;)

Imar

[code]page 2 Display2.asp
<%
Option Explicit
Dim strStatus
%>

<%
Dim Connection
Dim connStatus
Dim objRS
Dim strSQL
Dim intID
Dim sFName, sLName, sEmail, sPhone, sRepeat, sPriority, sComment, sTech, sNotes, sDept, sDate, sTime, sSolved
Dim SCRIPT_NAME
SCRIPT_NAME = Request.ServerVariables("SCRIPT_NAME")

Set Connection = Server.CreateObject("ADODB.Connection")

Select Case LCase(Trim(Request.QueryString("action")))
Case "editsave"
    ' Updating, so take ID from Form
    intID = Request.Form("ID")
    intID = Replace(intID, "'", "''")
    If Len(intID) > 0 Then

     ' You shouldn't just use this check for creating
     ' the SQL statement, but for the entire update as well
     ' If intID is a zero length string, there is no
     ' point in executing the query
     ' Move all actions that consume time to before the .Open statement
     ' An open connection is a scarce resource, so use it as
     ' briefly as possible
     ' You can't use the comma in your SQL statement
     ' as you are updating just one field
     ' I changed it to space, so it doesn't conflict with the WHERE clause
     ' I also changed Request.Form("Name") to Request.Form("Fname")

     strSQL = "UPDATE tblIThelp SET " _
        & "Fname = '" & (Request.Form("Fname")) & "' " _
        & "Lname = '" & (Request.Form("Lname")) & "' " _
        & "Email = '" & (Request.Form("email")) & "' " _
        & "phone = '" & (Request.Form("phone")) & "' " _
        & "dept = '" & (Request.Form("dept")) & "' " _
        & "repeat = '" & (Request.Form("repeat")) & "' " _
        & "priority = '" & (Request.Form("priority")) & "' " _
        & "Comment = '" & (Request.Form("comment")) & "' " _
        & "notes = '" & (Request.Form("notes")) & "' " _
        & "tech = '" & (Request.Form("tech")) & "' " _
        & "date = '" & (Request.Form("date")) & "' " _
        & "time = '" & (Request.Form("time")) & "' " _
        & "solved = '" & (Request.Form("solved")) & "' " _
        & "WHERE (id = " & intID & ")"
     ' Use Response.Write(strStatus) to output the SQL to the
     ' browser for debugging purposes. Comment the next two lines
     ' again when the SQL looks OK.
     Response.Write(strStatus)
     Response.End()
     Connection.Open strStatus
     Connection.Execute(strSQL)
     Connection.Close()

     Set Connection = Nothing
     Response.Redirect("ViewIThelp.asp")

     ' If the update is successful, redirect away

    End If
Case "select"

    ' Selecting, so take ID from querystring

    intID = Request.QueryString("ID")
    If Len(intID) > 0 Then
     strSQL = "SELECT * FROM [tblIThelp] WHERE [ID] =" & intID
     Connection.Open strStatus
     Set objRS = Connection.Execute(strSQL)

     ' By using a variable, you don't run the risk of having
     ' an error in the form when objRS is EOF
     sFName = objRS("Fname")
     sLName = objRS("Fname")
     sEmail = objRS("Fname")
     sPhone = objRS("Fname")
     sPriority = objRS("Fname")
     sRepeat = objRS("Fname")
     sComment = objRS("Fname")
     sTech = objRS("Fname")
     sNotes = objRS("Fname")
     sDept = objRS("Fname")
     sDate = objRS("Fname")
     sTime = objRS("Fname")
     sSolved = objRS("Fname")
     Connection.Close
     Set Connection = Nothing
    End If
Case else
    Response.Write("Sorry, but you are supposed to pass a valid querystring" & _
     " to this page.")
End Select
%>
<html>
<body>
<form name="form1" method="post" action="<%=Request.ServerVariables("SCRIPT_NAME")% >?action=editsave">
<input type="text" name="Fname" value=<%=sFname%>>
<input type="text" name="Lname" value=<%=sLname%>>
<img src="images/<%=sSolved%>.gif">
<input type="text" name="email"value=<%=sEmail%>>
<input type="text" name="phone" value=<%=sPhone%>>
<input type="text" name="Dept" value=<%=sDept%>>
<select name="repeat">
    <option value="yes">Yes</option>
    <option value="no">No</option>
    <option selected><%=sRepeat%></option>
</select>
<select name="priority">
    <option value="high">Yes</option>
    <option value="low">No</option>
    <option selected><%=sPriority%></option>
</select>
<textarea name="comment" cols="54"><%=sComment%></textarea>

<input type="text" name="tech" value=<%=sTech%>>
<input type="text" name="date" value=<%=sDate%>>
<input type="text" name="time" value=<%=sTime%>>
<textarea name="notes" cols="54"><%=sNotes%></textarea>
<input type="submit" name="Submit" value="Submit">
</form>
</body>
</html>

Imar · September 10th, 2003, 03:39 PM

Right, one more thing: You are assinging the same column from the database to all your variables:

sFName = objRS("Fname")
sLName = objRS("Fname")

should be

sFName = objRS("Fname")
sLName = objRS("Lname")

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.

TnTandyO · September 10th, 2003, 04:42 PM

ok I feel like maybe I should just walk away from this for a while but I just cant leave it here. :(

Now it is working but not right... I redirected it back to the first page, but it's not getting that far, I make changes and it goes to'

Display2.asp?action=editsave
the form is blank and image is broken and it did not change the data base.....

Sick of me yet????
Thank you so much,
Tandy

Imar · September 10th, 2003, 04:46 PM

What do you mean by "redirect it back" Can you describe the process in more detail?

here's the work flow the way it should be:

1. You open page1.asp which shows multiple records.
2. You click an image and are taken to page2.asp, and passing action=select and the ID in the querystring
3. You see the record you can edit
4. You make some modifications and click Save
5. The database is updated
6. You are redirected back to page1.

In what step are things not working? Take a look at the HTML source in your browser. There may be an ASP error, hidden in the source because of broken <table>, <tr> and <td> tags.

Imar

---------------------------------------
Imar Spaanjaars
Everyone is unique, except for me.