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

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links


Posted By:      Posted Date: October 09, 2010    Points: 0   Category :Sql Server

please help,







View Complete Post

More Related Resource Links

join - delete table(s)

I have two table:Parent has the key Id, UserIdChild has PaymentId, UserIdTo view the data using DropDownList.I want to delete information that is selected in the DropDownList (SelectedValue).My code:        products_to_delete var p = from and db.tbl_Payments                                 WHERE p.PaymentType.Contains (DropDownList1.SelectedValue)                                 Join in and db.tbl_PaymentDetails                                 He p.ID equals s.PaymentId                                 select p;        db.tbl_Payments.DeleteAllOnSubmit (products_to_delete);        db.SubmitChanges ();This code onl

Can't perform Create, Update or Delete operations on 'Table(outbox)' because it has no primary ke



I'm a beginner in asp.net and I have some problem inserting data into sql server 2005 with linq.


my code is : 


        Dim Result As New SMS.SendResult
        If Result.status = 0 Then
            Label1.Text = "Your Message Sent Successfuly"
            Dim db As New IGSSDataContext
            Dim out As New outbox With {.ClientID = intRandomNumber, .From = User.Identity.Name, .TO = TextBox1.TabIndex, _
            .Time = Now, .Message = Message}
            out.ID = (ID)
            TextBox1.Text = ""

select max record to join another table sybase

select a.pono,(select (user) from user where userid=a.userid having date=max(date)) as user from a inner join b on a.no=b.no  in the result , i have selected the same id and retrieve two records every thing are same except the date how can i select the record out of two record which date is max date as the where Clasuse to select correct user poid    date                name 1        12/08/2010      Mary 1        20/08/2010      Peter   now i would like to select name which id=1 and date is max and then use the name to join another table because name is foreign key  

Delete Record From Table A that Is Not In Table B

I have two tables; Table A id, name 101, jones 102, smith 103, williams 104, johnson 105, brown 106, green 107, anderson   Table B id, name, city, state 101, jones, des moine, Idaho 103, williams, Corvallis, Oregon 104, johnson, Grand Forks, North Dakota 105, brown, Phoenix, Arizona 107, anderson, New York, New York   I need to delete records from Table A that are not in Table B.  My front end is writen in .net and I am using Data Access Layer along with a Business Logic Layer for data interaction.   I have tried at least seven variations of joining, right outer join, left outer join resulting in wiping our the entire table or nothing at all; not to mention deleting the record that ought to remain and keeping the record that needs to be deleted!   In my BLL I tried to capture the rowsAffected for the deletion by using-without success. Dim rowsAffected As Integer = Adapter.ID_Deletion(ID) If rowsAffected = 1 Then Exit Function Else Return rowsAffected = 1 End If   Please help.   MsMe.

I have relation many to many - how can I delete records from intermediate table ?

I have tables: Books id title Authors id name BooksAuthors book_id author_id Entity Framework generated only models for tables Books and Authors in relation many to many. How can I delete from table BooksAuthors all records where book_id =5 ?

How 2 join Multiple Keys based table???

I have a table INC with 2 Columns/Fields, i.e. YR and CL set as primary keys by selecting both the columns and selecting primary key symbol with right click. How to set up a FK with the other table INC_DTL's CL which I seek to be restricted to a combination of the INC's 2 fields? Thanx in advance.

T-SQL 2005 for same table join?

 I have below table with two columns.. Type      Code AB        Company_chris BC        Company_chis DE        Company_chis AB        Company_bob AB         Company_James BC        Company_James AB         Company_mark DE         Coampny_mark BC        Company_scott Unique value in TYPE column : AB , BC, DE Primary Key is :  TYPE and CODE I’m looking output in result query ......... Code                  Type1     Type2     Type3 Company_chris      AB         BC           DE Company_bob      AB         NULL       NULL Company_mark      AB         NULL       DE Company_scott      NULL      BC        NULL   Any t-sql 2005? Thanks.

how to return records in squence of inner join table?

