VB Net IBM AS400 Connection

[JesseH]

I need to read data from an AS400 (several tables), perform query selections (inner joints, outjoints, etc) and process the results (as a table) in VB.net.

I have been working on this issue for over six months. I am willing to pay for help. If you can do this, please contact me at hernandj@eim-co.com

Thanks

 

[jmcilhinney]

What exactly is the problem? Are you saying that you don't know how to make the connection to the database? You don't know how to retrieve the data? You don't have an appropriate database driver? Are you saying that you don't understand SQL? You don't know how to use ADO.NET? If you've been working on it for six months then you must have something to show for it. Give us an idea of what you have and what exactly is giving you issue.

 

[JesseH]

Issue

Reading files from the AS400 is not a problem. Joins using the SQL Wizard is not an issue. The issue is creating Data Sets that are a result of joins. For example, for inventory history records from xx/xx/xx to yy/yy/yy join with inventory master to add the product description. To the result of that join, join the Customer master record to add the Custome Name. Display the final joined data set in a grid box.

Thanks for the question.

 

[jmcilhinney]

Then this isn't a VB.NET question but rather an SQL question, which is not strictly within the scope of this forum. It doesn't matter how simple or complex your query is, executing a query and getting the results into a DataTable is done in exactly the same way. If you don't know how to write the SQL code for the query then that's something else entirely.

The best way to attack problems like this is to build them up one piece at a time. Start off with the date range:

SELECT * FROM InventoryHistory WHERE Date BETWEEN @StartDate AND @EndDate

Now join that the inventory to get the description:

SELECT
   ih.*,
   i.Description
FROM
   InventoryHistory ih INNER JOIN Inventory i
      ON ih.InventoryID = i.ID
WHERE
   ih.Date BETWEEN @StartDate AND @EndDate

Now join that with the customer to get the name:

SELECT
   ih.*,
   i.Description,
   c.Name
FROM
   (InventoryHistory ih INNER JOIN Inventory i ON ih.InventoryID = i.ID)
    INNER JOIN Customer c ON ih.CustomerID = c.ID
WHERE
   ih.Date BETWEEN @StartDate AND @EndDate

 

[jmcilhinney]

Actually, the last bit about displaying the data is a VB.NET question. By a "grid box" I assume that you mean a Windows.Forms.DataGrid. It's always best to use the proper names for controls, classes, etc. to avoid confusion. Once you have your data in a DataTable you simply assign that to the DataSource property of the grid. If you need more ADO.NET help then I suggest that you look up fellow member TechGnome and read the ADO.NET tutorials in his signature for a start.