Parameterized ADO Insert Query Fails Table Validation Rule Unexpectedly

[darkwind]

In current-build Access 365, I'm using an ADO parameterized query to insert data into an emails table. When attempting to do so, one of the validation rules in the table appears to always fail. When manually entering the same data into the same field, the validation rule appears to behave properly. I'm really scratching my head over this one, so hopefully someone here can spot what I couldn't.



For context, the table in question (tblSingleEmails) is just a contact list for one-off emails in relation to a given project. tblSingleEmails consists of an autonumber primary key (singleEmailID), a foreign key (projectID), and two fields - emailUser and emailDomain. There are various reasons for splitting the full email address in this way, but I won't go into them here.

Both emailUser and emailDomain have a Validation Rule and Validation Text set up. These rules are really just there to ensure during data entry users understand what they're supposed to enter in each field. For emailUser, it's:

Not Like "*@*"

For emailDomain:

(Not Like "*@*") And (Like "*.*")

Not nearly enough to validate the entry as correct - but I have out-of-scope VBA to handle that. This just ensures the user is entering something that looks like an email username and domain name.

I'm using a class called SqlCommand to create my parameters for me, as defined here: http://codereview.stackexchange.com/...ers-on-the-fly

That class is pretty well validated for this use-case - I've used it in numerous places throughout my code without any trouble so far. I therefore don't think there is an issue with it, although I did do a bit of testing to confirm that, which I'll discuss later. I call that class from a function in a standard module where I'm attempting to insert some test email values. The relevant module code:

Public Function AddEmails(ByVal lProjectID As Long, ParamArray EmailValues() As Variant) As Boolean

   ...
         
         Dim sSQL As String, lIndex As Long, objConn As ADODB.connection
35       sSQL = "INSERT INTO tblSingleEmails (projectID, emailUser, emailDomain) VALUES (?, ?, ?);"
40       Set objConn = CurrentProject.connection

   ...


60       For lIndex  = LBound(EmailValues()) To UBound(EmailValues())
            
            Dim bResult As Boolean, objCmd As New SqlCommand, sUser As String, sDomain As String
65          sUser = Left(EmailValues(lIndex ), InStr(1, EmailValues(lIndex ), "@") - 1)
70          sDomain = Right(EmailValues(lIndex ), Len(EmailValues(lIndex )) - InStr(1, EmailValues(lIndex ), "@"))
75          bResult = objCmd.ExecuteNonQuery(objConn, sSQL, lProjectID, sUser, sDomain)
80          If Not bResult Then Err.Raise FunctionErr.InsertQueryFailed, sModName & "." & sProcName, "Failed to insert single email."
85       Next lIndex 


   ...


End Function

Here's the exact code called by objCmd.ExecuteNonQuery:

Public Function ExecuteNonQuery(connection As ADODB.connection, sql As String, ParamArray parametervalues()) As Boolean


    Dim cmd As New ADODB.Command
    cmd.ActiveConnection = connection
    cmd.CommandType = adCmdText
    cmd.CommandText = sql
    
    Dim i As Integer
    Dim value As Variant
    For i = LBound(parametervalues) To UBound(parametervalues)
        value = parametervalues(i)
        cmd.parameters.Append ToSqlInputParameter(value)
    Next
    
    On Error Resume Next
        cmd.Execute
        ExecuteNonQuery = (Err.Number = 0)
    On Error GoTo 0
    
End Function

I've paused execution before cmd.Execute and confirmed that cmd.Parameters(2) = "gmail.com", so the parameterization is working correctly. Comparing that to cmd.CommandText, it's clear that it is attempting to insert "gmail.com" into the emailDomain field of tblSingleEmails, all as intended, as cmd.CommandText is properly populated with the sSQL string. I also tested in the immediate pane:

debug.Print Not ("gmail.com" Like "*@*") And ("gmail.com" like "*.*")

This returns True, confirming that it should be passing validation. I've also manually entered my test data into the table, and the validation rules have performed as expected. emailDomain is perfectly happy with "gmail.com" when manually entered, and won't accept "gmailcom" or "gmail@com.com" -- all exactly as expected.

However, running cmd.Execute results in the aforementioned error with error description equal to my Validation Text for the emailDomain field. I feel like I must be missing something obvious, but I don't see it. Any help would be greatly appreciated.

[Micron]

Is your table field formatted as rtf? Sometimes the values being inserted are not the same as what you can see, and testing T/F in the immediate window won't reveal rtf tags such as <div>. Did you debug.print or query (? in immediate window) the variable value and check the string?

[darkwind]

The field in question is short text type -- so no chance of rtf on the table side, unless I'm missing something there. I don't believe I tested the variable directly -- I tested once it was pushed into cmd.Parameters():

Debug.Print cmd.Parameters(2) = "gmail.com"
True

Since the cmd.CommandText is :

INSERT INTO tblSingleEmails (projectID, emailUser, emailDomain) VALUES (?, ?, ?);

That means the 3rd parameter is "gmail.com" as expected, which is the value being pushed to the emailDomain field.

[Micron]

