How to get values from an object in sql query in asp.net vb.net-CodePudding

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

CodePudding user response：

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 generated 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.

CodePudding user response：

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:

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