By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,747 Members | 1,672 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,747 IT Pros & Developers. It's quick & easy.

ExecuteNonQuery

P: n/a


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
Jul 21 '07 #1
Share this Question
Share on Google+
8 Replies


P: n/a
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


"Tony M" <To*************@msn.comwrote in message
news:e$**************@TK2MSFTNGP04.phx.gbl...
>

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

Jul 21 '07 #2

P: n/a
Tony M wrote:
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.

--
Göran Andersson
_____
http://www.guffa.com
Jul 21 '07 #3

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

Thanks again

"Scott M." <s-***@nospam.nospamwrote in message
news:OH**************@TK2MSFTNGP02.phx.gbl...
>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


"Tony M" <To*************@msn.comwrote in message
news:e$**************@TK2MSFTNGP04.phx.gbl...
>>

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



Jul 25 '07 #4

P: n/a
"Tony M" <To*************@msn.comwrote in
news:e$**************@TK2MSFTNGP04.phx.gbl:
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.
Jul 25 '07 #5

P: n/a
Gotta ask...

What the heck is a "SQL injection attack"?

Cheers,
Johnny J.


"Spam Catcher" <sp**********@rogers.comwrote in message
news:Xn**********************************@127.0.0. 1...
"Tony M" <To*************@msn.comwrote in
news:e$**************@TK2MSFTNGP04.phx.gbl:
>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.

Jul 25 '07 #6

P: n/a
"Johnny Jörgensen" <jo**@altcom.sewrote in
news:#N**************@TK2MSFTNGP02.phx.gbl:
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.

Jul 25 '07 #7

P: n/a
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

"Tony M" <To*************@msn.comwrote in message
news:eJ**************@TK2MSFTNGP06.phx.gbl...
Thanks to all.
seems like allot of work just to see if a record exist, but it works.

Thanks again

"Scott M." <s-***@nospam.nospamwrote in message
news:OH**************@TK2MSFTNGP02.phx.gbl...
>>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


"Tony M" <To*************@msn.comwrote in message
news:e$**************@TK2MSFTNGP04.phx.gbl...
>>>

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




Jul 25 '07 #8

P: n/a
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.
"Johnny Jörgensen" <jo**@altcom.sewrote in message
news:%2****************@TK2MSFTNGP02.phx.gbl...
Gotta ask...

What the heck is a "SQL injection attack"?

Cheers,
Johnny J.


"Spam Catcher" <sp**********@rogers.comwrote in message
news:Xn**********************************@127.0.0. 1...
>"Tony M" <To*************@msn.comwrote in
news:e$**************@TK2MSFTNGP04.phx.gbl:
>>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.


Jul 25 '07 #9

This discussion thread is closed

Replies have been disabled for this discussion.