.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Problems with AND/OR in SQL query in strongly type datasets

Posted By:      Posted Date: October 22, 2010    Points: 0   Category :ASP.Net


In my ASP.NET web application I am using strongly typed datasets for data access. In my dataset.xsd class I have added an SQL query that works in SQL Management Studio, and when executed in the Table Adapter Query Configuration Wizard --> Execute Query form. But when invoked from the application using the autogenerated table adapter it returns no hits.

INNER JOIN Department ON Department.Dep_Id = Device.Dev_Dep_id 
INNER JOIN Customer ON Customer.Cus_Id = Department.Dep_CustomerNr 
INNER JOIN RootCustomer ON RootCustomer.RCus_Id = Customer.RCus_Id
(RootCustomer.RCus_Id = @CustomerId) AND (Device.Dev_Status = 10) AND (Device.Dev_serial LIKE '%' + @searchPattern + '%') OR
(RootCustomer.RCus_Id = @CustomerId) AND (Device.Dev_Status = 10) AND (Device.Dev_CustMark LIKE '%' + @searchPattern + '%') OR
(RootCustomer.RCus_Id = @CustomerId) AND (Device.Dev_Status = 10) AND (Device.Dev_IpAddress LIKE '%' + @searchPattern + '%') OR
(RootCustomer.RCus_Id = @CustomerId) AND (Device.Dev_Status = 10) AND (Device.Dev_ArticleNr LIKE '%' + @searchPattern + '%') OR
(RootCustomer.RCus_Id = @CustomerId) AND (Device.Dev_Status = 10) AND (Device.Dev_Descr LIKE '%' + @searchPattern + '%')
ORDER BY Device.Dev_serial

What possib

View Complete Post

More Related Resource Links

UnTyped DataSets and Strongly Type DataSets

We all are use datasets as a means of carrier of data from one layer to another. Most of the time we are using weakly typed datasets. In this article I will explain the differences between weakly typed datasets and strongly type datasets

Data Points: Efficient Coding With Strongly Typed DataSets


Someone once said to me that the hallmark of a good developer is the desire to spend time efficiently. Developers are continually pursuing ways to make coding easier and faster, and to reduce the number of errors.

John Papa

MSDN Magazine December 2004

How to create strongly typed datasets with access parameter queries



How can you create strongly typed datasets using an access database against access select statements that use parameters?

The problem is VS.Net doesn't allow select queries with parameters to be dragged onto a form, it only allows access queries without parameters!

I also tried the dataadapter wizard, but again it only allows me to select queries without parameters?

Many thanks in advance


Sub Query Problems

Apologies if I have not posted this in the correct section.  I am having some difficulties with some sub queries. First I'd like to show the database design. Rez_Desc Table Rez_ID Rez_Number Client_Name Arriving_ID Pickup_ID 1001 201000123 Mr. Ross 1 2 1002 201000124 Mrs. Smith 2 1 Arrival_Desc Table Arriving_ID Label 1 AUS 2 USA OffSite_Desc Table OffSite_ID Label 1 AUS 2 USA Ok so this is my table structure, simplified. This is the query I am trying to run. SELECT Rez_Desc.Rez_Number AS RezNum, (SELECT OD.Label FROM Offsite_Desc OD LEFT JOIN Rez_Desc AS RD ON OD.Offsite_ID = RD.Pickup_ID AND RD.Rez_ID=Rez_Desc.Rez_ID) AS Pickup, (SELECT AR.Label FROM Arrival_Desc AR LEFT JOIN Rez_Desc AS RD ON AR.Arriving_ID = RD.Arriving_ID AND RD.Rez_ID=Rez_Desc.Rez_ID) AS DropOff FROM Rez_Desc And I get the following error:- Msg 512, Level 16, State 1, Line 1 Subquery returned more than 1 value. This is not permitted when the subquery follows =, !=, <, <= , >, >= or when the subquery is used as an expression. To my knowledge and understanding it is being caused because the sub query is returning more than 1 result which it should not be doing.  Seeing as how in my example table above I have 2 entries, both with relevant ID's etc. Need some help!

DropDownList problems : The ViewData item that has the key 'userTYPE_id_user_type' is of type 'Sy

