6 Replies - 2795 Views - Last Post: 29 October 2011 - 03:03 AM Rate Topic: -----

#1 rekha_kj  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 56
  • Joined: 14-September 11

To retrieve the data from two tables in a database

Posted 29 October 2011 - 01:44 AM

Hi,
This is my coding in vb.net. I have used two database (1) party (2)partydup.
Structure of the tables:
party table: fields :code, name1,add1,add2 etc.
partyduptable: fields: code, description

In both tables i have common field ("code"). I tried to retrieve the value of "description" field from partydup table when the "code" field in both the tables are same. I run the below coding but it is showing the error as "No data exists for the row/column". But i have a record which contains the code field value as 123 in both the tables. Still it is showing the error. Please let me know what is the mistake i have done.
Imports System.IO
Imports System.Data.OleDb
Imports System.Data.OleDb.OleDbConnection
Imports System.Data.OleDb.OleDbDataAdapter
Imports System.Data.OleDb.OleDbDataReader
Imports System.Data.OleDb.OleDbCommand
Public Class Form3
Inherits System.Windows.Forms.Form

Dim cn As OleDbConnection
Dim cmd As OleDbCommand
Dim dr As OleDbDataReader
Dim sql As String




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

End Sub
Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=party1.mdb;")
cn.Open()
sql = "select partydup.description from party,partydup where party.code=partydup.code"
cmd = New OleDbCommand(sql, cn)

dr = cmd.ExecuteReader()
If dr.HasRows Then
TextBox1.Text = dr(0)
MsgBox(dr(0))
Else
MsgBox("No rows returned")
End If


'cmd.ExecuteNonQuery()
cn.Close()

End Sub
End Class


Thanks
Rekha

This post has been edited by smohd: 29 October 2011 - 02:47 AM
Reason for edit:: Code tags added. Please use [code] tags when posting codes


Is This A Good Question/Topic? 0
  • +

Replies To: To retrieve the data from two tables in a database

#2 DimitriV  Icon User is offline

  • They don't think it be like it is, but it do
  • member icon

Reputation: 584
  • View blog
  • Posts: 2,738
  • Joined: 24-July 11

Re: To retrieve the data from two tables in a database

Posted 29 October 2011 - 01:53 AM

Please put code between code tags.
Was This Post Helpful? 1
  • +
  • -

#3 rekha_kj  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 56
  • Joined: 14-September 11

Re: To retrieve the data from two tables in a database

Posted 29 October 2011 - 01:54 AM

Hi,
This is my coding in vb.net. I have used two database (1) party (2)partydup.
Structure of the tables:
party table: fields :code, name1,add1,add2 etc.
partyduptable: fields: code, description

In both tables i have common field ("code"). I tried to retrieve the value of "description" field from partydup table when the "code" field in both the tables are same. I run the below coding but it is showing the error as "No data exists for the row/column". But i have a record which contains the code field value as 123 in both the tables. Still it is showing the error. Please let me know what is the mistake i have done.
Imports System.IO
Imports System.Data.OleDb
Imports System.Data.OleDb.OleDbConnection
Imports System.Data.OleDb.OleDbDataAdapter
Imports System.Data.OleDb.OleDbDataReader
Imports System.Data.OleDb.OleDbCommand
Public Class Form3
Inherits System.Windows.Forms.Form

Dim cn As OleDbConnection
Dim cmd As OleDbCommand
Dim dr As OleDbDataReader
Dim sql As String




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

End Sub
Private Sub Button1_Click_1(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

cn = New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=party1.mdb;")
cn.Open()
sql = "select partydup.description from party,partydup where party.code=partydup.code"
cmd = New OleDbCommand(sql, cn)

dr = cmd.ExecuteReader()
If dr.HasRows Then
TextBox1.Text = dr(0)
MsgBox(dr(0))
Else
MsgBox("No rows returned")
End If


'cmd.ExecuteNonQuery()
cn.Close()

End Sub
End Class



*Mentor Edit: The code tags looks like this: :code:

This post has been edited by smohd: 29 October 2011 - 02:49 AM
Reason for edit:: fixed code tags

Was This Post Helpful? 0
  • +
  • -

#4 DimitriV  Icon User is offline

  • They don't think it be like it is, but it do
  • member icon

Reputation: 584
  • View blog
  • Posts: 2,738
  • Joined: 24-July 11

Re: To retrieve the data from two tables in a database

Posted 29 October 2011 - 01:55 AM

Did you realise that your .ExecuteNonQuery line is commented? As in, it has a little symbol (') at the front.
J-Bo
Was This Post Helpful? 1
  • +
  • -

#5 rekha_kj  Icon User is offline

  • D.I.C Head

Reputation: 0
  • View blog
  • Posts: 56
  • Joined: 14-September 11

Re: To retrieve the data from two tables in a database

Posted 29 October 2011 - 01:58 AM

Yes but i have ExecuteReader() fun. instead of ExecutenonQuery.Is it correct


thanks
Rekha
Was This Post Helpful? 0
  • +
  • -

#6 DimitriV  Icon User is offline

  • They don't think it be like it is, but it do
  • member icon

Reputation: 584
  • View blog
  • Posts: 2,738
  • Joined: 24-July 11

Re: To retrieve the data from two tables in a database

Posted 29 October 2011 - 02:38 AM

Im fairly sure that's right - I'm not too skilled in DB's but I've seen enough like that to think its right.
J-Bo
Was This Post Helpful? 2
  • +
  • -

#7 smohd  Icon User is offline

  • Critical Section
  • member icon


Reputation: 1819
  • View blog
  • Posts: 4,627
  • Joined: 14-March 10

Re: To retrieve the data from two tables in a database

Posted 29 October 2011 - 03:03 AM

Your problem is because you didnt read anything from your data reader. You must read before checking if there is rows or not. So put thus line before your if statement: dr.Read()

NOTE: Next time no need to re post your code if you forget to out code tags, just report it to moderators and they will fix it. Also code tags are like :code:
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1