0

I am unfamiliar with SQL Server. I am used to MySQL and I am kind of bangning my head over a query. Not sure what the problem is.

The erreor I get is:

Msg 213, Level 16, State 1, Procedure tr_insertmerch, Line 29 Column name or number of supplied values does not match table definition.

The column name or number of supplied values usually means the query has more values then columns or the other way arround but I counted them and they all fit. Also I checked twice the names and they seem good as well. I am wondering if the problem is from tr_insertmerch.

But I have no idea where that is. The query was working fine until I added a new column to the table a few days ago. I added a column named youtubevideo and that is set as NULL in this query.

INSERT INTO
   merchant( register_date, name, phone, fax, email, anonymous, bank_name, account, routing, payment_modeID, website, description, free_call, street_address, zip, city, reference_name, reference_phone, tax1_num, tax2_num, login, password, regionID, statusID, cardID, monthly_fees, money_royalty_on_receive, money_royalty_on_send, sellerID, subsellerID, distributor, pourcentage, BtoC, trans_commission_rate_promo, trans_commission_rate, guaranted_card, youtubevideo) 
VALUES
   (
      '06-25-2019', 'somewebsite', '514 795-4444', NULL, 'info@somewebsite.com', '1', 'Bank', NULL, NULL, NULL, 'http://www.somewebsite.com', 'compagny', NULL, '41 Rue De l ile', 'G9B 7L4', 'Trois-Rivières', NULL, NULL, NULL, NULL, 'info@somewebsite.com', '*******', '12', '1', '363460', '0.00', '2', '0', '201', '196112', '0', '30', '1', '25', '30', NULL, NULL
   )

This is the export of my merchant table

