Here is the sample xml
<ClinicalDocument xmlns:sdtc="urn:h12-org:abcd" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:h12-org:v3">
<realmCode code="US" />
<typeId extension="POCD_HD000040" root="2.16.840.1.113883.1.3" />
<templateId root="2.16.840.1.113883.10.20.22.1.1" />
<id root="226ff30f-3b1f-11e3-a969-005056bb0109" />
[code]....
how to write a sql query.
I know how to use Linked server query to retrieve data from two different sql on premise instances.I would like to try the same on sql server instances hosted on azure.When I connect to sql instance, I don't see ServerOBjects->Linked server. I just see Database and security.Is this possible on sql azure, if so how can we achieve it
View 4 Replies View Relatedhi,
i am generating report for my project.
my table goes like
ID VASID VASSID JanMail JanVisit JanPhone JanComment FebMail FebVisit FebPhone FebComment................. DecPhone
1 25 4 True False False Mail me False True False Visit me
My report has 3 check boxes for Mail,Visit,Phone respectively and a listbox of 12 months january-december respectively..
once user check on Mail and select a month suppose January in my report,the report should generate only January Comments.
here user can select any number of months.if user selects january,february and march, report should generate respective months Comments.
Please help me in writing SQL Query to get the comments.......
if any one know the solution for the above problem,Please help me
Thanks & Regards,
is it possible to make a custom query to fetch and update data from sql server 2005 in SharePoint designer
i make a new data source library and use a custom query to get data but don€™t know how to configure update custom command
can any buddy help me out
Currently, I want to get images of an item in my report for illustration purpose.
Below is my query to get image for item 'GL-10000' in the database. However, I would like to pass a parameter value, '@sItem', from my report so that it would get all related pics of items. Â
SQL Query:
Select top 1 item_picture_mst.picture from item_picture_mst
where item_picture_mst.item in (select item_all.item from item_all where item_all.item = 'GL-10000')
ORDER BY item_picture_mst.picture DESC;
ItemIDItemNamePrice
1Item1120.00
2Item2234.00
3Item3250.00
4Item4300.00
5Item5300.00
6Item6290.00
7Item7170.00
8Item890.00
9Item9170.00
the above is the existing table and i need a query to retrieve max price with out using max function and sub query
I'm trying to figure this out
I have a store procedure that return the userId if a user exists in my table, return 0 otherwise
------------------------------------------------------------------------
Create Procedure spUpdatePasswordByUserId
@userName varchar(20),
@password varchar(20)
AS
Begin
Declare @userId int
Select @userId = (Select userId from userInfo Where userName = @userName and password = @password)
if (@userId > 0)
return @userId
else
return 0
------------------------------------------------------------------
I create a function called UpdatePasswordByUserId in my dataset with the above stored procedure that returns a scalar value. When I preview the data from the table adapter in my dataset, it spits out the right value.
But when I call this UpdatepasswordByUserId from an asp.net page, it returns null/blank/0
passport.UserInfoTableAdapters oUserInfo = new UserInfoTableAdapters();
Response.Write("userId: " + oUserInfo.UpdatePasswordByUserId(txtUserName.text, txtPassword.text) );
Do you guys have any idea why?
Good day., please help me,in a formview control, i set it in Insert Mode, so it should display info from table 1 but when i click on the insert button, it will insert it in table 2.btw, table 1 and table 2 are in the same database?? how about if they are not in the same database?how?please help me,Thanks.,SALAMAT PO.,
View 3 Replies View RelatedHey All,
I am developing a data acquistion system which monitors the amount of energy that a user consumes in different parts of a house and displays the information in real time on their computer screen. I am collecting the data through tranducers attached to the circuit breakers in the breaker box and sending the data to analog-to-digital converter channels in a MCU. I am retrieving the data from the serial port and storing it to a text file. Each line of data in the text file represents three fields which are separated by commas. I will be reading data from multiple data collection boxes so the first field is the unit number, the second fied represents the analog-to-digital converter channel number from each unit, and the third field is the data that is collected from the ATD channel. I am trying to use SSE to retrieve the data from the text file, and parse each line of data into individual columns in a databse. Then I want to be able to extract the data associated with a particular ATD channel number from the databse and display it in the appropriate text field on a windows form.
I've got the MCU programmed. I have no problem collecting the data from the serial port, and I can do the visual basic programming okay. I have absolutely no clue how to read the data into the database, continuosly read new values into the databse, and then access the stored data to update the text fields on the form. Please help if you can, I've been working on this specific problem for a couple of weeks and I'm not making any progress. Thanks.
Hey All,
I am developing a data acquistion system which monitors the amount of energy that a user consumes in different parts of a house and displays the information in real time on their computer screen. I am collecting the data through tranducers attached to the circuit breakers in the breaker box and sending the data to analog-to-digital converter channels in a MCU. I am retrieving the data from the serial port and storing it to a text file. Each line of data in the text file represents three fields which are separated by commas. I will be reading data from multiple data collection boxes so the first field is the unit number, the second fied represents the analog-to-digital converter channel number from each unit, and the third field is the data that is collected from the ATD channel. I am trying to use SSE to retrieve the data from the text file, and parse each line of data into individual columns in a databse. Then I want to be able to extract the data associated with a particular ATD channel number from the databse and display it in the appropriate text field on a windows form.
I've got the MCU programmed. I have no problem collecting the data from the serial port, and I can do the visual basic programming okay. I have absolutely no clue how to read the data into the database, continuosly read new values into the databse, and then access the stored data to update the text fields on the form. Please help if you can, I've been working on this specific problem for a couple of weeks and I'm not making any progress. Thanks.
Hey.
I have two sets of tables.
Machine Warranty table (primary key=machine_warranty_id)
("INSERT into machine_warranty(Job_No, Cust_ID, Machine_SerialNo, Product_ID, Unit_Price, Payment_Type, Date_Installed, Freebies, Remarks, Unit_Location, Date_Paid_MW, Cheque_CC_No, Date_TrialStart, Waterblock, Pressure_Regulator, Rental) VALUES ('" + job + "','" + txt_CustID.Text + "','" + txt_MachineSerialNo.Text + "','" + txt_ProductID.Text + "','" + txt_UnitPrice.Text + "','" + dd_PaymentType.SelectedValue + "','" + txt_DateInstalled.Text + "','" + txt_Freebies.Text + "','" + txt_Remarks.Text + "','" + txt_UnitLocation.Text + "','" + txt_DatePaid_MW.Text + "','" + txt_ChequeCCNo.Text + "','" + txt_DateTrialStart.Text + "','" + dd_WaterBlock.SelectedValue + "','" + dd_PressureRegulator.SelectedValue + "','" + dd_Rental.SelectedValue + "')", connectionOne);
//machine warranty id is a primary key is auto generated already therefore not in the query line.
Warranty Period table (primary key=warranty_period_id/ foreign key=machine_warranty_id)("INSERT into warranty_period(Warranty_Period_ID, Warranty_Start) VALUES ('"+ txt_Warranty.Text +"','"+ txt_Warranty_Start.Text +"')", );
How to retrieve the machine_warranty_id from the first sql query line? So that I could use it for Warranty Period Insert statement.
I would like to use a dynamic query to obtain a value...a recordcount specifically.
I have two options so far. The 2nd option works fine, but seems like it would be slower. The first option doesn't recognize that i have declared my variable (@int). Any ideas how i would use the first method...or if it's worth trying?
Method 1
--# produces error: Must declare the variable '@int'.
DECLARE @int as int
EXEC ('SELECT @int = COUNT(ProductID) FROM Products')
print @int
Method2
--# works fine...but seems less efficient.
EXEC ('SELECT ProductID FROM Products')
print @@rowcount
I am using SQL Server 2005.
I have a table with records like this:
Date EmpID ADCode ADAmount
----------------------------------------------------------------------
01-Jul-07 101 GPF 150
01-Jul-07 102 GPF.ADV 100
01-Jul-07 103 GPF 250
01-Jul-07 103 GPF.ADV 200
01-jul-07 104 GPF 300
I want to show results like this using a single SQL query:
Date EmpID GPF GPF.ADV
-----------------------------------------------------------
01-Jul-07 101 150 0
01-Jul-07 102 0 100
01-Jul-07 103 250 200
01-Jul-07 104 300 0
I tried:
select PaySlipDate,EmpID,
case ADCode when 'GPF' then ADAmount else 0 end GPF,
case ADCode when 'GPF.ADV' then ADAmount else 0 end 'GPF.ADV'
from EmpSalaryRecord
It is showing like this:
Date EmpID GPF GPF.ADV
-----------------------------------------
01-Jul-07 101 0 0
01-Jul-07 101 150 0
01-Jul-07 102 0 100
01-Jul-07 103 0 0
01-Jul-07 103 250 0
01-Jul-07 103 0 200
It is showing multiple records of each employee for each date. First a record with GPF and GPF.ADV both zero and then records with values. I want a single record for each date and employee.
How to retrieve only nth row as a result of execution of one query?
For example:
Lte the table be:
SNo SudentName Marks
001 Ashok kumar 67
002
Anderson
70
003 Alfred
60
004 Ameeruddin
65
005 Kalyan
Kumar.
69
Now the Query is: How is the 3rd ranker. The answer must be Ashok kumar.
How to write this query in SQL Server.
I want to join two tables of information together. I want to bring the order information together with the order charge. But since the OrderCharges table can have multiple values, I just want to retrieve the sum of those charges for a specific OrderID+ItemID.
My data looks something like this:
CREATE TABLE #OrderItems
(
OrderItemsID INT IDENTITY (1,1) PRIMARY KEY,
OrderID INT,
ItemID INT
)
SET IDENTITY_INSERT #OrderItems ON
[Code] ....
So I'm looking to see a result set that would like this this:
OrderItemsID-----ItemID-----OrderID----OrderDescription--OrderCharge
----1------------124---------1------------Shipping----------6.55
----2------------156---------1------------Shipping---------16.85
----3------------156---------2------------Shipping----------7.40
----4------------158---------1------------Shipping----------7.85
----5------------158---------2------------Shipping---------15.25
I am new to SQL, still learning.
If I want to retreive unique records for e.g. VendorName,City and State however no 2 vendors must be of the same city or state.
How can I do this?
I have used Select and distinct but that still brings unique vendornames in the same state and city. I only need one vendor per city and State.
Help!
how do you write a query to retrieve an attribute value from xml?
example xml:
<PassportResponse><Passport><RequestID>200409201037256748130032433</RequestID><Status>COMPLETE</Status><CustId>33625</CustId><UserId>00000000000000000000</UserId><RequestTimeStamp>9/20/2004 10:37:25 AM</RequestTimeStamp><CompletedTimeStamp>9/20/2004 10:37:26 AM</CompletedTimeStamp></Passport><Errors /><Reports><Report product="DetailPPC"><ProductCode>LOCNF110</ProductCode><Status>COMPLETE</Status><ReportName>PPC DETAILED NETWORK</ReportName><CriticalResult /><OrderTimeStamp>9/20/2004 10:37:26 AM</OrderTimeStamp><CompletedTimeStamp>9/20/2004 10:37:26 AM</CompletedTimeStamp><RiskAddress /><ADDRESS><ScrubberCode> C1 </ScrubberCode><AddressChanged> Y </AddressChanged><HomeStreetNum>339</HomeStreetNum><HomePreDir /><HomeStreetName>ALL PEAT BEDS</HomeStreetName><HomePostDir /><HomeStreetType>RD</HomeStreetType><HomeCity>EARLTON</HomeCity><HomeState>NY</HomeState><HomeZip>12058</HomeZip><HomeZip4>2707</HomeZip4><HomeCountry>USA</HomeCountry></ADDRESS><PERSONAL><PPC><PRODHEAD><ProductName>PPC</ProductName><PPCErrorCode>L000</PPCErrorCode><PPCErrorMsg>success</PPCErrorMsg><PPCReturnSrc>ADDRESS</PPCReturnSrc><PPCRecordCnt>1</PPCRecordCnt></PRODHEAD><PRODBODY><RECORDNUMBER><PPCVal>7/9 </PPCVal><FireDistrName>COXSACKIE FPD NO 2 </FireDistrName><StateSpecificCode /><FireSubscribeDistrCode /><PPCCountyFIPS>039</PPCCountyFIPS><PPCCountyName>GREENE </PPCCountyName></RECORDNUMBER></PRODBODY></PPC></PERSONAL><COMMERCIAL><PPC><PRODHEAD><ProductName>PPC</ProductName><PPCErrorCode>L000</PPCErrorCode><PPCErrorMsg>success</PPCErrorMsg><PPCReturnSrc>ADDRESS</PPCReturnSrc><PPCRecordCnt>1</PPCRecordCnt></PRODHEAD><PRODBODY><RECORDNUMBER><PPCVal>7/9 </PPCVal><FireDistrName>COXSACKIE FPD NO 2 </FireDistrName><StateSpecificCode /><FireSubscribeDistrCode /><PPCCountyFIPS>039</PPCCountyFIPS><PPCCountyName>GREENE </PPCCountyName></RECORDNUMBER></PRODBODY></PPC></COMMERCIAL></Report></Reports></PassportResponse>'
I want to get back a table of all products. Example
Report product="DetailPPC">
want to return DetailPPC the xml may contain multiple reports I want a table with all the report products:
SELECT PassportResponse.Reports.query('.') as result FROM
@xml.nodes('/PassportResponse/Reports') PassportResponse(reports)
the sql will return the reports but I want to get what the product = only.. I'm hoping to do this without a substring function.
any help would be appreciated.
thanks
The below is my query in SQL SERVER 2012
DECLARE
@PN_INC INT,
@POSITION_DESC VARCHAR(15)
SELECT @PN_INC= MAX(PositionNumberInc) FROM dbo.tblPosition WHERE LTRIM(RTRIM(UPPER(PositionDescription)))=LTRIM(RTRIM(UPPER(@POSITION_DESC)));
SELECT @PN_INC is returning null when there is no record in the table;
1. how can I make it return 0 when @PN_INC is null else it should pick the MAX(PositionNumberInc) value
2. I have written the below query to return 0 when @PN_INC is null but I need query to select the MAX(PositionNumberInc) value when @PN_INC is not nullÂ
SELECT @PN_INC= coalesce(MAX(PositionNumberInc), 0) FROM dbo.tblPosition WHERE LTRIM(RTRIM(UPPER(PositionDescription)))=LTRIM(RTRIM(UPPER(@POSITION_DESC)));
I need to retrieve points column from my database for the specific user that is signed on and sum all of them. How can I do it using the Gridview and also code in vb for a label? Thanks.
View 4 Replies View RelatedHere is my sql procedure:
ALTER PROCEDURE dbo.SoftWareShow /* ( @parameter1 int = 5, @parameter2 datatype OUTPUT ) */ @SoftID uniqueidentifierAS SELECT [SoftID], [SoftName], [SoftJoinDate], [SoftSize], [SoftMode], [SoftRoof], [SoftHome], [SoftDemo], [SoftFirstClassID], [SoftSecondClassID], [SoftDesc], [SoftReadCount], [SoftDownCount],ltrim(rtrim([SoftUrlOne])) SoftUrlOne, ltrim(rtrim([SoftUrlTwo])) SoftUrlTwo, ltrim(rtrim([SoftUrlThree])) SoftUrlThree, ltrim(rtrim([SoftUrlFour])) SoftUrlFour FROM [SoftWare] WHERE ([SoftID] = @SoftID) RETURNwhere I retrieve data using sqldatasource, an error appear. how can do ?
Hello,
Before I ask a question, I am visually impaired and cannot read printed words only on a screen. I am 51 years old and have been coding professionally since I was 23 (yes that long). I have four apps on the market and run a small consultancy company in the UK and my SQL knowledge is scant. I have read two books on the subject and thought I had an idea on how SQL works.
I used to belong to another forum but they gave me a hard time so please go easy on me.
I developed an ADO app ages ago and remembered some of it ...
I have four tables (the following is a simplified version of the real thing)
MainTable (Contact data like name address etc. 23 fields)
Towns (Two fields TownUIN and TownName)
Counties (Two fields CountyUIN and CountyName)
Countries (Two fields CountryUIN and CountryName)
My database is normalised to 3NF and contains no duplicate or redundant data.
My problem ...
Sample data
Main:
MainUIN MainName MainTown
1000 Fred Bloggs 1
1001 Fred Smith 2
Towns:
TownUIN TownName
1 Bradford
2 Leeds
SELECT MainName,MainTown FROM Main WHERE MainUIN=1000 returns
'Fred Bloggs' 1 (the TownUIN)
I want ...
'Fred Bloggs' 'Bradford'
It was suggested I use
SELECT MainName,MainTown FROM Main,Towns WHERE MainUIN=1000 AND TownUIN=MainTown
This returned 'Fred Bloggs' 'Bradford' but was slow.
My question is what am I doing wrong?
I am using SQL Server 2005 Express with VB.Net 2005 on Vista Business and XP.
I am sorry my SQL knowledge is weak please dont get angry just tell me the basic thing that I do not know.
Hi.I have a IIS server with php3 installed. I have SQL server databaseand data stored in unicode format (nvarchar fields). In header of myphp I have the meta: <meta HTTP-EQUIV="content-type"CONTENT="text/html; charset=UTF-8">.I have the following problem:- I trie to do a query using ODBC (version 3.525.1022.0) and SQLServer odbc driver (version 200.85.1022.00). The query doesn'tretrieve information in utf format and accents and extra charactersaren't showed correctly.- I trie to di the query usin native OLE DB. I use mssql funtions andthe problem is the same.If I install php4 and I use "newCOM("ADODB.Connection",NULL,CP_UTF8);", then it works correctly.Anybody knows if its posible to retrieve data from sql-server in utf-8format using php3?I see in other postings that odbc driver only accept utf-8 in version3.7 or higher? Is it true? If is true, where I can download odbcdriver 3.7?Thank's in advance.Miki.
View 1 Replies View RelatedHi, does anyone know how to retrieve mistmatched records across 2 tables.
To clarify, I have table A with 1175 records and Table B with 894 records.
The records from table A match exactly some the records in table B.
I want to create another table with the extra 281 records from Table A which does not match that of Table B.
I have tried the query with Select where fields_1.a<>fields_2.b AND fields_2.a<>fields_2.b etc
but that doesn't seem to work.
*desparate*
Hi,
I need help to build a query that shows me how many columns inside a range on columns are null.
Example: quantity1;quantity2;quantity3;quantity4;quantity5; quantity6;quantity7;
Which columns are null?
Thanks in advance
I really need creating a query that will retreive all records from a table where the dbo.CorpAdv.AcctNum field equals a specific value (for this example "0023"), the TranCode = "R" and the sum of the records, starting with the latest, equals the value of a field in another table (dbo.Master.TotalAdv)
dbo.Master.TotalAdv is numeric (dollar amount) and in this example the value is $1,850.00
dbo.CorpAdv.pID is an integer and unique ID for each record, later records have higher numbers
dbo.CorpAdv.AcctNum is text field
dbo.CorpAdv.AdvAmt is numeric (dollar amounts)
[code]....
I need a query which retrives the firstnames of the person where last name is john. I am considering the performance as the main factor.
Is there any better query than the following?
Select firstname from tablename where lastname='John'
Considering performance as the major factor....
i need to retrieve data from a particular field of the gridview according to the selected row to stored it into session .....what i had done so far as following: Protected Sub GridView1_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) DetailsView1.PageIndex = GridView1.SelectedIndex Session.Add("receiverName", GridView1.??????) End Sub????? is the part i am not sure what to code.... i tried different method by seems to have problem of convertion.
View 1 Replies View RelatedOkay, I used the SQLDataSource control to get my data from the database
table. What or how do I retrieve individual data from the
sqldatasource? I want to do some string comparison and manipulation
before I display it to the browser. How can this be accomplish?
Help is appreciated.
Hi,
I have a website which is designed to search for employee information. I have the search system working which does exactly what I want to, but as an added feature I want there to be a button which, when someone clicks on it, it takes whatever the previous search was and generates a set of data in XML format which is based on the results. For example:
User searches for all entries with Forename = John; Results are listed in a gridview as per expected.
User then presses button with XML on it, and page pops up with just the XML output on it, i.e. whatever results are on the gridview but in a nested XML format
<records> <record> <Forename>John</Forename> <Surname>Smith</Surname> <Email>j.smith@blah.com</Email> <Ext>1234</Ext> <DeptList>History</DeptList> </record></records>
I have created a stored procedure which will take the parameters from the search boxes and return the above information, but I don't know if this is the best way. Here it is for those interested:
CREATE PROCEDURE ps_record_SELECT_NameSurnameEmailExtDeptasXML
@Forename varchar(50),
@Surname varchar(50),
@Email varchar(50),
@Ext varchar(4),
@DeptList varchar(50)
AS
SELECT Forename, Surname, Email, Ext, DeptList
FROM dbo.record
WHERE Forename LIKE COALESCE(@Forename,Forename) AND
Surname LIKE COALESCE(@Surname,Surname) AND
Email LIKE COALESCE(@Email,Email) AND
Ext LIKE COALESCE(@Ext,Ext) AND
DeptList LIKE COALESCE(@DeptList,DeptList)
FOR XML AUTO, ELEMENTS
If someone could be kind enough to help me out with this, I'd be really grateful.
Many thanks,
Tom
Last night I face performace problems in web site. I got like 100 000 users and site fails because of sql-connections.
Currently I'm making a new connection for every query, and my code looks like this:Public Shared Function executeQueryReturnDataset(ByVal strsql As String) As DataSet
Dim sqlConnection As New SqlConnection(Connstr)
Dim sqlCommand As New SqlCommand(strsql, sqlConnection)
Dim sqlDataSet As New DataSet
Try
sqlConnection.Open()
Dim myDataAdapter As New SqlDataAdapter
myDataAdapter.SelectCommand = sqlCommand
myDataAdapter.Fill(sqlDataSet)
Catch e As Exception
Console.Write(e.ToString())
Finally
sqlConnection.Close()
End Try
Return sqlDataSet
End Function I was wondering would it be better to save an instance from connection object to memory and use that same connection for all querys?
Background:I am using Visual Studio 2005 Standard SP1 to create an ASP.NET website that accesses an SQL 2005 database. I am using vb.net as well.I am passing an ID in a query string from one page to the next, where I retrieve it using an SQLDataSource. The data will only be one row; it is returning customer information.Problem:I need to be able to get specific fields from the SQLDataSource and populate some textboxes. Honestly, I'm not really sure where to begin. For the sake of argument, let's say that I am working with this:'My TextboxDim LastName As Textbox'My SQLDataSource (filtered by query string using the customer's ID to only get 1 customer's information at a time)SQLDataQuestion:So, how would I go about retrieving the lastname field from SQLData and inserting it into the textbox LastName?Thanks, J'Tok
View 2 Replies View RelatedDears,
I'm using FormView to insert and retrieve data from SQL Server Database 2000. I'm using stored procedure to insert and display data by using C#.
Every time I insert the data, I can't retrive it. To retrieve all the inserted data, I have to close my website, and then reopen it again.
I close the connection every time after using the stored procedures, but nothing happened.
What do you think the problem is ??
Let us suppose that there are 900 rows in One table, and that table contains neither primary key, nor Identity column.
How can I retrieve all the roows from 201 to 250?(like the middle limits)
In oracle there is a property called RowID, but is there any such item in SQL server?