How to Query Return Value on using Compact SQL Command?

Question

I using a compact database created on visual studio. just for a stand alone system with it's database intact already although i'm stuck here in using a select query that could retrieve a boolean if the user exist on the database and also then return it's ID and Username if the user entry exist. can i ask for help regarding on this one.. I am a student trying to learn c# on using compact database.

private void btnLogin_Click(object sender, EventArgs e)
    {
        try
        {
            if (!IsEmpty()) 
            {
                if (!IsLenght()) 
                {
                    using (SqlCeConnection con = new SqlCeConnection("Data Source=" +
                    System.IO.Path.Combine(Path.GetDirectoryName(System.Reflection.Assembly.GetEntryAssembly().Location), "INCdb.sdf"))) 
                    {
                        con.Open();
                        SqlCeCommand cmd = con.CreateCommand();
                        cmd.CommandText = "SELECT * FROM LoginTB Where username=@user1 AND password=@pass1";
                        cmd.Parameters.AddWithValue("@user1", UserTxt.Text.Trim());
                        cmd.Parameters.AddWithValue("@pass1", PassTxt.Text.Trim());
                        cmd.CommandType = CommandType.Text;

                        validlogin = (bool)cmd.ExecuteScalar();
                        con.Close();
                        MessageBox.Show(validlogin.ToString());


                        if (validlogin == true) 
                        {
                            // cmd. return value ID
                            // cmd. return value Username
                            //SysMain Mn = new SysMain();
                            //Mn.ShowDialog();
                            //this.Hide();
                        }
                    }                        
                }
            }
        }
        catch (Exception ex) 
        {
            gbf.msgBox(1, ex.Message.ToString(), "");
        }
    }

What value you try to return exactly on your database? Or just try to check it returns any rows or not to validate your login is successful or not? And do not store your passwords as a plain text. — Soner Gönül
– Soner Gönül, Commented Aug 12, 2015 at 13:43
yes sir i am trying to validate if the data exist on the database and then return the value of it's id and username and logID which is found in the database as one of it's columns. — Migz Snow
– Migz Snow, Commented Aug 12, 2015 at 13:45
ExecuteScalar will return the value stored in the first column of the first row of the first result set returned by your query. — Jodrell
– Jodrell, Commented Aug 12, 2015 at 13:50
ic.. so i shouldn't have use ExecuteScalar for this one sir Jodrell? i was hoping to have a return boolean value like "true" if the username and password exist in the database "false" if not. but if it was "true" if it pass the 2nd if condition then retrieve it's ID, username, LogID for the specific row found in the Table LoginTB. that i was hoping to be done on this code sir i think i have the wrong one then. — Migz Snow
– Migz Snow, Commented Aug 12, 2015 at 13:55

Jodrell · Accepted Answer · 2015-08-12 13:58:08Z

1

The code below is probably better, unless there is something special and unstated about the schema of LoginTB.

// ...
var validLogin = false;
using (SqlCeConnection con = new SqlCeConnection(
    "Data Source=" +
        System.IO.Path.Combine(
            Path.GetDirectoryName(
                System.Reflection.Assembly.GetEntryAssembly().Location),
    "INCdb.sdf"))) 
{
    con.Open();
    SqlCeCommand cmd = con.CreateCommand();
    cmd.CommandText =
            "SELECT COUNT(*) FROM LoginTB Where username=@user1 AND password=@pass1";
    cmd.Parameters.AddWithValue("@user1", UserTxt.Text.Trim());
    cmd.Parameters.AddWithValue("@pass1", PassTxt.Text.Trim());
    cmd.CommandType = CommandType.Text;
    validlogin = ((int)cmd.ExecuteScalar()) > 0;
}

MessageBox.Show(validlogin.ToString());

// ...

Note the use of COUNT

answered Aug 12, 2015 at 13:58

Jodrell

35.8k6 gold badges94 silver badges131 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Migz Snow Over a year ago

yes Sir Thanks but how about retrieving some of the column ID, Username, and LogID like using a foreach?

Jodrell Over a year ago

Well, then you'll need to call ExecuteReader. msdn.microsoft.com/en-us/library/9kcbe65k(v=vs.110).aspx

Migz Snow Over a year ago

Thank you much sir i've learn i need for this post.. thanks very much also for the link "ExecuteReader".

Collectives™ on Stack Overflow

How to Query Return Value on using Compact SQL Command?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related