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


Top 5 Contributors of the Month
Kaviya Balasubramanian
Sgraph Infotech
Imran Ghani
Post New Web Links

huge select issue

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

Hi,

I am having alot of trouble trying to find out how to run a query. I dont know if a view is the best way to go, and if it is which fields to link in within the view.

I have 2 tables, one is 'contacts' and one is 'resume'.

users insert data into the resume table, with their username in the 'User Id' column.

the contacts table is unrelated, but I only want to show table resume data to users if they share a row in the contacts table with the other user and the permissions are a certain amount.

On a page, id like rows from the resume table to show (where User Id = the querystring 'id'), but only show if the signed in user (Profile.UserName) is on a row (in column 'theiruserid') with the url querystring 'id' username (in column 'myuserid'), and that the 'permissions' column of the resume table is '1', or the 'permissions' column of the resume table matches the number in the contacts table (on the row that both usernames are on).

How do i do this?

would a view work? if so, how do i set up this view? which columns need to be linked? and then what select statement can be used?

I have been through so many select statements and different view combinations, thanks so much if you can help







View Complete Post


More Related Resource Links

SQL Server 2008 Linked Server SELECT INTO issue

  
I have a SQL Server 2008 installation running on a clustered Windows Server 2008 R2. I am trying to execute a query on a remote SQL Server to create a table. In order to do so, I call a stored procedure on the remote SQL Server. The stored procedure's description is as follows: ------------------------------------- Create procedure [dbo].[USP_RemoteExec] @varSQL varchar(max) as declare @tempSql nvarchar(max); set @tempSql = CONVERT(nvarchar(max),@varSQL); exec sp_executesql @tempSql ------------------------------------- I pass the following command to the remote stored procedure as follows: exec [servername].[dbname].dbo.USP_RemoteExecute @varSQL='if object_id(''testdb.dbo.tmp_testEmpty3'') is not null drop table testdb.dbo.tmp_testEmpty3; create table testdb.dbo.tmp_testempty3(col1 int, col2 varchar(20));' go select * from [servername].testdb.dbo.tmp_testEmpty3 go This statement returns the following: --------------------------------- col1,col2 (0 row(s) affected) But, when I run the following statement: exec [servername].[dbname].dbo.USP_RemoteExecute @varSQL='if object_id(''testdb.dbo.tmp_testEmpty'') is not null drop table testdb.dbo.tmp_testEmpty; select * into testdb.dbo.tmp_testEmpty from (select top 10 mytab.[col1] as [col1] , mytab.[col2] as [col2] , mytab.[col3] as [col3] FROM testdb.dbo.mytab as [mytab] with (nolock)) as A' go select * from [

SQL Server 2008 Linked Server SELECT INTO issue

  
I have a SQL Server 2008 installation running on a clustered Windows Server 2008 R2. I am trying to execute a query on a remote SQL Server to create a table. In order to do so, I call a stored procedure on the remote SQL Server. The stored procedure's description is as follows: ------------------------------------- Create procedure [dbo].[USP_RemoteExec] @varSQL varchar(max) as declare @tempSql nvarchar(max); set @tempSql = CONVERT(nvarchar(max),@varSQL); exec sp_executesql @tempSql ------------------------------------- I pass the following command to the remote stored procedure as follows: exec [servername].[dbname].dbo.USP_RemoteExecute @varSQL='if object_id(''testdb.dbo.tmp_testEmpty3'') is not null drop table testdb.dbo.tmp_testEmpty3; create table testdb.dbo.tmp_testempty3(col1 int, col2 varchar(20));' go select * from [servername].testdb.dbo.tmp_testEmpty3 go This statement returns the following: --------------------------------- col1,col2 (0 row(s) affected) But, when I run the following statement: exec [servername].[dbname].dbo.USP_RemoteExecute @varSQL='if object_id(''testdb.dbo.tmp_testEmpty'') is not null drop table testdb.dbo.tmp_testEmpty; select * into testdb.dbo.tmp_testEmpty from (select top 10 mytab.[col1] as [col1] , mytab.[col2] as [col2] , mytab.[col3] as [col3] FROM testdb.dbo.mytab as [mytab] with (nolock)) as A' go select * from [

Strange issue with a huge SSRS 2008 report (supposed to return 3 million records)

  

Hi,

NOTE: The strange part (as mentioned in title) will come in the end.

I am running a SSRS 2008 report which fetches 3 million records from a remote server. After around 1 hour the report processing stops and I see an error icon on the lft bottom of the browser window. When I click on that to see the error details it shows some PageRequestManagerSQLErrorException with an unknown error message with code 12029 (sometimes 12002).

