Solved

How can I list columns in dropdown from selected table?

Posted on 2012-04-13
6
278 Views
Last Modified: 2012-04-14
I have four dropdowns:
1st displays provider type
2nd displays databases on server
3rd displays tables in selected database
4th    How can I list columns in dropdown from selected table?
The code I have works if ddlColumn is a datagrid, but I now want to use a dropdown.
Thanks in advance for any help given.

Protected Sub ddlTable_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs)
        Response.Write("<div style='position:absolute;width:530px;height:154px;left:9px;top:87px;font-size:xx-small;overflow:auto;border:1px solid #722F90;' id='DIV17'>")
        HttpContext.Current.Session("strTable") = ddlTable.SelectedItem.ToString()
        ProviderLabel.Text = HttpContext.Current.Session("strProvider")
        DataBaseLabel.Text = HttpContext.Current.Session("strDatabase")
        TableLabel.Text = HttpContext.Current.Session("strTable")
        detailsLabel.Text = HttpContext.Current.Session("strConnection") & "Initial Catalog=" & DataBaseLabel.Text & ";"
        Dim server_tb As String = Nothing
        Dim user_tb As String = Nothing
        Dim pswrd_tb As String = Nothing
        Dim timeout_tb As String = Nothing
        Dim database_tb As String = Nothing
        Dim table_tb As String = Nothing
        Dim connstr As String = "@" & detailsLabel.Text
        Dim str1() As String = connstr.Split(";"c)
        server_tb = str1(0).Split("="c)(1)
        'trusted_tb = str1(1).Split("="c)(1)
        user_tb = str1(1).Split("="c)(1)
        pswrd_tb = str1(2).Split("="c)(1)
        'timeout_tb = str1[4].Split('=')[1];
        'database_tb = str1[5].Split('=')[1];
        'database_tb = str1[1].Split("="c)[1];
        database_tb = str1(3).Split("="c)(1)
        table_tb = TableLabel.Text
        Response.Write("730:server_tb:" & server_tb & "  user_tb:" & user_tb & "  pswrd_tb:" & pswrd_tb & "  database_tb:" & database_tb & "  table_tb:" & table_tb & "#<br/>")
        Session("Server") = server_tb
        Session("UserName") = user_tb
        Session("Password") = pswrd_tb
        Session("DatabaseSelected") = database_tb
        'Session("TrustedSelected") = trusted_tb
        Session("TableSelected") = table_tb
        Session("IsConnectionInfoSet") = True
        osqlQuery = "select * from " & table_tb
        Dim conn As New SqlConnection(detailsLabel.Text & ";")
        Dim sda As New SqlDataAdapter(osqlQuery, conn)
        Dim dtData As New DataTable()
        Dim dt As DataTable = Nothing
        Dim ds As New DataSet()
        Try
            '*******************************************
            Dim database As sqlCamsAdmin.SqlDatabase = sqlCamsAdmin.SqlDatabase.CurrentDatabase(Server)
            Dim table As sqlCamsAdmin.SqlTable = database.Tables(Request("table"))
            Dim columns As SqlColumnCollection = Table.Columns
            '*******************************************
            ds.Tables.Add()
            ds.Tables(0).Columns.Add("key", GetType(Boolean))
            ds.Tables(0).Columns.Add("id", GetType(Boolean))
            ds.Tables(0).Columns.Add("name", GetType(String))
            ds.Tables(0).Columns.Add("datatype", GetType(String))
            ds.Tables(0).Columns.Add("size", GetType(Integer))
            ds.Tables(0).Columns.Add("precision", GetType(Integer))
            ds.Tables(0).Columns.Add("scale", GetType(Integer))
            ds.Tables(0).Columns.Add("nulls", GetType(Boolean))
            ds.Tables(0).Columns.Add("default", GetType(String))
            ds.Tables(0).Columns.Add("encodedname", GetType(String))
            For i As Integer = 0 To columns.Count - 1
                Dim columnInfo As SqlColumnInformation = columns(i).ColumnInformation
                ds.Tables(0).Rows.Add(New Object() {columnInfo.Key, columnInfo.Identity, Me.Server.HtmlEncode(columnInfo.Name), Me.Server.HtmlEncode(columnInfo.DataType), columnInfo.Size, columnInfo.Precision, columnInfo.Scale, columnInfo.Nulls, Me.Server.HtmlEncode(columnInfo.DefaultValue), Me.Server.UrlEncode(columnInfo.Name)})
            Next i
            ddlColumn.DataSource = ds
            ddlColumn.DataBind()
        Catch ex As Exception
            ErrorLabel2.Text = "=2:768:Err:<br/><br/><br/><br/><br/><br/><br/><br/><br/><br/><br/><br/><br/><br/><br/><br/><br/><br/>" & ex.ToString()
        End Try
        Response.Write("</div>")
    End Sub


