SQL Update Error

[daves2369]

Hi there

Im programming using a SQL update statement in vb.net and I keep getting a syntax error in UPDATE statement

I haven't got that much experience with SQL and I cant seem to spot the problem

Im using the oledb connection to connect to a access database

Heres the code:

Private Sub adminuserchangename_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles adminuserchangename.Click
        Dim getrecord As Integer = adminuserlist.SelectedIndex
        Dim newname As String


        Do
            newname = InputBox("What would you like the new name to be")
            If newname = "" Then
                MsgBox("Invalid entry please try again")
            End If
        Loop Until newname > ""


        Dim objcommand As New OleDbCommand("UPDATE tblusers SET User=? WHERE ID=?", Conn)


        objcommand.Parameters.AddWithValue("@User", newname)
        objcommand.Parameters.AddWithValue("@ID", getrecord + 1)


        Conn.Open()


        Try
            objcommand.ExecuteNonQuery()
        Catch SQLError As OleDb.OleDbException
            MessageBox.Show(SQLError.Message)
        End Try
        Conn.Close()
        objcommand.Parameters.Clear()


        FillDataSetAndViewusers()
        adminuserlist.Items(getrecord) = newname




    End Sub

Anyone spot the problem?

Cheers

 

[jmcilhinney]

My immediate guess would be that User is a reserved word. You must either use a different column name or escape the column name in SQL code. I'd suggest a different name because UserName or something like that is likely to be more precise.

 

[daves2369]

My immediate guess would be that User is a reserved word. You must either use a different column name or escape the column name in SQL code. I'd suggest a different name because UserName or something like that is likely to be more precise.

Cheers that was the problem

 

[SLPx]

use [User] instead 'User'

I.e

UPDATE tblusers SET [User]=? WHERE ID=?

 

[Herman]

It's a bad habit to use SQL reserved words anyways.

 

[SLPx]

With all respect mate, I agree with you wholly.

still I found it useful in certain ways. For Ex, In one of an application the customer used the term 'TOP' to indicate the short-term for 'Term Of Payment'
Yet, no other option than [TOP] in this case.

Yet, anyways its a bad habit to use SQL reserved words direct for field names.

Moreover, its again a very bad habit to include SQL text within programme body.
The best thing is, put all SQL in a stored procedure and call the SP from programme.

You not only isolate DB from app, SQLServer itself checks all SQL syntax's, and reduce more round trips to server. A well defined execution plan upfront and then performance hits.
(Usually an update to User in this case, will look up such user from DB, checks other integrity constants etc., which if lies in server, quick response and if in the client [APP] then number of round trip network calls for each step)

 

[jmcilhinney]

For Ex, In one of an application the customer used the term 'TOP' to indicate the short-term for 'Term Of Payment'
Yet, no other option than [TOP] in this case.

Why not just use TermOfPayment? There isn't really a need for acronyms and abbreviations.

Moreover, its again a very bad habit to include SQL text within programme body.
The best thing is, put all SQL in a stored procedure and call the SP from programme.

That's not really true. I'm not saying that you shouldn't use sprocs but they aren't inherently better. That's a DBA's point of view but not necessarily a developer's. The first issue that comes to mind is shifting business logic into not just your data layer but your data store. If you want to use an ORM like Entity Framework then there's no writing SQL at all and it will all be generated for you. This is just another development compromise where there is no single perfect solution and you have to weigh up the pros and cons of each option to find the best for your circumstances.

 

[SLPx]

Well admin, in the first instance, its directly a query output like 'TermOfPayment AS [TOP]' for direct report output, so yet there are ways, it didn't excericise for quick responses.

anyway, for sp thing, Yes its extending business logic to storage, And its what it is. SPs are Database interface and not the database.
In inbuilt SQL, You cannot change database without changing the programme in your paradigm and OOP is lost at large scale. Still its possible to do sql upfront, the whole application will loss performance by not using right thing at the right place.

For ex, If you update a new book title into the database, What it really matters are Book title updated and Author name updated if its not already there, with intergrity constants checked. In SQL included app, This is number of processing and function calls with the tables and fields must need to maintain their field names, internal table structure, or generate Views for the entire life cycle of the application as is, For update, programme need to check for book exists, book author exists, user authored for ammend this book, etc.

Which then of course three network calls, waiting for each individual response to be completed to insert the new book and SQL server/Oracle just dont know what SQL they might receive in the next second and had to recompile the query excution plans again and again making your responses from the database be slow and reducing performance of the app itself. Network related problems like so much of remote location, Number of application instances running in the network, certain service points are connected with so noisy or low bandwidth networks etc, will escalate the problem.

Then a simple three line sql becomes 50 lines network calls. certain objects to be repeated again and again for no reason. App to hang untill all roundtrips completed, etc so much burden and performance loopholes.

Application need to focus on updating the author and maintain User interface smooth
i.e
booktitle.update(<booktitle details>, <Author Details>)

if booktitle.isUpdateOk=true then msgbox("Booktitle added successfully")

[SP::
IF NOT EXISTS(Author) begin
>update/insert author

IF NOT EXISTS(Booktitle) begin
>>update/insert booktitle

>>return booktitle
end else begin
>>return matching booktitle
end
end else begin
IF NOT EXISTS(Booktitle) begin
>update/insert booktitle

>return booktitle
end else begin
>return matching booktitle
end
end


:SP]

Not checking db intergrity constants and waiting
ie.
If NOT Author.isAuthorExists=true then author.update
if NOT booktitle.isBookTitleExists =true then booktitle.update()

if Authour.isupdateok and booktitle.isUpdateOk then msgbox("Booktitle added successfully")

DB SP need to check the integrity and raise any errors or update as accordingly.
Above ex will reduce network use to 50% and increase application performance by 200%

For a large scale application it matters.

Anyway, We have options to isolate or combine levels.
for me the best way was
presentation layer classes---Business layer and service layer Classes--I/o and Network classes---Database interface classes (wrap SP as function in Net where SP receive SP's parameter values in function argument)---Database SP--Database itself.

And also, Developer need to Be the DBA, Web Developer, UI Engineer, Tester, and User and in the reverse too. Its the whole picture matters. Not individual component itself. Best written one thing with poorly written one thing will provide us an Ugly user experience, From the storage location to User interface, isolation, coupling and cohesion, doing what a function itself need to do and not depending or coding some other area of the programme inside that function matters.