Read from multiple tables in vb.net data reader
I'm trying to read from two tables in mysql:
Dim sqlcom As MySqlCommand = New MySqlCommand("Select * from mother, father where IDNO= '" & TextBox14.Text & "' ", sqlcon)
-But I get this error:
Column 'IDNO' in where clause is ambiguous
Here is the whole code:
Dim NoAcc As String
Dim NoAccmod2 As String
Dim NoPas As String
Dim sqlcon As New MySqlConnection("Server=localhost; Database=school;Uid=root;Pwd=nitoryolai123$%^;")
Dim sqlcom As MySqlCommand = New MySqlCommand("Select * from mother, father where IDNO= '" & TextBox14.Text & "' ", sqlcon)
sqlcon.Open()
Dim rdr As MySqlDataReader
rdr = sqlcom.ExecuteReader
If rdr.HasRows Then
rdr.Read()
NoAcc = rdr("IDNO")
If (TextBox14.Text = NoAcc) Then TextBox7.Text = rdr("MOTHE开发者_运维知识库R")
If (TextBox14.Text = NoAcc) Then TextBox8.Text = rdr("MOTHER_OCCUPATION")
If (TextBox14.Text = NoAcc) Then TextBox10.Text = rdr("FATHER")
If (TextBox14.Text = NoAcc) Then TextBox11.Text = rdr("FATHER_OCCUPATION")
End If
-Any suggestions that could help solve this problem? Or even other techniques on achieving the goal of reading data from two tables using data reader?
This is a winform, not a web form
Without seeing the schema of your tables I can't say for certain, but I'd guess your ID columns are named the same in both tables. To get around that, you're going to need to fully qualify the one you're looking for, by using mother.IDNO or father.IDNO (or mother.IDNO AND father.IDNO).
Try this on your command:
Dim sqlcom As MySqlCommand = New MySqlCommand("Select * from mother, father where mother.IDNO= '" & TextBox14.Text & "' AND father.IDNO = '" & TextBox14.Text & "'", sqlcon)
If (TextBox14.Text = NoAcc) Then TextBox7.Text = rdr("MOTHER")
If (TextBox14.Text = NoAcc) Then TextBox8.Text = rdr("MOTHER_OCCUPATION")
If (TextBox14.Text = NoAcc) Then TextBox10.Text = rdr("FATHER")
If (TextBox14.Text = NoAcc) Then TextBox11.Text = rdr("FATHER_OCCUPATION")
Looks rather redundant/inefficient doesn't it?
Also you might try using an INNER JOIN instead of dual IDNO "Where"s
精彩评论