Wrox Programmer Forums
|
Classic ASP Databases Discuss using ASP 3 to work with data in databases, including ASP Database Setup issues from the old P2P forum on this specific subtopic. See also the book forum Beginning ASP.NET Databases for questions specific to that book. NOT for ASP.NET 1.0, 1.1, or 2.0.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Classic ASP Databases section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old May 24th, 2004, 10:25 AM
Authorized User
 
Join Date: Jan 2004
Posts: 43
Thanks: 0
Thanked 0 Times in 0 Posts
Default update problem

I'm having a problem with my updated statement. I've looked everywhere and can't come up with what I'm doing wrong. I'm able to select but not to update. It's probably something silly. Any help would be appreciated. Here's the code.

emp=Request.Form("emp")
tel=Request.Form("tel")
note=Request.Form("note")

Set DataConn = Server.CreateObject("ADODB.Connection")
DataConn.Open "Main"

SQLstmt = "UPDATE personnel "
SQLStmt = SQLstmt & "SET personnel.employee='" & emp & "', "
SQLStmt = SQLstmt & "personnel.telephone='" & tel & "', "
SQLStmt = SQLstmt & "personnel.code='" & note & "' "
SQLStmt = SQLStmt & " WHERE personnel.employee='" & emp & "'"

And the response.write

UPDATE personnel SET personnel.employee='Susan McMahon', personnel.telephone='696-6838', personnel.code='811' WHERE personnel.employee='Susan McMahon'
 
Old May 24th, 2004, 10:41 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 1,110
Thanks: 0
Thanked 3 Times in 3 Posts
Default

Does it give an error message.

Also, check to see if the IUSR account has access to the folder that has the database is in.

 
Old May 24th, 2004, 11:58 AM
Authorized User
 
Join Date: Jan 2004
Posts: 43
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I believe it's a folder permissions issue as you suggested. Thank you! I was so caught up in my update statement I didn't think to look.
 
Old May 25th, 2004, 05:31 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

What is the error that you get? Can you post that?

_________________________
-Vijay G
Strive for Perfection
 
Old May 26th, 2004, 08:15 AM
Friend of Wrox
 
Join Date: Sep 2003
Posts: 171
Thanks: 0
Thanked 1 Time in 1 Post
Default

DataConn.execute "update personnel set employee='" & emp & "', telephone='" & tel & "', code='" & note & "' where employee = '" & emp & "'"

By the way, why are you even updating the employee field with the same info?

 
Old May 26th, 2004, 08:46 AM
Authorized User
 
Join Date: Jan 2004
Posts: 43
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Well, the idea was to update the employee field with the edited info from the form.

I've checked all the permissions for the general user account and they seem fine.

I also did not mention it was an Access 97 database. I've since converted it to 2000 hoping my error was somehow related to that. I also added an emp_id field because I see how what I had was not going to work as I intended.

So, I am currently trying to select an employee by name, and I output to the screen the emp_id. But it won't recognize the field. "Item cannot be found in the collection corresponding to the requested name or ordinal". So I assume it just can't see it. It can see the record because it will output everything else. Just not this field I have added.

I hope I'm explaining this clearly. This is what I'm currently trying to do. The code below works great. But, if I try to output emp_id to the screen, I get the error I mentioned.

Set RS = DataConn.Execute("SELECT * FROM personnel where employee ='" & emp & "'")
Response.write rs("title")

I appreciate all your help.
 
Old May 26th, 2004, 01:17 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Justine,

When you select record based on Name, consider the name is "Justne" And I wanted to edit it as "Justine" as there was "i" missing in it. So the update statement would be like this as per your code.

Code:
UPDATE personnel SET personnel.employee='Justine', personnel.telephone='696-6838', personnel.code='811' WHERE personnel.employee='Justine'
Now, I will never succeed in updating that, as Justine is not found in the table. Also if there are many others with the same name Justine, all their data would be overwritten with this. And you risk losing data of others.

So better have an Id field and always update using that in the where clause.

Regarding the "Item cannot be found in the collection corresponding to the requested name or ordinal" Error that you were getting, It happens when you are trying to access a column name that doesnot exist in the SELECT statement. As you say you were using SELECT *, I wonder if that column "emp_id" exists in your table. To find that out try doing a response.write and execute the query in your access db to see if that succeeds and the column emp_id that you are trying to access exists there with value.

Code:
Response.write "SELECT * FROM personnel where employee ='" & emp & "'"
Should output something like
SELECT * FROM personnel where employee ='Justine'

Run this in a SQL view in your access db and see if that result in what you are trying to extract later.

Hope that helps.
Cheers!

_________________________
-Vijay G
Strive for Perfection
 
Old May 26th, 2004, 02:46 PM
Authorized User
 
Join Date: Jan 2004
Posts: 43
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Happygv - yes I did spot that. Thank you! I guess I was trying to explain that but did a poor job of it. But I do very much appreciate your help! So, I did add the emp_id field. But the query acts as though the column doesn't even exist. If I were to select * from personnel where emp_id = 1. I would get my error. If I were to select * from personnel where employee = "justine" I would get a record. So I'm confused as to why it is ignoring my emp_id field. It does exist in the database, but my query tells me it does not.

If I select * from personnel and try to run response.write rs("emp_id") I once again get my error. I've tried to strip it down to the very basics to pinpoint my error. But I can't seem to solve it. Thanks again for your help.
 
Old May 26th, 2004, 03:05 PM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,480
Thanks: 0
Thanked 1 Time in 1 Post
Default

Quote:
quote:If I were to select * from personnel where employee = "justine" I would get a record. So I'm confused as to why it is ignoring my emp_id field. It does exist in the database, but my query tells me it does not
That can never happen. It's strange.

Did you try the Response.write thing? What was that resulting? Does that show emp_Id? What is the datatype of emp_id in personnel table?

Try this, if you haven't done that yet.

Step1
------
Response.write "SELECT * FROM personnel where employee ='" & emp & "'"
Response.end

Step2
------
Open your access mdb file.
1) Click on "Queries".
2) Double click "Create query in Design view".
3) Add the Table "personnel"
4) From "View" Menu, choose SQL View.

Paste the output of Step1, and execute that. Let me know if you see emp_id there, and what value it holds?

Or see if you have mis-spelt the emp_id/empid column name in your recordset referencing.

Cheers!

_________________________
-Vijay G
Strive for Perfection
 
Old May 26th, 2004, 03:18 PM
Authorized User
 
Join Date: Jan 2004
Posts: 43
Thanks: 0
Thanked 0 Times in 0 Posts
Default

I did what you asked, in access, my query returned the employee record with an emp_id value of 9, which is correct. The emp_id column is not misspelled in my query or in my asp so does that define the problem better for you?? Again, thank you so much.





Similar Threads
Thread Thread Starter Forum Replies Last Post
Update problem aceaceace Visual Basic 2005 Basics 6 February 21st, 2007 10:25 PM
Update problem filipczako ADO.NET 6 December 15th, 2005 11:58 AM
Update Problem skwilliams Classic ASP Databases 0 December 1st, 2005 11:02 AM
update problem lamdog ASP.NET 1.0 and 1.1 Basics 1 March 15th, 2005 11:59 PM
Update problem acko SQL Server 2000 3 June 29th, 2004 01:50 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.