Click here to Skip to main content
15,903,388 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have four table " Admin,Student,Parent,Teacher ",
I wrote a procedure to call a AdminLogin that is working fine below is the code.


SQL
CREATE PROCEDURE Login(@useName varchar(50),@pass varchar(50))
AS BEGIN
SELECT COUNT(*) FROM Admin WHERE name = @useName AND password = @pass
END


Actually i want to change the Admin as Student, Teacher ,Parent (roles) respectively.

So i try to change the procedure call as follows

SQL
<pre lang="sql">CREATE PROCEDURE Login(@useName varchar(50),@pass varchar(50),@user varchar(50))
AS BEGIN
SELECT COUNT(*) FROM @user WHERE name = @useName AND password = @pass
END


But i cant add this procedure , How can i attain this.


My basic need is.

Four users
1.Admin
2.Teacher
3.Student
4.Parent

I am getting this from the combobox.

UserName from textBox
Password from textBox

This is my c# button click event code


C#
<pre lang="cs">private void loginButton_Click(object sender, EventArgs e)
{
    username = userNameTextBox.Text;
    password = passwordTextBox.Text;
    role = roleSelectionComboBox.Text;
    if(username == "" || password == "" || role =="")
    {
        MessageBox.Show("Please fill all");
    }
    else
    {
        dbcon.Connection();
        query = "Login";
        com = new SqlCommand(query,dbcon.con);
        com.CommandType = CommandType.StoredProcedure;
        com.Parameters.AddWithValue("@useName", username);
        com.Parameters.AddWithValue("@pass", password);
        com.Parameters.AddWithValue("@user", role);
        int userCount = (Int32)com.ExecuteScalar();
        if(userCount == 1)
        {
            this.Hide();
            Dashboard AdminDashboard = new Dashboard(username);
            AdminDashboard.Show();
        }
        else
        {
            MessageBox.Show("count is 1");
        }
    }
}
Posted

Nope, you can't do that, pass a table name in as a parameter (well actually you could but then you'd have to create adhoc sql to do this).

What you would need is effectively a switch statement which queries against the correct table, but all this is very unpleasant. Big switches confound the query plan Sql Server uses.

Having to do that is also symptomatic of not having your data structured correctly. Almost certainly these four tables should be just one, with a column used in someway to discern between the types. Then you proc becomes very simple.
 
Share this answer
 
Comments
shajis001 23-May-14 5:20am    
Rob Philop Is creating a view solve this problem or not because i am planning to go with that approach.

I have created a view

<pre lang="SQL">CREATE VIEW CommonTable AS

SELECT 'Admin' AS userTable, * FROM Admin

UNION ALL

SELECT 'Student' AS userTable, * FROM Student

UNION ALL

SELECT 'Parent' AS userTable, * FROM Parent

UNION ALL

SELECT 'Teacher' AS userTable, * FROM Teacher
</pre>
Rob Philop Is creating a view solve this problem or not because i am planning to go with that approach.

I have created a view
SQL
CREATE VIEW CommonTable AS 

SELECT 'Admin' AS userTable, * FROM Admin

UNION ALL

SELECT 'Student' AS userTable, * FROM Student

UNION ALL

SELECT 'Parent' AS userTable, * FROM Parent

UNION ALL

SELECT 'Teacher' AS userTable, * FROM Teacher
 
Share this answer
 
v2

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900