Cannot figure out how to get the row I need in a datatable
-
I have a windows form program that needs to check records to make sure they are valid, and if so will append the description to the record. In an effort to reduce the impact this has on our database server, I'm loading everything I need into memory using a DataAdapter and filling a DataTable. Now that I have my table filled, I want to go through each item number and search the table to see if it exists...that's where I seem to be stuck. I'm open to other ideas if this isn't the best method. I've considered arraylists, dictionarys, even jagged arrays but couldn't figure out how to iterate through the DataReader to fill the arrays which is why I fell back to the DataTable. Here's what I have so far:
string\[\] AllContents = File.ReadAllLines(scanner\_number); DataTable dt1 = inv1.get\_part\_information(); foreach (string s in AllContents) { string\[\] splitup = s.Split(new char\[\]{}, StringSplitOptions.RemoveEmptyEntries); DataRow\[\] row = dt1.Select("'I$PN' = 'CA-106-77'"); string test1 = row\[0\].ToString(); string test2 = row\[1\].ToString(); splitup\[2\] = splitup\[2\].Remove(8); splitup\[2\] = splitup\[2\].Substring(4, 2) + "/" + splitup\[2\].Substring(6, 2) + "/" + splitup\[2\].Substring(0, 4); textBox1.Text += "...." + s + Environment.NewLine; }now, I've hard coded the select string because I'm just trying to figure out how to get the Expression correct. The system appears to find the column correctly, and that is a verified value, however it always fails on the string test line...and watching the datarow in visual studio shows that there is no data in the row. I've searched around online and did see one forum suggest trying something like this considering I am only ever expecting one row to be returned:
DataRow row = dt1.Select("'I$PN' = 'CA-106-77'")[0]
but this gives me the indexoutofrange exception.
"You're damned if you do, and you're damned if you dont" - Bart Simpson
-
I have a windows form program that needs to check records to make sure they are valid, and if so will append the description to the record. In an effort to reduce the impact this has on our database server, I'm loading everything I need into memory using a DataAdapter and filling a DataTable. Now that I have my table filled, I want to go through each item number and search the table to see if it exists...that's where I seem to be stuck. I'm open to other ideas if this isn't the best method. I've considered arraylists, dictionarys, even jagged arrays but couldn't figure out how to iterate through the DataReader to fill the arrays which is why I fell back to the DataTable. Here's what I have so far:
string\[\] AllContents = File.ReadAllLines(scanner\_number); DataTable dt1 = inv1.get\_part\_information(); foreach (string s in AllContents) { string\[\] splitup = s.Split(new char\[\]{}, StringSplitOptions.RemoveEmptyEntries); DataRow\[\] row = dt1.Select("'I$PN' = 'CA-106-77'"); string test1 = row\[0\].ToString(); string test2 = row\[1\].ToString(); splitup\[2\] = splitup\[2\].Remove(8); splitup\[2\] = splitup\[2\].Substring(4, 2) + "/" + splitup\[2\].Substring(6, 2) + "/" + splitup\[2\].Substring(0, 4); textBox1.Text += "...." + s + Environment.NewLine; }now, I've hard coded the select string because I'm just trying to figure out how to get the Expression correct. The system appears to find the column correctly, and that is a verified value, however it always fails on the string test line...and watching the datarow in visual studio shows that there is no data in the row. I've searched around online and did see one forum suggest trying something like this considering I am only ever expecting one row to be returned:
DataRow row = dt1.Select("'I$PN' = 'CA-106-77'")[0]
but this gives me the indexoutofrange exception.
"You're damned if you do, and you're damned if you dont" - Bart Simpson
Hi, I'm no DB specialist but did some, so I'll try and help: 1.
itmaster21 wrote:
DataRow row = dt1.Select("'I$PN' = 'CA-106-77'")[0]
is bound to fail if no data is found: you can't access row 0 when there are no rows at all.
itmaster21 wrote:
DataRow[] row = dt1.Select("'I$PN' = 'CA-106-77'");
seems OK with two comments: 1. it is unfortunate to have special characters in field names, without the dollar sign, the field name should not need single quotes, it could look like
DataRow[] row = dt1.Select("name = 'CA-106-77'");2. you are confusing yourself by calling the result row, it really should be rows indicating you may obtain 0, 1 or any number of rows.itmaster21 wrote:
string test1 = row[0].ToString(); string test2 = row[1].ToString();
is unsafe again: the number of rows returned can be less than 2, in which case one or both index operations would fail with an IndexOutOfRangeException. You need to program more defensively.
itmaster21 wrote:
splitup[2] = splitup[2].Remove(8);
Dito. What guarantee do you have splitup holds 3 or more elements? At the very least, you should put everything in a try-catch construct, and show the entire exception if and when you get it (that is Exception.ToString, not just Exception.Message). Hope this helps.
Luc Pattyn [Forum Guidelines] [My Articles]
- before you ask a question here, search CodeProject, then Google - the quality and detail of your question reflects on the effectiveness of the help you are likely to get - use the code block button (PRE tags) to preserve formatting when showing multi-line code snippets
-
Hi, I'm no DB specialist but did some, so I'll try and help: 1.
itmaster21 wrote:
DataRow row = dt1.Select("'I$PN' = 'CA-106-77'")[0]
is bound to fail if no data is found: you can't access row 0 when there are no rows at all.
itmaster21 wrote:
DataRow[] row = dt1.Select("'I$PN' = 'CA-106-77'");
seems OK with two comments: 1. it is unfortunate to have special characters in field names, without the dollar sign, the field name should not need single quotes, it could look like
DataRow[] row = dt1.Select("name = 'CA-106-77'");2. you are confusing yourself by calling the result row, it really should be rows indicating you may obtain 0, 1 or any number of rows.itmaster21 wrote:
string test1 = row[0].ToString(); string test2 = row[1].ToString();
is unsafe again: the number of rows returned can be less than 2, in which case one or both index operations would fail with an IndexOutOfRangeException. You need to program more defensively.
itmaster21 wrote:
splitup[2] = splitup[2].Remove(8);
Dito. What guarantee do you have splitup holds 3 or more elements? At the very least, you should put everything in a try-catch construct, and show the entire exception if and when you get it (that is Exception.ToString, not just Exception.Message). Hope this helps.
Luc Pattyn [Forum Guidelines] [My Articles]
- before you ask a question here, search CodeProject, then Google - the quality and detail of your question reflects on the effectiveness of the help you are likely to get - use the code block button (PRE tags) to preserve formatting when showing multi-line code snippets
Hello Luc, Thank you for the response. Well, the splitup string array is actually where I am parsing out a text file I download from a hand held scanner basically containing the item, quantity, and date seperated by spaces. Up until now I've honestly never really worried too much about Exception handling in this stage. I'm a network professional by trade, but when I would work on programming projects like this I generally throw everything together sloppy to "learn" or figure out if I can even do what it is I'm trying to do before going back through and adding my try, catch blocks and writing the user friendly messages when an exception occours. It's probably not good, however, to get into that habbit though, huh? Thanks! Furthermore, I know it's tricky when you have special characters in the column but this was all designed way before I came on board and I know they're not going to let me change that now :( I'll play around with the code a bit and try to repost an update tomorrow.
"You're damned if you do, and you're damned if you dont" - Bart Simpson
-
I have a windows form program that needs to check records to make sure they are valid, and if so will append the description to the record. In an effort to reduce the impact this has on our database server, I'm loading everything I need into memory using a DataAdapter and filling a DataTable. Now that I have my table filled, I want to go through each item number and search the table to see if it exists...that's where I seem to be stuck. I'm open to other ideas if this isn't the best method. I've considered arraylists, dictionarys, even jagged arrays but couldn't figure out how to iterate through the DataReader to fill the arrays which is why I fell back to the DataTable. Here's what I have so far:
string\[\] AllContents = File.ReadAllLines(scanner\_number); DataTable dt1 = inv1.get\_part\_information(); foreach (string s in AllContents) { string\[\] splitup = s.Split(new char\[\]{}, StringSplitOptions.RemoveEmptyEntries); DataRow\[\] row = dt1.Select("'I$PN' = 'CA-106-77'"); string test1 = row\[0\].ToString(); string test2 = row\[1\].ToString(); splitup\[2\] = splitup\[2\].Remove(8); splitup\[2\] = splitup\[2\].Substring(4, 2) + "/" + splitup\[2\].Substring(6, 2) + "/" + splitup\[2\].Substring(0, 4); textBox1.Text += "...." + s + Environment.NewLine; }now, I've hard coded the select string because I'm just trying to figure out how to get the Expression correct. The system appears to find the column correctly, and that is a verified value, however it always fails on the string test line...and watching the datarow in visual studio shows that there is no data in the row. I've searched around online and did see one forum suggest trying something like this considering I am only ever expecting one row to be returned:
DataRow row = dt1.Select("'I$PN' = 'CA-106-77'")[0]
but this gives me the indexoutofrange exception.
"You're damned if you do, and you're damned if you dont" - Bart Simpson
I don't remember, (read I am too lazy to test) but dt1.Select("[I$PN] = 'CA-106-77'"); check out http://msdn.microsoft.com/en-us/library/system.data.datacolumn.expression.aspx[^]
Need software developed? Offering C# development all over the United States, ERL GLOBAL, Inc is the only call you will have to make.
If you don't ask questions the answers won't stand in your way.
Most of this sig is for Google, not ego. -
I don't remember, (read I am too lazy to test) but dt1.Select("[I$PN] = 'CA-106-77'"); check out http://msdn.microsoft.com/en-us/library/system.data.datacolumn.expression.aspx[^]
Need software developed? Offering C# development all over the United States, ERL GLOBAL, Inc is the only call you will have to make.
If you don't ask questions the answers won't stand in your way.
Most of this sig is for Google, not ego.Hi Ennis, That's the really weird part. I've tried enclosing the column in [], and when I do that the debugger throws the error: "Cannot find column named "CA-" which doesn't make any sense to me. Playing around, the only way I can get it to find the column is wrapping single quotes around it.
"You're damned if you do, and you're damned if you dont" - Bart Simpson
-
Hi Ennis, That's the really weird part. I've tried enclosing the column in [], and when I do that the debugger throws the error: "Cannot find column named "CA-" which doesn't make any sense to me. Playing around, the only way I can get it to find the column is wrapping single quotes around it.
"You're damned if you do, and you're damned if you dont" - Bart Simpson
The link suggests that '-' is a special character and must be escaped.
Need software developed? Offering C# development all over the United States, ERL GLOBAL, Inc is the only call you will have to make.
If you don't ask questions the answers won't stand in your way.
Most of this sig is for Google, not ego. -
The link suggests that '-' is a special character and must be escaped.
Need software developed? Offering C# development all over the United States, ERL GLOBAL, Inc is the only call you will have to make.
If you don't ask questions the answers won't stand in your way.
Most of this sig is for Google, not ego.Oh, I thought it said you only needed to do that for the column name. So it should look like this? [I$PN] = 'CA\-106\-77' ? update. Ah, I got it working. I used the debugger and looked at the table again. The table see's the column header with double quotes in it. I was able to get it to work by using this.
DataRow[] rows = dt1.Select("[\"I$PN\"] = 'CA-106-77'");
Thank you for your help!
"You're damned if you do, and you're damned if you dont" - Bart Simpson
modified on Monday, February 9, 2009 8:59 AM
-
Hello Luc, Thank you for the response. Well, the splitup string array is actually where I am parsing out a text file I download from a hand held scanner basically containing the item, quantity, and date seperated by spaces. Up until now I've honestly never really worried too much about Exception handling in this stage. I'm a network professional by trade, but when I would work on programming projects like this I generally throw everything together sloppy to "learn" or figure out if I can even do what it is I'm trying to do before going back through and adding my try, catch blocks and writing the user friendly messages when an exception occours. It's probably not good, however, to get into that habbit though, huh? Thanks! Furthermore, I know it's tricky when you have special characters in the column but this was all designed way before I came on board and I know they're not going to let me change that now :( I'll play around with the code a bit and try to repost an update tomorrow.
"You're damned if you do, and you're damned if you dont" - Bart Simpson
Hi Luc, I was able to get this to work. Looking at the debugger again, the system see's the column name with double quotes. I was able to get it to find the record by using this search expression:
DataRow[] rows = dt1.Select("[\"I$PN\"] = 'CA-106-77'");
Notice I changed the name to rows! Now I'm going to spend some time writing some try and catches just in the event there are no rows or multiple rows found. Thanks for all of your help and input!
"You're damned if you do, and you're damned if you dont" - Bart Simpson