Solved

How can I list columns in dropdown from selected table?

Posted on 2012-04-13
6
294 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Today is the age of broadband.  More and more people are going this route determined to experience the web and it’s multitude of services as quickly and painlessly as possible. Coupled with the move to broadband, people are experiencing the web via …
Parsing a CSV file is a task that we are confronted with regularly, and although there are a vast number of means to do this, as a newbie, the field can be confusing and the tools can seem complex. A simple solution to parsing a customized CSV fi…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
How to Install VMware Tools in Red Hat Enterprise Linux 6.4 (RHEL 6.4) Step-by-Step Tutorial

726 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