-
February 26th, 2009, 05:48 AM
#1
store/update a image in sql server using vb.net
hi,
i looking for a easy way to save a image to a sql server i
have found a lot of examples but many is for asp.net and others have
many functions for do a simple process that with vb6 i do with a few lines:
"mstream.LoadFromFile .FileName
RS.Fields("imagem_sql").Value = mstream.Read
Image1.Picture = LoadPicture(.FileName)
RS.update"
so i looking for a simple way to do that i have found this:
connetionString = "Data Source=ip;Initial Catalog=bd;User ID=user;Password=pass"
Dim con As String = connetionString
Dim str As String = "select * from store where id like '" & TextBox23.Text & "'"
Dim da As New SqlDataAdapter(str, con)
Dim cmd As New SqlCommand(str, New SqlConnection(con))
Dim MyCB As SqlCommandBuilder = New SqlCommandBuilder(da)
Dim ds As New DataSet()
connection.Open()
command = New SqlCommand(sql, connection)
adapter.SelectCommand = command
adapter.Fill(ds)
adapter.Dispose()
command.Dispose()
connection.Close()
Dim fs As New FileStream(txtImageFile.Text, FileMode.OpenOrCreate, FileAccess.Read, FileShare.ReadWrite)
Dim MyData(fs.Length) As Byte
fs.Read(MyData, 0, fs.Length)
fs.Close()
ds.Tables(0).Rows(0)("imagem_sql") = MyData
da.Update(ds, 0)
fs = Nothing
MyCB = Nothing
ds = Nothing
da = Nothing
cmd.Connection.Close()
con = Nothing
but this simple dont update nothing
i have tryed to update a text value but the result is the same nothing is updated!
but he actully dont return any error and i can see that the record is opened but simple canĀ“t update
any one can help me to understand what is wrong and why this dont update?
thanks a lot for your help
-
February 26th, 2009, 08:31 AM
#2
Re: store/update a image in sql server using vb.net
When it comes to SQL, most of my projects are using Stored procedures to access the data, so my examples will use this method...
SQL Stored Procs
Code:
CREATE PROCEDURE [dbo].[New_logo]
(
@Name nvarchar(50),
@Logo image
)
AS
BEGIN
INSERT
INTO [Logos]
(
[Name],
[Logo]
)
VALUES
(
@Name,
@Logo
)
END
CREATE PROCEDURE [dbo].[Update_Logos]
(
@ID int,
@Name nvarchar(50),
@Logo image
)
AS
BEGIN
UPDATE [Logos]
SET
[Name] = @Name,
[Logo] = @Logo
WHERE [ID] = @ID
END
CREATE PROCEDURE [dbo].[Get_Logos]
AS
BEGIN
SELECT
[ID],
[Name],
[Logo]
FROM [Logos]
END
CREATE PROCEDURE [dbo].[Select_Logos]
(
@ID int
)AS
BEGIN
SELECT
[ID],
[Name],
[Logo]
FROM [Logos]
WHERE [ID] = @ID
END
I then use functions to call each of the stored prcos...
VB.NET Functions
Code:
Public Function GetLogos() As List(Of Logo)
Dim sqlConn As New SqlConnection(GetConnectionString())
Dim cmd As New SqlCommand
Dim dr As SqlDataReader
Getlogos = New List(Of Logo)
Try
sqlConn.Open()
With cmd
.Connection = sqlConn
.CommandType = CommandType.StoredProcedure
.CommandText = "Get_logo"
.Connection = sqlConn
dr = .ExecuteReader()
If Not dr Is Nothing Then
While dr.Read
Dim Thislogo As New Logo
With ThisLogo
.ID = dr("ID")
.Name = dr("Name")
.Logo = dr("Logo")
End With
GetLogos.Add(ThisLogo)
Wend
End If
End With
Catch ex As Exception
ErrorLogInsert( ex.ToString)
End Try
End Function
Public Function SelectLogo(ID as Integer) As Logo
Dim sqlConn As New SqlConnection(GetConnectionString())
Dim cmd As New SqlCommand
Dim dr As SqlDataReader
SelectLogo = New Logo
Try
sqlConn.Open()
With cmd
.Connection = sqlConn
.CommandType = CommandType.StoredProcedure
.CommandText = "Select_logo"
.Connection = sqlConn
dr = .ExecuteReader()
If Not dr Is Nothing Then
If dr.Read
With SelectLogo
.ID = dr("ID")
.Name = dr("Name")
.Logo = dr("Logo")
End With
End If
End If
End With
Catch ex As Exception
ErrorLogInsert( ex.ToString)
End Try
End Function
Public Sub Updatelogo(ByVal logoItem As logo)
Dim sqlConn As New SqlConnection(GetConnectionString())
Dim cmd As New SqlCommand
Dim dr As SqlDataReader
Try
sqlConn.Open()
With cmd
.Connection = sqlConn
.CommandType = CommandType.StoredProcedure
.CommandText = "Update_logo"
.Parameters.AddWithValue("@ID", logoItem.ID)
.Parameters.AddWithValue("@Logo", logoItem.Logo)
.Parameters.AddWithValue("@Name", logoItem.Name)
.Connection = sqlConn
dr = .ExecuteReader()
End With
Catch ex As Exception
ErrorLogInsert( ex.ToString)
End Try
End Sub
Public Sub Newlogo(ByVal Logoitem As Utility)
Dim sqlConn As New SqlConnection(GetConnectionString())
Dim cmd As New SqlCommand
Dim dr As SqlDataReader
Try
sqlConn.Open()
With cmd
.Connection = sqlConn
.CommandType = CommandType.StoredProcedure
.CommandText = "New_Logo"
.Parameters.AddWithValue("@Logo", Utilityitem.Logo)
.Parameters.AddWithValue("@Name", Utilityitem.Name)
.Connection = sqlConn
dr = .ExecuteReader()
End With
Catch ex As Exception
ErrorLogInsert(ex.ToString)
End Try
End Sub
After all of that is done ... its a simple case of using memory streams to transfer the data to and from the picturebox..
To get it from SQL into a Picturebox
Code:
Private Sub ShowLogo(ByVal ID As Integer)
LogoData = SelectLogo(ID)
With LogoData
TxtName.Text = .Name
TxtID.Text = CStr(.ID)
If Not .Logo Is Nothing Then
If .Logo.Length > 4 Then
Dim ms As New IO.MemoryStream(.Logo, 0, .Logo.Length)
PBlogo.Image = Image.FromStream(ms)
End If
End If
End With
End Sub
And to save a New logo into SQL, I normally create a temporary filestreamed copy from the original file..
Code:
Private Sub Logo_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim Res As System.Windows.Forms.DialogResult
OFDialog.Filter = "All Images|*.jpg;*.bmp;*.gif;*.jpeg|Jpeg Files|*.jpg;*.jpeg|Bitmap Files|*.bmp|Gif Files|*.gif|All Files|*.*"
OFDialog.FileName = ""
Res = OFDialog.ShowDialog()
If Res = Windows.Forms.DialogResult.OK Then
If OFDialog.FileName <> "" Then
If File.Exists(OFDialog.FileName) Then
PBlogo.Load(OFDialog.FileName)
Dim fs As New FileStream(OFDialog.FileName, FileMode.Open, FileAccess.Read)
Dim temp(fs.Length) As Byte
fs.Read(temp, 0, fs.Length)
Tmp.Logo = temp
End If
End If
End If
End Sub
And then to save (or to Update) you can use
Code:
Private Sub SaveNewLogo()
Dim LogoData As New Logo
With LogoData
.Name = TxtName.Text
.ID = 0
If Not Tmp.Logo Is Nothing Then
.Logo = Tmp.Logo
Else
ReDim .Logo(1)
End If
End With
NewLogo(LogoData)
End Function
Private Sub UpdateLogo()
With LogoData
.Name = TxtName.Text
.ID = Cint(TxtID.Text)
If Not Tmp.Logo Is Nothing Then
.Logo = Tmp.Logo
Else
ReDim .Logo(1)
End If
End With
UpdateLogo(LogoData)
End Function
Hope These Help you ....
Gremmy...
Articles VB6 : Break the 2G limit - Animation 1, 2 VB.NET : 2005/8 : Moving Images , Animation 1 , 2 , 3 , User Controls
WPF Articles : 3D Animation 1 , 2 , 3
Code snips: VB6 Hex Edit, IP Chat, Copy Prot., Crop, Zoom : .NET IP Chat (V4), Adv. ContextMenus, click Hotspot, Scroll Controls
Find me in ASP.NET., VB6., VB.NET , Writing Articles, My Genealogy, Forum
All VS.NET: posts refer to VS.NET 2008 (Pro) unless otherwise stated.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|