Help in the login

I have a private method below

private void Login()
{
int UserId;
UsersDataModel usersDataModel = new UsersDataModel();
using (SqlConnection con = DBCS.GetConString())
{
string UserName = username.Value.Trim();
string Password = password.Value.Trim();
string SchoolCode = lblSchoolCode.Text.Trim();
string SchoolName = lblSchoolName.Text.Trim();
string LastLogin = lblLastLoginDate.Text.Trim();
//UserId = Convert.ToInt32(hfUserID.Value.Trim());
con.Open();
SqlCommand cmd = new SqlCommand("Validate_User", con);
cmd.CommandType = CommandType.StoredProcedure;
cmd.Parameters.AddWithValue("@Username", UserName);
cmd.Parameters.AddWithValue("@Password", Password);
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataTable dt = new DataTable();
da.Fill(dt);
RowCount = dt.Rows.Count;
for (int i = 0; i < RowCount; i++)
{
UserName = dt.Rows[i]["Username"].ToString();
Password = dt.Rows[i]["Password"].ToString();
SchoolCode = dt.Rows[i]["SchoolCode"].ToString();
SchoolName = dt.Rows[i]["SchoolName"].ToString();
LastLogin = dt.Rows[i]["LastLoginDate"].ToString();
UserId = Convert.ToInt32(dt.Rows[i][""]);
if (UserName == username.Value && Password == password.Value)
{
Session["NickName"] = UserName;
Session["SchoolCode"] = SchoolCode;
Session["SchoolName"] = SchoolName;
Session["LastLoginDate"] = LastLogin;
if (UserId == -1)
{
lblMessage.Text = "Username and/or password is incorrect.";
}
else if (UserId == -2)
{
lblMessage.Text = "Account has not been activated.";
}
else
{
WebMessageBox.Show("logged in");
}
}
else
{
lblMessage.Text = "Invalid User Name or Password! Please try again!";
}
}
}
}

But this function gives me this error

Column 'Username' does not belong to table .
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: System.ArgumentException: Column 'Username' does not belong to table .
Source Error:
Line 46: for (int i = 0; i < RowCount; i++)
Line 47: {
Line 48: UserName = dt.Rows[i]["Username"].ToString();
Line 49: Password = dt.Rows[i]["Password"].ToString();
Line 50: SchoolCode = dt.Rows[i]["SchoolCode"].ToString();

Below is my stored procedure:

USE [SchoolMIS]
GO
/****** Object: StoredProcedure [dbo].[Validate_User] Script Date: 12/11/2017 7:05:30 PM ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[Validate_User]
@Username NVARCHAR(20),
@Password NVARCHAR(20)
AS
BEGIN
SET NOCOUNT ON;
DECLARE @UserId INT,
@LastLoginDate DATETIME
SELECT @UserId = UserId,
@LastLoginDate = LastLoginDate
FROM Users
WHERE Username = @Username AND [Password] = @Password
IF @UserId IS NOT NULL
BEGIN
IF NOT EXISTS(SELECT UserId FROM UserActivation WHERE UserId = @UserId)
BEGIN
UPDATE Users
SET LastLoginDate = GETDATE()
WHERE UserId = @UserId
SELECT @UserId [UserId] -- User Valid
END
ELSE
BEGIN
SELECT -2 -- User not activated.
END
END
ELSE
BEGIN
SELECT -1 -- User invalid.
END
END

Hope someone can give me an assistance.

3 Replies

Know the answer? Post it — somebody with the same question will find it here.

Nilesh Shah8y ago
Accepted answer
you need to modify your SP

there is no select statement with the columns you specified in your code

do you really know what you want to do?
Jes Sie8y ago
Thanks Nilesh and amit for pointing out my mistake. already modified my SP and now it's working.
Amit Gupta8y ago
The error seems that username column doesnot exists on your table (i.e users table) which you called in your sp. Check that those table contains that table.