not-null-field issue - C# Discussion Boards

10-Sep-07 7:41

I have a fairly simple winform that has several textBoxes, one listbox and one combobox. I have the listBox bound to a field in a table (table1), when a user clicks on an item in the listBox the textBoxes are bound to that table so they display the other data columns for that record. The comboBox is bound to another table (table2) in the dataset that is merely a lookup table. The "Selected Value" of the combobox is bound to one of the colums in table1. This results in the appropriate value being selected in the combobox list. The comboBox has been changed to type "DropDownList" so the user cant type in the box, they can just select one of the pre-existing values in the comboBox. This all works great. The problem occurs when I try to change the selected value in the comboBox. The value does not update in the database when I call the update() method of the table adapter. If I change one of the textBoxes I call a function that ends the current edit ------this.BindingContext[ds_form_contacts, "table1"].EndCurrentEdit(); ------- then calls table1TableAdapter.Update() . This works fine, but if I change the comboBox, I call the same function and the value for the comboBox does not update. If I change the comboBox value then change one of the textBoxes the newly selected value for the comboBox will get updated in the database. I can post code if it helps. All of the binding was done in the designer in vs2005

thanks, -Kevin

Re: binding question

kboyette11-Sep-07 14:56

11-Sep-07 14:56

Oh C# gurus, surely someone has the answer to my burning binding question....

anyone????

anyone????

Bueller????

Please....

-Kevin

Re: binding question

kboyette12-Sep-07 10:29

12-Sep-07 10:29

I was able to get one step closer. I added a function to handle the SelectionChangeCommited event. In the function I added code to force the rowstate to be "Modified".
((DataRowView)this.tblgroupsBindingSource.Current).Row.BeginEdit();

Interestingly enough this works only if I select the same value in the comboBox twice in a row. The first time it does not change the rowstate.

Thoughts?

Re: binding question

kboyette12-Sep-07 10:38

12-Sep-07 10:38

Sorry, Scratch that. The BeginEdit() has no effect on the situation. I removed it and it still does work when you select the value from the combobox twice in a row.
I put a MessageBox in just after the endcurrentedit() to check the state of the row. The first time through it is Unchanged, but when I select it again it is Modified.

thoughts?

not-null-field issue

newbieAl10-Sep-07 7:25

10-Sep-07 7:25

I have a C# application that calls a MySQL procedure and writes data to the db. All of the fields get populated properly but for the ID field, which is my PK field. It populates the field with an empty string instead of the value. the ID field is a varchar field.

When I try a direct insert through the MySql Query Browser, it populates the field correctly, but when using the C# code/appl. the above issue occurs.

What could the issue be?

kboyette10-Sep-07 7:45

10-Sep-07 7:45

when you use the MySql QB what value does it use to populate the field? and I would have to see the code you are using to call the MySQL procedure.

-Kevin

newbieAl10-Sep-07 8:00

10-Sep-07 8:00

Here is the c# code that calles the stored procedure:

