Can't make data show up in a data grid view 1

[emaduddeen]

Hi Everyone,

Can you look at my code? Can you tell me why I can't display data in my data grid view?

The connection string comes directly from pasting the connection string from the server explorer and the SQL statement is from the query builder so I know those are ok.

Here's the code:

Imports System.Data
Imports System.Data.SqlClient

Public Class Form1
    Private strConnectString As String = "Data Source=.\SQLEXPRESS;" & _
    "AttachDbFilename=D:\Development\VB\Emad\cbt1\DataSetExample\DataSetExample\Baseball.mdf;" & _
    "Integrated Security=True;" & _
    "User Instance=True"

    Private objSqlConnection As New SqlConnection(strConnectString)
    Private objSqlDataAdapter As New SqlDataAdapter
    Private objDataSet As New DataSet
    Private objSqlCommand As New SqlCommand

    Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        With objSqlCommand
            .Connection = objSqlConnection
            .CommandText = "SELECT Positions.Role, Players.PlayerName, Players.Rank, Players.HireDate, " & _
                                  "Players.Sarary " & _
                             "FROM(Players) " & _
                            "INNER JOIN Positions ON Players.PositionCode = Positions.PositionID"
            .CommandType = CommandType.Text
        End With

        objSqlDataAdapter.SelectCommand = objSqlCommand
        objSqlConnection.Open()
        objSqlDataAdapter.Fill(objDataSet, "Players")
        objSqlConnection.Close()

        ' Show the data in the grid.
        '---------------------------
        With grdPlayers
            .DataSource = objDataSet
            .DataMember = "Players"
            .AutoGenerateColumns = True
        End With

        objDataSet = Nothing
        objSqlConnection = Nothing
        objSqlCommand = Nothing

    End Sub
End Class

The code is from a tutorial that should be working, but I believe I left something out that is missing.

Thanks.

Truly,
Emad

 

[Sorwen]

Quick look everything seems ok but this [red]objDataSet = Nothing[/red] don't set the DataSet to nothing as you are basically removing all of the data.

-I hate Microsoft!
-Forever and always forward.
-My kingdom for a edit button!

 

[jebenson]

Try removing this line:

objDataSet = Nothing



I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[emaduddeen]

Hi Everyone,

Thanks for the replies.

I commented out the = nothing lines but still no data shows up.

Truly,
Emad

 

[SgtJarrow]

I saw this:

SELECT Positions.Role, Players.PlayerName, Players.Rank, Players.HireDate, " & _
"Players.[red]Sarary[/red] "

It looks to me like you misspelled Salary. If so, your query may be failing and returning no records...

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB.NET Programmer

 

[emaduddeen]

Hi mstrmage1768,

Thanks for catching the misspelled column name but I checked the table and that's the actual name for the column.

The query is copied from the query builder which I tested by using Execute Query from the query builder.

Is it something wrong I did when setting up the data grid view?

Truly,
Emad

 

[SgtJarrow]

Nothing else I see but what sorwen and jebensen have already suggested...

=======================================
People think it must be fun to be a super genius, but they don't realize how hard it is to put up with all the idiots in the world. (Calvin from Calvin And Hobbs)

Robert L. Johnson III
CCNA, CCDA, MCSA, CNA, Net+, A+, CHDP
VB.NET Programmer

 

[emaduddeen]

Additional notes:

I stepped through the code with a break point on the objSqlConnection.Open() line and f11 executes the code but when it reaches the objSqlDataAdapter.Fill(objDataSet, "Players") the form is displayed with a blank data grid and the objSqlConnection.Close() line is never executed.

Looks like something may be wrong the my .Fill statement.

Truly,
Emad

 

[jebenson]

It might be the FROM clause, which in your code is "FROM(Players) "

Put a space between FROM and (Players) - "FROM (Players). Also, you don't really need the parentheses around the table name.

I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[emaduddeen]

Hi jebenson,

Good catch.

That was the problem.

Thanks so much.

Truly,
Emad