Solved

Data won't write to database

Posted on 2004-10-04
6
174 Views
Last Modified: 2010-04-23
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
0
Comment
Question by:gleznov
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 2
  • 2
  • 2
6 Comments
 
LVL 6

Expert Comment

by:PreachDotNet
ID: 12217443
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
 
LVL 4

Expert Comment

by:gdexter
ID: 12217470
Is the 'SupplierCode' column a foreign key?
Is their any special Constraints on that column in SQL Server?
0
 

Author Comment

by:gleznov
ID: 12217689
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
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 4

Accepted Solution

by:
gdexter earned 250 total points
ID: 12217909
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
 
LVL 6

Assisted Solution

by:PreachDotNet
PreachDotNet earned 250 total points
ID: 12218050
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
 

Author Comment

by:gleznov
ID: 12218065
Ahhhhhhhhhhhh....  Didn't reconfigure the dataadapter after adding the new column.  Sheeze (lol)  Thanks!

JP
0

Featured Post

PeopleSoft Has Never Been Easier

PeopleSoft Adoption Made Smooth & Simple!

On-The-Job Training Is made Intuitive & Easy With WalkMe's On-Screen Guidance Tool.  Claim Your Free WalkMe Account Now

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I think the Typed DataTable and Typed DataSet are very good options when working with data, but I don't like auto-generated code. First, I create an Abstract Class for my DataTables Common Code.  This class Inherits from DataTable. Also, it can …
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.

730 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question