C# log in app with SQL [closed]

This question is unlikely to help any future visitors; it is only relevant to a small geographic area, a specific moment in time, or an extraordinarily narrow situation that is not generally applicable to the worldwide audience of the internet. For help making this question more broadly applicable, visit the he开发者_如何转开发lp center. Closed 11 years ago.

Hi I'm trying to get data from a local sql service database to take the input from a user register form. but when i push the button its not recorded onto the serviceable database.

do i need to use execute non query? how would i fix this code up? thanks

using System.Data.Sql;
using System.Data.SqlClient;

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

        private void button1_Click(object sender, EventArgs e)
        {
            Form2 form2 = new Form2();
            form2.Show();

            string connection = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\PaddlePower.mdf;Integrated Security=True;User Instance=True";

            SqlConnection cn = new SqlConnection(connection);
            try
            {
                cn.Open();
                MessageBox.Show("open");
            }

            catch (Exception)
            {
                MessageBox.Show("Did not connect");
            }

            string username = textBox1.Text;
            string password = textBox2.Text;
            string sqlquery = ("SELECT * FROM User WHERE Username = '" + textBox1.Text + "'");
            sqlquery = "INSERT INTO [User] (Username, Password) VALUES ('" + textBox1.Text + "','" + textBox2.Text + "')";

            SqlCommand command = new SqlCommand(sqlquery, cn);

            command.Parameters.AddWithValue("Username", username);
            command.Parameters.AddWithValue("Password", password);
            command.Parameters.Clear();


        }

    }
}

---

Something along the lines of the following should hopefully do it. There's some room for improvement, but I at least hope it solves the problem you're having.

string connection = @"Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\PaddlePower.mdf;Integrated Security=True;User Instance=True";
object queryResult = null;

using (SqlConnection cn = new SqlConnection(connection))
{
    cn.Open(); // Open connection

    // SELECT
    using (SqlCommand cmd = new SqlCommand("SELECT * FROM User WHERE Username = @Username AND Password = @Password", cn))
    {
        cmd.Parameters.AddWithValue("@Username", textBox1.Text);
        cmd.Parameters.AddWithValue("@Password", textBox2.Text);

        queryResult = cmd.ExecuteScalar();
    }

    // INSERT
    using (SqlCommand cmd = new SqlCommand("INSERT INTO [User] (Username, Password) VALUES (@Username, @Password)", cn))
    {
        cmd.Parameters.AddWithValue("@Username", textBox1.Text);
        cmd.Parameters.AddWithValue("@Password", textBox2.Text);

        cmd.ExecuteNonQuery(); // or int affected = cmd.ExecuteNonQuery()
    }
}

You can requse the first SqlCommand object or create a new one. There's very little difference with either way you choose to do it.

queryResult is just there for storing the result of cmd.ExecuteScalar(). You can map it to an object if you want (when selecting multiple columns) or cast it to a new type (if you're selecting a single column).

---

The direct answer is yes, you need to execute a non query. You see, you've prepared the command but you have not issued it. jstnasn's example should be very helpful. Take note of the using statements -- these will implicitly close the command when you exit the using statement, thus ensuring that the command is always closed when done.

The same occurs for the SqlConnection -- the using helps make sure that the connection is disposed of properly. However, if your database connection string allows connection pooling, then I believe the using statement will merely kill your object, without actually killing the connection to the database. This is advantageous because you will have lower I/O overhead the next time you need to open a database connection -- you'll just be connecting to an existing TCP/IP socket rather than opening a new on.

---

You have no parameters, nor do you ever actually send the query to the database

        // parameter placeholders defined with @parameter_name
        sqlquery = "INSERT INTO [User] (Username, Password) VALUES (@username, @Password);

        SqlCommand command = new SqlCommand(sqlquery, cn);

        command.Parameters.AddWithValue("@Username", username);
        command.Parameters.AddWithValue("@Password", password);

        // This will make the query happen on the database.
        // It will handle sending the parameters and all that good stuff
        // http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executenonquery.aspx
        command.ExecuteNonQuery();