ExecuteNonQuery

[Tony M]

Shouldn't the ExecuteNonQuery be TRUE if it finds a record and FALSE is not?
I get FALSE all the time and I seeded the database with the email address.
I just want to check if record exist before moving on.
If this doesn't work I guess I could load a datatable and check to see if
rows > 0.


Dim cnString As OleDbConnection

cnString = New OleDbConnection("Provider=MicroSoft.Jet.OLEDB.4.0; Data
source = " & Server.MapPath("Someplace.mdb"))

Dim CheckCus As New OleDbCommand("SELECT * from Cus where EMailAddress =" &
QM & Trim(txtEMail.Text) & QM, cnString)


CheckCus.Connection.Open()

If CheckCus.ExecuteNonQuery() Then
txtFirstName.Text = "Yes"
Else
txtFirstName.Text = "No " & Now
End If

CheckCus.Connection.Close()

Thanks
Tony

 

[Scott M.]

A SELECT statement is a query. You don't use ExecuteNonQuery when you are,
in fact, querying the database. Since your query *can* return results, you
need to use the ExecuteReader method of your command, which returns a
DataReader object that you then use to look at the query results.

Try this:

Try
Dim con As New OleDbConnection("Provider=MicroSoft.Jet.OLEDB.4.0; Data
Source = " & Server.MapPath("Someplace.mdb"))
Dim cmd As New OleDbCommand("SELECT * from Cus where EMailAddress =" &
QM & Trim(txtEMail.Text) & QM, cnString)
con.Open()

Dim dr As Oledb.OledbDataReader = cmd.ExecuteReader()

If dr.HasRows Then
txtFirstName.Text = "Yes"
Else
txtFirstName.Text = "No " & Now
End If

Catch ex As Oledb.OledbException
'Database exceptions handled here
Catch ex As Exception
'All other exceptions handled here
Finally
dr.Close()
con.Close()
con.Dispose()
End Try

 

[=?ISO-8859-1?Q?G=F6ran_Andersson?=]

Shouldn't the ExecuteNonQuery be TRUE if it finds a record and FALSE is not?
I get FALSE all the time and I seeded the database with the email address.
I just want to check if record exist before moving on.
If this doesn't work I guess I could load a datatable and check to see if
rows > 0.


Dim cnString As OleDbConnection

cnString = New OleDbConnection("Provider=MicroSoft.Jet.OLEDB.4.0; Data
source = " & Server.MapPath("Someplace.mdb"))

Dim CheckCus As New OleDbCommand("SELECT * from Cus where EMailAddress =" &
QM & Trim(txtEMail.Text) & QM, cnString)


CheckCus.Connection.Open()

If CheckCus.ExecuteNonQuery() Then
txtFirstName.Text = "Yes"
Else
txtFirstName.Text = "No " & Now
End If

CheckCus.Connection.Close()

Thanks
Tony

The method doesn't return a boolean at all, it returns an integer. If
you would have use Option Strict the compiler would have told you that.

The return value is the number of affected records. As a select query
never changes any records, your call will always return zero.

 

[Tony M]

Thanks to all.
seems like allot of work just to see if a record exist, but it works.

Thanks again

 

[Guest]

Dim CheckCus As New OleDbCommand("SELECT * from Cus where EMailAddress
=" & QM & Trim(txtEMail.Text) & QM, cnString)

You should always use SQL Parameters for your queries. Otherwise you can be
the victim of a SQL injection attack.

 

[Johnny Jörgensen]

Gotta ask...

What the heck is a "SQL injection attack"?

Cheers,
Johnny J.

 

[Guest]

Gotta ask...

What the heck is a "SQL injection attack"?

http://en.wikipedia.org/wiki/SQL_injection


There are .NET examples in the wikipedia article.

 

[Scott M.]

Just remember Tony, that connecting to a database and querying it is one of
the most common things programmers do and it's one of the most common things
programmers do incorrectly or incompletely.

Putting your code inside of a Try...Catch and remembering to dispose of the
connection are essential to building robust database applications.

-Scott

 

[Scott M.]

It's when the user passes data (injects) that would cause SQL to throw an
exception and possibly reveal information about your database, table, fields
away for more detailed attacks. Simply passing a single quote can do it.