CODEWITHSUNDEEP
sqlvb.netoledbms-access-2013
Aegelis
Aegelis

Reputation: 43

AddWithValue to SQL Statements? VB.Net

I have a bunch of AddWithValue statements. I have been racking my brain to figure out a way to change them into SQL Statements.

This is what it currently looks like;

Dim updateStatement As String =
        "UPDATE Customers SET " &
        "Name = @NewName, "
        "WHERE Name = @OldName "

    Dim updateCommand As New OleDbCommand(updateStatement, connection)
    updateCommand.Parameters.AddWithValue("@NewName", newCustomer.Name)
    updateCommand.Parameters.AddWithValue("@OldName", oldCustomer.Name)

'Connection Open blah blah blah Connection Closed.

I was told a different way of doing it was;

Dim SQL as String = "UPDATE Customers SET" &
"Name = '" Customer.Name "', "

But it refuses to work this way saying that just the two first double quotations is it.

"Name = '" Customer.Name "'," In italics the code is black and not red which refuses to work.

I am not using a database, I am simply pulling it from the file itself (As if it were in Access)

Help me with whatever you can! I wish I could just leave it because if it's not broken don't fix it. But my teacher has a funny way of messing with people.

Upvotes: 0

Views: 14723

Answers (4)

HAJJAJ
HAJJAJ

Reputation: 3787

 ' Add CustomerID parameter for WHERE clause.

    command.Parameters.Add("@ID", SqlDbType.Int)
    command.Parameters("@ID").Value = customerID


    ' Use AddWithValue to assign Demographics. 
    ' SQL Server will implicitly convert strings into XML.

 command.Parameters.AddWithValue("@demographics", demoXml)

CHeck this one please

Upvotes: 0

Joel Coehoorn
Joel Coehoorn

Reputation: 416059

Do NOT do this. EVER. It leaves you vulnerable to a very serious security issue known as Sql Injection.

If you want a different way to add a parameter, I can give you one that will actually improve your code:

Dim updateStatement As String =
    "UPDATE Customers SET " &
    "Name = @NewName, "
    "WHERE Name = @OldName "

'You should also create your connection this way
Using updateCommand As New OleDbCommand(updateStatement, connection)
    'I have to guess at your DB types and lengths
    updateCommand.Parameters.Add("@NewName", OleDbType.VarChar, 50).Value = newCustomer.Name
    updateCommand.Parameters.Add("@OldName", OleDbType.VarChar, 50).Value = oldCustomer.Name
    updateCommand.ExecuteNonQuery()
End Using

There are a certain issues you can run into when using .AddWithValue(), because it forces .Net to guess what the database type of your parameter will be. Sometimes, .Net will guess wrong. Even when it guesses wrong, things usually still work most of the time, but what can happen is that performance will suffer dramatically, because the type mismatch forces per-row conversions or breaks the use of an index.

Upvotes: 3

Oran D. Lord
Oran D. Lord

Reputation: 747

You're missing some well-placed string concatenation operators and spaces to get the syntax right:

Dim SQL as String = "UPDATE Customers SET " & "Name = '" & newCustomer.Name & "'"

BUT, and perhaps you might already notice the security issue with this, if I set Customer.Name to "maliciousUser'; DROP TABLE Customers; -- " what could happen? Different is not necessarily better.

Check this out: From what do sql parameters protect you?

Upvotes: 0

Szymon
Szymon

Reputation: 43023

I'm not going to give you an answer on how to concatenate queries (i.e. concatenate literal values into the query) correctly because concatenating queries is not the correct way.

The correct way is the way you originally used: using parameters.

If you put literal values into your queries, you have the following problems:

  • your code is vulnerable to SQL injection. That reason is enough to never recommend it.

  • you may have problems with types conversion, e.g. for dates, times, numbers as you pass values as strings which have to be converted into proper data types by the database system.

Upvotes: 1

Related Questions