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


Top 5 Contributors of the Month
david stephan
Post New Web Links

FOR XML Path Query - Help Needed

Posted By:      Posted Date: May 22, 2011    Points: 0   Category :
 

Hi All,

I'm attempting to write a query using FOR XML PATH and I'm having a few issues that maybe someone can help me with. I'm pretty sure it's something simple that I'm missing, but here are the details.

This is the expected output:

<CWCS_TRANS> 
<DTG>05-04-2011 12:25:00 CST</DTG> 
<SEQ_NUM>00002</SEQ_NUM> 
<COLLECTION>CW1</COLLECTION> 
<DISTRICT>0147</DISTRICT> 
<SCHOOLS> 
 
<SCHOOL>0002</SCHOOL> 
 
<RECORD_TYPE>WCT</RECORD_TYPE> 
 
<YEAR>2011</YEAR>    
 
<TERM>1S</TERM>              
 
<SECTION> 
 
<SECTION_ID>WORLD HISTORY</SECTION_ID>

View Complete Post


More Related Resource Links

SQL Query help needed

  
I made a change to my SQL tables but I am having trouble changing my query to acommodate that change.  This query works:   select members.playername, ratings.rating, MAX(ratings.DATE), ratings.type from roles left join ratings on roles.fk_member_id=ratings.member_ID_FKleft join members on roles.fk_member_id=members.pk_id where roles.fk_member_id in (SELECT ROLES.FK_MEMBER_ID FROM ROLES    WHERE ROLES.TYPE = 'Player'   And ratings.type='8'   AND FK_TEAMBAR_NUMBER = 109)group by playername, date, ratings.type, ratings.rating   The change is that now my application tracks historical data on a member's rating.  So now there are multiple records for each member.  I want my query to return a table of the entire team members and their most recent rating (most recent date).  The above query will pull all ratings for each team member.  I just need the most recent rating - therein lies my problem. Here are the table structures: RATINGS TABLE: RATING_ID          intMEMBER_ID_FK   intDATE                   smalldatetimeSET_ID_FK          intRATING         

