Use a variable outside of an if/case statement vb.net

Question

I am working in vb.net visual studio express with an sql back end. I have a very long few SQL commands and I need to change a section of the sql command dependent on a number being generated from a textbox. I can either re-write 150 lines of code multiple times to handle the situation or somehow carry a variable down to it outside of an IF statement. Here is what I mean.

If textbox.text = "5" then

    Dim string = "Some text to be used in an sql statement"

Elseif textbox.text = "6" then

    Dim string = "Some other text to be used in an sql statement"

End if (Please note there are more than 2 examples)

Using comm1 as new sqlcommand ("blah blah blah " & String & " blah blah blah", conn1)

So considering the "& String & is outside of my if statement it means that it is not recognized being outside of the if statement. However I dont want to re-write 150 lines of code multiple times so I need a way to carry that string function down to it. Can i do this with the if statement or a case statement?

Prepare an array of your sql commands and use the content of the textbox (once converted to integer) as an indexer to the array — Steve, Aug 06 '15 at 22:05
[Prepared statements, prepared statements. prepared statements.](https://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.prepare%28v=vs.110%29.aspx). Make the text you want one of the parameters. — Cᴏʀʏ, Aug 06 '15 at 22:07
That one idea worked, yea cory, I feel like a scrub from missing that one. i've obviously been at work too long. — Cheddar, Aug 06 '15 at 22:11
[Scope in Visual Basic](https://msdn.microsoft.com/en-us/library/1t0wsc67.aspx) — Ňɏssa Pøngjǣrdenlarp, Aug 06 '15 at 23:22

Cᴏʀʏ · Accepted Answer · 2015-08-06T22:18:19.390

I would really suggest never ever building a SQL command like this (string concatenation). Get in the habit of protecting yourself from SQL injection attacks by familiarizing yourself with prepared statements. Prepared statements require the use of well defined parameters, after which a command is generated (prepared!) for you.

I'll show you an example, which will make the dynamic text one of the parameters in a prepared statement:

Dim textParam As New SqlParameter("@TextParam", SqlDbType.VarChar, 100)

If textbox.Text = "5" Then
    textParam.Value = "ValueWhenTextIs5"
Else If textbox.Text = "6" Then
    textParam.Value = "ValueWhenTextIs6"
Else If
    ...
End If

Using comm1 As New SqlCommand (Nothing, conn1)

    comm1.CommandText = "INSERT INTO Test(Column) VALUES (@TextParam)"
    comm1.Parameters.Add(textParam)

    comm1.Prepare();
    comm1.ExecuteNonQuery();

End Using

[more info](http://stackoverflow.com/questions/5818619/what-does-sqlcommand-prepare-do-and-when-should-it-be-used) — Sam Axe, Aug 06 '15 at 22:50

score 2 · Answer 2 · answered Aug 06 '15 at 22:13

2

Please note there will be an issue with SQL Injection because you are not using parameterized queries, but this is how you can declare a value outside of an if statement so you can use it outside of an if statement.

Dim s as String = String.Empty

If textbox.text = "5" then

    s = "Some text to be used in an sql statement"

Elseif textbox.text = "6" then

    s = "Some other text to be used in an sql statement"

End if ' (Please note there are more than 2 examples)

'... incomplete code
 Using comm1 as new sqlcommand ("blah blah blah " & s & " blah blah blah", conn1)

answered Aug 06 '15 at 22:13

ps2goat

8,067
1
35
68

1

I know about them sql injection attacks but im not terrible worried about it. – Cheddar Aug 06 '15 at 22:14
Cory's answer shows a decent way to avoid SQL injection. SQL Server would ensure that any value passed in as a parameter would not be misinterpreted as a command. E.g., users couldn't enter malicious sql statements by adding comments or closing single quotes on variables – ps2goat Aug 06 '15 at 22:15
ahh yea, i will add the addwith value function.! – Cheddar Aug 06 '15 at 22:16
@Cheddar, it's super easy to do it, though. Once the hole is found, it's like breaking into a website by removing the `disabled` attribute from a website's submit button. – ps2goat Aug 06 '15 at 22:16
Yea im going to add it. – Cheddar Aug 06 '15 at 22:16
@ps2goat: Note that besides avoiding SQL injection attacks, the command parameters take care of formating the parameters the right way. You don't have to care about nested quotes or line breaks in a string, number formats or date formats. Date formats are especially tricky. – Olivier Jacot-Descombes Aug 06 '15 at 22:25
@OlivierJacot-Descombes, nested quotes can be a means to injection. I don't really care about someone having trouble with improper formatting-- security matters most to me, especially in the context of this question, and should be the number one reason for parameterization. – ps2goat Aug 07 '15 at 04:48
@ps2goat: Yes. Parameterization has a lot of advantages. Let's use it whenever possible! – Olivier Jacot-Descombes Aug 07 '15 at 12:07

Use a variable outside of an if/case statement vb.net

2 Answers2