.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

How do I create a query that joins two tables and returns a single entry with the most recent update

Posted By:      Posted Date: September 21, 2010    Points: 0   Category :Sql Server

Say I have the following two tables (Purchase & Customer). 

CustomerID StoreName PurchaseDate
1 Asheville 9/14/2010 15:49

View Complete Post

More Related Resource Links

How to create a single entry page for site collections in MOSS 2007



I have  7 site collections containing a few hundred team collaboration sites. Some users are internal (intranet) but most are external (extranet). Extranet users are experiencing problems with losing links to their sites and forgetting which URLs to use. So I would like to create a single entry page that all users can enter after the logon process, which shows a list of sites they have permission to access, and other user-specific information. Perhaps relevant documents, tasks, recently modified documents etc.

I know this sounds like a 'My Site' but we want to use use a general welcome page instead. We use Windows authentication.
What is the best way of doing this and is it something that can be done in SharePoing Designer or does it need to be done in Visual Studio?

Many thanks in advance.

Running a single SQL update query on multiple foxpro databases

Hi, Env : SSIS 2008 My requirement is to run a update SQL statement on multiple foxpro databases. The fox database paths are available in a SQL server table.  I've created a for each loop control and assigned the database path to a variable named dbpath. Within the loop, I added an "execute sql task" control. Now what properties are to be set to get the requirement done. Appreciate any help. Regards. BH  Please click "Mark as Answer" if this resolves your problem or "Vote as Helpful" if you find it helpful. BH

help with query - update from 2 tables

hi i have 2 tables     table1: num,X,Y     table2: num,X,Y i need to update X and Y in table1 where table1.num = table2.num how to do it ? i need it in Oracle query (i think that in sql server it will work too) thank's in advance 

Most Recent records from one of the table among the tables in multiple joins.


Hi All

I have a single select query with multiple table joins in it.

I want to retrieve the Most Recent records from one of the table among the tables in multiple joins.


(select t1.col1, t2.col2 , t3.col3, t4.col4, t4.Add_Date,t5.col5,

from dbo.table1 t1

left outer join dbo.table2 t2 on t1.col1=t2.col2

left outer join

(select col3 from dbo.table3 where condition1="expr") as t3

on t1.col1=t3.col3

left outer join dbo.table4 t4 on t1.col1=t4.col4 order by t4.Add_Date

left outer join dbo.table5 t5 on t1.col1=t5.col5 )

Problem is that, I am not able to use the "Order by" for a particular table.



Insert/Update data to multiple tables on a single button click


Hi experts,

I need to insert/update data to 3 or more tables on a single button click.What is best method for achieving this?I am using mysql as my DB.

Thanks for your valuable reply in advance.

update two tables from single grid view using stored procedure PLZ help


using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace INV_DN
    public partial class Form1 : Form
        SqlConnection con;
        SqlDataAdapter da1;
        SqlCommand cmd;
        //  SqlCommandBuilder cb;
        DataSet ds;
        public Form1()

        private void Form1_Load(object sender, EventArgs e)
            con = new SqlConnection("User Id=sa;Password=123;database=amar");
            cmd = new SqlCommand("getdata", con);

Create multiple XML files from a single query



I'm using this code for creating and xml file out of a SQL2005 table:

select (select 
    from ProductsInfo_ZAP p
    for xml path('PRODUCT'),TYPE, ROOT('PRODUCTS')
) for xml path(''), ROOT('STORE')

Is there a way for me to create multiple xml files, one for every row, going by the primary key column, or do I have to create a separate query for each row?

I would also wondering what is the syntax for transforming this query into a stored procedure


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.

SqlCommand.ExecuteNonQuery() returns -1 when doing Insert / Update / Delete

Sometimes you end up with a return value of -1 when using the SqlClient.SqlCommand.ExecuteNonQuery method.

Why is that?

Well, the ExecuteNonQuery method is there for statements for changing data, ie. DELETE / UPDATE /INSERT, and the returned value are the number of rows affected by that statement.

When checking the documentation we can see that there are some conditions that return -1.

For UPDATE, INSERT, and DELETE statements, the return value is the number of rows affected by the command.

When a trigger exists on a table being inserted or updated, the return value includes the number of rows affected by both the insert or update operation and the number of

rows affected by the trigger or triggers. For all other types of statements, the return value is -1. If a rollback occurs, the return value is also -1.

merging multiple tables in a single dataset to single table


 i have a stored procedure which returns three tables to a dataset ..... now i need to merge all three tables to a single table from d same dataset 

like dataset1 has table1 table2 and table3 .... i want all the three tabels to be merged into dataset1 itself .... instead of three diffrent tables so that i can show all three table data in a single datagrid  as a compact data and combination of 3 tables from d single dataset.....

can some1 help me please.....

Event Hanlder to update other SQL tables



I'm writing a small app to allow viewing and editing of a single SQL table (the _Assets table).

