Using multiple tables from a database

[gggram2000]

Hi,
Im having problems writing a query statement to select different columns from different tables to display on an invoice i made on an excel file. The reason i have a problem is because im using c-sharp and coding is a bit complicated at first...if anyone has ran into this problem or may know how to solve it, i would love to hear ur input.
P.S.Im using visual studio 2005 in case the coding may differ

George

[aaronsandoval]

If you are looking to "JOIN" data from different tables you can do this within your stored procedure.

Here is an Example:

[CODE=SQL]
CREATE PROCEDURE [User_GetDetails _ByID]
(
@UserID int
)
AS
SELECT
tblA.UserID,
tblA.FavoriteCo lorID,
tblB.ColorName,
tblA.FirstName,
tblA.LastName
FROM
Users tblA
LEFT JOIN Colors tblB ON tblA.FavoriteCo lorID = tblB.ColorID
WHERE
tblA.UserID = @UserID
GO
[/CODE]

Hope this helps, please let me know if you would like further explination of this solution.

- Aaron Sandoval
"remember: There's a thin line between good | Great..."

[Plater]

Hi,
Im having problems writing a query statement to select different columns from different tables to display on an invoice i made on an excel file. The reason i have a problem is because im using c-sharp and coding is a bit complicated at first...if anyone has ran into this problem or may know how to solve it, i would love to hear ur input.
P.S.Im using visual studio 2005 in case the coding may differ

George

You use SQL to query databases, not a programming language like C# or VBNET.
What are you using for your code?

[gggram2000]

You use SQL to query databases, not a programming language like C# or VBNET.
What are you using for your code?

Well I'ved tried inserting sql queries into the commands that are supposed to carry out the procedure like this for example:

SELECT GuestFirstName FROM Guest WHERE ToGuestInvoice > 0

But it gives errors on FROM and WHERE because it doesn't recognize it. If this behind code would work I wouldn't have any problems...mayb e it has an assembly or something im missing but I doubt it. Maybe u can enlighten me...Would really appreciate it.

George

[aaronsandoval]

SELECT GuestFirstName FROM Guest WHERE ToGuestInvoice > 0

George

Post your code for this C# method and we may be able to look at the syntax to see if it's incorrect somewhere.

[gggram2000]

Go to Try...right there I'm really kinda lost on how to access rows from two tables in a database...so as later to display it on an outside file.

