ADO 可以将 ntext、text 或 image 列或参数映射为 Field 或 Parameter 对象。使用 GetChunk 方法逐块检索数据,使用 AppendChunk 方法逐块写数据。
解决方案 »
- 高手帮优化代码
- SQL读取注册表信息
- 昨天笔试了一个存储过程的问题,请大家帮忙,在线等
- 各位射雕英雄,我有日期型问题请教!
- 史上最大一坨sql,谁帮我解决一下,涉及表名为变量存储过程
- 如何彻底删除SQL SERVER 2005
- 高分求解数据库问题,一表对多表的问题。敬请大家赐教!!
- 问大家两个问题..都进来帮帮忙啊..谢谢啦..(sql6.5和sql2000的)
- mssql连接问题?
- 想知道有多少人使用JDBC与ORACLE连接,能提供有关JDBC的中文资料吗?
- select * from table1 where name like '%\'c%' escape '\' 在vb+access2000中出错
- 急!!!这个procedure怎么写--高手请进!
Dim cn As ADODB.Connection
Dim rstPubInfo As ADODB.Recordset
Dim strCn As String
Dim strPubID As String
Dim strPRInfo As String
Dim lngOffset As Long
Dim lngLogoSize As Long
Dim varLogo As Variant
Dim varChunk As Variant
Const conChunkSize = 100
' Open a connection.
Set cn = New ADODB.Connection
strCn = "Server=srv;Database=pubs;UID=sa;Pwd=;"
cn.Provider = "sqloledb"
cn.Open strCn 'Open the pub_info_x table.
Set rstPubInfo = New ADODB.Recordset
rstPubInfo.CursorType = adOpenDynamic
rstPubInfo.LockType = adLockOptimistic
rstPubInfo.Open "pub_info_x", cn, , , adCmdTable 'Prompt for a logo to copy.
strMsg = "Available logos are : " & vbCr & vbCr Do While Not rstPubInfo.EOF
strMsg = strMsg & rstPubInfo!pub_id & vbCr & _
Left(rstPubInfo!pr_info,
InStr(rstPubInfo!pr_info, ",") - 1) & vbCr & vbCr
rstPubInfo.MoveNext
Loop
strPubID = InputBox(strMsg)
' Copy the logo to a variable in chunks.
rstPubInfo.Filter = "pub_id = '" & strPubID & "'"
lngLogoSize = rstPubInfo!logo.ActualSize
Do While lngOffset < lngLogoSize
varChunk = rstPubInfo!logo.GetChunk(conChunkSize)
varLogo = varLogo & varChunk
lngOffset = lngOffset + conChunkSize
Loop
' Get data from the user.
strPubID = Trim(InputBox("Enter a new pub ID:"))
strPRInfo = Trim(InputBox("Enter descriptive text:"))
' Add a new record, copying the logo in chunks.
rstPubInfo.AddNew
rstPubInfo!pub_id = strPubID
rstPubInfo!pr_info = strPRInfo
lngOffset = 0 ' Reset offset.
Do While lngOffset < lngLogoSize
varChunk = LeftB(RightB(varLogo, lngLogoSize - _
lngOffset),conChunkSize)
rstPubInfo!logo.AppendChunk varChunk
lngOffset = lngOffset + conChunkSize
Loop
rstPubInfo.Update
rstPubInfo.Close
cn.Close
End Sub
Dim cnDb Set cnDb = ConnectionOpen("server", "testTbl", "user1", "pass") FileAdd cnDb, "d:\temp\test.jpg"
cnDb.Close
End SubFunction ConnectionOpen(Source, Catalog, UserId, Password)
Dim cnDb, strCn Set cnDb = CreateObject("ADODB.Connection") strCn = "Provider=SQLOLEDB;" _
& "Data Source=" & Source & ";" _
& "Initial Catalog=" & Catalog & ";" _
& "User Id=" & UserId & ";" _
& "Password=" & Password & ";"
cnDb.Open strCn
Set ConnectionOpen = cnDb
End Function
Sub FileAdd(cnDb, strFilename)
Dim stFile
Dim cmdFileAdd
Dim prmFilename, prmFileImage Set stFile = CreateObject("ADODB.Stream")
stFile.Type = adTypeBinary
stFile.Open
stFile.LoadFromFile strFilename Set cmdFileAdd = CreateObject("ADODB.Command")
With cmdFileAdd
.ActiveConnection = cnDb
.CommandType = adCmdStoredProc
.CommandText = "FilesAdd" Set prmFilename = _
.CreateParameter("@Filename", adVarWChar _
, adParamInput, 255, strFilename)
.Parameters.Append prmFilename Set prmFileImage = _
.CreateParameter("@FileImage", adLongVarBinary _
, adParamInput, stFile.Size + 1) .Parameters.Append prmFileImage prmFileImage.AppendChunk stFile.Read .Execute End WithEnd Sub
[Filename] [nvarchar] (255) NOT NULL ,
[FileImage] [image] NULL
) ON [PRIMARY] TEXTIMAGE_ON [PRIMARY]
GO2> SPCREATE PROCEDURE FilesAdd
(
@Filename nvarchar(255)
, @FileImage image
)
AS
INSERT INTO Files(Filename, FileImage)
VALUES(@Filename, @FileImage)
GO