Solved

Data won't write to database

Posted on 2004-10-04
6
164 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
What Security Threats Are You Missing?

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
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

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Article by: Kraeven
Introduction Remote Share is a simple remote sharing tool, enabling you to see, add and remove remote or local shares. The application is written in VB.NET targeting the .NET framework 2.0. The source code and the compiled programs have been in…
Microsoft Reports are based on a report definition, which is an XML file that describes data and layout for the report, with a different extension. You can create a client-side report definition language (*.rdlc) file with Visual Studio, and build g…
In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

706 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

Need Help in Real-Time?

Connect with top rated Experts

18 Experts available now in Live!

Get 1:1 Help Now