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

Binding a SqlDataSource, to a GridView at Runtime

Hello

I'm experiencing some problems, binding a SqlDataSource to a GridView.

The following code creates the SqlDataSource:
string strConn = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
string strProvider = ConfigurationManager.ConnectionStrings["ConnectionString"].ProviderName;

SqlDataSource ds = new SqlDataSource(strProvider, strConn);
ds.SelectCommand = "SELECT * FROM rammekategori";

Then i bind the SqlDataSource to a GridView:

GridView1.DataSource = ds;
GridView1.DataBind();

ErrorMessage:

Format of the initialization string does not conform to specification starting at index 0.

Exception Details: System.ArgumentException: Format of the initialization string does not conform to specification starting at index 0.

Line 24: GridView1.DataBind();

Am i totally off target here? Can it be something about, that you have to set the datasource of the gridview, before the Page_Load event?

Thanks - MartinHN

You are probably not going to be happy with the results if you do manage to fix your immediate error. May I suggest you actually put the SqlDataSource in the .aspx page? You can then bind it in code, but don't change the select statement in code, as that too will cause some issues.|||

oh. The code was copied from VS2005, i forgot one important detail.

I add the SqlDataSource to the page, by using Page.Controls.Add(ds);

Still, i get the error, even if i set the DataSource property of the gridview like this:

GridView1.DataSource = (SqlDataSource)Page.FindControl("ds");

--

MartinHN

|||

What if you try this:

SqlDataSource m_SqlDataSource = Page.FindControl("ds") as SqlDataSource;

if (m_SqlDataSource != null)
{
GridView1.DataSourceID = m_SqlDataSource.ID;
GridView1.DataBind();
}

HTH,
Ryan

|||

>>>>What if you try this:

SqlDataSource m_SqlDataSource = Page.FindControl("ds") as SqlDataSource;

if (m_SqlDataSource != null)
{
GridView1.DataSourceID = m_SqlDataSource.ID;
GridView1.DataBind();
}

Still no success... It is still the samme error message:Format of the initialization string does not conform to specification starting at index 0.

I was wondering, if i need to set a couple of more properties on my SqlDataSource.

One thing i just tried, was createing a new SqlDataSource directly on the aspx page, from the Designer in VS2005. The i set the datasource, to the FindControl method, with the name as the parameter. That worked. But i'm interessted in getting a SqlDataSource object, from lower tier, than the page it self. So that i have a complete SqlDataSource with select, update, delete and insert commands, to use along with the gridview...

|||

Ah. I totally missed the actual error. Please post your connection string, leaving out any login information, but keep the formatting as-is.

|||

ok...

The connectionstring i am using are as follows:

<connectionStrings>
<add name="ConnectionString" connectionString="DRIVER={MySQL ODBC 3.51 Driver};SERVER=myserverip;DATABASE=mydb;UID=myuid;PASSWORD=mypwd;" providerName="System.Data.Odbc" />
</connectionStrings>

Should there be any troubles using a mySQL DB?

|||

I think I see your problem. The overloads for the SqlDataSource on MSDN are as follows:

SqlDataSource()
SqlDataSource(string connectionString, string selectCommand)
SqlDataSource(string providerName, string connectionString, string selectCommand)

So, you should be doing something like this:

string strConn = ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
string strProvider = ConfigurationManager.ConnectionStrings["ConnectionString"].ProviderName;
string selectCommand = "SELECT * FROM rammekategori";

SqlDataSource ds = new SqlDataSource(strProvider, strConn, selectCommand);

HTH,
Ryan

|||

Ryan - I really cannot find a way to show my appreciation... That was very nice to have sorted out, thanks for your help.

What about insert, delete and update commands, can i set them after I've instantiated the SqlDataSource object?

|||

martinhn wrote:

What about insert, delete and update commands, can i set them after I've instantiated the SqlDataSource object?

Yes.

SqlDataSource.DeleteCommand
SqlDataSource.InsertCommand
SqlDataSource.UpdateCommand

HTH,
Ryan

sql

Best Way to delete a parent record

whats the best way to delete a parent record?

for now what i am doing is to have a special delete page (means not DetailsView/GridView) just to delete parent records so user can see what child records will be deleted too. is the a better way?

my code below:

The T-SQL Stored Procedure

ALTER PROCEDURE dbo.DeleteInventory(@.InventoryIDint)ASDELETE FROM BookingWHERE InventoryID = @.InventoryIDDELETE FROM InventoryWHERE InventoryID = @.InventoryID

The code in the Code Behind to execute when a button(delete) click

Try Dim tempAs Integer = SqlDataSource4.DeleteIf temp > 0Then lblDeleteDtatus.ForeColor = Drawing.Color.Blue lblDeleteDtatus.Text = temp &" Records/Rows Deleted."Else lblDeleteDtatus.ForeColor = Drawing.Color.Orange lblDeleteDtatus.Text ="No records Deleted!"End If Catch exAs Exception lblDeleteDtatus.ForeColor = Drawing.Color.Red lblDeleteDtatus.Text ="An Error Occured.<br />" & ex.Message.ToStringEnd Try

any better methods anyone wants to share?

Assuming that it makes business sense to delete an inventory record and orders for that item, your stored procedure would be better as

ALTER PROCEDURE dbo.DeleteInventory
(
@.InventoryID int,
@.Count INT OUTPUT -- Return the number of Inventory records to delete as an output parameter
)
AS
SET NOCOUNT ON
SELECT @.COUNT = COUNT(*) FROM Booking WHERE InventoryID = @.InventoryID
BEGIN TRANSACTION
DELETE FROM Booking WHERE InventoryID = @.InventoryID
DELETE FROM Inventory WHERE InventoryID = @.InventoryID
COMMIT

|||

i used "

Dim

"tempAs Integer = SqlDataSource4.Delete"

to get the rows affected, isn't it about the same? by the way with ur method, its simpler but
i seem to have problems. how do i "link" it to a variable? i know how to link it to a Control, etc but how to a variable?