Select DataGrid Index based on row ID value?

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

1 Answer
1

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?

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.