Using listbox element for SQL Statement

toddw607 · January 23rd, 2008, 09:28 AM

I'm trying to take a selected element from a listbox and add it into a SQL Statement. Here is the code:

Code:

Protected Sub Button1_Click(ByVal sender as Object, ByVal e as System.EventArgs) Handles Button1.Click

Code:

   Dim obj as Object
   Dim name as String
  obj=lstIPC.SelectedValue
  name = Convert.ToString(obj)
  moduleinteger = 2
  DisplayTable(name)
 
End Sub

Public Sub DisplayTable(ByVal name as String)

   Dim SQL As String = "SELECT Col001 FROM " & name

     The listbox is loading fine but when I click the button it gives me the following error:
       Incorrect syntax near 'FROM'.

   I've tried to send a normal String through as a parameter and that works fine. The issue is that the parameter "name" is not being recognized as a string. The elements within the listbox are pulled from SQL Server DB table and the elements are defined as varchar(50) so I know that's not the problem. Also here is how I populated the listbox in the page_load method:

Code:

    Dim SQL2 As String = "SELECT IPCName FROM IPCNAMES"

Code:

            Dim PubsConn2 As New SqlConnection(ConnStr2)
            Dim TitlesCmd2 As New SqlCommand(SQL2, PubsConn2)
            Dim Titles2 As SqlDataReader

            PubsConn2.Open()
            Titles2=TitlesCmd2.ExecuteReader()
            lstIPC.DataSource = Titles2
            lstIPC.DataBind()
            lstIPC.Items.Insert(0, new ListItem ("-- Choose an IPC --"))
            PubsConn2.Close()

Can anyone give me an idea of why this is giving me the error? TIA.

gbianchi · January 23rd, 2008, 09:56 AM

hi there.. try with selecteditem, or selectedtext.. selectedvalue is used when you have values asociated to every item (like item, code)...

HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========
My programs achieved a new certification (can you say the same?):
WORKS ON MY MACHINE
http://www.codinghorror.com/blog/archives/000818.html
================================================== =========
I know that CVS was evil, and now i got the proof:
http://worsethanfailure.com/Articles...-Hate-You.aspx
================================================== =========

toddw607 · January 23rd, 2008, 10:06 AM

Hi Gonzalo,
    Thanks for responding. I tried to place selecteditem in my code and received the same error and when I attempted to place selectedtext in it gave me the following error:
         'SelectedText' is not a member of 'System.Web.UI.WebControls.ListBox'.
    Could it have something to do with my declarations? Here they are just in case:
                          <%@ Page Language="VB" Debug="True" Strict="False" Explicit="True" Buffer="True"%>
<%@ Import Namespace="System" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.IO" %>
<%@ Import Namespace="System.Data.sqlClient" %>
<%@ Import Namespace="System.Text.RegularExpressions" %>
<%@ Import Namespace="System.Text" %>

    Also, could it have something to do with my conversion technique? Is there something better than
name = Convert.ToString(obj)? Thanks again.

gbianchi · January 23rd, 2008, 10:10 AM

hi.. for a quick check, can you just go back to your original code (the one with the from error), and print the resulting sql string? (the one that has the error..)

HTH

Gonzalo

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
^^Took that from dparsons signature and he Took that from planoie's profile
================================================== =========
My programs achieved a new certification (can you say the same?):
WORKS ON MY MACHINE
http://www.codinghorror.com/blog/archives/000818.html
================================================== =========
I know that CVS was evil, and now i got the proof:
http://worsethanfailure.com/Articles...-Hate-You.aspx
================================================== =========

dparsons · January 23rd, 2008, 10:17 AM

You should use:

lstIPC.SelectedItem.Text

there is no SelectedText property.

Also, I would change this:
Dim obj as Object
Dim name as String
obj=lstIPC.SelectedValue
name = Convert.ToString(obj)

to

Dim name as String = lstIPC.SelectedValue or
Dim name as String = lstIPC.SelectedItem.Text

since, in either case, the return type is string you do not need to convert it.

The reason for your error, I imagine, is as Gonzalo orginally said. By Binding the list as you have you have probably only populated the string portion of the listitems and not the values.

To change that do something like:
lstIPC.DataTextField = "somecolumninyourdatasource"
lstIPC.DataValueField = "somecolumninyourdatasource"
lstIPC.DataSource = ds
lstIPC.DataBind()

hth.

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
.: Wrox Technical Editor / Author :.
Wrox Books 24 x 7
================================================== =========

toddw607 · January 23rd, 2008, 10:52 AM

Hi dparsons,
   I did as Gonzalo said which was bringing out a JS message box on the client side and my SQL statement is not receiving anything from the parameters, it is inputting "Select Col001 From " and that's it. So with my original code there is nothing being passed to the receiving method.
   I tried to do what you said in your message, which i already had the DataTextField and DataValueField declared as attributes within the asp.net section of my code but it was a good idea and safer to place them where I bind the data. But everything else is exactly as you said. Here is my new code:
          Protected Sub Button1_Click(ByVal sender as Object, ByVal e as System.EventArgs) Handles Button1.Click
moduleinteger = 2
  Dim name as String = lstIPC.SelectedItem.Text
  DisplayTable(name)

End Sub
    However, when I run this I get the following error:
      System.NullReferenceException: Object reference not set to an instance of an object.
   Which points to the line:
       Dim name as String = lstIPC.SelectedItem.Text

   It's seems as though it should work. Any thoughts? TIA

dparsons · January 23rd, 2008, 11:03 AM

That leads me to believe that lstIPC.SelectedItem returns null.

This will depend on your program flow but if your page load looks like this:

Protected Sub Page_Load(sender as Object, e as EventArgs)
   DataBindList()
End Sub

It needs to be
Protected Sub Page_Load(sender as Object, e as EventArgs)
   If Not Page.IsPostBack Then
       DataBindList()
   End If
End Sub

Otherwise you will rebind your list on postback and all of the selections you have made in the list box will be gone, resulting in the Null Reference.

hth.

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
.: Wrox Technical Editor / Author :.
Wrox Books 24 x 7
================================================== =========

toddw607 · January 23rd, 2008, 11:11 AM

That was it!! Thank you so much. I used to use that most times as a default but for some reason I strayed. I think I need to set up a few templates again. Thanks again. :-)

dparsons · January 23rd, 2008, 11:12 AM

=] Glad it worked out for you.

================================================== =========
Read this if you want to know how to get a correct reply for your question:
http://www.catb.org/~esr/faqs/smart-questions.html
================================================== =========
.: Wrox Technical Editor / Author :.
Wrox Books 24 x 7
================================================== =========