Select DataGrid Index based on row ID value?

Question

This is my first project, so be gentle

I know how to get the current row Index value and its [ID] value using the following code:

public void Sql_Address_SelectionChanged(object sender, SelectionChangedEventArgs e)
{
    DataGrid gd = (DataGrid)sender;
    if (gd.SelectedItem is DataRowView row_selected)
    {
        Public_Strings.selectedID = Int32.Parse(row_selected["ID"].ToString());
        Public_Strings.currentIndex = Int32.Parse(sql_address.SelectedIndex.ToString());
    }
}

I am displaying synchronized SQL table values in two different places - a DataGrid in TabItem 1 and a couple of editable TextBoxes in TabItem 2. I also have Next/Previous buttons in TabItem 2 to move up and down the DataGrid Index that also refresh the content of the TextBoxes. Everything works fine, but when I add or modify an entry in the SQL table, the Index shifts, because of the grouping and the Next/Previous buttons reset to the default Index 0.

I know how to bypass this when Deleting an entry by using this method:

public void Delete(object sender, RoutedEventArgs e)
{
    MessageBoxResult messageBoxResault = System.Windows.MessageBox.Show("Ali se prepričani?", "Potrditev izbrisa", System.Windows.MessageBoxButton.YesNo);
    if (messageBoxResault == MessageBoxResult.Yes)
    {
        Public_Strings.currentIndex= sql_address.SelectedIndex-1;
        SqlCommand cmd = new SqlCommand
            {
                CommandText = "DELETE FROM cbu_naslovi WHERE [ID]='" + Public_Strings.selectedID + "'",
                Connection = con
            };
        cmd.ExecuteNonQuery();
        Datagrid();
        sql_address.SelectedIndex = Public_Strings.currentIndex;
    }
}

My Add method:

public void Add(object sender, RoutedEventArgs e)
{
    MessageBoxResult messageBoxResault = System.Windows.MessageBox.Show("Ali se prepričani?", "Potrditev vnosa", System.Windows.MessageBoxButton.YesNo);
    if (messageBoxResault == MessageBoxResult.Yes)
    {
        SqlCommand cmd = new SqlCommand
            {
                CommandText = "INSERT INTO cbu_naslovi VALUES ('" + ulica.Text + "','" + hisna_st.Text + "','" + id_hise.Text + "','" + postna_st.Text + "','" + obmocje.Text + "','" + katastrska_obcina.Text + "','" + st_objekta.Text + "','" + st_delov.Text + "','" + st_parcele_1.Text + "','" + st_parcele_2.Text + "','" + st_parcele_3.Text + "','" + st_parcele_4.Text + "','" + st_parcele_5.Text + "','" + st_parcele_6.Text + "','" + st_parcele_7.Text + "')",
                Connection = con
            };
        cmd.ExecuteNonQuery();
        Datagrid();
        address.Content = ulica.Text.ToString() + " " + hisna_st.Text.ToString() + id_hise.Text.ToString();
    }
}

I need a solution that allows me to select the index based on the ID value of row, so that when I add or modify and entry in the SQL table the Next/Previous buttons continue from the newly added/modified index. Basically something in the lines of:

sql_address.SelectedIndex = "sql_address.SelectedIndex where sql_address[ID] = Public_Strings.currentIndex" - Paraphrasing

Visual refference:

https://image.ibb.co/k2XDAz/1.png

https://image.ibb.co/gJ00qz/2.png

Adephx · Accepted Answer · 2018-10-24T07:12:16.150

I solved this issue using the following combination of code:

public void Add(object sender, RoutedEventArgs e)
{
    MessageBoxResult messageBoxResault = System.Windows.MessageBox.Show("Ali se prepričani?", "Potrditev vnosa", System.Windows.MessageBoxButton.YesNo);
    if (messageBoxResault == MessageBoxResult.Yes)
    {
        SqlCommand cmd = new SqlCommand
            {
                CommandText = "INSERT INTO cbu_naslovi VALUES ('" + ulica.Text + "','" + hisna_st.Text + "','" + id_hise.Text + "','" + postna_st.Text + "','" + obmocje.Text + "','" + katastrska_obcina.Text + "','" + st_objekta.Text + "','" + st_delov.Text + "','" + st_parcele_1.Text + "','" + st_parcele_2.Text + "','" + st_parcele_3.Text + "','" + st_parcele_4.Text + "','" + st_parcele_5.Text + "','" + st_parcele_6.Text + "','" + st_parcele_7.Text + "')",
                Connection = con
            };
        cmd.ExecuteNonQuery();
        SaveID();
        sql_address.SelectedIndex = 0;
        SearchIndex();
        address.Content = ulica.Text.ToString() + " " + hisna_st.Text.ToString() + id_hise.Text.ToString();
        search.Text = string.Empty;
    }
}

