ansaurus

Question

vba sql problem

Answer 1

A:

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.

n8wrl 2009-07-22 13:22:18

2009-07-22 13:30:21

what is the error?

n8wrl 2009-07-22 13:45:56

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

2009-07-22 13:51:52

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?

blispr 2009-07-22 14:18:29

This is the error message Data type mismatch in criteria expression.

2009-07-22 14:47:58

IsDiff table datatype is "text" is that a problem ??

2009-07-22 14:50:24

It says 'criteria expression' so I suspect you have a conversion error in one of empid or testid. I notice you're putting quotes around the values - are they numbers or strings?

n8wrl 2009-07-22 15:11:31

THey are number not strings

2009-07-22 15:13:59

Have you tried this - find out the values of Me.txtEmpId.Value and Me.txtAutoNumber.Value and execute the SQL statement with those values hard-coded? What happens then?

n8wrl 2009-07-22 16:06:34

Answer 2

+1 A:

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.

Joel Coehoorn 2009-07-22 13:22:36

Could you please show me an example about the way that i need to write query ?

2009-07-22 14:52:21

Can you post a working example of sql injection when using jet?

Albert D. Kallal 2009-07-22 23:34:17

Albert, have a look at this thread: http://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 ...").

David-W-Fenton 2009-07-23 01:15:11

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.

David-W-Fenton 2009-07-23 01:16:19

"DAO is the correct first choice" -- subjective. But I've added DAO to the answer.

onedaywhen 2009-07-23 05:25:40

"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 :)

onedaywhen 2009-07-23 05:44:13

Answer 3

+1 A:

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 & "'")

blispr 2009-07-22 13:26:51

2009-07-22 13:31:35

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.

blispr 2009-07-22 13:59:56

Answer 4

+2 A:

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 & ";")

David Walker 2009-07-22 15:29:40

Yes currently the error gone away , but the rows udates it shows as "Zero"

2009-07-22 15:35:00

it says Zero rows updated instead one row updated

2009-07-22 15:35:43

and i am sure that there is a record in the table

2009-07-22 15:42:27

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= " ") Incidentally, you can leave off the .Value portion.

David Walker 2009-07-22 15:56:18

ansaurus

tags:

views:

answers:

vba sql problem

related questions