I have a table in which there is one column named "Address1"
Now in this column all the data in this displaying as follow:-
sector-2, Guj,676
Sector-4,AHD,8989
Now i want to modify all the data in this column,
if there is any "," comma found in the data,
it should delete that comma from the data?
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
1
101
E1
60
0
0
12/11/2010
1
101
R1
0
110
0
12/11/2010
1
101
E2
80
0
0
12/11/2010
2
102
R2
0
0
300
15/11/2010
2
102
E2
100
0
0
15/11/2010
2
102
R1
0
60
0
15/11/2010
2
102
E1
230
0
0
15/11/2010
I'm having a gridview in my aspx page with checkbox, User can select one or more than one record(s) at a time and clicks on delete button, Then i'm constructing an object of my DTO and adding all the checked row's Primary Key (DataKeyNames - GUID's in case) to a serializable class and generating an XML and sending it as parameter to the Stored Procedure.
My Doubt is, can i bulk delete all the rows from database table those are present in my XML document??
If it works, I can do bulk update with XML only, i already did bulk insertion by generating an XML..
My Another Question is, Will it affect the performance? Each time when i construct an DTO object and adding it to the Generic List??
WHich one is better, I mean ObjectDataSource or SQLDataSource or my XML method?
My XML will be like this
<root>
<Customer>
<CustomerID>GUID1</CustomerID>
<CustomerID>GUID2</CustomerID>
<CustomerID>GUID3</CustomerID>.....
How can I return a comma delimited list of codes for each client, to a temp field in the results of a select statement in a stored procedure?
Is it posible to get a comma delimited list into a temp field of a temp table?
I have a table tblCodes that has the following fields ClientID, CodeID
I have a table tblCodeLookup that has the following fields CodeID, CodeDecription
I need to get a temp table with two fields ClientID and a field that holds a comma delimited list of CodeDescriptions for each clientID. Maybe the tmp fiel would be named ClientsCodes Select clientID, ClientsCodes
I've requirement where user can enter multiple columns like first name, lastname, email, age, sex fields and insert them into database.on my frontend aspx page, i'll be showing a single row initially with 5 text boxes(first name, lastname, email, age, sex) and a button to add more columns. when user clicks on add more, another 5 textboxes will be shown and then i'll be taking 5 + 5 (textbox values) and comma seperate them and need to insert into a table with primary auto increment key ID and the remaining 5 columns(firstname, lastname, email, age, sex). I previously worked on the same requirmeent but with only single column. Now i need to insert for 5 columns, How can i do this? Below is my stored proc which i used for single column insertion.
[Code]....
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 one table in sql server called student_info
In this table keys kills column contain value like below
'c,c++c#'
'c++,c,c#'
'c,c++,c#,asp.net'
etc...
I want to search the student information based on keyskills like naukri
1.Any keyskills from textbox1
2.All keyskills from textbox2
3.Except keyskills from textbox3
When I enter c,c++ or c++,c in textbox1 then it will dispaly all the student whose have c,c++ keyskill
How to achieve this.
I have one table named 'dbo.ac_Products' Within this table there is a column named 'ProductId' There is also another table named 'dbo.ac_CatalogNodes' Within this table there is a column named 'CatalogNodeId' and a column named 'CatalogNodeType'
I need to delete all the rows from 'dbo.ac_Products' and 'dbo.ac_CatalogNodes' where 'ProductId' = 'CatalogNodeId' and 'CatalogNodeType' = 1
I am deploying the solution on the server i want no user can deleted the table by using the sql injection, i want to put restriction on the table that no one can delete the table. in short drop table should not execute.
View 5 RepliesI'm trying to delete some data in my database where no data is inside the field name and field id
select freelancer.id, freelancer.name FROM [bewerber].[dbo].[freelancer] WHERE freelancer.id = 'NUll' and freelancer.name = 'NUll' ORDER BY freelancer.vorname
but it seems to be wrong.
am getting the value of quan and quantity like below i have given... so i want to subtract these two and update it in item_quan field in c_item table. but quan and quantity getting from query giving correct value but its not getting updated in the query below given the bolded update query below...
da7 = new SqlDataAdapter("SELECT item_name, item_quan,item_cat FROM c_item WHERE (item_name = '" + fname + "') AND (item_cat = '1')", con);
I have one asp.net application using Sql server as back end.. in sql server database i have one table which consist two fields.. ItemName and RateI have one notepad file which consist around 700 ItemName with Rate..
So how to get this ItemName and Rate in my Table...
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 am trying to delete using Gridview from child first and check if there is no child to delete in parent table i am using sql datasource in the delete tab in sql datasource i put DELETE FROM table1 WHERE (IDtable = @IDtable) IF (SELECT count(*) FROM table1 WHERE parent_dtl_id = 66) =0
BEGIN delete from parent_dtl where order_dtl_id= 66 END
i deleted all items,deleting from 2 tables child and parent was successfully on the last row from gridview but i have this error on my page on the last delete Specified argument was out of the range of valid values. Parameter name: value
if It is okay to delete entry directly in any aspnet_* table ? because i screwed something.
View 3 RepliesWithin my stored procedure I have a piece of SQL that is supposed to remove from a temporary table, any values that are not set to '1' for a particular field, but this does not work as required.
The SQL in question looks like this:
[CODE]
[code].....
Can you see what I have done wrong here , as the test for tab3.THIS_VALUE = 1 is returning results for THIS_VALUE = 0 also, but all I want is a table that has values with THIS_VALUE = 1 only.
i want to delete data from two table using left outer join..
here is my query:
delete from projects p LEFT OUTER JOIN emp_project empp on p.serial_no=empp.projectid where p.serial_no=7
but i am getting error:
Incorrect syntax near 'p'.
here my code-
<asp:TemplateField HeaderText="HIGH RISK (10-12)" ItemStyle-HorizontalAlign="Center">
<ItemTemplate>
<asp:Label ID="lblHighrisk" runat="server" Text='<%# Eval("URANGE").ToString().Split('-')[0] %>' />
</ItemTemplate>
</asp:TemplateField>
but that is giving compile time error 'Server tag is not well formed'
how can i split the frist word in sql if the field contain ',' otherwise, only show the data value.
if the field is 'A,B,C'
I would like to select A as a result.
if field in a sql data table is encrypted or not ??!!
like a function that return true or false>>> IsEncrypted()
suppose I am having sample table like below.
I need to remove duplicate orderid's and i need all column names also while doing query.
note that rows are not in the order. Order will be like this only.
I am able to get the records which are duplicated but not able to delete further.
In this case how to delete?
My consideration is that only distinct orderid's should be there in the table. Not bothering about the which orderid row is being deleted.
name orderid destination
a 1 aaa
d 2 ddd
b 1 bbb
f 3 fff
e 2 eee
g 4 ggg
h 3 hhh
c 1 ccc
In this query below I need the total of the 'amount' field.
SELECT
[Amount]
FROM
deals_DealBucks
CDC or Change data capture is a new feature in SQL Server 2008, which is an ability to record changes to table data into another table without writing triggers or some other mechanism, Change data capture records the changes like insert, update, and delete to a table in SQL server.I have sql 2005 and I have created triggers and tables CDC to capture the data. everything functions good and the data is being updated in the _CDC tables. all i need to do now is to generate the updated data as a report (excel/html)- what should I do ......?i need to show only the updated columns when user selects the date periods ( Range between dates ) and the person name - i need to display any updated info about that person during that period. So this updated columns about this person should be displayed in excel formatted column wise.
View 2 Repliessuppose 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.
I have two pages:
1) login and send username,password, machineID and a rand string to the server
2) check the machineID whether it exists in the table.
Problem is the checking machine procedure:
select MachineID from computer where MD5MachineID=@MD5MachineID2 and SUBSTRING((master.dbo.fn_varbintohexstr(HASHBYTES('MD5',MachineID+@RString))),3,32)=@MIDST
RString,MD5MachineID2,MIDST are submited by Login MD5MachineID is stored in table.
I need the check if MD5(MachineID+RString) is match the MIDST (submited).
Now the situation is :
if RString is a fixed string(not a variable).like 'abcdefg', the MD5 is correct.
if I use the RString (submited value as a variable), the MD5 is incorrect.