424,835 Members | 1,454 Online
Need help? Post your question and get tips & solutions from a community of 424,835 IT Pros & Developers. It's quick & easy.

 P: n/a Hi - For my VB.NET app, I have a SQL2K database that I use to create a dataset with multiple data tables. I've created a dataview (dvReportsTo) of one of the tables, SCPMaster, and I've bound a combobox control to that dataview. I'm trying to filter the dataview based on values in a second table (ReportsTo) in the same dataset, and it's not working. If it's possible to do this, please help me figure out what I've done wrong and how to implement this correctly. If I'm trying to do something that is not supported, I'd appreciate any suggestions of a workaround. Here's the filter I'm trying to implement: strFilter = "SCPMasterID <> '" & uidSelectedPerson.ToString & "'" strFilter &= " AND SCPMasterID NOT IN" strFilter &= " (SELECT ReportsToStaffID FROM ReportsTo" strFilter &= " WHERE ReportsToBossID = '" & uidSelectedPerson.ToString & "')" frmMain.dvReportsTo.RowFilter = strFilter Here's the DataException that I'm getting: System.Data.SyntaxErrorException: Syntax error: Missing operand after 'ReportsToStaffID' operator. at System.Data.ExpressionParser.Parse() at System.Data.DataExpression..ctor(String expression, DataTable table, Type type) at System.Data.DataExpression..ctor(String expression, DataTable table) at System.Data.DataFilter..ctor(String expression, DataTable table) at System.Data.DataView.set_RowFilter(String value) at StarContactPro_02_DT.modContact.SetReportsToFilter () in d:\Data\Visual Studio Projects\StarContactPro-02-DT\modContact.vb:line 107" Thanks for your help. - Jeff Nov 20 '05 #1
