Show Gridview on search of ButtonClick

Question

i want data on buttonclick eg. if i have a CaseID =SR012004221000004, according to caseid search ,related data shows in GridView Here is the source Code Enter CaseId * No record Found '> '> '> '>

Sachin Singh · Answer

updated code step 1. Execute below query to your DB Create procedure spGetDetails @searchText NVARCHAR(1000) as begin select * from dbo.comm_paymentDetai where 1=( CASE WHEN ( (CAST(fslCaseId AS VARCHAR(50)) LIKE '%' + @searchText + '%' ) OR (CONVERT(VARCHAR, pil.CreateDate, 106) LIKE '%' + @searchText + '%' ) OR (CONVERT( VARCHAR (10), paymentType) LIKE '%' + @searchText + '%' ) OR (CAST(payamount AS VARCHAR(10)) LIKE '%' + @searchText + '%' ) OR (CAST(modeofpay AS VARCHAR(10)) LIKE '%' + @searchText + '%' ) ) THEN 1 ELSE 0 end step 2. protected void Button1_Click(object sender, EventArgs e) { string constr = ConfigurationManager.ConnectionStrings[ "MedleaprLabCS" ].ConnectionString; using (SqlCommand cmd= new SqlCommand( constr )) { SqlCommand cmd= new SqlCommand( " spGetDetails " ,con); cmd.CommandType=CommandType.StoredProcedure; cmd.Parameters.AddWithValue( "@ searchText " ,txtCase.Text); cmd.Open(); gvreceiptDetail.DataSource = cmd.ExecuteReader(); gvreceiptDetail.DataBind(); } }

Sachin Singh · Answer

Ok then better keep your code clean step 1. Execute below query to your DB Create procedure spGetDetails @searchText NVARCHAR(1000) as begin select * from dbo.comm_paymentDetai where CASE WHEN ( (CAST(fslCaseId AS VARCHAR(50)) LIKE '%' + @searchText + '%' ) OR (CONVERT(VARCHAR, pil.CreateDate, 106) LIKE '%' + @searchText + '%' ) OR (CONVERT( VARCHAR (10), paymentType) LIKE '%' + @searchText + '%' ) OR (CAST(payamount AS VARCHAR(10)) LIKE '%' + @searchText + '%' ) OR (CAST(modeofpay AS VARCHAR(10)) LIKE '%' + @searchText + '%' ) THEN 1 ELSE 0 end step 2. protected void Button1_Click(object sender, EventArgs e) { string constr = ConfigurationManager.ConnectionStrings[ "MedleaprLabCS" ].ConnectionString; using (SqlCommand cmd= new SqlCommand( constr )) { SqlCommand cmd= new SqlCommand( " spGetDetails " ,con); cmd.CommandType=CommandType.StoredProcedure; cmd.Parameters.AddWithValue( "@ searchText " ,txtCase.Text); cmd.Open(); gvreceiptDetail.DataSource = cmd.ExecuteReader(); gvreceiptDetail.DataBind(); } }

Sachin Singh · Answer

Because you need to use the variable name not Connectionstring name string constr = ConfigurationManager.ConnectionStrings[ "MedleaprLabCS" ].ConnectionString; using (SqlConnection con = new SqlConnection(constr)) and without quote exactly like above.

Sachin Singh · Answer

You have 2 options option 1. Convert gridview to datatable $(function(){ $( "[id*= gvreceiptDetail ]" ).DataTable(); }) option 2. modify your search button as "btnCaseid" runat= "server" Text= "Search" CssClass= "btn btn-primary" onclick= "btnCaseid_Click" /> and below code public DataTable GetAll(string searchtext) { DataTable dt= new DataTable(); string querystring = "" ; querystring = "select * from yourtable" ; if (querystring != "" ) { querystring += " where paymenttype like '%" + txtsearch.Text + "%' or modeofpay like '%" + txtsearch.Text + "%' Or payamount like '%" + txtsearch.Text + "%' Or paydate like '%" + txtsearch.Text + "%' ;"; } using (SqlConnection con= new SqlConnection( "yourconnectionstring" )) { SqlDataAdapter da= new SqlDataAdapter (querystring, con); con.Open(); da.Fill(dt); } return dt; } protected void Button1_Click(object sender, EventArgs e) { DataTable dt= new DataTable(); dt= GetAll(txtsearch.Text); if (dt.Rows.Count > 0) { gvreceiptDetail .DataSource = dt; gvreceiptDetail .DataBind(); } }

Sachin Singh · Answer

@Nirvriti One end was missing updated code step 1. Execute below query to your DB Create procedure spGetDetails @searchText NVARCHAR(1000) as begin select * from dbo.comm_paymentDetai where 1=( CASE WHEN ( (CAST(fslCaseId AS VARCHAR(50)) LIKE '%' + @searchText + '%' ) OR (CONVERT(VARCHAR, pil.CreateDate, 106) LIKE '%' + @searchText + '%' ) OR (CONVERT(VARCHAR(10), paymentType) LIKE '%' + @searchText + '%' ) OR (CAST(payamount AS VARCHAR(10)) LIKE '%' + @searchText + '%' ) OR (CAST(modeofpay AS VARCHAR(10)) LIKE '%' + @searchText + '%' ) ) THEN 1 ELSE 0 end end step 2. protected void Button1_Click(object sender, EventArgs e) { string constr = ConfigurationManager.ConnectionStrings[ "MedleaprLabCS" ].ConnectionString; using (SqlCommand cmd= new SqlCommand(constr)) { SqlCommand cmd= new SqlCommand( "spGetDetails" ,con); cmd.CommandType=CommandType.StoredProcedure; cmd.Parameters.AddWithValue( "@searchText" ,txtCase.Text); cmd.Open(); gvreceiptDetail.DataSource = cmd.ExecuteReader(); gvreceiptDetail.DataBind(); } }

Guest User · Answer

Sachin Singh Msg 156, Level 15, State 1, Procedure spGetDetails, Line 13 [Batch Start Line 0] Incorrect syntax near the keyword 'THEN'.

Guest User · Answer

Sachin Singh in gridview i only want to show PaymentType,PaymentMode,Payment Amount,Date Of Payment

Guest User · Answer

design

Guest User · Answer

databse table

Guest User · Answer

When i search CaseId then i get syntax error " Incorrect syntax near '%SR012003120000001%'" SR012003120000001 is the caseid

Guest User · Answer

Sachin Singh public DataTable GetAll(string searchtext) { DataTable dt = new DataTable(); string querystring = ""; querystring = "select * from comm_paymentDetail"; if (querystring != "") { querystring += " where paymenttype like '%" + txtCase.Text + "%' or modeofpay '%" + txtCase.Text + "%' Or payamount '%" + txtCase.Text + "%' Or paydate '%" + txtCase.Text + "%'; "; } string constr = ConfigurationManager.ConnectionStrings["MedleaprLabCS"].ConnectionString; using (SqlConnection con = new SqlConnection("MedleaprLabCS")) // Exception occur here { SqlDataAdapter da = new SqlDataAdapter(querystring, con); con.Open(); da.Fill(dt); } return dt; } protected void btnCaseid_Click(object sender, EventArgs e) { DataTable dt = new DataTable(); dt = GetAll(txtCase.Text); if (dt.Rows.Count > 0) { gvreceiptDetail.DataSource = dt; gvreceiptDetail.DataBind(); } } Exception: Format of the initialization string does not conform to specification starting at index 0.

Sachin Singh · Answer

sorry, It should be txtCase.Text

Guest User · Answer

Sachin Singh what you use txtsearch.Text?