Using an sql statement in my datagridview

[msboddie]

I have a datagridview and a sql statement and I need to get the data to populate in the datagridview.

Query:
sqlStr = "SELECT Release,PRODWK,SHIPMON,COMPL,TGTSHIP,SCHPROD,AB,ARM,SHAFT,FIT,HDW,FIN,WELDCMP,DATE_REQ"
sqlStr += " FROM jobscopeMeyer.dbo.PPJOBM p "
sqlStr += " Left JOIN ("
sqlStr += " Select SearchKey As RELEASE_PU, "
sqlStr += " MAX(Case When DataItem = '27 PRODWK' THEN AlphaValue END) AS 'PRODWK',"
sqlStr += " MAX(CASE WHEN DataItem = '28 SHIPMON' THEN AlphaValue END) AS 'SHIPMON',"
sqlStr += " MAX(CASE WHEN DataItem = '30 %COMPL' THEN AlphaValue END) AS 'COMPL',"
sqlStr += " MAX(CASE WHEN DataItem = '31 TGTSHIP' THEN AlphaValue END) AS 'TGTSHIP',"
sqlStr += " MAX(CASE WHEN DataItem = '70 SCHPROD' THEN AlphaValue END) AS 'SCHPROD',"
sqlStr += " MAX(CASE WHEN DataItem = '81 AB%' THEN NumericValue END) AS 'AB',"
sqlStr += " MAX(CASE WHEN DataItem = '82 ARM%' THEN NumericValue END) AS 'ARM',"
sqlStr += " MAX(CASE WHEN DataItem = '83 SHAFT%' THEN NumericValue END) AS 'SHAFT',"
sqlStr += " MAX(CASE WHEN DataItem = '84 FIT%' THEN NumericValue END) AS 'FIT',"
sqlStr += " MAX(CASE WHEN DataItem = '85 HDW%' THEN NumericValue END) AS 'HDW',"
sqlStr += " MAX(CASE WHEN DataItem = '86 FIN%' THEN NumericValue END) AS 'FIN',"
sqlStr += " MAX(CASE WHEN DataItem = '87 WELDCMP' THEN AlphaValue END) AS 'WELDCMP'"
sqlStr += " From jobscopeMeyer.dbo.UserDefinedFields"
sqlStr += " GROUP BY SearchKey"
sqlStr += ") PU ON RELEASE_PU = RELEASE"
sqlStr += " Left JOIN ("
sqlStr += " Select RELEASE AS RELEASE_PJ, MAX(DATE_REQUESTED) AS DATE_REQ"
sqlStr += " From JobscopeMeyer.dbo.PPJOBD"
sqlStr += " WHERE DATE_REQUESTED NOT IN (0,99999999)"
sqlStr += " GROUP BY RELEASE"
sqlStr += " ) PJ ON RELEASE_PJ = RELEASE"
sqlStr += " where RELEASE = '" + strRelease + "'"

Here is the output from the query:



Here is what the Datagridview look like:

[wes4dbt]

What is the problem? Do you have a question?

[msboddie]

The problem I have is how do I put the data from the query in the Datagridview?

[.paul.]

What type of database is it?

[.paul.]

You need to post your code that runs the query if you have any. If you don’t, any advice depends on the type of database. It’s just a case of filling a DataTable, then binding to your dgv. Slightly more complex coding as you have added columns to your dgv at design time…

[jmcilhinney]

Populate a DataTable and bind it to the grid, preferably via a BindingSource. You can use a data adapter and call Fill on it or you can use a data reader and call Load on the DataTable. There are loads of examples around, which you should have been able to find for yourself with a simple web search. Now you're here, try following the CodeBank link in my signature below and check out my own examples in my thread on Retrieving & Saving Data.

[jmcilhinney]

You should also clean up that code to create the SQL. There's no reason to use all that string concatenation. Recent versions of VB support multiline String literals, e.g.

Dim sql = "SELECT Column1
                 ,Column2
                 ,Column3
           FROM MyTable"

In earlier versions, you could use an XML literal, e.g.

Dim sql = <sql>SELECT Column1
                     ,Column2
                     ,Column3
               FROM MyTable</sql>.Value

[msboddie]

what type of database is it?

mssql

[.paul.]

https://stackoverflow.com/questions/...fill-datatable

[jmcilhinney]

It really doesn't matter what the database is. The principle is exactly the same regardless. All that changes is the specific ADO.NET provider, the connection string and possibly the SQL syntax. You still call Fill on a data adapter or Load on a DataTable with a data reader regardless. I haven't yet but I'm willing to bet that if I do a web search with some obvious keywords, I'll find examples of exactly that.

...

Sure enough, I just searched the web for "vb.net populate datagridview from database" and the first two results included code examples of using a data adapter. It's not hard to find this stuff so any effort at all should yield results. If you have issues implementing what you find then that's something to post a question about, but at least look for yourself first.

[.paul.]

It really doesn't matter what the database is. The principle is exactly the same regardless. All that changes is the specific ADO.NET provider, the connection string and possibly the SQL syntax.

A few questions about the database reveal whether it’s going to be OLEDB, SQLDB, or even MySQLDB. But as you say, all of the methods are the same, but I might’ve saved us some work later

[jmcilhinney]

as you say, all of the methods are the same

It might be an OleDbDataAdapter or a SqlDataAdapter or a MySqlDataAdapter but it's still a data adapter and you use them all in exactly the same way, so someone should be able to find out for themselves that you use a data adapter and then ask specific questions if required. If you don't even know the generalities of ADO.NET then you haven't actually tried to work out the problem for yourself, but even more so, you haven't tried to learn a topic beforehand so that you don't encounter problems in the first place. In my "humble" opinion, sites like this one should be used to help with the stuff you can't work out for yourself, not the stuff you can't be bothered to work out. That type of approach never really helps anyone in anything but the shortest of terms. If you want to write software then you should be able to use a search engine. The search keywords I used in post #10 were all taken from the OP's own post #1. If you can type those words here, you can type them in a search engine and find a lot of the information for yourself. That said, working through a tutorial or two on data access before even attempting to write data access code is what the conscientious would do, so such a search would not be required.

I might’ve saved us some work later

That later work is really all that should have been needed, unless a more specific issue was encountered.