Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 559
  • Last Modified:

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

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
tjgquicken
Asked:
tjgquicken
  • 4
  • 3
1 Solution
 
ororioleCommented:
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
 
prairiedogCommented:
What is your InsertCommand?
0
 
tjgquickenAuthor Commented:
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's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

 
prairiedogCommented:
Is userTable a strong typed table? How do you populate userTable?
0
 
tjgquickenAuthor Commented:
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
 
prairiedogCommented:
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
 
tjgquickenAuthor Commented:
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
 
tjgquickenAuthor Commented:
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

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

  • 4
  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now