[RESOLVED] [:: Displaying items from Database into ListBox - Please help ::]

[Trusted]

Hello all,

I want to get items into the ListBox from Database on FormLoad.

My code is:

Private Sub Models_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
 
        Dim qry As String
        qry = "SELECT model FROM models"
 
        Dim sqlcon As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=D:\My Personal\VB Projects\Mobile Repairing\Mobile Repairing\repairs.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True")
        
        Dim sqlcmd As New SqlCommand(qry, sqlcon)
        Dim dr As SqlDataReader
 
        sqlcon.Open()
        dr = sqlcmd.ExecuteReader
        dr.Read()
 
        If dr.HasRows Then
            While dr.Read()
                ModelListBox.Items.Add(ToString)
            End While
        End If
 
        dr.Close()
        sqlcon.Close()
 
    End Sub

I am already having three models in database that has to be loaded in ListBox on formload.

Any help will be appreciated.

Thanks in advance.

Trusted

[.paul.]

try this:

Dim qry As String
qry = "SELECT model FROM models"
 
Dim sqlcon As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=D:\My Personal\VB Projects\Mobile Repairing\Mobile Repairing\repairs.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True")
 
Dim sqlcmd As New SqlCommand(qry, sqlcon)
Dim dr As SqlDataReader
 
sqlcon.Open()
dr = sqlcmd.ExecuteReader
dr.Read()
 
If dr.HasRows Then
    While dr.Read()
        ModelListBox.Items.Add(dr.GetString(0))
    End While
End If
 
dr.Close()
sqlcon.Close()

[Trusted]

Hello Paul,

Thanks a lot for your reply.

It worked very nicely... BUT... it is missing the first row from database. That means, I have three rows and it is displaying only two rows.

Trusted

[Trusted]

Hey it worked...

I simply commented 'dr.read() on line number 11 above in your code, and it worked perfectly.



Thanks again,

Can you please help me with updating ListBox items to the database as I will be adding new items from textbox to Listbox.

Trusted

Trusted

[jmcilhinney]

Use a DataAdapter to populate a DataTable and then bind that to the ListBox. Add your new items to the DataTable rather than the ListBox. Use the same DataAdapter to save the changes back to the database. Follow the Database FAQ link in my signature for more information on using ADO.NET.

[Trusted]

Hello JMC,

Thank you for your response.

As I am very new to VS [ as you know that ], this is my first attempt for database, so bad me, I don't understand most of the things and terms.

But with the help of your above post, I did add using INSERT directly to the database, and it is working fine. My code is as follows:

Private Sub btnAddModel_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnAddModel.Click
 
        Dim qry As String
        Dim sqlcon As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=D:\My Personal\VB Projects\Mobile Repairing\Mobile Repairing\repairs.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True")
 
        If TextBox1.Text <> "" Then
            ModelListBox.Items.Add(TextBox1.Text)
 
            qry = "insert into models(model) values('" & TextBox1.Text & "')"
 
            Dim sqlcmd As New SqlCommand(qry, sqlcon)
            sqlcon.Open()
            sqlcmd.ExecuteNonQuery()
            sqlcon.Close()
            TextBox1.Clear()
        Else
            MsgBox("Please enter a Model in the above given box")
        End If
 
    End Sub

Now I got stuck for deleting a particular row from database.

Any help and/or any corrections will be appreciated.

Trusted

[jmcilhinney]

If you want to delete one record at a time then the code is basically the same as you have for inserting except the SQL code will change. That said, you should get into good habits now and start using parameters rather than string concatenation to build SQL statements. The resources in the Database FAQ thread provide examples and there's a link in my signature to a blog post dedicated to the topic.

Rather than editing one record at a time directly in the database, you might want to consider editing the data locally first and then saving as a batch. Again, the Database FAQ resources provide examples, including one in my own CodeBank thread.

[Trusted]

Hi JMC,

Thanks for your response. My app is deleting a particular row successfully now

From your signature, I am TRYING to learn SQL statements. If I find any difficulty, I will ask in a new thread. Still every term is new to me, I have to understand it slowly step-by-step. Thanks all for the help.

Trusted

[jmcilhinney]

If this issue is resolved then please use the Thread Tools menu to mark the thread Resolved. Also note that, if you do have SQL questions down the track, they should be asked in the Database Development forum.

[Trusted]

Sure I will,

But the problem is I tried raising the SQL questions in Database Development Forum, but I get better response in here

Trusted