private void button2_Click(o bject sender, EventArgs e)
{
//Get configuration settings for the database conection
ResortApplicati on.conf configdb = new conf();
//this is creating the connection string
string xmldb = configdb.fnGetK eyConfig("xmldb ");
//this is creating the database connection
SqlConnection dbConnection = new SqlConnection(x mldb);
//this is creating the database command
SqlCommand myCommand = new SqlCommand();
//this is declaring a transaction variable with sqltransaction which is a default type in .net
SqlTransaction myTrans;
//open connection
dbConnection.Op en();
// Begin the transaction.
myTrans = dbConnection.Be ginTransaction( );
// Assign the connection property.
myCommand.Conne ction = dbConnection;
// Assign transaction object for a pending local transaction
myCommand.Trans action = myTrans;

try
{

var rows = from e in RESORT.Guest where e.ToGuestInvoic e = true select e;
foreach( var e in rows )
{
string name = e.LastName;
int id = e.GuestID;
Console.WriteLi ne( id + ": " + name );
}

myCommand.Execu teNonQuery(); //execute a non query
myTrans.Commit( ); //commit your transaction
}

[Plater]

A transaction is ment to be of update/insert/delete (I believe) which can only be to one table.
If you are doing a query, you should just use the SqlCommand object.

Take a look at this article:

http://www.thescripts.com/forum/thread635615.html

[gggram2000]

A transaction is ment to be of update/insert/delete (I believe) which can only be to one table.
If you are doing a query, you should just use the SqlCommand object.

Take a look at this article:
http://www.thescripts.com/forum/thread635615.html

Hey thanks Plater...makes sense...so when i use only the sqlcommand object can i use like a join on the behind code for the specific button procedure?

[Plater]

Hey thanks Plater...makes sense...so when i use only the sqlcommand object can i use like a join on the behind code for the specific button procedure?

Aye, you can change the CommandText to any valid SQL statement.

For example:

string sqlq="";
sqlq+=" SELECT ";
sqlq+=" tblA.UserID, ";
sqlq+=" tblA.FavoriteColorID, ";
sqlq+=" tblB.ColorName, ";
sqlq+=" tblA.FirstName, ";
sqlq+=" tblA.LastName ";
sqlq+=" FROM ";
sqlq+=" Users tblA ";
sqlq+=" LEFT JOIN Colors tblB ON tblA.FavoriteColorID = tblB.ColorID ";
sqlq+=" WHERE ";
sqlq+=" tblA.UserID = @UserID ";
sqlCom.CommandText = sqlq;
sqlCom.Parameters.Add("@UserID ", SqlDbType.Int).Value = 12345

[gggram2000]

Kool, thanks. Appreciate your help!

[gggram2000]

If you are looking to "JOIN" data from different tables you can do this within your stored procedure.

Here is an Example:

[CODE=SQL]
CREATE PROCEDURE [User_GetDetails _ByID]
(
@UserID int
)
AS
SELECT
tblA.UserID,
tblA.FavoriteCo lorID,
tblB.ColorName,
tblA.FirstName,
tblA.LastName
FROM
Users tblA
LEFT JOIN Colors tblB ON tblA.FavoriteCo lorID = tblB.ColorID
WHERE
tblA.UserID = @UserID
GO
[/CODE]

Hope this helps, please let me know if you would like further explination of this solution.

- Aaron Sandoval
"remember: There's a thin line between good | Great..."

Yeah a little further explanation wouldnt' hurt...

[aaronsandoval]

To begin, there are many ways to accomplish this task. This "JOIN" feature in SQL is very useful and can be used in many instances to "SELECT" data from multiple tables. Here is a small example of how we would use a "JOIN" statement and how we would use it with our C# ASP.NET application.

For this example, we have created 1 SQL Stored Procedure, 1 ASP.NET 2.0 Page with C# Code Behind Page. We are using the Northwind Database that is found in any copy of SQL. We are going to bind the results (from dataset) to a GridView control on our page.


SQL STORED PROCEDURE
[CODE=SQL]
CREATE PROCEDURE [CustomerOrders_ GetAll_ByCountr y]
(
@Country nvarchar(15)
)
AS
SELECT
tblA.OrderID,
tblB.ContactNam e,
tblB.CompanyNam e,
tblA.ShippedDat e
FROM
Orders tblA
LEFT JOIN Customers tblB ON tblA.CustomerID = tblB.CustomerID
WHERE
tblA.ShipCountr y = @Country
GO
[/CODE]

NOTE: I personally use "SQL Helper" class which can be found on Microsofts website when you download DAAB (Data Access Application Block) for 2.0. This is a very easy way for you to work with your database and use Stored Procedures in your code. Another great way would be to use strongly-typed datasets to access this data.

IMPORTANT: If you want to use this SQL Helper class don't forget to add it into the App_Code folder in your project.


ASP.NET 2.0 HTML CODE
[CODE=html]
<%@ Page Language="C#" AutoEventWireup ="true" CodeFile="Defau lt.aspx.cs" Inherits="Defau lt" %>

<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dt d">
<html xmlns="http://www.w3.org/1999/xhtml" >
<head id="Head1" runat="server">
<title>GridVi ew Example</title>
</head>
<body>
<form id="form1" runat="server">
<asp:ScriptMana ger ID="ScriptManag er1" runat="server" />
<div>
<asp:GridView ID="GridView1" runat="server" CellPadding="4" ForeColor="#333 333" GridLines="None " AllowPaging="Tr ue" PageSize="10" OnPageIndexChan ging="GridView1 _PageIndexChang ing">
<FooterStyle BackColor="#5D7 B9D" Font-Bold="True" ForeColor="Whit e" />
<RowStyle BackColor="#F7F 6F3" ForeColor="#333 333" />
<EditRowStyle BackColor="#999 999" />
<SelectedRowSty le BackColor="#E2D ED6" Font-Bold="True" ForeColor="#333 333" />
<PagerStyle BackColor="#284 775" ForeColor="Whit e" HorizontalAlign ="Center" />
<HeaderStyle BackColor="#5D7 B9D" Font-Bold="True" ForeColor="Whit e" />
<AlternatingRow Style BackColor="Whit e" ForeColor="#284 775" />
</asp:GridView>
&nbsp;</div>
</form>
</body>
</html>
[/CODE]


ASP.NET 2.0 C# CODE BEHIND PAGE
[CODE=cpp]
using System;
using System.Data;
using System.Configur ation;
using System.Collecti ons;
using System.Web;
using System.Web.Secu rity;
using System.Web.UI;
using System.Web.UI.W ebControls;
using System.Web.UI.W ebControls.WebP arts;
using System.Web.UI.H tmlControls;
// DON'T FORGET TO ADD THIS DataAccessLayer NAMESPACE SO YOU CAN USE THE SQLHelper CLASS
using DataAccessLayer ;

public partial class Default : System.Web.UI.P age
{
// OF COURSE THIS SHOULD BE IN YOUR WEB.CONFIG FILE & ENCRYPTED
string conString = "Server=YourSer verName;Databas e=Northwind;Use r ID=MyUserName;P assword=MyUserP assword;";

protected void Page_Load(objec t sender, EventArgs e)
{
Bind_GridView1( );
}

public void Bind_GridView1( )
{
// we are going to set the country equal to USA
string country = "USA";
DataSet ds = new DataSet();
ds = SqlHelper.Execu teDataset(conSt ring, "CustomerOrders _GetAll_ByCount ry", country);
GridView1.DataS ource = ds;
GridView1.DataB ind();
// THIS WOULD BE ONE WAY YOU COULD IMPLIMENT THE FOREACH STATEMENT WITH THE RETURNED DATA
foreach(DataRow aRow in ds.Tables[0].Rows)
{
Response.Write( aRow["ContactNam e"].ToString() + "<br />");
}
}

public void GridView1_PageI ndexChanging(ob ject sender, GridViewPageEve ntArgs e)
{
GridView1.PageI ndex = e.NewPageIndex;
Bind_GridView1( );
}

}
[/CODE]


CONCLUSION:
You can create a new ASP.NET 2.0 C# WebSite and add this code into a page called "Default.as px". Don't forget to add in the stored procedure into your Northwind Database, so you can successfully run example.

I hope this code helps. You used the "foreach" loop statement in your code and in this Bind method it writes out the data on the page as well as binds to the gridview control.


- Aaron Sandoval
"remember: Greatness can easily be achieved, as long as you never stop wanting to learn from others..."

[gggram2000]

Thanks Aaron, your code really helped. Appreciate the assistance.