vb mysql 保存图片路径_vb.net如何实现在sql数据库中存取图片

向SQL Server数据库添加图片和文字

作者：孟宪会 出自：【孟宪会之精彩世界】 发布日期：2003年6月27日 2点53分33秒

--------------------------------------------------------------------------------

下面的代码实现向SQL Server数据库添加图片和文字的功能。

首先，在SQL查询分析器中执行下面的SQL语句，以创建表和存储过程。

CREATE TABLE Photos (

[name] varchar(50),

[photo] image NULL

)

GO

CREATE PROCEDURE sp_InsertPhoto

@name AS VARCHAR(50),

@image AS IMAGE

AS

INSERT INTO Photos ([name],  [photo])

VALUES (@name, @image)

GO

下面就是完整的代码，拷贝即可运行：

Imports System.IO

Public Class Form1

Inherits System.Windows.Forms.Form

Dim cn As SqlClient.SqlConnection

#Region " Windows Form Designer generated code "

Public Sub New()

MyBase.New()

'This call is required by the Windows Form Designer.

InitializeComponent()

'Add any initialization after the InitializeComponent() call

End Sub

'Form overrides dispose to clean up the component list.

Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)

If disposing Then

If Not (components Is Nothing) Then

components.Dispose()

End If

End If

MyBase.Dispose(disposing)

End Sub

'Required by the Windows Form Designer

Private components As System.ComponentModel.IContainer

'NOTE: The following procedure is required by the Windows Form Designer

'It can be modified using the Windows Form Designer.

'Do not modify it using the code editor.

Friend WithEvents Label1 As System.Windows.Forms.Label

Friend WithEvents Label2 As System.Windows.Forms.Label

Friend WithEvents TextBox1 As System.Windows.Forms.TextBox

Friend WithEvents PictureBox1 As System.Windows.Forms.PictureBox

Friend WithEvents Button1 As System.Windows.Forms.Button

Friend WithEvents Button2 As System.Windows.Forms.Button

Friend WithEvents OpenFileDialog1 As System.Windows.Forms.OpenFileDialog

Friend WithEvents SqlConnection1 As System.Data.SqlClient.SqlConnection

Friend WithEvents SqlCommand1 As System.Data.SqlClient.SqlCommand

Friend WithEvents Button3 As System.Windows.Forms.Button

Friend WithEvents Button4 As System.Windows.Forms.Button

Friend WithEvents Button5 As System.Windows.Forms.Button

Private Sub InitializeComponent()

Me.Label1 = New System.Windows.Forms.Label()

Me.Label2 = New System.Windows.Forms.Label()

Me.TextBox1 = New System.Windows.Forms.TextBox()

Me.PictureBox1 = New System.Windows.Forms.PictureBox()

Me.Button1 = New System.Windows.Forms.Button()

Me.Button2 = New System.Windows.Forms.Button()

Me.OpenFileDialog1 = New System.Windows.Forms.OpenFileDialog()

Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection()

Me.SqlCommand1 = New System.Data.SqlClient.SqlCommand()

Me.Button3 = New System.Windows.Forms.Button()

Me.Button4 = New System.Windows.Forms.Button()

Me.Button5 = New System.Windows.Forms.Button()

Me.SuspendLayout()

'

'Label1

'

Me.Label1.Location = New System.Drawing.Point(15, 19)

Me.Label1.Name = "Label1"

Me.Label1.Size = New System.Drawing.Size(80, 24)

Me.Label1.TabIndex = 0

Me.Label1.Text = "姓名："

'

'Label2

'

Me.Label2.Location = New System.Drawing.Point(11, 64)

Me.Label2.Name = "Label2"

Me.Label2.Size = New System.Drawing.Size(80, 20)

Me.Label2.TabIndex = 1

Me.Label2.Text = "图片"

'

'TextBox1

'

Me.TextBox1.Location = New System.Drawing.Point(75, 16)

Me.TextBox1.Name = "TextBox1"

Me.TextBox1.Size = New System.Drawing.Size(173, 20)

