Update three tables - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

Transact-SQL (2008)

Update three tables

Author

Topic

JazziBear
Starting Member

1 Post

Posted - 2012-05-05 : 19:02:00

Hi Guy's (Newbee first post)

I am trying to update 3 tables using 1 form

My DB is in Access

I have SQLServer 2012

I am writing code in VisualStidio 2010

All table keys are auto incremented so i am thinking I dont need to include the tble Id's in my queries

(If I do then how as numbers are already uniquely generated)

The code I am using is as follows



insertCommand.CommandText = "INSERT INTO Owner VALUES ('" & tbxFirstName.Text & "', '" & tbxLastName.Text & "')"

            insertCommand.CommandText &= "INSERT INTO CD VALUES ('" & tbxArtist.Text & "', '" & tbxAlbum.Text & "')"

            insertCommand.CommandText &= "INSERT INTO CDTracks VALUES ('" & tbxTrackName.Text & "')"

When I run this code I get the following error

SqlException was unhandled

Column name or number of supplied values does not match table definition

Then when I check my DB I find that the first table has been updated

any help wolud be massively appreciated

visakh16
Very Important crosS Applying yaK Herder

52326 Posts

Posted - 2012-05-05 : 19:10:50

you should include column list also for insert

like:-

]
insertCommand.CommandText = "INSERT INTO Owner (Col1Name,Col2Name) VALUES ('" & tbxFirstName.Text & "', '" & tbxLastName.Text & "')"

            insertCommand.CommandText &= "INSERT INTO CD (Col1Name,Col2Name) VALUES ('" & tbxArtist.Text & "', '" & tbxAlbum.Text & "')"

            insertCommand.CommandText &= "INSERT INTO CDTracks (ColName) VALUES ('" & tbxTrackName.Text & "')"

------------------------------------------------------------------------------------------------------
SQL Server MVP
http://visakhm.blogspot.com/

sunitabeck
Master Smack Fu Yak Hacker

5155 Posts

Posted - 2012-05-05 : 20:59:51

quote:
Then when I check my DB I find that the first table has been updated

If the values you provide exactly matches the number and ordinal positions of the columns, only then can you omit the column names. So in your case, the Owner table probably has two columns; so that statemetn succeeded. The CD table has more than two columns (or only one column) and hence the error message. For example:

CREATE TABLE #tmp1(id1 INT, id2 INT);

-- I can do this - because I am supplying id1 and id2.
INSERT INTO #tmp1 VALUES (1,1);

-- But I cannot do this - because I am supplying only one value.
INSERT INTO #tmp1 VALUES (2);

-- Instead, I would do this IF I want id2 to be null in this insert.
INSERT INTO #tmp1 (id1) VALUES (2);

-- 
DROP TABLE #tmp1;

If you have autoincremented columns, leave them out (from the column list and the values list).

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources