mysql,vb.net,checkbox , Filtering MySql database with multiple checkboxes in VB.NET


Filtering MySql database with multiple checkboxes in VB.NET

Question:

Tag: mysql,vb.net,checkbox

I want to filter my datas which are stored in MySql database with combining multiple checkbox and textbox queries. You can find the screenshot of the form here:

SEARCH FORM:

enter image description here

TABLES:

PROJECTS TABLE enter image description here

DETAILS TABLE enter image description here

PS: Only ID field in Projects table is unique!

If user writes in any of these three textboxes than the rest of the textbox will getting empty. Currently it works fin with textboxes. I want to combine the seach criteria with the checkboxes.

Assume that user writes in TEXTBOX1 and selects FAS, VAPA and ACC from checkboxes than the result should be listed in my listview box.

Here's the codes I've come so far :

    Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click

    If TextBox1.Text = "" And TextBox2.Text = "" And TextBox3.Text = "" Then
        MsgBox("Bir arama kriteri girin!", MsgBoxStyle.Critical, "UYARI!")
        Exit Sub
    End If


        ListView1.Items.Clear()

        If TextBox1.Text <> "" Then
                Dim cmd As New MySqlCommand
                Dim ad As New MySqlDataAdapter
                Dim projeadi As String = "select projects.ID, projects.PROJEADI, projects.TEKLIFFIRMA, details.ID, details.MARKA from projects INNER JOIN details ON projects.ID = details.ID WHERE PROJEADI='" & TextBox1.Text & "';"
                Dim tbl As New DataTable
                Dim i As Integer
                With cmd
                    .CommandText = projeadi
                    .Connection = con
                End With
                With ad
                    .SelectCommand = cmd
                    .Fill(tbl)
                End With

                For i = 0 To tbl.Rows.Count - 1
                    With ListView1
                        .Items.Add(tbl.Rows(i)("ID"))
                        With .Items(.Items.Count - 1).SubItems
                            .Add(tbl.Rows(i)("PROJEADI"))
                            .Add(tbl.Rows(i)("TEKLIFFIRMA"))
                            .Add(tbl.Rows(i)("MARKA"))
                        End With
                    End With
                Next

        ElseIf TextBox2.Text <> "" Then
            Dim cmd As New MySqlCommand
            Dim ad As New MySqlDataAdapter
            Dim tekliffirma As String = "select projects.ID, projects.PROJEADI, projects.TEKLIFFIRMA, details.ID, details.MARKA from projects INNER JOIN details ON projects.ID = details.ID WHERE TEKLIFFIRMA='" & TextBox2.Text & "';"
            Dim tbl As New DataTable
            Dim i As Integer
            With cmd
                .CommandText = tekliffirma
                .Connection = con
            End With
            With ad
                .SelectCommand = cmd
                .Fill(tbl)
            End With

            For i = 0 To tbl.Rows.Count - 1
                With ListView1
                    .Items.Add(tbl.Rows(i)("ID"))
                    With .Items(.Items.Count - 1).SubItems
                        .Add(tbl.Rows(i)("PROJEADI"))
                        .Add(tbl.Rows(i)("TEKLIFFIRMA"))
                        .Add(tbl.Rows(i)("MARKA"))
                    End With
                End With
            Next
        ElseIf TextBox3.Text <> "" Then
            Dim cmd As New MySqlCommand
            Dim ad As New MySqlDataAdapter
            Dim marka As String = "select projects.ID, projects.PROJEADI, projects.TEKLIFFIRMA, details.ID, details.MARKA from projects INNER JOIN details ON projects.ID = details.ID WHERE MARKA='" & TextBox3.Text & "';"
            Dim tbl As New DataTable
            Dim i As Integer
            With cmd
                .CommandText = marka
                .Connection = con
            End With
            With ad
                .SelectCommand = cmd
                .Fill(tbl)
            End With

            For i = 0 To tbl.Rows.Count - 1
                With ListView1
                    .Items.Add(tbl.Rows(i)("ID"))
                    With .Items(.Items.Count - 1).SubItems
                        .Add(tbl.Rows(i)("PROJEADI"))
                        .Add(tbl.Rows(i)("TEKLIFFIRMA"))
                        .Add(tbl.Rows(i)("MARKA"))
                    End With
                End With
            Next
        End If

Please advise.

Thank you.

Oguz


Answer:

Well, since i don't know whats the name of the table field which corresponds to the checkboxes filter criteria, i'm supposing that it is Projects.Sistem

I would group all checkboxes into a GroupBox in order to use a For loop to check them all. If any of them is checked, an AND clause will be added to the end of your SQL statement.

Once done, another For will check every checked CheckBox to add the Name of the checkbox to your final SQL statement in order to obtain a SQL statement like this

SELECT projects.ID, projects.PROJEADI, projects.TEKLIFFIRMA, details.ID,
details.MARKA from projects INNER JOIN details ON projects.ID = details.ID
WHERE ..... AND Projects.Sistem IN ('FAS', 'VAPA', 'CCTV',.....)

The below code is only an approximation. You will need to check it and adapt it to your existing code

Dim SQL As String = ""

If projeadi <> "" Then
   SQL = projeadi
End If

If tekliffirma <> "" Then
   SQL = tekliffirma
End If

If marka <> "" Then
   SQL = marka
End If

For Each chkBox As CheckBox in GroupBox1.Controls
   If chkBox.Checked = True Then
      SQL = SQL & " AND Project.Sistem IN ( "
      Exit For
   End if
Next
For Each chkBox As CheckBox In GroupBox1.Controls
    If chkBox.Checked = True Then
         Sql = Sql & "'" & chkBox.Name & "',"
    End If
Next
Sql = Sql.Substring(0, Sql.Length - 1)
Sql = Sql & ")"

Related:


IBM Cognos _days_between function not working


mysql,database,date,cognos
I'm having an issue with Cognos 10. I'm trying to calculate the number of days between to dates so I use the _days_between( date1, date2 ) function. _days_between ([Derniere Date Changement diaphragme] , [Premiere Date Changement diaphragme]) I'm quite sure the two Dates are Date objects ( I set them...

Php Mysql Query not working properly


php,mysql
I have a table name tblnetworkstatus and I have 11 columns Id issue_name affected_server affected_service issue_type priority duration status start_date end_date description I am getting id in affected_server and affected_service which I am storing in my DB, now I have three situations Either both affected_server and affected_service has been selected...

How to create multiple jquery form fields and insert in mysql database, without using mysql_real_escape_string


javascript,php,jquery,mysql
I need to create dynamic text fields with a button (which simply adds a new row of dynamic text fields). Then I need to INSERT the data in a in the database. I borrowed an example of what I need done from someone else's question, the problem is, it uses...

Connecting to database using Windows Athentication


sql-server,vb.net,authentication,connection-string
I would like to use window authentication in my program to connect to my sql server. users already have certain permissions on the SQL server and I would like to leverage that in my program. The way I currently connect to the server is using this connection string. Dim ConnectionString...

Convert Double from String


asp.net,vb.net,visual-studio-2012,converter
When converting string to floating, the converter creates wrong results. ?Global.System.Convert.ToDouble("635705821821928755").ToString("0") "635705821821929000" ?Global.System.Convert.ToSingle("635705821821928755").ToString("0") "635705800000000000" I am working with VB.Net Visual Studio 2012, Framework 4 on ASP.Net Webpage. Is there any solution for converting huge numbers from string into floating?...

Query how often an event occurred at a given time


mysql,sql
[Aim] We would like to find out how often an event "A" ocurred before time "X". More concretely, given the dataset below we want to find out the count of the prior purchases. [Context] DMBS: MySQL 5.6 We have following dataset: user | date 1 | 2015-06-01 17:00:00 2 |...

Having two arrays in variable php


php,mysql,arrays,variables,multidimensional-array
i've this code, i'm trying to explode my date e.g "04 June 2015" to have the day and the month in other 2 variables, but i have all in a method, with an array, is it possible, my code doesn't work, it writes "Array[0]" why? while ($i < $number) {...

Purging Database - Count purged/not-purged tables


mysql,sql,sql-server,database,stored-procedures
So I am working with a database where I will be purging various tables that contain rows that are older than 30days. I have fairly limited knowledge of SQL and wanted to know if there was a certain way to select the row that will be purged and the rows...

Using VLOOKUP formula or other function to compare two columns


mysql,excel,vba,date
I have one table like this: SHORT TERM BORROWING 1/6/2009 94304 12/31/2010 177823 6/30/2011 84188 12/31/2011 232144 6/30/2012 94467 9/30/2012 91445 12/31/2012 128523 3/31/2013 83731 6/30/2013 78330 9/30/2013 70936 12/31/2013 104020 3/31/2014 62345 6/30/2014 62167 9/30/2014 63494 12/31/2014 104239 3/31/2015 69056 I have another column which lists each date from...

MySQL: Select several rows based on several keys on a given column


mysql,sql,database
I have this mysql table structure: ------------------------------------ | item_id | meta_key | meta_value | ------------------------------------ 159 category Bungalow 159 location Lagos 159 price 45000 160 category Bungalow 160 location Abuja 160 price 53500 ... 350 category Bungalow 350 location Lagos 350 price 32000 What I'd like to do is select...

