Insert to database

[muzikhera]

i have two related table name employee and userloginlogs. In my program user must first log username and password located at employee table. If the user already logged in i want to get the employeeID at employee table then save it to userloginlogs table plus the login and logout date of the user.

Take a look at my login button code.

private void btnLogin_Click(object sender, EventArgs e)
        {
            SqlConnection Connect = new SqlConnection();
            Connect.ConnectionString = @"Data Source=BURBANK\SQLEXPRESS;" + "Initial Catalog= ERPdb;" + "Persist Security Info=True;" + "User ID=antonette;" + "Password=ilovemis";
            Connect.Open();
            SqlCommand cmd = new SqlCommand("SELECT ISNULL(username, '') AS username, ISNULL(password, '') AS password FROM employee WHERE username='" + txtUserName.Text + "' and password='" + txtPassword.Text + "'", Connect);

            SqlDataReader dr = cmd.ExecuteReader();

            string userText = txtUserName.Text;
            string passText = txtPassword.Text;

            if (dr.Read())
            {
                frmMain main = new frmMain();
                this.Hide();
                main.Show();
            }
            else
                MessageBox.Show("Invalid UserName or Password.");

            {
                dr.Close();
                Connect.Close();
            }
        }

Can someone help me.

Thanks and regards to daniweb members/user.

[mshauny]

You could do the second insert statement inside the while loop. where you said

while(rd.Read())

because you know know from that statement the user login was successful. then you could say something like
SELECT userid FROM employee WHERE username = [his/her username]

then you will have the employeeid.

then you can test in against null or if you are sure that it will always return the correct one, just run it again then logs table as

INSERT INTO tableName(field1,filed2,fieldN) VALUES(employeeID,field2Val,fieldNval)

as for the timing, there are many approaches to take but the easier one would be to use the .NET DateTime class.

Hope it helps you

[muzikhera]

I tried to add this line of code but still get error

Sql Connection conn = null;

conn = new SqlConnection("@"Data Source=BURBANK\SQLEXPRESS;" + "Initial Catalog= ERPdb;" + "Persist Security Info=True;" + "User ID=antonette;" + "Password=ilovemis");

conn.Open();

String strEmployeeID = "SELECT employeeID FROM employee WHERE username = 'antonette'";

SqlCommand command = new SqlCommand(strEmployeeID, Connect);

String returnValue = (string)command.ExecuteScalar();

conn.Close();

return reutrnValue;

Can someone help me fix it?

Thanks and regards.

[Cort3z]

//code block

[Cort3z]

One error i see is that you have some issues with your: " - markings. I am not sure what type of sql you use, or even if there are any major differences, but I am used to mySQL. In mySQL you could just write:

"SELECT userID FROM employeeDB WHERE username='"+usernameInput+"' AND password='"+passwordInput+"';"

Then what you do is that you store that id as a int or something (if it returns something other than null) and then you say:

"INSERT INTO userlogDB (userID, date) VALUES ('"+storedIdInt+"', curdate());"

Something like that

[muzikhera]

im using sqlserver2005