[RESOLVED] Count isnt returning anything

[aashish_9601]

i have used this code, but i always get msgbox as 0.
What am i doing wrong.

SqlStr = "SELECT Count(memberid) FROM members WHERE membername Like '*" & Me.TxtFind.Text & "*'"
                Using Conn As New OleDb.OleDbConnection(My.Settings.DMSConnectionString)
                    Using Cmd As New OleDb.OleDbCommand(SqlStr, Conn)
                        Conn.Open()
                        MessageBox.Show(Cmd.ExecuteNonQuery().ToString)
                        Conn.Close()
                    End Using
                End Using

[kleinma]

ExecuteNonQuery is when you are performing something OTHER than a "SELECT" on a database, aka you are not querying data, you are deleting or updating, or inserting data.

You should be using ExecuteScalar instead, which executes a query that is only supposed to return a single value.

[aashish_9601]

before posting it did use executescalar but it isnt working

[kleinma]

"it isn't working" means nothing to me. You need to explain what happened when you tried. Did you get an error, did you get an undesired result? How can we help you if you provide no information other than "it isn't working"

[aashish_9601]

sorry for the improperness

I am getting 0 as the message in the msgbox

[Grimfort]

It is probable that your answer is actually 0 then.

Are you using the correct wildcard type for your database ie sql should be
'%" & Me.TxtFind.Text & "%'"

[kleinma]

I agree, you probably are just returning no values because no values match, and as Grimfort said, % is used for wildcard SQL matching when you use the LIKE statement.

[aashish_9601]

ok % is working. Then when should * be used

[kleinma]

When selecting all rows of a table in a select statement so you don't have to list every single column name in the given table you are pulling data from.

"Select * from members where memberid = 1234"

[GaryMazzone]

* is only used in DAO as a wildcard charactor.

--Matt I think he ment in the Like part of the where clause