[maxbre]

Hi.

I have done a register form that register an account to a database, but to be available to use this on other computers I've decided to make a "settings form" there you can configure your SQL settings.



I want to take the values from the textBoxes in form2 and use it in form1.

something like this

Data Source='" + instanceName + "'; Initial Catalog='" + database + "'; User ID='" + User + "'; Password='" + Pass + "'; Integrated Security=TRUE");



Here is the code for Form1.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

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

        private void button1_Click(object sender, EventArgs e)
        {
            if (textFirstName.Text == "settings")
            {
                Form2 form2 = new Form2();

                form2.ShowDialog();
               
            }

            if (textFirstName.Text == "settings" == false)
            {
                SqlConnection myConnection = new SqlConnection("Data Source=Max-PC\\SQLExpress; Initial Catalog=Account; User ID=sa; Password=Windows; Integrated Security=TRUE");

                SqlDataAdapter da = new SqlDataAdapter();
                da.InsertCommand = new SqlCommand("INSERT INTO tUser (sUserID, sUserPW, sUserName) VALUES ('" + textFirstName.Text + "','" + textLastName.Text + "','" + textFirstName.Text + "')", myConnection);
                //da.InsertCommand.Parameters.Add("@FIRSTNAME", SqlDbType.VarChar).Value = textFirstName.Text;
                //da.InsertCommand.Parameters.Add("@LASTNAME", SqlDbType.VarChar).Value = textLastName.Text;

                myConnection.Open();
                da.InsertCommand.ExecuteNonQuery();
                myConnection.Close();

                MessageBox.Show("Your account has been created!");

                textFirstName.Text = "";
                textLastName.Text = "";
            }

        }
    }
}

Form2.

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace WindowsFormsApplication1
{
    public partial class Form2 : Form
    {
        public Form2()
        {
            InitializeComponent();
        }
    }
}

This post has been edited by Curtis Rutland: 02 October 2012 - 02:02 PM

[Martyr2]

There are a few ways. One nice way is to pass the current form (form1) to the showdialog() method and after the result is returned and is ok, set variables/call methods of form1 using data from form2. Here we call this from form1 when we show the dialog...

Form2 f2 = new Form2();

// Here "this" is going to be form1, the current form.  
if (f2.ShowDialog(this) == DialogResult.OK)
{
      // Read the contents of f2 TextBox and put it in a local variable of form1. 
      localDbString = f2.txtDatabase.Text;
}

You could also expose public methods on form1 and have form2 reference form one, calling its public methods and passing it values from form2.

You could create a separate class that both forms know about and used to store settings. Pass around an instance of that class and have various forms fill it or read it. This is a nice solution when you have a ton of settings that many forms may use.

Those are just three, but there are a few others.

[Ryano121]

Some more relevant reading -

How To Get Values From Form1 To Form2 (Or Any Other Forms).

[Curtis Rutland]

Also, please crop your screenshots in the future. You posted a huge, high-res screenshot of mostly blank desktop. I've fixed it for you this time.

[maxbre]

Thanks for all the answers.

I got the variable from form2 now. but the connection string dosn't seems to set the value in the connection string as I want it to do. i'm getting "instance failure".

Form1

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace WindowsFormsApplication1
{
    public partial class Form1 : Form
    {
        Form2 form2 = new Form2();

        public Form1()
        {
            InitializeComponent();
        }

        private void button1_Click(object sender, EventArgs e)
        {
            if (textFirstName.Text == "settings")
            {
                form2.ShowDialog();
            }

            if (textFirstName.Text == "settings" == false)
            {
                SqlConnection myConnection = new SqlConnection("Data Source=" + form2.MyValue + "; Initial Catalog=Account; User ID=sa; Password=Windows; Integrated Security=TRUE");

                SqlDataAdapter da = new SqlDataAdapter();
                da.InsertCommand = new SqlCommand("INSERT INTO tUser (sUserID, sUserPW, sUserName) VALUES ('" + textFirstName.Text + "','" + textLastName.Text + "','" + textFirstName.Text + "')", myConnection);
                //da.InsertCommand.Parameters.Add("@FIRSTNAME", SqlDbType.VarChar).Value = textFirstName.Text;
                //da.InsertCommand.Parameters.Add("@LASTNAME", SqlDbType.VarChar).Value = textLastName.Text;

                myConnection.Open();
                da.InsertCommand.ExecuteNonQuery();
                myConnection.Close();

                MessageBox.Show("Your account has been created!");

                textFirstName.Text = "";
                textLastName.Text = "";

            }
        }
    }
}

Form2

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;

namespace WindowsFormsApplication1
{
    public partial class Form2 : Form
    {
        public string MyValue
        {
            get { return textInsanceName.Text; }
            set { textInsanceName.Text = value; }
        }
        public Form2()
        {
            InitializeComponent();
        }
    }
}

[Curtis Rutland]

Give us an actual copy and paste of the error you're getting.

[h4nnib4l]

Quote

something like this

Data Source='" + instanceName + "'; Initial Catalog='" + database + "'; User ID='" + User + "'; Password='" + Pass + "'; Integrated Security=TRUE");

This ConnectionStringBuilder class would work for this. Create a method that that takes your parameters as arguments, and pass those to a SqlConnectionStringBuilder, and then return the connection:

