Why will MS Access DB not update?

Question

Another SQL Query issue that i am having. If anyone could help it would be appreciated.

No errors are thrown (using the Try syntax) however it is not updating the database.

    Dim con As OleDbConnection = New OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source='\\$$$$\$$$$\$$$$.accdb';")

Dim str As String
        str = "update Layer_1 set 1=@1, 2=@2, 3=@3, 4=@4, 5=@5, 6=@6, 7=@7, 8=@8, 9=@9, 10=@10 where ID=@id"
        Dim cmd As New OleDbCommand(str, con)

    cmd.Parameters.AddWithValue("@1", val2.Text)
    cmd.Parameters.AddWithValue("@2", val3.Text)
    cmd.Parameters.AddWithValue("@3", val4.Text)
    cmd.Parameters.AddWithValue("@4", val5.Text)
    cmd.Parameters.AddWithValue("@5", val6.Text)
    cmd.Parameters.AddWithValue("@6", val7.Text)
    cmd.Parameters.AddWithValue("@7", val8.Text)
    cmd.Parameters.AddWithValue("@8", val9.Text)
    cmd.Parameters.AddWithValue("@9", val10.Text)
    cmd.Parameters.AddWithValue("@10", val11.Text)
        cmd.Parameters.AddWithValue("@ID", SysID.Text)
    con.Open()
    cmd.ExecuteNonQuery()
    con.Close()

So the Val[#].Text is a textbox, whilst the SysId is a label,

I also have each Param written in the following Syntax, just to see if there is a problem with my code. But its the same output, no DB update but no errors.

I do have a smaller variation of this codes which works but i am not sure why, as it is an exact copy with more expressions added in.

Dim str As String
str = "update FDSL set Hostname=@Hostname, Owner=@Owner where ID=@id"
Dim cmd As New OleDbCommand(str, con)
cmd.Parameters.AddWithValue("@Hostname", TextBox1.Text)
cmd.Parameters.AddWithValue("@Owner", TextBox2.Text)
cmd.Parameters.AddWithValue("@ID", textbox6.Text)
con.Open()
cmd.ExecuteNonQuery()
con.Close()

Any Ideas?

Cheers, Tad

What if you add that `@ID` parameter last, as it is the last in the query? (Maybe Access only uses positional params, not named ones) — Hans Keﬆing, Dec 09 '13 at 11:31
I would guess the same as @HansKesting order of parameters might matter. — , Dec 09 '13 at 11:33
are you running debug or release version? If you are debugging probably you are updating the database copy located at bin\debug folder on your project directory — ɐsɹǝʌ ǝɔıʌ, Dec 09 '13 at 12:01
Good idea, however my data source is an external central DB, so it's not being saved to the debug folder, rather an actual share file. — Tad, Dec 09 '13 at 12:14
Ummmm your code seems to be correct, but I would try to open the connection before `OleDbCommand` is declared — ɐsɹǝʌ ǝɔıʌ, Dec 09 '13 at 12:21
If i open the connection before the command is declared, will it not just open the con, see there is nothing to do, then close it? Just a heads up it didn't work — Tad, Dec 09 '13 at 13:39
I meant that I would first open the connection. Once open, declare the `OleDbCommand` passing it an open connection as argument. Then add the parameters to the command and finally `ExecuteNonQuery` and close the connection — ɐsɹǝʌ ǝɔıʌ, Dec 09 '13 at 14:23
Very very strange. I have exactly the same code in a project and it's working fine. The only thing I can think of is to check the connection string — ɐsɹǝʌ ǝɔıʌ, Dec 09 '13 at 15:31
The connection string is fine, since it is actually pulling the data to begin with. Deffinately strange, hence the post. Thanks tho! — Tad, Dec 09 '13 at 15:34

score 2 · Accepted Answer · answered Dec 09 '13 at 11:58

2

In the one you have that works you have cmd.Parameters.AddWithValue("@ID", textbox6.Text) which is linking to a text box. The one that does not work has cmd.Parameters.AddWithValue("@ID", SysID.Text) which you say links to a label. I can't see why that would make a difference but could you try a read-only textbox just to see if that works. Also are your IDs strings or numeric. I tend to convert my ID params into integers rather than just use numeric string values directly from a textbox. e.g. cmd.Parameters.AddWithValue("@ID", CInt(SysID.Text))

answered Dec 09 '13 at 11:58

Mych

2,527
4
36
65

This actually solved a part of the problem, it was reading the Sysid as a string when it was expected as integer. I had to change the DB to allow it but thanks for the tip! – Tad Dec 10 '13 at 09:22
You say solved part of the problem... do you still have another issue or have you solved that yourself? – Mych Dec 10 '13 at 12:02
Your idea solved half of the issue, i resolved the other bit myself after some thinking. As i said it was reading the SYSid as string, but it was being stored as double. So using your solution to declare it as an integer and changing the back end made it work. – Tad Dec 10 '13 at 14:29

score 0 · Answer 2 · edited May 23 '17 at 10:25

0

Your parameters cannot start with numbers.

See here:
SQL Server - Invalid characters in parameter names

Instead of names like @1, @2, etc.,
use @p1, @p2 etc.
(That way you can still have them generated dynamically.)

edited May 23 '17 at 10:25

Community

1
1

answered Dec 09 '13 at 11:31

Yehuda Shapira

8,460
5
44
66

They don't actually start with numbers, I assigned that for data protection. Thanks anyway! – Tad Dec 09 '13 at 11:35

score 0 · Answer 3 · answered Dec 09 '13 at 15:57

0

OleDbCommand does not support named parameters. Use this instead:

update FDSL set Hostname=?, Owner=? where ID=?

And add the parameters in the order that they appear in the query.

However you should be getting an error in that case, so something else may be swallowing the exception.

answered Dec 09 '13 at 15:57

D Stanley

149,601
11
178
240

Thats the thing, the extract that your refering to actually works as is. It was done this way to prevent SQLinjection as suggested. The main Exerpt however, does no work. – Tad Dec 09 '13 at 16:01

Why will MS Access DB not update?

3 Answers3