2. ASPRunner.NET General questions
  3. [SOLVED]  How do I apply this to one record?
This topic is locked
[SOLVED]

 How do I apply this to one record?

 		4/29/2016 2:32:01 PM
ASPRunner.NET General questions
A
 Arkie author

I have an event which fires after a Successful Registration and simply copies the ID field to another field for another purpose. Here's the event:
string sql = "update registration set regfield1 = regid";

CommonFunctions.db_exec(sql, null);
This works fine, except it's too fine. It acts on EACH record in the database. As the database grows larger, this may become an unnecessary, cpu consuming task. I only need it to act on the latest registration.
I've tried different syntax variations of the same sql statement but they all the same result.

I considered a "where.." clause but since it's a new registrant, I'm at a loss of what to use for that filter.
MS Access database
Ideas? Suggestions? TIA... ~Joe
T
 		Tim 4/29/2016

Maybe:
string sql = "update registration set regfield1 = regid where regid = " + userdata["regid"] ;

CommonFunctions.db_exec(sql, null);
or, assuming you have a username field that the user fills out during registration:
string sql = "update registration set regfield1 = regid where username = '" + userdata["username"] + "'";

CommonFunctions.db_exec(sql, null);
A
 		Arkie author 4/29/2016



Maybe:
string sql = "update registration set regfield1 = regid where regid = " + userdata["regid"] ;

CommonFunctions.db_exec(sql, null);
or, assuming you have a username field that the user fills out during registration:
string sql = "update registration set regfield1 = regid where username = '" + userdata["username"] + "'";

CommonFunctions.db_exec(sql, null);


Nice try but ...Syntax error (missing operator) in query expression 'regid =' is the error message I get.
I'm not sure that this will even work. I think it still has the same problem as my original solution in that it can't tell which record to use. I want to use the auto-numbered field since it'll always be unique.

Thanks... ~Joe
admin 4/29/2016

Joe,
did you try the second suggestion by Tim?

string sql = "update registration set regfield1 = regid where username = '" + userdata["username"] + "'";

CommonFunctions.db_exec(sql, null);
jadachDevClub member 4/29/2016

You can also do it after successful login
dynamic tblregistration = GlobalVars.dal.Table("registration");

tblregistration.Param["Username"] = XSession.Session["UserID"];

tblregistration.Value["regfield1"] = data["regid"];

tblregistration.Update();
A
 		Arkie author 5/2/2016



Joe,
did you try the second suggestion by Tim?

string sql = "update registration set regfield1 = regid where username = '" + userdata["username"] + "'";

CommonFunctions.db_exec(sql, null);



I don't recall if I even tried this or not, but the usernames are subject to change while the auto-numbered IDs are not. Maybe... if I change a bunch of other code??
admin 5/2/2016

Tim's suggestion is exactly what you need. Username cannot change between the moment user registered and AfterSuccessfulRegistration event fires. You are overthinking it.
A
 		Arkie author 5/2/2016



You can also do it after successful login
dynamic tblregistration = GlobalVars.dal.Table("registration");

tblregistration.Param["Username"] = XSession.Session["UserID"];

tblregistration.Value["regfield1"] = data["regid"];

tblregistration.Update();


Thanks Jerry... <img src='https://asprunner.com/forums/file.php?topicimage=1&fieldname=reply&id=79269&image=1&table=forumreplies' class='bbc_emoticon' alt=':)' />

I changed the 2nd line to read:

tblregistration.Param["regid"] = data["regid"];
and the code works as advertised. I'm unclear why the original line didn't work, but the db did not update.
~Joe