Retrieve data from fields in MS access DB

[themouse]

Hello Everybody,

I am new to this forum and although I am a novice with VB, step by step I am leraning the basic concepts of programming with VB.

I need help... obviously. I have create a query with the tableadapter in my VB app for a login form:


Dim login = Me.UsernamePasswordTableAdapter1.LogInCheck(TextBox1.Text, TextBox2.Text)

If login Is Nothing Then
MsgBox("Login Incorrect", MsgBoxStyle.Exclamation)
TextBox1.Clear()
TextBox2.Clear()
Else
Me.Hide()
Form2.Show()

End If

Everything works fine so I went on with my project. I have created a form to retrieve user and pw. With the following script I have been able to create what is necessary to send an email to an address filled by a user through a text field . Perfect even for this, everything works fine (codes are partials and my intent was just to give you a rough idea of the code I have been using):


ProgressBar1.Value = 10
Dim mail As New MailMessage()
ProgressBar1.Value = 17
smtpServer.Credentials = New Net.NetworkCredential("xxxx@gmail.com", "password")
ProgressBar1.Value = 23
'using gmail
smtpServer.Port = 587
ProgressBar1.Value = 27
smtpServer.Host = "smtp.gmail.com"
ProgressBar1.Value = 40
smtpServer.EnableSsl = True
mail = New MailMessage()
mail.From = New MailAddress("xxxx@gmail.com")
mail.To.Add("xxxx@gmail.com")
mail.Subject = "test"
mail.Body = "test"
smtpServer.Send(mail)
ProgressBar1.Value = 100
MsgBox("mail Sent!")
ProgressBar1.Value = 0
TextBox1.Clear()
....


Now the problem is that what I need to do is not exactly to send an email to an address typed by user. In fact, what I really need is the following:

1. User enters an email address in a text field and press a button -- done works OK

2. Through tableadapter query it checks if the email address is in the DB, if not it returns with an error (same concepts as for user and pw check) -- done works OK

3. The problem is after having checked if the email address exists in the DB, I need it to read and copy the data in the fields beside the email address (fields named username and password) and paste them let's say in a label (label1.text). What I need to know is the code to do this. Could you please help me with this...?

Sorry for the long post but I hope at least what I am looking for is clear


Thanks for your help

 

[cjard]

OK, you seem familiar with tableadapters etc so these instructions should make sense:

In Server Explorer, expand the tables inside your DB
Drag the table containing the emails, to the dataset
A tableadapter with associated datatable should appear
Edit the properties of the tableadapter
..edit the SelectCommand to be something like:

SELECT * FROM userdata WHERE emailAddress LIKE ?

(userdata is the table, emailaddress is the column)

You may see a "Do you want to update the other commands to reflect the main query?" - it doesnt really matter what you put, but saying No will be faster

Change the name of the Fill/GetData methods to be FillByEmailAddress/GetDataByEmailAddress

-

In your code, you either have a dataset on your form already, in which case you'll say:

userDataTableAdapter.FillByEmailAddress(Me.datasetName.UserData, "user@gmail.com")
If Me.datasetName.UserData.Count = 0 Then
  MessageBox.Show("Email address not recognised")
Else
  'Me.datasetName.UserData(0).XXX are properties that will get the data "along side" the email you searched
End If

If you havent got a dataset on your form that you can fill the datatable of:

Dim dt as UserDataDatable = userDataTableAdapter.GetDataByEmailAddress("user@gmail.com")

This will make the TA download the data, put it into a new datatable that you can interact with:

dt.Count 'the number of rows
dt(0).XXX 'access the row zero (first row) named properties.

Note that dt(0) might be dt.Rows(0) in VB.. In c# dt[0] works for sure, but I dont know whether the same is true of vb

 

[themouse]

Thanks CJard your solution really makes sense to me.

I will try it as soon as I can and I'll let you know.

Most likely I will bother you again

Thanks