VB.NET SQL Error

[Shrimpersfan]

I have an application which uses a SQL connection and brings data into a datagridview. The app allows the user to take this data, make changes on it and then save these changes.

 

This is fine when the user only brings back and manipulates around 100 - 1000 records, when the recordset is higher than this, all records that have been manipulated get inserted into the database apart from 1 record.

 

The error message within the application says 'Cannot insert NULL value into column 'QuoteID', column does not insert NULL's. Insert failed.

 

The app always brings back this error when using a large recordset, when i look in the db to see how many had been imported, every record is saved except one.

 

Just wondering if anyone had come across something like this before.

[Celeste]

I'm not familiar enough with VB.Net to know for sure ... but I do have some programming sense that may help ...

 

[*]is the unsave record the first or last or somewhere in the middle?

[*]are you sure that the problem is related to record count? (perhaps there is a bad record that gets included frequently based on selection criteria)

[*]have you been able to identify & analyze the problematic record?

[*]is there invalid data in the field?

[*]does the database not allow null values in the field?

[*]do you have some sort of character that is fooling the program? (line feed, paragraph mark, unmatched quote)

 

I hope these thoughts have been useful in assisting you to find your problem.

Celeste

[SteveAJones71]

If you are making changes then you should not be INSERTing but UPDATEing the records, there are fairly basic ways to iterate through your recordset finding just the records that have changed and then you can manually update the relevant entries. During this process you can also find new records and write them to the database via a slightly different method.

 

If it help I'll be willing to have a look at the code for you.

[mrwITch]

I have an application which uses a SQL connection and brings data into a datagridview. The app allows the user to take this data, make changes on it and then save these changes.

 

This is fine when the user only brings back and manipulates around 100 - 1000 records, when the recordset is higher than this, all records that have been manipulated get inserted into the database apart from 1 record.

 

The error message within the application says 'Cannot insert NULL value into column 'QuoteID', column does not insert NULL's. Insert failed.

 

The app always brings back this error when using a large recordset, when i look in the db to see how many had been imported, every record is saved except one.

 

Just wondering if anyone had come across something like this before.

 

 

Do you have another tool via which you can query the DB

If so run a query something like

Select * from

where QouteID is Null

(you may need to vary the syntax of the above query to suit your DB)

 

You should also try to get a defintion of the table containing the column QuoteID

 

Your error condition suggests that the column QuoteID now has a Not-Null constraint applied to it & that in the past this may not havebeen true

Now that you've trie to write a recotrd back to the DB - you are falling foul of this constraint

 

If this turns out to be the correct analysis - then you should check any such rows

Are they still valid - can you have rows where QuoteID is NULL

 

If not then you can either delete the row (if the record & any associated data is invalid)

Or you can try to specify a valid value for QuoteID - If you can determine such from other data