You're right - short text means no rtf. Usually when validation fails when code is involved (in this case fail means allowing what you don't want) it usually means the code is constructing a string that passes the table field validation if that's where it is - still not clear to me. If that is not the case, then I'd have to believe that the field validation expression is not correct, or able to be processed, in which case it could be that the expression is ignored and an error is not raised. I cannot put test@gmail.com into a table with field validation rule as Not Like "*@*". In fact, I cannot even enter test@ and if that's what you need, your validation expression would be an issue even if you get it to work. In your case, I think I'd drop table field validation rule and do it all in code. You did put your expression on the correct row of the table properties - and not on validation message row?

[darkwind]

I cannot put test@gmail.com into a table with field validation rule as Not Like "*@*". In fact, I cannot even enter test@ and if that's what you need, your validation expression would be an issue even if you get it to work.

Right -- the emailUser field contains test and the emailDomain field would contain gmail.com. Your misunderstanding is exactly the one I would expect users to have, which is why I bothered with the validation rule in the first place- to prevent that exact data entry

Here's a breakdown of the situation in table form - it might be a touch easier to understand:

Field Name	Test Value	Validation Rule	Result (Expected)	Result (Manual)	Result (ADO)
emailUser	test	Not Like "*@*"	Pass	Pass	Pass
emailDomain	gmail.com	(Not Like "*@*") And (Like "*.*")	Pass	Pass	Fail
emailUser	test@gmail.com	Not Like "*@*"	Fail	Fail	Fail
emailDomain	gmailcom	(Not Like "*@*") And (Like "*.*")	Fail	Fail	Fail

So the validation rule for emailUser is behaving as expected, but the emailDomain validation rule is failing unexpectedly only when run through the ADO recordset.

You did put your expression on the correct row of the table properties - and not on validation message row?

Yes, confirmed.

For the time being, I've removed the validation rule on emailDomain and will handle that validation differently. But I still can't begin to understand this behavior.

[Micron]

Your misunderstanding is exactly the one I would expect users to have

I think my misunderstanding is overlooking that I had considered the wrong field. I was testing the other one, not the domain.
Unless you're connecting to a different db, I don't see the reason for ADO over DAO. Perhaps you discovered a bug there.

[darkwind]

Parameterization is the nice thing about ADO in my opinion. It's convenient to not have to worry about type formatting in queries or SQL-injection style issues (even inadvertent ones). Using the linked method, I just toss whatever data I have, regardless of type, into the method, and the AdoValueConverter class handles turning that value into the correct type of parameter. Instead of:

SQL = "SELECT * FROM tblWhatever WHERE stringCondition=""" & SomeString & """ AND dateCondition=#" & SomeDate & "#;"
Set rs = CurrentDb.OpenRecordset (SQL)

I can do:

SQL = "SELECT * FROM tblWhatever WHERE stringCondition=? AND dateCondition=?;"
SomeString = Me.StringField
SomeDate = Me.DateField
Set rs = objCmd.Execute(objConn, SQL, SomeString, SomeDate)

And forget it. I don't care what got put into those fields - the query will work (or at least - not throw an error). The AdoValueConverter looks at the values of the parameters provided and turns them into the correct type of ADO input parameter. Sure, I still have to make sure I don't send a string to a numeric field - but other than that kind of thing, I'm good to go. In the first case, someone could easily cause a syntax error by entering a double-quote into the text field. With ADO, it's a non-issue. Until I started doing this I was continually battling with disallowing characters in fields or sufficiently escaping text fields and so forth- and still sometimes getting random syntax errors due to an unexpected quote in an unexpected field. Throw in that I'm working with multiple databases here and that some are web databases (where ADO is just required), and to me, it makes more sense to just always use ADO. Besides, there's really no good reason for those characters not to be allowed in most of these fields - just a limitation of working the way I was.

Anyway, that's off-topic enough. Appreciate you looking over my shoulder on this, and I'll just live with the workaround for now.

[Micron]

Thanks for the perspective on ADO.

[CJ_London]

just a thought - ADO filters have a more rigid structure than DAO. For example '(A OR B) AND C' works in DAO but fails in ADO - you have to write it as '(A AND C) OR (B AND C)'. So perhaps remove the brackets?

Not Like "*@*" And Like "*.*"

or perhaps

(Not Like "*@*" And Like "*.*")

[darkwind]

just a thought - ADO filters have a more rigid structure than DAO. For example '(A OR B) AND C' works in DAO but fails in ADO - you have to write it as '(A AND C) OR (B AND C)'. So perhaps remove the brackets?

This was a promising idea that got me a bit excited. It didn't work, but it did point me in the right direction.

I had not realized that ADO would be checking the validation rule itself, since the validation rule is saved at the table-level. I had thought that ADO would just receive the results of the validation rule test - but of course, that's not how it works. ADO gets the validation rule and assesses it directly... using ANSI 92 rules! So of course the validation rule wasn't working! * is just * in ANSI 92 - % is the wildcard character!

Not ALike "%@%" And ALike "%.%"

Fixed! Well, sort of. Of course, Access normally uses ANSI 89 internally. And while it's using ANSI 89, it's not possible to make this work - the table will reject normal data entry when using that validation rule. So, I had to swap the entire database to ANSI 92 - but fortunately, I'm early enough in development that isn't an issue. At least now I understand what was going on

[CJ_London]

Glad you got it sorted