Stupid SQL C# question...

[InsomniaCalls]

Hi there,

I've just started to learn C# so go easy on me (I'm used to VB.NET).
I'm creating a Logon window for an application which connects to the local SQL instance and changes a label on the form to state whether the connection was successful or not.

I know the connection is successful but the label isn't changing (I have a MessageBox set to show as a backup while I figure this out).

I think I'm using encapsulation correctly in that label1 is private on the Logon form, with a public string Label1 specifying the get and set properties.
I'm not sure the issue lies with this anyway, as I got the same issue if I set label1 to public.

The SQL code is specified in a separate class ConnectSQL:

class ConnectSQL
    {
        public void Connect(string myConnectionString)
        {
            // If the connection string is null, use a default.
            if (myConnectionString == "")
            {
                myConnectionString = "user id=*****;password=*****;server=localhost\\instancename;database=dbname";
            }
            SqlConnection myConnection = new SqlConnection(myConnectionString);
            SqlCommand myCommand = new SqlCommand();
            myCommand.Connection = myConnection;
            Logon logon = new Logon();
            
            try
            {
                myConnection.Open();

                if (myConnection.State == ConnectionState.Open)
                    logon.Label1 = "Connected";
                    MessageBox.Show("success");
            }
            catch (Exception)
            {
                logon.Label1 = "Connection Failed";
                MessageBox.Show("Failed");
            }
        }

I have a timer set to run on the load event of Logon which calls the ConnectSQL Connect method:

private void timer1_Tick(object sender, EventArgs e)
        {
            timer1.Enabled = false;
            ConnectSQL connectnow = new ConnectSQL();
            connectnow.Connect("connectionstringdetails");
        }

If I throw a button onto the Logon form and set the click event to change Label1.text it works fine, but nothing happens as part of the Connect method.

I appreciate this code is probably pretty poor, but I'm just starting out so if you can point me in the right direction I'd appreciate it!

Thanks

Si

 

[Ladforce]

dont you have to do something like logon.Label1.Text to change the text value?

 

[InsomniaCalls]

I don't think so. I've got this code in the Logon form:

public string Label1
        {
            get {return label1.Text;}
            set {label1.Text = value;}
        }

I think that should let me set the value without including .Text
I may well be wrong though (but if so I'd have expected the text to change when I declared label1 to be public and used label1.text = "Completed".

 

[Beepcake]

Should this:

if (myConnection.State == ConnectionState.Open)
logon.Label1 = "Connected";
MessageBox.Show("success");
Be this?

if (myConnection.State == ConnectionState.Open)
{
logon.Label1 = "Connected";
MessageBox.Show("success");
}

Otherwise the "success" messagebox will show whether .Status is Open or not.

It should throw an exception if it's not able to open though, so that's probably not the problem.

What happens when you breakpoint and trace through?

 

[InsomniaCalls]

Thanks for the replies.

I changed the If statement but unfortunately it didn't make a difference.

If I step through, the logon.Label1 shows as "Attempting to connect..." at the point it reaches the logon.Label1 = "Connected" line.
If I hover over it again after the next step it shows as "Connected" as I'd expect so it looks like it's being changed but possibly isn't refreshing on the form?

 

[Ladforce]

in your get and set do your label1's need to be Label1 with a capital L?

 

[InsomniaCalls]

No, the Label1 is a public string so doesn't have a text property.
The Label1 is used for getting and setting the text property of label1 from another class without making label1 itself public.

Thanks for your suggestions though, keep 'em coming!

 

[DireEmblem]

Give us whole program....

 

[InsomniaCalls]

Well all the relevant areas are:

Logon form:

{
    public partial class Logon : Form
    {
        public Logon()
        {
            InitializeComponent();
        }

        static void Main()
        {
            Application.EnableVisualStyles();
            Application.SetCompatibleTextRenderingDefault(false);
            Application.Run(new Logon());
        }

        private void Logon_Load(object sender, EventArgs e)
        {
            pictureBox1.Image = Properties.Resources.ProgressWarn;
            timer1.Enabled = true;
        }

        private void timer1_Tick(object sender, EventArgs e)
        {
            timer1.Enabled = false;
            ConnectSQL connectnow = new ConnectSQL();
            connectnow.Connect();
        }

        public string Label1
        {
            get { return label1.Text; }
            set { label1.Text = value; }
        }
        public string UserID
        {
            get { return UserIdTb.Text; }
        }

        private void CloseBtn_Click(object sender, EventArgs e)
        {
            Application.Exit();
        }

        private void LogonBtn_Click(object sender, EventArgs e)
        {
            ConnectSQL logonconnection = new ConnectSQL();
            if (logonconnection.IsValidUser(UserID))
            {
                this.Hide();
                GlobalClass.LoggedOnUser = UserID;
                TestApplication mainform = new TestApplication();
                mainform.Show();
            }
            else
            {
                MessageBox.Show("Invalid UserID specified.", "Logon", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }
        }
    }
}

ConnectSQL class:

{
    class ConnectSQL
    {
        Logon logon = new Logon();
        SqlConnection myConnection = new SqlConnection("user id=*****;password=*****;server=localhost\\instancename;database=dbname");

        public void Connect()
        {
            try
            {
                myConnection.Open();

                if (myConnection.State == ConnectionState.Open)
                {
                    string connectionstate = "Connected";
                    logon.Label1 = connectionstate;
                }
            }
            catch (Exception)
            {
                logon.Label1 = "Connection Failed";
            }
            finally
            {
                myConnection.Close();
            }
        }

        public bool IsValidUser(string username)
        {
            try
            {
                bool rv = false;
                SqlCommand mycommand = new SqlCommand();
                myConnection.Open();
                mycommand.Connection = myConnection;
                mycommand.CommandType = CommandType.StoredProcedure;
                mycommand.CommandText = "ValidateUserLogin";
                mycommand.Parameters.Add("@UserName", SqlDbType.VarChar, 30).Value = username;
                rv = Convert.ToBoolean(mycommand.ExecuteScalar());
                myConnection.Close();
                return rv;
            }
            catch (Exception)
            {
                throw;
            }


        }
    }


}

Any clues would be a great help.

 

[Spyhop]

The Login form already exists - it's the one calling the Connect method. So there's no reason to create a second instance where you write:

Logon logon = new Logon()

When you do that then work on 'logon', you're setting that instance of the Login form's label property just fine, but it doesn't matter, because that instance is never shown.

The layout of the code is a bit weird, but you could make Connect return a bool instead of void, then replace the try/catch block with:

// not exception proof
myConnection.Open();
return (myConnection.State == ConnectionState.Open)

Then put this in the Login form's timer method:

if (connectnow.Connect("connectionstringdetails"))
{
this.label1.Text = "abc"; // this. not essential
}
else
{
this.label1.Text = "xyz";
}

If you're using System.Timers.Timer, you will have to use the Control.Invoke method in order to update the label from the timer's thread, but I don't think that's a problem with System.Windows.Forms.Timer.

 

[InsomniaCalls]

Excellent - thanks very much!