Is this possible???

Question

Hello,

My question relates to the following select statement:

Select Report_description from Report where Report_name = (grab this value from the item selected from a listbox)

I wonder whether it would be possible to make the above statement a stored procedure but instead of filling in the last value in the bracket, I would like to grab that value from else where, for example from an item from a listbox which has been selected by the user.

Answer 1

Hi is Dude

we use this n number of time. The thing you need to do is, just create the comma separated value of the selected item  list in the front end.

As an Example

list selected values as

'i','am','a',boy'       (you need to do this in the front end itself)

in query do like this

Select Report_description from Report where Report_name in('i','am','a',boy')

you need to use the IN operator to select the selected values for the Report table

 

Regards,

Thanks.

Gurpreet S. Gill

 

 

 

Answer 2

Hi Gill,

Its great to know that this can be done. Unfortunately I am a newbie to all this. Could you please elaborate??? For example if I was using ASP.NET, and I suppose alll this code would go into the code behind file of the list box control? So what would the code actually look like??? And would I just leave the last value in the stored procedure as a blank space??

Thank you so much

Answer 3

I cant say much about the ASP.NET, but this code works for me.

here the ListBox1 is the List box from where you want to collect the values, CSV is string variable, used in IN clause of SQL

Try this

 

Dim CSV As String, SQL As String, i As Integer

CSV = ""

'Loop to all the Items in the ListBox1

For i = 0 To ListBox1.Items.Count - 1

'Check if selected or not

If ListBox1.Items(i).Selected Then

' if selected, make the comma separated value single Quote around it

CSV = CSV & "'" & ListBox1.Items(i).Text & "' , "

End If

Next

' Ignore the last extra comma

CSV = Left(CSV, Len(CSV) - 3)

' Create the SQL command

SQL = "Select Report_description from Report where Report_name IN( " & CSV & " )"

' Your codes goes here

' Use the SQL variable to execute the query

'

 

 

Kiind Regards,

Gurpreet S. Gill

Answer 4

ohhhh, PLEASE IGNORE THIS  post twice same

Dim CSV As String, SQL As String, i As Integer

CSV = ""

'Loop to all the Items in the ListBox1

For i = 0 To ListBox1.Items.Count - 1

'Check if selected or not

If ListBox1.Items(i).Selected Then

' if selected, make the comma separated value single Quote around it

CSV = CSV & "'" & ListBox1.Items(i).Text & "' , "

End If

Next

' Ignore the last extra comma

CSV = Left(CSV, Len(CSV) - 3)

' Create the SQL command

SQL = "Select Report_description from Report where Report_name IN( " & CSV & " )"

' Your codes goes here

' Use the SQL variable to execute the query

'

 

Kind Regards,

Gurpreet S. GIll

 

Answer 5

thank you very much gill!!!