locked
Edit SQL table script? RRS feed

  • Question

  • User1389508859 posted

    I have been having numerous errors relating back to my INSERT string in my code-behind (written in C#). It started with "Error code 213: Column name or number of supplied values does not match table definition." I found an error in one instance and simply added the column headings to the other. (i.e. INSERT INTO log (col1,col2,col3) VALUES 'x', 'y', 'z'). The latest I have gotten is this:

    (TABLE 1)
    Error code 515: Cannot insert the value NULL into column 'ResID', table 'B&B.dbo.Reservations'; column does not allow nulls. INSERT fails. The statement has been terminated.

    (TABLE 2)
    Error code 110: There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.

    I believe the issue is because I didn't specify a start value for the primary ID. I verified this by going into SQL SERVER MANAGEMENT STUDIO and viewing my table script. The create portion is below.

    Can I alter this to include something like [identity(100,1)] ?

    CREATE TABLE [dbo].[Reservations](
    	[ResID] [smallint] NOT NULL,
    	[CustomerName] [varchar](50) NOT NULL,
    	[Phone] [varchar](10) NOT NULL,
    	[Email] [varchar](50) NULL,
    	[Arrive] [date] NOT NULL,
    	[Depart] [date] NOT NULL,
    	[Room] [varchar](20) NOT NULL,
    	[CardType] [varchar](10) NOT NULL,
    	[CreditCard] [varchar](16) NOT NULL,
    	[Discount] [varchar](5) NULL,
    	[TotalDue] [money] NOT NULL,
     CONSTRAINT [PK_Reservations] PRIMARY KEY CLUSTERED 
    Friday, November 22, 2013 8:32 PM

Answers

All replies

  • User2103319870 posted

    Hi,

    To set Identity Property to an existing column in a table you can refer the below link

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, November 22, 2013 9:41 PM
  • User1389508859 posted

    Thanks! That seems to have worked, although I am still getting the first error (Error code 110: There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.) on my Customers table. I'll have to take a closer look at that code again.

    I appreciate it! That was perfect.

    Friday, November 22, 2013 10:26 PM
  • User2103319870 posted

    mallorz

    (Error code 110: There are fewer columns in the INSERT statement than values specified in the VALUES clause. The number of values in the VALUES clause must match the number of columns specified in the INSERT statement.)

    This error means that in your insert statement you doesnt have values for the column mentioned.

    For ex if you have 5 columns mentioned in insert statement, you need to have 5 values also in insert statement.

    Most likely you are missing values in your statement, please check it and ensure that you have passed values for all column in insert statement.

    Please see the below example for more details

    Correct Code

    --This code will execute fine because for all columns there is value mentioned in this statement
    INSERT INTO [Test_Dev].[dbo].[TestTable]
               ([FirstName]
               ,[LastName])
         VALUES
               ('TestValues'
               ,'TestValue2')
    GO

    Incorrect code

    -- This code will throw exception because the value for LastName is not mentioned in this statement
    INSERT INTO [Test_Dev].[dbo].[TestTable]
               ([FirstName]
               ,[LastName])
         VALUES
               ('TestValues'
               )
    GO

    Above code is an sql server statement but this will give you and idea what is wrong in your code.


    Friday, November 22, 2013 10:33 PM
  • User1389508859 posted

    Yes, I found that as well. I had a portion that read: + "','" + "','" back to back which created one extra column as well as an obvious syntax error.

    Thanks again; I really appreciate your time.

    Friday, November 22, 2013 10:47 PM
  • User2103319870 posted

    Glad you found the solution to the problem.Smile

    Friday, November 22, 2013 11:16 PM