Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people, just like you, are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
Solved

SQL DATA BASE COLUMNS

Posted on 2012-12-24
10
544 Views
Last Modified: 2012-12-25
I ahve an SQL data base with 15 columns.   The data base is updated by two different forms with different infomation; however, information that is needed to be drawn into a gridview.

My issue is the forms do not insert data into the columns utilized by the other form which is throwing the following error:

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

Is there a work around for this?
0
Comment
Question by:BOEING39
  • 4
  • 3
  • 3
10 Comments
 
LVL 22

Expert Comment

by:Steve Wales
ID: 38719628
You'll need to modify the forms / programs - whatever the code is that updates the table.

Make sure that the code for the inserts is inserting the correct number of columns, with the correct data types.

If you want to be sure, you can specify the column names in the insert statement:

insert into mytable(col1, col2, col3)
values (data_for_col1, data_for_col1, data_for_col3);
0
 

Author Comment

by:BOEING39
ID: 38719632
The columns are correct that are being inserted.   I am new to SQL; however, I guess my question here is does the number of table categories or columns have to match the form?   The table contains 15 columns.  

Form 1 uses 10 of those and Form 2 uses the other 5.    When I delete the 5 columns used on the 2nd form from SQL Server , Form 1 work just fine.

In MS Access this made no difference.  I could have as many columns in the table as I needed which were not all used with each individual form.
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 38719638
Well, depends how your columns are defined.

If you have the 5 that aren't used defined to allow nulls, you could just insert the 10 that form 1 uses, and have the others as nulls.

Alternatively, if the table is defined so that everything is not null, then you will need to provide a value even if it's just a space or a zero.

Let's say I have a table that looks like this:

create table tab1 (keycol int, col1 char(10), col2 char(10))

If form 1 uses the key column and col1 and form 2 uses the key column and col2, I could do this:

Form 1:
insert into tab1 (keycol, col1) values (@variable1, @variable2)

Then on Form 2:
insert into tab1 (keycol, col2) values (@variable1, @variable2)

Since the columns aren't defined as not null, the 2 values for each insert are inserted as stated, the unspecified column is null.

However, since you're getting a column error, I'm guess that's not the issue.  The columns are probably defined as not null.

Can you provide the table definition and the insert code from the 2 forms ?  (mask table and column names with generic tab1, col1 type syntax if you prefer.
0
Use Case: Protecting a Hybrid Cloud Infrastructure

Microsoft Azure is rapidly becoming the norm in dynamic IT environments. This document describes the challenges that organizations face when protecting data in a hybrid cloud IT environment and presents a use case to demonstrate how Acronis Backup protects all data.

 
LVL 2

Expert Comment

by:corvetteguru
ID: 38719641
Are you using stored procedures or scripted SQL?

This error means, simply put, the number of items you are updating does not match the number that need to be updated.

There are two causes, the above mentioned no nulls field or the proc is wrong.

I tend to agree that the null fields is the problem.
0
 

Author Comment

by:BOEING39
ID: 38719653
When asking original question I explain the defaultMCO below shares the same columns in some cases.   I have attached the code behind for both forms as well as the server setup.
Key.doc
default.aspx.cs
defaultMCO.aspx.cs
0
 

Author Comment

by:BOEING39
ID: 38719662
The only no null field is ID.....
0
 
LVL 22

Expert Comment

by:Steve Wales
ID: 38719673
Problem is in defaultMCO.aspx.cs

There are 15 columns in the table.  You're inserting 12 values:

        cmd.CommandText = ("INSERT into AOS VALUES (@AOSdate,@Sta,@Ship,@ATA,@InFLt,@ArrTime,@Status,@MCOissuedBy,@History,@ETR,@Reason1,@MCOInst)");

        cmd.Parameters.AddWithValue("@AOSdate", AOSdate.Text);
        cmd.Parameters.AddWithValue("@Sta", Sta.SelectedValue);
        cmd.Parameters.AddWithValue("@Ship", Ship.SelectedValue);
        cmd.Parameters.AddWithValue("@ATA", ATA.SelectedValue);
        cmd.Parameters.AddWithValue("@InFlt", InFlt.Text);
        cmd.Parameters.AddWithValue("@ArrTime", ArrTime.Text);
        cmd.Parameters.AddWithValue("@Status", Status.SelectedValue);
        cmd.Parameters.AddWithValue("@MCOissuedBy", MCOissuedBy.SelectedValue);
        cmd.Parameters.AddWithValue("@History", History.SelectedValue);
        cmd.Parameters.AddWithValue("@ETR", ETR.SelectedValue);
        cmd.Parameters.AddWithValue("@Reason1", Reason1.Text);
        cmd.Parameters.AddWithValue("@MCOInst", MCOInst.Text);


Since the table is not allowing nulls, you are required to specify all 15 values.

Also since you're not specifying any column names, you need to have the 15 columns in the same order in the insert statement as they appear in the table.  These 12 values are all jumbled up.

You can change up the order if you specify the columns names (like I mentioned in one of my earlier replies).

Make the insert look like the one in the other form (Correct order, specifying all values) and your problem should go away.
0
 
LVL 2

Accepted Solution

by:
corvetteguru earned 500 total points
ID: 38719677
I see the problem...

You have this insert statement:

INSERT into AOS VALUES (@AOSdate,@Sta,@Ship,@ATA,@InFLt,@ArrTime,@Status,@MCOissuedBy,@History,@ETR,@Reason1,@MCOInst)

Do this:

INSERT into AOS
(AOSdate,Sta,Ship,ATA,InFLt,ArrTime,Status,MCOissuedBy,History,ETR,Reason1,MCOInst)
VALUES (@AOSdate,@Sta,@Ship,@ATA,@InFLt,@ArrTime,@Status,@MCOissuedBy,@History,@ETR,@Reason1,@MCOInst)

Using the field list will stop errors like your's...
0
 

Author Closing Comment

by:BOEING39
ID: 38720293
Corvetteguru:


Right on the mark.    Thank you.

cmd.CommandText = ("INSERT into AOS (AOSdate,Ship,ArrTime,InFLt,History,ETRDate,ETRTime,Sta,Reason1,Reason2,Parts,Updated,ATA,Status,ETR) VALUES (@AOSdate,@Ship,@ArrTime,@InFlt,@History,@ETRDate,@EtrTime,@Sta,@Reason1,@Reason2,@Parts,@Updated,@ATA,@Status,@ETR)");
0
 
LVL 2

Expert Comment

by:corvetteguru
ID: 38720323
You are most welcome! Now... enjoy your Holiday! :)
0

Featured Post

Ransomware-A Revenue Bonanza for Service Providers

Ransomware – malware that gets on your customers’ computers, encrypts their data, and extorts a hefty ransom for the decryption keys – is a surging new threat.  The purpose of this eBook is to educate the reader about ransomware attacks.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Row insertion failed. Array 5 45
SQL, add where clause 5 23
SQL Group By Question 4 18
sql 2016 data tools breakdown.. 1 11
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the SELECT statement in SQL to return specific rows and columns, with various degrees of sorting and limits in place.

789 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question