i have stored procedure performs select statement in order return user data session state on login. far, stored procedure has returned empty values when called clicking login button. have tested stored procedure in sql mgmt studio running after hard-coding input username , achieved desired results. have placed breakpoints in visual studio , found while username being sent correctly textbox in input, parameters being returned empty "". leads me believe problem formatting input parameter, can't figure out is. appreciated.
here c# method:
public static user getuser(int? id, string username) { string selectbyid = "spselectuserbyid"; string selectbyusername = "spselectuserbyusername"; user u = new user(); string connstring = configurationmanager.connectionstrings["connectionstring"].connectionstring; using (sqlconnection conn = new sqlconnection(connstring)) { sqlcommand cmd = new sqlcommand(); cmd.commandtype = system.data.commandtype.storedprocedure; if (string.isnullorempty(id.tostring())) { cmd = new sqlcommand(selectbyusername, conn); cmd.parameters.add("@usernamein", sqldbtype.varchar).value = username.tostring(); } else if (string.isnullorempty(username)) { cmd = new sqlcommand(selectbyid, conn); cmd.parameters.add("@userid", sqldbtype.int).value = id; } sqlparameter username = new sqlparameter(); sqlparameter password = new sqlparameter(); sqlparameter firstname = new sqlparameter(); sqlparameter middleinitial = new sqlparameter(); sqlparameter lastname = new sqlparameter(); sqlparameter userstar = new sqlparameter(); sqlparameter userrank = new sqlparameter(); sqlparameter userrankcode = new sqlparameter(); sqlparameter assignment = new sqlparameter(); sqlparameter shift = new sqlparameter(); sqlparameter contactphone = new sqlparameter(); sqlparameter phonetype = new sqlparameter(); sqlparameter email = new sqlparameter(); sqlparameter assignmentid = new sqlparameter(); sqlparameter shiftid = new sqlparameter(); username.parametername = "@username"; password.parametername = "@password"; firstname.parametername = "@firstname"; middleinitial.parametername = "@middleinitial"; lastname.parametername = "@lastname"; userstar.parametername = "@userstar"; userrank.parametername = "@userrank"; userrankcode.parametername = "@userrankcode"; assignment.parametername = "@assignment"; shift.parametername = "@shift"; contactphone.parametername = "@contactphone"; phonetype.parametername = "@phonetype"; email.parametername = "@email"; assignmentid.parametername = "assignmentid"; shiftid.parametername = "shiftid"; username.sqldbtype = system.data.sqldbtype.varchar; password.sqldbtype = system.data.sqldbtype.varchar; firstname.sqldbtype = system.data.sqldbtype.varchar; middleinitial.sqldbtype = system.data.sqldbtype.varchar; lastname.sqldbtype = system.data.sqldbtype.varchar; userstar.sqldbtype = system.data.sqldbtype.int; userrank.sqldbtype = system.data.sqldbtype.varchar; userrankcode.sqldbtype = system.data.sqldbtype.varchar; assignment.sqldbtype = system.data.sqldbtype.varchar; shift.sqldbtype = system.data.sqldbtype.varchar; contactphone.sqldbtype = system.data.sqldbtype.varchar; phonetype.sqldbtype = system.data.sqldbtype.varchar; email.sqldbtype = system.data.sqldbtype.varchar; assignmentid.sqldbtype = system.data.sqldbtype.int; shiftid.sqldbtype = system.data.sqldbtype.int; username.size = 200; password.size = 200; firstname.size = 200; middleinitial.size = 200; lastname.size = 200; userstar.size = 200; userrank.size = 200; userrankcode.size = 200; userrankid.size = 200; assignment.size = 200; shift.size = 200; contactphone.size = 200; phonetype.size = 200; email.size = 200; assignmentid.size = 200; shiftid.size = 200; username.direction = system.data.parameterdirection.output; password.direction = system.data.parameterdirection.output; firstname.direction = system.data.parameterdirection.output; middleinitial.direction = system.data.parameterdirection.output; lastname.direction = system.data.parameterdirection.output; userstar.direction = system.data.parameterdirection.output; userrank.direction = system.data.parameterdirection.output; userrankcode.direction = system.data.parameterdirection.output; assignment.direction = system.data.parameterdirection.output; shift.direction = system.data.parameterdirection.output; contactphone.direction = system.data.parameterdirection.output; phonetype.direction = system.data.parameterdirection.output; email.direction = system.data.parameterdirection.output; assignmentid.direction = system.data.parameterdirection.output; shiftid.direction = system.data.parameterdirection.output; cmd.parameters.add(username); cmd.parameters.add(password); cmd.parameters.add(firstname); cmd.parameters.add(middleinitial); cmd.parameters.add(lastname); cmd.parameters.add(userstar); cmd.parameters.add(userrank); cmd.parameters.add(userrankcode); cmd.parameters.add(assignment); cmd.parameters.add(shift); cmd.parameters.add(contactphone); cmd.parameters.add(phonetype); cmd.parameters.add(email); cmd.parameters.add(assignmentid); cmd.parameters.add(shiftid); conn.open(); cmd.executenonquery(); u.username = username.value.tostring(); u.password = password.value.tostring(); u.firstname = firstname.value.tostring(); u.middleinitial = middleinitial.value.tostring(); u.lastname = lastname.value.tostring(); u.userstar = convert.toint16(userstar.value); u.userrank = userrank.value.tostring(); u.userrankcode = userrankcode.value.tostring(); u.assignment = assignment.value.tostring(); u.assignmentid = convert.toint16(assignmentid.value); u.shift = shift.value.tostring(); u.shiftid = convert.toint16(shiftid.value.tostring()); u.contactphone = contactphone.value.tostring(); u.phonetype = phonetype.value.tostring(); u.email = email.value.tostring(); return u; } } here stored procedure:
create procedure spselectuserbyusername @usernamein varchar(max) = null , @userid int = null output , @firstname varchar(max) = null output , @middleinitial varchar(max) = null output , @lastname varchar(max) = null output , @userstar int = null output , @userrank varchar(max) = null output , @userrankcode varchar(max) = null output , @userrankid int = null output , @assignment varchar(max) = null output , @assignmentid int = null output , @shift varchar(max) = null output , @shiftid int = null output , @contactphone varchar(max) = null output , @phonetype varchar(max) = null output , @email varchar(max) = null output begin select @userid = users.user_id , @firstname = users.first_name , @lastname = users.last_name , @middleinitial = users.middle_initial , @userstar = users.user_star , @userrank = concat(users.user_rank,' - ',ranks.title) , @userrankcode = users.user_rank , @userrankid = ranks.id , @assignmentid = users.assignment , @assignment = assignment.assignmentname , @shiftid = users.regular_shift , @shift = s.displayname , @contactphone = users.contact_phone , @phonetype = users.phone_type , @email = users.email dbo.users left join [shift] s on users.regular_shift = s.shiftid left join assignment on users.assignment = assignment.assignmentid left join ranks on users.user_rank = ranks.code users.username = @usernamein end
change procedure following, , use sqldataadapter result datatable:
create procedure spselectuserbyusername @usernamein varchar(max) = null , begin select users.user_id, users.first_name, users.last_name, users.middle_initial, users.user_star, concat(users.user_rank,' - ',ranks.title), users.user_rank, ranks.id, users.assignment, assignment.assignmentname, users.regular_shift, s.displayname, users.contact_phone, users.phone_type, users.email dbo.users left join [shift] s on users.regular_shift = s.shiftid left join assignment on users.assignment = assignment.assignmentid left join ranks on users.user_rank = ranks.code users.username = @usernamein end so can use adapter retrieve data this:
string connstring = configurationmanager.connectionstrings["connectionstring"].connectionstring; datatable resulttable = new datatable(); using (sqlconnection conn = new sqlconnection(connstring)) { using (sqlcommand cmd = new sqlcommand()) { cmd.commandtype = system.data.commandtype.storedprocedure; cmd.commandtext = ""; cmd.parameters.add("@usernamein", sqldbtype.int).value = txtusername.text; // or eve supplay username sqldataadapter sqladapter = new sqldataadapter(cmd); sqladapter.fill(resulttable); } } 
Comments
Post a Comment