C# Question

Getting column information in SQL

I am somwhat new to SQL, so I am not sure I am going about this the right way.
I am trying to fetch data from my SQL Server database where I want to find out if

is 1/0, but it needs to search on a specific user and sort after the newest date as well.

What I am trying to do is something like this:

string connectionString = ".....";
SqlConnection cnn = new SqlConnection(connectionString);

SqlCommand checkForInOrOut = new SqlCommand("SELECT CHECKEDIN from timereg ORDER BY TIME DESC LIMIT 1 WHERE UNILOGIN = '" + publiclasses.unilogin + "'", cnn);

So my question, am I doing this right? And how do I fetch the data collected, if everything was handled correctly it should return 1 or 0. Should I use some sort of
? I am doing this in C#/WPF


Answer Source

There are some errors in your query. First WHERE goes before ORDER BY and LIMIT is an MySql keyword while you are using the Sql Server classes. So you should use TOP value instead.

int checkedIn = 0;
string cmdText = @"SELECT TOP 1 CHECKEDIN from timereg 
                   WHERE UNILOGIN = @unilogin
                   ORDER BY TIME DESC";
string connectionString = ".....";
using(SqlConnection cnn = new SqlConnection(connectionString))
using(SqlCommand checkForInOrOut = new SqlCommand(cmdText, cnn))
    checkForInOrOut.Parameters.Add("@unilogin", SqlDbType.NVarChar).Value = publiclasses.unilogin;

    // You return just one row and one column, 
    // so the best method to use is ExecuteScalar

    object result = checkForInOrOut.ExecuteScalar();

    // ExecuteScalar returns null if there is no match for your where condition
    if(result != null)
       MessageBox.Show("Login OK");

       // Now convert the result variable to the exact datatype 
       // expected for checkedin, here I suppose you want an integer
       checkedIN = Convert.ToInt32(result);
       MessageBox.Show("Login Failed");

Note how I have replaced your string concatenation with a proper use of parameters to avoid parsing problems and sql injection hacks. Finally every disposable object (connection in particular) should go inside a using block

Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download