String conversion for SQL select statement

Question

Using a SELECT statement I query a database and get back a result in the format: name1,name2,name3

Depending on the database entry this result could have any number of names: name1,name2...name(n)

I would like to use this data to query another database like so:

SELECT Name, SerialNo, Model FROM InstrumentTable where ID=1 and InstName IN name1,name2,name3

In order to do this I need to convert name1,name2,name3 to ('name1','name2','name3')

I have tried splitting the String into an array of Strings

    Dim ref1s As String = cmdf1.ExecuteScalar()
    Dim fields() As String = Nothing
    fields = ref1s.Split(",")

and then concatenating them in an array

For i As Integer = 0 To fields.Count - 1
     MsgBox(String.Concat("'", fields(i), "'"))
 Next

but I haven't been able to figure out how to do it yet.

Adding the brackets at the start and end of the string shouldn't be a problem just adding the quotes to each name and separating them with a comma is the issue.

Can anyone help me with this?

If you are using sql server 2008 or above, toucans use table values parameter. — Hamlet Hakobyan
– Hamlet Hakobyan, Commented Dec 13, 2012 at 18:02

Tony Hopkinson · Accepted Answer · 2012-12-13 18:08:16Z

1

You got a bit previous

For i As Integer = 0 To fields.Count - 1
  fields[i] = String.Concat("'",fields[i],"'")
Next

Then

strValue = fields.Join(',')

answered Dec 13, 2012 at 18:08

Tony Hopkinson

20.4k3 gold badges35 silver badges40 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

sacredfaith · Accepted Answer · 2012-12-13 18:05:30Z

1

user557425,

Something like this might point you in the right direction:

Dim lst As List(Of String)
For i As Integer = 0 to fields.Count - 1
    if i = fields.Count - 1 Then
       lst.Add(fields(i) & "'")
    Else
        lst.Add(fields(i) & "','")
    End if
Next

Dim sqlSB As StringBuilder
sqlSB.Append("SELECT * FROM TABLE WHERE BLAH IN(")
For each s As String in lst
     sqlSB.Append(s)
Next

'use the stringbuilder as command text in a SqlCommand...

answered Dec 13, 2012 at 18:05

sacredfaith

8801 gold badge8 silver badges23 bronze badges

Collectives™ on Stack Overflow

String conversion for SQL select statement

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related