Question on Primary keys in existing SQL database

[polymathinc]

OK, with everyone's help I am able to update the data in an existing SQL database. Thanks.

Question, when I issue the INSERT command to an existing table to append a record and store some data it results in the following error "[ODBC SQL Driver][SQL Server]Cannot insert the value NULL into column "SalesOrder", table sysprocompanyT.dbo.sormaster; column does not allow nulls, INSERT fails.

The simple code I have is:
INSERT INTO sormaster (salesorder) VALUES ("000526")

This of course creates the record in the view but does not write the record back to the server when I issue the TABLEUPDATE() command.

I have a database called SCANNER
I have a remote view called sormaster


Am I missing something? This is my first experience with MS SQL server and I appreciate the help.

 

[ilyad]

What is the SalesOrder field type in SQL Server?

 

[Mike Lewis]

You need to make sure the primary key is flagged as an updateable field.

If you are using the remote view designer, make sure there is a tick against the primary key field under the "pencil" column (this is on the Update Criteria page).

Normally, you don't want the PK to to be updateable, but that's not the case when doing an INSERT.

I'm assuming from the title of your question that SalesOrder is the primary key of the target table. If that's not the case, let us know.

Mike


__________________________________
Mike Lewis (Edinburgh, Scotland)

My Visual FoxPro site: www.ml-consult.co.uk

 

[JRB-Bldr]

While the backend that I most often use is not SQL Server, I sometimes get a similar error message when INSERTING records.

In my situation (and I assume in your's as well) when I get the error message Cannot insert the value NULL into column..... it is because I have not formated my input record properly.

When I look at the recipient table I find that it contains fields which are flagged NULLS - NOT ALLOWED. If my input data has a blank value in a field marked as such I will get that error message.

I can also get this error message if I have my VFP table containing field names limited to 10 characters and which do not match the recipient table field name EXACTLY, then the INSERT again thinks that I am attempting to insert NULLS into some field(s). If this happens then I make a Local View of my VFP table and create 'pseudo' field names which, within the View, can be longer than 10 characters so as to exactly match the destination field names.

Consequently I have to carefully examine my input data to ensure that it matches the field requirements of the destination table.

Good Luck,
JRB-Bldr

 

[polymathinc]

Thanks to all for your comments and suggestions. I added the SET NULL OFF statement and this seemed to fix the problem when "appending" inserting records into the SQL table.

And, following Mike L's advice I made sure that the PK was flagged as updateable.

I appreciate the quick responses and the assistance.

Dave G.