SQL Problem

[NOTSOSURE]

Hey all

im having problems filling a ListView1 object using another form each time i click the button to fire the event nothing get's displayed in the listview1 object can any 1 help

I have one form called form2 ( and form4 (The form where the search criteria is selected using textboxes)

The database is called Pictures ad the fields are pictureID, pictureTitle, pictureDescription, pictureData

here is my code for the button on form4 to load data into the listview1 object. can any 1 help

    Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click


Dim frm2Search As New Form2
            Dim mySqlCommand As SqlCommand
            mySqlCommand = New SqlCommand
            mySqlCommand.Connection = Me.SqlConnection1
            mySqlCommand.CommandType = CommandType.Text
            mySqlCommand.CommandText = "SELECT pictureID, pictureTitle, pictureDescription, pictureData FROM Pictures WHERE pictureTitle LIKE @pictureTitle;"
           mySqlCommand.Parameters.Add(New SqlParameter("@pictureTitle", SqlDbType.Text))
          mySqlCommand.Parameters("@pictureTitle").Value = "%" + Me.TextBoxTitle.Text + "%"


          frm2Search.ListView1.Items.Clear()
          Try

                mySqlCommand.Connection.Open()
                Dim myDataReader As SqlDataReader
                myDataReader = mySqlCommand.ExecuteReader(CommandBehavior.Default)

                Dim pic As picture

                While myDataReader.Read
                    pic = New picture
                    pic.pictureID = myDataReader.GetValue(0)
                    pic.pictureTitle = myDataReader.GetValue(1)
                    pic.pictureDescription = myDataReader.GetValue(2)
                    pic.pictureData = New System.IO.MemoryStream(myDataReader.GetValue(3), True)

                    Dim i As ListViewItem = New ListViewItem
                    i.Text = pic.pictureTitle
                    i.Tag = pic
                    frm2Search.ListView1.Items.Add(i)

                End While
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            Finally
                mySqlCommand.Connection.Close()
            End Try
    End Sub

[<ABX]

TIP: name your controls and forms not just the instances

Did you notice that in the code you posted that it does not say "ListView2" anywhere your referencing "ListView1"?

[NOTSOSURE]

SORRY I MENT TO SAY LISTVIEW1 can u still spot where i'm going wrong?

[NOTSOSURE]

would i place the SQLConnection1 on form where the search criteria is being entered or on the form that wioll be getting the data?

[<ABX]

Ok I understand your problem now...

you have already loaded an instance of Form2 and it is being displayed when you call this code, right?

Your problem is that you are not referencing that instance you create a new instance and then not display it.

To make sure this is your problem modify your code like so

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
 
 
Dim frm2Search As New Form2
            Dim mySqlCommand As SqlCommand
            mySqlCommand = New SqlCommand
            mySqlCommand.Connection = Me.SqlConnection1
            mySqlCommand.CommandType = CommandType.Text
            mySqlCommand.CommandText = "SELECT pictureID, pictureTitle, pictureDescription, pictureData FROM Pictures WHERE pictureTitle LIKE @pictureTitle;"
           mySqlCommand.Parameters.Add(New SqlParameter("@pictureTitle", SqlDbType.Text))
          mySqlCommand.Parameters("@pictureTitle").Value = "%" + Me.TextBoxTitle.Text + "%"
 
 
          frm2Search.ListView1.Items.Clear()
          Try
 
                mySqlCommand.Connection.Open()
                Dim myDataReader As SqlDataReader
                myDataReader = mySqlCommand.ExecuteReader(CommandBehavior.Default)
 
                Dim pic As picture
 
                While myDataReader.Read
                    pic = New picture
                    pic.pictureID = myDataReader.GetValue(0)
                    pic.pictureTitle = myDataReader.GetValue(1)
                    pic.pictureDescription = myDataReader.GetValue(2)
                    pic.pictureData = New System.IO.MemoryStream(myDataReader.GetValue(3), True)
 
                    Dim i As ListViewItem = New ListViewItem
                    i.Text = pic.pictureTitle
                    i.Tag = pic
                    frm2Search.ListView1.Items.Add(i)
 
                     
 
                End While
            Catch ex As Exception
                MessageBox.Show(ex.Message)
            Finally
                mySqlCommand.Connection.Close()
 
'ONLY CHANGE IS BELOW HERE:
                frm2Search.Show
            End Try
    End Sub

if this is your problem then show me where you load the instance of form2 that you want to fill the listbox or upload your project...

[NOTSOSURE]

Thanks mate that helps loads

Thanks again

[NOTSOSURE]

sorry to disturb u again lol

but i have two more question

1.

every time a click the search button no matter how many times i click it a new form4 appears

so if i click the search button 5 times 5 search forms load.

how can i stop this i think it's somthing to do with

Dim frm4open As New Form4

2.

each time i perform a search it fills a new form2 with the data and not the form i originaly pressed the search button on

any ideas how to stop this

i think it's somthing to do with the

Dim frm2Search As New Form2

thanks

[<ABX]

I think at first I misunderstood your setup

correct me if I am wrong

form4 is the form you enter all your search information on to and is the form that your code snippit is from

form2 is the form that displays the results of the query

The answer to your first problem on another form (not one listed above) you are creating a new instance of form4, to solve this problem you must make sure that after you are done searching you call forms close method eg. [InstanceName].Close. Problem 2 is the same thing as problem 1 you havent been closing the instances of the forms.

[TheBOI]

Hey NotSoSure, just out of curiousity - what method have you used to store images in your database field pictureData?

[NOTSOSURE]

Het TheBOI

I used a fileStream. What that does is reads an image into file stream, and from there into Byte array. So what is does converts the picture to a number of bytes and stores those bytes in to the field pictureData in an SQL database.

'Read Picture into file stream, and from there into Byte array.
        Dim myFileStream As New FileStream(FileName, FileMode.Open, FileAccess.Read)

        Dim arrayOfBytes(myFileStream.Length) As Byte

        myFileStream.Read(arrayOfBytes, 0, arrayOfBytes.Length)

        myFileStream.Close()

        'Open SQL Connection
        SqlConnection1.Open()

        'Copy entered values into parameters, converting data type where necessary
        SqlCommand1.Parameters("@pictureTitle").Value = PictureTitle
        SqlCommand1.Parameters("@pictureDescription").Value = PictureDescription
        SqlCommand1.Parameters("@pictureOwner").Value = PictureOwner
        SqlCommand1.Parameters("@pictureData").Value = arrayOfBytes


        'Create variable for returned value from SQL execution
        Dim SQLreturn As Integer

        'Protect against problems using a Try...Catch block
        Try
            'Execute the command
            SQLreturn = SqlCommand1.ExecuteNonQuery()

        Catch ex As Exception
            MessageBox.Show(ex.Message)

        End Try

        'Report back results
        Dim result As String

        If SQLreturn <> 1 Then

            result = "Picture wasn't inserted successfully"
            MessageBox.Show(result, "Failed", MessageBoxButtons.OK, MessageBoxIcon.Error)
            StatusBarPanelStatus.Text = result

        Else

            result = "Picture Inserted successfully"
            MessageBox.Show(result, "Inserted", MessageBoxButtons.OK, MessageBoxIcon.Information)
            StatusBarPanelStatus.Text = result

        End If

        'Close SQL Connection
        SqlConnection1.Close()