Execute SQL query entered inside TextBox control and display results in Datagridview
To all Gurus (willing to help a beginner):
...out there who are well versed in SQL queries against a "MS Access" database inside
a Windows Form Application:
I would like to allow an advanced user to enter in a "SQL statement" inside a TextBox control
then execute this command against a DataGridView in VB.NET Visual Studio (Windows Form Application) 2017.
Specifically, the code that will take the "SQL statement" entered inside the TextBox and display the
corresponding results in the DataGridView as seen in the attached screen capture.
The Database is in MS ACCESS 2016 (.accdb) and the main application is complete with regards to
adding, editing, deleting, searching and reporting using both Crystal Reports and Report Viewer.
However, I would just like to add this one more feature for those who know SQL well enough to be able
to allow these advanced users to generate custom reports on the fly according to an SQL statement they
enter inside the large TextBox and then display these results in the DataGridView after clicking on the
"Execute" button.
I did this once before in Visual Basic 6.0 and the code for the Execute button was as follows:
Private Sub cmdSQL_Click()
On Error Resume Next
Adodc1.RecordSource = txtSQL.Text
Adodc1.Refresh
End Sub
Obviously, this code will not work in Visual Studio because we are dealing with DataAdapters and
BindingSource elements in Visual Studio 2017.
So any help straightening me out with this one will be forever appreciated.
Re: Execute SQL query entered inside TextBox control and display results in Datagridv
Do some research on ADO.NET and make an attempt for yourself, then ask a question here if and when you encounter an actual issue. There's loads of information out there already, so there's no need for us to repeat everything from the basics. I suggest that you start with the Database FAQ link in my signature below and check out some of the ADO.NET resources on this very site.
Re: Execute SQL query entered inside TextBox control and display results in Datagridv
Using the text from a textbox as the Sql statement is no problem, as it's just a string.
So, this
Using da As New OleDbDataAdapter("Select some fields From someTable", yourConnection)
'do something
End Using
and this
Using da As New OleDbDataAdapter(someTextbox.Text, yourConnection)
'do something
End Using
Both are valid (if a valid Sql statement was entered into the textbox)
I would suggest using a Try/Catch to handle any errors
Code:
Try
Using da As New OleDbDataAdapter(someTextBox.Text, yourConnection)
'do something
End Using
Catch ex As Exception
MessageBox.Show(ex.ToString)
End Try
Re: Execute SQL query entered inside TextBox control and display results in Datagridv
There may be better ways to go about this. What you probably don't want to do is have the user be able to type in any string, and if it parses, execute it as SQL. That seems like a really bad idea, though it may be that your users would never abuse the system in ways that would wreck the database. It would certainly be possible, based on that description, though.
If all they want to do is be able to filter something from a table, you might get away with a simpler design using the RowFilter property of a DataView. If they need to be able to perform JOIN statements, then that wouldn't work.
Re: Execute SQL query entered inside TextBox control and display results in Datagridv
Originally Posted by Shaggy Hiker
There may be better ways to go about this. What you probably don't want to do is have the user be able to type in any string, and if it parses, execute it as SQL. That seems like a really bad idea...
I have to agree with this. If you must implement this, make sure your users understand the risks involved here. All it would take is something like Delete TblProducts to nuke an entire table. I really don't think it is a good idea to allow the average user access to such power.
EDIT:
On second thought, there is a away to do this kind of thing safely. You can restrict the accounts that have access to this feature. You can disallow Updates, Inserts and Deletes and only allow Selects when this feature is in use to prevent abuses like changing important data or straight up nuking entire tables.
C++ programmers will dismiss you as a cretinous simpleton for your inability to keep track of pointers chained 6 levels deep and Java programmers will pillory you for buying into the evils of Microsoft. Meanwhile C# programmers will get paid just a little bit more than you for writing exactly the same code and VB6 programmers will continue to whitter on about "footprints". - FunkyDexter
There's just no reason to use garbage like InputBox. - jmcilhinney
The threads I start are Niya and Olaf free zones. No arguing about the benefits of VB6 over .NET here please. Happiness must reign. - yereverluvinuncleber
Re: Execute SQL query entered inside TextBox control and display results in Datagridv
Originally Posted by Niya
I have to agree with this. If you must implement this, make sure your users understand the risks involved here. All it would take is something like Delete TblProducts to nuke an entire table. I really don't think it is a good idea to allow the average user access to such power.
EDIT:
On second thought, there is a away to do this kind of thing safely. You can restrict the accounts that have access to this feature. You can disallow Updates, Inserts and Deletes and only allow Selects when this feature is in use to prevent abuses like changing important data or straight up nuking entire tables.
Hello Niya:
Thank you for your reply. In regards to the dangers of what can happen, I am fully aware of this as SQL injections from bad users or other sources must be avoided. I have planned to only allow the "SELECT" command for this database as adding to, editing or deleting will not be allowed on the DataGridView or any SQL commands that would include ALTER, DELETE, INSERT etc.
The DataGridView will be disabled for this and only allowed users with specific accounts will have access to this form using a secure logon which I have already created.
All I need though is the exact CODE to put in the Execute button event to process a "SELECT FROM TABLENAME WHERE FIELD = 'CRITERIA'" and then display this on the DataGridView to be able to allow the user to print (if so desired) and that would be basically all I need.
Again, users will not be allowed to ADD, EDIT OR DELETE from this form. So I do appreciate the heads up because like you mentioned before a simple DELETE TABLE command can wipe out the entire Database.
Thank you for the heads up nonetheless.
Deee
Last edited by dideVBnet; Sep 15th, 2021 at 01:12 PM.