Solved

Why is my app trying to insert null data into my db?

Posted on 2008-06-11
8
548 Views
Last Modified: 2011-10-19
I'm getting an error message from my C#/ASP.NET app that's telling me that it can't insert null data into a column in the database that doesn't accept nulls. Fair enough. Except my problem is that I can't figure out why it's trying to insert a null value in the first place, since when I query the data that (I think) I'm inserting, it's not null. The code is below. Here's the application trace:

userTable != null
number of rows in userTable = 1
client_id = 100
update user record failed with message: Cannot insert the value NULL into column 'client_id', table 'equickbooks.dbo.Users'; column does not allow nulls. INSERT fails.
The statement has been terminated.
Stack trace:   at System.Data.Common.DbDataAdapter.UpdatedRowStatusErrors(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
  at System.Data.Common.DbDataAdapter.UpdatedRowStatus(RowUpdatedEventArgs rowUpdatedEvent, BatchCommandInfo[] batchCommands, Int32 commandCount)
  at System.Data.Common.DbDataAdapter.Update(DataRow[] dataRows, DataTableMapping tableMapping)
  at System.Data.Common.DbDataAdapter.UpdateFromDataTable(DataTable dataTable, DataTableMapping tableMapping)
  at System.Data.Common.DbDataAdapter.Update(DataTable dataTable)
  at KJI.Components.UsersDB.UpdateUserRecord(DataTable userTable, DataTable rolesTable, DataTable permsTable)
Exception type: System.Data.SqlClient.SqlException

Thanks.
myConnection = new SqlConnection(ConfigurationSettings.AppSettings["connectionString"]);

myConnection.Open();

txn = myConnection.BeginTransaction();

SqlDataAdapter myDA = new SqlDataAdapter( "select * from users where id=-1", myConnection );

myDA.MissingMappingAction = MissingMappingAction.Ignore;

myDA.SelectCommand.Transaction = txn;

SqlCommandBuilder myCB = new SqlCommandBuilder( myDA );
 

if (userTable != null) 

	{

	HttpContext.Current.Trace.Write("userTable != null");

         HttpContext.Current.Trace.Write("number of rows in userTable = " + userTable.Rows.Count);

	DataRowState state = userTable.Rows[0].RowState;

					HttpContext.Current.Trace.Write("UsersDB.UpdateUserRecord", "client_id = " + userTable.Rows[0]["client_id"]);

         HttpContext.Current.Trace.Write("UsersDB.UpdateUserRecord", "id = " + userTable.Rows[0]["id"]);

	myDA.Update( userTable );

	HttpContext.Current.Trace.Warn("myDA.Update( userTable ) successful"); // not executed

Open in new window

0
Comment
Question by:tjgquicken
  • 4
  • 3
8 Comments
 
LVL 16

Expert Comment

by:ororiole
Comment Utility
Check your table definition. client_id is probably set to IDENTITY so it will assign the id itself upon insert. You cannot set it yourself, and should just set the other values when you insert. You can retrieve the identity after it is set if you wish.
0
 
LVL 22

Expert Comment

by:prairiedog
Comment Utility
What is your InsertCommand?
0
 

Author Comment

by:tjgquicken
Comment Utility
client_id isn't one of those columns that's filled automatically with the next number in a series.

I don't seem to have an InsertCommand.
0
 
LVL 22

Expert Comment

by:prairiedog
Comment Utility
Is userTable a strong typed table? How do you populate userTable?
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.

 

Author Comment

by:tjgquicken
Comment Utility
I have this database schema in a file called IFData.xsd. I ran Microsoft's xsd.exe and got a class file from it called IFData.cs. I uploaded a picture of the xsd schema.

It's populated this way: I have an class EditUserInfo with an IFData member object and an IFData.UsersRow (automatically defined by xsd.exe) object. I instantiate EditUserInfo, then the values in the UsersRow object are either hard coded or taken from a web form, i.e.:
usersRow.client_id = 100;
usersRow.first_name = firstname_field.Text.Trim();
usersRow.last_name = lastname_field.Text.Trim();
usersRow.date_joined = DateTime.Now;
etc.
then I call...
UsersDB.UpdateUserRecord(editUserInfo.ifData.Users, editUserInfo.ifData.UserRoles, null);   // which calls...
 

static internal bool UpdateUserRecord( DataTable userTable, DataTable rolesTable, DataTable permsTable )

{

	HttpContext.Current.Trace.Write("UsersDB", "entering UpdateUserRecord");

	SqlTransaction txn = null;

	SqlConnection myConnection = null;

	try 

	{

		myConnection = new SqlConnection(ConfigurationSettings.AppSettings["connectionString"]);

		myConnection.Open();

		txn = myConnection.BeginTransaction();

		SqlDataAdapter myDA = new SqlDataAdapter( "select * from users where id=-1", myConnection );
 

// ..continued above

Open in new window

untitled.GIF
0
 
LVL 22

Expert Comment

by:prairiedog
Comment Utility
This line SqlCommandBuilder myCB = new SqlCommandBuilder( myDA ) should generate the InsertCommand for you. I would set a break point here to see what the InsertCommand looks like.
0
 

Author Comment

by:tjgquicken
Comment Utility
It looks like the new SqlCommandBuilder(myDA) line isn't generating an insert command.
HttpContext.Current.Trace.Write("UsersDB.UpdateUserRecord", "InsertCommand = " +
                    myDA.InsertCommand.ToString());
causes the following:
update user record failed with message: Object reference not set to an instance of an object.
Stack trace:   at KJI.Components.UsersDB.UpdateUserRecord(DataTable userTable, DataTable rolesTable, DataTable permsTable)
Exception type: System.NullReferenceException
0
 

Accepted Solution

by:
tjgquicken earned 0 total points
Comment Utility
I don't know if changing the InsertCommand (or writing one manually) would've helped. I re-compiled my IFData : DataSet class using the xsd.exe tool, and that fixed it.
0

Featured Post

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

Today is the age of broadband.  More and more people are going this route determined to experience the web and it’s multitude of services as quickly and painlessly as possible. Coupled with the move to broadband, people are experiencing the web via …
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.
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
This video explains how to create simple products associated to Magento configurable product and offers fast way of their generation with Store Manager for Magento tool.

771 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

15 Experts available now in Live!

Get 1:1 Help Now