how to populate combo with database column names to plot a graph?

[satti]

there are two combobox cm1 and cm2 when user select columns from database for x axis and y axis in cm1 and cm2 the graph show the data from those columns .
Need your help that how to populate combos with column names and to show the graph of selected columns.

[G_Waddell]

Hi,
Try something like this:

Sub PopulateComboWithFieldNames(byref TableName as string, byref Combo as ComboBox, ByVal connection As SqlConnection)
 Dim cmd as new SQLCommand  
 Dim reader As SqlDataReader
 cmd.Connection = connection
 'I'd recommend using a stored Procedure with TableName passed as parameter
 'But for sake of clarity I'll do it like this...
 cmd.CommandText ="SELECT TOP 1 * FROM " & TableName

 if connection.state <> ConnectionState.Open then
     connection.open
 end if
reader = cmd.ExecuteReader()
 Dim schemaTable As DataTable = reader.GetSchemaTable()
 For Each row In schemaTable.Rows
        For Each column In schemaTable.Columns
         Combo.items.add(Column.ColumnName)
        Next           
    Next
    reader.Close()    

End Sub

[satti]

G Waddell am trying this code but getting nothing out of it .

 Private Sub txt2_GotFocus(ByVal sender As Object, ByVal e As System.EventArgs) Handles txt2.GotFocus
        Try
            txt2.Items.Clear()
            myRead.myfill("SELECT * FROM(information_schema.columns) WHERE  table_name = 'Tb_test' ORDER  BY ordinal_position  ")
            Dim rc, lr As Integer
            lr = myRead.dt.Columns.Count - 1
            For rc = 0 To lr
                txt2.Items.Add(myRead.dt.Rows(rc).Item(0))
            Next
        Catch exc As Exception
            Exit Sub
        End Try
    End Sub

[G_Waddell]

Hi,
What is MyRead? a Dataset? an adaptor? a Reader? some sort of data layer class?

I don't think you need the brackets around the information_schema.columns in your query, I tried it on SQL 2008 R2 and got a syntax error.

Your results will bring back a lot of data, if you only want column name and ordinal postion then try Select ordinal_Position, COLUMN_NAME FROM information_schema.columns WHERE (table_name ='Tb_test' ORDER BY ordinal_position

Try and put more steps or error handling in your code to identify what has happened and debugg with step by step i.e. Instead of just going straight into your loop see if you have results returned from your query first...

I'll use the example I gave you first modified with the query you used:

try
    dim cmd as new sqlcommand()
    cmd.connection = Myconnection
    cmd.CommandText = "SELECT Ordinal_Position, COLUMN_NAME FROM " & _
            "'Tb_test' ORDER BY Ordinal_Position"

    'Check connection is open 
    If MyConnection.State <> ConnectionState.Open then
        MyConnection.Open
    End if

    dim Reader as SqlDataReader = cmd.ExecuteReader

    'Check we get a result
    If Reader.HasRows then
        'we have rows
        While Reader.Read()
            txt2.Items.Add(Reader.GetSqlString(1))
        End While
    else
        'put in for debug purpose you can remove if not necessary
        Messagebox ("No Data Returned")
    end if
    Reader.close
Catch ex as Exception
    'unhandled errors
    messagebox.show(ex.message)
end try

[satti]

G_Waddell i have created a myread dll which contain datasets dataadapter , connection string with database .its like

Public Class MyRead
    Public s As String = myfile(Environment.CurrentDirectory & "\servername.txt")
    Public s1 As String = myfile1(Environment.CurrentDirectory & "\dbname.txt")
    Public usertype As String
    Public f_type As String
    Public db As New FbConnection
    Public sql As New FbConnectionStringBuilder
    Public str As String
    Public cmd As New FbCommand
    Public ds As New DataSet
    Public dt As New DataTable
    Public da As New FbDataAdapter
    Public Company As String
    Public uid As String
    Public gds As New DataSet
    Public gda As New FbDataAdapter
    Public gdt As New DataTable
    Public ngda As New FbDataAdapter
    Public ngdt As New DataTable
    Public nngda As New FbDataAdapter
    Public nngdt As New DataTable
    Public sdate As Date
    Public edate As Date
    Public msg As String
    Public title As String
    Public style As MsgBoxStyle
    Public response As MsgBoxResult
    Public frmname As String
    Public ndt As New DataTable
    Public nda As New FbDataAdapter
    Public vdt As New DataTable
    Public vda As New FbDataAdapter
    Public frmname1 As String
    Public lastrow As Long

[satti]

All i need is to plot a graph btw 2 columns . is there any other way?

[ryanjayson]

to populate the combobox to columns table on database, try this:

    Imports System.Data.SqlClient
    Imports System.Data
Public Class Form1

    Private Sub Form1_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load
        connect()
    End Sub

    Private Sub connect()
        Dim CONNECTION_STRING As String = "Data Source=LOCALHOST;Initial Catalog=DB_NAME; Integrated Security=True"
        Dim conn As SqlConnection = New SqlConnection(CONNECTION_STRING)
        'Specify the query
        Dim cmd As SqlCommand = New SqlCommand("SELECT COLUMN_NAME FROM information_schema.columns  WHERE TABLE_NAME = 'TableName'", conn)
        conn.Open()
        Dim sdr As SqlDataReader = cmd.ExecuteReader

        'do the looping to add the items on comboboxes
        While sdr.Read()
            ComboBox1.Items.Add(sdr.Item("COLUMN_NAME").ToString)
            ComboBox2.Items.Add(sdr.Item("COLUMN_NAME").ToString)
        End While
        conn.Close()
    End Sub

    Private Sub Button1_Click(sender As System.Object, e As System.EventArgs) Handles Button1.Click
        'Add code for ploting based on the selected columns on the comboboxes
        xAxis = ComboBox1.Text()
        yAxis = ComboBox2.Text()
            ...Bla, bla, bla
    End Sub
End Class

I suggest add button for plotting the graph.
Hope it make sense.