Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

Data won't write to database

Posted on 2004-10-04
6
Medium Priority
?
192 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
  • 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
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 
LVL 4

Accepted Solution

by:
gdexter earned 1000 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 1000 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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
Integration Management Part 2
In a question here at Experts Exchange (https://www.experts-exchange.com/questions/29062564/Adobe-acrobat-reader-DC.html), a member asked how to create a signature in Adobe Acrobat Reader DC (the free Reader product, not the paid, full Acrobat produ…

581 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