1

Below query is showing error

Please help :

DoCmd.RunSQL ("insert into tbltesting (IsDiff)values ('Yes') where empid= '" & Me.txtEmpId.Value & "' and testid= '" & Me.txtAutoNumber.Value & "'")
Improve this question
2
  • There is no error message but the control get stucked by showing a yellow color on the line all the textbox variables have values there when i put my cursor on the variables Commented Jul 22, 2009 at 13:32
  • Now that you've fixed all the numeric criteria by getting rid of the quotes on those fields, you need to lose the quotes on "Yes", which converts it to a string. Just use "Values (True)" and it should append properly. Commented Jul 23, 2009 at 1:19

4 Answers 4

Reset to default
2

I'm going to guess that empid and testid are numeric, and you're setting them off like they're strings in the SQL statement. Remove the single-quotes that you've wrapped around your field references.

DoCmd.RunSQL (" Update tbltesting set IsDiff ='Yes' where empid= " & Me.txtEmpId.Value & " and testid= " & Me.txtAutoNumber.Value & ";")
Improve this answer
Sign up to request clarification or add additional context in comments.

3 Comments

Yes currently the error gone away , but the rows udates it shows as "Zero"
and i am sure that there is a record in the table
Run this as a check to make sure your fields have the data that you think they have: DoCmd.RunSQL (" SELECT * FROM tbltesting WHERE empid= " & Me.txtEmpId.Value & " and testid= " & Me.txtAutoNumber.Value & ";") Incidentally, you can leave off the .Value portion.
1

Insert is creating/adding a new row to the table - the record never existed. Update changes one or more fields in a record thats already saved in the table.

So an insert statement would not have a "where" clause - that would be an "update" statement you would use in this case.

You either need (if you are creating a brand-new record in your DB) - 

DoCmd.RunSQL ("insert into tbltesting (IsDiff)values ('Yes') '")

Or you need (if you are changing some fields in an already-existing record ) - 

DoCmd.RunSQL ("update tbltesting set IsDiff = 'Yes' where empid= '" & Me.txtEmpId.Value & "' and testid= '" & Me.txtAutoNumber.Value & "'")
Improve this answer

2 Comments

DoCmd.RunSQL (" Update tbltesting set IsDiff ='Yes' where empid= '" & Me.txtEmpId.Value & "' and testid= '" & Me.txtAutoNumber.Value & "'") This is still giving error
Could you try - Me.txtAutoNumber.Value.Tostring(). Additionally. Could you : 1)Build a string say 'mySQLString' with the Update statement 2)MsgBox (mySQLString) and 3) DoCmd.RunSQL(mySQLString) ? Finally, Whatever you get in the MsgBox, copy it and execute it outside vba i.e. directly against the DB to check what the error is ? Hope that helps.
1

Well, one problem is that your query is vulnerable to sql injection. Never never NEVER concatenate values from user inputs directly into a query string. Instead, use an ADO.Command object along with real query parameters or parameterized SQL executed with DAO or similar.

Here is an example.

Improve this answer

6 Comments

Could you please show me an example about the way that i need to write query ?
Can you post a working example of sql injection when using jet?
Albert, have a look at this thread: stackoverflow.com/questions/512174/non-web-sql-injection -- I was very skeptical, too, and concluded that the only real vulnerability is with always-true WHERE clauses, such as True=True, because Jet/ACE can't execute multiple SQL statements (the usual worst-case SQL Injection scenario, e.g., "; DROP TABLE ...").
Why would you recommend using an ADO command object instead of parameterized SQL executed with DAO? This is Access we're talking about here, where DAO is the correct first choice for data interface library.
"the usual worst-case SQL Injection scenario, e.g., "; DROP TABLE ..." -- the scenario "DELETE FROM MyTable WHERE Name = 'x' = 'x';" is possible for the Access database engine and quite bad. I think that fact that SQL DDL cannot be injected is no excuse for creating vulnerabilities. Think of it a good practise :)
|
0

Does the empID record already exist in tblTesting? If so, you want an update:

UPDATE tblTesting
   SET IsDiff = 'Yes'
 WHERE empid = ...

and I won't get on my SQL injection soapbox...

Why do you have quotes around your empid/testid values? Are they strings or numbers? If numbers, watch out what you pass them to avoid conversion errors.

Improve this answer

7 Comments

DoCmd.RunSQL (" Update tbltesting set IsDiff ='Yes' where empid= '" & Me.txtEmpId.Value & "' and testid= '" & Me.txtAutoNumber.Value & "'") This is still giving error
There is no error message but the control get stucked by showing a yellow color on the line all the textbox variables have values when i put my cursor on the variables
At that point press "F5" to continue. It will give you the text of the error message. OR : open the immediate window (by using VBA menu "View"->"Immediate Window" , and type in "Print Error" in the window. It will tell you where its hitting an error - hopefully some more details of the error. Make sure your table name is set up correct - Are you pointing to the right table? Did you try executing the same SQL directly?
This is the error message Data type mismatch in criteria expression.
IsDiff table datatype is "text" is that a problem ??
|