Create search option with multiple join table.[Answered] RSS

2 replies

Last post Dec 19, 2016 01:57 AM by JBLD_12@hotmail.com

‹ Previous Thread|Next Thread ›

Shortcuts

Related Links

JBLD_12@hotm...

Member

6 Points

23 Posts

Create search option with multiple join table.

Dec 17, 2016 09:08 AM|JBLD_12@hotmail.com|LINK

 using (SqlConnection con = new SqlConnection(constr))
        {
            using (SqlCommand cmd = new SqlCommand("SELECT DISTINCT Process_Input.id, Device_Info.device, Device_Info.routing, Device_Info.business_unit, Process_Input.machine_type, Process_Input.lot_size, Process_Input.Hrs_day_Machine_Labour, Process_Input.uph," +
                " Process_Input.machine_available, Process_Input.cycle_time, Process_Input.capacity, Process_Input.through_put, Process_Input.yield, Process_Input.machine_utilize, Process_Input.lot_perday, Process_Input.area_operation " +
                "FROM Device_Info INNER JOIN Process_Input ON Device_Info.device = Process_Input.device" +
                "where Device_Info.device like '%" + TextBox13.Text + "%' or Device_Info.routing like '%" + TextBox13.Text + "%' or Device_Info.business_unit like '%" + TextBox13.Text + "%' " +
                " or Process_Input.machine_type like '%" + TextBox13.Text + "%' or Process_Input.lot_size like '%" + TextBox13.Text + "%' or Process_Input.Hrs_day_Machine_Labour like '%" + TextBox13.Text + "%' " +
                " or Process_Input.uph like '%" + TextBox13.Text + "%' or Process_Input.machine_available like '%" + TextBox13.Text + "%' or Process_Input.cycle_time like '%" + TextBox13.Text + "%' " +
                " or Process_Input.capacity like '" + TextBox13.Text + "%' or Process_Input.through_put like '" + TextBox13.Text + "%' or Process_Input.yield like '%" + TextBox13.Text + "%' " +
                " or Process_Input.machine_utilize like '%" + TextBox13.Text + "%' or Process_Input.lot_perday like '" + TextBox13.Text + "%' or Process_Input.area_operation  like '%" + TextBox13.Text + "%'"))
        
            {
                using (SqlDataAdapter sda = new SqlDataAdapter())
                {
                    cmd.Connection = con;
                    sda.SelectCommand = cmd;

                    using (DataTable dt = new DataTable())
                    {
                        sda.Fill(dt);
                        GridView1.DataSource = dt;
                        GridView1.EmptyDataText = "No Record(s) Found";
                        GridView1.DataBind();
                    }
                }
            }

Incorrect syntax near 'Device_Info'.

I didn't found where the syntax error in my query.

How to fix this issue guys? I need help.

Nilishere

Member

370 Points

137 Posts

Re: Create search option with multiple join table.

Dec 17, 2016 11:29 AM|Nilishere|LINK

Store the query in a variable and print it using response.write() . Now add a breakpoint to the variable to see what's the actual result query to be executed after replacing the values. Try like below

var strquery = "SELECT DISTINCT Process_Input.id, Device_Info.device, Device_Info.routing, Device_Info.business_unit, Process_Input.machine_type, Process_Input.lot_size, Process_Input.Hrs_day_Machine_Labour, Process_Input.uph," +
                " Process_Input.machine_available, Process_Input.cycle_time, Process_Input.capacity, Process_Input.through_put, Process_Input.yield, Process_Input.machine_utilize, Process_Input.lot_perday, Process_Input.area_operation " +
                "FROM Device_Info INNER JOIN Process_Input ON Device_Info.device = Process_Input.device" +
                "where Device_Info.device like '%" + TextBox13.Text + "%' or Device_Info.routing like '%" + TextBox13.Text + "%' or Device_Info.business_unit like '%" + TextBox13.Text + "%' " +
                " or Process_Input.machine_type like '%" + TextBox13.Text + "%' or Process_Input.lot_size like '%" + TextBox13.Text + "%' or Process_Input.Hrs_day_Machine_Labour like '%" + TextBox13.Text + "%' " +
                " or Process_Input.uph like '%" + TextBox13.Text + "%' or Process_Input.machine_available like '%" + TextBox13.Text + "%' or Process_Input.cycle_time like '%" + TextBox13.Text + "%' " +
                " or Process_Input.capacity like '" + TextBox13.Text + "%' or Process_Input.through_put like '" + TextBox13.Text + "%' or Process_Input.yield like '%" + TextBox13.Text + "%' " +
                " or Process_Input.machine_utilize like '%" + TextBox13.Text + "%' or Process_Input.lot_perday like '" + TextBox13.Text + "%' or Process_Input.area_operation  like '%" + TextBox13.Text + "%'" ;

Response.write("The actual query is : "+strquery); // add a break point here to see the value of strquery

Please mark as answer if you find my post useful

JBLD_12@hotm...

Member

6 Points

23 Posts

Re: Create search option with multiple join table.

Dec 19, 2016 01:57 AM|JBLD_12@hotmail.com|LINK

 using (SqlCommand cmd = new SqlCommand("SELECT DISTINCT P.id, D.device, D.routing, D.business_unit, P.machine_type, P.lot_size, P.Hrs_day_Machine_Labour, P.uph," +
               " P.machine_available, P.cycle_time, P.capacity, P.through_put, P.yield, P.machine_utilize, P.lot_perday, P.area_operation " +
               "FROM Device_Info AS D INNER JOIN Process_Input AS P ON D.device = P.device WHERE D.device like '%" + TextBox13.Text + "%' or D.routing like '%" + TextBox13.Text + "%' or D.business_unit like '%" + TextBox13.Text + "%' " +
               " or P.machine_type like '%" + TextBox13.Text + "%' or P.lot_size like '%" + TextBox13.Text + "%' or P.Hrs_day_Machine_Labour like '%" + TextBox13.Text + "%' " +
               " or P.uph like '%" + TextBox13.Text + "%' or P.machine_available like '%" + TextBox13.Text + "%' or P.cycle_time like '%" + TextBox13.Text + "%' " +
               " or P.capacity like '" + TextBox13.Text + "%' or P.through_put like '" + TextBox13.Text + "%' or P.yield like '%" + TextBox13.Text + "%' " +
               " or P.machine_utilize like '%" + TextBox13.Text + "%' or P.lot_perday like '" + TextBox13.Text + "%' or P.area_operation  like '%" + TextBox13.Text + "%'"))

Slightly change to the query' the "WHERE" should continue beside "FROM". Previous query I just ENTER like this "FROM.........." + " WHERE........"

After settle above Issue then I get this error. "Both DataSource and DataSourceID are defined on 'GridView1'. Remove one definition."

Solution :

"GridView1.DataSourceID = null;" Just add this LINE code.
GridView1.DataSource = dt;

Finally the searching option done settle.

‹ Previous Thread|Next Thread ›