how to get sorted data wihin the region date from and dateto

Question

i have to text boxes textbox1 in which user enter from date and in textbox2 user enetr to date and after button clickevent the gridview will display the record between fromdate and to date..how to write procedure in sql2005?how to write code and where i have to write?datatype is datetimei am not inserting DateFrom and DateTo in tablejest select using by datetime picker.i have only one column in table that is 'drpdate'.

Iftikar Hussain · Answer

try like this public class Connection { private readonly string _strConnect; public Connection() { _strConnect = ConfigurationManager.ConnectionStrings[MagnaDBConnection].ConnectionString; } public DataTable FnSelectData(string nameSqlParameter[] sqlParameters) { DataTable dt = new DataTable(); using (SqlConnection connection = new SqlConnection(_strConnect)) { if (connection.State != ConnectionState.Open) { connection.Open(); } using (SqlCommand cmd = new SqlCommand(name connection)) { cmd.CommandType = CommandType.StoredProcedure; foreach (var sqlParameter in sqlParameters) { cmd.Parameters.Add(sqlParameter); } SqlDataAdapter da = new SqlDataAdapter(cmd); da.Fill(dt); } connection.Close(); } return dt; } } in your code private void Load() { Connection connection=new Connection(); SqlParameter[] parmsDetails = new SqlParameter[2]; parmsDetails[0] = new SqlParameter(); parmsDetails[0].SqlDbType = SqlDbType.VarChar; parmsDetails[0].Value = 01/01/2013; parmsDetails[0].ParameterName = @StartDate; parmsDetails[1] = new SqlParameter(); parmsDetails[1].SqlDbType = SqlDbType.VarChar; parmsDetails[1].Value = 08/01/2013; parmsDetails[1].ParameterName = @EndDate; var dt = connection.FnSelectData(usp_ConsolidatedReportForProject parmsDetails); Response.Write(dt.Rows.Count); } Regards Iftikar

Sanjeeb Lenka · Answer

hi you can use like this: 1st add the reference of using System.Configuration; public DataTable GET_PRE_SALES_REPORT(string From string To) { string cn= ConfigurationManager.ConnectionStrings[MagnaDBConnection].ConnectionString; adap = new SqlDataAdapter( sp_PaymentRegisterGrid  cn); adap.SelectCommand.CommandType = CommandType.StoredProcedure; adap.SelectCommand.Parameters.AddWithValue(@from From); adap.SelectCommand.Parameters.AddWithValue(@to To); dt = new DataTable(); adap.Fill(dt); return dt; }

MANEESH AN · Answer

i had give strConnectbut i got an errorthe name strConnect does not exist in the current context..i dont get..pls help me?is above mentioned code applied in DLL layer right?

Sanjeeb Lenka · Answer

strConnect is the cn

MANEESH AN · Answer

class Connection { public SqlConnection conSql; SqlCommand cmd; public string strConnect; public void OpenConnection() { try { strConnect = ConfigurationManager.ConnectionStrings[MagnaDBConnection].ConnectionString; conSql = new SqlConnection(strConnect); string s = conSql.State.ToString(); if (s != Open) { conSql.Open(); } } in this which variable should use for cn?

Sanjeeb Lenka · Answer

hi cn is your connection string. if you store your connection string in different variable pass it their.

MANEESH AN · Answer

adap = new SqlDataAdapter(sp_PaymentRegisterGrid cn); i didnt get cn.pls explain

Ravi Shekhar · Answer

Use below code in application... public DataTable GET_PRE_SALES_REPORT(string From string To) { adap = new SqlDataAdapter( sp_PaymentRegisterGrid  cn); adap.SelectCommand.CommandType = CommandType.StoredProcedure; adap.SelectCommand.Parameters.AddWithValue(@from From); adap.SelectCommand.Parameters.AddWithValue(@to To); dt = new DataTable(); adap.Fill(dt); return dt; } and Use this SP ALTER PROCEDURE [dbo].[sp_PaymentRegisterGrid] ( @DTFROM datetime @DTTO datetime ) AS BEGIN SELECT ACC.dRPDate DATE ACC.cVType TYPE TS.vSeries SERIES ACC.iRPNo VOUCHERNO ACM.vAcName HEAD ACC.vNarration NARRATION ACC.deDRAmount DEBIT ACC.deCRAmount CREDIT FROM tbl_Acc_Voucher AS ACC INNER JOIN tblSerialNumbers AS TS ON ACC.iSeriesId = TS.iSeriesId INNER JOIN tbl_Acc_AccountMaster AS ACM ON ACM.iAcID = ACC.iAcID where convert(DATETIME dRPDate 101)>=convert(DATETIME@ DTFROM 101) AND convert(DATETIME dRPDate 101) @ DTTO 101) END

Iftikar Hussain · Answer

Please share your code. Regards Iftikar

MANEESH AN · Answer

I GOT ERROR :Object reference not set to an instance of an object.

Ravi Shekhar · Answer

try SELECT * from table where convert(DATETIME CreatedDate 101)>=convert(DATETIME@ From 101) AND convert(DATETIME CreatedDate 101) @To 101)

