none
Problem with UPDATE in MSAccess RRS feed

  • Question

  • Hi, i did this subroutine to update a table with an index value but i have an error with the data type

    because the indice value has many decimals and the error say

    Syntax error in UPDATE statement.

    and i don't know what to do to fix it

    I changed the datatype in msAccess to Double but the error still apear

     

    Public Sub Indexa(ByVal Indice As Double, ByVal Stem As String, ByVal Id_Consulta As Integer)

    Using con As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0; " & _

    " Data Source= C:\Documents and Settings\Administrator\Desktop\BD_Prueba.mdb;" & _

    " User id=admin; Password=;")

    Dim comando As New OleDbCommand("UPDATE Frecuencia_Palabra SET Indice = " & Indice & _

    " WHERE Stem = '" & Stem & "' AND Id_Consulta = " & Id_Consulta, con)

    con.Open()

    comando.ExecuteNonQuery()

    End Using

    End Sub

    Monday, April 28, 2008 4:39 AM

All replies

  • I think issue related to the fact that you are concatenating values dynamically to your SQL string and numbers contain some regional localization information. You need to use parameters instead on concatenation. In this case all

    lacalization information will be handled properly and you also will avoid potential SQL injection vulnerability. See next links with samples how to use parameterized queries.

     

    http://support.microsoft.com/kb/301075/en-us

    Monday, April 28, 2008 9:56 AM
    Moderator
  •  

    Hi, i checked but the problem was the database in MSaccess had the configuration different from the visual studio.

    Fractions appear with " , " when visual studio is with " . ". But i can't find how change that in visual studio. I did it in the database but is not the correct thing to do. Unless here i need Visual studio make the numbres looks like "0,563" and not "0.765". i'd be thankful if you could tell me where i could do that.

    Tuesday, April 29, 2008 12:39 PM
  • As I stated before you need to use parameterized query to avoid this issue with the regional settings. In this case provider will take care of all differences in settings. Do NOT use concatenation to attach values.

     

    Tuesday, April 29, 2008 11:39 PM
    Moderator

  • Ok, but could you explain a little why don't use concatenation to attach values?. does it make more inefficient?
    Thursday, May 1, 2008 2:20 PM
  • It is not only inefficient, it is also dangerous and could lead to syntax errors. For example, you need to concatenate numeric and date values to SQL string. Assuming it is something like

     

    "UPDATE MyTable Set MyColumn=" & MyNumberValue & " WHERE MyDateColumn=" & MyDate Value

     

    To accomplish concatenation operation numeric and date values will be convert to strings first and they will be converted to strings according to regional settings on computer where code is running. For example, you might get something like

     

    UPDATE MyTable Set MyColumn=1,234,567.00 WHERE MyDateColumn=01/02/2008

     

    Now you get at least two issues

    1. You numeric value produced unwanted commas and would lead to syntax error in your SQL statement

    2. What is 01/02/2008, is it January 02, 2008 or is it February 01, 2008?

     

    One more issue with concatenation is SQL injection vulnerability where input from user copuld be executed against database because user could concatenate any SQL statement string to existing query and it could be executed.

     

    Using parameterized query will resolve all the above issues.

     

     

    Friday, May 2, 2008 9:48 AM
    Moderator