The following assumes that the value in at least one of your columns is "test", but you don't know which column or columns. It stores the name of any matching columns in a List<string>:
var columns = new List<string>();
var db = Database.Open("myDB");
var row = db.QuerySingle("SELECT * FROM Interstates WHERE StateID = @0", stateid);
foreach(string col in row){
if(row[col] != null && row[col].ToString() == "test"){
columns.Add(col);
}
}
The dynamic record I think is 'row'. But what is the other term?
Cannot implicitly convert type 'WebMatrix.Data.DynamicRecord' to 'System.Collections.IEnumerable'
Description:
An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: Microsoft.CSharp.RuntimeBinder.RuntimeBinderException: Cannot implicitly convert type 'WebMatrix.Data.DynamicRecord' to 'System.Collections.IEnumerable'
Source Error:
Line 4: var db = Database.Open("try1");
Line 5: var row = db.QuerySingle("SELECT * FROM Interstates WHERE StateID = @0", stateid);
Line 6: foreach(string col in row){ Line 7: if(row[col] != null && row[col].ToString() == "295"){
Line 8: columns.Add(col);
Source File: c:\UsersfgasfdT\Documents\My Web Sites\mthisRev100\findIScol.cshtml Line:
6
You are recieving this error because the .QuerySingle() method returns just a single dynamic object matching value in the sequence, which the foreach loop can't iterate through.
You can access your properties directly from that object if you want, otherwise you may want to use the
.Query() method instead if you intend to return multiple results and iterate through them.
Example
var db = Database.Open("try1");
//The following will return multiple results into your rows object
var rows = db.Query("SELECT * FROM Interstates WHERE StateID = @0", stateid);
//You should now be able to iterate through your rows collection of objects
foreach(string col in rows){
//Your Logic Here
}
There are 33 columns I need to search through. I could have 33 if statements there, but that is kind of Mickey Mouse (even for me).
I had tried db.Query. I get red squigly lines under row[col] and error messages "cannot apply indexing with [] to an expression of type 'System.Collections.Generic.IEnumberable<dynamic>
var columns = new List<string>();
var stateid = "NJ";
var db = Database.Open("try1");
var row = db.Query("SELECT * FROM Interstates WHERE StateID = @0", stateid);
foreach(string col in row){
if(row[col] != null && row[col].ToString() == "295"){
columns.Add(col);
}
}
@wavemaster I think Mike's update should fix your issue, as I hadn't realized that you wanted to iterate through all of the columns (instead of multiple results)
Mike's recent addition to his answer would be exactly what you would need :
var columns = new List<string>();
var db = Database.Open("myDB");
//Performs your Query
var row = db.QuerySingle("SELECT * FROM Interstates WHERE StateID = @0", stateid);
//This will iterate through each of your columns in your result
foreach(string col in row.Columns){
if(row[col] != null && row[col].ToString() == "295"){
columns.Add(col);
}
}
wavemaster
Participant
1350 Points
1161 Posts
how to find the column name that has a certain value in a single row
Jan 30, 2013 09:14 PM|LINK
My table has 34 columns StateID, and IS1 - IS33.
In one particular row I have the value that is in one of these columns IS1 - IS33.
I just need to know which column.
Doing my research I see lots of folks talking about finding a column of a certain name across a single database or even multiple databases.
"SELECT COLUMN_NAME FROM InterStates WHERE ?=@0" AND StateID=@1", value , stateid);
Mikesdotnett...
All-Star
155597 Points
19981 Posts
Moderator
MVP
Re: how to find the column name that has a certain value in a single row
Feb 03, 2013 08:32 AM|LINK
The following assumes that the value in at least one of your columns is "test", but you don't know which column or columns. It stores the name of any matching columns in a List<string>:
var columns = new List<string>(); var db = Database.Open("myDB"); var row = db.QuerySingle("SELECT * FROM Interstates WHERE StateID = @0", stateid); foreach(string col in row){ if(row[col] != null && row[col].ToString() == "test"){ columns.Add(col); } }Web Pages CMS | My Site | Twitter
wavemaster
Participant
1350 Points
1161 Posts
Re: how to find the column name that has a certain value in a single row
Feb 03, 2013 01:43 PM|LINK
The dynamic record I think is 'row'. But what is the other term?
Cannot implicitly convert type 'WebMatrix.Data.DynamicRecord' to 'System.Collections.IEnumerable'
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details: Microsoft.CSharp.RuntimeBinder.RuntimeBinderException: Cannot implicitly convert type 'WebMatrix.Data.DynamicRecord' to 'System.Collections.IEnumerable'
Source Error:
Line 4: var db = Database.Open("try1"); Line 5: var row = db.QuerySingle("SELECT * FROM Interstates WHERE StateID = @0", stateid); Line 6: foreach(string col in row){Line 7: if(row[col] != null && row[col].ToString() == "295"){ Line 8: columns.Add(col);
Source File: c:\UsersfgasfdT\Documents\My Web Sites\mthisRev100\findIScol.cshtml Line: 6
Stack Trace:
wavemaster
Participant
1350 Points
1161 Posts
Re: how to find the column name that has a certain value in a single row
Feb 03, 2013 01:58 PM|LINK
row:
Columns Count 35
[0] "StateId" string
[1] "IS1" string
[2] "IS2" string
etc
Rion William...
All-Star
31992 Points
5191 Posts
Re: how to find the column name that has a certain value in a single row
Feb 03, 2013 02:58 PM|LINK
You are recieving this error because the .QuerySingle() method returns just a single dynamic object matching value in the sequence, which the foreach loop can't iterate through.
You can access your properties directly from that object if you want, otherwise you may want to use the .Query() method instead if you intend to return multiple results and iterate through them.
Example
var db = Database.Open("try1"); //The following will return multiple results into your rows object var rows = db.Query("SELECT * FROM Interstates WHERE StateID = @0", stateid); //You should now be able to iterate through your rows collection of objects foreach(string col in rows){ //Your Logic Here }wavemaster
Participant
1350 Points
1161 Posts
Re: how to find the column name that has a certain value in a single row
Feb 03, 2013 03:53 PM|LINK
There are 33 columns I need to search through. I could have 33 if statements there, but that is kind of Mickey Mouse (even for me).
I had tried db.Query. I get red squigly lines under row[col] and error messages "cannot apply indexing with [] to an expression of type 'System.Collections.Generic.IEnumberable<dynamic>
var columns = new List<string>();
var stateid = "NJ";
var db = Database.Open("try1");
var row = db.Query("SELECT * FROM Interstates WHERE StateID = @0", stateid);
foreach(string col in row){
if(row[col] != null && row[col].ToString() == "295"){
columns.Add(col);
}
}
Mikesdotnett...
All-Star
155597 Points
19981 Posts
Moderator
MVP
Re: how to find the column name that has a certain value in a single row
Feb 03, 2013 08:45 PM|LINK
Oops - should have been
foreach(string col in row.Columns){Web Pages CMS | My Site | Twitter
Rion William...
All-Star
31992 Points
5191 Posts
Re: how to find the column name that has a certain value in a single row
Feb 03, 2013 09:30 PM|LINK
wavemaster
Participant
1350 Points
1161 Posts
Re: how to find the column name that has a certain value in a single row
Feb 04, 2013 06:35 PM|LINK
Yes, only one column has the value I am looking for..
Rion William...
All-Star
31992 Points
5191 Posts
Re: how to find the column name that has a certain value in a single row
Feb 04, 2013 06:43 PM|LINK
Mike's recent addition to his answer would be exactly what you would need :
var columns = new List<string>(); var db = Database.Open("myDB"); //Performs your Query var row = db.QuerySingle("SELECT * FROM Interstates WHERE StateID = @0", stateid); //This will iterate through each of your columns in your result foreach(string col in row.Columns){ if(row[col] != null && row[col].ToString() == "295"){ columns.Add(col); } }