PHP / MySQLi: How to prevent SQL injection on INSERT (code partially working)


php,mysql,mysqli,sql-injection,sql-insert
I am new to PHP and hope someone can help me with this. I would like to store two values (an email and a password) in a MySQL db using PHP. The input is passed to the PHP page via Ajax in jQuery (through an onclick event on a website)....

mysql detect field value change


mysql
I have found a very similar question with exact same title here: Mysql, Check field value change?, but it is not exactly what I need. DB: MySql, Table: tbl_geodata I have query: SELECT id,timestamp,ver_fw FROM tbl_geodata WHERE imei LIKE '353227026533507' Result is: --Full data +------+---------------------+--------+ | id | timestamp |...

MySQL substring match using regular expression; substring contain 'man' not 'woman'


mysql,regex
I have an issue while I fetch data from database using regular expression. While I search for 'man' in tags it returns tags contains 'woman' too; because its substring. SELECT '#hellowomanclothing' REGEXP '^(.)*[^wo]man(.)*$'; # returns 0 correct, it contains 'woman' SELECT '#helloowmanclothing' REGEXP '^(.)*[^wo]man(.)*$'; # returns 0 incorrect, it can...

MySQLdb Python - Still getting error when using CREATE TABLE IF NOT EXISTS


python,mysql
I'm using this code to create a database with tables in Python: def CreateDatabase(): global DB_CNX global DB_NAME cursor = DB_CNX.cursor() cursor.execute("""CREATE DATABASE IF NOT EXISTS {} DEFAULT CHARACTER SET 'utf8'""".format(DB_NAME)) cursor.execute("""CREATE TABLE IF NOT EXISTS NAMES(NAME VARCHAR(50) PRIMARY KEY NOT NULL)""") DB_CNX.close() But even if I use the syntax...

compare today's date with unix timestamp value in database


php,mysql
In database I am storing date value unix timestamp value for e.g.'1434952110' using time() now I am trying to compare todays value with this value as below $jobpostdate = date("Y-m-d");//today's date passing in database to compare query $sql = "SELECT jsp_title, jsp_subtitle, jsp_desc, jsp_location, jsp_date "; $sql .= "FROM js_projects...

How to pass all value of ListBox Control to a function?


vb.net,listbox
I am writing a simple application to read the value a textbox and add to a listbox control . But i have to pass the listbox control to function . Any suggestion ? Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load test("E:\Satyajit.txt") End Sub Public Function...

Can Rails deal with DB uniqueness without index?


mysql,ruby-on-rails,rdbms
I see add_index ~ unique: true statement in schema.rband think uniqueness is constraint for table, not for index.Using index is one of way realizing uniqueness, Programmer should not designate to the RDBMS "how" and index quicken searching but take costs inserting. In fact, is there another way to keep uniqueness...

MySQL Query returning strange values


