• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 199
  • Last Modified:

Looking in two tables

I have a DB with 2 tables. Where I store the user information.

it's possible to look in one table, and see if the data is there, and if not look in other table?
0
Ennio
Asked:
Ennio
  • 7
  • 6
1 Solution
 
Jeff CertainCommented:
Yep... create a query to get you r data from table 1. If it returns nothing, then hit table 2.

e.g.
Dim myId as Integer
Dim conn As SqlConnection = New SqlConnection(EnterpriseObjects.EnterpriseApplication.Application.ConnectionString)
conn.Open()
'create a command...
Dim cmd As SqlCommand = New SqlCommand("SELECT ID FROM TABLE1", conn)
cmd.CommandType = CommandType.Text
'execute...
Dim objTemp As Object = cmd.ExecuteScalar
If IsNothing(objTemp) OrElse IsDBNull(objTemp) Then
  cmd = New SqlCommand("SELECT ID FROM TABLE2", conn)
  objTemp = cmd.ExecuteScalar
  If IsNothing(objTemp) OrElse IsDBNull(objTemp) Then
    myId = 0
  Else
    myId = Integer.Parse(objTemp)
  End If
Else
  myId = Integer.Parse(objTemp)
End If
'cleanup...
cmd.Dispose()
conn.Close()
0
 
Jeff CertainCommented:
On second thought, a better option would be to use UNION and do this in one query:

Dim thisUser as String
Dim strSQL as string ="SELECT UserName FROM Table1 WHERE UserName =" & myId & " UNION ALL SELECT UserId FROM Table2 WHERE UserId=" & myId
Dim conn As SqlConnection = New SqlConnection("your connection string")
conn.Open()
'create a command...
Dim cmd As SqlCommand = New SqlCommand(strSQL, conn)
cmd.CommandType = CommandType.Text
'execute...
Dim objTemp As Object = cmd.ExecuteScalar
If IsNothing(objTemp) OrElse IsDBNull(objTemp) Then
  thisUser = ""
Else
  thisUser = objTemp
End If
'cleanup...
cmd.Dispose()
conn.Close()
0
 
EnnioAuthor Commented:
I'm new to asp .net, and I'm using an ACCESS DB, where is my code with one select in the DB.

***************************************************************************
Sub btnSubmit_Click(sender As Object, e As EventArgs)
      If Page.IsValid Then
            Dim myConn As OleDbConnection
            Dim myComm As OleDbCommand
            myConn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source = " & Server.MapPath("/database/babysitter.mdb") & ";")
            myConn.Open()      
            myComm = New OleDbCommand("Select * from tbl_babysitter where email='" & txtuname.text & "' and passwd='" & txtpwd.Text & "'", myConn)
            If (myComm.ExecuteReader().HasRows() ) then
                  myConn.Close()
                  FormsAuthentication.RedirectFromLoginPage(txtuname.Text, True)
            Else
                  myConn.Close()
                  Label2.Text = "Invalid Username/Password"
            End If
      Else
            Label2.Text = "You must enter the required fields"
      End If
End Sub
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
Jeff CertainCommented:
Well... if you can give me some information about the second table, I might be able to help. ;)

As a general rule, if you're only checking for the existence of a record, you can use ExecuteScalar instead of a DataReader or DataAdapter.

For a single table:
Dim thisUser as String
Dim strSQL as string ="Select email from tbl_babysitter where email='" & txtuname.text & "' and passwd='" & txtpwd.Text & "'"
Dim conn As  New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source = " & Server.MapPath("/database/babysitter.mdb") & ";")
conn.Open()
'create a command...
Dim cmd As New OleDbCommand(strSQL, conn)
cmd.CommandType = CommandType.Text
'execute...
Dim objTemp As Object = cmd.ExecuteScalar
If IsNothing(objTemp) OrElse IsDBNull(objTemp) Then
  Label2.Text = "Invalid Username/Password"
Else
  FormsAuthentication.RedirectFromLoginPage(txtuname.Text, True)
End If
'cleanup...
cmd.Dispose()
conn.Close()
0
 
EnnioAuthor Commented:
sorry... the second table name is tbl_parents
0
 
Jeff CertainCommented:
do the parents have an email field as well? If so...

Dim thisUser as String
Dim strSQL as string ="Select email from tbl_babysitter where email='" & txtuname.text & "' and passwd='" & txtpwd.Text & "' UNION ALL Select email from tbl_parents where email='" & txtuname.text & "' and passwd='" & txtpwd.Text & "'"
Dim conn As  New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; Data Source = " & Server.MapPath("/database/babysitter.mdb") & ";")
conn.Open()
'create a command...
Dim cmd As New OleDbCommand(strSQL, conn)
cmd.CommandType = CommandType.Text
'execute...
Dim objTemp As Object = cmd.ExecuteScalar
If IsNothing(objTemp) OrElse IsDBNull(objTemp) Then
  Label2.Text = "Invalid Username/Password"
Else
  FormsAuthentication.RedirectFromLoginPage(txtuname.Text, True)
End If
'cleanup...
cmd.Dispose()
conn.Close()

FWIW: you really ought to have this data stored in a single table, with a flag to indicate whether the individual is a parent or babysitter.... it will make your table maintenance far easier.
0
 
EnnioAuthor Commented:
I did in 2 tables because they have different information, do you think it's better to have a login table ?
0
 
Jeff CertainCommented:
Well... if the information is different, then leave them separate. But if it is virtually identical, then merge them...

All I could see were the email and password info, and surmised that the remainder of the fields would be similar.
0
 
EnnioAuthor Commented:
They are very similar, like name address, But I'm storing other information that the parents table has, and the babysitter table doesn't.
0
 
Jeff CertainCommented:
Well... often the approach taken is to store all common data (name, email, phone #, address, etc) in a single table. You would then add a table for the babysitter-specific information and a second table for the parent-specific information. Whenever you needed specific information, you could use a JOIN operation to join the tables (INNER JOIN will return only rows that match between the tables) on some kind of unique UserId from the first table.

This way, all your login information and contact information is in a single table.
0
 
EnnioAuthor Commented:
Ok I got, I will make some changes on my DB. Thank You
0
 
Jeff CertainCommented:
Hey.... did the code above work? ;)
0
 
EnnioAuthor Commented:
yes it worked.
0

Featured Post

Free Tool: Port Scanner

Check which ports are open to the outside world. Helps make sure that your firewall rules are working as intended.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

  • 7
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now