Access SQL not updating any rows

  • Thread starter Thread starter Guest
  • Start date Start date
G

Guest

I have a vb.net app that is building a SQL statement. When the SQL statement
executes, it does not error, but updates zero rows. If I take the statement
and paste it into my Access database as a Query and execute it, it updates
rows. My code is moving data from one column to another in the same table.

Dim cnSQL As New OleDbConnection
Dim cmd As New OleDbCommand
Dim sSQL As String
Dim iCount As Integer
Dim X As Integer
Dim sPayer As String = 4

Try
cnSQL.ConnectionString = GetOleDBConnectionString() ' This
function gets my connect string
cnSQL.Open()

sSQL = "update tblPayees " & _
" set paymentamt7 = paymentamount " & _
" where paymenttype like '*7' " & _
" and payerid = " & sPayer & _
" and (paymentamt7 = 0 or " & _
" paymentamt7 is null)"
cmd.Connection = cnSQL
cmd.CommandText = sSQL
iCount = cmd.ExecuteNonQuery()
Debug.WriteLine(iCount.ToString & " records had payments
moved to column " & X.ToString)
Next

When I look at the SQL statement executing, it looks like this -

update tblPayees set paymentamt7 = paymentamount where paymenttype like
'*7' and payerid = 4 and (paymentamt7 = 0 or paymentamt7 is null)

It updates zero rows, but when I paste that exact statement into an Access
query, it works correctly and updates my table. Anybody have any idea what
might be causing this?
 
I figured it out -

The wildcard character in Access is usually the asterisk (*) - and that
works from within Access. But when I changed the wildcard character in the
SQL statement to the Percent Sign (%) - my code worked. I don't understand
it, but I'm past my problem.
 
¤ I figured it out -
¤
¤ The wildcard character in Access is usually the asterisk (*) - and that
¤ works from within Access. But when I changed the wildcard character in the
¤ SQL statement to the Percent Sign (%) - my code worked. I don't understand
¤ it, but I'm past my problem.
¤

It's because you're using Jet OLEDB and ADO.NET instead of DAO. That is the difference.


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Is that also why the Access REPLACE function didn't work when I used it in a
SQL statement? I always thought Jet OLEDB and Access used the same syntax.
When using an Access database, usually I develop my SQL in Access then paste
it into my vb code. Is there Jet SQL Reference documentation somewhere?
 
¤ Is that also why the Access REPLACE function didn't work when I used it in a
¤ SQL statement? I always thought Jet OLEDB and Access used the same syntax.
¤ When using an Access database, usually I develop my SQL in Access then paste
¤ it into my vb code. Is there Jet SQL Reference documentation somewhere?
¤

The Replace function is a VBA function, it isn't part of Jet SQL. You can use it from a query in
Microsoft Access because the application can interpret VBA. Jet doesn't know anything about many of
the VBA functions and that is why queries that use them cannot be executed outside of the Access
application.

Some of the VBA functions are exposed via "sandbox" mode:

http://support.microsoft.com/default.aspx?scid=kb;en-us;239482

The following might be of some help:

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/acfundsql.asp
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnacc2k/html/acadvsql.asp


Paul ~~~ (e-mail address removed)
Microsoft MVP (Visual Basic)
 
Back
Top