php,mysql
The query is supposed to do the following: Obtain the question and associated answers based on the identification number of the question. (In this case the identification number is called account_id. Order them so that each question (and it's appropriate answers) are lined up in order. The query: SELECT *...

How do I display my mysql table column headers in my php/html output?


php,html,mysql,table,data
2 Questions... Scenario: I would like to query my database table via a form and then display the results that occur(if there are results) and my current situation is that it does work but it clears the form completely and leaves my to an empty page with just the results...

If I export my database with phpmyadmin will it lock my tables or take my database down?


mysql,database,phpmyadmin
I want to create a backup of my database using the phpmyadmin export function. the database can't have any down time so I need to know if running a database export will take the database down? I've looked on-line but all I get is instructions on how to export. No...

How do I find records in one table that do exist in another but based on a date?


mysql,sql
I want to find all the dealers who haven't had an order in 2015 yet. I know this query doesn't work but I thought it might be helpful to understand what I want to do. In this example I want to get just "Bob" as a result. He is the...

Symfony2 creating and persisting entity relationships


php,mysql,symfony2,doctrine2
I have two entities Skin and Email. I want Email to be a part of the Skin entity, however I can't use the console to update schema automatically right now, and that is probably why I can't get the relationship to work. So I want to store all the Emails...

Retrieve Values As Column


mysql,sql
I've the following table in my PHPMYADMIN The desired output is headline                              impressions              clicks Buy A new Iphone                 ...

Visual Basic Datagrid View change row colour


vb.net,datagridview,datagrid
Every other change to the datagrid view works fine but for some reason the row color just wont change. Ive debugged and my application goes through the loop to change the row color. Also I have a button that gives the datagrid view a new list and colors the rows...

Fatal error in if stament [duplicate]


php,mysql,mysqli
This question already has an answer here: Compile Error: Cannot use isset() on the result of an expression 2 answers Error - Cannot use isset() on the result of an expression (you can use "null !== expression" instead) I'm getting the error above the start of the if statement...

ZipEntry() and converting persian filenames


vb.net,persian,sharpziplib
In my vb.net project I'm trying to add a file with a Persian name to a zip file and I do this with the code bellow: Dim myentry As New ZipEntry(dr.Item("MyFile").ToString()) zipOut.PutNextEntry(myentry) however when I open the zip file I see the file name is changed to a gibberish Is...

Comparing arrays with numbers in vb.net


arrays,vb.net
I need a way to compare two arrays in vb.net and save result in third array: Dim KonRes(3) As Integer Dim UserRes(3) As Integer Dim YelRed(3) As Integer KonRes(0) = 1 KonRes(1) = 2 KonRes(2) = 3 KonRes(3) = 4 UserRes(0) = 4 UserRes(1) = 3 UserRes(2) = 2 UserRes(3)...

I Want to fetch SQL Records in MySQL of current Year


mysql
I am using the query SELECT count(enq.`enquiryId`), Month(enq.`date`), Year(enq.`date`) FROM enquiry enq GROUP BY MONTH(enq.`date`) But I get all year records. I just want to get Current or any input year(At Runtime) records. Please help....

Inner Join 3 tables pl/sql


mysql,sql
Basically i want to inner join 3 tables and the case is understood by the query itself as follows. Tables: A has 2 columns column 1 and column 2 B has 2 columns column 3 and column 4 C has 3 columns column 5,column 6 and column 7 Query: select...

How to call MySQL view in Struts2 or Hibernate


java,mysql,hibernate,java-ee,struts2
I have a view in MySQL database CREATE VIEW CustInfo AS SELECT a.custName, a.custMobile, b.profession, b.companyName, b.annualIncome FROM customer a INNER JOIN cust_proffessional_info b ON a.cust_id=b.cust_id Is there any way that i can call this view using Struts2 or in Hibernate. I have tried to search it but could not...

Trying to rewrite mysql_* to pdo


php,mysql,pdo
First of all I should say that I started to learn PDO and trying to rewrite some old mine codes. Here is one that I found on tutorial for a jquery/ajax/pdo/mysql comment system which is working. This is the mysql_* part which I'm trying to rewrite -> file submit.php which...

Notice: Array to string conversion in “path of php file” on line 64


php,mysql,arrays,oracle
PHP CODE -: <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> <html xmlns="http://www.w3.org/1999/xhtml"> <head> <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> <title>Untitled Document</title> </head> <body> <?php $rows =0; $fp = fopen("leave1.csv","r"); if($fp){ while(!feof($fp)){ $content = fgets($fp); if($content) $rows++; } } fclose($fp); //echo $rows; $_SESSION['rows'] = $rows; ?>...

SQL Group By multiple categories


php,mysql,sql,mysqli
This is the query I'm using to count how many events are booked for each category of bookings. SELECT Categories.name, count(case when locations.name ='loc 1' then 1 end) as Location1, count(case when locations.name ='loc 2' then 1 end) as Location2, count(case when locations.name ='loc 3' then 1 end) as Location3,...

How to search images by name inside a folder?


php,mysql,image
I have a MySQL table with a column "rounds" and each "rounds" has his own photos. Exemple round1 has photos from start=380 end=385. This means it has 6 photos and the name of the photos contains 380,381,382,383,384 or 385 inside. I use this PHP code to display the photos from...

Removing Alert When Using DeleteFile API


vb.net,vba,api,delete
I'm writing a VBA application which involves looping a large number of directories recursively. I am using the FindFirstFile API to to achieve this, as it offers a substantial performance boost over the FileSystemObject. In order to remove the FSO from my code entirely, I need a routine to delete...

Convert date to string format


vb.net,converter
I have successfully imported data from xls into dataGridView. The date column in dataGridView is showing the DateTime format. I want this date column to be displayed in "dd MMM yyyy" format. I have tried as shown below: connExcel.Open() Dim dtExcelSchema As DataTable dtExcelSchema = connExcel.GetOleDbSchemaTable(OleDbSchemaGuid.Tables, Nothing) Dim SheetName As...

MySQL trigger help, capturing a variable from php insert into


php,mysql,triggers
I am writing a MySQL trigger to encrypt a column of sensitive data using RSA plugin. There is an insert statement from the website which is PHP based $sql = "insert into redcap_data (project_id, event_id, record, field_name, value) values ($project_id, " . getEventId() . ", '" . prep($_GET['id']) . "',...

Get XML node value when previous node value conditions are true (without looping)


xml,vb.net,linq-to-xml
Sample XML - <?xml version="1.0"?> <Root> <PhoneType dataType="string"> <Value>CELL</Value> </PhoneType> <PhonePrimaryYn dataType="string"> <Value>Y</Value> </PhonePrimaryYn> <PhoneNumber dataType="string"> <Value>555-555-5554</Value> </PhoneNumber> <PhonePrimaryYn dataType="string"> <Value>Y</Value> </PhonePrimaryYn> <PhoneType dataType="string"> <Value>HOME</Value> </PhoneType>...

concatenate field names in a mysql update with inner join


php,mysql
Concatenating two field names from different tables using INNER JOIN during UPDATE statement. I am trying to concatenate two field names in a mysql update statement. This is what I have so far, needless to say it isn't working, any suggestions would be much appreciated. UPDATE products INNER JOIN sub_categories...

Ignore Group if LIMIT is not reached in MySQL


mysql
I am working on a rather tricky SQL for my level of knowledge. I have searched and searched for an answer but haven't came across anything. Hopefully someone can shed some light on this. How can you stop SQL from outputting group of rows if the limit set is not...

Temporarily Number Rows to Group by in MYSQL


mysql
I am trying to create a temporary column that gives each set of five results a number so that I can group by.. I am having a lot of trouble trying to work out how to do this. I don't want to take any criteria into account, I want the...

Select most recent entry where timestamp is from the past hour


mysql
I've tried multiple ways, all of them resulting in incorrect rows returned, so far I've tried SELECT * FROM table WHERE id=1 AND timestamp >= NOW() - INTERVAL 1 HOUR ORDER BY timestamp DESC LIMIT 1 .... AND timestamp > DATE_SUB(NOW(), INTERVAL 1 HOUR) ORDER BY timestamp DESC LIMIT 1...

C# MySQL Parameters.AddWithValue


c#,mysql
Hello I found this code snippet for Adding Values to MySQL Commands in C# MySqlCommand command = connection.CreateCommand(); command.CommandText = "INSERT INTO tb_mitarbeiter (Vorname) VALUES (?name)"; command.Parameters.AddWithValue("?name", mitarbeiter); connection.Open(); command.ExecuteNonQuery(); Now I want to add data to more than one coloumn, but if i try it like this: command.Parameters.AddWithValue("?id", Projektid,...

C# - Can't connect to remote MySQL server


c#,mysql
My problem is that I can't connect to my website remote MySQL server. I have read all answers in stackoverflow.com, but I can't find right answer. Here's my C# code: using System; using System.Collections.Generic; using System.Data; using System.Data.SqlClient; namespace ConsoleApplication3 { class Program { static void Main(string[] args) { SqlConnection...

how will i order this query by DoctorName


php,mysql
I use this query but I don't know how will I order this by DoctorName? $q=mysqli_query($link,"Select * from doctor where status='". $stat ."'"); ...

timestamp SQL to Excel


php,mysql,sql,excel
If this is a duplicate, please let me know, I haven't found anything. I have written a php file that can read content from a database table and write it into a excel .xls file. Everything works fine except by that timestamps. In my generated .xls file every timestamp is...

Using Sum in If in Mysql


mysql,sql,select,sum
I want to select the sum of values of my_field. The unit of my_field is in seconds. I don't want to exceed 24 hours, so when it exceeds 24 hours, I want it to select 24*60*60 = 86400. SELECT IF(SUM(my_field) > 86400, 86400, SUM(my_field)) ... This solution doesn't seem to...

How to program a recurring billing/invoice system using PHP and MySQL


php,mysql
I have already programmed a basic invoicing system using PHP/MySQL which has a table structure as follows; Invoice table; invoice_id, invoice_date, customer_id, etc Invoice line table; invoice_line_id, invoice_id, quantity, price, description, etc I need the system to automatically generate future invoices at set intervals (for example every 1 week or...

MySQL - How can I know my query is tuned?


mysql,performance,explain
I'm creating a DB from scratch and I'm trying to create queries with performance in mind. For test purpose I filled my tables with test data. My query needs to join 3 tables: 2 with millions of rows and the third with hundred thousands rows. How can I know my...

PHP: While loop not working after adjusting SELECT for SQL injection prevention


php,mysql,select,sql-injection,associative-array
I am trying to set up PHP queries for MySQL in a way to prevent SQL injection (standard website). I had a couple of INSERT queries where changing this worked well but on the following SELECT I keep getting an error since the update and it looks like the while...