Select query error while retrieve data

Question

jallan2010 0 Light Poster

14 Years Ago

Private Sub PopulateProductList()       
 Dim con As OleDbConnection 
Dim cmd As OleDbCommand      
  Dim dr As OleDbDataReader     
   Dim strSQL As String     
   Dim objListItem As ListItem       
  Try       
     strSQL = "SELECT [Sr No], [First Name] FROM [Lani]"            
con = New OleDbConnection(connectionString)           
 con.Open()             
cmd = New OleDbCommand(strSQL, con)          
  dr = cmd.ExecuteReader()             
lblani.Items.Clear()            
 Do While dr.Read()                
objListItem = New ListItem(dr.Item("First Name").ToString(), _                                           CInt(dr.Item("Sr No")))                lblani.Items.Add(objListItem)         
   Loop           
 If lblani.Items.Count > 0 Then              
  lblani.SetSelected(0, True)          
  End If             
' Close and Clean up objects         
   dr.Close()           
 con.Close()           
 cmd.Dispose()            
con.Dispose()        
 Catch ex As OleDbException            
MsgBox(ex.Message.ToString)        End TryPrivate Sub PopulateProductList()
        Dim con As OleDbConnection
        Dim cmd As OleDbCommand
        Dim dr As OleDbDataReader
        Dim strSQL As String
        Dim objListItem As ListItem

        Try
            strSQL = "SELECT [Sr No], [First Name] FROM [Lani]"

            con = New OleDbConnection(connectionString)
            con.Open()

            cmd = New OleDbCommand(strSQL, con)
            dr = cmd.ExecuteReader()

            lblani.Items.Clear()
        
            Do While dr.Read()
                objListItem = New ListItem(dr.Item("First Name").ToString(), _
                                           CInt(dr.Item("Sr No")))
                lblani.Items.Add(objListItem)
            Loop
            If lblani.Items.Count > 0 Then
                lblani.SetSelected(0, True)
            End If

            ' Close and Clean up objects
            dr.Close()
            con.Close()
            cmd.Dispose()
            con.Dispose()

        Catch ex As OleDbException
            MsgBox(ex.Message.ToString)
        End Try

And the following sub routine is load in the Listbox_selectedindexchanged event

Syntax (Toggle Plain Text)

Private Sub PopulateForm()      
  Dim con As OleDbConnection    
    Dim cmd As OleDbCommand      
  Dim dr As OleDbDataReader        
Dim strSQL As String      
  Dim objListItem As ListItem        
' Dim strID As String       
  Try             
objListItem = CType(lblani.SelectedItem, ListItem)             
strSQL = ("Select * from [Lani] Where [Sr No] = " & txtid.Text)            
con = New OleDbConnection(connectionString)          
  con.Open()             
cmd = New OleDbCommand(strSQL, con)           
 dr = cmd.ExecuteReader()            
 If dr.Read() Then              
  ' Populate form with the data in textbox            
    txtid.Text = dr.Item("Sr No").ToString()           
     txtfirst.Text() = dr.Item("First Name").ToString()                txtmiddle.Text() = dr.Item("Middle Name").ToString()                txtlast.Text() = dr.Item("Last Name").ToString()                txtcomp.Text() = dr.Item("Company Name").ToString()             End If             ' Close and Clean up objects            dr.Close()            con.Close()            cmd.Dispose()            con.Dispose()          Catch e As Exception            MsgBox(e.Message, MsgBoxStyle.Critical, "General Error")        End Try     End Sub      End Sub Private Sub PopulateForm()
        Dim con As OleDbConnection
        Dim cmd As OleDbCommand
        Dim dr As OleDbDataReader
        Dim strSQL As String
        Dim objListItem As ListItem
        ' Dim strID As String

        Try

            objListItem = CType(lblani.SelectedItem, ListItem)

            strSQL = ("Select * from [Lani] Where [Sr No] = " & txtid.Text)
            con = New OleDbConnection(connectionString)
            con.Open()

            cmd = New OleDbCommand(strSQL, con)
            dr = cmd.ExecuteReader()

            If dr.Read() Then
                ' Populate form with the data in textbox
                txtid.Text = dr.Item("Sr No").ToString()
                txtfirst.Text() = dr.Item("First Name").ToString()
                txtmiddle.Text() = dr.Item("Middle Name").ToString()
                txtlast.Text() = dr.Item("Last Name").ToString()
                txtcomp.Text() = dr.Item("Company Name").ToString()
                
            End If

            ' Close and Clean up objects
            dr.Close()
            con.Close()
            cmd.Dispose()
            con.Dispose()


        Catch e As Exception
            MsgBox(e.Message, MsgBoxStyle.Critical, "General Error")
        End Try

    End Sub

   End Sub

