locked
Datagridview - Sql Query from textbox RRS feed

  • Question

  • Hi everyone,

    in need to write a query in a textbox on the form and execute it through a button, getting results in my datagridview. How can I do it?

    Thank you for support.

    Wednesday, May 22, 2019 7:32 AM

All replies

  • Hi,

    I made a demo,you can try it.

    Imports System.Data.SqlClient
    Public Class Form1
        Dim constr As String = "Data Source = (localdb)\MSSQLLocalDB; Integrated Security = True ;AttachDbFileName= C:\Users\alexl2\Desktop\DataBase\Alex\alex.mdf"
        Dim conn As SqlConnection
        Dim sda As SqlDataAdapter
        Dim dt As New DataTable
    
        Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
    
        End Sub
    
        Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
            Using conn = New SqlConnection(constr)
                conn.Open()
                sda = New SqlDataAdapter(TextBox1.Text, conn)
                sda.Fill(dt)
                DataGridView1.DataSource = dt
            End Using
        End Sub
    End Class
    

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, May 22, 2019 7:52 AM
  • Hi Alex,

    thank you for supporting... It gave my something like 3050 errors bonded to pre-existing datagridview code in my application. Is there any alternative way?

    Regards.

    Wednesday, May 22, 2019 8:29 AM
  • Hi Alex,

    thank you for supporting... It gave my something like 3050 errors bonded to pre-existing datagridview code in my application. Is there any alternative way?

    Regards.

    Hi,

    what error?Please provide your code and error screenshot.

    Best Regards,

    Alex


    MSDN Community Support Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Wednesday, May 22, 2019 8:35 AM
  • Hi Alex, 

    sorry if I don't provide it as you request but it's really hard to do it with all those errors.

    But look, I already have this code to run a query which works perfectly... 

     Private Sub Cerca_Click(sender As Object, e As EventArgs) Handles Cerca.Click
            Try
                Me.RisultatiQueryTableAdapter.Fill(Me._19QM002DataSet.RisultatiQuery, CercaCodiceErroreTextBox.Text, CercaErroreTextBox.Text)
            Catch ex As System.Exception
                System.Windows.Forms.MessageBox.Show(ex.Message)
            End Try
        End Sub

    Built as SQL Statement in Dataset.

    I hope it helps you. I'd need to pick query that I would write in a textbox and run it.

    Wednesday, May 22, 2019 9:29 AM
  • Hello,

    Generally speaking the proper way to go about performing this task is to have a SQL token parser or to present a list of tables and columns to select from e.g.

    In regards to having 3050 errors when trying Alex's code, I would recommend creating a .zip file of the project, upload it to Microsoft OneDrive (use the same login as for the forums), get a link and present the link here so we can see what may be causing this many errors followed by recommending how to move past the errors.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Wednesday, May 22, 2019 9:54 AM
  • Hi Karen,

    thank you for the answer. Is that a walk-around or the only proper way to solve my problem? Would you take a look of my project?

    Wednesday, May 22, 2019 10:06 AM
  • Hi Karen,

    thank you for the answer. Is that a walk-around or the only proper way to solve my problem? Would you take a look of my project?

    What I presented is from a code sample I wrote for MSDN code sample site which explains how to make use of SMO (SQL-Server Management Objects) but can also be done using special SQL statements as SMO is simply an API over SQL-Server statements. SMO does not work well with SQL-Server Express so that means to do the same thing SQL statements are needed to get the exact same results. For other databases e.g. MS-Access for example it's more difficult to get the same results.

    Sticking with SQL-Server with or without SMO using the image I posted as an example, the query would be pushed to a connection and command object to retrieve data into a DataTable or a List(Of T).

    In closing this is a viable solution, not a work-around. 

    If you post a .zip file link I will look at your code.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange


    Wednesday, May 22, 2019 10:36 AM
  • Hi Karen,

    thank you for the answer. Is that a walk-around or the only proper way to solve my problem? Would you take a look of my project?

    What I presented is from a code sample I wrote for MSDN code sample site which explains how to make use of SMO (SQL-Server Management Objects) but can also be done using special SQL statements as SMO is simply an API over SQL-Server statements. SMO does not work well with SQL-Server Express so that means to do the same thing SQL statements are needed to get the exact same results. For other databases e.g. MS-Access for example it's more difficult to get the same results.

    Sticking with SQL-Server with or without SMO using the image I posted as an example, the query would be pushed to a connection and command object to retrieve data into a DataTable or a List(Of T).

    In closing this is a viable solution, not a work-around. 

    If you post a .zip file link I will look at your code.



    I really do like your solution, because I'd like to give opportunity to build its own SQL and that was the meaning of my topic. Your solution was clear and looked easy to use. Where can I find your code or more infos? How end-user run it in DataGridView that's on my form?

    This is my project by the way:

    https://1drv.ms/u/s!AgvU7DK7U5HWhhyBPKohkcVBQLti

    Wednesday, May 22, 2019 11:12 AM
  • I peeked at your project (in the future upload files in a .zip file not a .rar file as some may not be able to open a .rar file like myself at work I can only work with .zip files).

    Without loading your project I noticed you are using a TableAdapter which creates strong typed classes to represent data and is fairly unforgiving right out of the box. To get around the main restrictions of a TableAdapter (which I've only used in providing answers on these forums as TableAdapters are more of a nuisance then helpful) you are going to need to go through several hoops as explained in the following article

    So to get what you want either work with the article above or go a different route then using TableAdapter.


    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange


    Wednesday, May 22, 2019 12:28 PM
  • I peeked at your project (in the future upload files in a .zip file not a .rar file as some may not be able to open a .rar file like myself at work I can only work with .zip files).

    Without loading your project I noticed you are using a TableAdapter which creates strong typed classes to represent data and is fairly unforgiving right out of the box. To get around the main restrictions of a TableAdapter (which I've only used in providing answers on these forums as TableAdapters are more of a nuisance then helpful) you are going to need to go through several hoops as explained in the following article. 

    So to get what you want either work with the article above or go a different route then using TableAdapter.



    Hi Karen,

    I read and follow the article... Is there anything newer? I think I have a problems related with coding sample about the article although I have exactly substituted parts related to my application.

    It opened my mind however. I'm on the right path.

    Thank you.

    Wednesday, May 22, 2019 3:28 PM
  • I peeked at your project (in the future upload files in a .zip file not a .rar file as some may not be able to open a .rar file like myself at work I can only work with .zip files).

    Without loading your project I noticed you are using a TableAdapter which creates strong typed classes to represent data and is fairly unforgiving right out of the box. To get around the main restrictions of a TableAdapter (which I've only used in providing answers on these forums as TableAdapters are more of a nuisance then helpful) you are going to need to go through several hoops as explained in the following article. 

    So to get what you want either work with the article above or go a different route then using TableAdapter.



    Hi Karen,

    I read and follow the article... Is there anything newer? I think I have a problems related with coding sample about the article although I have exactly substituted parts related to my application.

    It opened my mind however. I'm on the right path.

    Thank you.

    There is nothing newer, TableAdapter components have not changed over the past few years and will not as Microsoft focus is on .NET Core which is a newer Framework and at this time does not have TableAdapter components and my guess is they will not be available as the current push is for Entity Framework Core.

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Wednesday, May 22, 2019 3:32 PM
  • Ok, then, this is officially driving me crazy.

    Thursday, May 23, 2019 6:54 AM
  • Ok, then, this is officially driving me crazy.

    If I may, don't use TableAdapters, use DataAdapter or use combination of connection and commands e.g. SqlConnection SqlCommand.

    Please remember to mark the replies as answers if they help and unmarked them if they provide no help, this will help others who are looking for solutions to the same or similar problem. Contact via my Twitter (Karen Payne) or Facebook (Karen Payne) via my MSDN profile but will not answer coding question on either.

    NuGet BaseConnectionLibrary for database connections.

    StackOverFlow
    profile for Karen Payne on Stack Exchange

    Friday, May 24, 2019 2:33 PM