Hi, I have test database with following script. I am trying to explain my problem with this sample db script. I am creating a temp. table with the ordered column from other table and then using that table to join the other table. If you notice the output of the below select query, the returned rows from first table are in the sequence of insertion not in the sequence of the temp. table. Is there any other way to retrieve rows in the sequence of temp. (joined) table? CREATE TABLE [dbo].[Table_2]( [c1] [int] NULL, [c2] [nvarchar](50) NULL ) ON [PRIMARY] GO INSERT [dbo].[Table_2] ([c1], [c2]) VALUES (1, N'z') INSERT [dbo].[Table_2] ([c1], [c2]) VALUES (2, N'y') INSERT [dbo].[Table_2] ([c1], [c2]) VALUES (3, N'x') INSERT [dbo].[Table_2] ([c1], [c2]) VALUES (4, N'a') INSERT [dbo].[Table_2] ([c1], [c2]) VALUES (5, N'b') INSERT [dbo].[Table_2] ([c1], [c2]) VALUES (6, N'c') CREATE TABLE [dbo].[Table_1]( [c1] [int] NULL, [c2] [nvarchar](50) NULL ) ON [PRIMARY] GO INSERT [dbo].[Table_1] ([c1], [c2]) VALUES (3, N'x') INSERT [dbo].[Table_1] ([c1], [c2]) VALUES (2, N'y') INSERT [dbo].[Table_1] ([c1], [c2]) VALUES (1, N'z') INSERT [dbo].[Table_1] ([c1], [c2]) VALUES (6, N'c') INSERT [dbo].[Table_1] ([c1], [c2]) VALUES (5, N'b') INSERT [dbo].[Table_1] ([c1], [c2]) VALUES (4, N'a&

Join Filter for n to n Table

Hi all, We have a merge publication system in SQL 2008 R2. Publication is Enterprise Edition, Client's are Express Edition. Basically, we have 3 tables Roles, UserRoles,Users. Role : RoleID, RoleName columns User : UserID, UserName columns UserRole : UserID,RoleID columns   I want to filter these tables by HOST_NAME() function, which is equal to UserID for each subscription. IF i set Dynamic Filtering for User table (UserID=HOSTNAME()) , i receive only 1 row in User table in subscription database, ok. And also i set same Filter to UserRole table, and i can get only needed RoleID's to UserRole table, ok. But when i want to receive only necessary Roles to subscription database Role table, it does not work. I tried to use a Subquery, but i understood that it is a static filtering method. I tried Join filter for Role table, but i always receive all roles to Role table. I tried "SELECT <published_columns> FROM [dbo].[Role] INNER JOIN [dbo].[UserRole] ON [Role].[RoleID] = [UserRole].[RoleID]". But it receives all roles to Role table. I tried several Join filters for these tables but can't find a solution. How can i handle this?   Best regards.      

Delete objects from table using another table to select rows - help needed

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' = 1Any help would be greatly appreciated!

3 table join how to question

I have an sql statement I want to convert to LINQ (VB.Net).  I cannot figure it out and was hoping for some help.SELECT COUNT(*) AS Expr1, SiteInfo.SiteName FROM Advice INNER JOIN InformixSource ON Advice.DSSERVER = InformixSource.PODServerName INNER JOIN SiteInfo ON InformixSource.SiteNum = SiteInfo.SiteNum GROUP BY SiteInfo.SiteName, SiteInfo.SiteNum ORDER BY SiteInfo.SiteNum  

Delete one table record if the no more record are associated from another table

Hello All,   In my scenario I have two tables say Table A and Table B. Table A has 3 records   Id Name Value 1 ABC 123 2 XYZ 456 3 PQR 789   Now in another table I have 4 records   Id FName Lname TableAID 101 ABC XYZ 1 102 AAA BBB 1 103 XXX YYY 1 104 PPP QQQ 2   Now I display table B records in gridview when I select 3 records say id 101,102,103 and click on delete button. Table B selected records should be deleted and its working fine.   The problem is that in Table A record 1 ABC 123 is only associated with that many records so when I delete that record from Table B at that time the associated record in Table A should also be deleted as no more association with that record in Table B.   So please give me query or appropriate solution for that.  

Inline table valued function, full outer join and cross apply

Good afternoon, I'm experiencing quite strange issue with our T-SQL code. We have inline table-valued function which is using full outer join on 1 = 1. The reason why we do it this way is - we need to combine one row from 2 different result sets either of them can have either 0 or 1 row. The strange thing, that if we have the situation when both row-sets are empty, function does not return the row (as expected). Although, select which uses this function with cross apply "uses" the result from the previous row. Below is the simplified example. First select with ID = 2 predicate does not return any rows. Although if I remove this filter, it re-uses Value2 from the previous row. create FUNCTION dbo.Func(@Id int) RETURNS TABLE AS RETURN ( with CTE1(Value1) as ( select 'Value1' where @ID = 1 ) ,CTE2(Value2) as ( select 'Value2' from CTE1 ) select CTE1.Value1, CTE2.Value2 from CTE1 full outer join CTE2 on 1 = 1 ) go select * from dbo.Func(2) go create table dbo.TestTable ( ID int not null ) go insert into dbo.TestTable values(1), (2) go select t.ID, f.Value1, f.Value2 from dbo.TestTable t cross apply dbo.Func(t.ID) f where t.ID = 2 go select t.ID, f.Value1, f.Value2 from dbo.TestTable t cross apply dbo.Func(t.ID) f order by t.id go Our SQL version is: Microsoft SQL Server 2008 (SP1) - 10.0.2531.0 (X64) &nb

Delete Duplicate Records from MySQL table


I have Revision column in Quote table with time stamp (MySql table) now i am viewing the entire date and time my end user want to view only Date he doesnot want to see time how can i pull only date.i am displaying revision column in two ways one in grid view and another one in field value

Query in my Grid view
Dim query As String = "SELECT QuoteNumber,Revision,Vendor,PartNumber,status,Customer,Requestor from quotes"
On select check box in grid view, filed values will be loaded with data.

field value
txtRev.Text = dt.Rows(indx)("Revision").ToString()




Joining Dimtime table by a referenced Join create a problem



I am trying to join a dimtime table by a refernced join to an orderlines table which is the fact.

(the referenced join was defined in the relationship matrix of the cube ).

The referenced table is the ordersheader which had no measures in it but only used a linking table between those two other.

The join from the dimtime to the ordersheader  is done by date and from ordersheader to orderlines by orderid

The problem is that in the cube browser measures are shown as they were multiplied across all dates (like the join was done in a wrong way )

I have created an sql query and the logic is working just fine when quering directly from the DB


What am I doing wrong

Join Tables For Definitions (Data Table/Definition Table)



I am well versed with Join/Outer Join/Inner Join. My question is not related to normal Joining of two tables. Its little complex. Let me see if I can explain well.

There are two tables, were data is stored.

Name_Info : nvarchar(10)
Date_Info : smalldate()
Spec_Info_1 : tinyint
Spec_Info_2 : tinyint
Spec_Info_3 ; tinyint

Name_Data : nvarchar(10)
City_Data : nvarchar(10)
State_Data : nvarchar(10)

To join these two tables, I use the following query (joined on Name_Info ON Name_Data)


SELECT Name, Date=CONVERT(varchar, Date, 101), City, State,
CASE Spec_Info_1 WHEN '0' THEN 'House' WHEN '2' THEN 'Shop' WHEN '3' THEN 'Bay' ELSE '-' END AS Spec_Info_1, 
CASE Spec_Info_2 WHEN '0' THEN '10 Feet' WHEN '2' THEN '20 Feet' WHEN '3' THEN '40 Feet' ELSE '-' END AS Spec_Info_2, 
CASE Spec_Info_3 WHEN '0' THEN '2 Windows' WHEN '2' THEN '4 Windows' WHEN '3' THEN '6 Windows' ELSE '-'  END AS Spec_Info_3
FROM Table_Spec_Data LEFT OUTER JOIN Table_Name_Data On Table_Spec_Data.Name_Info=Table_Name_Data.Name_Data  

InfoPath Forms : Adding rows dynamically from a SQL table along with a 'Delete' button per row


My requirement is something like this:

In a Task Approval form, I want to have a view which shows all the rows in a particular SQL table (in a infopath repeating table?). Along with each row, there should also be a button, on clicking which the row should disappear from the infopath view table. Do you think InfoPath can handle all this? :) 

To summarize:

1. How do I add rows to a table dynamically?

2. Per row, how do I create a button dynamically?

3. How do I remove the row from the view when the button is clicked?

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