Me.TextBox1.TabIndex = 2

Me.TextBox1.Text = ""

'

'PictureBox1

'

Me.PictureBox1.Location = New System.Drawing.Point(68, 48)

Me.PictureBox1.Name = "PictureBox1"

Me.PictureBox1.Size = New System.Drawing.Size(376, 222)

Me.PictureBox1.TabIndex = 3

Me.PictureBox1.TabStop = False

'

'Button1

'

Me.Button1.Location = New System.Drawing.Point(278, 13)

Me.Button1.Name = "Button1"

Me.Button1.Size = New System.Drawing.Size(96, 26)

Me.Button1.TabIndex = 4

Me.Button1.Text = "浏览图片…"

'

'Button2

'

Me.Button2.Location = New System.Drawing.Point(57, 277)

Me.Button2.Name = "Button2"

Me.Button2.Size = New System.Drawing.Size(100, 32)

Me.Button2.TabIndex = 5

Me.Button2.Text = "添加到数据库"

'

'SqlConnection1

'

Me.SqlConnection1.ConnectionString = "Data Source=.;Initial Catalog=mxh;User Id=sa;Password=;"

'

'SqlCommand1

'

Me.SqlCommand1.CommandText = "dbo.[sp_InsertPhoto]"

Me.SqlCommand1.CommandType = System.Data.CommandType.StoredProcedure

Me.SqlCommand1.Connection = Me.SqlConnection1

Me.SqlCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@RETURN_VALUE", _

System.Data.SqlDbType.Int, 4, System.Data.ParameterDirection.ReturnValue, _

False, CType(10, Byte), CType(0, Byte), "", System.Data.DataRowVersion.Current, Nothing))

Me.SqlCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@name", _

System.Data.SqlDbType.VarChar, 50))

Me.SqlCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@image", _

System.Data.SqlDbType.VarBinary, 2147483647))

'

'Button3

'

Me.Button3.Location = New System.Drawing.Point(265, 277)

Me.Button3.Name = "Button3"

Me.Button3.Size = New System.Drawing.Size(79, 32)

Me.Button3.TabIndex = 6

Me.Button3.Text = "显示记录"

'

'Button4

'

Me.Button4.Location = New System.Drawing.Point(362, 277)

Me.Button4.Name = "Button4"

Me.Button4.Size = New System.Drawing.Size(72, 32)

Me.Button4.TabIndex = 7

Me.Button4.Text = "退出"

'

'Button5

'

Me.Button5.Location = New System.Drawing.Point(167, 277)

Me.Button5.Name = "Button5"

Me.Button5.Size = New System.Drawing.Size(85, 32)

Me.Button5.TabIndex = 8

Me.Button5.Text = "删除该记录"

'

'Form1

'

Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)

Me.ClientSize = New System.Drawing.Size(491, 324)

Me.Controls.AddRange(New System.Windows.Forms.Control() {Me.PictureBox1, _

Me.Button5, Me.Button4, Me.Button3, Me.Button2, Me.Button1, Me.TextBox1, Me.Label1, Me.Label2})

Me.Name = "Form1"

Me.Text = "Form1"

Me.ResumeLayout(False)

End Sub

#End Region

'Browse Button

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _

Handles Button1.Click

'Display Picture File

OpenFileDialog1.InitialDirectory = "d:\pic"

OpenFileDialog1.DefaultExt = "gif"

OpenFileDialog1.Filter = "Bmp Files(*.bmp)|*.bmp|Gif Files(*.gif)|*.gif|Jpg Files(*.jpg)|*.jpg"

OpenFileDialog1.ShowDialog()

PictureBox1.Image = Image.FromFile(OpenFileDialog1.FileName)

End Sub

'Add Button

Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _

Handles Button2.Click

' To Insert Image

Dim st As New FileStream(OpenFileDialog1.FileName, FileMode.Open, FileAccess.Read)

Dim s As String = TextBox1.Text

Dim mbr As BinaryReader = New BinaryReader(st)

Dim buffer(st.Length) As Byte

