Hello Everyone"
I have been working on a project where the user can add a new record each time, The user will click an Add new record button and the new record forms comes up, here is my dilemma :-)
There will be times when a user needs to add a new record containing an attachment; this could be a JPG, BMP, and DOC.
I have some code that would allow me to save an image file to the SQL2005, what I would like to do is that when I click on the "Attachment" button it will give me the option to select the file, Here is the code that I'll be adding to the attachment button:
Private Sub Attachment_Click_1(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles Attachment.Click
OpenFileDialog1.Title = "Set Image File"
OpenFileDialog1.Filter = "Bitmap Files|*.bmp" & _
"|Gif Files|*.gif|JPEG Files|*.jpg"
OpenFileDialog1.DefaultExt = "bmp"
OpenFileDialog1.FilterIndex = 1
OpenFileDialog1.FileName = ""
OpenFileDialog1.ShowDialog()
If OpenFileDialog1.ShowDialog = Windows.Forms.DialogResult.Cancel Then
Exit Sub
End If
Dim sFilePath As String
sFilePath = OpenFileDialog1.FileName
If sFilePath = "" Then Exit Sub
If System.IO.File.Exists(sFilePath) = False Then
Exit Sub
Else
txtImageFile.Text = sFilePath
mImageFilePath = sFilePath
End If
End Sub
What I would like to happen is that after I save the image to the database to automatically create or populate a link on my "Add new record" form, tah after is save and someone else opens or edits the previesly added records that they can go an clcik on the link and for the file to open up.
I think I am asking for to much. I am new in programming and any help will be greatly appreciated.
Here is the code to save the data:
Imports System.Data.SqlClient
Imports System.IO
' 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")
Here is the code to retrieve the picture
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()
con.Open()
da.Fill(ds, "MyImages")
Dim myRow As DataRow
myRow = ds.Tables("MyImages").Rows(0)
Dim MyData() As Byte
MyData = myRow("imgField")
Dim K As Long
K = UBound(MyData)
Dim fs As New FileStream _
("C:\winnt\Gone Fishing2.BMP", FileMode.OpenOrCreate, _
FileAccess.Write)
fs.Write(MyData, 0, K)
fs.Close()
fs = Nothing
MyCB = Nothing
ds = Nothing
da = Nothing
con.Close()
con = Nothing
MsgBox ("Image retrieved")