Hi my name is vishal,Member:
10248768. I was able to solve my problem/question on How to pass/bind result of variable from datareader execution of sql command of one form to another form which accepts and inserts that value to a table in sql server2008 through another form in c# windows forms with sql server 2008.
So first i created a separate class named:
Class1 which contains global variables which will accept values from other forms. Given below is
c# code of class(
Class1):
namespace Mini_Project
{
class Class1
{
public static int Manager;
public static int Man
{
get{return Manager;}
set{Manager=value;}
}
public static int User;
public static int Uan
{
get { return User; }
set { User = value; }
}
public static int Admin;
public static int Adn
{
get { return Admin; }
set { Admin = value; }
}
public static string MName = "";
public static string ManagerName
{
get { return MName; }
set { MName = value; }
}
public static string UName = "";
public static string UserName
{
get { return UName; }
set { UName = value; }
}
public static string AName = "";
public static string AdminName
{
get { return AName; }
set { AName = value; }
}
}
Given below is my
c# code of login form(
frmLogin):
namespace Mini_Project
{
public partial class frmLogin : Form
{
public int mid { get; set; }
public int uid { get; set; }
public frmLogin()
{
InitializeComponent();
}
private void frmLogin_Load(object sender, EventArgs e)
{
}
private bool ManagerUser(string username, string password)
{
bool success = false;
SqlConnection conn = new SqlConnection("Data Source=NPD-4\\SQLEXPRESS;Initial Catalog=Task;Integrated Security=true");
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd = new SqlCommand("Select manager_id,manager_first_name from [dbo].[ManagerDetail2] where username='"+txtUsername.Text+"' and password='"+txtPassword.Text+"' and LoginAttempts< 3", conn);
SqlDataReader rd = cmd.ExecuteReader();
while(rd.Read())
{
success=true;
Class1.Man = Convert.ToInt32(rd[0].ToString());
Class1.ManagerName = rd[1].ToString();
}
rd.Close();
conn.Close();
return success;
}
private bool ValidateUser(string username, string password)
{
bool success = false;
SqlConnection conn = new SqlConnection("Data Source=NPD-4\\SQLEXPRESS;Initial Catalog=Task;Integrated Security=true");
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd = new SqlCommand("Select * from [dbo].[UserDetail2] where username='"+txtUsername.Text+"' and password='"+txtPassword.Text+"' and LoginAttempts< 3", conn);
SqlDataReader rd = cmd.ExecuteReader();
while(rd.Read())
{
success=true;
Class1.Uan = Convert.ToInt32(rd[0].ToString());
Class1.UserName = rd[1].ToString();
}
rd.Close();
conn.Close();
return success;
}
private void btnLogin_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection("Data Source=NPD-4\\SQLEXPRESS;Initial Catalog=Task;Integrated Security=true");
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
if ((txtPassword.Text == "password") && (txtUsername.Text.ToLower() == "admin"))
{
Class1.Adn = 1;
MDIParent1 h = new MDIParent1();
h.Show();
this.Close();
}
else
{
string username = txtUsername.Text;
string password = txtPassword.Text;
bool validUser = ValidateUser(username, password);
bool validmanager = ManagerUser(username, password);
if (validUser)
{
string name = ("Select user_first_name,user_last_name,user_id from [dbo].[UserDetail2] where username='" + txtUsername.Text + "'");
cmd = new SqlCommand(name);
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
SqlDataReader dr = cmd.ExecuteReader();
while (dr.Read())
{
name = dr[0].ToString() + " " + dr[1].ToString()+System.Environment.NewLine+dr[2].ToString();
uid = Convert.ToInt32(dr[2].ToString());
uid = Class1.Uan;
}
dr.Close();
cmd = new SqlCommand("Update [dbo].[UserDetail2] set LoginAttempts=0 where username='" + txtUsername.Text + "'", conn);
cmd.ExecuteNonQuery();
Class1.Uansers = 1;
MDIParent1 m = new MDIParent1();
m.Show();
this.Close();
}
if (validmanager)
{
string manager = ("Select manager_first_name,manager_last_name,manager_id from [dbo].[ManagerDetail2] where username='" + txtUsername.Text + "'");
cmd = new SqlCommand(manager);
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
SqlDataReader fr = cmd.ExecuteReader();
while (fr.Read())
{
manager = fr[0].ToString() + " " + fr[1].ToString()+System.Environment.NewLine+fr[2].ToString();
mid = Convert.ToInt32(fr[2].ToString());
mid = Class1.Man;
}
fr.Close();
cmd = new SqlCommand("Update [dbo].[ManagerDetail2] set LoginAttempts=0 where username='" + txtUsername.Text + "'", conn);
cmd.ExecuteNonQuery();
Class1.Mansers = 1;
MDIParent1 g = new MDIParent1();
g.Show();
this.Close();
}
else
{
cmd = new SqlCommand("Update [dbo].[UserDetail2] set LoginAttempts=LoginAttempts+1 where username='" + txtUsername.Text + "'", conn);
cmd.ExecuteNonQuery();
cmd = new SqlCommand("Update [dbo].[ManagerDetail2] set LoginAttempts=LoginAttempts+1 where username='" + txtUsername.Text + "'", conn);
cmd.ExecuteNonQuery();
MessageBox.Show("Invalid user name or password. Please try tomorow ", "Task", MessageBoxButtons.OK, MessageBoxIcon.Warning);
txtUsername.Focus();
}
}
}
The above code works Fine!
As you can see i enter/login into application as a
admin using default username:
admin and default password:
password
So as a admin i create manager for tasks. Given below is my c# code of form(
frmManager) through which i create manager for tasks and also insert values into table:
ManagerDetail2 through
frmManager form:
namespace Mini_Project
{
public partial class frmManager : Form
{
public int bGenId = -1;
public frmManager()
{
InitializeComponent();
}
private void btnCreate_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection("Data Source=NPD-4\\SQLEXPRESS;Initial Catalog=Task;Integrated Security=true");
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd = new SqlCommand("Insert into [dbo].[ManagerDetail2](manager_first_name,manager_last_name,manager_dob,manager_sex,email,username,password,status,created_by,LoginAttempts,row_upd_date)" + "Values(@manager_first_name,@manager_last_name,@manager_dob,@manager_sex,@email,@username,@password,@status,@created_by,@LoginAttempts,GetDate()); Select @autoGenId=SCOPE_IDENTITY();", conn);
cmd.Parameters.AddWithValue("@manager_first_name", txtFName.Text);
cmd.Parameters.AddWithValue("@manager_last_name", txtLName.Text);
cmd.Parameters.AddWithValue("@manager_dob", dtDOB.Value);
if (cboSex.SelectedIndex == 0)
{
cmd.Parameters.AddWithValue("@manager_sex", "Male");
}
else if (cboSex.SelectedIndex == 1)
{
cmd.Parameters.AddWithValue("@manager_sex", "Female");
}
else if (cboSex.SelectedIndex == 2)
{
cmd.Parameters.AddWithValue("@manager_sex", "Transgender");
}
cmd.Parameters.AddWithValue("@email", txtEmailID.Text);
cmd.Parameters.AddWithValue("@username", txtUsername.Text);
cmd.Parameters.AddWithValue("@password", txtPassword.Text);
cmd.Parameters.AddWithValue("@status", 1);
cmd.Parameters.AddWithValue("@Created_by", 1);
cmd.Parameters.AddWithValue("@LoginAttempts", 0);
cmd.Parameters.Add("@autoGenId", SqlDbType.Int).Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
bGenId = Convert.ToInt32(cmd.Parameters["@autoGenId"].Value);
Class1.Mansers = 1;
cmd = new SqlCommand("Update [dbo].[ManagerDetail2] set LoginAttempts=0 where username='" + txtUsername.Text + "'", conn);
cmd.ExecuteNonQuery();
((MDIParent1)this.MdiParent).updateUserActivities(bGenId, 2, txtFName.Text.ToString() + "Manager detail was added successfully");
MessageBox.Show("Manager Detail was added successfully", "Task", MessageBoxButtons.OK, MessageBoxIcon.Information);
conn.Close();
this.Close();
}
The above code works Fine!
So using
username and
password from table:
ManagerDetail2 i enter/login into application as a
manager who creates new users to application/that particular created user will come under to created manager. Given below is my c# code of form(
frmUser) through which i create new users to application who will come under by their created manager and also insert values into table:
UserDetail2 in sql server2008 from c# windows forms:
namespace Mini_Project
{
public partial class frmUser : Form
{
public int autoGenId = -1;
public frmUser()
{
InitializeComponent();
}
private void btnCreate_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection("Data Source=NPD-4\\SQLEXPRESS;Initial Catalog=Task;Integrated Security=true");
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd = new SqlCommand("Insert into [dbo].[UserDetail2](user_first_name,user_last_name,user_dob,user_sex,email,username,password,status,row_upd_date,created_by,LoginAttempts)" + "Values(@user_first_name,@user_last_name,@user_dob,@user_sex,@email,@username,@password,@status,GetDate(),@created_by,@LoginAttempts); Select @autoGenId=SCOPE_IDENTITY();", conn);
cmd.Parameters.AddWithValue("@user_first_name", txtFName.Text);
cmd.Parameters.AddWithValue("@user_last_name", txtLName.Text);
cmd.Parameters.AddWithValue("@user_dob", dtDOB.Value);
if (cboSex.SelectedIndex == 0)
{
cmd.Parameters.AddWithValue("@user_sex", "Male");
}
else if (cboSex.SelectedIndex == 1)
{
cmd.Parameters.AddWithValue("@user_sex", "Female");
}
else if (cboSex.SelectedIndex == 2)
{
cmd.Parameters.AddWithValue("@user_sex", "Transgender");
}
cmd.Parameters.AddWithValue("@email", txtEmailID.Text);
cmd.Parameters.AddWithValue("@username", txtUsername.Text);
cmd.Parameters.AddWithValue("@password", txtPassword.Text);
cmd.Parameters.AddWithValue("@status", 1);
cmd.Parameters.AddWithValue("@created_by",Class1.Man);
cmd.Parameters.AddWithValue("@LoginAttempts", 0);
cmd.Parameters.Add("@autoGenId", SqlDbType.Int).Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
autoGenId = Convert.ToInt32(cmd.Parameters["@autoGenId"].Value);
cmd = new SqlCommand("Update [dbo].[UserDetail2] set LoginAttempts=0 where username='" + txtUsername.Text + "'", conn);
cmd.ExecuteNonQuery();
((MDIParent1)this.MdiParent).updateUserActivities(autoGenId, 1, txtFName.Text + "User detail was added successfully");
MessageBox.Show("User Detail was added successfully", "Task", MessageBoxButtons.OK, MessageBoxIcon.Information);
conn.Close();
this.Close();
}
The above code works Fine just the way i wanted!
As you can see in below c# code of login form(
frmLogin):
private bool ManagerUser(string username, string password)
{
bool success = false;
SqlConnection conn = new SqlConnection("Data Source=NPD-4\\SQLEXPRESS;Initial Catalog=Task;Integrated Security=true");
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd = new SqlCommand("Select manager_id,manager_first_name from [dbo].[ManagerDetail2] where username='"+txtUsername.Text+"' and password='"+txtPassword.Text+"' and LoginAttempts< 3", conn);
SqlDataReader rd = cmd.ExecuteReader();
while(rd.Read())
{
success=true;
Class1.Man = Convert.ToInt32(rd[0].ToString());
Class1.ManagerName = rd[1].ToString();
}
rd.Close();
conn.Close();
return success;
}
private void btnLogin_Click(object sender, EventArgs e)
{
SqlConnection conn = new SqlConnection("Data Source=NPD-4\\SQLEXPRESS;Initial Catalog=Task;Integrated Security=true");
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
string username = txtUsername.Text;
string password = txtPassword.Text;
bool validmanager = ManagerUser(username, password);
if (validmanager)
{
string manager = ("Select manager_first_name,manager_last_name,manager_id from [dbo].[ManagerDetail2] where username='" + txtUsername.Text + "'");
cmd = new SqlCommand(manager);
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
SqlDataReader fr = cmd.ExecuteReader();
while (fr.Read())
{
manager = fr[0].ToString() + " " + fr[1].ToString()+System.Environment.NewLine+fr[2].ToString();
mid = Convert.ToInt32(fr[2].ToString());
mid = Class1.Man;
}
fr.Close();
cmd = new SqlCommand("Update [dbo].[ManagerDetail2] set LoginAttempts=0 where username='" + txtUsername.Text + "'", conn);
cmd.ExecuteNonQuery();
Class1.Mansers = 1;
MDIParent1 g = new MDIParent1();
g.Show();
this.Close();
}
So as you can see i get value/
manager_id of currently logged in manager of application through sql select query in datareader execution of sql command,then i pass the currently logged in manager,his
manager_id to a variable named:
Man of data-type:
Int in class named:
Class1.
Then i pass the same value of
Man from
Class1 to value of field:
created_by in table:
UserDetail2 through c# code of form(
frmUser):
SqlConnection conn = new SqlConnection("Data Source=NPD-4\\SQLEXPRESS;Initial Catalog=Task;Integrated Security=true");
if (conn.State != ConnectionState.Open)
{
conn.Open();
}
SqlCommand cmd = new SqlCommand();
cmd.Connection = conn;
cmd.CommandType = CommandType.Text;
cmd = new SqlCommand("Insert into [dbo].[UserDetail2](user_first_name,user_last_name,user_dob,user_sex,email,username,password,status,row_upd_date,created_by,LoginAttempts)" + "Values(@user_first_name,@user_last_name,@user_dob,@user_sex,@email,@username,@password,@status,GetDate(),@created_by,@LoginAttempts); Select @autoGenId=SCOPE_IDENTITY();", conn);
cmd.Parameters.AddWithValue("@user_first_name", txtFName.Text);
cmd.Parameters.AddWithValue("@user_last_name", txtLName.Text);
cmd.Parameters.AddWithValue("@user_dob", dtDOB.Value);
if (cboSex.SelectedIndex == 0)
{
cmd.Parameters.AddWithValue("@user_sex", "Male");
}
else if (cboSex.SelectedIndex == 1)
{
cmd.Parameters.AddWithValue("@user_sex", "Female");
}
else if (cboSex.SelectedIndex == 2)
{
cmd.Parameters.AddWithValue("@user_sex", "Transgender");
}
cmd.Parameters.AddWithValue("@email", txtEmailID.Text);
cmd.Parameters.AddWithValue("@username", txtUsername.Text);
cmd.Parameters.AddWithValue("@password", txtPassword.Text);
cmd.Parameters.AddWithValue("@status", 1);
cmd.Parameters.AddWithValue("@created_by",Class1.Man);
cmd.Parameters.AddWithValue("@LoginAttempts", 0);
cmd.Parameters.Add("@autoGenId", SqlDbType.Int).Direction = ParameterDirection.Output;
cmd.ExecuteNonQuery();
autoGenId = Convert.ToInt32(cmd.Parameters["@autoGenId"].Value);
cmd.ExecuteNonQuery();
I want to thank all the members of this forum who has tried to help me/also to members of this forum who had given their suggestions/solutions in terms of solving my problem for their time and patience! GOD Bless!
Submit an article or tip