Show Gridview on search of ButtonClick

Guest User

4y1.2kASP.NET

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

13 Replies

Know the answer? Post it — somebody with the same question will find it here.

Sachin Singh4y ago
Accepted 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();
}

}
+4
Sachin Singh4y ago
@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();
}
}
+3
Guest User4y ago
Sachin Singh

Msg 156, Level 15, State 1, Procedure spGetDetails, Line 13 [Batch Start Line 0]
Incorrect syntax near the keyword 'THEN'.
+3
Sachin Singh4y ago
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();
}


        }
+4
Guest User4y ago
Sachin Singh

in gridview i only want to show PaymentType,PaymentMode,Payment Amount,Date Of Payment
+3
Guest User4y ago
design
+3
Guest User4y ago
databse table
+3
Guest User4y ago
When i search CaseId then i get syntax error "Incorrect syntax near '%SR012003120000001%'"
SR012003120000001 is the caseid
+3
Sachin Singh4y ago
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.
+3
Guest User4y ago
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.
+3
Sachin Singh4y ago
sorry,
It should be txtCase.Text
+3
Guest User4y ago
Sachin Singh

what you use txtsearch.Text?
+3
Sachin Singh4y ago
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();
            }
        }
+3