Solved

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

Posted on 2008-06-11
8
554 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
[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
  • 4
  • 3
8 Comments
 
LVL 16

Expert Comment

by:ororiole
ID: 21762846
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
ID: 21762862
What is your InsertCommand?
0
 

Author Comment

by:tjgquicken
ID: 21762986
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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 22

Expert Comment

by:prairiedog
ID: 21763081
Is userTable a strong typed table? How do you populate userTable?
0
 

Author Comment

by:tjgquicken
ID: 21763218
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
ID: 21763372
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
ID: 21763425
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
ID: 21778849
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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
ASP.NET Calendar Control 5 54
ASP.net Moving Visual Studio 2015 project to 2017 2 68
Visual C# Windows Project - Clone Form 4 39
Display info from DB to Label in asp.net 7 44
User art_snob (http://www.experts-exchange.com/M_6114203.html) encountered strange behavior of Android Web browser on his Mobile Web site. It took a while to find the true cause. It happens so, that the Android Web browser (at least up to OS ver. 2.…
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.
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
In an interesting question (https://www.experts-exchange.com/questions/29008360/) here at Experts Exchange, a member asked how to split a single image into multiple images. The primary usage for this is to place many photographs on a flatbed scanner…

739 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