SQL Help

[pmanson]

Hi All,

Currently working on some SQL for my dissertation (ARAGH!).

I have an aspx website which uses a grid view to call an access database. This gridview is calling the user table from the database.

The User table is as follows:

User_Id (PK)
Dept/Site_Id (FK to Dept/Site table)
User_Surname
User_FirstName
User_Role

I can't update the table which must be a straight forward problem to fix but I can't see the wood for the trees.


My Update SQL is as follows:

UPDATE [User]
SET User_Id = ?, [Dept/Site_Id] = ?, User_Surname = ?, User_FirstName = ?, User_Role = ?

However its returning a parse error. Any ideas why?

The error is saying that it would create duplicate errors in the index, primary key, or relationship.


Phill

[catretriever]

Are you updating an existing record, or wishing to insert a new one?

If it's the former than I think you will need a 'WHERE' clause to limit which record you affect.
If it's the latter, then use INSERT INTO, instead of UPDATE

[pmanson]

catretriever said:

Are you updating an existing record, or wishing to insert a new one? If it's the former than I think you will need a 'WHERE' clause to limit which record you affect. If it's the latter, then use INSERT INTO, instead of UPDATE

I've got a seperate INSERT statement which works as follows:

INSERT INTO [User] (User_Id, User_Surname, [Dept/Site_Id], User_FirstName, User_Role) VALUES (?, ?, ?, ?, ?)

I thought i needed a WHERE statement but i've been going around in circles! I'll have another go.

[pdV6]

pmanson said:

catretriever said: Are you updating an existing record, or wishing to insert a new one? If it's the former than I think you will need a 'WHERE' clause to limit which record you affect. If it's the latter, then use INSERT INTO, instead of UPDATE I've got a seperate INSERT statement which works as follows: INSERT INTO [User] (User_Id, User_Surname, [Dept/Site_Id], User_FirstName, User_Role) VALUES (?, ?, ?, ?, ?) I thought i needed a WHERE statement but i've been going around in circles! I'll have another go.

As CR says, your update is trying to modify ALL rows in your [User] table to have the same values. Ignoring the fact that you really don't want to be doing that anyway (I'm guessing ), the parser error is because that would make all the User_Id values the same, which is not allowed as its your primary key. Add a WHERE clause to tie it down to the specific row you really want to change.

Your INSERT works without a WHERE because you're creating a brand new row with the values you set. In fact, adding a WHERE to an INSERT would cause another parser error I'm sure.

[pmanson]

Ok my new UPDATE statement is as follows:

UPDATE [User] SET User_Id = ?, [Dept/Site_Id] = ?, User_Surname = ?, User_FirstName = ?, User_Role = ?
WHERE (User_Id = ?) AND ([Dept/Site_Id] = ?) AND (User_Surname = ?) AND (User_FirstName = ?) AND (User_Role = ?)

Still getting the following error:

"The changes you requested to the table were not successful because they would create duplicate values in the index, primary key, or relationship. Change the data in the field or fields that contain duplicate data, remove the index, or redefine the index to permit duplicate entries and try again."


So in response to PDV6's post I don't want it to set the User_Id as that is already defined.

Just got to work out the SQL to choose the line i've selected to update then.

>> Edited by pmanson on Tuesday 7th March 13:33

[pmanson]

Ok,

Still confused here!


I have another table which I can update with the following SQL:

Type table update said:

UPDATE Type SET Type_Name = ? WHERE (Type_ID = ?) AND (Type_Name = ?)

It just updates a tables with different types of Assets in it. Eg. Laptop and Desktop


So the UPDATE query on my User table which I can't get to work should be as follows:

User table update said:

UPDATE [User] SET [Dept/Site_Id] = ?, User_Surname = ?, User_FirstName = ?, User_Role = ? WHERE (User_Id = ?) AND (Dept / Site_Id = ?) AND (User_Surname = ?) AND (User_FirstName = ?) AND (User_Role = ?)

But that returns the error:

"No value given for one or more required parameters."

[pdV6]

OK - lets narrow this down a bit. What information are you trying to change and what information do you have by which you can identify the row?

You really need to pare the SQL down to the minimum required to get the job done, e.g.

UPDATE [Users] SET [Dept/Site_Id] = whatever WHERE [UserId] = whatever

rather than updating all columns in the row for no good reason.

[jamieboy]

Not sure if it's just a typo in your post or in your SQL, but you're referring to the field as [Dept/Site_Id] and as [Dept / Site_Id] (with spaces), which could throw the missing parameter error.

If you're not wanting to change the User_Id, I'm not sure why you're including it in your update statement?

Split it down one at a time until it works. So if you know the User_Id of one row (say, UserId is 10), use something like:

UPDATE [User] SET [Dept/Site_Id] = ? WHERE (User_Id = 10)

If this works, try the same thing for each of the other fields, then try updating more than one field at a time.

edit - just as PDV6 said while I was typing. Must get a faster keyboard.

>> Edited by jamieboy on Tuesday 7th March 14:11

[Don]

pmanson said:

Ok my new UPDATE statement is as follows: UPDATE [User] SET User_Id = ?, [Dept/Site_Id] = ?, User_Surname = ?, User_FirstName = ?, User_Role = ? WHERE (User_Id = ?) AND ([Dept/Site_Id] = ?) AND (User_Surname = ?) AND (User_FirstName = ?) AND (User_Role = ?)

So. Is User_Id the Primary Key? So why on earth have you got a Where clause with all that other stuff in?

UPDATE USER
SET [Dept/Site_Id] = ?, User_Surname = ?, User_FirstName = ?, User_Role = ?
WHERE User_Id = ?

should do the job...

[pmanson]

Don said:

pmanson said: Ok my new UPDATE statement is as follows: UPDATE [User] SET User_Id = ?, [Dept/Site_Id] = ?, User_Surname = ?, User_FirstName = ?, User_Role = ? WHERE (User_Id = ?) AND ([Dept/Site_Id] = ?) AND (User_Surname = ?) AND (User_FirstName = ?) AND (User_Role = ?) So. Is User_Id the Primary Key? So why on earth have you got a Where clause with all that other stuff in? UPDATE USER SET [Dept/Site_Id] = ?, User_Surname = ?, User_FirstName = ?, User_Role = ? WHERE User_Id = ? should do the job...

WooHoo! Thanks Don that worked a treat.

I knew it was something straight forward.

Regards,

Phill

[sybaseian]

The link is for Sybase transact SQL, but it will give you the basics of using queries with access. It much better than the Microsoft version and will cover 95% of what you need.

MS SQL server and MS access were originally based on Sybase, that's why most of the SQL statements and stored procs are very similar. Same goes for the basic architechture as well.

http://manuals.sybase.com/onlinebooks

>> Edited by sybaseian on Tuesday 7th March 15:15