Select Data from Database Depend on Items in Listbox c#

Question

I have a problem to select data depending on all items in a Listbox. Here, in my Listbox there are two items, named Kamera125 and Kamera127. Kamera125 and Kamera127 exist in a MS Access Database. So, when I run my program, I want my program to select Kamera125 and Kamera127 from listbox that connected to MS Access. I used the following query

string selectsemuakoordgaris = "select * from koordinatgaris where namakamera='" + listBox3.Text + "'";

and it doesn't work.

These is my codes :

            private void ProsesSemuaKamera()
            {
                Stopwatch watch = Stopwatch.StartNew();

                Ping ping = new Ping();
                PingReply pingreply;

                OleDbConnection kon = new OleDbConnection(koneksi);
                OleDbCommand command = kon.CreateCommand();
                kon.Open();
                string selecturl = "select * from datakamera";
                command.CommandText = selecturl;
                OleDbDataReader bacadata = command.ExecuteReader();

                while (bacadata.Read())
                {
                    int counturl = 0;
                    pingreply = ping.Send(bacadata["ipadd"].ToString());

                    if (pingreply.Status == IPStatus.Success)
                    {
                        listBox1.Items.Add(bacadata["ipadd"].ToString());
                        listBox3.Items.Add(bacadata["namakamera"].ToString());
                        textBox1.Text += bacadata["namakamera"].ToString() + Environment.NewLine;

                        CaptureSemuaKamera = new Capture(bacadata["urlkamera"].ToString());
                        Application.Idle += new EventHandler(ProcessFrameSemuaKamera);
                    }
                    else if (pingreply.Status != IPStatus.Success)
                    {
                        listBox2.Items.Add(bacadata["ipadd"].ToString());
                    }
                }
                kon.Close();

                watch.Stop();
                File.AppendAllText(@"D:\Dokumen\Alfon\TA Alfon\Waktu Eksekusi Ping.txt", "Waktu eksekusi ping " + DateTime.Now + " :" + " " + watch.Elapsed.TotalMilliseconds.ToString() + Environment.NewLine);
            }


            private void ProcessFrameSemuaKamera(object sender, EventArgs e)
            {
                Image<Bgr, Byte> sourceImage = CaptureSemuaKamera.QueryFrame();
                SourceBox.Image = sourceImage.Bitmap;
ProsesSemuaKamera();
            }

            private void ProsesKameraSemua()
            {
                Image<Bgr, Byte> sourceImage = CaptureSemuaKamera.QueryFrame();
                SourceBox.Image = sourceImage.Bitmap;

                OleDbConnection kon = new OleDbConnection(koneksi);
                OleDbCommand commandkoord = kon.CreateCommand();
                OleDbCommand commandkoordgaris = kon.CreateCommand();

                kon.Open();

                string selectsemuakoord = "select * from koordinatkotak where namakamera='"+ listBox3.Items + "'";
                string selectsemuakoordgaris = "select * from koordinatgaris where namakamera='" + listBox3.Items + "'";
                commandkoord.CommandText = selectsemuakoord;
                commandkoordgaris.CommandText = selectsemuakoordgaris;
                OleDbDataReader bacakoord = commandkoord.ExecuteReader();
                OleDbDataReader bacakoordgaris = commandkoordgaris.ExecuteReader();

                while (bacakoord.Read() && bacakoordgaris.Read())
                {
                    #region Perspective projection

                    PointF[] srcs = new PointF[4];
                    srcs[0] = new PointF(int.Parse(bacakoord["x1source"].ToString()), int.Parse(bacakoord["y1source"].ToString())); //119, 187
                    srcs[1] = new PointF(int.Parse(bacakoord["x2source"].ToString()), int.Parse(bacakoord["y2source"].ToString())); //242, 181
                    srcs[2] = new PointF(int.Parse(bacakoord["x3source"].ToString()), int.Parse(bacakoord["y3source"].ToString())); //253, 225
                    srcs[3] = new PointF(int.Parse(bacakoord["x4source"].ToString()), int.Parse(bacakoord["y4source"].ToString())); //112, 231

                    PointF[] dsts = new PointF[4];
                    dsts[0] = new PointF(int.Parse(bacakoord["x1proj"].ToString()), int.Parse(bacakoord["y1proj"].ToString()));
                    dsts[1] = new PointF(int.Parse(bacakoord["x2proj"].ToString()), int.Parse(bacakoord["y2proj"].ToString()));
                    dsts[2] = new PointF(int.Parse(bacakoord["x3proj"].ToString()), int.Parse(bacakoord["y3proj"].ToString()));
                    dsts[3] = new PointF(int.Parse(bacakoord["x4proj"].ToString()), int.Parse(bacakoord["y4proj"].ToString()));


                    HomographyMatrix mywarpmat = CameraCalibration.GetPerspectiveTransform(srcs, dsts);
                    Image<Bgr, Byte> newImage = sourceImage.WarpPerspective(mywarpmat, 355, 288, Emgu.CV.CvEnum.INTER.CV_INTER_LINEAR, Emgu.CV.CvEnum.WARP.CV_WARP_FILL_OUTLIERS, new Bgr(0, 0, 0));
                    Image<Gray, Byte> newImageGray = newImage.Convert<Gray, Byte>();

                    Image<Bgr, Byte> imageToShow = newImage.Copy();
                    Image<Bgr, Byte> imageToShowGaris = newImage.Copy();

                    ProjectionBox.Image = newImage.Bitmap; //I want to show Projection result in ProjectionBox. All of coordinates are saved in database. When Kamera125 is choosen, all of coordinates in Kamera125 will be executed. So here, I want to execute all of coordinates of Kamera125 and Kamera127 that is shown in listBox.

                    #endregion
                }
                kon.Close();
            }

