sql DB code question

Question

I have an existing application that is used to connect to a sql 2005 DB table to validate user names and passwords on a website. I need to add another validation to this code to test for a specific condition. The code makes the sql connection and verifies their password already and in that same row there is another column that has a numeric value. What I need to do is write a statement that says IF [column Name] = 20; execute this code etc. The existing code doesnt really give me anything to go on for how to verify a value against the field value. If it helps the DB name is WEBSITEDB and the table name is CustomerMaster the column name is TestCode. If anyone can lend me some advice I would appreciate it greatly!

Jan Montano · Answer

Have you tried debugging the code? At what line does it break before going to the catch clause? This way we could easily identify what's causing the error. You could also modify the catch clause temporarily to this: catch (Exception e) { UsernameGreet.Text = e.Message; } Also... to be able to reference sqlRdr[TestCode]  TestCode should be included in your select query. I guess you forgot it =) sqlCmd.CommandText = SELECT [CustomerNumber] [Division] FROM AR1_CustomerMaster WHERE [CustomerNumber] = ' + sCn + ' AND [Division] = ' + sDn +'; should be: sqlCmd.CommandText = SELECT [CustomerNumber] [Division]  [TestCode] FROM AR1_CustomerMaster WHERE [CustomerNumber] = ' + sCn + ' AND [Division] = ' + sDn +';

Dan · Answer

Thanks for the code sinppet. I played with it some more and it doesnt seem to work but Im sure its my doing. I dont want the person to get access if their code is 20 so I wanted to make that the first identifier then execute the page redirect. I thought the code looked right but the result is it always returns Login Failed Case 3 to the text label. IF anyone can look over my code and make sure Im not totally off I would appreciate it. Thanks! Heres the code: try { String sCn = LoginNameTxt.Text.Replace(- ).Substring(2 7).Trim(); String sDn = LoginNameTxt.Text.Replace(- ).Substring(0 2).Trim(); sqlCmd.CommandText = SELECT [CustomerNumber] [Division] FROM AR1_CustomerMaster WHERE [CustomerNumber] = ' + sCn + ' AND [Division] = ' + sDn +'; sqlCmd.Connection = sqlConn; SqlDataReader sqlRdr = sqlCmd.ExecuteReader(); if (sqlRdr.Read() == true) //more records remain { if ((int)sqlRdr[TestCode] == 20) { // User validated set the session variable and redirect page if (sqlRdr.GetString(0).Trim().Equals(sCn)) { Session[Username] = LoginNameTxt.Text; Response.Redirect(Page01CustomerLoginSucceeded.aspx); } else { //UsernameGreet.Text = Login failed 1; } } else { UsernameGreet.Text = NPK Error; } } else // false no records read { UsernameGreet.Text = Login Failed Case 2; } } catch { UsernameGreet.Text = Login Failed Case 3; } sqlRdr.Close(); sqlConn.Close();

Jan Montano · Answer

String sCn = LoginNameTxt.Text.Replace(- ).Substring(2 7).Trim(); String sDn = LoginNameTxt.Text.Replace(- ).Substring(0 2).Trim(); // look up the login name in the db based on the login name entered in the text box sqlCmd.CommandText = SELECT [CustomerNumber] [Division] [TestCode] FROM CustomerMaster WHERE [CustomerNumber] = ' + sCn + ' AND [Division] = ' + sDn +'; sqlCmd.Connection = sqlConn; SqlDataReader sqlRdr = sqlCmd.ExecuteReader(); if (sqlRdr.Read()) { if ((int)sqlRdr[TestCode] = 20) { // insert code } } sqlRdr.Close();

Dan · Answer

This code is the sql connection to the table and the first 2 lines refer to parts/sections of the password that is entered in a textbox. String sCn = LoginNameTxt.Text.Replace(- ).Substring(2 7).Trim(); String sDn = LoginNameTxt.Text.Replace(- ).Substring(0 2).Trim(); // look up the login name in the db based on the login name entered in the text box sqlCmd.CommandText = SELECT [CustomerNumber] [Division] FROM CustomerMaster WHERE [CustomerNumber] = ' + sCn + ' AND [Division] = ' + sDn +'; sqlCmd.Connection = sqlConn; SqlDataReader sqlRdr = sqlCmd.ExecuteReader();

Jan Montano · Answer

Hi Dan Could you kindly post the current code (sql part) you have so that we could tweak it from there. Thanks. Regards Jan