Hello,I've been searching for that problem for hours...I have two table :          -user          -userTypeI'm trying to create a user, wich as a column id_userType, so I'm trying to do a dropdownlist with the userType List I've that error code : The ViewData item that has the key 'userTYPE_id_user_type' is of type 'System.Int32' but must be of type 'IEnumerable<SelectListItem>'. // // GET: /User/Create public ActionResult Create() { //Send to the view, the userTypeList IEnumerable<SelectListItem> userTypeList = new SelectList(_userRepository.FindAllUserType().Distinct().ToList(), "id_user_type", "Description"); ViewData["userType_id_user_type"] = userTypeList; return View(); } here the post controller // // POST: /User/Create [HttpPost] public ActionResult Create(users user) { if (ModelState.IsValid) { try { _userRepository.addUser(user); _userRepository.save(); return RedirectToAction("Index"); } catch { return Vie

manage with two datasets - Who like tricky query ???

I've a big issue I don't know how to manage this with report builder 2.0 I've four table. - SystemUser (systemUserId) - FilteredContact (onwerid, owneridname, contactid, lastname..) - FilteredAppointment (ownerid, stateCode, Subject, ActivityId, CreatedBy, OwnerIdName..) - FilteredActivityParty (ActivityId, PartyId, participationTypeMask...) So basicly the user can create contact, so it becomes the owner of the contact. And user can create appointments, but need to choose a contact as required to the appointment. This contact can be created by the current user or not. So FilteredContact.contactID = FilteredActivityParty.partyId , as a contact is in a list of activityParty. FilteredAppointment.activityId = FilteredActivityParty.activityId So FilteredSystemUser.systemUserId = FilteredContact.ownerId FilteredAppointment.activity.ownerId = FilteredContact.contactId   With all that, I need to get by FilteredSystemUser.systemUserId, the amount of FilteredContact that Im the owner and I need to know how many people(partyid) where Filteredappointment.ownerId=Filteredsystemuser.systemuserid. And I cant find a way to manage this without two datasets. I've no idea how to link thus two datasets between them. Thanks for your help

Analysis Service Oracle Number inconsistent Data Type for TABLE or Named Query

Dear Gurus, I'd VERY OLD PROBLEM. And I believe it addressed since 2006. When I design DataSource Views from Oracle Data Source. I found it return different oracle number data type for TABLE or NAMED QUERY   Provider Data Type Column Data Type Data Source View Data Type Oracle OLE DB Provider (OraOLEDB.Oracle.1) Table Number System.Int64   View Number System.Decimal   Named Querey Number System.Decimal Microsoft OLE DB Provider for Oracle (MSDAORA.1) Table Number System.Double   View Number System.Double   Named Query   1 System.Int64   Named Query   1.1234 System.Int64 Althought I know I can fix IT via MANUALLY EDIT DATASOURCE VIEW XML SOURCE. But I don't think this is a better solution. Is anybody have ideas ?  Wilson

Problems writing a dynamic L2E query

I'm trying to re-work a L2E query to be more dynamic, but I'm not having much luck. Basically, I have two parameters (and many more to come, just laying the foundation), and the parameters are both optional from a user-endpoint.Originally I wrote this static expression:int personnelId = 1234; int divisionId = 1234; var results = (from a in ctx.Attendees from d in a.Divisions join p in ctx.Personnel on a.PersonnelID equals p.PersonnelID where a.PersonnelID == personnelId && d.DivisionID == divisionId select new Attendee { firstName = a.FirstName, lastName = a.LastName });Attendee is a POCO. After I wrote this, I realized that if personnelId or divisionId weren't passed in (or if just one were passed in) I'd want a different result set. I'm conceptualizing the idea like this (doesn't compile, but you get the drift):var results = (from a in ctx.Attendees select new Attendee { firstName = p.FirstName, lastName = p.LastName }); if (personnelId != null) { results = (from a in results join p in ctx.Personnel on a.PersonnelID equals p.PersonnelID where a.PersonnelID == personnelId select a); } if (divisionId != null) { results = (from a in results from d in a.Divisions where d.DivisionID == divisionId select a); } results = results.ToList();Doesn't work well though, because my

Ms sql query for this type of table structure

Hi This is my Table structure Field1       Filed2        001          A001          B001          C 001          D002          A003          B 003          C I need MS SQL query I need to show Field1 which has B and Not in DI mean 003 will shown coz it has B still D not comes... and 001 ,002 will not shown Coz 001 has B and D and 002 will not shown coz still it doesnt have B..Need Query... 

Problems escaping apostrophe when using an XPath query with SelectSingleNode

