edit-update record- help

TnTandyO · September 11th, 2003, 11:08 AM

Sorry I guess I'm just being a girl and a little to sensitive. :)

ok, It had some issues with the date and time field.. thinking maybe because they are numbers, so I took those out of the query.. the it tells me Sfname is not updateable.
I have another form that lets me add to this database, so if I can add to it shouldnt I be able to update? Or is sfname the problem, since I dont have a field in my database named sFname ect..

TnTandyO · September 11th, 2003, 11:19 AM

Hey hey I got it to work... had an sFname where no s should be....
grrrr 24 hour on one s...
but date and time are not working.. I took them out it works fine. (have an images issue but I think I can fix that. :))

Imar · September 11th, 2003, 01:16 PM

Yoohoo!!!!!

Congratulations.

Bloody sFName again ;) It's partly my fault. "Old school programming" dictated Hungarian Notation", where each variable was prefixed with 1 or more letters to indicate its type, like sName (for a string), iLoopCount (for an Integer) and so on.

However, it's not common practice to do that in the database, so your field names didn't match. Nowadays, using this way of naming your variables is less common, especially in strongly typed languages (as you can see from their type what they are supposed to be). FirstName, LastName etc are much more user-friendly and easier to read then sFName.....

The date issue may be caused by the way you insert the date. In Access, SQL Server and other databases, you can insert your date in the ISO format: YYYYMMDD, so today would be 20030911

This way, the database will understand the date correctly.

This page will tell you more about that.

One other issue you need to fix: Replacing single ticks in your code. Suppose somebody types this in the name field: O'Brien

As soon as that happens, your code will break as the last ' will be interpreted as the closing delimiter for your SQL value. So, if you expect your data will contain this character, fix it by escaping it (put another ' in front of it):

sFName = Replace(sFName, "'", "''")

The last part actually contains two apostrophes. Now the name will look like this:

O''Brien

When you pass that to the database, it will end up as O'Brien in the column FName.

Look here for more info.

(I am telling you this now, as I am sure you'll run into this pretty soon).

Cheers and good luck, sensitive girl ;)

Imar

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

TnTandyO · September 11th, 2003, 01:36 PM

Imar,
Preventive maintenance ... Thanks for that heads-up.
I guess I better do that with all of the text fields..
At this point I think I'm done with this program, unless I cause errors with that replace stuff ;) Have a few bells and whistles to add(the fun part).

Thanks for all your help, and if you don't see me back here for a while you can assume the program is working well, and I took a much needed break from this computer! :) If I can find some where to ftp this too once I'm done would you like to see it?
Take care,
Tandy

Imar · September 11th, 2003, 02:02 PM

Yeah, I'd like that. It would be nice to see how it ended up.
If it's too much trouble, though, don't bother. Getting your site to work on another location (at a remote host) might open up a can of worms big enough for another three page thread. ("What do you mean, it won't run at the production server? It runs fine on my development workstation....") :-)

Take some rest before you get square eyes......

Imar

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

TnTandyO · September 11th, 2003, 03:09 PM

:( I'm sorry for asking so many questions, As you can tell I'm pretty new at this. In fact this would be my 2nd time messing with this stuff.
I have tried to figure out where to put
sFName = Replace(sFName, "'", "''")
but it doesn't seem to make a difference.

Square eyes... you mean they are not supposed to be this way? Oh no!! haha.

Imar · September 11th, 2003, 04:32 PM

:) I know what you mean. I have been staring at a computer screen for the past couple of days as well. Just too busy to do something else...

Oh well, here goes:
Let's look at your SQL UPDATE statement:

Code:

strSQL = "UPDATE tblIThelp SET " _
        & "sFname = '" & (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 & ")"

The best place to put the replace, is right around the Request.Form thingies:

Code:

strSQL = "UPDATE tblIThelp SET " _

Code:

        & "Fname = '" & Replace((Request.Form("Fname")), "'", "''") & "', " _
        & "Lname = '" & Replace((Request.Form("Lname")), "'", "''") & "', " _
  etc. etc, more UPDATE statement here (I left that out)

Personally, I find this kind of code a bit messy and difficult to read and I prefer to write it like this:

Code:

' Declare

Code:

Dim sFName
Dim sLName

' Assign
sFName = Request.Form("FName")
sLName = Request.Form("LName")

' Modify / fix
sFName = Replace(sFName, "'", "''")
sLName = Replace(sLName, "'", "''")

This requires more steps, but IMO it's easier to read, understand and modify.

Even better would be this (as indicated by the link I posted):

Place the following function somewhere on your page, or put it in an include file for easy access:

Code:

Function FixSQL(ByVal SQLStatement)
  If Len(SQLStatement) > 0 Then
    FixSQL = Replace(SQLStatement, "'", "''")
  Else
    FixSQL = ""
  End If
End Function

With this function in your page, replacing the values is as easy as this:

Code:

strSQL = "UPDATE tblIThelp SET " _
        & "Fname = '" & FixSQL((Request.Form("Fname"))) & "', " _
        & "Lname = '" & FixSQL((Request.Form("Lname"))) & "', " _

or, in my preferred "verbose coding style":

Code:

' Assign

Code:

sFName = Request.Form("FName")
sLName = Request.Form("LName")

' Modify / fix
sFName = FixSQL(sFName)
sLName = FixSQL(sLName)

Does that make sense??

Imar

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

TnTandyO · September 11th, 2003, 06:31 PM

Thanks, that makes sense.... I'll give it a shot in the morning. Just don't have the heart for any more tonight.......
Did I keep you that busy? lol, duh!!
Thanks
TAndy