static SqlConnection ConnectionBuilder(string dataSource, string initialCatalog, string userID, string password)
{
    var conBuilder = new SqlConnectionStringBuilder
                            {
                                DataSource = dataSource,
                                InitialCatalog = initialCatalog,
                                UserID = userID,
                                Password = password
                            };
	
	return new SqlConnection(conBuilder.ConnectionString);
}

Then call that method: var myConnection = ConnectionBuilder(*your parameters*)

Quote

SqlDataAdapter da = new SqlDataAdapter();
da.InsertCommand = new SqlCommand("INSERT INTO tUser (sUserID, sUserPW, sUserName) VALUES ('" + textFirstName.Text + "','" + textLastName.Text + "','" + textFirstName.Text + "')", myConnection);
//da.InsertCommand.Parameters.Add("@FIRSTNAME", SqlDbType.VarChar).Value = textFirstName.Text;
//da.InsertCommand.Parameters.Add("@LASTNAME", SqlDbType.VarChar).Value = textLastName.Text;

Here's a good tutorial on using parameters. You have a kind of bastardized parameterization going on in your INSERT statement. You're adding parameters, but they don't exist in the statement.

This:

INSERT INTO tUser (sUserID, sUserPW, sUserName) 
VALUES ('" + textFirstName.Text + "','" + textLastName.Text + "','" + textFirstName.Text + "')

should look like this:

INSERT INTO tUser (sUserID, sUserPW, sUserName) 
VALUES (@LASTNAME, @FIRSTNAME)

And then declare your parameters:

da.InsertCommand.Parameters.AddWIthValue("@LASTNAME", textLastName.Text);
da.InsertCommand.Parameters.AddWithValue("@FIRSTNAME", textFirstName.Text);

See how that works? Also in that tutorial, Curtis Rutland demonstrates how to place your connections and commands into using statements: you should look into that as well.

[maxbre]

I tried with the ConnectionStringBuilder now but still getting "Instance Failure".

This is the code right now:

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Xml;
using System.Data.SqlClient;

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

        private void button1_Click(object sender, EventArgs e)
        {
            Form2 form2 = new Form2();
            form2.ShowDialog();
        }
        
        
        private void button2_Click(object sender, EventArgs e)
        {
            XmlDocument xDoc = new XmlDocument();
            xDoc.Load("C:\\Users\\Max\\Desktop\\config.xml");

            string dataSource = xDoc.SelectSingleNode("Config/InstanceName").InnerText;
            string initialCatalog = xDoc.SelectSingleNode("Config/Database").InnerText;
            string userID = xDoc.SelectSingleNode("Config/Login").InnerText;
            string password = xDoc.SelectSingleNode("Config/Password").InnerText;

            SqlConnectionStringBuilder builder = new SqlConnectionStringBuilder();
            builder.DataSource = dataSource;
            builder.InitialCatalog = initialCatalog;
            builder.UserID = userID;
            builder.Password = password;

            MessageBox.Show(builder.ConnectionString);


            SqlConnection myConnection = new SqlConnection(builder.ConnectionString);

            myConnection.Open();
            MessageBox.Show("Connected!");
            myConnection.Close();
        }
    }
}

[Skydiver]

It sounds like the problem is purely on establishing the connection. If you have Visual Studio Professional (or higher) on the machine you are trying to connect from, can you connect using the Tools.Connect to Database... menu option?

[maxbre]

No I don't have Visual Studo Professional (or higher). Actually, all I want to do is make it possible for the user to change the connection string, and the only way I can think of is making a variable of the new SQL details like Instance, DB, UserID and Password but seems like the SqlConnection function can't read variables in the string.

So what i've done so far is:
The user enter the new SQL details in 4 textBoxes in another form. One for Instance, DB, UserID and one for Password.
When the user click the save-button the details stores in a xml file.
Then in form1, i've done 4 variables for each detail value.
And then created the ConnectionString with the ConnectionStringBuilder.
So why isn't this working?

SqlConnection myConnection = new SqlConnection(builder.ConnectionString);

[Skydiver]

Does the connection string that is built up and displayed by line 45 of your code look valid? If it looks valid, then the only questions left will be:
- can the machine be addressed by name
- are the ports open on that machine
- is the SQL server and associated services running on that machine

One way to independently test those outside you your program was to use VS. If you have SQL Server Management Studio, you could also try connecting without using your program.

As an aside, how is the Instance being entered by the user? Does it look like "MACHINENAME\SqlExpress" ?

[maxbre]

Yes the instance looks like "MACHINENAME\SQLExpress" and there is no problems with the SQL server itself. I can connect to the SQL server without any problem by using this string instead of the variable:

"Data Source=Max-PC\\SQLExpress; Initial Catalog=Account; User ID=sa; Password=Windows; Integrated Security=TRUE"

but then the user won't be avaible to change the string.

[Skydiver]

Does the string returned by builder.ConnectionString look like:

"Data Source=Max-PC\\SQLExpress; Initial Catalog=Account; User ID=sa; Password=Windows; Integrated Security=TRUE"

[maxbre]

Yes, it looks exactly the same.

[Curtis Rutland]

Tell me, do you have the "\\" in the XML file? If you do, remove one of the slashes, and it should work.