Comments by Advay Pandya (Top 67 by date)

Do you want to know, where can we find the indexes in SQL server ?

Welcome :)

Always welcome. It happens in the coding world. We developers are not able to find small mistakes of their own code. :)

Your question is not clear. Which kind of result you want ? Do you want to join rows/columns ? or you want the result in 2 different tables ?

Please make sure the database of connection string and database that you are checking are same.

Okay, You might get error on 1 or 2 attempts because I dont have DB structure so I am not able to run the query.
Please replace inner join part from below lines. I think now it will work.

INNER JOIN ( select CNTM_CONTACT_ID,max(CNTM_Active_Date) AS CNTM_Active_Date from CONTACT_MST_LOG b
) T ON CNTM.CNTM_Active_Date = T.CNTM_Active_Date AND CNTM.CNTM_CONTACT_ID = T.CNTM_CONTACT_ID

U can exclude the CNTM_CONTACT_ID condition if you don't need.
Please let me know if you are still facing issue.

I have modified your query as per my understanding. I have removed the where condition of date and put a separate join. The reason of joining is to have multiple where condition with the table.
Please try below and please let me know if it will still not a working solution for you:

select CNTM.CNTM_CONTACT_ID,CNTM.CNTM_ISACTIVE,CNTM.CNTM_Active_Date as ActiveDate,
day(Convert(varchar, CNTM.CNTM_Active_Date ,110)) as ActiveDay,
month(Convert(varchar, CNTM.CNTM_Active_Date ,110)) as ActiveMonth,
year(Convert(varchar, CNTM.CNTM_Active_Date ,110)) as ActiveYear
from USER_MST inner join CONTACT_MST CNTM1 on USRM_CONTACTID=CNTM1.CNTM_CONTACT_ID
inner join USERROLEREL_DET on URRD_USER_ID=USRM_USER_ID inner join ROLE_MST on URRD_ROLE_ID=ROLM_ROLE_ID
inner join CONTACT_MST_LOG CNTM on CNTM1.CNTM_CONTACT_ID = CNTM.CNTM_CONTACT_ID
INNER JOIN ( select max(CNTM_Active_Date) AS CNTM_Active_Date from CONTACT_MST_LOG b
where CNTM.CNTM_CONTACT_ID = b.CNTM_CONTACT_ID ) T ON CNTM.CNTM_Active_Date = T.CNTM_Active_Date
where USRM_COMPANY_ID= '544' and ROLM_ROLE_ID = 9 AND CNTM.CNTM_ISACTIVE = 'Y'
group by CNTM.CNTM_Active_Date,CNTM.CNTM_CONTACT_ID,CNTM.CNTM_ISACTIVE,
day(Convert(varchar, CNTM.CNTM_Active_Date ,110)),
month(Convert(varchar, CNTM.CNTM_Active_Date ,110)),
year(Convert(varchar, CNTM.CNTM_Active_Date ,110))
Order by year(Convert(varchar, CNTM.CNTM_Active_Date ,110)) ,month(Convert(varchar, CNTM.CNTM_Active_Date ,110))

I am still not clear about the latest active users. Does your query returns correct result for the today's date ? If yes then I can help you to make query result for all the days instead of single date.

Thanks Maciej :)

Ohh Sorry, It's my column name. Let me edit that. Please check now

Welcome :) . Please ask me if you need any other help in this query.

Yes, I got it. if "CommHeirID " is NULL in LEFT join and it is eliminated in the INNER join then it means row does not exixts in the table "UMTranImpoPendLog". So if the row does not exists then you should insert the new row instead of updating it. It returns NULL result because LEFT JOIN will bring you all the rows of left table, no matter row is exists or not in the right table. Please let me know if you have any concern or query on this.

Do u want to update records those are there in "UMCommWithHeldLog" table, but not there in "UMTranImpoPendLog " table ? Something like this ?

You seems still confuse. You are getting data from left join because the columns are there physically from the left table. So, you can update those records from left the table. But there is no data from right side table because of which you are getting NULL columns. So, you cant update those records. Please explain which kind of data you want to update, so we can find alternate solution.

ya, but while inserting in the table or just fetching data using select statement ?

Please provide some more details on this. Reset means when ? While inserting data or while fetching data? and what to do if the number exceeds 9999 for a month ?

Yes, if there is no row then what will get updated ? Please share your issue in detail so I can suggest some alternate way....

Great advise for 1.50..... 5ed

Thanks Maciej.... :)

Thanks... :)

Please put some clarity in the question.

Thanks Maciej :)

yup. sure. It deserves....

Nice article Maciej.... :)

Welcome :)

