SQL Reporting :: Query Designer Returning Different Results To SSMS?
Jan 29, 2010
I'm having an issue with SSRS 2008 inside VS 2008, where I have a report that is connected to the database via a Data Source which is working correctly, but when I execute a query inside the query designer to test my data, it returns zero records.
The issue I'm having is that the EXACT same query inside SSMS returns the correct record. I'm really stumped as to why it all is connected, but not returning the record. I have another report in the solution that uses the same database, but that one actually works ok.
The SQL is below - each result is coming up 3 times rather than just once.
SELECT Consignments.LegacyID, TripDate, CollectionName, DeliveryName, Pallets, Weight, BaseRate, Consignments.FuelSurcharge, AdditionalCharges, BaseRate * Quantity AS 'InvoiceValue', Consignments.Customer, InvoiceNumber, CASE WHEN child.LegacyID = Consignments.Customer THEN child.LegacyID WHEN parent.LegacyID = Consignments.Customer THEN parent.LegacyID ELSE this.LegacyID END AS 'InvoiceAcc' FROM SageAccount this LEFT JOIN SageAccount parent on parent.LegacyID = this.InvoiceAccount LEFT JOIN SageAccount child on this.LegacyID = child.InvoiceAccount JOIN Consignments on (Consignments.Customer = this.LegacyID AND this.Customer = 'True') OR (Consignments.Customer = parent.LegacyID AND parent.Customer = 'True') OR (Consignments.Customer = child.LegacyID AND child.Customer = 'True') WHERE (this.LegacyID = @Customer) AND (TripDate BETWEEN @fromdate AND @todate) AND (InvoiceNumber IS NOT NULL)
The SQL was given to me for another similar query, but this time I have modified it to try to use it for the query I'm doing now, so I'm assuming that I'm doing something stupid.
I have a query using LINQ-to-SQL. It queries an underlying database table Rooms. It uses Where conditions to narrow down the results, namely:
Gender. Current Occupancy < Max Occupancy Available Flag is Checked
I know this should return results but it keeps returning an empty set. Any ideas? Code is below
Dim selectedHalls = (From sh In dbHalls.Rooms _ Where sh.gender = Session("gender").ToString _ Where sh.max_occupancy > sh.current_occupancy _ Where sh.is_available = 1 _ Select sh.building_name).Distinct()
UPDATE: I've verified that the issue is with the statement where sh.is_available = 1, which doesn't make sense since this is a bit field.
I'm trying this example: [URL] I got it to work before, like 6-months ago. I'm coming back to this now and I can't seem to get the example working. Do I need to create a query in SSMS and then set up my Master/Detail views off of that? It seems like the GridView is NOT linked to the DropDownList and it seems like there is no way to link the two Objects.
I am hoping someone can point me in the right direction here. I am trying to create web service that will return ajaxified results. Specifically, I want to write a web service that will fetch email through a secure connection. However, rather then have the web service return every single email, I just want to fetch maybe 5 emails at a time. I've always used Ajax as a client helper technology and not sure how to go about implementing this on a server side, or if its even possible.
Edit: I solved my problem but if you have anything to add do. Note: I did not create the DB it was created by Wordpress hosted on GoDaddy with my site I have a MySql Database called "wordpress" (for clarity). I want to be able to grab the most recent post from my blog and show it on the landing page for my url. So my thought is this: connect to the MySql DB, run a query to grab the most recent post, display the post.
I built a class to handle the connection and process the request:
public class DAL { private string connectionString = "DRIVER={MySQL ODBC 3.51 Driver}; SERVER=[server here]; PORT=[port]; DATABASE=wordpress; USER=[user name here]; PASSWORD=[password here];"; private OdbcConnection blogConnection;
In my web form I have a search box. The user enters a string, and once the search button is clicked, I pass the value from my .js file to my .cs file and do some processing to get the results. Up to this point everything is fine, but now I dont know how to get my results back to my .js page. The serach results at this point are in a dataset, and also in an multidimensional array. I have a multi array because I need to pass back the code and description of the items found.
[Code]....
At this point I have used the passed string 'value' to get my required code/descriptions, but i'm not sure how/if I can get either the Dataset or multidimensional array back to my .js page
I have a procedure that is used on a search page.. im trying to provide a highlevel resultset which you can then click on a record to view all records associated with it.
My expectation is to return 5 columns.. the ID and Date is what i want to group on.. so that if there are 10 records for ID=5 and Date=10/25/2010 then in my results i would have 1 record. Once im done with the page, you can then click on that line in the gridview to view the 10 records for that id. Does that make sense?
I have the following query, but for the criteria i entered, im getting back 3 records instead of just 2 since there is a differnet reboot count, BUT i really still just need overall data. so it should be only 1 record and seen below is what is returned and what is expected..
I am trying to create a search page, this allows the admin to search through the entries in the database by certain criteria, such as province (like state but diff country)
Here is the code I have so far. The problem is that I am not getting any errors. But I am also not getting any results. The page just posts back and returns to the blank search page. The GridView does not display any results.
here is my code as it stands:
Imports System.Data Imports System.Data.SqlClient Imports System.Web.Configuration Partial Class administration_Search Inherits System.Web.UI.Page Protected Sub ProvinceButton_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ProvinceButton.Click 'get the search string from text box Dim Search As String Search = ("%" & ProvinceButton.Text & "%") 'if the string has value then continue with search If Len(Trim(Search)) > 0 Then Dim con As String = WebConfigurationManager.ConnectionStrings("fubar").ToString() 'build sql string Dim s As String s = ("SELECT id, surname, name FROM orders WHERE province like @strSearch") 'declar connection Dim c As SqlConnection = New SqlConnection(con) 'add command Dim x As New SqlCommand(s, c) 'add parameter x.Parameters.AddWithValue("@strSearch", strSearch) c.Open() Dim r As SqlDataReader = x.ExecuteReader GV.DataSource = r GV.DataBind() c.Close() Else Province.Text = ("Please enter search terms") End If End Sub End Class
On the .aspx page I have a textbox (id: province) and a button (id:submit)
Gridview is not being populated now that i built my objectdatasource in the code behind.. why. If i build the source on the aspx page and configure it that way, it returns results.. but when i try to build it in the code behind it just displays my empty template for the gridview.
I am facing a big problem with simple linq query.. I am using EF 4.0..
I am trying to take all the records from a table using a linq query:
var result = context.tablename.select(x=>x);
This results in less rows than the normal sql query which is select * from tablename;
This table has more than 5 tables as child objects (foreign key relations: one to one and one to many etc)..
This result variable after executing that linq statement returns records with all child object values without doing a include statement.. I don't know is it a default behavior of EF 4.0 . I tried this statement in linqpad also..but there is no use... But interesting thing is if I do a join on the same table with another one table is working same is sql inner join and count is same..but I don't know why is it acting differently with that table only.. Is it doing inner joins with all child tables before returning the all records of that parent table?
A co-worker of mine just came back from a SQL Server Reporting Services class (we are using SQL Server 2005). In that class they were told that if they had a one to many relationship that they would have to flatten the results into one row of the resultset in order to show the 'many' portion of the relationship. For example, my company has two tables:Jobs Notes/Memo'sor each Job we can have 0 or more Notes/Memo's.According to my co-worker the Report Server must have each of the Notes/Memo's in one row of the resultset. That we therefore have to flatten the list of Notes into Note1, Note2, Note3...etc fields.Is this correct? How would I handle a situation where I didn't know how many Notes there were?
I had requirement that i need to display four charts in one report. The data for the report is given through stored procedure. In this stored procedure each chart have its own result set. ie., the Stored Procedure is returning four result sets.
For some reason my query is returning multiple states. It will return records for CA, TX, AZ. All I want is NY that's it and to me it looks really clear.
SELECT [name1], [name2], [mail_street1], [mail_street2], [mail_city], [mail_state], [mail_zip], [phone], [website] FROM [flat_list] WHERE (([rl] = 'x') OR ([rs] = 'x') AND ([mail_state] = 'NY')) ORDER BY [name1], [name2]
i have some query that fills a table and when i test it in the data tab, it shows more than 100 records, but when i try it on the preview tab it only show 8, i'm using the exact same parameters and i get different result,
I have a page where users can search by company name, the data list is pulled from an Excel spreadsheet hosted on the site. I'm seeing some strange results returned, for example:
In the company field say there are the following entries:
Anns Company Bernards Business Company B Dermots Domain Property Company
If I search for "Com" I get the following returns (correct):
Anns Company Company B Property Company
If I search for "Der" I get my "Sorry there are no matching results" message (Incorrect)
If I search for "Ann" I get the following returns (Correct):
Anns Company
And if I search for "e" I get the following returns (Correct):
Bernards Business Dermots Domain Property Company
It seems random as to why it doesn't return some values, I've tried moving rows around to see if that's the problem but it makes no difference. My range is set correctly in the spreadsheet.
When I debug the VB code behind, when I search for something that works correctly e.g. "Com" in the above example, once enter the value and click search Visual Studio begins steping into the code and shows me the contents of the variables however when I search for something that's not working e.g. "Der" and click search it immediatly returns the "Sorry there are no matching results" message and the code behind does not get steped into.
I have a simple model that contains about 6 tables dealing with customers. As long as I am dealing with one table at a time or returning the single or list of an entire class everything is great. I am struggling with having to return subsets or combinations of the class information. For example customer sales by year.
This query involves these classes:
Customer CustomerOrder
I need to return something like
CustomerName Year TotalSales
I can build the query fine. Returning that to a model is what puzzles me. I have no class that I form a generic.list with and nothing to inherit on the view. What is the best way to handle this?
How do you delete more than one entity at the same time in the designer Entities list. it seems that the designer interface only allows the selection of 1 entity at a time....
I have integrated sandbox paypal into my application.After successful payment i am not getting success in the query string.Instead i get is return to merchant but if i do cancel i get the cancel in the query string
I'm using a JQuery plugin [URL] to add auto-completion to a "city" textfield. The component calls an ASP.NET page that simply loads an array of all possible city values (>8000) and then iterates that array returning those that start with the text the user has so far entered.
The thing is, it's pretty slow in real use. It lags behind what the user types to the extent that most of the time the user probably won't notice that it's there.
So, my question is, how can I speed it up?
I had thought that an array would be a better way to go than putting the data in a database and having to hit that multiple times. Do others agree that having this information hard-coded is the way to go given that it's not at all volatile and needs to be all about speed of return?
If so, what would you look at to improve the speed of performance? Should I be caching the data on application start and accessing it from memory? Would I be better off instead with multiple arrays, each containing values starting with a particular letter so I can go straight to the relevant one and thus iterate a much smaller array? Or am I missing a much more obvious way to go about this?
