Click here to Skip to main content
15,069,133 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I have a textbox to enter data and a button to search for that data returned to a datagridview. The data is not getting passed to the datatable.


private void button1_Click(object sender, EventArgs e)
        {
            SqlConnection con = new SqlConnection("Data Source = AIRS1C49; Initial Catalog = BoundBook; Integrated Security = True");
            SqlCommand cmd = new SqlCommand();
            SqlDataAdapter sda = new SqlDataAdapter(@"SELECT Firearm.Serial_Number, Firearm.Manufacturer, Firearm.Country_of_Mfg, Firearm.Model, Firearm.Type, Firearm.[Action], Firearm.Gage_Caliber, Firearm.Status, Firearm.Acquired, Firearm.Disposed, Acquisition.Acq_ID,
                         Acquisition.Acq_Date, Acquisition.Acq_Reason, Acquisition.Serial_Number AS Expr1, Acquisition.Acq_Note, Acquisition.DataEntryDate_A, Acquisition.FFL_ID, FFL.FFL_ID AS Expr2, FFL.FFL_Name, FFL.FFL_Address1,
                         FFL.FFL_Address2, FFL.FFL_City, FFL.FFL_State, FFL.FFL_ZipCode, FFL.FFL_Email, FFL.FFL_Phone, FFL.FFL_License_Number, FFL.FFL_Expiration_Date, Disposition.Disp_ID, Disposition.Disp_Date, Disposition.Disp_Reason,
                         Disposition.Serial_Number AS Expr3, Disposition.FFL_ID AS Expr4, Disposition.Disp_Note, Disposition.DataEntryDate_D
                         FROM            Firearm INNER JOIN
                         Acquisition ON Firearm.Serial_Number = Acquisition.Serial_Number INNER JOIN
                         FFL ON Acquisition.FFL_ID = FFL.FFL_ID INNER JOIN
                         Disposition ON Firearm.Serial_Number = Disposition.Serial_Number AND FFL.FFL_ID = Disposition.FFL_ID
                         WHERE FireArm.Serial_Number = '" + textBox1.Text + "%'", con);
           
            DataSet dt = new DataSet();
            sda.Fill(dt);
            dataGridView1.DataSource = dt;


What I have tried:

I have tried using a smaller query, but I get the same results, nothing passed to the datatable. Someone told me that I needed to open and close the connection so I added a con.Open(); and con.Close(); but that made no difference so I removed them. I'm using the same connection string on another form and it works there so I can't imagine it being the problem.

Any ideas?
Posted
Updated 11-May-18 23:05pm
v2

C#
SqlDataAdapter sda = new SqlDataAdapter(@"SELECT Firearm.Serial_Number, Firearm.Manufacturer, Firearm.Country_of_Mfg, Firearm.Model, Firearm.Type, Firearm.[Action], Firearm.Gage_Caliber, Firearm.Status, Firearm.Acquired, Firearm.Disposed, Acquisition.Acq_ID,
             ...
             WHERE FireArm.Serial_Number = '" + textBox1.Text + "%'", con);

Not a solution to your question, but another problem you have.
Never build an SQL query by concatenating strings. Sooner or later, you will do it with user inputs, and this opens door to a vulnerability named "SQL injection", it is dangerous for your database and error prone.
A single quote in a name and your program crash. If a user input a name like "Brian O'Conner" can crash your app, it is an SQL injection vulnerability, and the crash is the least of the problems, a malicious user input and it is promoted to SQL commands with all credentials.
SQL injection - Wikipedia[^]
SQL Injection[^]
SQL Injection Attacks by Example[^]
PHP: SQL Injection - Manual[^]
SQL Injection Prevention Cheat Sheet - OWASP[^]
   
Comments
Dr. Johnny Fever 11-May-18 22:34pm
   
So I should use Parameters, to prevent this?
Patrice T 11-May-18 22:40pm
   
yes
Ppolymorphe is bang on about the SQL Injection risk: Never concatenate strings to build a SQL command. It leaves you wide open to accidental or deliberate SQL Injection attack which can destroy your entire database. Use Parametrized queries instead.

When you concatenate strings, you cause problems because SQL receives commands like:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'Baker's Wood'
The quote the user added terminates the string as far as SQL is concerned and you get problems. But it could be worse. If I come along and type this instead: "x';DROP TABLE MyTable;--" Then SQL receives a very different command:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'x';DROP TABLE MyTable;--'
Which SQL sees as three separate commands:
SQL
SELECT * FROM MyTable WHERE StreetAddress = 'x';
A perfectly valid SELECT
SQL
DROP TABLE MyTable;
A perfectly valid "delete the table" command
SQL
--'
And everything else is a comment.
So it does: selects any matching rows, deletes the table from the DB, and ignores anything else.

So ALWAYS use parameterized queries! Or be prepared to restore your DB from backup frequently. You do take backups regularly, don't you?

So the first thing you need to do is go through your entire app and fix that - leave one concatenation, and you had better backup often!

When you have done that, look at your query, and when you don't get the data you want, start with the WHERE clause, since that is what decided what data is returned:
SQL
... WHERE FireArm.Serial_Number = '" + textBox1.Text + "%'"
Equals is an exact match; it doesn't acknowledge wildcards. Did you mean:
SQL
... WHERE FireArm.Serial_Number LIKE @SerNo + '%'"
   
always in any sql connection code
open and close instance SqlConnection Class . this is very important
   
Comments
Dave Kreskowiak 12-May-18 11:06am
   
This is not as critical as you think. There are things that will open and close the connection for you, like an DataAdapter or TableAdapter.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)




CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900