public void SaveID()
{
    DatagridIndex();
    sql_address.SelectedIndex = sql_address.Items.Count - 1;
    Public_Strings.saveID3 = Public_Strings.saveID1;
    Datagrid();
}

public void SearchIndex()
{
    if (Public_Strings.saveID3 == Public_Strings.saveID1) { }
    else
    {
        sql_address.SelectedIndex++;
        SearchIndex();
    }
}

public void DatagridIndex()
{
    SqlCommand cmd = new SqlCommand
        {
            CommandText = "SELECT * FROM [cbu_naslovi] ORDER BY [ID] ASC",
            Connection = con
        };
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    dataGrid1 = new DataTable("cbu_naslovi");
    da.Fill(dataGrid1);
    sql_address.ItemsSource = dataGrid1.DefaultView;
}

public void Datagrid()
{
    SqlCommand cmd = new SqlCommand
        {
            CommandText = "SELECT * FROM [cbu_naslovi] ORDER BY [ULICA] ASC, LEN ([HS]) ASC, [HS] ASC, [HID] ASC",
            Connection = con
        };
    SqlDataAdapter da = new SqlDataAdapter(cmd);
    dataGrid1 = new DataTable("cbu_naslovi");
    da.Fill(dataGrid1);
    sql_address.ItemsSource = dataGrid1.DefaultView;
}

TLDR version, add an entry and create a "new" Datagrid ordered by ID ASC, the last ID on the list is always going to be the newly created one as long as you are using auto-increment. Save that ID in a NEW string. Now call for the "correct" Datagrid, order it the way you want and compare the IDs starting from Index 0 with the saved one until you find the right one, then stop the code. Your add button should now redirect you to the correct index and your next/previous buttons should work fine.

Edit_v1: There might be an easier solution, but I'm too stupid to find it (could theoretically solve it with a method that adds +1 to a counter every time you press the Add button, but be careful that you start with the correct number of IDs when importing CVS file).

Edit_v2: Or just SQL to find the last ID: Safest way to get last record ID from a table Then use the following method to convert it to string: How can I get SQL result into a STRING variable?

Edit_v3: Another method:

    public void Add(object sender, RoutedEventArgs e)
    {
        if (ulica.Text != "" && hisna_st.Text != "" && postna_st.Text != "" && obmocje.Text != "")
        {
            MessageBoxResult messageBoxResault = MessageBoxEx.Show(this, "Ali se prepričani?", "Potrditev vnosa", MessageBoxButton.YesNo);
            if (messageBoxResault == MessageBoxResult.Yes)
            {
                SqlCommand cmd = new SqlCommand
                {
                    CommandText = "INSERT INTO cbu_naslovi VALUES ('" + ulica.Text + " " + hisna_st.Text + id_hise.Text + "','" + ulica.Text + "','" + hisna_st.Text + "','" + id_hise.Text + "','" + postna_st.Text + "','" + obmocje.Text + "','" + katastrska_obcina.Text + "','" + st_objekta.Text + "','" + st_delov.Text + "','" + st_parcele_1.Text + "','" + st_parcele_2.Text + "','" + st_parcele_3.Text + "','" + st_parcele_4.Text + "','" + st_parcele_5.Text + "','" + st_parcele_6.Text + "','" + st_parcele_7.Text + "','" + st_parcele_8.Text + "','" + st_parcele_9.Text + "','" + st_parcele_10.Text + "','" + st_parcele_11.Text + "','" + st_parcele_12.Text + "','" + st_parcele_13.Text + "','" + st_parcele_14.Text + "','" + st_parcele_15.Text + "','" + st_parcele_16.Text + "','" + st_parcele_17.Text + "'); SELECT SCOPE_IDENTITY();",
                    Connection = con
                };
                int lastId = Convert.ToInt32(cmd.ExecuteScalar());
                InvokeDataGridAddress();
                SetToRow(lastId);
                address.Content = ulica.Text.ToString() + " " + hisna_st.Text.ToString() + id_hise.Text.ToString();
            }
        }
        else
        {
            MessageBoxEx.Show(this, "Vpisati je potrebno podatke!");
        }
    }

    public int CurrentID
    {
        get
        {
            int tmp = 0;
            if (dg_address.SelectedIndex >= 0)
            {
                int.TryParse(dtAddress.Rows[dg_address.SelectedIndex].ItemArray[0].ToString(), out tmp);
            }
            return tmp;
        }
    }

    public void SetToRow(int Id)
    {
        Mouse.OverrideCursor = System.Windows.Input.Cursors.Wait;
        dg_address.SelectionChanged -= DG_Address_SelectionChanged;
        while (CurrentID != Id && dg_address.SelectedIndex < dtAddress.Rows.Count - 1)
        {
            dg_address.SelectedIndex++;
        }
        dg_address.SelectionChanged += DG_Address_SelectionChanged;
        Mouse.OverrideCursor = System.Windows.Input.Cursors.Arrow;
    }

Select DataGrid Index based on row ID value?

1 Answers1