Protected Sub ddlProvider_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs)
        '***Start Code Block***
        'Dim server As SqlServer = SqlServer.CurrentServer
        HttpContext.Current.Session("strProvider") = ddlProvider.SelectedItem.ToString()
        ProviderLabel.Text = HttpContext.Current.Session("strProvider")
        detailsLabel.Text = HttpContext.Current.Session("strConnection")
        DataBaseLabel.Text = HttpContext.Current.Session("strDatabase")
        TableLabel.Text = HttpContext.Current.Session("strTable")
        Try
            odbName = "master"
            Dim server As SqlServer = Nothing
            server = SqlServer.CurrentServer
            Dim GetString As String = Nothing
            Dim GetString2 As String = Nothing
            Dim builder As New SqlConnectionStringBuilder(server.GetConnectionString())
            Dim conn As New SqlConnection()
            GetString = (builder.ConnectionString & "; Initial Catalog=" & odbName & ";")
            conn = New SqlConnection(GetString)
            conn.Open()
            Dim tblDatabases As DataTable = conn.GetSchema(SqlClientMetaDataCollectionNames.Databases)
            If conn.State <> ConnectionState.Closed Then
                conn.Close()
            End If
            Dim filterList As New List(Of String)()
            filterList.Add("master")
            filterList.Add("model")
            filterList.Add("msdb")
            filterList.Add("tempdb")
            filterList.Add("ASPNETDB")
            Dim items As New ArrayList()
            ddl.Items.Clear()
            items.Add("Select").ToString()
            For Each rowDatabase As DataRow In tblDatabases.Rows
                If Not (String.IsNullOrEmpty(rowDatabase("database_name").ToString())) AndAlso Not (filterList.Contains(rowDatabase("database_name").ToString())) Then
                    If rowDatabase("database_name").ToString() = "master" Then

                    Else
                        builder.InitialCatalog = rowDatabase("database_name").ToString()
                        conn.ConnectionString = builder.ConnectionString
                        items.Add(rowDatabase("database_name").ToString())
                    End If

                End If
            Next rowDatabase
            If items.Count <> 0 Then
                ddl.DataSource = items
                ddl.DataBind()
            End If
        Catch ex As System.Exception
            ErrorLabel2.Text = "649:BD1:" & ex.ToString()
        End Try
    End Sub


    Protected Sub ddl_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs)
        '***Start Code Block***
        HttpContext.Current.Session("strDatabase") = ddl.SelectedItem.ToString()
        ProviderLabel.Text = HttpContext.Current.Session("strProvider")
        detailsLabel.Text = HttpContext.Current.Session("strConnection")
        DataBaseLabel.Text = HttpContext.Current.Session("strDatabase")
        TableLabel.Text = HttpContext.Current.Session("strTable")
        Try
            Dim odbName As String = HttpContext.Current.Session("strDatabase")
            Dim odbreader As SqlDataReader = Nothing
            Dim odbcomm As SqlCommand = Nothing
            Dim oCounter As Integer = 0
            Dim tblTables As DataTable = Nothing
            Dim oItems As New ArrayList()
            Dim hsh As New Hashtable()
            Dim data2 As New ArrayList()
            Dim server As sqlCamsAdmin.SqlServer = Nothing
            server = sqlCamsAdmin.SqlServer.CurrentServer
            Dim builder As New SqlConnectionStringBuilder(server.GetConnectionString())
            Dim odbconn As New SqlConnection(builder.ConnectionString & "; Initial Catalog=" & odbName & ";")
            odbconn.Open()
            tblTables = odbconn.GetSchema(SqlClientMetaDataCollectionNames.Tables)
            odbconn.Close()
            Dim filterList As New List(Of String)()
            'filterList.Add("master");
            'filterList.Add("model");
            'filterList.Add("msdb");
            'filterList.Add("tempdb");
            'filterList.Add("ASPNETDB");
            If Page.IsPostBack Then
                ddlTable.Items.Clear()
                oItems.Add("Select").ToString()
                data2.Add("Select").ToString()
                For Each rowDatabase As DataRow In tblTables.Rows
                    If Not (String.IsNullOrEmpty(rowDatabase("table_name").ToString())) Then
                        builder.InitialCatalog = rowDatabase("table_name").ToString()
                        odbconn.ConnectionString = builder.ConnectionString
                        oItems.Add(rowDatabase("table_name").ToString())
                        data2.Add(KP(rowDatabase("table_name").ToString()))
                    End If
                    oCounter += 1
                Next rowDatabase
                If odbconn.State <> ConnectionState.Closed Then
                    odbconn.Close()
                End If
                ddlTable.DataSource = data2
                'ddlTable.DataSource = oItems
                ddlTable.DataBind()
            End If
        Catch ex As System.Exception
            ErrorLabel2.Text = "702:ddl:" & ex.ToString()
        End Try
        '***End Code Block***
    End Sub
