SQL Update on multiple tables

UPDATE dbo.Users
 SET FullName = @FullName
 , [e-mail] =  @Email
WHERE (ID = @selected)  

ps: you cannot update multiple tables at once, but you can update multiple fields from the same table at once.

So any ideas on how I can accomplish what I need to do?

One thing you could do is use a stored procedure. What I usually do is create a stored procedure something along the lines of “load data” which takes a parameter used to return the needed data. In your case, you would put your select statement into that “load data” stored procedure. Then from VB set your command object to the stored procedure name, set the command type to stored procedure, and set the parameters and load the data to your dataset or data table like normal.

Now for updates do the same thing. Create a stored procedure called “update data” and provide the necessary parameters to figure out what row to update (@selected) and also all the data that you will be updating.

Inside this “update data” stored procedure, update how ever many tables you want. As far as VB is concerned, you only used one command to do the work.

If you need an example on how to do this, please post your table schema and I’ll be happy to work out a good example for you.

Sorry I'm new to all of this.  I'm not sure what you mean by "table schema"  

It’s no problem all,

If you want to look at the stored procedure idea further I think it would be easer going if we had a bit more information such as the name of the tables involved and a list of the columns that are found in each of those tables. This will allow the creation of SQL that you can drop right into some newly created stored procedures.

Using stored procedures in a SQL Server 2000 database is not hard, the first time may be a bit confusing but after you’ve done it once you’ll be using them all over. Are you using Microsoft SQL Server 2000? I maybe made some assumptions that you were.

Yes I am using SQL Server 2000

Before I get into the stored procedure thing let me ask you this......

The SQL Update query I am trying to use is this:

UPDATE dbo.Users SET FullName = @FullName, [e-mail] = @email FROM dbo.Users INNER JOIN dbo.Positions ON dbo.Users.ID = dbo.Positions.ID WHERE (ID = @ID)

The thing is...... I can run that from the Query Builder inside of Visual Studio and it will ask me for the parameters - If I put in all the requested parameters the query will run fine.  I am able to update the DB exactly as I had planned but when I try to use that same query on the update statement attached to the Gridview (using the auto edit) I get an error

Line 1: Incorrect syntax near '-'.
Must declare the variable '@ID'

But it is delcared !

<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:PhonebookConnectionString2 %>"

  SelectCommand="SELECT dbo.Users.FullName, dbo.Positions.Phone, dbo.Users.[e-mail], dbo.Positions.Title, dbo.Users.ID FROM dbo.Users INNER JOIN dbo.Positions ON dbo.Users.ID = dbo.Positions.ID WHERE (dbo.Users.FullName LIKE '%' + @search + '%') OR (dbo.Positions.Phone LIKE '%' + @search + '%') OR (dbo.Users.[e-mail] LIKE '%' + @search + '%') OR (dbo.Positions.Title LIKE '%' + @search + '%')"

UpdateCommand="UPDATE dbo.Users SET FullName = @FullName, [e-mail] = @email FROM dbo.Users INNER JOIN dbo.Positions ON dbo.Users.ID = dbo.Positions.ID WHERE (ID = @ID)">

        <SelectParameters>
            <asp:ControlParameter ControlID="txtSearch" Name="search" PropertyName="Text" />
        </SelectParameters>

        <UpdateParameters>
            <asp:ControlParameter ControlID="GridView1" Name="ID" PropertyName="SelectedValue" />
        </UpdateParameters>

    </asp:SqlDataSource>


I just don't understand why this will work if I manually give it info but it won't pick up the parameters.  Maybe there's something wrong here that I'm missing but I just don't see it

Line 1: Incorrect syntax near '-'. hmmm?

are you somehow loosing the [ and ] around the world e-mail?

Other then that I'm not seeing the issue I'm afraid.

A few things I notice though:

UPDATE dbo.Users
      SET FullName = @FullName, [e-mail] = @email
FROM dbo.Users
      INNER JOIN dbo.Positions
            ON dbo.Users.ID = dbo.Positions.ID
WHERE (ID = @ID)

For each of these tables is ID the primary key? Because if yes, then you are linking the tables on the primary key. Did you mean to do something like this:

UPDATE dbo.Users
      SET FullName = @FullName, [e-mail] = @email
FROM dbo.Users
      INNER JOIN dbo.Positions
            ON dbo.Users.PositionID = dbo.Positions.ID
WHERE (ID = @ID)

Also what ID are you refering to in the where part? The ID in the user table or the ID in the positions table?

If you are refering to the ID in the users table why are you joining on the Positions table?
If youa re refering to the ID in the positions table, are you wanting to update all Fullnames and Emails for everyone of a specific position type?

In either case I'm not sure why you are joining on the Positions table.

The ID in the positions table is the ID from the User Table.  

ID is the Primary Key of the User Table
PositionID is the Primary key of the Positions table but it also has ID from the User Table

In the User Table Is where I want to update any changes made to the Name or email address

In the Posion Table is where I need to update any changes made to the Title or the Phone #

The ID field links the two tables together

As for my where statement I set @ID to be the id of the selected record in the gridview which should be ID from the Users Table (at least that what I was trying to acomplish)

Again, I'm just stumbling blindly through this trying to learn as I go so maybe I'm doing this all way wrong.  I have a knack for finding the hardest way to do something

Hopefully this will help clear things up

**User Table**
ID (primary Key)
Full Name
Email



**Positions Table**
PositionID (primary Key)
Title
Phone
ID (from the User Table)

Your mind is in the right mode, you realize you must update two tables. However, I just don’t think this will be possible with one update statement. I’m not sure why you are getting the error you are receiving but even if you fixed that error would you be updating all the data necessary? Or are you going to run two update statements?

The thing to keep in mind is that just because you write one select statement that does many joins to return data you do not have to write only one update statement to update that data.

The problem you are running into is you are only provided one time to run the update statement, thus you are feeling you need to combine the updates into one update SQL statement.

If you want to peruse looking for and fixing the cause of your current error I understand. I do feel, however, that you would be better served by using a stored procedure for at least the update.

Leaving the select statement as it is would work, but the update would work far better as a stored procedure or stored proc as I sometimes find my self saying.

Also please keep in mind my experience with the Gridview (for ASP?) is somewhat limited so I can only talk from a generic select/update mindset.

Just for my own understanding, are you using a dataset or datatable? Are you using a dataadapter to pull data into your app and a dataadapter to update back to your database?

Yes that will help thank you.

I'm using asp.net 2.0 and I'm using the new Gridview Control so I just throw in on there and connect it to a SQL data souce and then set me Update, Select, and Delete statements.  There is an option to set the statements to a Stored Proc so I guess I can do.  At this point I'd take any solution just as long as it works.  

I've made the stored procedure but I'm now getting this error msg

Procedure or function sp_Update_User_Positions has too many arguments specified.

when do you get this error?

When I run the application I select any record from the gridview - make some changes and click update (where it runs the update stored proc)  Then I get the error

How do you specify your parameters? The error you are getting is saying you have specified too many. Make sure you are not specifying the same one twice by mistake.

Can you post the code that is written, that specifies the parameters?

Actually I got it to work without the Stored Proc by doing this in the update statement :

UPDATE dbo.Users SET FullName = @FullName, Image=@Image  WHERE (ID = @ID)
UPDATE dbo.Positions SET Title=@Title, Phone=@Phone Where (ID=@ID)

Thanks a ton for all the help though !!

Even though I didn't use the Stored Proc you pointed me in the right direction.  Thanks for the time and patience !