public void createUserProcedure(string uId, string lname, string fname, string email, string pswd, DateTime expdate, int secId, char usrStat, DateTime lgn)
{
MySqlCommand cmd = new MySqlCommand();

try
{
DB_Connection conn = new DB_Connection();
cmd.Connection = (MySqlConnection)conn.DBConnect();

cmd.CommandText = "createUser";
cmd.CommandType = CommandType.StoredProcedure;

cmd.Parameters.AddWithValue("USERID", uId);
cmd.Parameters["USERID"].Direction = ParameterDirection.Input;

cmd.Parameters.AddWithValue("USER_LAST_NAME", lname);
cmd.Parameters["USER_LAST_NAME"].Direction = ParameterDirection.Input;

cmd.Parameters.AddWithValue("USER_FIRST_NAME", fname);
cmd.Parameters["USER_FIRST_NAME"].Direction = ParameterDirection.Input;

cmd.Parameters.AddWithValue("USER_EMAIL", email);
cmd.Parameters["USER_EMAIL"].Direction = ParameterDirection.Input;

cmd.Parameters.AddWithValue("USER_PSWRD", pswd);
cmd.Parameters["USER_PSWRD"].Direction = ParameterDirection.Input;

cmd.Parameters.AddWithValue("USER_PASSWORD_EXPIRE_DATE", expdate);
cmd.Parameters["USER_PASSWORD_EXPIRE_DATE"].Direction = ParameterDirection.Input;

cmd.Parameters.AddWithValue("USER_SECURITY_LEVEL_ID", secId);
cmd.Parameters["USER_SECURITY_LEVEL_ID"].Direction = ParameterDirection.Input;

cmd.Parameters.AddWithValue("USER_STS", usrStat);
cmd.Parameters["USER_STS"].Direction = ParameterDirection.Input;

cmd.Parameters.AddWithValue("USER_UNSUCCESSFULL_LOGIN", lgn);
cmd.Parameters["USER_UNSUCCESSFULL_LOGIN"].Direction = ParameterDirection.Input;

cmd.ExecuteNonQuery();

}

This is how I call the procedure:
DateTime dt = new DateTime();
dt = DateTime.Parse("02/01/2007");

UserDAO test = new UserDAO();

test.createUserProcedure("test","TEST", "tst", "tst@net", null, dt, 1, 'A', dt);

Here is the stored procedure in mysql:
DELIMITER $$

DROP PROCEDURE IF EXISTS `the_vault`.`createUser` $$
CREATE DEFINER=`VAULT_DEV`@`%` PROCEDURE `createUser`(IN USERID varchar(20), IN USER_LAST_NAME VARCHAR(40), IN USER_FIRST_NAME VARCHAR(20), IN USER_EMAIL VARCHAR(50),IN USER_PSWRD VARCHAR(20), IN USER_PASSWORD_EXPIRE_DATE DATETIME, IN USER_SECURITY_LEVEL_ID INT, IN USER_STS VARCHAR(1), IN USER_UNSUCCESSFULL_LOGIN DATETIME)
BEGIN
INSERT INTO USERS VALUES(USER_ID, USER_LAST_NAME, USER_FIRST_NAME, USER_EMAIL, USER_PASSWORD, USER_PASSWORD_EXPIRE_DATE,
USER_SECURITY_LEVEL_ID, USER_STATUS, USER_UNSUCCESSFULL_LOGIN);
END $$

DELIMITER ;

Elina Blank10-Sep-07 8:46

Elina Blank

10-Sep-07 8:46

newbieAl wrote:
DROP PROCEDURE IF EXISTS `the_vault`.`createUser` $$
CREATE DEFINER=`VAULT_DEV`@`%` PROCEDURE `createUser`(IN USERID varchar(20), IN USER_LAST_NAME VARCHAR(40), IN USER_FIRST_NAME VARCHAR(20), IN USER_EMAIL VARCHAR(50),IN USER_PSWRD VARCHAR(20), IN USER_PASSWORD_EXPIRE_DATE DATETIME, IN USER_SECURITY_LEVEL_ID INT, IN USER_STS VARCHAR(1), IN USER_UNSUCCESSFULL_LOGIN DATETIME)
BEGIN
INSERT INTO USERS VALUES(USER_ID, USER_LAST_NAME, USER_FIRST_NAME, USER_EMAIL, USER_PASSWORD, USER_PASSWORD_EXPIRE_DATE,
USER_SECURITY_LEVEL_ID, USER_STATUS, USER_UNSUCCESSFULL_LOGIN);
END $$

Check the "Bold" values. They are different. Is this an intended difference, or is it a mistake?

Sincerely,
Elina

Life is great!!!
Enjoy every moment of it! Blush | :O

kboyette10-Sep-07 8:54

10-Sep-07 8:54

I'd say that's it. Everything else looks pretty good.

-Kevin

newbieAl10-Sep-07 9:18

10-Sep-07 9:18

Kevin, That was the issue,however why would MySQL still write this record to the table even though tere wasn't a valid USERID value?

kboyette10-Sep-07 9:53

10-Sep-07 9:53

Is the USER_ID field actually set as the PK in MySQL?
You say it is a varchar?
Is it set to Auto Increment?
Is set to not null?

in MySQL QB does the newly inserted value show up as empty or null?

-Kevin

newbieAl10-Sep-07 10:24

10-Sep-07 10:24

Yes, PK
Yes, varchar
No it is not set to auto increment
Yes, it is set to not null

in mysql qb it shows up as the value that I passed to the stored procedure

kboyette10-Sep-07 12:01

10-Sep-07 12:01

yes, now that you've fixed it, it should show up as the value you passed. Your question was why did it allow this in the first place? Your original post stated "It populates the field with an empty string instead of the value". That was my question was the value showing up in the db as any empty string or null, before you fixed it? There is a difference. I think your application was posting an empty string and not a null value, thus passing the db's test to not allow nulls. and since you do not have the field set as auto-incrementing an empty string would be a valid unique string. You probably could not run it twice in a row since that would violate the "unique" requirements of a pk. I guess in any event it is working now, eh?

-Kevin

newbieAl11-Sep-07 2:24

11-Sep-07 2:24

Yes, it is working. Your analysis is correct. It put an empty string it orig. and not the value.

Thanks for your help!

newbieAl10-Sep-07 9:07