Data won't write to database

Hello,  I have a database with one particular trouble column.  It's a column I had to add (used to be there, someone deleted it), and I don't know if that has anything to do with this or not.  But it's a nvarchar column in a sql server database.  Here's my code:

            Dim dr As DataRow
            dr = DataSet_Items1.Tables("Products").NewRow
            dr.Item("ProductCode") = OID
MsgBox(OSupplier)
            dr.Item("SupplierCode") = OSupplier
MsgBox(dr.Item("SupplierCode")
            dr.Item("ProductName") = ONom
            dr.Item("UnitPrice") = OUPri
            dr.Item("Manufacturer") = OManf
            dr.Item("Model") = OModel
            dr.Item("Qty_Issue") = OUIss
            dr.Item("UnitsInStock") = 0
            dr.Item("ReorderLevel") = 0
            dr.EndEdit()

            DataSet_Items1.Tables("Products").Rows.Add(dr)

            Try
                ' When you call the update ADO.net will go through all the record that have a rowstate  'Modified' and will update that record
                SqlDataAdapter1.Update(DataSet_Items1)
            Catch ex As Exception
                MsgBox(ex.Message)
            End Try

            DataSet_Items1.AcceptChanges()


Each of those OName variables holds the information to plug in.  Every one of them works except for OSupplier.  Both of those message boxes return the correct value though, a string number - "1409" etc.  I've also tried changing OSupplier to val(OSupplier) with no difference in results.  Results are that it writes everything else to the database and that column has a null.  So I went into the database in sql server, turned off nulls for that column, and although the datarow item is set properly, it's obviously still trying to write a null, because I get an error that says (paraphrased)
Cannot write NULL to column SupplierCode - database requires a non-null value.

So somewhere between setting the number = to the variable (at which stage the datarow item holds the correct info) and writing all changes to the database, this is getting reverted to null.  Any ideas?

JP
gleznovAsked:
Who is Participating?
 
gdexterConnect With a Mentor Commented:
You may want to try catching the Adapters RowUpdated Event and look at what is going on during the update.

-----------------------------------------------------------------------------------------------------------------------
    Private Shared Sub SqlDataAdapter1_RowUpdated(ByVal sender As Object, ByVal e As  
                                                  System.Data.OleDb.OleDbRowUpdatedEventArgs) Handles SqlDataAdapter1.RowUpdated

        If (e.StatementType = StatementType.Insert) Then
           Dim val As String = e.Row("SupplierCode")
        End If

    End Sub
----------------------------------------------------------------------------------------------------------------------
What I think has happended is the Adapters CommandBuilder object is not up to date ie(does not contain the column) in the Insert/Update Command

How are you loading the DataSet?
If you are loading through the Wizard than that is probably your problem. You will need to re-configure your adapter.

If you are loading through code you may want  to do something like this:
In you load method...

     
        dim cb As New OleDbCommandBuilder(da)
        cb.QuotePrefix = "["
        cb.QuoteSuffix = "]"

        With SqlDataAdapter1            
            .FillSchema(DataSet_Items1 SchemaType.Source, tblName)
            .Fill(DataSet_Items1, tblName)
            .UpdateCommand = cb.GetUpdateCommand
            .InsertCommand = cb.GetInsertCommand
        End With



0
 
PreachDotNetCommented:
try   dr.Item("SupplierCode") = OSupplier.ToString
I read somewhere that .ToString'ing all the variables you pass into a database will take care of nulls etc.
0
 
gdexterCommented:
Is the 'SupplierCode' column a foreign key?
Is their any special Constraints on that column in SQL Server?
0
Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

 
gleznovAuthor Commented:
SupplierCode isn't a foreign key (although it really should be)  There's no constraint I can find on it.

Even using .ToString, I still get:

Cannot Insert the Value NULL into Column 'SupplierCode', table '...', column does not allow nulls.  INSERT fails.  The statement has been terminated.

Yet, the datarow item is fine.  How strange.

JP
0
 
PreachDotNetConnect With a Mentor Commented:
What gdexter says,
To elucidate, if the data adapter is a control you've dropped onto the design surface of a form then right click on it and click generate dataset.  Under properties have a look at the insert statement to find out if the field is there.  If not, run through Configure Data Adapter wizard until it recreates the select insert and delete queries.
Sometimes, if you generate the dataset without deleting the original one then it will append the new one as a new table and all your
dim dr as datarow
for each dr in ds.Tables(0).rows

becomes
dim dr as datarow
for each dr in ds.Tables(1).rows

in which case ds.Tables(0) is missing the column name but ds.Tables(1) has the correct columns.

Do a count on the number of tables in the dataset.  Shouldnt happen unless your a sloppy programmer like me that uses indexes instead of keys ;)
0
 
gleznovAuthor Commented:
Ahhhhhhhhhhhh....  Didn't reconfigure the dataadapter after adding the new column.  Sheeze (lol)  Thanks!

JP
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.