selecting and presenting data from database
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?
Code:
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
Re: selecting and presenting data from database
What data are you trying to return?
Re: selecting and presenting data from database
What is "rstContractsProduct"? Your recordset you show is named 'rst', yet you show this "rstContractsProduct!QuantityOrdered".
Re: selecting and presenting data from database
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.
Code:
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
Re: selecting and presenting data from database
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.
Re: selecting and presenting data from database
Thread moved to the 'Database Development' forum (the 'VB6' forum is only meant for questions which don't fit in more specific forums)
Re: selecting and presenting data from database
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
Re: selecting and presenting data from database
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.
Re: selecting and presenting data from database
Quote:
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.
Re: selecting and presenting data from database
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
Code:
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