And listitem class

Help with Code Tags Visual Basic 4 / 5 / 6 Syntax (Toggle Plain Text)

Public Class ListItem    Private mValue As String   ' Stores a named description of the item    Private mID As Integer     ' Stores a primary key to the record     Public Sub New(ByVal strValue As String, ByVal intID As Integer)        mValue = strValue        mID = intID    End Sub     Public Sub New()        mValue = ""        mID = 0    End Sub     Property ID() As Integer        Get            Return mID        End Get        Set(ByVal Value As Integer)            mID = Value        End Set    End Property     Property Value() As String        Get            Return mValue        End Get        Set(ByVal Value As String)            mValue = Value        End Set    End Property       Public Overrides Function ToString() As String        Return mValue    End FunctionEnd ClassPublic Class ListItem
    Private mValue As String   ' Stores a named description of the item
    Private mID As Integer     ' Stores a primary key to the record

    Public Sub New(ByVal strValue As String, ByVal intID As Integer)
        mValue = strValue
        mID = intID
    End Sub

    Public Sub New()
        mValue = ""
        mID = 0
    End Sub

    Property ID() As Integer
        Get
            Return mID
        End Get
        Set(ByVal Value As Integer)
            mID = Value
        End Set
    End Property

    Property Value() As String
        Get
            Return mValue
        End Get
        Set(ByVal Value As String)
            mValue = Value
        End Set
    End Property

      Public Overrides Function ToString() As String
        Return mValue
    End Function
End Class

And subsequently I want to Design the select query by adding the three column i.e. First Name, Middle Name and Last Name in the Listbox and by selecting the list item that will show in text box.

Thanks in advance for helping me.

vb.net

Edited 14 Years Ago by kvprajapati because: Added [code] tags. For easy readability, always wrap programming code within posts in [code] (code blocks).

4 Contributors
27 Replies
145 Views
1 Month Discussion Span
Latest Post 14 Years Ago Latest Post by pritesh2010

kvprajapati 1,826 Posting Genius

14 Years Ago

>Select query error while retrieve data

Please post that error.

kvprajapati 1,826 Posting Genius

14 Years Ago

Can you post error message description?

If field type of [Sr No] is number then use,

IF IsNumeric(txtId.Text) Then
  strSQL = "Select * from [Lani] where [Sr No]=" & txtid.Text
  ....
End If

If field type if text/varchar (non-numeric) then use,

strSQL = "Select * from [Lani] where [Sr No]='" & txtid.Text & "'"

PS: Try to use/learn parametrized queries or stored procedure.

kvprajapati 1,826 Posting Genius

14 Years Ago

IF IsNumeric(txtId.Text) Then
  strSQL = "Select * from [Lani] where [Sr No]=" & txtid.Text
  
  con = New OleDbConnection(connectionString)
  con.Open()

  cmd = New OleDbCommand(strSQL, con)
  dr = cmd.ExecuteReader()

  If dr.Read() Then
     ' Populate form with the data in textbox
      txtid.Text = dr.Item("Sr No").ToString()
      txtfirst.Text = dr.Item("First Name").ToString()
      txtmiddle.Text = dr.Item("Middle Name").ToString()
      txtlast.Text = dr.Item("Last Name").ToString()
      txtcomp.Text = dr.Item("Company Name").ToString()
  End If
  
  ' Close and Clean up objects
   dr.Close()
   con.Close()
   cmd.Dispose()
   con.Dispose()
