VB - Find record button (help plz)

R

renvilo

Well-Known Member
Joined
Jan 28, 2008
Messages
485
Reaction score
0
Hi Guys,

I’m not a professional programmer so maybe you can help me out please…

I am using Visual Studio 2005, Visual Basic and a SQL 2005 Database. Everything seems to be going ok but I do need 2 things.

1. How do I “Find” a record. Let’s say there’s 2000 people in the database, how can I call it up within my program if I need to change the telephone number

2. This one might be tricky to explain. I have a table “DemographicInfo” and a table “sex” (the “sex” table is kinda where it can do a lookup. “sex” has the value’s of “Male, Female, Unknown”)

Now on my Form I have a dropdown list. How do I set it to do a lookup from that “sex” table and put it into the “demographic table?”


I hope everything is clear. If not please let me know.

Thanks in advance
 
Hmm sounds to me like you are needing to look into/learn SQL.

Do you know about SELECT, UPDATE type SQL statements?

How did you populate your DB in the first place, Copy/Paste?
 
I have a SQL 2005 database that I've created. My program can do Inserts/Updates/Delete

I want a button "Find Patient" and if you click it it should ask you what the "HospitalNumber" or "CompanyNumber" is.

Then It will bring up that record and you can make changes...



Some Code:



Private Sub frmNewPatient_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

'TODO: This line of code loads data into the 'TBDataSet.TblTBDemographicInfo' table. You can move, or remove it, as needed.
Me.TblTBDemographicInfoTableAdapter.Fill(Me.TBDataSet.TblTBDemographicInfo)

End Sub
 
I'm not sure how you want to implement this exactly. Do you want a datagrid with only the selected record? Or are you creating a custom form with all the fields...

Either way you'll need to SELECT the required record from tblPatient

Maybe something along the lines of...
Code: 
        using System.Data.SqlClient;
        using System.Data;

        Dim strDBConnection As String = "server=(local);database=DatabaseName;user id=UserName;password=Pwd;connection reset=false;connection lifetime=5;Trusted_Connection=Yes;"
        Dim dbConnection As SqlConnection
        dbConnection = New SqlConnection(strDBConnection)

        'Assume text boxs txtHospitalNumber" and "txtCompanyNumber on some form
        Dim strSelectSql As String = "Select * from [DatabaseName].[OwnerName].tblPatient WHERE HospitalNumber = '" & txtHospitalNumber.Text & "' OR CompanyNumber = '" & txtCompanyNumber.Text & "';"

        'Open the connection
        dbConnection.Open()

        'Create a command 
        Dim selectSqlCommand As SqlCommand = New SqlCommand(strSelectSql, dbConnection)
        Dim sqlData As SqlDataAdapter = New SqlDataAdapter(selectSqlCommand)
        Dim dsSelectData = New DataSet()
        sqlData.Fill(dsSelectData)
 
Yes I'm creating a custom form with text boxes and so on...

So If I search for a record it has to populate in the text boxes...

My current connection to the database is with Databinding and a dataset.
 
I assume you already have one big dataset with the entire tblPatient inside it.

You could create a DataView on that DataTable inside your DataSet.

Something like:
Code: 
Dim myFilter As String
myFilter = "HospitalNumber = '" & txtHospitalNumber.Text & "' OR CompanyNumber = '" & txtCompanyNumber.Text & "'"
Dim myDataView as New DataView = (<DataTable of tblPatient>, myFilter, "PatientNumber",
                  DataViewRowState.CurrentRows);
 
Man I like what you saying... Just don't know what you saying :-)

Sorry for being a bit stupid on my side... I do make out about half of what you said... But I'm getting confused with the:

"You could create a DataView on that DataTable inside your DataSet."
 
renvilo said:
"You could create a DataView on that DataTable inside your DataSet."
Click to expand...

You previously set you were using a DataSet. DataSets can store multiple tables. Each one of there is of type DataTable.

Look at the field DataSet.Tables (this is the table collection) inside the DataSet...

Hope I'm making sense and not just talking nonsense
 
Write custom objects strictly typed to your tables, using CRUD methodologies, and then you dont need to use those nasty datasets, datatables :)
 
Well I tried something... I created a query in my Dataset... Just trying to test it... Or am I wasting my time...

