DataSource Controls :: Search On Xml Field In Table Of Sql Server?
Feb 12, 2010
suppose i have few fileds in table and one filed is xml type. how to search this.
the xml which is stored in my xml filed like
<OpenShipments xmlns="x-schema:C:UPSLabelOpenShipments.xdr">
<OpenShipment ShipmentOption="RS" ProcessStatus="">
<ShipTo>
[code]...
we can query sql server data very easily but i dont know how to query the xml data which stored in xml filed.how to query address field in my xml or how to query Options field,how query CompanyOrName.
please help me to construct the sql. guide me in detail with few sample for querying xml datatype.
In of my application I am using the database to store and retrive images. The table stores the documents and the datatype of the field is IMage.
Now with Sql Server 2008, i would like to upgrade this to use this new feature. WHat changes do I have to make in terms of code and also in the database.
I'm assuming I need to use a pivot table from what I've found so far. However, every one that I've seen has not had the date setup like I have.
I have a Gridview on my page which I need to be populated like so
Date NCMR
1/1/10-1/31/10 #
2/1/10-2/28/10 #
I want to do this for 10 months... bottom record being the current month (or last month). I would also like the Date to format to January 2010 I used the above date format because that is how it is in the table. Basically, I need to know how to create the pivot table to search the table for 1/*/10, 2/*/10, etc
I want a word search i doing the search like this , but it is giving character wherever there is in the string,if i give two character like ok it is searching for a full string where ever Ok is there select * from table1 where textfield like '%word%' this query would match word but also wordabc how can i make it aware of delimitions
I have a gridview with two textboxes (first name & last name) and a linkbutton to search results inside the gridview.
I have made select parameters as well and the search works if i write in both textboxes. However, if I leave one textbox blank, i don't get any results.
[code]...
How can I get results from searching only one textbox?
How i can make the normal search string to sql server full text search parse, because when we are user enyer search text "how to run windows schedule in C#", in database we have article to to this, but data not returning and sometime is say error in key word and etc.
I have an SQL table which looks similar to the following:
Title | Author | Tags 'title1' 'author1' 'horror, steven king, clowns' 'title2' 'author2' 'childrens, roald dahl, chocolate'
Let's say I wanted to find the name of a book that has the tag 'clowns' in it...how can I do this? 'clowns' is a substring of the tag for 'title1'...but how do I search for substrings? I have tried to use the "LIKE" keyword but obviously it doesn't work in this situation.
I have a form that submits one entry to a 'Listings' table and multiple entries to a 'ListingLocation' table depending on how many cities the user selected. When the listing is created, I need the auto generated 'id' field to input into each of the the listing rows of the 'ListingLocation' table. This seems like it should be simple but I cannot figure it out. I currently have it hardcoded to enter '22' where I need it to enter the listing id.
I need to select a field value from a database table. If the value read is '1' then I need a 'Y' to display as output in the select statement. I haven't figured out how to do this yet and queries I tried that did not work.
select IsIata from Table_1 CASE WHEN (IsIata = '1') THEN 'Y' select IsIata CASE WHEN (IsIata = '1') THEN 'Y' from Table_1
I have a table added to a dbml file in my website. The website is already deployed to Live server. Now I have been instructed to a add a new field to the table. The field will be defaulted as getdate().
I don't plan to modify my dbml file for this change since the filed holds a default value. Will it cause any issue(performance related or any other). I am sure that I will not have to use the newly added field in the website. I am new to LINQ.
I have a SQL Server table with a number of fields that are defined with the "Float" data type. I'm running a simple query to retrieve the data from this table and use it to populate a Data Table in my C#.net application.
I just discovered that the data type of the Data Table fields is Double, not Float as I expected. As such, when I store a value like 0.157 it ends up getting stored like this: 0.15700000524520874
I'm aware that such extra "noise" occurs when such a conversion occurs. My major question has to do with why .Net didn't set the Data Type to Float (ie. Single) when the table was first populated?
I have a table in SQL where I insert data (CatCodes) using C# code but I want to insert ID code (DealerID) with it as well. Following is my Stored Procedure and C# code:
[Code]....
C#:
[Code]....
As you can see, I am insering CatCode values in Categories table using string cbspecialities.
How can I insert DealerID the same way? DealerID is automatically generated in tblDealers table when "InsertExternalInfo" asp.SqlDataSource control runs "InfoInsert" SP.
I am trying to update my table ARTICLES and it has a field COUNTER, Everytime a user enter this ARTICLES, the page_load event runs a LINQ to SQL query taking the QueryString as parameter, increasing the COUNTER field in 1.
I have a insert into statement thats inserting my numbers from a numeric text box on my aspx page. I am choosing the Amount Type then putting in a a number.
So i have a drop down that i select example Direct Charge then i enter 600.00 and click add it puts that in to the databse for me woot..
I then put in say Misc Credit and enter 200.00 it inserts in that into the database woot.
The look up table that has my amount types has a field called numeric opperator which is either + or - the Credit being a - and the Charge being a + then my stored procedure takes all my + numbers and adds them up and subtracts all the - numbers.
I want to change that so that it actuall be inserted into the table as a - number form the start. So I want choose Misc Credit enter 200.00 and click add then my event says o yea thats a credit insert 200.00 as -200.00
I have a table named RECIPE which is linked to an other table name RECIPE_STATUS
In RECIPE table I have a field named Status_Id which contains the name of the Status which is fetch from the RECIPE_STATUS table based on this ID.
The RECIPE table sample information are as follow :
ID Name Status_Id ==================== 1 Test1 0 2 Test2 1 3 Test3 2
The RECIPE_STATUS entries are as follow:
ID Name ======== 0 Locked 1 Running 2 Free
From an ASP.net page I would like that my user is capable of changing the RECIPE_STATUS from a given recipe. For that I have a DataGrid which is bind to the RECIPE table.
But then what I would like is that the STATUS shown to my user to be the name Locked,Running or free depedning on the Id
How can I bound in my Datagrid the Recipe.Status to be bind to RECIPE_STATUS.Id ?
IN addition to that, If my user change the status for a given recipe, it should be properly save.
I have a website which uses full text search. It was working fine on my web host. LAst month, I changed hosting to godaddy.com. They dont support full text search and now my website is not working properly. what change should i make in my stored procedures that they work same as they work with full text. what is alternative to full text search. I tried using LIKE but it doesnt work same way.
I need to write a query to populate a gridview with minimum rates in ascending order for a month from three tables.For example i have three tables A,B,C as follows
TABLE A TABLE B TABLE C Id id id Quote_no Quote_no Quote_ no Name Rate1 Equip_name Rate2 E_rate Date R1_rate R2_rate Date
Now if the data is as follows
I need to get the Name from Table A ,minimum Rate1 and Rate2 values for a month in a dropdownlist from date field from Table B and the corresponding minimum rate(E_rate) for Equipment E1 and E2 only from Table C for the month in ascending order group by Name.
TABLE A
id Quote_no Name 1 101 XYZ
2 102 ABC TABLE B id Quote_no Rate1 Rate2 Date
1 101 105 200 12/11/2010
2 102 90 210 15/11/2010
TABLE C id Quote_no Equip_name E_rate R1_rate R2_Rate Date
I am actually using 2 type of queries in my website e.g
These Queries are from my stored procedures
SELECT * FROM TblName WHERE FREETEXT(ColumnName, @parameter)
AND
SELECT * FROM TblName WHERE CONTAINS((Column1, Column2), @parameter))
I have enabled Full Text Indexing on my tables both local and on my hosting company MS SQL Server.
The above queries are working fine on my local database and return results but these queries return nothing when i try to run them on hosting company MS SQL Server.
I have identical data on both MS SQL Servers and both servers are 2008.
I'm implementing an AJAX autocomplete feature where the user types into a textbox and the webpage generates suggestions (pretty much like how Google's search box works). I got the AJAX part to work right, but for some reason, my database query does not.Let's say I want to find "Chicago". Using the following query does not find it.SELECT TOP 10 citynames FROM city WHERE CONTAINS(citynames, 'chi*')But using this query does.SELECT TOP 10 citynames FROM city WHERE CONTAINS(citynames, 'chicago')By the way, "citynames" is an ntext type field and I'm using SQL SERVER 2008.Does anyone know why my first query can't find "Chicago"? I've been staring at that query for hours, yet can't find anything wrong with it. Something wrong with the database maybe?
Trying to figure out how to handle data returned for a site search from a description field. I don't think I want to display all the text from the description field, but I also don't want to "chop" a word in half. How do I get the first 100 characters, plus a few extra letters if needed to prevent a word from being chopped in half. In some cases, the description field is less than 100 characters, so all the text will be displayed. However, if the description does need to be chopped at 100, I'd like to finish the word and add ... to indicate that there is more text.
I am working on a website hosted with GoDaddy, SQL Server 2005. I have a table in my SQL Server database with a full-text index. On my website, a user can type in search terms and the terms are then passed to a stored procedure in the database which performs the search. The first search takes about 45 seconds to get results. Subsequent searches return results immediately. I found a description of this problem in a Microsoft knowledgebase article:http://support.microsoft.com/kb/915850/en-usGoDaddy tech support says I need to purchase a dedicated server to make the changes suggested in this article.Surely there is some other solution. Does anyone know how to avoid this delay without changing the server configuration?