End If

kvprajapati 1,826 Posting Genius

14 Years Ago

I think you want to fetch a row when an item from listbox is selected/

...
 IF  Not IsNothing(ListBox1.SelectedValue) Then
  strSQL = "Select * from [Lani] where [Sr No]=" & ListBox1.SelectedValue
  
  con = New OleDbConnection(connectionString)
  con.Open()

  cmd = New OleDbCommand(strSQL, con)
  dr = cmd.ExecuteReader()

  If dr.Read() Then
     ' Populate form with the data in textbox
      txtid.Text = dr.Item("Sr No").ToString()
      txtfirst.Text = dr.Item("First Name").ToString()
      txtmiddle.Text = dr.Item("Middle Name").ToString()
      txtlast.Text = dr.Item("Last Name").ToString()
      txtcomp.Text = dr.Item("Company Name").ToString()
  End If
  
  ' Close and Clean up objects
   dr.Close()
   con.Close()
   cmd.Dispose()
   con.Dispose()
End If
....

kvprajapati 1,826 Posting Genius

14 Years Ago

Use lblani instead of ListBox1.

Reply to this topic

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.

jallan2010 0 Light Poster · Answer 1 · 2010-02-17T11:02:05+00:00

It on the following line while retrieve data from database line

strSQL = ("Select * from [Lani] Where [Sr No] = " & txtid.Text)

I will appreciate your reply,

Thank you

jallan2010 0 Light Poster · Answer 2 · 2010-02-18T10:49:31+00:00

Sir, Further it will show me an error on the following line
I have use Isnumeric method
cmd = New OleDbCommand(strSQL, con)
Command text was not set for the command object.

If you kind help me for that please,

Thank you

jallan2010 0 Light Poster · Answer 3 · 2010-02-18T11:26:38+00:00

Sir,
Everything is alright but while selecting item on Listbox it will not read on my textbox. In listbox there are 5 items but while select the item is will not read on five textbox please help.

what can i do next.

Please help me

jallan2010 0 Light Poster · Answer 4 · 2010-02-18T11:50:03+00:00

Sir,

Listbox1.selectedvalue doesn't work.

Please sir help me,

Thanks

jallan2010 0 Light Poster · Answer 5 · 2010-02-18T12:09:39+00:00

SIR,

Written mistake sir I have use lblani instead of Listbox1.

Thank you

jallan2010 0 Light Poster · Answer 6 · 2010-02-18T12:20:34+00:00

Sir,
Sorry Sir I have already written in my code lblani instead of Listbox1.

Please help me

Thank you

kvprajapati 1,826 Posting Genius Team Colleague · Answer 7 · 2010-02-18T12:22:39+00:00

kvprajapati 1,826 Posting Genius

14 Years Ago

Please post your complete code here.

jallan2010 0 Light Poster · Answer 8 · 2010-02-18T12:31:33+00:00

jallan2010 0 Light Poster

14 Years Ago

Sir,

Here is my project complete winrar zip archive file sir.

Please help me.

This attachment is potentially unsafe to open. It may be an executable that is capable of making changes to your file system, or it may require specific software to open. Use caution and only open this attachment if you are comfortable working with zip files.

My_Pro.zip (81.57 KB)

kvprajapati 1,826 Posting Genius Team Colleague · Answer 9 · 2010-02-18T12:42:50+00:00

Take a look,

