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
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
Edited by satti
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
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
All i need is to plot a graph btw 2 columns . is there any other way?
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.
Im about to have my big brake and i'm gonna ask you about creating a music app like spotify? Do you know where to start from?
Please see my post vbScript - The Basics for more details on vbScript.
So far I've posted either functions/subs or code ...