7 Replies

 P: n/a The subquery is the problem. You're going to have to do a dataTable.Select and loop through the results, building a comma separated list. Then use that list with Not In. For the complete expression syntax, this may be of help to you in the future http://msdn.microsoft.com/library/de...ssionTopic.asp -- W.G. Ryan, eMVP http://forums.devbuzz.com/ http://www.knowdotnet.com/williamryan.html http://www.msmvps.com/WilliamRyan/ http://www.devbuzz.com/content/zinc_...center_pg1.asp "Jeff" wrote in message news:CO***************@newsread3.news.atl.earthlin k.net... Hi - For my VB.NET app, I have a SQL2K database that I use to create a dataset with multiple data tables. I've created a dataview (dvReportsTo) of one of the tables, SCPMaster, and I've bound a combobox control to that dataview. I'm trying to filter the dataview based on values in a second table (ReportsTo) in the same dataset, and it's not working. If it's possible to do this, please help me figure out what I've done wrong and how to implement this correctly. If I'm trying to do something that is not supported, I'd appreciate any suggestions of a workaround. Here's the filter I'm trying to implement: strFilter = "SCPMasterID <> '" & uidSelectedPerson.ToString & "'" strFilter &= " AND SCPMasterID NOT IN" strFilter &= " (SELECT ReportsToStaffID FROM ReportsTo" strFilter &= " WHERE ReportsToBossID = '" & uidSelectedPerson.ToString & "')" frmMain.dvReportsTo.RowFilter = strFilter Here's the DataException that I'm getting: System.Data.SyntaxErrorException: Syntax error: Missing operand after 'ReportsToStaffID' operator. at System.Data.ExpressionParser.Parse() at System.Data.DataExpression..ctor(String expression, DataTable table, Type type) at System.Data.DataExpression..ctor(String expression, DataTable table) at System.Data.DataFilter..ctor(String expression, DataTable table) at System.Data.DataView.set_RowFilter(String value) at StarContactPro_02_DT.modContact.SetReportsToFilter () in d:\Data\Visual Studio Projects\StarContactPro-02-DT\modContact.vb:line 107" Thanks for your help. - Jeff Nov 20 '05 #2

 P: n/a "Jeff" schrieb Here's the filter I'm trying to implement: strFilter = "SCPMasterID <> '" & uidSelectedPerson.ToString & "'" strFilter &= " AND SCPMasterID NOT IN" strFilter &= " (SELECT ReportsToStaffID FROM ReportsTo" strFilter &= " WHERE ReportsToBossID = '" & uidSelectedPerson.ToString & "')" frmMain.dvReportsTo.RowFilter = strFilter Here's the DataException that I'm getting: System.Data.SyntaxErrorException: Syntax error: Missing operand after 'ReportsToStaffID' operator. at System.Data.ExpressionParser.Parse() at System.Data.DataExpression..ctor(String expression, DataTable table, Type type) at System.Data.DataExpression..ctor(String expression, DataTable table) at System.Data.DataFilter..ctor(String expression, DataTable table) at System.Data.DataView.set_RowFilter(String value) at StarContactPro_02_DT.modContact.SetReportsToFilter () in d:\Data\Visual Studio Projects\StarContactPro-02-DT\modContact.vb:line 107" The Dataview is not a database. You can not execute SQL statements on the Dataview. The valid syntax for the Rowfilter is described here: http://msdn.microsoft.com/library/en...ssionTopic.asp -- Armin How to quote and why: http://www.plig.net/nnq/nquote.html http://www.netmeister.org/news/learn2quote.html Nov 20 '05 #3

 P: n/a Thanks for the instant(!) response, William - I've tried your suggestion, and there seems to be a problem with the values in the comma-separated NOT IN list being GUIDs. (I went through the article you referenced, and I wasn't able to find the correct syntax for this.) What should I change?? Here's my relevant code: strStaff = "(" strFilter = "ReportsToBossID = '" & uidSelectedPerson.ToString & "'" drStaff = frmMain.dsSCP.Tables("ReportsTo").Select(strFilter ) intRows = drStaff.GetUpperBound(0) For intRow = 0 To intRows If blnFirstItem Then blnFirstItem = False Else strStaff &= "," End If strStaff &= "'" & drStaff(intRow)(0).ToString & "'" Next strStaff &= ")" strFilter = "SCPMasterID <> '" & uidSelectedPerson.ToString & "'" If intRows > 0 Then strFilter &= " AND SCPMasterID NOT IN " & strStaff frmMain.dvReportsTo.RowFilter = strFilter Thanks for your help. - Jeff "William Ryan eMVP" wrote in message news:eB*************@tk2msftngp13.phx.gbl... The subquery is the problem. You're going to have to do a dataTable.Select and loop through the results, building a comma separated list. Then use that list with Not In. For the complete expression syntax, this may be of help to you in the future http://msdn.microsoft.com/library/de...ssionTopic.asp -- W.G. Ryan, eMVP http://forums.devbuzz.com/ http://www.knowdotnet.com/williamryan.html http://www.msmvps.com/WilliamRyan/ http://www.devbuzz.com/content/zinc_...center_pg1.asp "Jeff" wrote in message news:CO***************@newsread3.news.atl.earthlin k.net... Hi - For my VB.NET app, I have a SQL2K database that I use to create a dataset with multiple data tables. I've created a dataview (dvReportsTo) of one of the tables, SCPMaster, and I've bound a combobox control to that dataview. I'm trying to filter the dataview based on values in a second table (ReportsTo) in the same dataset, and it's not working. If it's possible to do this, please help me figure out what I've done wrong and how to implement this correctly. If I'm trying to do something that is not supported, I'd appreciate any suggestions of a workaround. Here's the filter I'm trying to implement: strFilter = "SCPMasterID <> '" & uidSelectedPerson.ToString & "'" strFilter &= " AND SCPMasterID NOT IN" strFilter &= " (SELECT ReportsToStaffID FROM ReportsTo" strFilter &= " WHERE ReportsToBossID = '" & uidSelectedPerson.ToString & "')" frmMain.dvReportsTo.RowFilter = strFilter Here's the DataException that I'm getting: System.Data.SyntaxErrorException: Syntax error: Missing operand after 'ReportsToStaffID' operator. at System.Data.ExpressionParser.Parse() at System.Data.DataExpression..ctor(String expression, DataTable table, Type type) at System.Data.DataExpression..ctor(String expression, DataTable table) at System.Data.DataFilter..ctor(String expression, DataTable table) at System.Data.DataView.set_RowFilter(String value) at StarContactPro_02_DT.modContact.SetReportsToFilter () in d:\Data\Visual Studio Projects\StarContactPro-02-DT\modContact.vb:line 107" Thanks for your help. - Jeff Nov 20 '05 #4

 P: n/a If you would ,set a breakpoint right before you set the rowfilter and let me see what the exact command you're setting is. it's hard to tell from here and I don't have your values so i can't easily tell. -- W.G. Ryan MVP Windows - Embedded http://forums.devbuzz.com http://www.knowdotnet.com/dataaccess.html http://www.msmvps.com/williamryan/ "Jeff" wrote in message news:Yz*****************@newsread3.news.atl.earthl ink.net... Thanks for the instant(!) response, William - I've tried your suggestion, and there seems to be a problem with the values in the comma-separated NOT IN list being GUIDs. (I went through the article you referenced, and I wasn't able to find the correct syntax for this.) What should I change?? Here's my relevant code: strStaff = "(" strFilter = "ReportsToBossID = '" & uidSelectedPerson.ToString & "'" drStaff = frmMain.dsSCP.Tables("ReportsTo").Select(strFilter ) intRows = drStaff.GetUpperBound(0) For intRow = 0 To intRows If blnFirstItem Then blnFirstItem = False Else strStaff &= "," End If strStaff &= "'" & drStaff(intRow)(0).ToString & "'" Next strStaff &= ")" strFilter = "SCPMasterID <> '" & uidSelectedPerson.ToString & "'" If intRows > 0 Then strFilter &= " AND SCPMasterID NOT IN " & strStaff frmMain.dvReportsTo.RowFilter = strFilter Thanks for your help. - Jeff "William Ryan eMVP" wrote in message news:eB*************@tk2msftngp13.phx.gbl... The subquery is the problem. You're going to have to do a dataTable.Select and loop through the results, building a comma separated list. Then use that list with Not In. For the complete expression syntax, this may be of help to you in the future http://msdn.microsoft.com/library/de...ssionTopic.asp -- W.G. Ryan, eMVP http://forums.devbuzz.com/ http://www.knowdotnet.com/williamryan.html http://www.msmvps.com/WilliamRyan/ http://www.devbuzz.com/content/zinc_...center_pg1.asp "Jeff" wrote in message news:CO***************@newsread3.news.atl.earthlin k.net... Hi - For my VB.NET app, I have a SQL2K database that I use to create a dataset with multiple data tables. I've created a dataview (dvReportsTo) of one of the tables, SCPMaster, and I've bound a combobox control to that dataview. I'm trying to filter the dataview based on values in a second table (ReportsTo) in the same dataset, and it's not working. If it's possible to do this, please help me figure out what I've done wrong and how to implement this correctly. If I'm trying to do something that is not supported, I'd appreciate any suggestions of a workaround. Here's the filter I'm trying to implement: strFilter = "SCPMasterID <> '" & uidSelectedPerson.ToString & "'" strFilter &= " AND SCPMasterID NOT IN" strFilter &= " (SELECT ReportsToStaffID FROM ReportsTo" strFilter &= " WHERE ReportsToBossID = '" & uidSelectedPerson.ToString & "')" frmMain.dvReportsTo.RowFilter = strFilter Here's the DataException that I'm getting: System.Data.SyntaxErrorException: Syntax error: Missing operand after 'ReportsToStaffID' operator. at System.Data.ExpressionParser.Parse() at System.Data.DataExpression..ctor(String expression, DataTable table, Type type) at System.Data.DataExpression..ctor(String expression, DataTable table) at System.Data.DataFilter..ctor(String expression, DataTable table) at System.Data.DataView.set_RowFilter(String value) at StarContactPro_02_DT.modContact.SetReportsToFilter () in d:\Data\Visual Studio Projects\StarContactPro-02-DT\modContact.vb:line 107" Thanks for your help. - Jeff Nov 20 '05 #5

 P: n/a Sure. Here's the value of strFilter: "SCPMasterID <> '23865682-20c1-11c4-8000-8ea6b5cb2452' AND SCPMasterID NOT IN ('3ac54427-20c1-11c4-8000-8ea6b5cb2452','3ac54429-20c1-11c4-8000-8ea6b5cb245 2','3ac5442b-20c1-11c4-8000-8ea6b5cb2452','3b5dda80-20c1-11c4-8000-8ea6b5cb2 452','3b5dda82-20c1-11c4-8000-8ea6b5cb2452')" - Jeff "William Ryan eMVP" wrote in message news:ut**************@tk2msftngp13.phx.gbl... If you would ,set a breakpoint right before you set the rowfilter and let me see what the exact command you're setting is. it's hard to tell from here and I don't have your values so i can't easily tell. -- W.G. Ryan MVP Windows - Embedded http://forums.devbuzz.com http://www.knowdotnet.com/dataaccess.html http://www.msmvps.com/williamryan/ "Jeff" wrote in message news:Yz*****************@newsread3.news.atl.earthl ink.net... Thanks for the instant(!) response, William - I've tried your suggestion, and there seems to be a problem with the values in the comma-separated NOT IN list being GUIDs. (I went through the article you referenced, and I wasn't able to find the correct syntax for this.) What should I change?? Here's my relevant code: strStaff = "(" strFilter = "ReportsToBossID = '" & uidSelectedPerson.ToString & "'" drStaff = frmMain.dsSCP.Tables("ReportsTo").Select(strFilter ) intRows = drStaff.GetUpperBound(0) For intRow = 0 To intRows If blnFirstItem Then blnFirstItem = False Else strStaff &= "," End If strStaff &= "'" & drStaff(intRow)(0).ToString & "'" Next strStaff &= ")" strFilter = "SCPMasterID <> '" & uidSelectedPerson.ToString & "'" If intRows > 0 Then strFilter &= " AND SCPMasterID NOT IN " & strStaff frmMain.dvReportsTo.RowFilter = strFilter Thanks for your help. - Jeff "William Ryan eMVP" wrote in message news:eB*************@tk2msftngp13.phx.gbl... The subquery is the problem. You're going to have to do a dataTable.Select and loop through the results, building a comma separated list. Then use that list with Not In. For the complete expression syntax, this may be of help to you in the future http://msdn.microsoft.com/library/de...ssionTopic.asp -- W.G. Ryan, eMVP http://forums.devbuzz.com/ http://www.knowdotnet.com/williamryan.html http://www.msmvps.com/WilliamRyan/ http://www.devbuzz.com/content/zinc_...center_pg1.asp "Jeff" wrote in message news:CO***************@newsread3.news.atl.earthlin k.net... > Hi - > > For my VB.NET app, I have a SQL2K database that I use to create a dataset > with multiple data tables. I've created a dataview (dvReportsTo) of one of > the tables, SCPMaster, and I've bound a combobox control to that dataview. > I'm trying to filter the dataview based on values in a second table > (ReportsTo) in the same dataset, and it's not working. > > If it's possible to do this, please help me figure out what I've done wrong > and how to implement this correctly. If I'm trying to do something that is > not supported, I'd appreciate any suggestions of a workaround. > > Here's the filter I'm trying to implement: > > strFilter = "SCPMasterID <> '" & uidSelectedPerson.ToString & "'" > strFilter &= " AND SCPMasterID NOT IN" > strFilter &= " (SELECT ReportsToStaffID FROM ReportsTo" > strFilter &= " WHERE ReportsToBossID = '" & uidSelectedPerson.ToString & > "')" > > frmMain.dvReportsTo.RowFilter = strFilter > > Here's the DataException that I'm getting: > > System.Data.SyntaxErrorException: Syntax error: Missing operand after > 'ReportsToStaffID' operator. > at System.Data.ExpressionParser.Parse() > at System.Data.DataExpression..ctor(String expression, DataTable table, > Type type) > at System.Data.DataExpression..ctor(String expression, DataTable table) > at System.Data.DataFilter..ctor(String expression, DataTable table) > at System.Data.DataView.set_RowFilter(String value) > at StarContactPro_02_DT.modContact.SetReportsToFilter () in d:\Data\Visual > Studio Projects\StarContactPro-02-DT\modContact.vb:line 107" > > > Thanks for your help. > > - Jeff > > Nov 20 '05 #6

 P: n/a Hi Jeff, First of all, I would like to confirm my understanding of your issue. From your description, I understand that you need to filter records that are not in the Guid list. If there is any misunderstanding, please feel free to let me know. Based on my research, the Guid values in the list are strings that cannot be converted implicitly to Guid. We have to use Convert function to change it to Guid. Here is an example: "SCPMasterID <> '23865682-20c1-11c4-8000-8ea6b5cb2452' AND SCPMasterID NOT IN (Convert('3ac54427-20c1-11c4-8000-8ea6b5cb2452', 'System.Guid'), Convert('3ac54429-20c1-11c4-8000-8ea6b5cb2452', 'System.Guid'))" HTH. If anything is unclear, please feel free to reply to the post. Kevin Yu ======= "This posting is provided "AS IS" with no warranties, and confers no rights." Nov 20 '05 #7

 P: n/a Thanks, Guys - I've been able to get this to work using William's and Kevin's recommendations. - Jeff "Kevin Yu [MSFT]" wrote in message news:V1*************@cpmsftngxa10.phx.gbl... Hi Jeff, First of all, I would like to confirm my understanding of your issue. From your description, I understand that you need to filter records that are not in the Guid list. If there is any misunderstanding, please feel free to let me know. Based on my research, the Guid values in the list are strings that cannot be converted implicitly to Guid. We have to use Convert function to change it to Guid. Here is an example: "SCPMasterID <> '23865682-20c1-11c4-8000-8ea6b5cb2452' AND SCPMasterID NOT IN (Convert('3ac54427-20c1-11c4-8000-8ea6b5cb2452', 'System.Guid'), Convert('3ac54429-20c1-11c4-8000-8ea6b5cb2452', 'System.Guid'))" HTH. If anything is unclear, please feel free to reply to the post. Kevin Yu ======= "This posting is provided "AS IS" with no warranties, and confers no rights." Nov 20 '05 #8

### This discussion thread is closed

Replies have been disabled for this discussion.