Can't create new supplier, Insert Fails.

HI, it seems that User interface is not giving its validation, however you can check few properties in HQ for the Supplier table and you can go to Enterprise manager if you have then go to HQ Database and then supplier table and select it design mode then check HQID is there with the default set to (0).

sec> I am trying to insert a new supplier after moving to RMS 2.0 I'm not

All fields in RMS have a default value if they are a non BLOB field (or timestamp). Do you change the schema of supplier table?

cod

I am trying to insert a new supplier after moving to RMS 2.0 I'm not using headquarters and I get the following error whenever I try to insert a new supplier or copy an existing one. Thanks for any help.

cannot insert the value NULL into column 'HQID', table 'USN.dbo.supplier'; column does not allow nulls. INSERT fails.

messagenews: snipped-for-privacy@e65g2000hsc.googlegroups.com...

cod nailed it. I changed the schema, but not intentionally. I reindexed and checked my database before moving from 1.2 to 2.0 and everything worked fine but when I moved to 2.0 my suppliers table became corrupted. checktable and checkdb and everything else i tried wouldn't fix it. The only thing I could do was copy the table before and then copy it back after the upgrade. So I know realize that when you copy a table the way I did it drops the indexes as well as the schema. Is there a script or anything i can use to repair the schema and index of that table? Thanks for your help.

I know only a way.. another script :) Do you have sql enterprise manager or management studio? Open with management studio the schema of your supplier table, and check the default value of all columns..

Or if is possible, trace with sql profiler the t-sql events to find the column or the script that throw the rollback of transaction.

Or send by mail/newsgroup the sql-script created for your table.

I don't have SQL enterprise manager or management studio but I can get whichever one is cheaper that will get the job done. Is that the name of the software or does it come as part of a package?

I was able to alter the default value of the hqid column in the supplier table with a one line script. It now lets me alter and add suppliers (so my original problem is fixed), but I'd like to have the supplier table set up the way it is supposed to be. I'm sure it's possible to alter each field and set their defaults, constraints and indexes, but I don't even know where I'd get the information on which columns are indexed, what the indexes are supposed to be called, the constraints of each column, and which other columns besides hqid, if any, have default values.

You can get AquaStudio from

It's a good package to manage MSSQL/mysql and many others RDBMS ..

The RMS HQ and SO database have no foreign keys on databases, a table have the primary key and 1 index.

Now I set a reminder on my phone to check tomorrow the supplier table of RMS v2.0 and when will be possible I send you the schema of standard supplier table.

bye cod

snipped-for-privacy@gmail.com wrote:

Ok .. in the end of this message you found the standard script of supplier table of RMS v2.0

I have removed from the script the collation and the default constraint created for DEFAULT value .. All fields have a default value .. 0 for number.. '' for nvarchar fields..

The exceptions are: DBTimeStamp -> timestamp -> null -> used for rowversioning (managed by sql server) CustomDate1 -> null

....

do you have the script of your supplier table to compare with the standard?

cod

CREATE TABLE [dbo].[Supplier]( [Country] [nvarchar](20) DEFAULT (''), [HQID] [int] NOT NULL DEFAULT (0), [LastUpdated] [datetime] NOT NULL DEFAULT (getdate()), [State] [nvarchar](20) NOT NULL DEFAULT (''), [ID] [int] IDENTITY(1,1) NOT NULL, [SupplierName] [nvarchar](30) NOT NULL DEFAULT (''), [ContactName] [nvarchar](30) NOT NULL DEFAULT (''), [Address1] [nvarchar](30) NOT NULL DEFAULT (''), [Address2] [nvarchar](30) NOT NULL DEFAULT (''), [City] [nvarchar](30) NOT NULL DEFAULT (''), [Zip] [nvarchar](20) NOT NULL DEFAULT (''), [EmailAddress] [nvarchar](255) NOT NULL DEFAULT (''), [WebPageAddress] [nvarchar](255) NOT NULL DEFAULT (''), [Code] [nvarchar](17) NOT NULL DEFAULT (''), [DBTimeStamp] [timestamp] NULL, [AccountNumber] [nvarchar](20) NOT NULL DEFAULT (''), [TaxNumber] [nvarchar](20) NOT NULL DEFAULT (''), [CurrencyID] [int] NOT NULL DEFAULT (0), [PhoneNumber] [nvarchar](30) NOT NULL DEFAULT (''), [FaxNumber] [nvarchar](30) NOT NULL DEFAULT (''), [CustomText1] [nvarchar](30) NOT NULL DEFAULT (''), [CustomText2] [nvarchar](30) NOT NULL DEFAULT (''), [CustomText3] [nvarchar](30) NOT NULL DEFAULT (''), [CustomText4] [nvarchar](30) NOT NULL DEFAULT (''), [CustomText5] [nvarchar](30) NOT NULL DEFAULT (''), [CustomNumber1] [float] NOT NULL DEFAULT (0), [CustomNumber2] [float] NOT NULL DEFAULT (0), [CustomNumber3] [float] NOT NULL DEFAULT (0), [CustomNumber4] [float] NOT NULL DEFAULT (0), [CustomNumber5] [float] NOT NULL DEFAULT (0), [CustomDate1] [datetime] NULL, [CustomDate2] [datetime] NULL, [CustomDate3] [datetime] NULL, [CustomDate4] [datetime] NULL, [CustomDate5] [datetime] NULL, [Notes] [ntext] NOT NULL DEFAULT (''), [Terms] [nvarchar](50) NOT NULL DEFAULT (''), CONSTRAINT [PK_Supplier] PRIMARY KEY NONCLUSTERED ( [ID] ASC )WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY] ) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]

of RMS v2.0

Thanks for this. I just renamed my old suppliers table, temporarily disabled the dbtimestamp and primary field, copied everything over, changed those back and everything is working great now. I appreciate you taking the time to help me out with this.

-Tyler