' KM for KeyValue is defined as returning an anonymous object:
    Private Function KM(ByVal Key As String, ByVal Value As String) As Object
        Return New With {Key Key, Key Value}
    End Function
    ' KP for KeyValue is defined as returning an anonymous object:
    'Private Function KP(ByVal Key As String, ByVal Value As String) As Object
    'Return New With {Key Key, Key Value}
    Private Function KP(ByVal Value As String) As Object
        Return Value
    End Function
0
Comment
Question by:homeshopper
  • 4
  • 2
6 Comments
 

Author Comment

by:homeshopper
ID: 37843504
Forgot to list the error when the code runs.
           '*******************************************
           '****Line 747 listed below ****
            Dim server As sqlCamsAdmin.SqlServer = sqlCamsAdmin.SqlServer.CurrentServer
            Dim database As sqlCamsAdmin.SqlDatabase = sqlCamsAdmin.SqlDatabase.CurrentDatabase(Server)
            Dim table As sqlCamsAdmin.SqlTable = database.Tables(Request("table"))
            Dim columns As SqlColumnCollection = Table.Columns
            '*******************************************
System.NullReferenceException: Object reference not set to an instance of an object. at sqlCamsAdmin.SqlDatabaseCollection.Refresh() in C:\Projects\cams00CsVbDev\sqlCams00Data\sqlServerCode.cs:line 2568 at sqlCamsAdmin.SqlDatabase.CurrentDatabase(SqlServer server) in C:\Projects\cams00CsVbDev\sqlCams00Data\sqlServerCode.cs:line 2775 at scriptFrame_tempScript.ddlTable_SelectedIndexChanged(Object sender, EventArgs e) in C:\Projects\cams00CsVbDev\sqlScriptVbData\scriptFrame\tempScript.aspx.vb:line 747
0
 
LVL 10

Expert Comment

by:adriankohws
ID: 37843508
After end-users selected the 3rd dropdown, use the tablename and execute a sql statement to the selected database to display the columns;

SELECT column_name
FROM information_schema.COLUMNS WHERE table_name = 'SelectedTableName'
ORDER BY ordinal_position
0
 
LVL 10

Accepted Solution

by:
adriankohws earned 500 total points
ID: 37843571
Private Sub 3rdCombobox_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles 3rdCombobox.SelectedIndexChanged

        4thCombobox.Clear()
        Dim eSQL As String = "SELECT column_name FROM information_schema.COLUMNS WHERE Table_name = 'SelectedTableName' ORDER BY ordinal_position"
         Dim Cmd As New SqlCommand(eSQL, YourConnection)
       Dim RD As SqlDataReader = Cmd.ExecuteReader
        Try
            While RD.Read
                4thCombobox.Items.Add(RD(0).ToString)
            End While
        Catch ex As Exception
            MsgBox("Error while executing (" & Cmd.CommandText & ")" & vbCrLf & "Details: " & ex.Message, MsgBoxStyle.Exclamation, "MSIS Server")
        Finally
            RD.Close()
            Cmd.Dispose()
        End Try
