CODEWITHSUNDEEP
sqlvb.net
charlie
charlie

Reputation: 481

vb.net trim word from end of string

I am trying to run an SQL query in my vb.net application using a loop

SQL = "SELECT * FROM table WHERE "
For m = 1 To num_array
    SQL = SQL & "type = '" & array(m, 1) & "' OR "
Next

but its showing an OR at the end.

how can i trim this last OR from my query?

Upvotes: 0

Views: 229

Answers (1)

Steve
Steve

Reputation: 216293

This seems a job for a StringBuilder

Dim sql = new StringBuilder("SELECT * FROM table WHERE ")
For m = 1 To num_array
    sql.Append("type = '" & array(m, 1) & "' OR ")
Next

If num_array > 0 Then
    sql.Length -= 4
End If

However you should pay special care to your string concatenation. It seems that your array doesn't contain numbers but string because you are putting everything between single quotes and this means that your type field is a string not a number.
Of course I hope that your array content is not directly inserted by your user otherwise you have a big security risk called Sql Injection. Anyway look at how to build a parameterized query.

Something like this 

Dim prms = New List(Of SqlParameter)()
Dim num_Array = 4
Dim sql = New StringBuilder("SELECT * FROM table WHERE ")
For m = 1 To num_array
    sql.Append("type = @p" & m & " OR ")
    prms.Add(New SqlParameter("@p" & m, array(m, 1)))
Next

If num_array > 0 Then
    sql.Length -= 4
End If 
Dim cmd = new SqlCommand(sql.ToString(), connection)
cmd.Parameters.AddRange(prms.ToArray())

Upvotes: 3

Related Questions