_ID field goes wrong
-
I have coded to retrieve data from SQL Server to ListView on C Sharp. Unfortunately, ID field goes wrong. Obviously, it has four records then it display on a listview with double recode. See my codes .... dbConnection.sqlCnn.Open(); string sSQL = "SELECT * FROM TBL_USER"; SqlCommand sCommand = new SqlCommand(sSQL, dbConnection.sqlCnn); SqlDataReader sReader = sCommand.ExecuteReader(); while (sReader.Read() == true) { _ID.Add(sReader[0].ToString()); for (int i = 0; i < _ID.Count; i++) //for (int i = 0; i < sReader.FieldCount; i++ ) { lstItems = new ListViewItem((i + 1).ToString()); lstItems.SubItems.Add(sReader[1].ToString()); lstItems.SubItems.Add(sReader[2].ToString()); lstItems.SubItems.Add(sReader[3].ToString()); lstItems.SubItems.Add(sReader[4].ToString()); lstItems.SubItems.Add(sReader[5].ToString()); this.lsv_user.Items.Add(lstItems); } } sReader.Close(); dbConnection.sqlCnn.Close(); Thanks
Chuon Visoth Angkor Wat - Cambodia asp.net - c sharp beginner
-
I have coded to retrieve data from SQL Server to ListView on C Sharp. Unfortunately, ID field goes wrong. Obviously, it has four records then it display on a listview with double recode. See my codes .... dbConnection.sqlCnn.Open(); string sSQL = "SELECT * FROM TBL_USER"; SqlCommand sCommand = new SqlCommand(sSQL, dbConnection.sqlCnn); SqlDataReader sReader = sCommand.ExecuteReader(); while (sReader.Read() == true) { _ID.Add(sReader[0].ToString()); for (int i = 0; i < _ID.Count; i++) //for (int i = 0; i < sReader.FieldCount; i++ ) { lstItems = new ListViewItem((i + 1).ToString()); lstItems.SubItems.Add(sReader[1].ToString()); lstItems.SubItems.Add(sReader[2].ToString()); lstItems.SubItems.Add(sReader[3].ToString()); lstItems.SubItems.Add(sReader[4].ToString()); lstItems.SubItems.Add(sReader[5].ToString()); this.lsv_user.Items.Add(lstItems); } } sReader.Close(); dbConnection.sqlCnn.Close(); Thanks
Chuon Visoth Angkor Wat - Cambodia asp.net - c sharp beginner
Hi, your code does not make sense to me. the while loop iterates over the records, that is OK. the for loop should not be there: IMO each record only provides data for one item in the ListView. :)
Luc Pattyn [Forum Guidelines] [My Articles]
The quality and detail of your question reflects on the effectiveness of the help you are likely to get. Show formatted code inside PRE tags, and give clear symptoms when describing a problem.
-
I have coded to retrieve data from SQL Server to ListView on C Sharp. Unfortunately, ID field goes wrong. Obviously, it has four records then it display on a listview with double recode. See my codes .... dbConnection.sqlCnn.Open(); string sSQL = "SELECT * FROM TBL_USER"; SqlCommand sCommand = new SqlCommand(sSQL, dbConnection.sqlCnn); SqlDataReader sReader = sCommand.ExecuteReader(); while (sReader.Read() == true) { _ID.Add(sReader[0].ToString()); for (int i = 0; i < _ID.Count; i++) //for (int i = 0; i < sReader.FieldCount; i++ ) { lstItems = new ListViewItem((i + 1).ToString()); lstItems.SubItems.Add(sReader[1].ToString()); lstItems.SubItems.Add(sReader[2].ToString()); lstItems.SubItems.Add(sReader[3].ToString()); lstItems.SubItems.Add(sReader[4].ToString()); lstItems.SubItems.Add(sReader[5].ToString()); this.lsv_user.Items.Add(lstItems); } } sReader.Close(); dbConnection.sqlCnn.Close(); Thanks
Chuon Visoth Angkor Wat - Cambodia asp.net - c sharp beginner
misCafe wrote:
while (sReader.Read() == true) { _ID.Add(sReader[0].ToString()); for (int i = 0; i < _ID.Count; i++) //for (int i = 0; i < sReader.FieldCount; i++ ) { lstItems = new ListViewItem((i + 1).ToString()); lstItems.SubItems.Add(sReader[1].ToString()); lstItems.SubItems.Add(sReader[2].ToString()); lstItems.SubItems.Add(sReader[3].ToString()); lstItems.SubItems.Add(sReader[4].ToString()); lstItems.SubItems.Add(sReader[5].ToString()); this.lsv_user.Items.Add(lstItems); } }
With that, you are adding the same record to the ListView n(sReader.FieldCount) times. Your problem is the line in bold, you are adding the same ID/name (
(i + 1).ToString()) for each record. EachListViewItemshould have a different name or be blank (no parameter). I think I know what you are trying to do, so I hope this helps:if (sReader.FieldCount > 0)
{
_ID.Add(sReader[0].ToString());
lstItems = new ListViewItem(sReader[0].ToString()); //Notice: If this is your ID column then it should be unique each record.
for (int i = 1; i < sReader.FieldCount; i++)
{
lstItems.SubItems.Add(sReader[i].ToString());
}
this.lsv_user.Items.Add(lstItems);
}You just need to replace the content of your while loop.
My failometer is detecting vast quantities of FAIL! "Its SQL - hardly programming..." (Caslen)
-
Hi, your code does not make sense to me. the while loop iterates over the records, that is OK. the for loop should not be there: IMO each record only provides data for one item in the ListView. :)
Luc Pattyn [Forum Guidelines] [My Articles]
The quality and detail of your question reflects on the effectiveness of the help you are likely to get. Show formatted code inside PRE tags, and give clear symptoms when describing a problem.
Well, I have commented on //for (int i = 0; i < sReader.FieldCount; i++ ) so, it isn't used. Exactly, if we do a right code, it will display a correct recode (only four records on listview). In db it has only four records. After I have coded (code i have posted), it displays many records (duplicate records) on the listview. One record in db it shows many records on listview. Do you make sense? :)
Chuon Visoth Angkor Wat - Cambodia asp.net - c sharp beginner
-
Well, I have commented on //for (int i = 0; i < sReader.FieldCount; i++ ) so, it isn't used. Exactly, if we do a right code, it will display a correct recode (only four records on listview). In db it has only four records. After I have coded (code i have posted), it displays many records (duplicate records) on the listview. One record in db it shows many records on listview. Do you make sense? :)
Chuon Visoth Angkor Wat - Cambodia asp.net - c sharp beginner
misCafe wrote:
Do you make sense?
Yes he does, you on the other hand don't. It seems that you are confussing 'records' and 'fields', records are the rows in the database and fields are the columns. The code that you have provided shows that you are adding the same row for every field (column) you have.
sReaderis the record (row),sReader[i]is the field (column). Hence why you go through the DataReader record by record (sReader.ReadLine()), and for each record you then go through it field by field, as shown in my other post. I hope that clears things up.My failometer is detecting vast quantities of FAIL! "Its SQL - hardly programming..." (Caslen)
-
misCafe wrote:
Do you make sense?
Yes he does, you on the other hand don't. It seems that you are confussing 'records' and 'fields', records are the rows in the database and fields are the columns. The code that you have provided shows that you are adding the same row for every field (column) you have.
sReaderis the record (row),sReader[i]is the field (column). Hence why you go through the DataReader record by record (sReader.ReadLine()), and for each record you then go through it field by field, as shown in my other post. I hope that clears things up.My failometer is detecting vast quantities of FAIL! "Its SQL - hardly programming..." (Caslen)
:rose:
Luc Pattyn [Forum Guidelines] [My Articles]
The quality and detail of your question reflects on the effectiveness of the help you are likely to get. Show formatted code inside PRE tags, and give clear symptoms when describing a problem.
-
Well, I have commented on //for (int i = 0; i < sReader.FieldCount; i++ ) so, it isn't used. Exactly, if we do a right code, it will display a correct recode (only four records on listview). In db it has only four records. After I have coded (code i have posted), it displays many records (duplicate records) on the listview. One record in db it shows many records on listview. Do you make sense? :)
Chuon Visoth Angkor Wat - Cambodia asp.net - c sharp beginner
you have two for loops, only one of them is commented out. :)
Luc Pattyn [Forum Guidelines] [My Articles]
The quality and detail of your question reflects on the effectiveness of the help you are likely to get. Show formatted code inside PRE tags, and give clear symptoms when describing a problem.
-
misCafe wrote:
while (sReader.Read() == true) { _ID.Add(sReader[0].ToString()); for (int i = 0; i < _ID.Count; i++) //for (int i = 0; i < sReader.FieldCount; i++ ) { lstItems = new ListViewItem((i + 1).ToString()); lstItems.SubItems.Add(sReader[1].ToString()); lstItems.SubItems.Add(sReader[2].ToString()); lstItems.SubItems.Add(sReader[3].ToString()); lstItems.SubItems.Add(sReader[4].ToString()); lstItems.SubItems.Add(sReader[5].ToString()); this.lsv_user.Items.Add(lstItems); } }
With that, you are adding the same record to the ListView n(sReader.FieldCount) times. Your problem is the line in bold, you are adding the same ID/name (
(i + 1).ToString()) for each record. EachListViewItemshould have a different name or be blank (no parameter). I think I know what you are trying to do, so I hope this helps:if (sReader.FieldCount > 0)
{
_ID.Add(sReader[0].ToString());
lstItems = new ListViewItem(sReader[0].ToString()); //Notice: If this is your ID column then it should be unique each record.
for (int i = 1; i < sReader.FieldCount; i++)
{
lstItems.SubItems.Add(sReader[i].ToString());
}
this.lsv_user.Items.Add(lstItems);
}You just need to replace the content of your while loop.
My failometer is detecting vast quantities of FAIL! "Its SQL - hardly programming..." (Caslen)