When I see the reportserver logs there is an error message logged in it which says "Microsoft.ReportingServices.Library.ReportServerDatabaseUnavailableException: The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing. ---> Microsoft.ReportingServices.Library.ReportServerDatabaseUnavailableException: The report server cannot open a connection to the report server database. A connection to the database is required for all requests and processing. ---> System.InvalidOperationException: Timeout expired.  The timeout period elapsed prior to obtaining a connection from the pool.  This may have occurred because all pooled connections were in use and max pool size was reached."

The <DatabaseQueryTimeOut> value in the report server configuration file is already having a value set to 7200 seconds(2 hours).<

SQL & JavaScript - Apostrophe Issue, SELECT statement is not returning any results.

  

Hi,

Any help would be great on this.

I have the following code within an .asp page in JavaScript:

function SDsitecalls1(Sitename)

SQL = "SELECT COUNT(*) FROM [SDADMIN].[SRFILE] WHERE SRSITE = 'Our Lady''s R C Sports College - Hbev' AND SRCOMPLETE=0 AND SRINCOMP=1 AND SR_Group=38;"
 tempval=connection.execute(SQL)
 response.write(tempval(0))
end function

The function is called via the below:

 <td style="width: 114px" class="style65"><a href="/suppdesk/customercalls.asp?customer=BSFMAN&site=Our+Lady''s+R+C+Sports+College"><%=SDsitecalls1("Our Lady's R C Sports College - Hbev")%></a></td> 

When this is ran I do not receive any errors but the SELECT command doesn't return any results (Just a 0). I've tried a few different methods such as using the Parameter SiteName and Single/Double Apostrophes however I either receive an error or blank results.

The function works with all other names however the apostrophe seems to cause issues here. Is there a way to extract the data without editing the SQL?

Thanks very much for the help,

Jas


Performance issue with select query

  

Hi,

I have simple select query as below

SELECT DISTINCT BCEGPrsLinesCd, BCEGCmlLinesCd,CountyFedInfoPrcStdNbr

FROM TPSeleBCEGAddress WITH(nolock)

WHERE LTRIM(RTRIM(ZIPCd))='1000'

AND LTRIM(RTRIM(StateCd))='MI'

AND LTRIM(RTRIM(CITYNM))='AFTON'

AND LTRIM(RTRIM(PrefixStreetDirCd))='E'

AND LTRIM(RTRIM(StreetNm))='OAKGREEN'

AND LTRIM(RTRIM(STREETTYPECD))='S'

AND 1000>= CONVERT(INT,(SUBSTRING(LOWAdrNbr,0,CHARINDEX(' ',LOWAdrNbr))))

AND 1000<= CONVERT(INT,(SUBSTRING(HIGHAdrNbr,0,CHARINDEX(' ',HIGHAdrNbr))))

AND (LTRIM(RTRIM( BCEGOddEvenCd ))='E' or LTRIM(RTRIM( BCEGOddEvenCd ))='B')

AND LTRIM(RTRIM(BCEGEffectiveYr))='1998'

Table TPSeleBCEGAddress has 64 Lakhs records in it and it takes around 22 Seconds to execute this query. Please let me know a way to improve performance of this select query.

Primary key defined on this table comprises of 16 columns.

Regards

Ravindra

 

 

 


Strange time issue on simple select statement

  

Hi, I have a strange time out issue on a simple select statement.
This is my select statement:

Select * from DyGeJournal where ord>=4609635 and Fracsec>=31

The ord and Fracsec is the primary key.
When I ask for the actual excecution plan, he is using a an index seek.
It takes more than 1,5 minutes to get a result back.

When I shrink my database, everyting is running as expected, the query above runs in milliseconds.
After a while (2 weeks) it start agian running very slow.

I have the same logic on other tables, but I don't have the problem on those tables.
What can cause this problem?


SELECT issue. to show bookings

  

i have the following T-SQL query that will output details of holiday bookings

 SELECT DateFrom, Quantity, Duration -- SUM(Quantity) 

	FROM BookingItem 

	JOIN Booking ON BookingItem.BookingRef = Booking.BookingRef

	WHERE (BookingItemStatusCode IN ('FRM','FRP') OR (BookingItemStatusCode = 'PRV' AND GETDATE() >= DATEADD(DAY, ProvisionalDays, Booking.CreatedDate)))

	AND OptionCode = '2B'

	AND DateFrom BETWEEN '01/JUL/2010' AND '31/DEC/2010'

	AND UnitTypeCode = '5P'

	ORDER 

Dataset select issue

  

Hi there,

I have written some code using data sets (which i am new to). I have brought back all of the rows, which is fine, but i need to select certain customers according to if they are active

SqlConnection cnn = new SqlConnection(@"Data Source=192.168.100.2;Initial Catalog=intranet;User ID=sa;Password=xxxxx;");
        SqlDataAdapter cmd1 = new SqlDataAdapter("select * from clients WHERE active = 1", cnn);

        DataSet ds = new DataSet();
        cmd1.Fill(ds, "clients");

        SqlDataAdapter cmd2 = new SqlDataAdapter("select * from tbl_imalive", cnn);
        cmd2.Fill(ds, "tbl_imalive");

        ds.Relations.Add("myrelation",
        ds.Tables["clients"].Columns["id"],
        ds.Tables["tbl_imalive"].Columns["customerID"]);

        parentRepeater.DataSource = ds.Tables["clients"];
        Page.DataBind();