I have an XML node which I am trying to search for a specific node which has a given value, but I am having problems when the value contains an apostrophe. I have tried replacing the apostrophe with &apos as follows:                string encodedTitle = title.Replace("'", "&apos;");                 string XmlPath = String.Format("item[title = \'{0}\']", encodedTitle);                 return NodeChannel.SelectSingleNode(XmlPath);                string encodedSearchString = searchString.Replace("'", "&apos;");                string XmlPath = String.Format("item[title = \'{0}\']", encodedSearchString);                return myNode.SelectSingleNode(XmlPath);However, this does not seem to find search strings with an apostrophe (it doesn't throw an exception or anything, it just returns null). Search strings without an apostrophe work fine. Is there any way I can fix this?

Where is 'Change query type' (Enterprise manager) equivalent in the Management Studio


In sql server 2000 Enterprise manager, there was a ‘Change Query type’ icon and on clicking it, it gave you the option of changing the current query to a

Insert into
Insert From
Create Table

I can’t seem to find this in SQL Server Management Studio, does it exist somewhere or is there an equivalent way of doing it. Any help would be appreciated.




Problem with SCOPE_IDENTITY in strongly typed datasets



I am developing an ASP.NET site and I am using strongly typed datasets and I am generating them automatically in Visual Studio 2008. I have been using TransactionScope to be able to use several table adapters from different datasets and update them in one transaction. When I create a new row, I use the update method in the table adapter to create new posts. The update method takes a dataset, datatable or a row as argument making them very easy to work with. After I have updated a row, I have generated a ExecuteScalarGetIdValue() call to get the latest inserted ID value. I use "SELECT SCOPE_IDENTITY" and it gives me an exception. When I try the query builder this SELECT SCOPE_IDENTITY is returning NULL. When I ask it in SQL Management Studio SQL Query window it returns a correct value. How can I get the correct value from the table adapter?

        id = this._event.ExecuteScalarGetIdValue();



      return true;

 Best regards, Janhe

Content Query Webpart Missing Content Type and Approval Status


I'm a great fan o the CQWP , and love the changes made in 2010 on this webpart. What bothers me is that I use to Filter Documents by Approval Status, thus showing Draft or Pending Documents and Group these by their Content Type.

Both these were available in 2007, but removed from 2010 CQWP. Any idea why, or how to bring this back? sample or URL guidance would be great. My gut feeling is custom XSLT but id like to confirm this first.

Thx a lot



problems in pivot query




             I am using following pivot query  but not getting result as single row getting as 4 rows.

One more problem i am unable to pass dates as parameters to storeprocedure something like this

select [@date] .

        SELECT 'Forecasted' AS HeadCount,
[8/1/2010], [8/8/2010], [8/15/2010], [8/22/2010]
FROM TblEmpCount) AS SourceTable
FOR StartDate IN ([8/1/2010], [8/8/2010], [8/15/2010], [8/22/2010])
) AS PivotTable;

I am getting result as 4 rows but i want result in single row like this

HeadCount    8/1/2010     8/8/2010    8/15/2010    8/222/2010

Forecasted     191                182                 176                169





database query problems


Hello All, I have a result table in MS SQL Database server (2005).

Subject   Exam                 SubExam   Year     Marks

Math       First Terminal    CT              2010    18
Math       First Terminal    Hall Exam   2010    67.2

Science   First Terminal    CT              2010    20
Science   First Terminal    Hall Exam   2010    78

Now how can I get the result followningly

                                First Terminal

                        CT       Hall Exam      Total


query problems


Hello All, I have a result table in MS SQL Database server (2005).

Subject   Exam                 SubExam   Year     subjective objective practical

Math       First Terminal    CT              2010    18             0              0
Math       First Terminal    Hall Exam   2010    38             46            0

Science   First Terminal    CT              2010    20             0              0
Science   First Terminal    Hall Exam   2010    32            40      &

SELECT and UPDATE query problems



I have a web form that should display advertisements depending of the number of impressions (number stored in a database) and I also need to update the number of impressions when page is loaded. The advertisements are displayed on a web page, but the problem is I don't know where to insert the SqlCommand for updating number of impressions.

Labels are used for displaying the number of impressions.  

        String connectionString = WebConfigurationManager.ConnectionStrings["CMS"].ToString();
        using (SqlConnection connection = new SqlConnection(connectionString))

            using (SqlCommand commandReklama = new SqlCommand("SELECT * FROM Reklama", connection))
                SqlDataReader readerReklama = commandReklama.ExecuteReader();
                while (readerReklama.Read())
                    string redirectUrl = readerReklama.GetString(1);
                    string url = readerReklama.GetString(2);
                    int brojPrikazivanja = readerReklama.GetInt32(3);
                    bool show = readerReklama.GetBoolean(4);                  
                    string naziv = readerReklama.GetString(5);
                    int location = readerReklama.GetInt32(6);

                    if (
ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend