selecting and presenting data from database

[tendaimare]

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 ' I want to extract this data from the database
                                            If rst.EOF Then
                                            'MsgBox ("SEARCH FAILED")' This was the original Line
                                            MsgBox ("QUANTITY ORDERED  " & rstContractsProduct!QuantityOrdered & "   My Load Number is   " & rst!LoadNumber)'
                                            ' I ended up putting the line above to see what would come up but originally the messagebox with search failed was the one that was above  
                                            Else
                                            What 
                                            MsgBox ("QUANTITY ORDERED  " & rstContractsProduct!QuantityOrdered & "   My Load Number is   " & rst!LoadNumber)
                                            End If
   
   End If

[Nightwalker83]

What data are you trying to return?

[SamOscarBrown]

What is "rstContractsProduct"? Your recordset you show is named 'rst', yet you show this "rstContractsProduct!QuantityOrdered".

[tendaimare]

I am trying to find out if there is a record with a matching "ProdCont" value in the database, but since I was still trying to make this code work in the first place I have only put messageboxes in the code. @SamOscarBrown Thanks for that, that was a mistake on my part. Here is the corrected code. Now i have even tried putting in an actual value that I know exists in the database but it still retuns the search failed messagebox.

   If txtSun.Text = "SUN" Then
            Set rst = New ADODB.Recordset
            Dim sSql As String
            sSql = "SELECT * FROM SundryProduct WHERE ProdCont='" & txt_con_code.Text & "'"
            rst.Open sSql, Cnn, adOpenForwardOnly, , adCmdText
            '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  " & rst!QuantityOrdered & vbCrLf & "   My Load Number is   " & rst!LoadNumber)
            End If
   End If

[SamOscarBrown]

Sounds like your query is not returning any records....so, do this:

Put "msgbox sSql" on the line before your 'rst.open' line.
That msgbox should tell you exactly what the query is....what it sounds like is your "txt_con_code.text" box entry does not match exactly with anything in your ProdCont fields.

[si_the_geek]

Thread moved to the 'Database Development' forum (the 'VB6' forum is only meant for questions which don't fit in more specific forums)

[SamOscarBrown]

In addition, your code is assuming (if one exists) that only ONE record will be returned. Instead of "if rst.EOF Then.../ Else/End",
do this:
(This will show you if more than one entry matches your query---you could also put that info into a listbox (additem) vice a msgbox as your progress in your code).

Do while NOT RS.EOF
msgbox cStr(rs!QuantityOrdered)
rs.MoveNext
Loop

[tendaimare]

I was using the while loop to do the same task but then my problem with the loop was that I could not find a way to take care of the scenario where the value I am looking for in the database is not found.
Do while NOT RS.EOF
msgbox cStr(rs!QuantityOrdered)
rs.MoveNext
Loop
In the code you showed with the loop where do you place the code to say "if the search fails to find a row in database that matches the search criteria then do this and that" is it below the movenext? or below the loop? I could not answer those question that why I then adopted the new method I am trying out. If i had answers for those question I could quickly run back to using the loop.

[FunkyDexter]

In the code you showed with the loop where do you place the code to say "if the search fails to find a row in database that matches the search criteria then do this and that" is it below the movenext?

You would use your original If statement but put the loop in the Else part. So if no records were foudn you'd display a search failed message. If records were found you'd loop through those records.

As for why you're not getting records I'm unsure but here a few thoughts.
1. It's a looong time since I've used ADO but I've got a vague memory of there being an issue where the pointer that came back wasn't always where you'd expect. It's possible that you actually have got records but the pointer has already been set to the end of the set. Try checking the record count instead and issue a MoveFirst to make sure the pointer's in the right place.
2. If you genuinely have no records being returned, try putting a breakpoint just after you build your sqlstring then outputting it to the immediate window. Run the sql directly against the database rather than from code and see what the result is.

[tendaimare]

thank you guys the code finally worked I left the select statements and finally went ror the loop and here is the final code that worked. Its producing the results, I think it will work

If txtSun.Text = "SUN" Then
                   Set rst = New ADODB.Recordset
                   rst.Open "SELECT * FROM SundryProduct WHERE ProdCont='" & txt_con_code.Text & "' ", Cnn, adOpenForwardOnly, , adCmdText
                   rst.MoveFirst

                   While Not rst.EOF

                       If (txt_con_code.Text = rst!ProdCont And LTrim$(MyTempCustomerCode.Text) = rst!CustomerCode) Then
                                     SundryLoadNumber = rst!LoadNumber
                                     SundryAccLoadTon = rst!AccumTons
                                     Exit Sub
                       Else
                                     SundryLoadNumber = 1
                                     SundryAccLoadTon = Format(0, "######.#0")
                               
                       End If
                       
                                    rst.MoveNext
                        
                      Wend
                     
      End If