Execute SQL query entered inside TextBox control and display results in Datagridview

[dideVBnet]

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.

I have included a screen shot of the form

Much thanks to all in advance.

Deee

[jmcilhinney]

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.

[wes4dbt]

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

        Try
            Using da As New OleDbDataAdapter(someTextBox.Text, yourConnection)
                'do something
            End Using
        Catch ex As Exception
            MessageBox.Show(ex.ToString)
        End Try

[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, 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.

[Niya]

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.

[dideVBnet]

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

[dideVBnet]

Hello wes4dbt:

Thank you for your reply!

I understand the Try/Catch code you provided well and I most probably will include this in the code.

The other code you provided I also understand except for "yourConnection" - what should I put
in place of "yourConnection"?

I know that "TextBox.Text" would be "txtSQL.Text" since this is the name of the TextBox itself.

But I am not sure what to substitute "yourConnection" for.

Please advise and thank you for your help.

Regards,
Deee

[Shaggy Hiker]

With ADO.NET, you create a connection as a first step. That's probably an OleDBConnection object, for Access.