Dataset's - datagridview help

[Tony M]

VS 2005 - vb .net - windows xp pro - windows app

All I'm trying to do for now is populate a datagridveiw (dgvTest).
I trying to learn how to retrieve records from DB and add, edit, delete.
I know there are records in the DB b/c I'm able to display records using the
control itself.
Now I want to do it dynamically. I'm really trying to retrieve records from
DB and manipulate them and update them. After I get this to run I want to
use a datareader and learn how that works.

dgvTest is empty when I run this code. I don't know how to bind the data or
if that is even the problem. The code runs w/o error.

Dim SqlCon As SqlConnection
Dim SQL As String
Dim SqlCmd As New SqlCommand
Dim SqlAdpt As New SqlDataAdapter
Dim SqlDS As New DataSet
SqlCon = New SqlConnection("Server=.\SQLExpress;AttachDbFilename=C:\Program
Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\MyFirstDatabase.mdf;
Database=dbname;Trusted_Connection=Yes;")
Try
SQL = "Select * from TonysTable2"
SqlAdpt = New SqlDataAdapter(SQL, SqlCon)
SqlAdpt.SelectCommand.Connection.Open()
SqlAdpt.Fill(SqlDS, "TonysTable2")
dgvTest.DataSource = SqlDS.DefaultViewManager
SqlCon.Close()
SqlCon = Nothing
MsgBox("Whoo hooo connected")
Catch ex As Exception
If SqlCon.State Then
SqlCon.Close()
SqlCon = Nothing
End If
MsgBox(ex.ToString)
End Try
End Sub

Thanks

 

[Rich P]

For starters - if you have sql server express running on your
workstation - I would reference the server (by name) in the connection
string and only the name of the database - not the physical mdf file.
At least with the full version of sql server you don't reference the
physical file. Try it as follows and see.

conn1.ConnectionString = "Data Source=serverName;Initial
Catalog=DBname;Integrated Security=True"

Here is a sample how to connect to/read data/display it in a
datagridview control:

Imports System
Imports System.Data.SqlClient

Private Sub GetData()
Dim conn1 As New SqlConnection, da As New SqlDataAdapter
Dim ds As New Dataset

conn1.ConnectionString = "Data Source=serverName;Initial
Catalog=DBname;Integrated Security=True"

da.SelectCommand = New SqlCommand
da.SelectCommand.Connection = conn1
da.SelectCommand.CommandText = "Select * From TonysTable2"
da.Fill(ds, "tbl1")
Datagridview1.Datasource = ds.Tables("tbl1")

End Sub

the line da.Fill(ds, "tbl1") will automatically create a copy of the
table structure of TonysTable2 and load whatever data you select in your
select statement. You can name this table anything you want. It does
not have to be the same name as the server table (or it could be -
doesn't matter). But whatever you name it - that name has to follow in
all the code which references your dataset ds.



Rich