Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

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

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

 
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

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say 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

Entity Framework is a powerful tool to help you interact with the DataBase but still doesn't help much when we have a Stored Procedure that returns more than one resultset. The solution takes some of out-of-the-box thinking; read on!
This article aims to explain the working of CircularLogArchiver. This tool was designed to solve the buildup of log file in cases where systems do not support circular logging or where circular logging is not enabled
This Micro Tutorial will teach you how to add a cinematic look to any film or video out there. There are very few simple steps that you will follow to do so. This will be demonstrated using Adobe Premiere Pro CS6.
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an anti-spam), the admin…

971 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