Private Sub lblani_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lblani.SelectedIndexChanged
        If Not IsNothing(lblani.SelectedValue) Then
            PopulateForm(lblani.SelectedValue)
        End If
    End Sub
  
    Private Sub PopulateForm(ByVal srno As String)
        Dim con As OleDbConnection
        Dim cmd As OleDbCommand
        Dim dr As OleDbDataReader
        Dim strSQL As String

        If IsNumeric(srno) Then
            strSQL = "Select * from [Lani] where [Sr No]=" & srno

            con = New OleDbConnection(connectionString)
            con.Open()

            cmd = New OleDbCommand(strSQL, con)
            dr = cmd.ExecuteReader()

            If dr.Read() Then
                ' Populate form with the data
                txtid.Text = dr.Item("Sr No").ToString()
                txtfirst.Text = dr.Item("First Name").ToString()
                txtmiddle.Text = dr.Item("Middle Name").ToString()
                txtlast.Text = dr.Item("Last Name").ToString()
                txtcomp.Text = dr.Item("Company Name").ToString()
            End If
            dr.Close()
            con.Close()
        End If
    End Sub

jallan2010 0 Light Poster · Answer 10 · 2010-02-18T13:26:53+00:00

Sir,
It still doesn't show me the value in the textbox.

Please help me,

Thank you

kvprajapati 1,826 Posting Genius Team Colleague · Answer 11 · 2010-02-18T13:53:52+00:00

Please change following (ListItem class),

Public Overrides Function ToString() As String
        Return mID.ToString()
 End Function

vvashishta 0 Newbie Poster · Answer 12 · 2010-02-18T15:20:05+00:00

It on the following line while retrieve data from database line
strSQL = ("Select * from [Lani] Where [Sr No] = " & txtid.Text)
I will appreciate your reply,
Thank you