&nb

AJAX GridView Checkbox Select All in ASP.Net

  
In the previous article GridView Select Multiple Rows you learnt the multiple row selection in GridView control. Here you will get the C# code sample to select all the gridview rows using single checkbox server control. You can highlight the selected rows with different color and Font state Bold. If you want to use the selection values on different ASP.Net

IsapiModule Error 500 Issue

  

I am about exhausted trying to figure out this error:

HTTP Error 500.0 - Internal Server Error

Description: The page cannot be displayed because an internal server error has occurred.

Error Code: 0x8007007b

Notification: ExecuteRequestHandler

Module: IsapiModule

Requested URL: http://localhost:80/ReportServer

Physical Path: c:\Program Files\Microsoft SQL Server\MSSQL.2\Reporting Services\ReportServer

When I enable Failed Request Tracing Rules and view the log output I am supposing that the actual problem is in the 2nd call where there are 2 backslashes before the actual filename in the specified path:

ISAPI_START

CALL_ISAPI_EXTENSION DllName="C:\Windows\Microsoft.NET\Framework\v2.0.50727\\aspnet_isapi.dll"

 

Select value in gridview

  

Hi, I am new to gridview. I want to make a gridview. Its row when selected should direct me to a specific .aspx page.Each row should direct to a different page. Should I use querystrings for this? Please suggest. Regards, ap.


MVC2 issue - Stuck. Trying to create a cms with nested partials

  

I am currently trying to display multiple items on a single page. I am not sure as to how to tackle this. What I have is a database table that has all the page data in. I have this run as a PagesController and this works fine. However what I would like to do is, if say the Products page is selected via the menu, I want to pull back not only the Products page html but also get all the products, and even be able to pull them through via category. I was trying to use a partial view, but I can't get it to work. The same would be for other pages, having partial views to display the other content, ie if Gallery is selected, the page html for Gallery shows and it then can push to a partial view that pulls all the gallery images from the gallery db table and so on. 

Maybe I am tackling this the wrong way. I have the pages stored in the db to allow for a CMS system I have setup. Maybe I need to use models for all the other items(Products, Gallery, Videos and such) and use the partial for the page html?

Any help would be greatly appreciated as I am confused.




huge number of data from database ... so how to minimize load time

  


Hi ...

I have used the above method to configure my crystal report ...

Its working fine , But i have huge number of data in database ....

So , it take long time to load the report ...

So , i have decided to show only the last 50 records inserted into the database , not all the 500 records to be loaded every time.

Plz help me with this issue  ...

 

Also , Plz explain me abt index legend in crystal report.


xpath issue

  

Hi i am using xpath to loop through each node,

the xml is below i can get the value of "link" but i cant get the thumbnail value, have you got any ideas how i can do this, my code is below.

Thanks

<item>

     <link>test 1</link>
     <media:thumbnail url="http://test.com/image.jpg" height="75" width="75" />

</item>

<item>

     <link>test 2</link>
     <media:thumbnail url="http://test.com/image.jpg" height="75" width="75" />

</item>



public void FindLinks(XPathNavigator p_xPathNav, DataTable dt)
        {           
            //run the XPath query
            XPathNodeIterator xPathIt = p_xPathNav.Select("//item");

            //use the

User validation Issue in xlsx file

  

Hi,

I am validating user from Login.xlsx file. It was working fine by validating email and password from that file. For testing i got new file same contents of file but added some more user names.

So after that when i validating the user i am getting the following error "Data type mismatch in criteria expression".

But when i replaced the new to old one it is working fine. The contens of both files are the same. So why this kind of error happened.

-Thanks


Multiple Paramatized Threading issue

  

I need the funcitonality to have two methods run concurrently of each other and the parent thread (aka the method that makes these two calls) to halt until BOTH are done. After the threads are done, I need to perform logic on the returns of the method, so it is essential for both of them to be complete before the parent thread continues. I have searched the internet and tried varous attacks, and I always come up with the same result, this being the parent thread continues operation after the first thread finishes, leaving the second thread running.  Can someone show me a quick example of how I can do this. I have tried checking states of the threads, halting the threads, but I always end up with the same thing. Any help would be great. Thanks.


Pattern for huge logic

  

Hi,


I am looking for a pattern/way to code for implement Huge logical hierarchy, Basically this is to verify person details like qualification, experience, expertised-in and global region.... and issue certain access for a perion of time.

The verification process has huge number of conditions it took around 1 year to document. And now we have to develop C# to implement it, can any one help me...? a sample code will be appreciated.


Thank you

Harsha


Categories: 
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