Option Explicit'工程->引用->Microsoft ActiveX Data Objects 2.0 Library (后面为版本号) Dim cn As New ADODB.Connection Dim rs As New ADODB.RecordsetPrivate Sub Command1_Click() If rs.State = adStateOpen Then rs.Close rs.Open "SELECT * FROM aaa", cn, adOpenDynamic, adLockOptimistic rs.AddNew rs!aa = "data" rs!bb = Text1.Text rs.Update End SubPrivate Sub Command2_Click() If rs.State = adStateOpen Then rs.Close rs.Open "SELECT * FROM aaa", cn, adOpenDynamic, adLockOptimistic
If Not rs.EOF Then Text1.Text = StrConv(rs!bb, vbUnicode) End If End SubPrivate Sub Form_Load() cn.ConnectionString = "Driver={SQL Server};SERVER=CUIZM;DATABASE=orientscm;UID=sa;PWD=" cn.Open '查询字符串可以上这里查 'http://www.connectionstrings.com/ End SubPrivate Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer) On Error Resume Next rs.Close Set rs = Nothing cn.Close Set cn = Nothing End Sub
'将内存中的文件存入数据库 Private Sub SaveMyFilesToDB(ObjRs As ADODB.Recordset, ByVal strField As String) Dim i As Long, k As Long Dim bT() As Byte
On Error GoTo Err1 With ObjRs.Fields(strField) k = UBound(MyFiles) For i = 0 To k .AppendChunk MyFiles(i).intFileNameLen .AppendChunk MyFiles(i).lngFileLen .AppendChunk MyFiles(i).strFileName If MyFiles(i).lngFileLen(0) > 0 Or MyFiles(i).lngFileLen(1) > 0 Or MyFiles(i).lngFileLen(2) > 0 Or MyFiles(i).lngFileLen(3) > 0 Then .AppendChunk MyFiles(i).btFileData End If Next End With Exit Sub Err1: lngLastErrorNum = Err.Number End Sub
Text1.Text = StrConv(rs!bb, vbUnicode) 主要就是这个函数了
to cuizm(射天狼) :rs!aa = "data" rs!bb = Text1.Text 不能存入数据表中(数据类型是Binary )
Set stm = New ADODB.Stream stm.Type = adTypeBinary stm.Open stm.LoadFromFile CommonDialog1.FileName rs.AddNew rs.Fields(0).AppendChunk stm.Read rs.Update
'以下代码用于测试 '在sqlserver查询分析其中建立表 'create table B(id int identity(1,1),C varbinary(500))'新建一个工程,在form1上加两个text,两个command '引用Microsoft ActiveX Data Objects 2.0 Library (后面为版本号)Option Explicit Dim cn As ADODB.ConnectionPrivate Sub Command1_Click() '将字符转换为2进制类型存入 cn.Execute "insert into B select convert(varbinary(500),'" & Trim(Text1.Text) & "')" End SubPrivate Sub Command2_Click() Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset '将2进制转换为字符类型输出 rs.Open "select cast(C as varchar(8000)) as C from B ", cn Text2.Text = rs!C & "" rs.Close Set rs = Nothing End SubPrivate Sub Form_Load() Set cn = New ADODB.Connection cn.Open "Provider=SQLOLEDB.1;Persist Security Info=True;User ID=sa;Initial Catalog=A;Data Source=." End SubPrivate Sub Form_Unload(Cancel As Integer) If Not cn Is Nothing Then If cn.State = adStateOpen Then cn.Close Set cn = Nothing End If End Sub
Dim cn As New ADODB.Connection
Dim rs As New ADODB.RecordsetPrivate Sub Command1_Click()
If rs.State = adStateOpen Then rs.Close
rs.Open "SELECT * FROM aaa", cn, adOpenDynamic, adLockOptimistic
rs.AddNew
rs!aa = "data"
rs!bb = Text1.Text
rs.Update
End SubPrivate Sub Command2_Click()
If rs.State = adStateOpen Then rs.Close
rs.Open "SELECT * FROM aaa", cn, adOpenDynamic, adLockOptimistic
If Not rs.EOF Then
Text1.Text = StrConv(rs!bb, vbUnicode)
End If
End SubPrivate Sub Form_Load()
cn.ConnectionString = "Driver={SQL Server};SERVER=CUIZM;DATABASE=orientscm;UID=sa;PWD="
cn.Open
'查询字符串可以上这里查
'http://www.connectionstrings.com/
End SubPrivate Sub Form_QueryUnload(Cancel As Integer, UnloadMode As Integer)
On Error Resume Next
rs.Close
Set rs = Nothing
cn.Close
Set cn = Nothing
End Sub
Private Sub SaveMyFilesToDB(ObjRs As ADODB.Recordset, ByVal strField As String)
Dim i As Long, k As Long
Dim bT() As Byte
On Error GoTo Err1
With ObjRs.Fields(strField)
k = UBound(MyFiles)
For i = 0 To k
.AppendChunk MyFiles(i).intFileNameLen
.AppendChunk MyFiles(i).lngFileLen
.AppendChunk MyFiles(i).strFileName
If MyFiles(i).lngFileLen(0) > 0 Or MyFiles(i).lngFileLen(1) > 0 Or MyFiles(i).lngFileLen(2) > 0 Or MyFiles(i).lngFileLen(3) > 0 Then
.AppendChunk MyFiles(i).btFileData
End If
Next
End With
Exit Sub
Err1:
lngLastErrorNum = Err.Number
End Sub
主要就是这个函数了
rs!bb = Text1.Text
不能存入数据表中(数据类型是Binary )
stm.Type = adTypeBinary
stm.Open
stm.LoadFromFile CommonDialog1.FileName
rs.AddNew
rs.Fields(0).AppendChunk stm.Read
rs.Update
'在sqlserver查询分析其中建立表
'create table B(id int identity(1,1),C varbinary(500))'新建一个工程,在form1上加两个text,两个command
'引用Microsoft ActiveX Data Objects 2.0 Library (后面为版本号)Option Explicit
Dim cn As ADODB.ConnectionPrivate Sub Command1_Click()
'将字符转换为2进制类型存入
cn.Execute "insert into B select convert(varbinary(500),'" & Trim(Text1.Text) & "')"
End SubPrivate Sub Command2_Click()
Dim rs As ADODB.Recordset
Set rs = New ADODB.Recordset '将2进制转换为字符类型输出
rs.Open "select cast(C as varchar(8000)) as C from B ", cn
Text2.Text = rs!C & ""
rs.Close
Set rs = Nothing
End SubPrivate Sub Form_Load()
Set cn = New ADODB.Connection
cn.Open "Provider=SQLOLEDB.1;Persist Security Info=True;User ID=sa;Initial Catalog=A;Data Source=."
End SubPrivate Sub Form_Unload(Cancel As Integer)
If Not cn Is Nothing Then
If cn.State = adStateOpen Then cn.Close
Set cn = Nothing
End If
End Sub