I have a form-view that allows the data to be viewed (ItemTemplate) and edited (EditItemTemplate).

Everything works well. All the SQL editing is done in the Mark-Up using a simple SqlDataSource, asp:Parameters and data-binding, (ie. there is no VB code behind).

However, I need to write an event-handler which updates other tables when the EditItemTemplate INSERT button is used.

This event handler needs to update OTHER SQL tables.

The idea is to create a HISTORY of changes (Updates) made to my _Assets table.

It would look something like this:

IF the value of the _Assets.Comments field is changed by the formview, then:

Open SQL connection to _CommentsHistory table

Update _CommentsHistory

SET CountrySerial = asp:Parameter Name "CountrySerial" from SqlDataSource ID="FindAsset"

SET OldComment = [the original Comment] asp:Parameter Name "Comments" from SqlDataSource ID="FindAsset"

SET NewComment = [the New Comment inserted in the formview] TextBox ID="CommentsTextBox" from FormView ID="FormView1"

SET ChangeDate = { fn (now)}

Set ChangedBy =

SqlDataAdapter.Update related tables



I would like to get 2 related tables from SQL Server to DataSet, insert new rows and save them back to SQL Db.

Let's say i have 2 related tables as shown below...

Table1 Table2
test1ID (Primary Key) test2ID (Primary Key)
data test1ID


In Sql i have Diagram with relationship and Update Rule is set to Cascade

I am trying to get them to dataset, insert "master" and "child" row and save them back to db.

I tried different approaches (none of them workes) and this is one of them:

Dim ds As New DataSet()
        Dim conn As New SqlConnection(ConfigurationManager.ConnectionStrings("NastanitveConnectionString").ConnectionString)
        Dim sql As String = "SELECT * from Table1;Select * from Table2;"
        Dim d

LINQ query with multiple joins, problem


I am using a LINQ query with multiple joins, the last join does not return any values even though values exist in the database. Below is my code.

when the query returns suiteNameTrg and SuiteTypeTrg are empty, all other values are returned correctly.

string suiteNameTrg = string.Empty;
            string suiteTypeTrg = string.Empty;
            using (DataClassesDataContext db = new DataClassesDataContext())
                    var productQuery = from assets in db.ASSETs
                    join relocatableUnits in db.RELOCATABLE_UNITs on assets.RUID equals relocatableUnits.RUID into assets_units
                    from relocatableUnits in assets_units.DefaultIfEmpty()
                   join build in db.BUILDINGs on assets.BUILDING_ID equals build.BUILDING_ID into assets_bins
                   from build in assets_bins.DefaultIfEmpty()
                   join test in db.TEST_SUITEs on assets.TEST_SUITE_ID equals test.TEST_SUITE_ID into test_bins
                   from test in test_bins.DefaultIfEmpty()
                    join testTrgt in db.TEST_SUITEs on assets.TARGET_TEST_SUITE_ID equals testTrgt.TEST_SUITE_ID into testTrgt_bins
                    from testTrgt in testTrgt_bins.DefaultIfEmpty()

                    select new

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 = ""

How to update Sql Server related tables from Dataset(SchemaFill)

Hi!I would like to update new records that get inserted in DataSet to Sql Server Database.Below is a working example of how to do this if you use "DataAdapter.Fill(ds)". But i don't want to fill data, becouse i don't need data, i only need shema (I will use this dataset to insert new data to Sql), so i would like to use "DataAdapter.FillSchema(ds)"Please help me out at least with this one... i'm stuck... can't continue untill i crack this nut.<%@ Page Language="VB" AutoEventWireup="false" CodeFile="Default2.aspx.vb" Inherits="Default2" %> <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head runat="server"> <title></title> </head> <body> <form id="form1" runat="server"> <div> <table> <tr> <td> <asp:GridView ID="GridView1" runat="server" ShowHeaderWhenEmpty="True"> <Columns> <asp:CommandField ShowSelectButton="True" /> </Columns>

Create Table from MDX query

Hi,I want create a table from the result of the MDX query. When I finish the wizard of import data in SQL Server Environment. at the last stage, the error is : Pre-execute (Error) Messages Information 0x4004300b: Data Flow Task 1: "component "Destination - Query" (31)" wrote 0 rows.(SQL Server Import and Export Wizard) please guid  me.Thank you a lot.Naeimeh

Application to Constantly query Table Values and Refresh with most recent value

Hello, I must apologize in advance because I am not sure if this is the correct place for this question. I am looking for ideas on how to build an application that in the most basic sense contains a field with the most current data from a table in a SQL Server database (this would be based off of a datetime timestamp in the table). I am new to this kind of problem and am unsure where to start looking for solution ideas. The table value updates variably and could possibly update every second to every hour. I would like to design an application that would be lightweight to both the server and the user's machine. This would take the place of an unneeded report on my Reporting Services server. Any and all suggestions would be welcome because I am not sure of options I even have on this. Thanks in advance for your time and consideration, Matt
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