listBox3.Text is hardly the value that you should use in this query. You need to use the listBox3.Items collection. However your question is too unclear to give an answer. Why my code doesn't work and not providing the code is really useless. Please add the actual code that you are using in trying to select from the database — Steve
– Steve, Commented Jan 23, 2016 at 11:45
Okay. Thanks for your advice :) Can you help me for my problem ? @Steve — Obaja Alfonsus Dhani
– Obaja Alfonsus Dhani, Commented Jan 23, 2016 at 12:06
Let me understand, you want to execute the ProsesKameraSemua when there is an item selected in your listBox3 and retrieve information for that item right? — Steve
– Steve, Commented Jan 23, 2016 at 12:18

Charles May · Accepted Answer · 2016-01-23 14:30:10Z

1

here's a simple method where you can pass in your listbox that has the items selected. It will return a string of the where clause built from the selected items.

    private string BuildWhereClause(ListBox lb)
    {
        string WHEREclause = string.Empty;
        foreach(var itm in lb.SelectedItems)
        {
            if (WHEREclause  == string.Empty )
            {
                WHEREclause += " WHERE namakamera = '" + itm + "' ";
            }
            else
            {
                WHEREclause += " OR namakamera = '" + itm + "' ";
            }
        }
        return WHEREclause;
    }

From this you can build your statement

string selectsemuakoord = "select * from koordinatkotak " + BuildWhereClause(YourListBox);

answered Jan 23, 2016 at 14:30

Charles May

1,7591 gold badge11 silver badges20 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

Obaja Alfonsus Dhani Over a year ago

So, based on your code, my program will be working if I select one of items in my listBox ?

Charles May Over a year ago

you can select none, one or many. It will build the WHERE clause based on what you choose. If you pass in a listbox with nothing selected, you will get an empty string back. Otherwise you will receive a where clause with your selections using OR between them.

Obaja Alfonsus Dhani Over a year ago

Thanks Charles. I had tried to implement your code but it still doesn't work. My program can't do projection when Kamera125 and Kamera127 exists in my listBox. So, another word, my program didn't read and choose anything items in listBox

Obaja Alfonsus Dhani Over a year ago

Wow ! Thanks Charles. That is something that I never known before. It works. Thanks so much :) I never realized before if there is a bit difference between giving a space after koordinatkotak and not. When I don't give a space after koordinatkotak it will be error and error says invalid argument. Can you explain to me why is it exist ?

Charles May Over a year ago

If you didn't put a space after koordinatkotak your statement would have WHERE directly after it so your query would look like select * from koordinatkotakWHERE... that is why in the method I make sure to prefix a space so that it ensures a space is added before appending. If you copied and pasted my code you should be fine. If however, you typed it in then ensure you have a space before the WHERE and the OR so it appends properly. Don't forget to mark this question answered if it resolved your question.

|

Collectives™ on Stack Overflow

Select Data from Database Depend on Items in Listbox c#

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related