Data not showing up in DataGrid

[bajanpoet]

Hi guys! Long time no see!

I'm trying to get a datagrid to work, but although when I run it there is no error, nothing appears on the datagrid. Yet, there seems nothing wrong with my code:

SQL = "select * from upload " & _
                      "where field003 like 'P'+'%'+'" & PONo & "' " & _
                      "and field009 like 'Being received' order by field003"

                'Set up connections
                Dim SQLCommand = New SqlCommand(SQL, conn)
                conn.Open()
                Dim da As SqlDataAdapter = New SqlDataAdapter(SQLCommand)
                Dim ds As New DataSet

                da.Fill(ds, SQL)

                'Attach dataset to datagrid
                dgBlowOut.DataSource = da

Thank you for your help:)

[dickersonka]

If you are using asp.net you need to use bind

[bajanpoet]

Not using asp.net. I'm creating a small desktop application.

[dickersonka]

try setting the datamember
The dataset can have multiple tables, when you set the datasource you need to specify which table to use

[bajanpoet]

Setting the datamember to the name of the table?

dgBlowOut.DataMember = "upload" [\code]

When I added that it gave me an error: 'Child list for field upload cannot be created'[code=VB.NET] dgBlowOut.DataMember = "upload"
[\code]

When I added that it gave me an error: 'Child list for field upload cannot be created'

[dickersonka]

The dataset table, not the sql table

Dim ds As New DataSet("uploads")

....

 dgBlowOut.DataMember = "uploads"

[bajanpoet]

I have also realized that in trying to run this application, after a while this error appears:
"An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this error may be caused by the fact that under the default settings SQL Server does not allow remote connections."

I had never seen this error before this time because when I clicked the View button and it did not change the datagrid, I would have closed it immediately..... it came up after about 4 seconds.

Does this help anyone to assist me?

[bajanpoet]

the Dataset is using a raw SQL statement that is being stored in a string variable SQL -

SQL = "select field003 as 'PO #',field009 as 'Status' from upload " & _
                      "where field003 like 'P'+'%'+'" & PONo & "' " & _
                      "and field009 like 'Being received' order by field003"
.....

I don't understand what the dataset table is...

[bajanpoet]

One more piece of info: Here's the connection string (As the issue seems to be a connection one.

Public Const ConnectionString = "Data Source=SBIDB2SVR;Initial Catalog=rbeacon;User Id=sbiapps;Password=sbiapps123;" [\code][code=VB.NET]
Public Const ConnectionString = "Data Source=SBIDB2SVR;Initial Catalog=rbeacon;User Id=sbiapps;Password=sbiapps123;"
[\code]

[dickersonka]

As long as you are sure the the datasource is correct and you can 'ping' the server then you need to configure the sql server to allow tcp ip connections.

Here's some links to show you how
http://www.datamasker.com/SSE2005_SCM1.htm

http://www.datamasker.com/SSE2005_SAC.htm

[bajanpoet]

ok, I'm a little confused as to why it is talking about SQL Server 2005 in the first place; I'm using SQL Server 2000.... I went looking around for where tcp ip connections are either enabled or disabled - and I found it, but it's already enabled for tcp ip!

[dickersonka]

Hmmm. Something is up with that. Are you running SQL 2000 and SQL 2005 on that machine then?

[bajanpoet]

The server is SQL 2000...I am using Visual Basic 2005 Express to code the application. That's the only 2005 thing on my PC.

[bajanpoet]

I have changed the connection string username and password and the connection seems to now be working - but now I'm back to the original issue: I can't get the datagrid populated from records using the SQL SELECT statement.

SQL = "select field003 as 'PO #',field009 as 'Status' from upload " & _
                      "where field003 like 'P'+'%'+'" & PONo & "' " & _
                      "and field009 like 'Being received' order by field003"

   'Set up connections
   Dim SQLCommand = New SqlCommand(SQL, conn)
   conn.Open()
   Dim da As SqlDataAdapter = New SqlDataAdapter(SQLCommand)
   Dim ds As New DataSet

   da.Fill(ds, SQL)

   'Attach dataset to datagrid
   dgBlowOut.DataSource = da

When I run it and enter a valid PO number, no records are displayed, but I can see the records when I run the SQL locally using Query Analyzer on my machine.

[dickersonka]

remember the datamember

Dim ds As New DataSet("uploads")

....

 dgBlowOut.DataMember = "uploads"

[bajanpoet]

Ok so now I'm trying to understand the Datamember... is the DataSet("Uploads") the NAME of a Dataset? As in that code creates a dataset with a name?

[bajanpoet]

Ok I have created a dataset name Uploads and set the datamember using the code given...

Dim ds As New DataSet("Uploads")
....
dgBlowOut.DataMember = "Uploads"

and now I get this error

"Child list for field Uploads cannot be created."

Ok so now I'm trying to understand the Datamember... is the DataSet("Uploads") the NAME of a Dataset? As in that code creates a dataset with a name?

[dickersonka]

Yes, that is the name of dataset. The problem seems to be that it doesn't know what table to use.
The dataset 'can' have multiple tables, and the datagrid doesn't know what to get its records from.

Check out these links for more clarification.

http://msdn.microsoft.com/en-us/library/system.windows.forms.datagrid.datasource.aspx

http://msdn.microsoft.com/en-us/library/ms810286.aspx

[dickersonka]

You might be able to do it generically and set

dgBlowOut.DataMember = ds.Tables[0].TableName

[bajanpoet]

You might be able to do it generically and set

dgBlowOut.DataMember = ds.Tables[0].TableName

When I tried that I got this error: value of type 'System.Data.DataTableConnection' cannot be converted to string.

[dickersonka]

I don't think its DataTableConnection. TableName and DataMember are both string types, make sure you have '.TableName' at the end

[bajanpoet]

I don't think its DataTableConnection. TableName and DataMember are both string types, make sure you have '.TableName' at the end

I do. Here's the code I put in:

dgBlowOut.DataMember = ds.Tables[0].TableName

There is a blue line under ds.Tables and another one under [0] Putting my mouse over the ds.Tables gives the 'Value of type ... ' error, putting the mouse under the 0 gives the error 'identifier expected'

[destino]

Hi guys! Long time no see!

I'm trying to get a datagrid to work, but although when I run it there is no error, nothing appears on the datagrid. Yet, there seems nothing wrong with my code:

SQL = "select * from upload " & _
                      "where field003 like 'P'+'%'+'" & PONo & "' " & _
                      "and field009 like 'Being received' order by field003"

                'Set up connections
                Dim SQLCommand = New SqlCommand(SQL, conn)
                conn.Open()
                Dim da As SqlDataAdapter = New SqlDataAdapter(SQLCommand)
                Dim ds As New DataSet

                da.Fill(ds, SQL)

                'Attach dataset to datagrid
                dgBlowOut.DataSource = da

Thank you for your help:)

You say that the app works with no errors but there is no data in the datagrid. Have you considered that your query may not be returning any data. If the underlying data does not match the query. Everything may be working correctly.

Try "SELECT * from upload" as a test. Alternatively run the query in access or enterprise manager

[bajanpoet]

You say that the app works with no errors but there is no data in the datagrid. Have you considered that your query may not be returning any data. If the underlying data does not match the query. Everything may be working correctly.

Try "SELECT * from upload" as a test. Alternatively run the query in access or enterprise manager

Thanks for the suggestion, Destino....
It's been working in Query Analyzer - I know the query brings results, as it is a business script that is used to select and delete purchase orders from time to time as requested by the Inventory department. The same script that does not show results in the datagrid shows results when I run it in Query Analyzer with the same input.