Solve a 3113 error SQL server

Question

0.00/5 (No votes)

See more:

Good afternoon everybody.

I´m trying to update a SQL Server table from an Access application, using VBA.

This is the VBA code:

Querie = "UPDATE dbo_pedidos_ventas SET Factura = " & CStr(folio) & ", ClaveUbicacionAnt = '" & strClave & "' WHERE Id_pedido = " & CStr(NumPedido)
DoCmd.RunSQL (Querie)

The variables "folio" (numeric) and "strClave" (string) contain the values I need to register, each one in its corresponding field.

When the instruction runs, it sends me an error 3113 "Cannot update claveubicacionant; field not updatable".

The field in conflict is type varchar(2) and I´m trying to assign a two-character string to it.

I'll really appreciate any help somebody could give or a tip to where to look for an answer.

CORRECTION: The question should have been: "How can I set updatable a particular field in a SQL Server table ?"

Thank you very much.

What I have tried:

I've been searching for an answer in Internet, and I'm going to keep trying.

Posted 6-Jul-18 7:53am

Miguel Altamirano Morales

Updated 9-Jul-18 8:59am

v3

Add a Solution

Comments

RedDk 6-Jul-18 14:17pm

VBA ... and ... SQL

Please edit your post and include appropriate tags. VBA, by the way ... oh, forget it. I'm out.

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Patrice T · Accepted Answer · 2018-07-06T08:25:00

Solution 1

Because of the way you build the query, we can't know what is really that query.
Change your code to print the query or use the debugger to see it.
When you got it, try to update the question.

VB

Querie = "UPDATE dbo_pedidos_ventas SET Factura = " & CStr(folio) & ", ClaveUbicacionAnt = '" & strClave & "' WHERE Id_pedido = " & CStr(NumPedido)

another problem you have.
Never build an SQL query by concatenating strings. Sooner or later, you will do it with user inputs, and this opens door to a vulnerability named "SQL injection", it is dangerous for your database and error prone.
A single quote in a name and your program crash. If a user input a name like "Brian O'Conner" can crash your app, it is an SQL injection vulnerability, and the crash is the least of the problems, a malicious user input and it is promoted to SQL commands with all credentials.
SQL injection - Wikipedia[^]
SQL Injection[^]
SQL Injection Attacks by Example[^]
PHP: SQL Injection - Manual[^]
SQL Injection Prevention Cheat Sheet - OWASP[^]
How can I explain SQL injection without technical jargon? - Information Security Stack Exchange[^]

Posted 6-Jul-18 8:25am

Patrice T

Comments

Miguel Altamirano Morales 9-Jul-18 14:53pm

Thanks a lot ppolymorphe.

In fact I have already read about SQL Injection, but your comment is worth taking into account.

In this particular case I can assure you there is no possibility of this happening; it's a desktop application and the values are taken from Combo Boxes, the user cannot introduce the data manually, only select them from the available lists.

I think my answer is not properly explained, so I´m going to fix it.

The only point should be: How can I made updatable a field in a SQL Server table ?

Dave Kreskowiak 9-Jul-18 15:26pm

The problem with how you're building the query is that is makes debugging the code harder and makes it easy for user input to turn your SQL statement into a syntax error. Use parameterized queries and this problem goes away.

Miguel Altamirano Morales 9-Jul-18 15:39pm

Thank you very much Dave; The point is that I´m using VBA, and I first have to see if this language accepts parameterized queries. (The system is an already-done one).

I even tried to update this particular field using a Recordset and the old fashion Motor Jet Engine function Recordset.edit and recordset.update; one of the fields is correctly updated but not the one with the problem, and the error message is the same; it´s like if the applicaction does not recognize this field.
Tomorrow I'll try to update this field via a SQL Querie inside the SQL Management Device or manually to see if it Works correctly. If it does not, I´m going to delete this field and try to use a new related-table.

Thanks Dave.

Patrice T 9-Jul-18 15:46pm

'I first have to see if this language accepts parameterized queries.'
VBA accepts them because it is mandatory for stored procedures.

Dave Kreskowiak 9-Jul-18 16:02pm

It has nothing to do with the field at all. If the query works for updating the one field for one record, it will work for the same field in all of the records in the table.

The problem will come down to the data that's going into the field. The characters in the field are messing up the SQL statement, probably because there's an apostrophe character (') somewhere in it. Parameterized queries takes care of this situation, and others, for you.

Miguel Altamirano Morales 10-Jul-18 10:56am

Thank you very much fellows, the suggestions you gave me are very valuable, and I already solved my problem.

See you !!!