skip to Main Content

I want to run a select command and I’ve a dropdownlist populated with database table names. How to write the select command? Here is my code

        Dim da As New OdbcDataAdapter("select table_name from INFORMATION_SCHEMA.tables WHERE TABLE_TYPE = 'BASE TABLE' and table_schema='public'", dbcon.con)
        Dim dt As New DataTable
        da.Fill(dt)
        ddltablename.DataSource = dt
        ddltablename.DataTextField = "table_name"
        ddltablename.DataValueField = "table_name"
        ddltablename.DataBind()

    End Sub
 Protected Sub btndump_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btndump.Click
  Dim da As New OdbcDataAdapter("select * from ddltablename.SelectedItem.tostring", dbcon.con)
  Dim ds As New DataSet
  da.Fill(ds)
  End Sub

2

Answers


  1. Probably something like this:

    Protected Sub btndump_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btndump.Click
      Dim tableName = ddltablename.SelectedItem.ToString();
      ' It would be prudent to create a function to verify table names against a 
      ' whitelist before sending it, since generating a sql command 
      ' using string concatenation carries the risk of sql injection
      Dim da As New OdbcDataAdapter("select * from " & tableName & ";", dbcon.con)
      Dim ds As New DataSet
      da.Fill(ds)
    End Sub
    

    Getting the value of your control, ddltablename, has to be done in the application context, not within the SQL command.

    Login or Signup to reply.
  2. Sure, lets drop in your combo box, and then a gridview.

    like this:

        <asp:DropDownList ID="cboTables" runat="server" Height="31px" Width="179px"
            DataTextField ="table_name"
            DataValueField ="table_name" Rows="50" >
        </asp:DropDownList>
    
    
        <asp:Button ID="cmdShowTables" runat="server" Text="Show Selected table" Width="175px" style="margin-left:25px"/>
        <br />
        <br />
    
    
        <asp:GridView ID="GridView1" runat="server"></asp:GridView>
    

    And our code can thus be:

    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
    
        If Not IsPostBack Then
    
            Dim strSQL As String =
                "SELECT table_name from INFORMATION_SCHEMA.tables " &
                "WHERE TABLE_TYPE = 'BASE TABLE' ORDER BY table_name"
    
            cboTables.DataSource = MyRst(strSQL)
            cboTables.DataBind()
    
        End If
    
    End Sub
    
    Protected Sub cmdShowTables_Click(sender As Object, e As EventArgs) Handles cmdShowTables.Click
    
        Dim rst As New DataTable
        rst = MyRst("SELECT * from " & cboTables.SelectedItem.Value)
        'GridView1.DataSource
        GridView1.DataSource = rst
        GridView1.DataBind()
    
    End Sub
    
    
    Function MyRst(strSQL As String) As DataTable
    
        Dim rstData As New DataTable
    
        Using conn As New OdbcConnection(My.Settings.TEST3ODBC)
            Using cmdSQL As New OdbcCommand(strSQL, conn)
                conn.Open()
                rstData.Load(cmdSQL.ExecuteReader)
            End Using
        End Using
    
        Return rstData
    
    End Function
    

    Output:

    enter image description here

    Or you can say do this:

        Dim rst As New DataTable
        rst = MyRst("SELECT * from " & cboTables.SelectedItem.Value)
        For Each OneRow as DataRow in rst.rows
            debug.print ("Hotel Name = " & OneRow("HoteName").ToString())
        Next
    
    Login or Signup to reply.
Please signup or login to give your own answer.
Back To Top
Search