Retrieve data from multiple tables.

Question

1.00/5 (1 vote)

See more:

i am trying to return result from multiple tables.
My tables are these

Masterleave:
Code varchar(20) Unchecked
Name varchar(150) Checked
Description varchar(250) Checked
------------------------------------------
TrLeaveRequest:
RequestID int Unchecked
RequestBy varchar(20) Checked
LeaveCode varchar(20) Checked
RequestDate datetime Checked
RequiredDate datetime Checked
JoinDate datetime Checked
TotalDays int Checked

there is some more...
---------------------------------------
TrLeaveApproval:
RequestID int Unchecked
RefTrID int Checked
RequestTo varchar(20) Checked
TrDate datetime Checked
Status int Checked

--------------------------------------
My asp code is

C#

private void FillGrid()
       {
           

           grvList.PageSize = Convert.ToInt16(drpRecords.SelectedValue.ToString());
           int PageNumber = Convert.ToInt32(txtPageNo.Text);
           LeaveRequestList objLst = new LeaveRequestList();
           string strFilter = "";
           string strFilter1 = "";
           strFilter1 += " AND R.RequestBy ='" + Session["empid"].ToString() + "'";

           if (txtLetterType.Text != "")
           {
               strFilter += " AND R.LeaveCode LIKE '%" + ExpertGeneric.CleanString(txtLetterType.Text) + "%'";
           }

           if (txtStatus.Text != "")
           {
               strFilter += " AND R.Status LIKE '%" + ExpertGeneric.CleanString(txtStatus.Text) + "%'";
           }

           objDB.strFilter = strFilter1 + strFilter;

           int cnt = objDB.Count();
           lblRecordNo.Text = "Total Rows : " + cnt.ToString("#0") + "&nbsp;&nbsp;&nbsp;";
           int TotalPage = (cnt / grvList.PageSize) + ((cnt % grvList.PageSize) > 0 ? 1 : 0);
           lblShowing.Text = " of " + TotalPage.ToString();
           if (PageNumber > TotalPage)
           {
               txtPageNo.Text = TotalPage.ToString();
               PageNumber = TotalPage;
           }
          objLst = objDB.GetListForGrid(grvList.PageSize, PageNumber, "RequestDate");
       //     objLst = objDB.GetListForGrid(Session["empid"].ToString());
           grvList.DataSource = objLst;
           grvList.DataBind();

           foreach (GridViewRow gr in grvList.Rows)
           {
               if (gr.Cells[3].Text != "Requested")
               {
                   ((ImageButton)gr.FindControl("imgDelete")).Visible = false;
                   ((ImageButton)gr.FindControl("imgEdit")).Visible = false;
               }
           }
       }

-------------------------------------------------------

