SQL Sever read data to array in C#

Question

Hi i am trying to create a auto fill in my application. but some how its not filling the array. can someone help me? i am new to C# so i am sorry for stupid mistakes.

private void autonrTextBox_TextChanged(object sender, TextChangedEventArgs e)
    {
        try
        {


            int i = 0;
            var check[i];
            using (var con2 = new SqlConnection(@"Data Source=DESKTOP-RSEBNR7;Initial Catalog=AudiDealer;Integrated Security=True"))
            using (var cmd2 = new SqlCommand("SELECT * FROM auto where autonr = " + autonrTextBox.Text, con2))
            {
                con2.Open();
                check = cmd2.ExecuteScalar();
                con2.Close();
                autonrTextBox.Text = check[0];
                kentekenTextBox.Text = check[1];
                merkTextBox.Text = check[2];
                modelTextBox.Text = check[3];
                kleurTextBox.Text = check[4];
                categorieTextBox.Text = check[5];
                pkSTextBox.Text = check[6];
                apkTextBox.Text = check[7];
                kilometerstandTextBox.Text = check[8];
                bijtellingTextBox.Text = check[9];
                energielabelTextBox.Text = check[10];
            }
        }
        catch
        {
            MessageBox.Show("Dit Auto nummer komt niet voor in de database. controleer deze en probeer opnieuw","Error");
        }
    }

ExecuteScalar returns a single value, look at using a reader + ExecuteReader — Alex K.
– Alex K., Commented Dec 20, 2016 at 11:41

Dmitrii Bychenko · Accepted Answer · 2016-12-20 12:08:03Z

2

You have to use ExecuteReader() even if you want to read a single record (ExecuteScalar returns the single value):

// I've hidden the connection string by ...
using (var con2 = new SqlConnection(@"...")) {
  // using will close connection for you, do not call Close() direct
  con2.Open();

  // Let sql be readable and parametrized
  string sql = 
    @"SELECT * 
        FROM auto 
       WHERE autonr = @prm_autonr";

  using (var cmd2 = new SqlCommand(sql, con2)) {
    cmd2.Parameters.AddWithValue("@prm_autonr", autonrTextBox.Text);

    using (var reader = cmd2.ExecureReader()) {
      // Do we have any records?
      if (reader.Read()) {
        // To be on the safe side use Convert.ToString():
        // what if the database field is of type Number(8, 5)? NVarChar2(11)?
        autonrTextBox.Text = Convert.ToString(reader[0]);
        kentekenTextBox.Text = Convert.ToString(reader[1]);
        merkTextBox.Text = Convert.ToString(reader[2]);
        modelTextBox.Text = Convert.ToString(reader[3]);
        kleurTextBox.Text = Convert.ToString(reader[4]);
        categorieTextBox.Text = Convert.ToString(reader[5]);
        pkSTextBox.Text = Convert.ToString(reader[6]);
        apkTextBox.Text = Convert.ToString(reader[7]);
        kilometerstandTextBox.Text = Convert.ToString(reader[8]);
        bijtellingTextBox.Text = Convert.ToString(reader[9]);
        energielabelTextBox.Text = Convert.ToString(reader[10]);
      } 
    }
  }
}

edited Dec 20, 2016 at 12:08

answered Dec 20, 2016 at 12:01

Dmitrii Bychenko

188k20 gold badges178 silver badges231 bronze badges

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

1 Comment

Pieter Waterschoot Over a year ago

Worked like a charm! Thank you!

Valeh Mikayilzadeh · Accepted Answer · 2016-12-20 11:49:04Z

1

You must use ExecuteReader. The ExecuteScalar return only single data. ex: count, sum, min, max. aggregate functions

https://msdn.microsoft.com/en-us/library/9kcbe65k(v=vs.110).aspx

https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executescalar(v=vs.110).aspx

answered Dec 20, 2016 at 11:49

Valeh Mikayilzadeh

9291 gold badge7 silver badges18 bronze badges

Collectives™ on Stack Overflow

SQL Sever read data to array in C#

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related