Extracting Data from SQL to VB (Part 2)
I am trying to populate a list by getting data from a database via sql. I have asked this question before, but I used a different configuration and the one I am currently using does not give any results.
extracting data to VB from SQL
This is my old post. I will now provide the code for a newer version of my attempt.
Imports System.Data.Sql
Imports System.Data.SqlClient
Public Class Form1
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Dim conn As New SqlConnection
conn.Open()
Dim comm As New SqlCommand("SELECT name FROM Table_1", conn)
Dim reader As SqlDataReader = comm.ExecuteReader
Dim dt As New DataTable
dt.Load(reader)
ListBox1.Items.Add(dt)
End Sub
End Class
If anyone would like to help me, I would really appreciate it. If possible, take a hands-on approach trying to enlighten me as this works best.
change 1
Imports System.Data.Sql
Imports System.Data.SqlClient
Public Class Form1
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Dim connString As String = "Data Source=THE_SHOGUNATE\SQLEXPRESS;Initial Catalog=le_database;Integrated Security=True"
Dim conn As New SqlConnection(connString)
conn.Open()
Dim comm As New SqlCommand("SELECT name FROM Table_1", conn)
Dim reader As SqlDataReader = comm.ExecuteReader
Dim dt As New DataTable
dt.Load(reader)
ListBox1.DataSource = dt
End Sub
End Class
With this code, the list is populated with 6 instances of "System.Data.DataRowView" rows, 6 with the number of items in my table. How do I get the actual values?
source to share
You missed connectionString
If you want to populate a list from DB, there are many ways
With DataReader
Imports System.Data.Sql
Imports System.Data.SqlClient
Public Class Form1
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Dim connectionString As String = "Data Sourec=localhost;........."
Dim conn As New SqlConnection(connectionString)
conn.Open()
Dim comm As New SqlCommand("SELECT name FROM Table_1", conn)
Dim reader As SqlDataReader = comm.ExecuteReader
/* As it is not working i commented this
listBox1.ItemsSource = dt; // use this instead of ListBox1.Items.Add(dt)
//because Add event add only one item in the list.
*/
Dim i As Integer
i=0
while reader.read()
listbox1.Items.Add(dr(i).ToString);
i++
End While
End Sub
End Class
With DataTable
Imports System.Data.Sql
Imports System.Data.SqlClient
Public Class Form1
Private Sub Form1_Load(sender As Object, e As EventArgs) Handles MyBase.Load
Dim connectionString As String = "Data Sourec=localhost;........."
Dim conn As New SqlConnection(connectionString)
conn.Open()
// Create new DataAdapter
SqlDataAdapter a = new SqlDataAdapter("SELECT * FROM EmployeeIDs", c)
// Use DataAdapter to fill DataTable
DataTable dt = new DataTable();
a.Fill(dt);
ListBox1.DataSource = dt;
ListBox1.DataTextField = "name";
End Sub
End Class
EDIT:
Other connection string options depend on your security and all of that. You should see this link Connection Strings for SQL Server 2008
source to share
The last solution I saw should work, but there are some important guidelines to keep in mind regarding SQL Server.
1) Avoid selecting * whenever possible, explicitly list your columns instead. Select * forces SQL to do extra work if you don't intend to pull out all the columns in the table. This is also not future proof, as dba may add a VARBINARY (MAX) column in the future and fill it with a blob value record. This script will make your request as written significantly slower and unnecessary.
2) Don't forget to close your SQLConnection when you're done with it. This will free up the connection and SQL resources.
if (cn.State != ConnectionState.Closed)
cn.Close();
Another interesting trick is the use of the USING directive, which will dispose of the SqlConnection object when execution goes out of scope.
using (SqlConnection cn = new SqlConnection(sConnectionString))
{
if (cn.State != ConnectionState.Open)
cn.Open();
// add query code here.
if (cn.State != ConnectionState.Closed)
cn.Close();
}
-
don't forget to close your SqlDataReader after your read loop finishes.
if (! dr.IsClosed) dr.Close ();
Hope this helps.
Andre Ranieri
source to share