8 Replies - 1383 Views - Last Post: 03 October 2013 - 10:35 PM Rate Topic: -----

#1 brucey2k3   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 39
  • Joined: 03-October 13

cant get sql to detect admin user in c#

Posted 03 October 2013 - 06:19 PM

Im having trouble getting my program to detect admin users. I have created a login system, but when an admin logs in, it skips past the sql query and moves on to open a user screen, not an admin. Here is my code:
using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using System.Data.SqlClient;
using System.Data.SqlTypes;

namespace myLoginProject
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            SqlConnection connection = new SqlConnection(@"server=.\SQLEXPRESS; database=loginTest;Trusted_Connection=yes");
            connection.Open();
            string selection = "select * from Logins where Name = '" + userNameBox.Text + "' and Password = '" + passwordBox.Text + "' ";
            SqlCommand command = new SqlCommand(selection, connection);
            SqlDataAdapter da = new SqlDataAdapter(command);
            DataSet ds = new DataSet();
            da.Fill(ds);
            DataTable dt = ds.Tables[0];
        }

        private void registerButton_Click(object sender, EventArgs e)
        {
            adminAuthScreen aas = new adminAuthScreen();
            aas.Show();
        }
        private int myMethod(string user, string pass)
        {
            user.Trim();
            pass.Trim();
            SqlConnection connection = new SqlConnection(@"server=.\SQLEXPRESS; database=loginTest;Trusted_Connection=yes");
            connection.Open();
            string selection = "select * from Logins where Name = '"+user+"' and Password = '"+pass+"' ";
            SqlCommand command = new SqlCommand(selection, connection);
            if (command.ExecuteScalar() != null)
                return 1;
            else
                return 0;
                
        }

        private void loginButton_Click(object sender, EventArgs e)
        {
            if (myMethod(userNameBox.Text,passwordBox.Text)>0)
            {
                MessageBox.Show("Welcome back, "+userNameBox.Text);
                SqlConnection myConnection = new SqlConnection(@"server=.\SQLEXPRESS; database=loginTest;Trusted_Connection=yes");
                try
                {
                    myConnection.Open();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }
               string checkAdmin1 = "SELECT * FROM Logins WHERE User = '"+userNameBox.Text+"' AND User='Admin'";
            SqlCommand checkIfAdmin = new SqlCommand(checkAdmin1, myConnection);
            if (checkIfAdmin.ExecuteScalar() != null)
            {
               adminScreen admnscrn = new adminScreen();
                admnscrn.Show();
            }
            else
            {
                userScreen usrscrn = new userScreen();
                usrscrn.Show();
            }
            }
        }


        public SqlConnection connection { get; set; }
    }
    }

It seems like this bit is the problem (at least its the problem during debugging):

private void loginButton_Click(object sender, EventArgs e)
        {
            if (myMethod(userNameBox.Text,passwordBox.Text)>0)
            {
                MessageBox.Show("Welcome back, "+userNameBox.Text);
                SqlConnection myConnection = new SqlConnection(@"server=.\SQLEXPRESS; database=loginTest;Trusted_Connection=yes");
                try
                {
                    myConnection.Open();
                }
                catch (Exception ex)
                {
                    MessageBox.Show(ex.ToString());
                }
               string checkAdmin1 = "SELECT * FROM Logins WHERE User = '"+userNameBox.Text+"' AND User='Admin'";
            SqlCommand checkIfAdmin = new SqlCommand(checkAdmin1, myConnection);
            if (checkIfAdmin.ExecuteScalar() != null)
            {
               adminScreen admnscrn = new adminScreen();
                admnscrn.Show();
            }
            else
            {
                userScreen usrscrn = new userScreen();
                usrscrn.Show();
            }
            }
        }

Can anyone help me find out what the problem is???

Is This A Good Question/Topic? 0
  • +

Replies To: cant get sql to detect admin user in c#

#2 brucey2k3   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 39
  • Joined: 03-October 13

Re: cant get sql to detect admin user in c#

Posted 03 October 2013 - 06:25 PM

I dont know how to edit posts but its not where i put WHERE User = '"+userNameBox.Text+"' AND User='Admin, ive changed it and it still isnt working :(
Was This Post Helpful? 0
  • +
  • -

#3 Momerath   User is offline

  • D.I.C Lover
  • member icon

Reputation: 1021
  • View blog
  • Posts: 2,463
  • Joined: 04-October 09

Re: cant get sql to detect admin user in c#

Posted 03 October 2013 - 08:06 PM

Unless the users name is Admin, your SQL statement will never work as it requires the User column to be both their name and 'Admin' at the same time. If the users name must be 'Admin', then just check for that, don't go through a middle variable (which in this case is the User column).
Was This Post Helpful? 0
  • +
  • -

#4 brucey2k3   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 39
  • Joined: 03-October 13

Re: cant get sql to detect admin user in c#

Posted 03 October 2013 - 08:23 PM

Sorry, I cant have put it across clearly. My columns are Name, Password and User. They are username, password and user type. When the new user registers they are Admin or User. I need SQL to determine which it is, as the code directs to different windows depending (or its supposed to at least)

for example, if i type in Anthony as the username, dreamincode as the password and Admin as the user type, i want it to find my name, then see im an administrator and send me to the admin window

and as i said in the second post, I changed it to WHERE Name = '"+userNameBox.Text+"' AND User='Admin and it still isnt working
Was This Post Helpful? 0
  • +
  • -

#5 brucey2k3   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 39
  • Joined: 03-October 13

Re: cant get sql to detect admin user in c#

Posted 03 October 2013 - 08:29 PM

IVE GOT IT! The column user, when in design mode, was surrounded by []! I changed it to UserType and it works, SUCCESS! I dont understand why it had [] around it though...
Was This Post Helpful? 0
  • +
  • -

#6 Skydiver   User is offline

  • Code herder
  • member icon

Reputation: 7053
  • View blog
  • Posts: 23,975
  • Joined: 05-May 12

Re: cant get sql to detect admin user in c#

Posted 03 October 2013 - 09:33 PM

Use parameters when building SQL statements. See the tutorial on here: Parameterizing Your SQL Queries: The RIGHT Way To Query A Database.

You don't want this to happen:
Posted Image
Was This Post Helpful? 1
  • +
  • -

#7 Momerath   User is offline

  • D.I.C Lover
  • member icon

Reputation: 1021
  • View blog
  • Posts: 2,463
  • Joined: 04-October 09

Re: cant get sql to detect admin user in c#

Posted 03 October 2013 - 09:34 PM

'user' is a reserved word in SQL Server Transact-SQL so it needed to surround the column name with [] to indicate that it wasn't the reserved word. I should have seen that :)

List of reserved words here, just FYI

This post has been edited by Momerath: 03 October 2013 - 09:35 PM
Reason for edit:: Added link

Was This Post Helpful? 0
  • +
  • -

#8 brucey2k3   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 39
  • Joined: 03-October 13

Re: cant get sql to detect admin user in c#

Posted 03 October 2013 - 09:36 PM

ahhhhh, brilliant, thank you ! :)
Was This Post Helpful? 0
  • +
  • -

#9 jimzcoder   User is offline

  • D.I.C Regular

Reputation: 57
  • View blog
  • Posts: 351
  • Joined: 14-November 12

Re: cant get sql to detect admin user in c#

Posted 03 October 2013 - 10:35 PM

haha. i got hooked up with skydiver's posted image.
its real funny but the lesson behind it is really great and worth considering in any projects regarding db administration.

:punk:
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1