C#

 public LeaveRequestList GetListForGrid(int RecCount, int PageNo, string OrderBy)

        {
           strSql = "WITH TempTable AS (SELECT R.RequestID,R.RequestBy,R.LeaveCode,R.RequestDate,R.RequiredDate,R.JoinDate,R.TotalDays,R.Remark,R.Status,R.CreatedBy,R.CreatedDate,R.ModifiedBy,R.ModifiedDate,T.Name  AS LeaveTypeName , E.Name AS EmployeeName, (CASE R.Status WHEN 1 THEN 'Requested' WHEN 2 THEN 'Approved' WHEN 3 THEN 'Rejected' ELSE '--' END) AS StatusString, ROW_NUMBER() OVER (ORDER BY " + OrderBy + ") AS 'RowNumber' FROM TrLeaveRequest R INNER JOIN MasterLeave T ON R.LeaveCode = T.Code INNER JOIN EmployeeMaster E ON R.RequestBy = E.EmpID WHERE 1 = 1 " + strFilter + ") SELECT * FROM TempTable WHERE RowNumber BETWEEN " + ((PageNo == 1) ? 1 : ((PageNo - 1) * RecCount) + 1).ToString() + " AND " + ((PageNo == 1) ? RecCount : (PageNo * RecCount)).ToString().ToString();
            LeaveRequestList objList = new LeaveRequestList();
            DataTable dt = new DataTable();
            dt = objDB.GetDataTableFromSQL(strSql);
            if (dt != null)
            {
                foreach (DataRow Dr in dt.Rows)
                {
                    LeaveRequest obj = new LeaveRequest();
                    obj.RequestID = Convert.ToInt32(Dr["RequestID"].ToString());

                    if (Dr["RequestBy"] != DBNull.Value)
                        obj.RequestBy = Dr["RequestBy"].ToString();
                    else
                        obj.RequestBy = "";

                    if (Dr["LeaveCode"] != DBNull.Value)
                        obj.LeaveCode = Dr["LeaveCode"].ToString();
                    else
                        obj.LeaveCode = "";


                    if (Dr["LeaveTypeName"] != DBNull.Value)
                        obj.LeaveTypeName = Dr["LeaveTypeName"].ToString();
                    else
                        obj.LeaveTypeName = "";

                    if (Dr["EmployeeName"] != DBNull.Value)
                        obj.EmployeeName = Dr["EmployeeName"].ToString();
                    else
                        obj.EmployeeName = "";

                    if (Dr["StatusString"] != DBNull.Value)
                        obj.StatusString = Dr["StatusString"].ToString();
                    else
                        obj.StatusString = "";


                    if (Dr["RequestDate"] != DBNull.Value)
                        obj.RequestDate = Convert.ToDateTime(Dr["RequestDate"].ToString());
                    else
                        obj.RequestDate = new DateTime();

                    if (Dr["RequiredDate"] != DBNull.Value)
                        obj.RequiredDate = Convert.ToDateTime(Dr["RequiredDate"].ToString());
                    else
                        obj.RequiredDate = new DateTime();

                    if (Dr["JoinDate"] != DBNull.Value)
                        obj.JoinDate = Convert.ToDateTime(Dr["JoinDate"].ToString());
                    else
                        obj.JoinDate = new DateTime();

                    if (Dr["TotalDays"] != DBNull.Value)
                        obj.TotalDays = Convert.ToInt32(Dr["TotalDays"].ToString());
                    else
                        obj.Status = 0;

                    if (Dr["Remark"] != DBNull.Value)
                        obj.Remark = Dr["Remark"].ToString();
                    else
                        obj.Remark = "";

                    if (Dr["Status"] != DBNull.Value)
                        obj.Status = Convert.ToInt32(Dr["Status"].ToString());
                    else
                        obj.Status = 0;

                    objList.Add(obj);
                }
            }
            return objList;
        }
--------------------------------------
   

it is not give me any error, same time no return. please help me to find solution.

Posted 13-Oct-14 22:16pm

tastini

Add a Solution

Comments

aarif moh shaikh 14-Oct-14 5:03am

Sorry i am not getting your actual point in your code..
but if you want to retrieve data from more than one tables than first of all you have to use primary/Secondary key concepts .. which is not apply on your tables.

Thanks AARIF

tastini 14-Oct-14 5:29am

Yes I did.. all the first one is primary key.

Richard Deeming 14-Oct-14 8:22am

You code is susceptible to SQL Injection[^].

NEVER use string concatenation to build a SQL query; ALWAYS use a parameterized query.

tastini 14-Oct-14 9:25am

can you solve it for me, in your way. really thankful to you.

3 solutions

Add a Solution

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Shweta N Mishra · Answer 1 · 2014-10-14T00:16:00

Solution 1

You should check in SQL editor whether you are getting the results and for better process i suggest to create a Stored procedure instead of using plain query in the front end and pass all the parameters in the SP required for filter.

Posted 14-Oct-14 0:16am

Shweta N Mishra

Comments

tastini 14-Oct-14 6:18am

Can you give me a stored procedure ?

