7 Replies - 1154 Views - Last Post: 07 June 2012 - 05:20 AM Rate Topic: -----

#1 vijaycare   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 17
  • Joined: 05-April 12

compare with id and retrieve the name in a table

Posted 31 May 2012 - 05:25 AM

Dear All,

I am doing a small program in vb2005 with access database.
In that I have a small issue, my database has 3 tables
tblbill having vendorid & truckid to compare, tblvendor has vendorid & vendorname,
tbltruck has truckid & truckno in these 3 tables i want to retrieve and show the
vendorname based on the truckid based search. I have written code for some level and
it doesn't work for me. can any one help me. This is my code

 Dim cmd1 As OleDb.OleDbCommand
        cmd1 = New OleDb.OleDbCommand("Select tblVendor.VendorName, tblTruck.TruckID, tblbill.vendorid, tblbill.truckid " & _
        "from tblVendor join tblbill [on (tblVendor.VendorID = tblBill.VendorID)] Join tblTruck  [on (tblTruck.TruckID = tblBill.TruckID]" & _
        " and Where tblBillTruckID = @TruckID and tblBill.DCDate = @Date", con.con)
        cmd1.CommandType = CommandType.Text
        cmd1.Parameters.AddWithValue("@TruckID", cboTruck.SelectedValue)
        cmd1.Parameters.AddWithValue("@Date", dtpDCDate.Value.ToShortDateString)
        Dim da1 As New OleDb.OleDbDataAdapter(cmd1)
        Dim ds1 As New DataSet
        da1.Fill(ds1)
        lstTVendor.Items.Clear()
        Try
            If ds1.Tables.Count > 0 And ds1.Tables(0).Rows.Count > 0 Then
                lstTVendor.DataSource = ds1
            End If
        Catch ex As Exception
            MsgBox("Check the lst vendor conn!", MsgBoxStyle.Critical)
        End Try




Is This A Good Question/Topic? 0
  • +

Replies To: compare with id and retrieve the name in a table

#2 CharlieMay   User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1729
  • View blog
  • Posts: 5,708
  • Joined: 25-September 09

Re: compare with id and retrieve the name in a table

Posted 31 May 2012 - 05:32 AM

Where tblBillTruckID = @TruckID

saw this right off the bat. Shouldn't that be tblBill.TruckID = @TruckID

This post has been edited by CharlieMay: 31 May 2012 - 05:32 AM

Was This Post Helpful? 0
  • +
  • -

#3 vijaycare   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 17
  • Joined: 05-April 12

Re: compare with id and retrieve the name in a table

Posted 31 May 2012 - 05:42 AM

Dear Sir,

That is my mistake and rectified it even it is showing syntax error.
Was This Post Helpful? 0
  • +
  • -

#4 CharlieMay   User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1729
  • View blog
  • Posts: 5,708
  • Joined: 25-September 09

Re: compare with id and retrieve the name in a table

Posted 31 May 2012 - 06:24 AM

OK, that statement doesn't look to follow Access syntax. I don't think Access has a JOIN but instead uses INNER JOIN or OUTER JOIN, etc...

Try replacing the FROM clause and JOIN clauses to this
FROM tblVendor INNER JOIN (tblBill INNER JOIN tblTruck ON tblBill.TruckID = tblTruck.TruckID) ON tblVendor.VendorID = tblBill.VendorID


Also, the WHERE clause is just WHERE tblBillTruckID = @TruckID and tblBill.DCDate = @Date get rid of the and that you have before it.

If this still doesn't work, post your updated code changes so I can have a look
Was This Post Helpful? 0
  • +
  • -

#5 vijaycare   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 17
  • Joined: 05-April 12

Re: compare with id and retrieve the name in a table

Posted 05 June 2012 - 11:50 PM

Ya, That also not working for me
can u please show me a sample code
to retrieve the name using an ID?
Following is my altered code
Dim cmd3 As OleDbCommand = New OleDbCommand("SELECT tblVendor.VendorName," & _
        " tblBill.DCNo FROM tblVendor INNER JOIN" & _
        " (tblBill INNER JOIN tblTruck ON tblBill.TruckID = tblTruck.TruckID)" & _
        " ON tblVendor.VendorID = tblBill.VendorID WHERE" & _
        " tblBill.DCDate = @Date and tblBill.TruckID = @TruckID", con.con)
        cmd3.Parameters.AddWithValue("@TruckID", cboTruck.SelectedValue)
        cmd3.Parameters.AddWithValue("@Date", dtpDCDate.Value.ToShortDateString)

        Dim da3 As New OleDb.OleDbDataAdapter(cmd3)
        Dim ds3 As New DataSet
        da3.Fill(ds3)

        lstDvendor.DataSource = ds3
        lstDvendor.DisplayMember = "VendorName"
        lstDvendor.DisplayMember = "DCNo"


Was This Post Helpful? 0
  • +
  • -

#6 CharlieMay   User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1729
  • View blog
  • Posts: 5,708
  • Joined: 25-September 09

Re: compare with id and retrieve the name in a table

Posted 06 June 2012 - 04:05 AM

Well you are also checking for a date. DCDate = @Date that is very specific in the datetime world.

First off, what type is DCDate in your database? (The only reason I ask this is that a lot of people become frustrated with datetime and revert to a string type so I want to be clear for my explanation, that we are dealing with a datetime field in your database)

if you remove the check for the date, does the query populate the datasource?

put a break point on line 12, run the code and when it stops, put your mouse over the ToShortDate method and see what it holds. Compare this with your database and see if you have a field that has that EXACT value in it.
Was This Post Helpful? 0
  • +
  • -

#7 vijaycare   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 17
  • Joined: 05-April 12

Re: compare with id and retrieve the name in a table

Posted 07 June 2012 - 01:01 AM

Dear Charlie,

Thanks for your response.
As per your advice I have tried both the method.
The first one shows the listview type diff.
And the second one tried but it doesn't show anything
on the toshortdate method.
What can i do for next?
Was This Post Helpful? 0
  • +
  • -

#8 CharlieMay   User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1729
  • View blog
  • Posts: 5,708
  • Joined: 25-September 09

Re: compare with id and retrieve the name in a table

Posted 07 June 2012 - 05:20 AM

Again, I'm not sure what type your DCDate field is but I'm going to assume it is a datetime type

Since datetime stores date and time you could use a BETWEEN clause in your statement

WHERE DCDate BETWEEN @Date1 AND @Date2 and tblBill.TruckID = @TruckID", con.con)
cmd3.Parameters.AddWithValue("@TruckID", cboTruck.SelectedValue)
cmd3.Parameters.AddWithValue("@Date1", dtpDCDate.Value.ToShortDateString & "12:00 AM")
cmd3.Parameters.AddWithValue("@Date1", dtpDCDate.Value.ToShortDateString & "11:59 PM")


This should pull all times for a given date so if your data looks like
06/07/2012 08:19 AM
06/07/2012 09:14 AM


a date of 6/7/2012 in your DateTimePicker should pull both of those results.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1