no results found

[pame1la]

creating search system, if no results are found ie sql query gives no results, and therefore assign rs to empty string.....

i assigned the rs as normal to the sql query and then have a if statement, if rs.EOF then return msgbox bUT getting error saying:
"cannot use empty object or column name..."

any ideas???

[szlamany]

creating search system, if no results are found ie sql query gives no results, and therefore assign rs to empty string.....

i assigned the rs as normal to the sql query and then have a if statement, if rs.EOF then return msgbox bUT getting error saying:
"cannot use empty object or column name..."

any ideas???

You should at least check the state of the recordset - 1 means you have a recordset...

If rs.State = 1 Then
    ...put you code in here
end if

[pame1la]

but it doesnt let me open the rs, comes up with that error!!! i can only check the state if its open rite?!

[szlamany]

but it doesnt let me open the rs, comes up with that error!!! i can only check the state if its open rite?!

Guessing that you got an error on the SQL side then - so no RS at all...

So check

If rs is Nothing Then
   MsgBox "Got no RS"
End if

[pame1la]

Private Sub Serach_Click()
If txtName.Text = "" Then
MsgBox "Please enter product name", vbOKOnly, "Search Error"
Else
Str = "SELECT * From Details WHERE ItemName = '" & txtName.Text & "'"""
rs.Open strTemp, gcnSV, adOpenForwardOnly, adLockReadOnly
If rs.State = 0 Then
MsgBox "No records match", vbOKOnly, "Search Result"

or

Private Sub Serach_Click()
If txtName.Text = "" Then
MsgBox "Please enter product name", vbOKOnly, "Search Error"
Else
Str = "SELECT * From Details WHERE ItemName = '" & txtName.Text & "'"""
If Str = "" Then
MsgBox "No records match", vbOKOnly, "Search Result"

WHAT AM I DOIN WRONG?

[szlamany]

Private Sub Serach_Click()
If txtName.Text = "" Then
MsgBox "Please enter product name", vbOKOnly, "Search Error"
Else
Str = "SELECT * From Details WHERE ItemName = '" & txtName.Text & "'"""
rs.Open strTemp, gcnSV, adOpenForwardOnly, adLockReadOnly
If rs.State = 0 Then
MsgBox "No records match", vbOKOnly, "Search Result"

or

Private Sub Serach_Click()
If txtName.Text = "" Then
MsgBox "Please enter product name", vbOKOnly, "Search Error"
Else
Str = "SELECT * From Details WHERE ItemName = '" & txtName.Text & "'"""
If Str = "" Then
MsgBox "No records match", vbOKOnly, "Search Result"

WHAT AM I DOIN WRONG?

What is this mess at the end of the string??

Str = "SELECT * From Details WHERE ItemName = '" & txtName.Text & "'"""


It should simply be:

Str = "SELECT * From Details WHERE ItemName = '" & txtName.Text & "'"

[pame1la]

ooooppppssss

its still not returning msgbox, doesnt do anything until i press search again it says, operation not allowed while object is open..

something still wrong
does If rsTemp.State = 0 Then

mean empty???

[szlamany]

If rsTemp is Nothing will check if the object even loaded...

Do you have code like:

dim rsTemp as ADODB.Recordset
set rsTemp=New ADODB.Recordset

before you try the OPEN?

Is your connection string clean?

If you change the select to something that can't fail - like SELECT 'TEST' - does that work?

[demotivater]

ooooppppssss

its still not returning msgbox, doesnt do anything until i press search again it says, operation not allowed while object is open..

something still wrong
does If rsTemp.State = 0 Then

mean empty???

.State = 0 means it's closed. 1 means it's open. 1 doesn't mean it returned any records, just that it's open.

[szlamany]

.State = 0 means it's closed. 1 means it's open. 1 doesn't mean it returned any records, just that it's open.

You are correct - I edited my post. 1 means it's open and the RS is there - columns, metadata - whether it has rows if what EOF is all about...

[pame1la]

yeah all seems rite : (

[szlamany]

yeah all seems rite : (

Does that mean that SELECT 'TEST' also did not work?

That would lead me to believe that your connection string isn't any good.

[pame1la]

its cool i got it