Maciej Los · Answer 2 · 2014-10-14T01:08:00

Solution 2

Your ASP.NET is potentially open for SQL Injection[^].

How to avoid it? Please, read this:
How To: Protect From SQL Injection in ASP.NET[^]
Stop SQL Injection Attacks Before They Stop You[^]
SQL Injection and how to avoid it[^]
Dynamic SQL & SQL injection[^]

As shweta nikhil mishra mentioned the general solution in this case is to use stored procedure[^].
Walkthrough: Displaying Data Using a Stored Procedure in the GridView Web Server Control[^]
How to: Execute a Stored Procedure that Returns Rows[^]

SQL

CREATE PROCEDURE
    @param1 type,
    @param2 type
    @paramN type
AS
BEGIN

    --sample SELECT statement 
    SELECT <Field_list>
    FROM TableName
    WHERE Field1 = @param1

END

Posted 14-Oct-14 1:08am

Maciej Los

Comments

tastini 14-Oct-14 7:17am

CREATE PROCEDURE EmployeeSales
@Employee int AS
SELECT SUM([Order Subtotals].Subtotal) AS Total
FROM Employees INNER JOIN
Orders INNER JOIN
[Order Subtotals] ON Orders.OrderID = [Order Subtotals].OrderID
ON Employees.EmployeeID = Orders.EmployeeID
WHERE (Employees.EmployeeID = @Employee)

in this example they retrieving two tables , how I can make it three. and how to pass the value.

cmd.CommandText = "StoredProcedureName";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = sqlConnection1;

in this example I guess they are running simply select statement no search. I mean no where command in that stored procedure. how to make it.

normally I am running the code in front end. I am not use storeprocedure if you can give me one code , I can use it. you have my tables and you know what I want. please give me the stored procedure.

Shweta N Mishra · Answer 3 · 2014-10-14T01:34:00

Solution 3

SQL

You can use something like below, but just check the highlihted code below as this condition does seems the cause of issue for getting no data as you are trying to match some filter string i.e. 1=1+some filter which will never match. i am not sure if this is neccessary condition , so if it is still required you can write that line as 1 = 1 +@param3

Create Proc Rpt_LeaveInformation(@param1 Nvarchar(100),@param2 Nvarchar(100),@param3 Nvarchar(50))
As
BEGIN
SELECT
    R.RequestID,R.RequestBy,R.LeaveCode,R.RequestDate,R.RequiredDate,R.JoinDate,R.TotalDays,R.Remark,R.Status,R.CreatedBy,R.CreatedDate,
    R.ModifiedBy,R.ModifiedDate,T.Name  AS LeaveTypeName , E.Name AS EmployeeName,
    (CASE R.Status WHEN 1 THEN 'Requested' WHEN 2 THEN 'Approved' WHEN 3 THEN 'Rejected' ELSE '--' END)
    AS StatusString, ROW_NUMBER() OVER (ORDER BY RequestDate) AS 'RowNumber'
FROM
    TrLeaveRequest R
    INNER JOIN MasterLeave T ON R.LeaveCode = T.Code
    INNER JOIN EmployeeMaster E ON R.RequestBy = E.EmpID
WHERE
    <pre>1 = 1 " + strFilter + ") </pre>SELECT *
FROM TempTable
WHERE RowNumber BETWEEN @param1 AND @param2

END

Posted 14-Oct-14 1:34am

Shweta N Mishra

Comments

tastini 14-Oct-14 7:55am

Msg 102, Level 15, State 1, Procedure Rpt_LeaveInformation, Line 14
Incorrect syntax near '<'.

this is the error it shows. while executing the storeprocedure. and regarding that filter not important after its running properly I will check it. in these sp rownumber your are not mention. if you can ,pls give me note.

Shweta N Mishra 14-Oct-14 8:01am

Use below, Row Number is already there check the last selected column