End Sub
0
Highfive + Dolby Voice = No More Audio Complaints!

Poor audio quality is one of the top reasons people don’t use video conferencing. Get the crispest, clearest audio powered by Dolby Voice in every meeting. Highfive and Dolby Voice deliver the best video conferencing and audio experience for every meeting and every room.

 

Author Comment

by:homeshopper
ID: 37844361
Thankyou for your comments, it is very helpful.
However, I am getting new error.
The full code is at the end.
The connection detail is as follows:
label:Data Source=dinosaur1;User ID=sa;Initial Catalog=Cams2;
eSQL:SELECT column_name FROM information_schema.COLUMNS WHERE Table_name = tblItems
error as follows:
System.Data.SqlClient.SqlException: Invalid object name 'information_schema.COLUMNS'. at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) at System.Data.SqlClient.SqlDataReader.ConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString) at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result) at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method) at System.Data.SqlClient.SqlCommand.ExecuteReader() at scriptFrame_tempScript.ddlTable_SelectedIndexChanged(Object sender, EventArgs e) in C:\Projects\cams00CsVbDev\sqlScriptVbData\scriptFrame\tempScript.aspx.vb:line 722
Protected Sub ddlTable_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlTable.SelectedIndexChanged
        Response.Write("<div style='position:absolute;width:530px;height:154px;left:9px;top:87px;font-size:xx-small;overflow:auto;border:1px solid #722F90;' id='DIV17'>")
        HttpContext.Current.Session("strTable") = ddlTable.SelectedItem.ToString()
        ProviderLabel.Text = HttpContext.Current.Session("strProvider")
        DataBaseLabel.Text = HttpContext.Current.Session("strDatabase")
        TableLabel.Text = HttpContext.Current.Session("strTable")
        detailsLabel.Text = HttpContext.Current.Session("strConnection") & "Initial Catalog=" & DataBaseLabel.Text & ";"
        'ddlColumn.Clear()
        Try
            Dim conn As New SqlConnection(detailsLabel.Text)
            Dim eSQL As String = "SELECT column_name FROM information_schema.COLUMNS WHERE Table_name = " & TableLabel.Text '& " ORDER BY ordinal_position"
            If conn.State <> ConnectionState.Open Then
                conn.Open()
            End If
            Dim Cmd As New SqlCommand(eSQL, conn)
            ErrorLabel.Text = conn.ConnectionString & "<br/>eSQL:" & eSQL
            Dim RD As SqlDataReader = Cmd.ExecuteReader
            While RD.Read
                ddlColumn.Items.Add(RD(0).ToString)
            End While
            RD.Close()
            Cmd.Dispose()
        Catch ex As Exception
            ErrorLabel2.Text = ":731:err:<br/><br/><br/><br/><br/><br/><br/><br/><br/><br/><br/><br/><br/><br/><br/>" & _
            ex.ToString()
            'ErrorLabel2.Text = "Error while executing (" & Cmd.CommandText & ")" & vbCrLf & "Details: " & ex.Message
        End Try
        Response.Write("</div>")
    End Sub
0
 

Author Comment

by:homeshopper
ID: 37844785
With your suggestions I have now got all the code to work.
I used
Dim eSQL As String = "SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = " & "'" & TableLabel.Text & "'" & " ORDER BY ordinal_position"

Thanks again for your help.
The complete code is below for others to see:


