C#: Execute a query from Windows Forms using TextBoxes and a Button

Question

How I can change my code, so I can search for specific columns of my table?

I want to write the request's column values in TextBoxes, and then execute the request when I press a Button.

private async void button26_Click(object sender, EventArgs e)
{
    if (label77.Visible) label77.Visible = false;
    if (!string.IsNullOrEmpty(textBox62.Text) && !string.IsNullOrEmpty(textBox62.Text))
    {
        SqlCommand command = new SqlCommand("INSERT INTO [Policlinic] ('id_Policlinic', 'Name', 'Address', 'Phone') VALUES (" + textBox62 + ", '" + textBox62 + "', '" + textBox62 + "','" + textBox62 + "')", sqlConnection);
        command.Parameters.Add("@id_Policlinic", SqlDbType.Int); command.Parameters["@id_Policlinic"].Value = Convert.ToInt32(textBox62.Text, 4);
        command.Parameters.AddWithValue("Name", textBox62.Text);
        command.Parameters.AddWithValue("Address", textBox62.Text);
        command.Parameters.AddWithValue("Phone", textBox62.Text);
        await command.ExecuteNonQueryAsync();
    }
    else
    {
        label77.Visible = true;
        label77.Text = "Поля должны быть заполнены!";
    }
}

Any thoughts?

Write code in button click event to search in the database table by the value entered in the textbox. — Chetan
– Chetan, Commented Apr 25, 2018 at 16:53
I would recommend naming your text boxes, buttons, and labels something useful, for example instead of textBox62 it could be txtName. I'm also assuming you don't want to use textBox62 for the name, address, and phone number? Your current code doesn't do anything other than insert into the database, so I'm not sure what kind of "search by column" you are talking about? — Ron Beyer
– Ron Beyer, Commented Apr 25, 2018 at 16:55
I intentionally left the names created automatically, for ease of understanding. I'm trying to figure out how I can change my code so that it works as I planned. — AnatoliyC
– AnatoliyC, Commented Apr 25, 2018 at 17:03

Joel Coehoorn · Accepted Answer · 2018-04-25 19:06:09Z

Try this:

private async void button26_Click(object sender, EventArgs e)
{
    //why check the SAME textbox twice?
    // You should give MEANINGFUL NAMES to your controls, rather than leaving them at the default
    if (string.IsNullOrEmpty(textBox62.Text) || string.IsNullOrEmpty(textBox62.Text)) 
    {
        label77.Visible = true;
        label77.Text = "Поля должны быть заполнены!";
        return;
    }
    label77.Visible = false;

    string sql = "INSERT INTO [Policlinic] (Name, Address, Phone) VALUES ( @Name, @Address, @Phone);";

    using (var con = new SqlConnection("connection string here"))
    using (var cmd = new SqlCommand(sql, con))
    {  
        //Use exact database column types and lengths here
        // DON'T trust ADO.Net to guess these types correctly.
        cmd.Parameters.Add("@Name", SqlDbType.NVarChar, 20).Value = textBox62.Text;
        cmd.Parameters.Add("@Address", SqlDbType.NVarChar, 80).Value =  textBox62.Text;
        cmd.Parameters.Add("@Phone", SqlDbType.NVarChar, 14).Value =  textBox62.Text;

        con.Open()
        await cmd.ExecuteNonQueryAsync();
    }
}

There are a number of important changes here:

Do NOT try to re-use the same SqlConnection object throughout your application. This interferes with the built-in connection pooling.
Do NOT trust ADO.Net to guess your parameter types and lengths.
DO rely on using blocks to the make sure your connection object is disposed, even if an exception is thrown.

If you're still learning how to do this stuff, I also recommend removing async/await for the time being, and just calling ExecuteNonQuery(). Add that stuff back after you have it working in the "normal" way.

Thanks for Do NOT trust ADO.Net to guess your parameter types and lengths

sɐunıɔןɐqɐp · Accepted Answer · 2018-04-25 22:22:44Z

0

I tried to summarize what you should do in the code below to run this query.

Just remember to replace that textBox62 by something else more suitable.

Make sure you have the right sever address and the right credentials before creating the connection.

After executing the query, you should not keep the connection open for a long time (read this).

private void button26_Click(object sender, EventArgs e)
{
    if (label77.Visible) label77.Visible = false;

    if (string.IsNullOrEmpty(textBox62.Text))
    {
        label77.Visible = true;
        label77.Text = "Поля должны быть заполнены!";
        return;
    }

    SqlConnection connection = null;
    SqlCommand command = null;
    try
    {
        // First create a SQL Connection:
        string connectionString = $"Data Source=127.0.0.1;Initial Catalog=MyDatabaseName";
        Credential credential = new SqlCredential("myusername", "mypassword");
        connection = new SqlConnection(connectionString, credential);
        connection.Open();

        // Then create the SQL command:
        command = connection.CreateCommand();
        command.CommandType = CommandType.Text;
        command.CommandText = "INSERT INTO dbo.[Policlinic] (Name, Address, Phone) VALUES (@Name, @Address, @Phone)";

        // Add '@' in front of all column names:
        command.Parameters.Add(new SqlParameter("@Name", textBox62.Text, 60)
        {
            SqlDbType = SqlDbType.NVarChar,
            Direction = ParameterDirection.Input
        });
        command.Parameters.Add(new SqlParameter("@Address", textBox62.Text, 120)
        {
            SqlDbType = SqlDbType.NVarChar,
            Direction = ParameterDirection.Input
        });
        command.Parameters.Add(new SqlParameter("@Phone", textBox62.Text, 20)
        {
            SqlDbType = SqlDbType.NVarChar,
            Direction = ParameterDirection.Input
        });

        // Execute command:
        Task.Run(() => command.ExecuteNonQuery()).Wait();

        // Parse any results here...
    }
    catch (Exception ex)
    {
        if (ex.InnerException != null) ex = ex.InnerException;
        MessageBox.Show(this, ex.ToString(), "Exception", MessageBoxButtons.OK, MessageBoxIcon.Error);
    }
    finally
    {
        // Now cleanup this mess:
        command?.Dispose();
        connection?.Close();
        connection?.Dispose();
    }
}

Let me know the error message in case you're still in trouble executing that.

edited Apr 25, 2018 at 22:22

answered Apr 25, 2018 at 17:08

sɐunıɔןɐqɐp

3,59516 gold badges41 silver badges43 bronze badges

9 Comments

AnatoliyC Over a year ago

program crashes

sɐunıɔןɐqɐp Over a year ago

@AnatoliyChurikov: Found another issue in your code: the SQL query is wrong.

AnatoliyC Over a year ago

MS VStudio writes - unjustifiable justification

sɐunıɔןɐqɐp Over a year ago

@AnatoliyChurikov: Is the "id_Policlinic" column of type INT IDENTITY in the "dbo.Policlinic" table? If so, you should not set the value, because it will be automatically assigned by the database.

AnatoliyC Over a year ago

id_Policlinic this is surrogate key. I have a connection to the database. creating a new connection is not a good idea, I think that you only need to create a query with an existing connection.

|

Collectives™ on Stack Overflow

C#: Execute a query from Windows Forms using TextBoxes and a Button

2 Answers 2

1 Comment

9 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

9 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related