USE [CLICLoyalty]
GO
/****** Object:  Table [dbo].[merchant]    Script Date: 25/06/2019 2:16:26 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_PADDING ON
GO
CREATE TABLE [dbo].[merchant](
    [merchantID] [bigint] NOT NULL,
    [register_date] [smalldatetime] NOT NULL,
    [name] [varchar](50) NOT NULL,
    [phone] [varchar](20) NOT NULL,
    [fax] [varchar](20) NULL,
    [email] [varchar](255) NULL,
    [anonymous] [bit] NOT NULL,
    [e_com] [bit] NOT NULL,
    [bank_name] [varchar](100) NULL,
    [account] [varchar](50) NULL,
    [routing] [varchar](50) NULL,
    [payment_modeID] [smallint] NULL,
    [alias_url] [varchar](100) NULL,
    [website] [varchar](100) NULL,
    [description] [text] NULL,
    [free_call] [varchar](50) NULL,
    [street_address] [varchar](100) NOT NULL,
    [zip] [varchar](20) NOT NULL,
    [city] [varchar](50) NOT NULL,
    [last_update] [smalldatetime] NOT NULL,
    [balance] [money] NOT NULL,
    [creditLine] [money] NOT NULL,
    [clic_balance] [money] NOT NULL,
    [monthly_fees] [money] NOT NULL,
    [clic_monthly_fees] [money] NULL,
    [min_clic_buy] [money] NOT NULL,
    [max_clic_buy] [money] NOT NULL,
    [min_clic_sell] [money] NOT NULL,
    [max_clic_sell] [money] NOT NULL,
    [min_money_buy] [money] NOT NULL,
    [max_money_buy] [money] NOT NULL,
    [min_money_sell] [money] NOT NULL,
    [max_money_sell] [money] NOT NULL,
    [reference_name] [varchar](50) NULL,
    [reference_phone] [varchar](20) NULL,
    [tax1_num] [varchar](50) NULL,
    [tax2_num] [varchar](50) NULL,
    [login] [varchar](255) NULL,
    [password] [varchar](30) NULL,
    [regionID] [bigint] NOT NULL,
    [sellerID] [bigint] NULL,
    [statusID] [int] NULL,
    [clic_royalty_on_receive] [int] NOT NULL,
    [money_royalty_on_receive] [int] NOT NULL,
    [clic_royalty_on_send] [int] NOT NULL,
    [money_royalty_on_send] [int] NOT NULL,
    [cardID] [bigint] NOT NULL,
    [subsellerID] [int] NULL,
    [email_new_merchant] [tinyint] NULL,
    [sent_as_new_merchant] [datetime] NULL,
    [languageID] [tinyint] NULL,
    [distributor] [bit] NOT NULL,
    [pourcentage] [int] NOT NULL,
    [BtoC] [bit] NOT NULL,
    [trans_commission_rate_promo] [int] NULL,
    [trans_commission_rate] [int] NULL,
    [promo] [bit] NULL,
    [publication_date] [datetime] NULL,
    [guaranted_card] [int] NULL,
    [youtubevideo] [varchar](max) NULL,
 CONSTRAINT [PK_Merchant] PRIMARY KEY CLUSTERED 
(
    [merchantID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [unique_log_merch] UNIQUE NONCLUSTERED 
(
    [login] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY],
 CONSTRAINT [UQ_UniqueMerchantID] UNIQUE NONCLUSTERED 
(
    [merchantID] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

GO
SET ANSI_PADDING OFF
GO
ALTER TABLE [dbo].[merchant] ADD  CONSTRAINT [DF_Merchant_register_date]  DEFAULT (getdate()) FOR [register_date]
GO
ALTER TABLE [dbo].[merchant] ADD  CONSTRAINT [DF_Merchant_anonymous]  DEFAULT ((0)) FOR [anonymous]
GO
ALTER TABLE [dbo].[merchant] ADD  CONSTRAINT [DF_Merchant_e_com]  DEFAULT ((1)) FOR [e_com]
GO
ALTER TABLE [dbo].[merchant] ADD  CONSTRAINT [DF_Merchant_balance_date]  DEFAULT (getdate()) FOR [last_update]
GO
ALTER TABLE [dbo].[merchant] ADD  CONSTRAINT [DF_Merchant_balance]  DEFAULT ((0)) FOR [balance]
GO
ALTER TABLE [dbo].[merchant] ADD  CONSTRAINT [DF_Merchant_credit_line]  DEFAULT ((0)) FOR [creditLine]
GO
ALTER TABLE [dbo].[merchant] ADD  CONSTRAINT [DF_Merchant_clic_balance]  DEFAULT ((0)) FOR [clic_balance]
GO
ALTER TABLE [dbo].[merchant] ADD  CONSTRAINT [DF_Merchant_monthly_fees_1]  DEFAULT ((0)) FOR [monthly_fees]
GO
ALTER TABLE [dbo].[merchant] ADD  CONSTRAINT [DF_Merchant_min_cash_buy]  DEFAULT ((0)) FOR [min_clic_buy]
GO
ALTER TABLE [dbo].[merchant] ADD  CONSTRAINT [DF_Merchant_max_cash_buy]  DEFAULT ((0)) FOR [max_clic_buy]
GO
ALTER TABLE [dbo].[merchant] ADD  CONSTRAINT [DF_Merchant_min_sell]  DEFAULT ((0)) FOR [min_clic_sell]
GO
ALTER TABLE [dbo].[merchant] ADD  CONSTRAINT [DF_Merchant_max_sell]  DEFAULT ((0)) FOR [max_clic_sell]
GO
ALTER TABLE [dbo].[merchant] ADD  CONSTRAINT [DF_Merchant_min_buy]  DEFAULT ((0)) FOR [min_money_buy]
GO
ALTER TABLE [dbo].[merchant] ADD  CONSTRAINT [DF_Merchant_max_buy]  DEFAULT ((0)) FOR [max_money_buy]
GO
ALTER TABLE [dbo].[merchant] ADD  CONSTRAINT [DF_Merchant_min_cash_sell]  DEFAULT ((0)) FOR [min_money_sell]
GO
ALTER TABLE [dbo].[merchant] ADD  CONSTRAINT [DF_Merchant_max_cash_sell]  DEFAULT ((0)) FOR [max_money_sell]
GO
ALTER TABLE [dbo].[merchant] ADD  CONSTRAINT [DF_Merchant_trans_clic_rate]  DEFAULT ((0)) FOR [clic_royalty_on_receive]
GO
ALTER TABLE [dbo].[merchant] ADD  CONSTRAINT [DF_Merchant_trans_money_rate]  DEFAULT ((0)) FOR [money_royalty_on_receive]
GO
ALTER TABLE [dbo].[merchant] ADD  CONSTRAINT [DF_Merchant_trans_sell_money_rate]  DEFAULT ((0)) FOR [clic_royalty_on_send]
GO
ALTER TABLE [dbo].[merchant] ADD  CONSTRAINT [DF_Merchant_trans_sell_clic_rate]  DEFAULT ((6)) FOR [money_royalty_on_send]
GO
ALTER TABLE [dbo].[merchant] ADD  CONSTRAINT [DF_merchant_email_new_merchant]  DEFAULT ((1)) FOR [email_new_merchant]
GO
ALTER TABLE [dbo].[merchant] ADD  CONSTRAINT [DF_merchant_sent_as_new_merchant]  DEFAULT (NULL) FOR [sent_as_new_merchant]
GO
ALTER TABLE [dbo].[merchant] ADD  CONSTRAINT [DF_merchant_languageID]  DEFAULT ((2)) FOR [languageID]
GO
ALTER TABLE [dbo].[merchant] ADD  DEFAULT ((0)) FOR [distributor]
GO
ALTER TABLE [dbo].[merchant] ADD  DEFAULT ((50)) FOR [pourcentage]
GO
ALTER TABLE [dbo].[merchant] ADD  DEFAULT ((1)) FOR [BtoC]
GO
ALTER TABLE [dbo].[merchant] ADD  DEFAULT ((25)) FOR [trans_commission_rate_promo]
GO
ALTER TABLE [dbo].[merchant] ADD  DEFAULT ((40)) FOR [trans_commission_rate]
GO
ALTER TABLE [dbo].[merchant] ADD  CONSTRAINT [DF_merchant_promo]  DEFAULT ((0)) FOR [promo]
GO
ALTER TABLE [dbo].[merchant] ADD  CONSTRAINT [DF_merchant_publication_date]  DEFAULT (NULL) FOR [publication_date]
GO
ALTER TABLE [dbo].[merchant] ADD  DEFAULT ((0)) FOR [guaranted_card]
GO
ALTER TABLE [dbo].[merchant]  WITH CHECK ADD  CONSTRAINT [FK_Merchant_card] FOREIGN KEY([cardID])
REFERENCES [dbo].[card] ([cardID])
GO
ALTER TABLE [dbo].[merchant] CHECK CONSTRAINT [FK_Merchant_card]
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'default current date' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'merchant', @level2type=N'COLUMN',@level2name=N'register_date'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'0:no 1:yes default : 0' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'merchant', @level2type=N'COLUMN',@level2name=N'anonymous'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'0: if merchant cannot sell on the website 1:yes default:1' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'merchant', @level2type=N'COLUMN',@level2name=N'e_com'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'default current date' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'merchant', @level2type=N'COLUMN',@level2name=N'last_update'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'default : 0' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'merchant', @level2type=N'COLUMN',@level2name=N'balance'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'default:0' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'merchant', @level2type=N'COLUMN',@level2name=N'creditLine'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'clic rate that merchant should give to the head_office' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'merchant', @level2type=N'COLUMN',@level2name=N'clic_royalty_on_receive'
GO
EXEC sys.sp_addextendedproperty @name=N'MS_Description', @value=N'royalty rate that merchant should give to the head_office' , @level0type=N'SCHEMA',@level0name=N'dbo', @level1type=N'TABLE',@level1name=N'merchant', @level2type=N'COLUMN',@level2name=N'money_royalty_on_receive'
GO
Arulkumar
  • 12,966
  • 14
  • 47
  • 68
Patrick Simard
  • 2,294
  • 3
  • 24
  • 38
  • 2
    Based on the error message, this table has a trigger (as named in message) that must also be updated to match your schema change. – SMor Jun 25 '19 at 18:26
  • 2
    I hope `tr_insertmerch` is the name of the Trigger, which may applied for the merchant table. You need to correct the new column addition inside the trigger too. [Refer this link](https://stackoverflow.com/q/12346914/2451726) to find the list of triggers related to merchant table – Arulkumar Jun 25 '19 at 18:26
  • That column `password`, without a column for the salt heavily implies you're storing plain text passwords. I *hope* not. – Thom A Jun 25 '19 at 18:30
  • 1
    Do you mean SSMS? If it's on the table then navigate to the table in the object explorer, and then expand it, and you'll find a triggers node. Expand that and you'll find the trigger. – Thom A Jun 25 '19 at 18:34
  • Found it! I added the col and now it works! Thx guys. Add your awnser and I will accept it. MSSQL is a really diffrent lol – Patrick Simard Jun 25 '19 at 18:37
  • As for the password, it's encoded with a salt from the user table. – Patrick Simard Jun 25 '19 at 18:39
  • Salt from the user table? That sounds might fishy, especially if it fits into a varchar(30). Storing the banking information in clear text is pretty scary too. This has a lot of red flags from a data standpoint. But glad you found the answer to the problem. My concern is that the trigger is doing an INSERT but not naming the columns. If it was then this wouldn't have been an issue. – Sean Lange Jun 25 '19 at 18:43

1 Answers1

2

I hope tr_insertmerch is the name of the Trigger, which may applied for the merchant table.

You need to correct the new column addition inside the trigger too.

Please refer this link to find the list of triggers related to the said table in SSMS.

Arulkumar
  • 12,966
  • 14
  • 47
  • 68