learning VB 2010 -- need help accessing a file on 400.

[Marzuce]

Hello and hope someone can point me in the right direction. I've searched both forums and using google to get some insight on what I am trying to do however I either find something alittle above my knowledge over VB as I am learning or I find sites with many broken links or well just a dead end.

I've programmed with RPG for 17 years now in hospital IT and have my head wrapped around that very well. I am wanting to learn Visual Basic now and later C#. But each step as they come.

Right now I want some direction on a simple program. Or at least coded example that I can learn and expand on. What I want done can easily be handled with query/400 or even MS Access using a ODBC connection however I am doing this with visual basic as a stand alone (not VBA) so that while I code and process this, it'll teach me along the way so that I will have a better understanding to expand further.

I am using Visual Studio 2010 Visual Basic Express.

I have a file on the 400, just going to call this xrpat# with fields old_patno and new_patno. Old 12character, the new is 7 numeric. So a client could have original number of ABC123456789 and the account now is 0000025 as one entry as example.

What I want to do is have a form that prompts for user id, password and system name. One that is entered in, a 2nd form will load asking for old patient number and a text box requesting the information then another label "new patient number" and a read only text box that will display what we pull from the as400 file.


frmLogin is the one that prompts for user info. frmSearch is the one prompting the searches. Below is the code I have so far. I can tell it does connect to the as400 as when I test with my profile information the status in my window just shows ready. If I key a incorrect user id or password it bombs out due to no error checking currently.


Public Class frmSearch

Dim con As ADODB.Connection
Dim rst As ADODB.Recordset

Dim strConnection As String
Dim strSQLstring As String



Private Sub btnSearch_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnSearch.Click

con = New ADODB.Connection
rst = New ADODB.Recordset

strConnection = "provider=IBMDA400; " & "Data Source=" & frmLogin.txtSystem.Text & ";" & _
"User ID=" & frmLogin.txtUserID.Text & ";" & "Password=" & frmLogin.txtPassword.Text & ";"

con.Open(strConnection)



This is where I am at a loss. Don't exactly know how I can sql/query the file with VB to select TESTLIB/XRPAT# new_patno where old_patno = txtSearchPatno.text <--- text box where the old patient number is entered into the form.


Any ideas? Think once I break this hurdle I will be able to expand and continue to learn new tricks

Thanks

[BillB]

You need to assign the open connection to the record set. Then you may execute the SQL select to retrieve the record(s). We often put the SQL in a string variable.
Note that your connection may use the name format that uses the dot "." instead of the "/" as the library file separator.
rst = con
sql = "SELECT new_patno FROM TESTLIB.XRPAT# WHERE old_patno = " & txtSearchPatno.text
rst.Open sql
If rst.EOF = False Then
do something
rst.close
End If

OR

rst.Open "SELECT new_patno FROM TESTLIB.XRPAT# WHERE old_patno = " & txtSearchPatno.text