QUERY CHECKING

Question

Alter PROCEDURE sp_over
(
@vParty_Id varchar(50),
@dDue_Date datetime,
@dtF datetime
)
AS
begin



select vLoan_No As LoanNumaber,dIssue_Date As Date,vParty_Id As CustomerName,dcLoan_Amt As LoanAmount,dDue_Date As DueDate from tbl_LoanMaster

WHERE vParty_Id>=@vParty_Id and dDue_Date<=@dtF and vStatus='N'

end

Iftikar Hussain · Answer

first modify the sp like this Alter PROCEDURE sp_over ( @vParty_Id varchar(50) @iSchemeID int ) AS begin declare @days int select @days=SchemeDaye from youSchemeTable where iSchemeID=@iSchemeID; select vLoan_No As LoanNumaberdIssue_Date As DatevParty_Id As CustomerNamedcLoan_Amt As LoanAmountdDue_Date As DueDate from tbl_LoanMaster WHERE vParty_Id>=@vParty_Id and DateDiff(ddgetdate()dDue_Date) end then in your code private void OverdueRegisterDisplay_Load(object sender EventArgs e) { DataRowView dataRow = cmbScheme .SelectedItem as DataRowView; int iSchemeID =(int) dataRow.Row.ItemArray[0]; DataTable dt = new DataTable(); dt = objLoanEntryBL.fnOverdueRegister(vParty_Id iSchemeID ); dataGridView1.DataSource = AutoNumberedTable(dt); dataGridView1.Columns[0].Width = 75; dataGridView1.Columns[1].Width = 75; dataGridView1.Columns[2].Width = 68; dataGridView1.Columns[3].Width = 230; dataGridView1.Columns[4].Width = 72; dataGridView1.Columns[5].Width = 68; } bl ******** public DataTable fnOverdueRegister(string vParty_Id int iSchemeID ) { DataTable dt = new DataTable(); dt = objLoanEntryDL.fnOverdueRegister(vParty_Id iSchemeID ); return dt; } dl ******** public DataTable fnOverdueRegister(string vParty_Id int iSchemeID ) { Connection con = new Connection(); con.OpenConnection(); SqlCommand cmd = new SqlCommand(); cmd.Connection = con.conSql; cmd.CommandText = sp_over ; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue(@vParty_Id vParty_Id); cmd.Parameters.AddWithValue(@iSchemeID iSchemeID ); SqlDataAdapter da = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); da.Fill(dt);//Error Procedure or Function 'sp_over' expects parameter '@dDue_Date' which was not supplied. con.CloseConnection(); return dt; } Regards Iftikar

JAYESH AJ · Answer

********main Error dl in dtfill section**************** form1 ****** LoanEntryBL objLoanEntryBL = new LoanEntryBL(); private void frmOverdueRegister_Load(object sender EventArgs e) { fnFill(); } private void fnFill() //selected iteam combox { try { DataTable dt = new DataTable(); dt = null; string WhereCondition = ; dt = objSchemeMasterBL.fnSearchSchemeMaster(WhereCondition); if (dt.Rows.Count > 0) { for (int i = 0; i { cmbScheme.DisplayMember = vScheme_Name; cmbScheme.ValueMember = iSchemeID; cmbScheme.DataSource = dt; } } } catch (Exception ex) { MessageBox.Show(ex.Message); } } private void btnExit_Click(object sender EventArgs e) { this.Close(); } private void button1_Click(object sender EventArgs e) { OverdueRegisterDisplay frmOverdueRegisterDisplay = new OverdueRegisterDisplay(); frmOverdueRegisterDisplay.vParty_Id = cmbScheme.Text; frmOverdueRegisterDisplay.dtF = dtF.Value; frmOverdueRegisterDisplay.Show(); } } form 2 ***** public string vParty_Id; public DateTime dtF; LoanEntryBL objLoanEntryBL = new LoanEntryBL(); private void OverdueRegisterDisplay_Load(object sender EventArgs e) { DataTable dt = new DataTable(); dt = objLoanEntryBL.fnOverdueRegister(vParty_Id dtF); dataGridView1.DataSource = AutoNumberedTable(dt); dataGridView1.Columns[0].Width = 75; dataGridView1.Columns[1].Width = 75; dataGridView1.Columns[2].Width = 68; dataGridView1.Columns[3].Width = 230; dataGridView1.Columns[4].Width = 72; dataGridView1.Columns[5].Width = 68; } private DataTable AutoNumberedTable(DataTable SourceTable) { DataTable ResultTable = new DataTable(); DataColumn AutoNumberColumn = new DataColumn(); AutoNumberColumn.ColumnName = S.No.; AutoNumberColumn.DataType = typeof(int); AutoNumberColumn.AutoIncrement = true; AutoNumberColumn.AutoIncrementSeed = 1; AutoNumberColumn.AutoIncrementStep = 1; ResultTable.Columns.Add(AutoNumberColumn); ResultTable.Merge(SourceTable); return ResultTable; } bl ******** public DataTable fnOverdueRegister(string vParty_Id DateTime dtF) { DataTable dt = new DataTable(); dt = objLoanEntryDL.fnOverdueRegister(vParty_Id dtF); return dt; } dl ******** public DataTable fnOverdueRegister(string vParty_Id DateTime dtF) { Connection con = new Connection(); con.OpenConnection(); SqlCommand cmd = new SqlCommand(); cmd.Connection = con.conSql; cmd.CommandText = sp_over ; cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue(@vParty_Id vParty_Id); cmd.Parameters.AddWithValue(@dtF dtF); SqlDataAdapter da = new SqlDataAdapter(cmd); DataTable dt = new DataTable(); da.Fill(dt);//Error Procedure or Function 'sp_over' expects parameter '@dDue_Date' which was not supplied. con.CloseConnection(); return dt; } query ****** Alter PROCEDURE sp_over ( @vParty_Id varchar(50) @dDue_Date datetime @dtF datetime ) AS begin select vLoan_No As LoanNumaberdIssue_Date As DatevParty_Id As CustomerNamedcLoan_Amt As LoanAmountdDue_Date As DueDate from tbl_LoanMaster WHERE vParty_Id>=@vParty_Id and dDue_Date end

Iftikar Hussain · Answer

Hi You need to pass the selected value of Combox to this sp. Share your code how you bind the combo box Regards Iftikar

Sunny Sharma · Answer

Dear Jayesh still not getting your point explain a bit more.

JAYESH AJ · Answer

THE query using schemeAbc select to the combobox and datetime select schemeA(365) B(4) days period closing date display btn click *************** another page display grid * Alter PROCEDURE sp_over ( @vParty_Id varchar(50) @dDue_Date datetime @dtF datetime ) AS begin select vLoan_No As LoanNumaberdIssue_Date As DatevParty_Id As CustomerNamedcLoan_Amt As LoanAmountdDue_Date As DueDate from tbl_LoanMaster WHERE vParty_Id>=@vParty_Id and dDue_Date end

Sunny Sharma · Answer

is that a question or solution ?? Please consider explaining the things properly while posting your question/concern in any forum. Remember we cannot read your mind/HardDisk. And yes if you're asking about the syntax of your Procedure it's pretty nice no issues. Hope you got my point :)