MANEESH AN · Answer

dRPDate datetime deDRAmount decimal(18 2) deCRAmount decimal(18 2) cVType char(1) vNarration varchar(1000) vSeries nvarchar(20) iRPNo int vAcName varchar(50) can you give coding?

Ravi Shekhar · Answer

Hi  if your sql column is in varchar format .please first convert into proper datetime format... try below query... SELECT * from table where convert(DATETIME CreatedDate 101)>=convert(DATETIME@ From 101) AND convert(DATETIME CreatedDate 101) @To 101) Mark it as answer if it helped .

Ravi Shekhar · Answer

Please share you table schema... In which format your column is in sql table.

Iftikar Hussain · Answer

you can use SqlDbType.DateTime Regards Iftikar

MANEESH AN · Answer

I HAVE DOUBT..IS THE ALL PARAMETER INCLUDING DATE ALSO CAN PASS AS VARCHAR...SEE MY CODE...I DONT GET PARAMETER(2)??

Iftikar Hussain · Answer

your SP is correct. I hope you will pass correct parameter (2) from your code to sql. Regards Iftikar

MANEESH AN · Answer

PLEASE GET ME ANSWER?

MANEESH AN · Answer

ALTER PROCEDURE [dbo].[sp_PaymentRegisterGrid] ( @DTFROM datetime @DTTO datetime ) AS BEGIN SELECT ACC.dRPDate DATE ACC.cVType TYPE TS.vSeries SERIES ACC.iRPNo VOUCHERNO ACM.vAcName HEAD ACC.vNarration NARRATION ACC.deDRAmount DEBIT ACC.deCRAmount CREDIT FROM tbl_Acc_Voucher AS ACC INNER JOIN tblSerialNumbers AS TS ON ACC.iSeriesId = TS.iSeriesId INNER JOIN tbl_Acc_AccountMaster AS ACM ON ACM.iAcID = ACC.iAcID where dRPDate >=@DTFROM and dRPDate END DLL public DataTable fnPayRegDateView(string strWhereCondition) { Connection objCon = new Connection(); SqlParameter[] parmsDetails = new SqlParameter[1]; parmsDetails[0] = new SqlParameter(); parmsDetails[0].SqlDbType = SqlDbType.VarChar; parmsDetails[0].Value = strWhereCondition; parmsDetails[0].ParameterName = @WhereCondition; DataTable dt = new DataTable(); dt = objCon.fnSelectData(sp_PaymentRegisterGridView parmsDetails); return dt; } IS THIS CORRECT OR NOT?I THINK STRING STRWHERECONDITION IS WRONG?

Iftikar Hussain · Answer

Hi You need to pass from and to date to your stored procedure. And write the query like this @StartDate and @EndDate are input parametrs of datetime for your SP SELECT col1col2... FROM table1 WHERE CreatedOn>=@StartDate and CreatedOn Regards Iftikar