VB.Net using & _ to carry to next line

Question

Error Error 1 Operator '&' is not defined for types 'String' and 'System.Windows.Forms.TextBox'.

What is wrong with this????!??

       SQL = "UPDATE ATG_PP_QTE_HEAD SET " & _
            "PART = '" & txtPart.Text & "', " & _
            "LOCATION = '" & txtLoc.Text & "', " & _
            "DESCRIPTION = '" & txtDescription.Text & "', " & _
            "CUSTOMER = '" & txtCustID.Text & "', " & _
            "CONTACT_NAME = '" & txtContactName.Text & "', " & _
            "CONTACT_PHONE = '" & txtPhone.Text & "', " & _
            "CONTACT_EMAIL = '" & txtEmail.Text & "', " & _
            "LEAD_TIME = '" & txtLead.Text & "', " & _
            "SETUP = " & txtSetup.Text & ", " & _
            "WEIGHTPP = " & txtPCWT.Text & ", " & _
            "NOTES = '" & txtNotes.Text & "', " & _
            "LAST_MODIFIED = '" & DateTime.Now & "', " & _
            "LABOR_RATE = " & txtLabor.Text & ", " & _
            "OVERHEAD = " & txtOH.Text & ", " & _
            "GA = " & txtGA.Text & ", " & _
            "SORT_CODE = '" & txtSortCode.Text & "', " & _
            "REFERENCE = '" & txtReference.Text & "', " & _
            "PL = '" & txtPL.Text & "', " & _
            "CUST_DRAW_NO = '" & txtCustDraw.Text & "', " & _
            "COMMISSION = " & txtCommission.Text & ", " & _
            "PCWT = " & txtPCWT & _
            "WHERE QUOTE_ID = " & txtQuoteID.Text

Seeing as how no one has access to all your variables, it'll be pretty hard for anybody to do a better job debugging this than you. Why don't you try removing all but one of the columns and then add them back 1 at a time until you find which one is causing the error to be thrown. — Dean MacGregor, Jun 07 '15 at 15:44
@Bjørn-RogerKringsjå - Is there a reason to be rude? I am just beginning with VB.Net, hence the reason for my inquiry. In my opinion, you shouldn't comment on something, unless you have a question, or potential reason... I actually figured out the ".text" issue after I posted it, and tried to Answer my own question on here, and it wouldn't let me.... Then, David, informed my about using parameter queries. The environment that this application is going in, doesn't really pose a threat. But, it seems to be good practice, so I will go with David's recommendation. — Brian Lefler, Jun 07 '15 at 16:00
http://stackoverflow.com/questions/542510/how-do-i-create-a-parameterized-sql-query-why-should-i — Ňɏssa Pøngjǣrdenlarp, Jun 07 '15 at 17:16

David · Accepted Answer · 2015-06-07T15:44:02.567

4

What is wrong with this????!??

Quite a bit, actually. But let's start with the error itself...

On this line:

"PCWT = " & txtPCWT & _

You're trying to concatenate a TextBox to a String. As the error states, you can't do that. Perhaps you meant to use the .Text property:

"PCWT = " & txtPCWT.Text & _

Now, what else is wrong?

First, your code is highly vulnerable to SQL injection attacks. You're going to want to use parameterized queries instead of executing user input as code.

Second, using parameterized queries will make the code a lot easier to read and support, which will make errors like this much easier to find.

Third, on this line there's a significant potential for bugs:

"LAST_MODIFIED = '" & DateTime.Now & "', " & _

Using parameterized queries will remove the culture-dependent string representations from the query and use the actual DateTime data in the query. And you should also get into the habit of using DateTime.UtcNow instead, as having a consistent non-timezone-dependent value is going to make things a lot easier when you have to deal with multiple time zones.

edited Jun 07 '15 at 15:44

answered Jun 07 '15 at 15:39

David

208,112
36
198
279

@BrianLefler: Currently your code is executing user input as code. This is extremely dangerous, as it allows any user to write any SQL code they want to execute on your database. A parameterized query treats user-input values as *values* instead of as *code*. A quick Google search will find many, many examples and tutorials. – David Jun 07 '15 at 15:45
Let me ask something.... I can't see how the user can execute code, against SQL, since my code explicitly (Literal string) "UPDATE ATG_PP_QTE_HEAD" "SET COLUMN = '?User Input'" So, the only vulnerability is that the user, could enter a poor value in one of the columns, which I prevent in other segments of code... Right? Using the above example, how could this be sabotaged? – Brian Lefler Jun 07 '15 at 15:49
2

@BrianLefler: What if the user inputs the string: "';DROP TABLE ATG_PP_QTE_HEAD;--" Your UPDATE query will set that value to an empty string, then drop the table, then ignore the rest of the query. The string that you send to the database is the code being executed by the database. If that string is made of user input, then the user is inputting the code that you're executing against the database. – David Jun 07 '15 at 15:52
Wow... Ok.. That makes sense. I missed that.... Ok, so what you are talking about, is "Stored Procedures" (Pervasive SQL) and passing parameters to the Procedure. Right? – Brian Lefler Jun 07 '15 at 15:55
@BrianLefler: No, I'm not talking about stored procedures. I'm talking about query parameters. If you're using old ADO.NET for your data access, you can start here: https://msdn.microsoft.com/en-us/library/ms254953 – David Jun 07 '15 at 15:57
Ahh. No, I am using ODBC. I will still review the link – Brian Lefler Jun 07 '15 at 16:02
@ David, can you look at the "Answer" I posted below, and tell me if I am picking up on your suggestion correctly? – Brian Lefler Jun 07 '15 at 16:24
@BrianLefler: It's a considerable improvement, yes. Though I don't think you need the quotes around the parameter placeholders in the UPDATE query. Please understand, however, that what you posted isn't really an "answer", and that Stack Overflow isn't really designed for ongoing conversations like this. It's likely that people may down-vote your "answer" and flag it for moderator attention to clean it up, so you may want to delete it. – David Jun 07 '15 at 16:26
I deleted it. There just wasn't enough room to post it in a comment, to show you. I put the single quotes, around the string types. Thanks! – Brian Lefler Jun 07 '15 at 16:29

VB.Net using & _ to carry to next line

1 Answers1