Can you please put some clarity in your question ? What is wrong in your result and what are you trying to achieve with this query ?

Please re-format your query. It's really not readable properly.

Hi, your question does not seem to be clear. Please improve your question by adding more details. So someone can provide you needed solution. Thanks...

Hi. Do you know how to implement this P3P policy ?

Always Welcome :)

Please provide more information. In which line you are getting error. Or please share how did u initialized variables those are used.

Please elaborate more on this.

Hi, yes.. If you set trigger then no matter from where are you updating the table it will be reflected. Whenever your table is updated the trigger will fire and it will update another table accordingly. If you are updating the table using ASP.Net still the trigger will update another table automatically. Please let me know if you need more info on this.

always welcome :)

Welcome. Your query is correct, but it may provide wrong result in the case of NULL value. Please check NULL values using your query. except keyword will also handle NULL value. Thanks..

I have a query on this. You want to copy data from one to another table. So the data will be inserted every time ? or the data will be inserted and updated both based on situation ?

If multiple value will return then you need to choose only one value. So, which value do you want to set as @TrID if you find multiple ? You cant choose multiple values in a variable.

Thanks :)

Always welcome :)

Which problem are you facing ? I saw your new post. Please reply there so if possible I can help you quicker.

Are you using any tool called "Zing Soft" ? I noticed that the path contains the directory of "ZingSoft".

Hey, you posted this query on previous question right ? Didn't you get the solution ?

Always Welcome :)

if you are inserting in bulk then there is no room for error. If error will occur then none of the data will be inserted. If insertion is completed successfully then only way to verify data using except keyword. it will result all the missing data. If there are 100 rows needs to be inserted and only 80 are inserted then "except" will show you missing 20 rows. Got my point ?

I have a query. The data are inserted in a loop or in bulk ? If the data are inserted in look, you can use try/catch block in SQL query and you can insert error log in a table from catch block. If the data is inserted in bulk then you will need to verify only using except keyword. Please let me know if you need more info or I am still missing something. :)

Can you please try to avoid looping and insert bulk data ? Because only blocking thing in your query in terms of performance is while loop.

Always welcome :)

Please check the HTML and CSS using firebug tool. It is very tough to identify the issue only by viewing image file.

Okay, It should work. It may be possible that there are few other filters in your query (Like where condition,Inner join etc.) Is it possible those condition filter out the data you need ? Can you please share some demo data with me, so I can check ?
Sometimes we are looking at one place and issue lies on another place..

I am not sure about the relation between both the tables. Can you use join to fetch columns from both the tables ?

Please let me know if you have any concern or query on this.

It's Forms authentication....

I mean to say, If I run the system on browser of the Live server directly by taking the server on remote then it works. So I can guess it is working on the server only.So issue might be with IIS setting. Got my point ??

@Suvabrata , I would like to share one point.. I notice that when I run this portal in the live server using remote connection, it works fine in all the versions of IE. So I can guess it is something related to IIS. What do you think ?

no. I am using session only, not cookies.

Ya, it's a publish version at server. Actually that should not be issue because the system is working fine in all other browsers.

This portal contains VB and C# both kind of files in separate directories.
Is it something which may cause this issue ?

@Suvabrata, thanks for providing this link... I saw this link earlier, but it's like to play with IIS settings. My portal is running live and feeling risky to do this kind of change. Looking for other solution. If I cant find any other then I will try this solution last. Do you know any way to get actual error message if any ? that will help me to investigate more on this.

Suvabrata, it's a small portal application. It's working fine for all the browsers, but getting HTTP 500 error in the IE9 and older versions of IE. I am also not able to get the real error message. Is there any way to find real error ? I am not sure which code block should I send. Do you have any idea ?

I have already keep this solution in my mind to store last visited page in session or cookies. But I am looking for other alternative. I am looking for default function or object. Session or cookies may affect performance of the application.
If you have any other alternative or any idea related to this then please share with me.

Hi Amit,

Thanks for your suggestion.

I tried this solution from my end.

But , when user change url directly then all the dot net objects which are used to get previous page url are getting refreshed. And they returns NULL value. So in this case I am not able to get the previous page information.

Please advise if you have any other idea.

Thanks
Advay Pandya

Hi Prasad,
Thanks for your solution.

But both links suggest to redirect page into login or some other AccessDenied page, I dont want to kill session or redirect user into any other page. I want user to stay in the same page.

ex. If user is in a.aspx page, and if they try to change url to b.aspx , then user should stay in a.aspx

When user change url directly then all the dot net objects which are used to get previous page url are getting refreshed. And they returns NULL value.

So how can I achieve this ?

Thanks
Advay Pandya