if the value in txtid is integer than its ok else..
strSQL = ("Select * from [Lani] Where [Sr No] = ' " & txtid.Text & " ')

and also please send in the exact error whic you are getting.

jallan2010 0 Light Poster · Answer 13 · 2010-02-18T16:13:56+00:00

Sir,
Still can't get the value on the textbox sir please

Please help me,

Thank you

kvprajapati 1,826 Posting Genius Team Colleague · Answer 14 · 2010-02-18T16:27:33+00:00

Do following changes in your code,

With ListItem class. Do not follow my post #17.

Public Overrides Function ToString() As String
        Return mValue
    End Function

SelectedIndexChanged handler of lbllani (listbox)

Private Sub lblani_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles lblani.SelectedIndexChanged
        If Not IsNothing(lblani.SelectedItem) Then
            Dim item As ListItem = lblani.SelectedItem
            PopulateForm(item.ID)
        End If
    End Sub
  
    Private Sub PopulateForm(ByVal srno As String)
        Dim con As OleDbConnection
        Dim cmd As OleDbCommand
        Dim dr As OleDbDataReader
        Dim strSQL As String

        If IsNumeric(srno) Then
            strSQL = "Select * from [Lani] where [Sr No]=" & srno

            con = New OleDbConnection(connectionString)
            con.Open()

            cmd = New OleDbCommand(strSQL, con)
            dr = cmd.ExecuteReader()

            If dr.Read() Then
                ' Populate form with the data
                txtid.Text = dr.Item("Sr No").ToString()
                txtfirst.Text = dr.Item("First Name").ToString()
                txtmiddle.Text = dr.Item("Middle Name").ToString()
                txtlast.Text = dr.Item("Last Name").ToString()
                txtcomp.Text = dr.Item("Company Name").ToString()
            End If
            dr.Close()
            con.Close()
        End If
    End Sub

jallan2010 0 Light Poster · Answer 15 · 2010-02-18T16:59:23+00:00

Sir,

Thank you so much it will work.
It will display only First Name
I want to show in list box with First Name, Middle Name and Last Name i.e. First Name Middle last Name
Mick John Grown
how to concetinate Select query.

jallan2010 0 Light Poster · Answer 16 · 2010-02-18T17:00:41+00:00

HOw can I show into list box all the three fields of First Name,middle name and last name

Please help me.

Thank you

kvprajapati 1,826 Posting Genius Team Colleague · Answer 17 · 2010-02-18T17:12:02+00:00

Private Sub PopulateProductList()
        ' This procedure populates the list box on the
        ' form with a list of available products from the
        ' Northwind database.

        Dim con As OleDbConnection
        Dim cmd As OleDbCommand
        Dim dr As OleDbDataReader
        Dim strSQL As String
        Dim objListItem As ListItem

        Try
            ' Build Select statement to query product names from the products
            ' table.
            strSQL = "SELECT [Sr No], [First Name], [Middle Name], [Last Name] FROM [Lani]"

            con = New OleDbConnection(connectionString)
            con.Open()

            cmd = New OleDbCommand(strSQL, con)
            dr = cmd.ExecuteReader()

            lblani.Items.Clear()

            ' Loop through the result set using the datareader class.  
            ' The datareader is used here because all that is needed 
            ' is a forward only cursor which is more efficient.             
            Do While dr.Read()
                objListItem = New ListItem(dr.Item("First Name").ToString() & " " & dr.Item("Middle Name").ToString() & " " & dr.Item("Last Name").ToString(), _
                                           CInt(dr.Item("Sr No")))
                lblani.Items.Add(objListItem)
            Loop
            If lblani.Items.Count > 0 Then
                lblani.SetSelected(0, True)
            End If

            ' Close and Clean up objects
            dr.Close()
            con.Close()
            cmd.Dispose()
            con.Dispose()
        Catch ex As OleDbException
            MsgBox(ex.Message.ToString)
        End Try
    End Sub

jallan2010 0 Light Poster · Answer 18 · 2010-02-19T10:44:23+00:00

Sir,
Many Many Thank you so much sir.

Can you please help me for my further error which is about the same project. In this project I have use two form first form is Form1 I have create button on it for Enter Lani button click open Member_LAg form. The problem is while I am close Member_LAg form then again I have to click the same Lani Button one error has generate it i.e.

Cannot access a disposed object.
Object Name: 'Members_Lag'.

Can you please help me.

I want to open Members_Lag on the same page i.e. Form1 while i am click on Enter Lani and then while I am close the the second form i.e Members_lag the whole project will close.

I am very appriciate your reply,

Thank you,

kvprajapati 1,826 Posting Genius Team Colleague · Answer 19 · 2010-02-19T10:52:04+00:00

If MembersLag.IsDisposed Then
            MembersLag = New Members_Lag
        End If
     MembersLag.Show()

jallan2010 0 Light Poster · Answer 20 · 2010-02-19T11:03:17+00:00

Sir,
Thank you so much

My another question is in txtlani textbox I have enter only digit i.e. 0 to 9 and currency formate i.e. 1,000.00 and 1,00,000.00 it is possible in text box.

Thank you,

kvprajapati 1,826 Posting Genius Team Colleague · Answer 21 · 2010-02-19T11:15:20+00:00

Thanks! If you have any question which is not related to opening post then you have to mark this thread as "Solved" and put your new question in new thread.

>it is possible in text box.

It is depends on the data type of field.

pritesh2010 30 Posting Whiz in Training · Answer 22 · 2010-03-22T12:12:26+00:00

please help me someone i have two text box in ,my form and i want to retrieve databased on the 3 text box condition. so i'm doing like this code
dim con as sqlconnection
dim ds as dataset,
dim da as sqldataadapter
dim cmd as sqlcommand
'on textbox key down event

private sub txtbox3_keydown 
'create a connection
con.open
cmd=new sqlcommand("Select  no, name from details where id like' "  & textbox3.text  &"'",con)
dr=cmd.executereader()
while(dr.read())
text box1.text=dr(1).tostring()
text box2.text=dr(2).tostring(0
end while
dr.close()
con.close()
end sub

now problem is it works but when i press for first record it show last record as default and after enter it show the correct record. i want directly want the correct records not want other records.

please help me it's very urgent.
post on my mail pleas pritesh_271187@yahoo.co.in