Trying to get data to textbox from sql. there is no row at position 0 exception

Question

 public void doldur()
    {
        SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\Odev.mdf;Integrated Security=True");
        con.Open();

        SqlDataAdapter da = new SqlDataAdapter("Select * From ogrenciler Order By adsoyad ASC", con);
        DataSet dts = new DataSet();

        da.Fill(dts, "ogrenciler");
        comboBox1.DataSource = dts.Tables["ogrenciler"];
        comboBox1.ValueMember = "adsoyad";
        comboBox1.DisplayMember = "adsoyad";

        comboBox2.DataSource = dts.Tables["ogrenciler"];
        comboBox2.ValueMember = "adsoyad";
        comboBox2.DisplayMember = "adsoyad";

        con.Close();
    }

-combobox2 gets the data correctly-

 private void ogrenci_Load(object sender, EventArgs e)
    {
        doldur();

    }

-it is working either-

 private void comboBox2_SelectedIndexChanged(object sender, EventArgs e)
    {
        textBox7.Text = "";
        textBox8.Text = "";
        textBox9.Text = "";
        textBox10.Text = "";

        SqlConnection con = new SqlConnection(@"Data Source=(LocalDB)\MSSQLLocalDB;AttachDbFilename=|DataDirectory|\Odev.mdf;Integrated Security=True");
        con.Open();

        SqlDataAdapter du = new SqlDataAdapter("Select * From ogrenciler Where adsoyad='"+ comboBox2.SelectedValue.ToString() +"'", con);
        DataSet dy = new DataSet();

        du.Fill(dy, "ogrenciler");

        textBox7.Text = dy.Tables[0].Rows[0]["adsoyad"].ToString();
        textBox8.Text = dy.Tables[0].Rows[0]["mail"].ToString();
        textBox9.Text = dy.Tables[0].Rows[0]["sinif"].ToString();
        textBox10.Text = dy.Tables[0].Rows[0]["sube"].ToString();

        con.Close();
    }

In here im getting:

"An exception of type 'System.IndexOutOfRangeException' occurred in System.Data.dll but was not handled in user code"(On textbox7 row)

I checked my table to be sure if I wrote correct SQL statement.(Everything is correct) Then I realize my dataadapter or dataset getting literally no data. Thats why I can't fill my textboxes. I tried to use datatable/rows.count, executescalar, datareader/dr.read, executereader. None of these worked. Then I started to suspect about my SQL Statement. Don't know how to fix. New to this language. I'm done. Please help me with this.

ps: This code was running properly 2days ago.

Edit 1: I just found out that If my combobox2's value includes "ı,ş,ğ" letters (and my first value includes "ş") i got this error.

FYI, your code is vulnerable to SQL injection attacks. You need to [parameterize your queries](https://stackoverflow.com/a/35163362/2457029) to prevent these attacks — maccettura, May 02 '18 at 18:29
You say this was working 2 days ago. If nothing changed code wise, I would check the data. Can you run your SQL query directly in Sql Server to confirm that is working properly/returning data — Brad, May 02 '18 at 18:30
-maccettura None gonna use this program except me. I'll work on totally local. -Brad yes i tried and it worked on SQL query. — Cem B., May 02 '18 at 18:30
@CemB. Still good practice to do things the right way, especially considering it is easier than concatenating strings. Plus it makes your code easier to debug — maccettura, May 02 '18 at 18:31
So what? Parameterizing queries is so simple. Besides you may decide to let others use it. Or you, or someone else, may copy and paste the code elsewhere as an example. My friend bobby tables loves this type of thing. http://bobby-tables.com/ — Sean Lange, May 02 '18 at 18:32
To solve the problem at hand you need to debug your code. This means you have to put in breakpoints and evaluate what is happening. The query you are executing is not returning a row. The ONLY way to figure out why is to know what query you are executing. And the easiest way to do that is putting a break point and looking at the runtime value. — Sean Lange, May 02 '18 at 18:34
@SeanLange I just found out that If my combobox2's value includes "ı,ş,ğ" letters (and my first value includes "ş") i got this error. Do you know why? — Cem B., May 02 '18 at 18:44
Because those characters result in a where clause that selects no rows. — John Wu, May 02 '18 at 18:54
Yes I do. Because you are not using parameters. Those are unicode characters and you don't have N in front of your string literal. Using parameters with the correct datatype would solve this. Or you could continue the unsafe code you have. where adsoyad=N'.... — Sean Lange, May 02 '18 at 19:00
@CemB. this all comes back to using parameters. It not only make your code safe but it handles the types appropriately. — maccettura, May 02 '18 at 19:02
@maccettura I know I will update my queries with parameters. But still I was able to query unicode characters 2 days ago, and now I can't. What I can do for undo whatevery happens and just query without N'.. — Cem B., May 02 '18 at 19:06
If the code you posted is the same thing you had 2 days ago there is no way it returned unicode correctly 2 days ago, unless the character is part of the extended ascii set. Regardless if you handle the data correctly the whole issue goes away. Why not just fix the code now and be done with it? It will seriously take about 2 minutes. — Sean Lange, May 02 '18 at 19:10

Sean Lange · Answer 1 · 2018-05-02T19:25:27.277

1

The right way to fix this is to use parameterized queries. This is really easy to fix. It prevents this type of error and also 100% prevents sql injection. It is so simple to parameterize queries that not doing it is just lazy.

SqlDataAdapter du = new SqlDataAdapter("Select * From ogrenciler Where adsoyad = @CombBox2", con);
du.SelectCommand.Parameters.Add("@ComboBox2", SqlDbType.NVarChar, 20).Value = comboBox2.SelectedValue.ToString();
DataSet dy = new DataSet();

edited May 02 '18 at 19:25

answered May 02 '18 at 19:20

Sean Lange

33,028
3
25
40

Still the same error. "An exception of type 'System.IndexOutOfRangeException' occurred in System.Data.dll but was not handled in user code. There is no row at position 0" – Cem B. May 02 '18 at 19:32
1

Well...your code doesn't even attempt to evaluate if there is data. You would normally put that sort of thing inside if(dy.Tables[0].Rows.Count > 0). I can't help you debug your logic here. What is your query that is actually running? Do you have a row with that value in the table? I can't just magically solve this for you. You have to put in some effort here. – Sean Lange May 02 '18 at 19:34
What is the table structure? How about some sample data? And the value you are searching for? – Sean Lange May 02 '18 at 19:35

Trying to get data to textbox from sql. there is no row at position 0 exception

1 Answers1