Create Proc Rpt_LeaveInformation(@param1 Nvarchar(100),@param2 Nvarchar(100))
As
BEGIN

SELECT
R.RequestID,R.RequestBy,R.LeaveCode,R.RequestDate,R.RequiredDate,R.JoinDate,R.TotalDays,R.Remark,R.Status,R.CreatedBy,R.CreatedDate,
R.ModifiedBy,R.ModifiedDate,T.Name AS LeaveTypeName , E.Name AS EmployeeName,
(CASE R.Status WHEN 1 THEN 'Requested' WHEN 2 THEN 'Approved' WHEN 3 THEN 'Rejected' ELSE '--' END) AS StatusString,
ROW_NUMBER() OVER (ORDER BY RequestDate) AS 'RowNumber'
into #TempTable
FROM
TrLeaveRequest R
INNER JOIN MasterLeave T ON R.LeaveCode = T.Code
INNER JOIN EmployeeMaster E ON R.RequestBy = E.EmpID
WHERE
1 = 1

SELECT *
FROM #TempTable
WHERE RowNumber BETWEEN @param1 AND @param2

END

tastini 14-Oct-14 9:29am

its running in sql , how I can pass my parameters on this sp now.

private void FillGrid()
{

grvList.PageSize = Convert.ToInt16(drpRecords.SelectedValue.ToString());
int PageNumber = Convert.ToInt32(txtPageNo.Text);
LeaveRequestList objLst = new LeaveRequestList();
string strFilter = "";
string strFilter1 = "";
strFilter1 += " AND R.RequestBy ='" + Session["empid"].ToString() + "'";

if (txtLetterType.Text != "")
{
strFilter += " AND R.LeaveCode LIKE '%" + ExpertGeneric.CleanString(txtLetterType.Text) + "%'";
}

if (txtStatus.Text != "")
{
strFilter += " AND R.Status LIKE '%" + ExpertGeneric.CleanString(txtStatus.Text) + "%'";
}

objDB.strFilter = strFilter1 + strFilter;

int cnt = objDB.Count();
lblRecordNo.Text = "Total Rows : " + cnt.ToString("#0") + " ";
int TotalPage = (cnt / grvList.PageSize) + ((cnt % grvList.PageSize) > 0 ? 1 : 0);
lblShowing.Text = " of " + TotalPage.ToString();
if (PageNumber > TotalPage)
{
txtPageNo.Text = TotalPage.ToString();
PageNumber = TotalPage;
}

DateTime dt2 = new DateTime();

Hashtable objHt = new Hashtable();
objHt.Add("@TrMode", "IE");
objHt.Add("@RequestBy", Session["empid"].ToString());
objHt.Add("@param1", grvList.PageSize);
objHt.Add("@param2", PageNumber);

DB.ExecuteSPorSQLNonQuery("Rpt_LeaveInformation", ref objHt, 1);

// objLst = objDB.GetListForGrid(grvList.PageSize, PageNumber, "RequestDate");
// objLst = objDB.GetListForGrid(Session["empid"].ToString());
grvList.DataSource = objLst;
grvList.DataBind();

foreach (GridViewRow gr in grvList.Rows)
{
if (gr.Cells[3].Text != "Requested")
{
((ImageButton)gr.FindControl("imgDelete")).Visible = false;
((ImageButton)gr.FindControl("imgEdit")).Visible = false;
}
}
}

how to do it correctly? really I am confusing.

Shweta N Mishra 15-Oct-14 7:55am

Where is your DB object created?, And object objHt is having 4 values , You can not pass 2 parameters to the SP when you have only 2.

You can also Simply call below

DB.ExecuteSPorSQLNonQuery("Rpt_LeaveInformation '"+@param1+"','"+@param2+"'");

Once you object is in place.

Retrieve data from multiple tables.

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Retrieve data from multiple tables.

3 solutions

Solution 1

Solution 2

Solution 3

Add your solution here

Preview 0

Existing Members

...or Join us