none
Vb .net upload multiple files to SQL server 2005 RRS feed

  • Question

  • I would like to upload multiple files to SQL Server 2005. The file paths are stored in a list box. I want to modfy the following code to loop through the files in the list box and upload them to SQL DB.

     

    I have tried to modify the code but cannot have the line:

     

    Dim fs As New FileStream ("C:\winnt\Gone Fishing.BMP", FileMode.OpenOrCreate, FileAccess.Read)

     

    In a loop. Can anyone please help?


     

    I have got the following code from MS Knowledge Base:

    http://support.microsoft.com/default.aspx?scid=kb;EN-US;308042

     

     

    Dim con As New SqlConnection _
           ("Server=YourServer;uid=<username>;pwd=<strong password>;database=northwind")
          Dim da As New SqlDataAdapter _
           ("Select * From MyImages", con)
          Dim MyCB As SqlCommandBuilder = New SqlCommandBuilder(da)
          Dim ds As New DataSet()

          da.MissingSchemaAction = MissingSchemaAction.AddWithKey

          Dim fs As New FileStream _
           ("C:\winnt\Gone Fishing.BMP", FileMode.OpenOrCreate, _
            FileAccess.Read)
          Dim MyData(fs.Length) As Byte
          fs.Read(MyData, 0, fs.Length)
          fs.Close()
          con.Open()
          da.Fill(ds, "MyImages")
          Dim myRow As DataRow
          myRow = ds.Tables("MyImages").NewRow()

          myRow("Description") = "This would be description text"
          myRow("imgField") = MyData
          ds.Tables("MyImages").Rows.Add(myRow)
          da.Update(ds, "MyImages")

          fs = Nothing
          MyCB = Nothing
          ds = Nothing
          da = Nothing

          con.Close()
          con = Nothing
          MsgBox ("Image saved to database")

    Friday, August 17, 2007 12:07 AM

All replies

  • Hi I don't know if this is what you're lookin for:

    Dim con As New SqlConnection _
           ("Server=YourServer;uid=<username>;pwd=<strong password>;database=northwind")
          Dim da As New SqlDataAdapter _
           ("Select * From MyImages", con)
          Dim MyCB As SqlCommandBuilder = New SqlCommandBuilder(da)
          Dim ds As New DataSet()

          da.MissingSchemaAction = MissingSchemaAction.AddWithKey

    Dim x as integer

    For x=0 to ListBox1.Items.Count-1
          Dim fs As New FileStream _
           (Listbox1.Items[x].ToString(), FileMode.OpenOrCreate, _
            FileAccess.Read)
          Dim MyData(fs.Length) As Byte
          fs.Read(MyData, 0, fs.Length)
          fs.Close()
          con.Open()
          da.Fill(ds, "MyImages")

          Dim myRow As DataRow
          myRow = ds.Tables("MyImages").NewRow()

          myRow("Description") = "This would be description text"
          myRow("imgField") = MyData
          ds.Tables("MyImages").Rows.Add(myRow)
          da.Update(ds, "MyImages")
    Next

          fs = Nothing
          MyCB = Nothing
          ds = Nothing
          da = Nothing

          con.Close()
          con = Nothing

    Friday, August 17, 2007 5:43 AM
  • I tried this already, and as mentioned in my initial statement the line:

    Dim fs As New FileStream (Listbox1.Items[x].ToString(), FileMode.OpenOrCreate, FileAccess.Read)
    causes an error.

    I don't know how to get around this.
    Friday, August 17, 2007 6:32 AM
  • what's the error? basically web applications cannot upload directly from client to database..you have to upload the file first to the web server (most probably under a folder that is not read-only) and from that you'll make your upload to the database.

    so it would be like this:

    Client-->WebServer--->Database
    Friday, August 17, 2007 6:50 AM