Protected Sub ddlProvider_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs)
        '***Start Code Block***
        HttpContext.Current.Session("strProvider") = ddlProvider.SelectedItem.ToString()
        ProviderLabel.Text = HttpContext.Current.Session("strProvider")
        detailsLabel.Text = HttpContext.Current.Session("strConnection")
        DataBaseLabel.Text = HttpContext.Current.Session("strDatabase")
        TableLabel.Text = HttpContext.Current.Session("strTable")
        Try
            odbName = "master"
            Dim server As SqlServer = Nothing
            server = SqlServer.CurrentServer
            Dim GetString As String = Nothing
            Dim GetString2 As String = Nothing
            Dim builder As New SqlConnectionStringBuilder(server.GetConnectionString())
            Dim conn As New SqlConnection()
            GetString = (builder.ConnectionString & "; Initial Catalog=" & odbName & ";")
            conn = New SqlConnection(GetString)
            conn.Open()
            Dim tblDatabases As DataTable = conn.GetSchema(SqlClientMetaDataCollectionNames.Databases)
            If conn.State <> ConnectionState.Closed Then
                conn.Close()
            End If
            Dim filterList As New List(Of String)()
            filterList.Add("master")
            filterList.Add("model")
            filterList.Add("msdb")
            filterList.Add("tempdb")
            filterList.Add("ASPNETDB")
            Dim items As New ArrayList()
            ddl.Items.Clear()
            items.Add("Select").ToString()
            For Each rowDatabase As DataRow In tblDatabases.Rows
                If Not (String.IsNullOrEmpty(rowDatabase("database_name").ToString())) AndAlso Not (filterList.Contains(rowDatabase("database_name").ToString())) Then
                    If rowDatabase("database_name").ToString() = "master" Then

                    Else
                        builder.InitialCatalog = rowDatabase("database_name").ToString()
                        conn.ConnectionString = builder.ConnectionString
                        items.Add(rowDatabase("database_name").ToString())
                    End If

                End If
            Next rowDatabase
            If items.Count <> 0 Then
                ddl.DataSource = items
                ddl.DataBind()
            End If
        Catch ex As System.Exception
            ErrorLabel2.Text = "648:BD1:" & ex.ToString()
        End Try
    End Sub
    Protected Sub ddl_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs)
        '***Start Code Block***
        HttpContext.Current.Session("strDatabase") = ddl.SelectedItem.ToString()
        ProviderLabel.Text = HttpContext.Current.Session("strProvider")
        detailsLabel.Text = HttpContext.Current.Session("strConnection")
        DataBaseLabel.Text = HttpContext.Current.Session("strDatabase")
        TableLabel.Text = HttpContext.Current.Session("strTable")
        Try
            Dim odbName As String = HttpContext.Current.Session("strDatabase")
            Dim odbreader As SqlDataReader = Nothing
            Dim odbcomm As SqlCommand = Nothing
            Dim oCounter As Integer = 0
            Dim tblTables As DataTable = Nothing
            Dim oItems As New ArrayList()
            Dim hsh As New Hashtable()
            Dim data2 As New ArrayList()
            Dim server As sqlCamsAdmin.SqlServer = Nothing
            server = sqlCamsAdmin.SqlServer.CurrentServer
            Dim builder As New SqlConnectionStringBuilder(server.GetConnectionString())
            Dim odbconn As New SqlConnection(builder.ConnectionString & "; Initial Catalog=" & odbName & ";")
            odbconn.Open()
            tblTables = odbconn.GetSchema(SqlClientMetaDataCollectionNames.Tables)
            odbconn.Close()
            Dim filterList As New List(Of String)()
            'filterList.Add("master");
            'filterList.Add("model");
            'filterList.Add("msdb");
            'filterList.Add("tempdb");
            'filterList.Add("ASPNETDB");
            If Page.IsPostBack Then
                ddlTable.Items.Clear()
                oItems.Add("Select").ToString()
                data2.Add("Select").ToString()
                For Each rowDatabase As DataRow In tblTables.Rows
                    If Not (String.IsNullOrEmpty(rowDatabase("table_name").ToString())) Then
                        builder.InitialCatalog = rowDatabase("table_name").ToString()
                        odbconn.ConnectionString = builder.ConnectionString
                        oItems.Add(rowDatabase("table_name").ToString())
                        data2.Add(KP(rowDatabase("table_name").ToString()))
                    End If
                    oCounter += 1
                Next rowDatabase
                If odbconn.State <> ConnectionState.Closed Then
                    odbconn.Close()
                End If
                ddlTable.DataSource = data2
                'ddlTable.DataSource = oItems
                ddlTable.DataBind()
            End If
        Catch ex As System.Exception
            ErrorLabel2.Text = "701:ddl:" & ex.ToString()
        End Try
        '***End Code Block***
    End Sub
    Protected Sub ddlTable_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlTable.SelectedIndexChanged
        Response.Write("<div style='position:absolute;width:530px;height:154px;left:9px;top:87px;font-size:xx-small;overflow:auto;border:1px solid #722F90;' id='DIV17'>")
        HttpContext.Current.Session("strTable") = ddlTable.SelectedItem.ToString()
        ProviderLabel.Text = HttpContext.Current.Session("strProvider")
        DataBaseLabel.Text = HttpContext.Current.Session("strDatabase")
        TableLabel.Text = HttpContext.Current.Session("strTable")
        detailsLabel.Text = HttpContext.Current.Session("strConnection") & "Initial Catalog=" & DataBaseLabel.Text & ";"
        Try
            Dim conn As New SqlConnection(detailsLabel.Text)
            Dim eSQL As String = "SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = " & "'" & TableLabel.Text & "'" & " ORDER BY ordinal_position"
            If conn.State <> ConnectionState.Open Then
                conn.Open()
            End If
            Dim Cmd As New SqlCommand(eSQL, conn)
            ErrorLabel.Text = conn.ConnectionString & "<br/>eSQL:" & eSQL
            Dim RD As SqlDataReader = Cmd.ExecuteReader
            While RD.Read
                ddlColumn.Items.Add(RD(0).ToString)
            End While
            RD.Close()
            Cmd.Dispose()
        Catch ex As Exception
            ErrorLabel2.Text = ":727:err:<br/><br/><br/><br/><br/><br/><br/><br/><br/><br/><br/><br/><br/><br/><br/>" & _
            ex.ToString()
        End Try
        Response.Write("</div>")
    End Sub
    Protected Sub ddlColumn_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlColumn.SelectedIndexChanged
        Response.Write("<div style='position:absolute;width:530px;height:154px;left:9px;top:87px;font-size:xx-small;overflow:auto;border:1px solid #722F90;' id='DIV17'>")
        HttpContext.Current.Session("strColumn") = ddlColumn.SelectedItem.ToString()
        'HttpContext.Current.Session("strColumn2") = ddlColumn2.SelectedItem.ToString()
        ProviderLabel.Text = HttpContext.Current.Session("strProvider")
        DataBaseLabel.Text = HttpContext.Current.Session("strDatabase")
        TableLabel.Text = HttpContext.Current.Session("strTable")
        detailsLabel.Text = HttpContext.Current.Session("strConnection") & "Initial Catalog=" & DataBaseLabel.Text & ";"
        Try
            Dim conn As New SqlConnection(detailsLabel.Text)
            Dim eSQL As String = "SELECT column_name FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME = " & "'" & TableLabel.Text & "'" & " ORDER BY ordinal_position"
            If conn.State <> ConnectionState.Open Then
                conn.Open()
            End If
            Dim Cmd As New SqlCommand(eSQL, conn)
            ErrorLabel.Text = conn.ConnectionString & "<br/>eSQL:" & eSQL
            Dim RD As SqlDataReader = Cmd.ExecuteReader
            While RD.Read
                ddlColumn2.Items.Add(RD(0).ToString)
            End While
            RD.Close()
            Cmd.Dispose()
        Catch ex As Exception
            ErrorLabel2.Text = ":755:err:<br/><br/><br/><br/><br/><br/><br/><br/><br/><br/><br/><br/><br/><br/><br/>" & _
            ex.ToString()
        End Try
        Response.Write("</div>")
    End Sub
    Protected Sub ddlColumn2_SelectedIndexChanged(ByVal sender As Object, ByVal e As System.EventArgs) Handles ddlColumn2.SelectedIndexChanged
        Response.Write("<div style='position:absolute;width:530px;height:154px;left:9px;top:87px;font-size:xx-small;overflow:auto;border:1px solid #722F90;' id='DIV17'>")
        HttpContext.Current.Session("strColumn") = ddlColumn.SelectedItem.ToString()
        HttpContext.Current.Session("strColumn2") = ddlColumn2.SelectedItem.ToString()
        ProviderLabel.Text = HttpContext.Current.Session("strProvider")
        DataBaseLabel.Text = HttpContext.Current.Session("strDatabase")
        TableLabel.Text = HttpContext.Current.Session("strTable")
        detailsLabel.Text = HttpContext.Current.Session("strConnection") & "Initial Catalog=" & DataBaseLabel.Text & ";"
        LoadData()
        Response.Write("</div>")
    End Sub
    Private Sub Reset()
        Dim SQL As String = "UPDATE tblItems INNER JOIN tblItemsBackup ON tblItems.ItemID = tblItemsBackup.ItemID SET tblItems.Item = tblItemsBackup.ItemBackup;"
        Dim CMD As New OleDb.OleDbCommand(SQL, m_DBCon)
        CMD.ExecuteNonQuery()
    End Sub
    Private Sub LoadData()
        ErrorLabel.Text = ""
        ErrorLabel2.Text = ""
        ErrorLabel3.Text = ""
        Try
            ProviderLabel.Text = HttpContext.Current.Session("strProvider")
            DataBaseLabel.Text = HttpContext.Current.Session("strDatabase")
            TableLabel.Text = HttpContext.Current.Session("strTable")
            detailsLabel.Text = HttpContext.Current.Session("strConnection") & " Initial Catalog=" & DataBaseLabel.Text & ";"
            'Dim strConn As String = HttpContext.Current.Session("strConnection")
            'Dim strDB As String = HttpContext.Current.Session("strDatabase")
            'Dim strTab As String = HttpContext.Current.Session("strTable")
            Dim strCol As String = HttpContext.Current.Session("strColumn")
            Dim strCol2 As String = HttpContext.Current.Session("strColumn2")
            m_DBCon.Close()
            If ProviderLabel.Text = "Null" Then
                m_DBCon.ConnectionString = detailsLabel.Text & "; Initial Catalog=" & DataBaseLabel.Text & ";"
            Else
                m_DBCon.ConnectionString = ProviderLabel.Text & "; " & detailsLabel.Text & "; Initial Catalog=" & DataBaseLabel.Text & ";"
            End If
            m_DBCon.Open()
            Dim strSQL As String
            strSQL = "Select * From " & TableLabel.Text
            Dim DA As OleDb.OleDbDataAdapter
            Dim DS As DataSet
            DA = New OleDb.OleDbDataAdapter(strSQL, m_DBCon)
            DS = New DataSet()
            DS.Clear()
            ErrorLabel.Text = "Label:804:LoadData():" & connectionString & "# strDB:" '& strDB & "# strTab:" & strTab & "# strCol:" & strCol & "# strCol2:" & strCol2 & "#<br/>" & _
            ErrorLabel3.Text = "strSQL" & strSQL & "#"
            DA.Fill(DS)
            Me.DGrid.DataSource = DS
            Me.DGrid.DataKeyField = strCol
            Me.DGrid.DataBind()
        Catch ex As System.Exception
            ErrorLabel2.Text = "err:811:" & ex.ToString()
            ErrorLabel3.Text = "Conn:812:" & HttpContext.Current.Session("strConnection") & "# strDB:" & HttpContext.Current.Session("strDatabase") & "# strTab:" & HttpContext.Current.Session("strTable") & "# strCol:" & HttpContext.Current.Session("strColumn") & "# strCol2:" & HttpContext.Current.Session("strColumn2") & "#"
        End Try
    End Sub
    Public Sub Page_Unload(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Unload
        Try
            m_DBCon.Close()
        Catch ex As System.Exception
            ErrorLabel.Text = "err:814:" & ex.ToString()
        End Try
    End Sub
    Public Sub DGrid_EditCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles DGrid.EditCommand
        Me.DGrid.EditItemIndex = e.Item.ItemIndex
        'Me.DGrid.CurrentPageIndex = e.Item.ItemIndex
        'Me.DGrid.DataBind()
        Session("SelecetdRowIndex") = e.Item.ItemIndex
        ErrorLabel.Text = ""
        ErrorLabel2.Text = ""
        ErrorLabel3.Text = ""
        ErrorLabel3.Text = "828:DGrid_EditCommand:"
        LoadData()
    End Sub
    Public Sub DGrid_UpdateCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles DGrid.UpdateCommand
        Try
            ErrorLabel.Text = ""
            ErrorLabel2.Text = ""
            ErrorLabel3.Text = ""
            Dim strConn As String = HttpContext.Current.Session("strConnection")
            Dim strDB As String = HttpContext.Current.Session("strDatabase")
            Dim strTab As String = HttpContext.Current.Session("strTable")
            Dim strCol As String = HttpContext.Current.Session("strColumn")
            Dim strCol2 As String = HttpContext.Current.Session("strColumn2")
            m_DBCon.Close()
            If ProviderLabel.Text = "Null" Then
                m_DBCon.ConnectionString = detailsLabel.Text & "; Initial Catalog=" & DataBaseLabel.Text & ";"
            Else
                m_DBCon.ConnectionString = ProviderLabel.Text & "; " & detailsLabel.Text & "; Initial Catalog=" & DataBaseLabel.Text & ";"
            End If
            m_DBCon.Open()
            Dim m_Key As Long
            Dim strKey As String = "e." & strCol2 & ".ItemIndex"
            Dim strItem As String = "e." & strCol & ".FindControl('txtItem')"
            'm_Key = DGrid.DataKeys(CType(strKey, Long))
            'm_Key = DGrid.DataKeys(CType(strCol2, Long))
            m_Key = DGrid.DataKeys(e.Item.ItemIndex)
            Dim tbox As TextBox
            'tbox.Text = strItem
            tbox = e.Item.FindControl("txtItem")
            Dim strSQL As String = "Update " & strTab & " Set " & strCol2 & "='" & tbox.Text & "' Where " & strCol & "=" & m_Key
            Dim CMD As New OleDb.OleDbCommand(strSQL, m_DBCon)
            ErrorLabel.Text = "Label:861:DGrid_UpdateCommand():" & strConn & "# strDB:" & strDB & "# strTab:" & strTab & "# strCol:" & strCol & "# strCol2:" & strCol2 & "#"
            ErrorLabel2.Text = "862:strSQL:" & strSQL & "#"
            CMD.ExecuteNonQuery()
            Me.DGrid.EditItemIndex = -1
            LoadData()
        Catch ex As System.Exception
            ErrorLabel2.Text = "err:867:" & ex.ToString()
            ErrorLabel3.Text = "Conn:868:" & HttpContext.Current.Session("strConnection") & "#"
        End Try
    End Sub
    Public Sub DGrid_CancelCommand(ByVal source As Object, ByVal e As System.Web.UI.WebControls.DataGridCommandEventArgs) Handles DGrid.CancelCommand
        Me.DGrid.EditItemIndex = -1
        LoadData()
    End Sub
    Protected Sub DGrid_PageIndexChanging(ByVal sender As Object, ByVal e As GridViewPageEventArgs)
        'CreateTemplatedGridView()
        'Me.DGrid.EditItemIndex = e.Item.ItemIndex
        DGrid.CurrentPageIndex = e.NewPageIndex
        DGrid.DataBind()
    End Sub
0
 

Author Closing Comment

by:homeshopper
ID: 37845813
Thanks for the help and suggested code.
With a slight alteration, your code works well.
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

A quick way to get a menu to work on our website, is using the Menu control and assign it to a web.sitemap using SiteMapDataSource. Example of web.sitemap file: (CODE) Sample code to add to the page menu: (CODE) Running the application, we wi…
The ECB site provides FX rates for major currencies since its inception in 1999 in the form of an XML feed. The files have the following format (reducted for brevity) (CODE) There are three files available HERE (http://www.ecb.europa.eu/stats/exch…
When you create an app prototype with Adobe XD, you can insert system screens -- sharing or Control Center, for example -- with just a few clicks. This video shows you how. You can take the full course on Experts Exchange at http://bit.ly/XDcourse.
This video demonstrates how to create an example email signature rule for a department in a company using CodeTwo Exchange Rules. The signature will be inserted beneath users' latest emails in conversations and will be displayed in users' Sent Items…

706 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now