Connectivity Error

[codenavigator]

I am trying to insert a record into an MS SQL table via a remote view. When I update the table, I get an error stating that a null value is not allowed in the column, even though the field is not null.

Any help would be appreciated.

Thanks

 

[rgbean]

What's the SQL server field type, what is the field type in your view, and what's the value of the data you are trying to update?

Many times when there aren't EXACT matches in the field types, the remote view / ODBC mechanism just doesn't do what you might expect. (e.g. Character vs VarChar)

Also, so everyone is starting at the same point:
1) What version an service pack level of VFP are you running?
2) What version and patch level of SQL Server are you using?
3) What version of the SQL ODBC driver are you using?

Rick

 

[Mike Lewis]

Codenavigator,

This is happening because you have not set the primary key field to be updatable.

In the view designer, go to Update Criteria, and make sure there is a tick under the pencil icon for every field, including the primary key. Without that, VFP will send the key as NULL, which gives rise to the message you saw.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com

 

[codenavigator]

You are exactly right. I thought that by clicking "Update All", I was setting all fields to be updateable, even the primary key fields. I see now that you have to manually check the primary fields.

THANKS!

 

[rgbean]

Gee Mike,
People are going to think you've used a Remote View before! (I have, it's just so automatic to check the update fields, I guess I never ran into the error! I've always said that I'm so good at answering questions because I've already made all the mistakes - I must have missed this one. )

Good catch!
Rick

 

[Mike Lewis]

Rick,

I've always said that I'm so good at answering questions because I've already made all the mistakes - I must have missed this one

Actually, it was worse than that for me. I was demonstrating remote views in a training course when I hit this problem. It took my 20 minutes or more to figure it out, trying not to look to embarassed while the trainees patiently waited.

Mike


Mike Lewis
Edinburgh, Scotland

My Visual Foxpro web site:

http://www.ml-consult.demon.co.uk

My Crystal Reports web site:

http://www.ml-crystal.com