Binding Gridview from two different tables

Hi all,

The Scenario:

Database1:Table1(callingPartyNumber,originalCalledPartyNumber, finalCalledPartyNumber, DateTimeConnect, DateTimeDisconnect, Duration)

Database2:Table2(Name,Number)

Output in Gridview:

callingPartyNumber

NameoriginalCalledPartyNumberfinalcalledPartyNumberdateTimeConnecteddateTimeDisconnectedDuration (HH:MM:SS)

I bind gridview programatically using DataTable and stored procedures. The data comes from a table (Table1) in a database (Database1) on SQL Server. The gridview displays fields callingPartyNumber, originalCalledPartyNumber, finalCalledPartyNumber, DateTimeConnect, DateTimeDisconnect and Duration in this order. All the columns in this gridview are databound columns.

I have another table (Table2) in a seperate SQL Server database (Database2) but on the same server which maps the callingPartNumber value with the name attached with that number. Note that the field names in Table2 are different from the field names in Table1. Is it possible to display the Name field also in the gridview after the first field callingPartyNumber and then the other fields.

Its like data coming from two tables into the gridview.

Thanks

I would suggest you to use a View in GridView by joining this tables.

CREATE VIEW dbo.Callers AS

SELECT dbo.Database1.Table1.* dbo.Database2.Table2.* FROM dbo.Database1.Table1 RIGHT OUTER JOIN dbo.Database2.Table2 WHERE dbo.Database1.Table1.CallingPartyNumber = dbo.Database2.Table2.Number

|||

SELECT callingPartyNumber,Name,originalCalledPartyNumber, finalCalledPartyNumber, DateTimeConnect, DateTimeDisconnect, Duration

FROM database1.dbo.Table1 t1

LEFT JOIN database2.dbo.Table2 t2 ON t1.callingPartyNumber=t2.Number

Change "LEFT JOIN" to "JOIN" if you only want records that have a name.

|||

Fabulous!!! Thank you so much. I guess I explained my problem nicely this time...eh...One question though, I didn't even get a chance to try your suggestion and the post was marked as answer within a few minutes of your posting it. I read somewhere, most probably in a post from a moderator, that they give us a chance (usually 48 hrs) to mark the post as an answer.

|||

I am still trying to figure out this but thought would share. You might know what I am doing wrong. When I do the LEFT JOIN, it pulls all the names right, but the total count of records is always a bit off than when I don't do a LEFT JOIN. I read about all the joins and LEFT JOIN is what I am looking for, but I think I am going wrong somewhere. I also have a WHERE clause added to the query.

CREATE Procedure GetCDR_LikeTollFree ( @.theDurationint =null,--to find duration more than theDuration @.totalDurationbigint =null,--to find the total duration for the result set @.callingPartyNumbervarchar(255) =null, @.originalCalledPartyNumbervarchar(255) =null, @.finalCalledPartyNumbervarchar(255) =null, @.dateTimeConnectdatetime =null, @.dateTimeDisconnectdatetime =null )AS BEGIN SELECT callingPartyNumber,Name, originalCalledPartyNumber, finalCalledPartyNumber,dateadd(ss, (dateTimeConnect + (60 * 60 * -5))+3600 ,'01-01-1970 00:00:00')AS dateTimeConnect,dateadd(ss, (dateTimeDisconnect + (60 * 60 * -5))+3600,'01-01-1970 00:00:00')AS dateTimeDisconnect, durationFROM db1.dbo.Calls t1LEFTJOIN db2.dbo.NumPlan t2ON t1.callingPartyNumber=t2.NumberWHERE (t1.callingPartyNumberLIKEISNULL(@.callingPartyNumber, t1.callingPartyNumber) +'%')AND (t1.originalCalledPartyNumberLIKEISNULL(@.originalCalledPartyNumber, t1.originalCalledPartyNumber) +'%')AND (t1.finalCalledPartyNumberLIKEISNULL(@.finalCalledPartyNumber, t1.finalCalledPartyNumber) +'%')AND (t1.duration >= @.theDuration)AND ((t1.datetimeConnect - 14400) >=ISNULL(convert(bigint,datediff(ss,'01-01-1970 00:00:00', @.dateTimeConnect)), t1.datetimeConnect))AND ((t1.dateTimeDisconnect - 14400) <=ISNULL(convert(bigint,datediff(ss,'01-01-1970 00:00:00', @.dateTimeDisconnect)), t1.dateTimeDisconnect))END

The query before inserting the LEFT JOIN was giving the correct number of records.

|||

Sorry about that...I am pretty sure the problem is at the table end. I have some bad data (duplicates) and thats creating a problem.

|||

Looking at your query, the only reason I can see that the record counts would be different is if in the NumPlan table, you have more than 1 name listed for a specific Number. If that is the case, then you will get one record back for each name.

As for your other question, moderators can also mark posts as answered (Submitters can always unmark them if need be). But it helps when people are scanning the forums looking for questions that haven't been answered, so I normally mark posts that I feel have a 95%+ chance of fully answering the question as answered. Unfortuantely, the moderator and MVP tags are a bit quirky and they come and go by themselves sometimes, so it's hard to tell who is a moderator/mvp and who isn't.

|||

Motley:

Looking at your query, the only reason I can see that the record counts would be different is if in the NumPlan table, you have more than 1 name listed for a specific Number. If that is the case, then you will get one record back for each name

You are absolutely right. I figured that out but it took some time. I keep trying myself and when I fall short of time, I post, and it has happened so many times that I figure out the solution to the problem after posting.

Motley:

As for your other question, moderators can also mark posts as answered (Submitters can always unmark them if need be). But it helps when people are scanning the forums looking for questions that haven't been answered, so I normally mark posts that I feel have a 95%+ chance of fully answering the question as answered. Unfortuantely, the moderator and MVP tags are a bit quirky and they come and go by themselves sometimes, so it's hard to tell who is a moderator/mvp and who isn't.

I was almost sure that you marked that post as answer as it wasTHEanswer. Don't worry about it. I only mentioned that as I read that moderators generally give us 48 hrs.

sql