mbr.Read(buffer, 0, CInt(st.Length))

st.Close()

InsertImage(buffer, s)

End Sub

'Function For Inserting in the Procdeure in the Database

Public Function InsertImage(ByRef buffer, ByVal str)

cn = New SqlClient.SqlConnection(SqlConnection1.ConnectionString)

cn.Open()

Dim cmd As New SqlClient.SqlCommand("sp_InsertPhoto", cn)

cmd.CommandType = CommandType.StoredProcedure

cmd.Parameters.Add("@name", SqlDbType.VarChar).Value = TextBox1.Text

cmd.Parameters.Add("@image", SqlDbType.Image).Value = buffer

cmd.ExecuteNonQuery()

MsgBox("Image inserted&quot

cn.Close()

End Function

'Function to Display Image

Private Sub ShowImage(ByVal s As String)

cn = New SqlClient.SqlConnection(SqlConnection1.ConnectionString)

cn.Open()

Dim str As String = "SELECT photo FROM Photos WHERE name='" & s & "'"

Dim cmd As New SqlClient.SqlCommand(str, cn)

TextBox1.Text = s

Dim b() As Byte

b = cmd.ExecuteScalar()

If (b.Length > 0) Then

Dim stream As New MemoryStream(b, True)

stream.Write(b, 0, b.Length)

DrawToScale(New Bitmap(stream))

stream.Close()

End If

cn.Close()

End Sub

'Function to Create Instance For the Image From the Buffer

Private Sub DrawToScale(ByVal bmp As Image)

PictureBox1.Image = New Bitmap(bmp)

End Sub

'显示处理

Private Sub Button3_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _

Handles Button3.Click

Dim i As String = InputBox("请输入名字：&quot

ShowImage(i)

End Sub

'退出处理

Private Sub Button4_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _

Handles Button4.Click

Me.Dispose()

End Sub

'删除处理

Private Sub Button5_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) _

Handles Button5.Click

cn = New SqlClient.SqlConnection(SqlConnection1.ConnectionString)

cn.Open()

Dim s As String = InputBox("请输入要删除的名字：&quot

Dim cmd As New SqlClient.SqlCommand("delete from photos where name='" & s & "'", cn)

cmd.ExecuteNonQuery()

MsgBox("Image deleted&quot

cn.Close()

End Sub

End Class

本文评论(Comments)：为了保护您的电子邮件不被骚扰，地址中的个别符号转换成了全角字符！

评论人：爱尔兰咖啡 电子邮件： 评论日期：2006年03月20日 09:02:13

System.Data.SqlClient.SqlException: 用户 'NT AUTHORITY\NETWORK SERVICE' 登录失败。 at System.Data.SqlClient.ConnectionPool.GetConnection(Boolean& isInTransaction) at System.Data.SqlClient.SqlConnectionPoolManager.GetPooledConnection(SqlConnectionString options, Boolean& isInTransaction) at System.Data.SqlClient.SqlConnection.Open() at 计件工资管理系统.WebForm1.cmdLogin_Click(Object sender, EventArgs e) in C:\Inetpub\wwwroot\计件工资管理系统\main.aspx.vb:line 53

出现这个问题

源码是:

Dim str As String = "server=(local);database=计件工资管理系统;integrated security=true"

Dim conn As New SqlConnection(str)

Dim comm As New SqlCommand

comm.Connection = conn

comm.CommandType = CommandType.Text

comm.CommandText = "select * from 登录表 where  登录名='" + TextBox1.Text + "' and 密码='" + TextBox2.Text + "' and 权限 ='" + DropDownList1.SelectedValue + "'"

Dim dr As SqlDataReader

Try

conn.Open()

dr = comm.ExecuteReader

If dr.Read Then

Response.Redirect("main1.aspx&quot

Else

lblInfo.Text = "用户名或密码错误!"

End If

If conn.State = ConnectionState.Open Then

conn.Close()

End If

Catch ex As Exception

lblInfo.Text = (ex.ToString)

End Try