DataSource Controls :: Query Request For A SQL Server Based Interview Question?
Feb 26, 2010
This was asked during one of my interview.I was not able to answer could someone help me out to write a query for this With the above table.We should have a qry written so that it shows the following result
ItemNo refno Process Name Qty 001 1 A John 50 001 2 A Jon 150 002 3 B Kalis 100 003 4 A Bob 300 Second Table ItemNo Newrefno Process Name Qty 001 001 ZZ peter 50 001 001/a ZkZ Joe 70 002 002 Ab Ray 100 Result Set 001 A John 200 001/a ZZ ZkZ peter joe 50 70 002 B Kalis 100 002 Ab Null Ray Null 100 Null 003 A Bob 300 null null null null null null null
Here first row of the result set explanation:
up to this 001 A John 200 based on first table sum...
and 001/a zz ZkZ peter joe 50 70
here 001/a newrefno of 001 so i need to show all the ItemNo 001 record in one row like the above resultset.
By right clicking on my database i created a query in server explorer. But where are this query stored can't find them back. I should aspect that their is a folder query's like there is a folder tables but this isn't the case.
Is there any possibility to execute the job in sql server. I want to create the Job. then through the quer i want to execute this Job by passing parameters. can we able to do this.Actucally i want to execute the SP from front end. but suddenly it should respond to the page. but that SP will run for nearly 5 minutes.
i have a table for customer forecast which contains customer, week number and quantit as columns. now i need to write query and list the week as column for each customer and each cell should have a quantity. how to write query for this using pivot logic. i dont want to use cursor.
I cant seem to find a good example of how to build the query logic. We are allowing users to search based on lat/long and need to add radius as another parameter. So you enter 29.30125 as the latitude and -95.04590 as the longitude and pick say 15miles as the radius, i need to return all records that fall within that radius.. can someone suggest a good site to review this type of query?
One of my peer wrote a query based on multiple tables using joins. One of the table is in a different database The query was taking much time
1. Create an SP 2. Create a temp table and copy all records from the external db 3. Avoid using Left Join as much as possible and use Inner Join 4. Create and use indexes 5. Remove tables/columns that are not necessary
In this scenario, I would like to suggest to use Covering Index, but how can it be created for multiple tables and a temp table?
I'm implementing a BLL layer and have a few questions. First, in my DAL, there is a contacts table, the select method is a sql query against a sql server 2000 database. The preview data returns everyting as expected. The query is
[Code]....
The BLL method is built like so
[Code]....
When using the BLL method to a gridview, the results of the subquery are not displayed. There are also other methods, each having a different where clause for ContactRole_ID and each is having the same issue of not displaying the subquery reult in the gridview. No error, but no data is displayed for the subquery either. Here is the templatefield of the gridview
[Code]....
Another item that is causing an error is in trying to update a record. Here is the DAL update method
[Code]....
And the BLL update method
[Code]....
The error says i have extra parameters, but have no clue where the extra parameters are being picked up. The error isObjectDataSource 'odsContacts' could not find a non-generic method 'UpdateContact' that has parameters: _ContactID, _Active, _ContactName, _Office, _VirtualOffice, _Pager, _Fax, _Home, _Cellular, _Email, _ContactRole_ID, Active, ContactRole_ID, ContactName, Office, Email, VirtualOffice, Pager, FAX, Home, Cellular.And the objectdatasource
1. I have a GridView on my page and it uses sqldatasource with parameterized query. What I want to do is, on page load (where nothing has been selected so no parameter supplied), I want it to query everything (something like SELECT * FROM [this_table]) but since my SelectCommand is something like
SELECT * FROM [this_table] WHERE [this_column] = @someParameters AND [that_column] = @someParameters.
Can I play around with default value to achieve something like that but how ? Now, when the page loads, it doesn't show anything (No Gridview).
2. On my page, I made something like (username, gender, address, and more) and one single search button. That means, no single control enable auto postback. What I am trying to accomplish is building dynamic query
(if username specifed -> SELECT * FROM [this_table] WHERE [username] LIKE @username).
If both username and gender are specified (SELECT * FROM [this_table] WHERE [username] LIKE @username AND [gender] = @gender) and you know the rest. How can I do this using GridView and SqlDataSource ? To my knowledge, I can only specify one SELECT statement in a sqldatasource.
Dim MediaQuery = From m In dB.DOWNLOADS _Where m.ID = id _Select
which returns a record from the database. One of the fields in the record is a FK to another table. I need to read this value to be able to set a dropdown based on the ID but I can't work out how to access it. For a standard record I can just do the following txtTitle.Text = MediaQuery.FirstOrDefault().TITLE
However with the foreign key it doesn't work like that. I've tried drpGroup.SelectedIndex = MediaQuery.FirstOrDefault().DOWNLOAD_GROUPS.ID where DOWNLOAD_GROUPS is the FK field but it returns Object reference not set to an instance of an object. If you're simply wanting to read some values from a single db record in the entitiy framework and one is a foreign key how should I go about getting the value?
I'm trying to make a dropdown list with data available based on the users login name. Heres what I have as my SQL Query in the SQLDataSource:
SELECT Client_Name, Client_ID FROM Client WHERE (Client_Name LIKE '%@Client_Name%')
So say if the login username is "User1" but the database client name is "User1 Steel" I want the dropdownlist to pull "User1 Steel" as the display and Client_ID as the value from the dropdown list.
I am having problems with the following update query that is part of an SQL Datasource. This query was automatically generated by VS2008 and I have formatted it so that it is easier to read.
Everything works but the [ZIP+4] field which does not update. I believe that VS2008 added the square brackets around the field name because of the plus character that it contains. If I change the ZIP+4 variable name to [ZIP+4] = @ZIP4 it still does not work. This brings up another question, I have never understood where these update queries are getting their data from.
I have an ASP.NET VB website in which I am using the Membership functionality to authenticate users. I am doing this through the Login control on the HTML page. I have working that it will authenticate the user and then opens a welcome screen in which I have a button they can push to open a Reports.aspx page. On that page I have a dropdown box that is being populated from a datasource connection query to my SQL Server database table. It is a hard coded query but want it to query the table off the username text value that the user provided on the login (i.e. select <something> from inventory where username = <username>). I have not behind code but only HTML at this point. Here it is ...
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Login.aspx.vb" Inherits="Login" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server">
[Code] .....
Here is the Reports form that has the dropdown on it:
<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Report.aspx.vb" Inherits="Report" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server">