Results 1 to 8 of 8

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

  1. #1

    Thread Starter
    New Member
    Join Date
    Sep 2021
    Posts
    3

    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.

    I have included a screen shot of the form

    Much thanks to all in advance.

    Deee
    Attached Images Attached Images  

  2. #2
    .NUT jmcilhinney's Avatar
    Join Date
    May 2005
    Location
    Sydney, Australia
    Posts
    107,634

    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.

  3. #3
    PowerPoster
    Join Date
    Sep 2005
    Location
    Modesto, Ca.
    Posts
    4,762

    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

  4. #4
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    36,574

    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.
    My usual boring signature: Nothing

  5. #5
    Angel of Code Niya's Avatar
    Join Date
    Nov 2011
    Posts
    6,484

    Re: Execute SQL query entered inside TextBox control and display results in Datagridv

    Quote Originally Posted by Shaggy Hiker View Post
    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.
    Last edited by Niya; Sep 15th, 2021 at 12:21 PM.
    Treeview with NodeAdded/NodesRemoved events | BlinkLabel control | Calculate Permutations | Object Enums | ComboBox with centered items | .Net Internals article(not mine) | Wizard Control | Understanding Multi-Threading | Simple file compression | Demon Arena

    Copy/move files using Windows Shell

    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

  6. #6

    Thread Starter
    New Member
    Join Date
    Sep 2021
    Posts
    3

    Re: Execute SQL query entered inside TextBox control and display results in Datagridv

    Quote Originally Posted by Niya View Post
    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.

  7. #7

    Thread Starter
    New Member
    Join Date
    Sep 2021
    Posts
    3

    Re: Execute SQL query entered inside TextBox control and display results in Datagridv

    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

  8. #8
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    36,574

    Re: Execute SQL query entered inside TextBox control and display results in Datagridv

    With ADO.NET, you create a connection as a first step. That's probably an OleDBConnection object, for Access.
    My usual boring signature: Nothing

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width