SQL 2005 Query Assistance needed

  
Question: I need some assistance with the below mentioned code. Essentially what I need to do is insert a new row for an off-setting A/R account. For Example, I will have two rows (one for the charge and one for the adjustment each related by the PatientVisitId (distinct and primary key)). An example of this would look like this minus a lot of my fields, but to give you a fast visual: Charge: Credit = $225.00, Debit = $0.00 Adjustment: Credit = $0.00, Debit = $65.00 What I need is a new row for the difference. In this instance, I would get a new row "A/R" with a Credit = $0.00, Debit = $160.00 SET NOCOUNT ON ; WITH CTE AS ( --- Bring in the Charges SELECT 'Charge' AS [Type], pv.TicketNumber, pv.PatientVisitId, b.[Entry] AS BatchEntry, '100' AS [Fund], LEFT(ISNULL(fin.Ledger , '') , 5)AS [GL#], LEFT(ISNULL(fac.Ledger , '') , 4)AS [Prog], LEFT(ISNULL(ic.Ledger , '') , 4) AS [Srce], '' AS [Func], --- Need case statement here for Place Of Service LEFT(ISNULL(doc.Ledger , '') , 4)AS [EE], '99999' AS [Seg 1], '99999' AS [Seg 2], ' ' AS [Debit], (CONVERT(varchar, SUM(pvp.totalfee))) AS [Credit], ISNULL(ic.ListName,'Self') + SPACE(2) + fac.ListName + SPACE(2) + 'A/R' AS [Description] FROM PatientVisit pv INNER JOIN DoctorFacility comp ON pv.CompanyI

SQL query help needed

  
The following query works: SELECT playername,        rating,        [DATE],        [TYPE] FROM   (SELECT members.playername,                ratings.rating,                ratings.DATE,                ratings.TYPE,                Row_number() OVER(PARTITION BY members.playername ORDER BY                ratings.DATE                       DESC )                rn         FROM   roles                LEFT JOIN ratings                  ON roles.fk_memb

SQL 2005 Query Assistance needed ASAP

  
I need some assistance with the below mentioned code. Essentially what I need to do is insert a new row for an off-setting A/R account. For Example, I will have two rows (one for the charge and one for the adjustment each related by the PatientVisitId (distinct and primary key)). An example of this would look like this minus a lot of my fields, but to give you a fast visual: Charge: Credit = $225.00, Debit = $0.00 Adjustment: Credit = $0.00, Debit = $65.00 What I need is a new row for the difference. In this instance, I would get a new row "A/R" with a Credit = $0.00, Debit = $160.00 ($225.00 charge minus $65.00 adjustment). SET NOCOUNT ON ; WITH CTE AS ( --- Bring in the Charges SELECT 'Charge' AS [Type], pv.TicketNumber, pv.PatientVisitId, b.[Entry] AS BatchEntry, '100' AS [Fund], LEFT(ISNULL(fin.Ledger , '') , 5)AS [GL#], LEFT(ISNULL(fac.Ledger , '') , 4)AS [Prog], LEFT(ISNULL(ic.Ledger , '') , 4) AS [Srce], '' AS [Func], --- Need case statement here for Place Of Service LEFT(ISNULL(doc.Ledger , '') , 4)AS [EE], '99999' AS [Seg 1], '99999' AS [Seg 2], 0.0 AS [Debit], SUM(pvp.totalfee) AS [Credit], ISNULL(ic.ListName,'Self') + SPACE(2) + fac.ListName + SPACE(2) + 'A/R' AS [Description] FROM PatientVisit pv INNER JOIN DoctorFacility comp ON pv.Compa

employee attendance with in/out times (was "Query Help Needed...")

  

Dear Friends,


I am in problem & have to solve one query.
I have a one table with the empno, employee time in & time out, data, employee can go out & come in fequently in a day.

I want to know that how much time every emp have attend in the company per day.

I want to know that how much time every emp have not attend in the company per day(once he enter at morning and he come out in the end of the day)

Kindly, do reply as soon as possible.

I am enclosing data defination in txt file along with the data in the MS Excel file.

Thanx in Advance...

SuraReddy, Emani


employee attendance with in/out times (was "Query Help Needed...")

  

Dear Friends,


I am in problem & have to solve one query.
I have a one table with the empno, employee time in & time out, data, employee can go out & come in fequently in a day.

I want to know that how much time every emp have attend in the company per day.

I want to know that how much time every emp have not attend in the company per day(once he enter at morning and he come out in the end of the day)

Kindly, do reply as soon as possible.

I am enclosing data defination in txt file along with the data in the MS Excel file.

Thanx in Advance...

SuraReddy, Emani

 

 

The Table Like this


Create table emptime
(
 EMPCODE VARCHAR2(5),
 CARDNO VARCHAR2(5),
 EMP_CODE_NAME VARCHAR2(25),
 INOUTTIME TIMESTAMP(6),               
 IOGATENO VARCHAR2(10),               
 DOOR VARCHAR2(20),               
 IN_OUT VARCHAR2(10)               
 CARDDATE DTE
);      

Aboslute path needed for hyperlink column

  

Hi,

I am working on data datagrid and getting 1 column as Hyper Column 

 

the Hyperlink Column is declared as 

 

<asp:HyperLinkColumn DataNavigateUrlField="Path" DataTextField="SongName" 

                    HeaderText="Songs" Target="_self" DataNavigateUrlFormatString="/{0}"></asp:HyperLinkColumn>

where SongName and Path are coming from database

 

 

now the values of Path is in the format "http://abc.html"

however when I click on the grid it gives me something like this http://localhost:XXXX/http://abc.html

 

Please tell me how to convert this relative path to absolute path.


Refactor TSQL Query Help Needed

  

Hello,

I am looking at a lengthy query that needs refactoring and clean up. The overall jist of the query is this:

We have a customer with several items they have subscribed to.

We have a lookup table for these subscriptions with the number of days the subscription is good for.

We have another table that has a count of the days since the subscription began.

We basically then do a join and a big case (I mean really big) case statement with different logic for each subscription code that matches what the customer has subscribed to. In the end the sp returns the subscription code and a Y or N flag for if the subscription has expired.

I've looked at PIVOT and I can't see how it would help here. ROW_NUMBER isn't much use as there really aren't any sub queries. I'm racking my brain trying to figure out a way to simplify this monsterous case statement and coming up empty so I was hoping for some ideas from others. Redesign of tables and approach is an option as we are already moving alot of hard coding to table driven etc.

Thank You for your help and ideas.


urgent needed an sql query

  

Hiii Frnds,

I have to generate a unique token no which will have three fields an incrementer,comp_code and

date.

I have problem in generating a unique incrementer. Incrementer should be incremented by 1 like 0001,0002 etc, should be unique and based on no of record inserted.

Plz rep asap

thanx and regards


Equivalent Query needed

  

Hi All,

 I have a table with Note ID and Comment Columns

Note ID           Comment

100                  Text 1

100                  Text 2 

101                  Text 3 

101                  Text 4

 I am using the query below to achieve my desired result

select NoteID, STUFF(
	(SELECT ',' + a.Comments AS [text()]
	from Notes a
	where a.NoteID = b.NoteID
	Order by a.Comments
	for xml PATH('')),1,1,''	) AS Comments_Concatenated
 from Notes b
group by NoteID
ORDER BY NoteID

 My result set i obtain after using the query is

NoteID            Comment

100                 Text1,Text2

101                 Text3,Text4

 

MS SQL Server: Search All Tables, Columns & Rows For Data or Keyword Query

  
If you need to search your entire database for specific data, this query will come in handy.

So when a client needs a custom report or some sort of custom development using Great Plains, most of the time I will have to track down the data in the system by running this query and find the table(s) it is in.

How to Encrypt Query String Parameters in ASP.NET

  
Encrypt Query String Parameters in ASP.NET.u can send secure data one page another page u can also use query string to encrypt

Content Query Web Part missing in SharePoint 2010

  
If you don't see content query web part listed in the web parts list, this is because you have not enabled "Search Server Web Parts" feature in site collection features. Enable this feature and content query web part will show in the list of web parts.

Virtual Path Problem

  

 I get the following error on last line below (working on remote host): 'E:\web\sfcascom\ls_admin/uploads/images/001.JPG' is not a valid virtual path,

Note that the slashes go in different directions

I've tried a number of things but nothing seems to work. Thanks-

 

Imports System.Drawing
Imports System.Drawing.Drawing2D
Imports System.Drawing.Imaging
Imports System.Web.UI.WebControls

Imports System.Drawing.Imaging.ImageCodecInfo
Partial Class Protected_imageupload
    Inherits System.Web.UI.Page
    Private Sub btnUpload_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnUpload.Click
        ' Initialize variables
        Dim sSavePath As String
        Dim sThumbExtension As String
        Dim intThumbWidth As Integer
        Dim intThumbHeight As Integer
        Dim sFilename As String = ""
        ' Set constant values
        'sSavePath = "images/"
        sSavePath = Server.MapPath("~/") + "ls_admin/uploads/images/"



        sThumbExtension = "_thumb"
        intThumbWidth = 160
        intThumbHeight = 120

        ' If file field isn?t empty
        If FilUpload.PostedFile IsNot Nothing Then
            ' Check file size (mustn?t be 0)
            Dim myFile As Http

Response.BinaryWrite help needed

  

Hi all

Im new to .NET and stuck now, so i hope anyone can help my with my problem.

I have a MS SQL database where i have stored some documents as Binary data. I cant figure out how to retrieve them and show/download them in my browser.

I found some guides online and have try to put something together.


Download.aspx.cs:

private void Write()
 {
 int id = 0;
 string strID = Request.QueryString["id"];
 int.TryParse(strID, out id);

 var db = new ModelDataContext();
 var doc = db.DocumentDatas.Where(x => x.ID == id).FirstOrDefault();

 byte[] byteArray = File.ReadAllBytes(doc);

 Response.BinaryWrite(byteArray);
 }

How do i fill the byteArray with the data?

Thanks in advance :)

/Ryan


Creating a user friendly alias for the website path

  

 Hello mates,

I am hosting my ASP.NET application on a Windows 2003 Server.

To browse to my application one has to type the path :http://serverName/applicationName


I want users to only type  applicationName on the web adress area.I believe there is something to do with alias and CNAME on DNS records that can adress this.

Any help on how to go about doing this will be highly appreciated.


sql query that don't run

  

Hello....

I'm going crazy... i've got a piece of code that don't update the database... Could somebody tel me why it doesn't run??

Dim conn As SqlConnection
        Dim cmd As SqlCommand
        Dim cmdstring As String = "Update Corsi Set [Contenuto]='" & TxtText.Content & "' Where [ID]='" & Request.QueryString("Id").Replace("'", "") & "'"
        conn = New SqlConnection(SqlAnni.ConnectionString)
        cmd = New SqlCommand(cmdstring, conn)
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