12 Replies - 899 Views - Last Post: 11 May 2013 - 04:41 AM Rate Topic: -----

#1 tendaimare  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 170
  • Joined: 04-November 10

selecting and presenting data from database

Posted 02 May 2013 - 05:11 AM

I want to select and view my data in the database but its proving to be a challenge . Any advice on where I could be missing it? If i run this code even when the select criteria is met, it always returns search failed,Any help?
     If txtSun.Text = "SUN" Then
            Set rst = New ADODB.Recordset
            rst.Open "SELECT * FROM SundryProduct WHERE ProdCont='" & txt_con_code.Text & "' ", Cnn, adOpenForwardOnly, , adCmdText
            If rst.EOF Then
            MsgBox ("QUANTITY ORDERED  " & rstContractsProduct!QuantityOrdered & vbCrLf & "   My Load Number is   " & rst!LoadNumber)
            Else
            MsgBox ("QUANTITY ORDERED  " & rstContractsProduct!QuantityOrdered & vbCrLf & "   My Load Number is   " & rst!LoadNumber)
            End If
   End If


Is This A Good Question/Topic? 0
  • +

Replies To: selecting and presenting data from database

#2 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3177
  • View blog
  • Posts: 10,638
  • Joined: 12-December 12

Re: selecting and presenting data from database

Posted 02 May 2013 - 05:20 AM

What does "it always returns search failed" mean? If you receive errors, what are these errors?

You current code will also attempt to read from the recordset, even if EOF is true.
Was This Post Helpful? 1
  • +
  • -

#3 tendaimare  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 170
  • Joined: 04-November 10

Re: selecting and presenting data from database

Posted 02 May 2013 - 05:29 AM

Initially the code was like this but then I changed it around.
   If txtSun.Text = "SUN" Then
            Set rst = New ADODB.Recordset
            rst.Open "SELECT * FROM SundryProduct WHERE ProdCont='" & txt_con_code.Text & "' ", Cnn, adOpenForwardOnly, , adCmdText
            If rst.EOF Then
            MsgBox ("SEARCH FAILED")
            Else
            MsgBox ("QUANTITY ORDERED  " & rstContractsProduct!QuantityOrdered & vbCrLf & "   My Load Number is   " & rst!LoadNumber)
            End If
   End If


Its giving me the messagebox search failed even when I know for sure that the value exists in the database. I am not receiving any errors, but i am not getting the results I expect.
Was This Post Helpful? 0
  • +
  • -

#4 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3177
  • View blog
  • Posts: 10,638
  • Joined: 12-December 12

Re: selecting and presenting data from database

Posted 02 May 2013 - 06:05 AM

Is ProdCont a number? In which case the value for it shouldn't be enclosed in apostrophes.
Was This Post Helpful? 0
  • +
  • -

#5 tendaimare  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 170
  • Joined: 04-November 10

Re: selecting and presenting data from database

Posted 02 May 2013 - 06:16 AM

No, its not a number its a text field


View Postandrewsw, on 02 May 2013 - 04:05 PM, said:

Is ProdCont a number? In which case the value for it shouldn't be enclosed in apostrophes.

Was This Post Helpful? 0
  • +
  • -

#6 andrewsw  Icon User is offline

  • Fire giant boob nipple gun!
  • member icon

Reputation: 3177
  • View blog
  • Posts: 10,638
  • Joined: 12-December 12

Re: selecting and presenting data from database

Posted 02 May 2013 - 06:35 AM

If you are checking if no records were returned you should use:

If rst.EOF And rst.BOF Then

this only applies in the case of no records, although I doubt that this accounts for your current issue.

Use and print a string variable:

Dim sSql As String
sSql = "SELECT * FROM SundryProduct WHERE ProdCont='" & txt_con_code.Text & "' "

This will make it much easier to check if the statement is correctly formed. And what does a typical txt_con_code.Text look like?

This post has been edited by andrewsw: 02 May 2013 - 06:36 AM

Was This Post Helpful? 0
  • +
  • -

#7 tendaimare  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 170
  • Joined: 04-November 10

Re: selecting and presenting data from database

Posted 02 May 2013 - 06:45 AM

I want to check if the record with that "ProdCont" value exists. If it exists then do some stuff with data. if it doesnt exist set the required variables to their corresponding values I've uploaded a picture of what a typical "ProdCont" value looks like, that particular value actually exists in the database but the system returns the search failed msgbox

Attached image(s)

  • Attached Image

Was This Post Helpful? 0
  • +
  • -

#8 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 572
  • View blog
  • Posts: 2,985
  • Joined: 19-May 09

Re: selecting and presenting data from database

Posted 03 May 2013 - 05:50 PM

What database are you using?
Was This Post Helpful? 0
  • +
  • -

#9 tendaimare  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 170
  • Joined: 04-November 10

Re: selecting and presenting data from database

Posted 09 May 2013 - 04:45 AM

View PostBobRodes, on 04 May 2013 - 03:50 AM, said:

What database are you using?


I am using an ms access database.
Was This Post Helpful? 0
  • +
  • -

#10 Neku  Icon User is offline

  • D.I.C Regular

Reputation: 20
  • View blog
  • Posts: 268
  • Joined: 21-May 09

Re: selecting and presenting data from database

Posted 10 May 2013 - 09:12 AM

seems like the problem is in your condition:

If rst.EOF Then
Msgbox "SEARCH FAILED"
Else
...
End if



it reach the end of the file and since that is the only condition it show the message search failed.

what do you think you'll get if it find nothing (exept the MsgBox)? some empty string maybe.. i don't know, what i know is that you have to change this condition or it will always give you the message.

also how do you use ! as variable name? i'm not sure rst!LoadNumber should be valid variable name.. maybe it cause problems too?
Was This Post Helpful? 0
  • +
  • -

#11 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 572
  • View blog
  • Posts: 2,985
  • Joined: 19-May 09

Re: selecting and presenting data from database

Posted 10 May 2013 - 05:30 PM

Neku, the ! ("bang") notation in VB designates a user-defined property, which occur most often as members of the Recordset object's Fields collection. So, myRecordset("myField") and myRecordset!myField are equivalent.

This post has been edited by BobRodes: 10 May 2013 - 05:32 PM

Was This Post Helpful? 2
  • +
  • -

#12 BobRodes  Icon User is offline

  • Your Friendly Local Curmudgeon
  • member icon

Reputation: 572
  • View blog
  • Posts: 2,985
  • Joined: 19-May 09

Re: selecting and presenting data from database

Posted 10 May 2013 - 05:35 PM

tendamaire, have you run the exact sql statement in access and found that the result contains records?
Was This Post Helpful? 0
  • +
  • -

#13 Neku  Icon User is offline

  • D.I.C Regular

Reputation: 20
  • View blog
  • Posts: 268
  • Joined: 21-May 09

Re: selecting and presenting data from database

Posted 11 May 2013 - 04:41 AM

View PostBobRodes, on 11 May 2013 - 02:30 AM, said:

Neku, the ! ("bang") notation in VB designates a user-defined property, which occur most often as members of the Recordset object's Fields collection. So, myRecordset("myField") and myRecordset!myField are equivalent.

something new to learn every day eh? :P
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1