Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2008-06-11
8
Medium Priority
?
556 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
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 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

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

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

IntroductionWhile developing web applications, a single page might contain many regions and each region might contain many number of controls with the capability to perform  postback. Many times you might need to perform some action on an ASP.NET po…
Problem Hi all,    While many today have fast Internet connection, there are many still who do not, or are connecting through devices with a slower connect, so light web pages and fast load times are still popular.    If your ASP.NET page …
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…
In this video, Percona Solutions Engineer Barrett Chambers discusses some of the basic syntax differences between MySQL and MongoDB. To learn more check out our webinar on MongoDB administration for MySQL DBA: https://www.percona.com/resources/we…
Suggested Courses

662 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