Image Load to SQL Server

[Guest]

I have gotten this to work for a single image load in SQL server, can anyone
help me make this a "For...Each" statement to include every file in the
folder?

on the 4th line of the main code, is the file path i made, all i need to do
now is cycle through, get "3A_00184", "3A_00191", "41033496", etc from the
folder "IMAGES"



Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Button3.Click


Dim cn As New SqlConnection(strCn)
Dim cmd As New SqlCommand("INSERT INTO BLOBS (BLOBDetails) " &
"VALUES (@BLOBDetails)", cn)
Dim strBLOBFilePath As String = "C:\FilePath\IMAGES\3A_00184"
Dim fsBLOBFile As New FileStream(strBLOBFilePath, FileMode.Open,
FileAccess.Read)
Dim bytBLOBData(fsBLOBFile.Length() - 1) As Byte
fsBLOBFile.Read(bytBLOBData, 0, bytBLOBData.Length)
fsBLOBFile.Close()
Dim prm As New SqlParameter("@BLOBDetails", SqlDbType.VarBinary, _
bytBLOBData.Length, ParameterDirection.Input, False, _
0, 0, Nothing, DataRowVersion.Current, bytBLOBData)
cmd.Parameters.Add(prm)
cn.Open()
cmd.ExecuteNonQuery()
cn.Close()


End Sub

 

[Herfried K. Wagner [MVP]]

I have gotten this to work for a single image load in SQL server, can
anyone
help me make this a "For...Each" statement to include every file in the
folder?

\\\
For Each File As String In Directory.GetFiles("C:\foo")
...
Next File
///

 

[Guest]

do i need to delete the following line or leave everything inside the
for...each as is?

Dim strBLOBFilePath As String = "C:\FilePath\IMAGES\3A_00184"

 

[Herfried K. Wagner [MVP]]

do i need to delete the following line or leave everything inside the
for...each as is?

Dim strBLOBFilePath As String = "C:\FilePath\IMAGES\3A_00184"

Yes. Put your code into the loop and use 'File' instead of this variable.
Specify the path in the call to 'GetFiles'.

 

[Cor Ligthert]

Yes. Put your code into the loop and use 'File' instead of this variable.
Specify the path in the call to 'GetFiles'.

--

Does not work it will fail on the add parameter, I think that you have to
give a better answer.

Cor

 

[Herfried K. Wagner [MVP]]

Does not work it will fail on the add parameter, I think that you have to
give a better answer.

Maybe you and the OP should turn the brain on...

 

[Cor Ligthert]

Herfried,

Maybe you and the OP should turn the brain on...

No problem at all, (I had yesterday no time anymore me to write this answer
because there are some other things that can in my opinion better).

To the OP, be aware that this is an not tested sample, however I changed as
well some things in your code. This code I show you now should save an image
and not a path in the database. The main reason that I write this is the way
you have to set the paramater again and again, when you just try to add it
you get a strange colletcion.

\\\
Dim cn As New SqlConnection(strCn)
Dim cmd As New SqlCommand("INSERT INTO BLOBS " & _
"(BLOBDetails) VALUES (@BLOBDetails)", cn)
cmd.Parameters.Add((New SqlParameter("@BLOBDetails",
SqlDbType.Image)))
For Each File As String In Directory.GetFiles("C:\foo")
Dim fsBLOBFile As New FileStream(File, _
FileMode.Open, FileAccess.Read)
Dim br As New IO.BinaryReader(fsBLOBFile)
Dim abyt As Byte() = br.ReadBytes(CInt(fsBLOBFile.Length))
br.Close()
fsBLOBFile.Close()
cmd.Parameters("@BLOBDetails").Value = abyt
cmd.ExecuteNonQuery()
Next File
cn.Dispose()
///

I hope this helps?

Cor