ps:semaphore... I'm not doing programming for a living so I don't know a lot about it.... But I'm thinking of going that route :-)
 
if you make custom objects its a hell of alot easier to maintain, the plumbing will take a couple of hours to get done, but after that interaction with the objects will be simple.

like.

Dim Patients as new Core.PatientsCollection()

Patients.Sort.Add("PatientName", Sort.Order.Desc")
Patients.Sort.Sort()

foreach(P as Core.Patient in Patients)
(
P.PatientName

Dim Hospital as Core.Hospital(P.HospitalId)
Hospital. watever

)

sorry for the psuedo code, i havnt written in vb in years:P
 
+1 to sem's solution. Writing proper Business Objects is the way to go. It will teach you the use and value of object orientation as well and, if implemented properly, it will make updates to your UI a breeze. You can even migrate from a Windows Forms-based application to a Web Application with minimal changes, because your Data and Business Logic tiers will remain unchanged.
 
Here is an example of a Business logic file from one of my apps i wrote last year

namespace BusinessLayer.BusinessComponents.BusinessHours
{
/// <summary>
/// Business Hours Core Class, used to determine date differences based on open and closed times per user.
/// </summary>
public class BusinessHours
{
public object CalculateBusinessMinutes(DateTime StartDate, DateTime EndDate, int userId)
{
BusinessHourCore.BusinessHours BusinessHours = new BusinessHourCore.BusinessHours();
object ret = null;
BusinessEntities.Users.Billing.BillingCollection BillingCollection = new BusinessLayer.BusinessEntities.Users.Billing.BillingCollection(userId, -1, 1);
foreach (BusinessEntities.Users.Billing.Billing Billing in BillingCollection)
{
if (Billing.Day == StartDate.DayOfWeek && Billing.Open)
{
ret = BusinessHours.DetermineBusinessHours((object)StartDate.ToString(), (object)EndDate.ToString(), (object)Billing.OpeningTime.ToString(), (object)Billing.ClosingTime.ToString(), "");
}
}
return ret;
}
}
}
Click to expand...

crappy formatting.
 
Last edited:
You guys don't have any jobs there for a junior guy coz I'll love to learn from you...

Hi guys, ok... I've added a query to my dataset. Looks like it's working and can't believe how easy it actually is, lol...

I'm not a Pro so I'm trying to keep it simple... They had a access database and I'm just putting it into SQL but I want to make a nice front end (not access again.)

if it was for work only access was fine but it's for my 1 subject at Unisa as well, lol... So I need to make it in a programming language... I'm trying to schedule a date with Bytes Technology to do a few short courses there... Not bad plus I'm not paying for it then.

That issue is solved after a few hours now.

Can you guys tell me what I can do about my Nr. 2 Problem?
 
2. This one might be tricky to explain. I have a table “DemographicInfo” and a table “sex” (the “sex” table is kinda where it can do a lookup. “sex” has the value’s of “Male, Female, Unknown”)

Now on my Form I have a dropdown list. How do I set it to do a lookup from that “sex” table and put it into the “demographic table?”
Click to expand...

Could you maybe post this table structure (Columns etc) so we can see how they fit together.
 
Well I'll try:

Table: TblDemographicInfo

IdNo (Autonumber)
HospID
CompanyNo
Surname
Name
Sex
CaptureDate


Table: TblSex

Male
Female
Unknown



Now in Access I did setup a front end to do a lookup in the Demographic sex field but the lookup will come from the TblSex

Is this also possible in VS 2005 using VB?
 
I think I get what you are trying to do.

Since you seen sorted with DataSets, you could bind a DataSet to a ComboBox to get a drop down of available options in tblGender is a better name :p

tblGender:
|Gender|
Male
Female
Unknown

Code: 
myComboBox.DataSource = myGenderDataSet;
myComboBox.DataTextField = "Gender";
myComboBox.DataValueField = "Gender";
myComboBox.DataBind();

Then the combobox will be filled with all the records from tblGender.
 
Well thanks man...

Just got it right.. lol... But very close to what you said there... Think your should also have worked... I owe you a beer or 2 :-D

Thanks again... My day is over for now and I'm really doing good on the program. Enjoy
 
You must log in or register to reply here.
Back
Top
Sign up to the MyBroadband newsletter
X