已知:
刚装好SQL Server~在数据库下新建了一个数据库,名叫:test123!其它都采用默认设置!新建了一个表,叫test567~结构如下:
列名 数据类型 长度 允许空行
NameID varchar 10 否
Item varbinary 1000 是
Number int 4 是
Date smalldatetime 4 是
Version tinyint 1 是
PassWord smallint 2 是要怎么样才可以读写里面的每个字段呢??希望有完整的例子!谢谢~~
刚装好SQL Server~在数据库下新建了一个数据库,名叫:test123!其它都采用默认设置!新建了一个表,叫test567~结构如下:
列名 数据类型 长度 允许空行
NameID varchar 10 否
Item varbinary 1000 是
Number int 4 是
Date smalldatetime 4 是
Version tinyint 1 是
PassWord smallint 2 是要怎么样才可以读写里面的每个字段呢??希望有完整的例子!谢谢~~
解决方案 »
- 怎樣從VB6中設置EXECL中的CheckBox的Value=True
- vb软件中是否有类似js的eval函数?如果没有,有什么代替性函数或代码?
- SetClipboardData 怎么用?
- 怎样建一个小型学生管理软件?
- 我想请教:在串行通信时,什么情况下用WaitForSingleObject(hMutex,1)这个函数?
- 如何用ADO将ACCESS生成Foxbase+的dbase文件?
- ToolBar方面的一個簡單問題,請進.....
- 我用listview的报表形式进行数据显示且数据居右显示,选中的时候,没有数据那一格就不反白显示。
- 急!是一个VB做的控件,属性在网页中无效的问题!!
- 求高人调试一段VB计算程序
- 为什么在VB6中使用Crystal Reports9作报表,运行时提示登录失败???
- 各位大哥们,为什么我自己用VB做的OCX控件,打包后安装到别人的机子里,说什么过期或未注册呀!
Sub sql()
Dim iRe As ADODB.Recordset
Dim iConc As String
'数据库连接字符串
iConc = "Provider=SQLOLEDB.1;Persist Security Info=True;" & _
"User ID=用户名;Password=密码;Initial Catalog=test123;Data Source=SQL服务器名"
'打开表
Set iRe = New ADODB.Recordset
iRe.Open "test567", iConc, adOpenKeyset, adLockOptimistic
'进行操作
With iRe
'新增一条记录
.AddNew
.Fields("NameID") = "aa"
.Fields("Item") = 1
.Fields("Number") = 100
.Fields("Date") = "2003-01-01"
.Fields("Version") = 1
.Fields("PassWord") = 1
.Save
'移动到第一条记录,并读取该记录nameid的值
.MoveFirst
MsgBox .Fields("NameID")
'删除第一条记录
.MoveFirst
.Delete adAffectCurrent
End With
'关闭连接
iRe.Close
Set iRe = Nothing
End Sub
select * from test567(nolock)
寫每個字段
insert into test567 (NameID,Item,Number,Date,Version,PassWord) values ('Jack',1,21,'20040430',1,3213)
注:用SQL的导出到文本方法,该字段是:"CC843200000000809135C0843200000000000246"这样子的!
As String
ConnectString = "FileDSN=info.dsn;UID=sa;PWD=23"
End Function
Public Function ExecuteSQL(ByVal SQL _
As String, MsgString As String) _
As ADODB.Recordset
'executes SQL and returns Recordset
Dim cnn As ADODB.Connection
Dim rst As ADODB.Recordset
Dim sTokens() As String
On Error GoTo ExecuteSQL_Error
sTokens = Split(SQL)
Set cnn = New ADODB.Connection
cnn.Open ConnectString
If InStr("INSERT,DELETE,UPDATE", _
UCase$(sTokens(0))) Then
cnn.Execute SQL
MsgString = sTokens(0) & _
" query successful"
Else
Set rst = New ADODB.Recordset
rst.Open Trim$(SQL), cnn, _
adOpenKeyset, _
adLockOptimistic
'rst.MoveLast 'get RecordCount
Set ExecuteSQL = rst
MsgString = "查询到" & rst.RecordCount & _
" 条记录 "
End If
ExecuteSQL_Exit:
Set rst = Nothing
Set cnn = Nothing
Exit Function
ExecuteSQL_Error:
MsgString = "查询错误: " & _
Err.Description
Resume ExecuteSQL_Exit
End Function
mrc.AddNew
mrc.Fields(0) = Trim(TextSB.Text)
mrc.Fields(1) = Trim(TextDMZ.Text)
mrc.Fields(2) = Trim(TextYMZ.Text)
mrc.Fields(3) = Trim(TextDay.Text)
mrc.Fields(4) = Trim(TextBZ.Text)
mrc.Fields(5) = Trim(TextBZ1.Text)
mrc.update
mrc.Close
MsgBox "修改红牌信息成功!", vbOKOnly + vbExclamation, "警告"
Set mrc = ExecuteSQL(txtSQL, Msgtext)
If mrc.EOF = False Then
MsgBox "红牌信息重复,请重新输入!", vbOKOnly + vbExclamation, "警告"
mrc.Close
ComboDMZ.Clear
ComboYMZ.Clear
TextDay.Text = ""
TextYY.Text = ""
TextJG.Text = ""
Else
mrc.Close
txtSQL = "select * from 红牌信息表"
Set mrc = ExecuteSQL(txtSQL, Msgtext)
mrc.AddNew
mrc.Fields(0) = Trim(TextBZ.Text)
mrc.Fields(1) = Trim(ComboDMZ.Text)
mrc.Fields(2) = Trim(ComboYMZ.Text)
mrc.Fields(3) = Trim(TextDay.Text)
mrc.Fields(4) = Trim(TextYY.Text)
mrc.Fields(5) = Trim(TextJG.Text)
mrc.update
MsgBox "添加红牌信息成功!", vbOKOnly + vbExclamation, "警告"
mrc.Close
End If
还有请问,学这种应用那些是必需知道的!有那些得注意!从那可以得到帮助信息等!
是不是有些不能改变的步骤~?
如:要建立这种应用,打开VB后引用ADO,下一步声明ADODB.Recordset的对像变量!还有...
可以给个频繁步骤列表么?我说得实在一头迷雾...我自己也不知道说了什么,但删了又觉得没意义了!
其实,应该是想问一些心得与体会!希望各位大侠告知
VB倒用了很长时间了,但数据库我真的没接触过!
dim cnnSQL as new adodb.connection
Dim rstSQL As ADODB.Recordset
Dim strSQL As String
'数据库连接字符串
strSQL = "Provider=SQLOLEDB.1;Persist Security Info=True;" & _
"User ID=sa;Password='';Initial Catalog=test123;Data Source=SQL服务器名"
cnnSQL.open strSQL
rstLs.Open "select * from test567", cnnSQL, adOpenKeyset, adLockOptimistic
'进行操作
With iRe
'新增一条记录
.AddNew
!NameID= "hdf"
!Item = 1
!Number = 100
!Date = cdate("2003-01-01")
!Version = 1
!PassWord = 1
.update
.MoveFirst
MsgBox !nameid
'删除第一条记录
.MoveFirst
.Delete
End With
'关闭连接
rstSQL.Close
Set rstSQL = Nothing
End Sub
Private Sub Form_Load()
Dim apppath As String
Dim DbFileName As String
Dim ConnectString As String
Dim i As Integer
Set conn = New ADODB.Connection
Dim sql As String
Set conn = New ADODB.Connection
Set rs = New ADODB.Recordset
rs.CursorLocation = adUseClient
DbFileName = App.Path & "\article.mdb"
ConnectString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & _
DbFileName & ";Persist Security Info=False;"
On Error Resume Next
With conn
.CursorLocation = adUseClient
.Open ConnectString
End With
rs.Open "select * from mz", conn, 1, 3Set rs = New ADODB.Recordset
rs.Open "select id,mc from mz", conn, 1, 3
If rs.EOF Then
Exit Sub
End IfFor i = 0 To rs.RecordCount - 1
List1.AddItem rs.Fields("mc")
List1.ItemData(i) = rs.Fields("id")
rs.MoveNext
NextEnd SubPrivate Sub List1_Click()
Dim strsql As String
strsql = "select memo from mz where id=" & List1.ItemData(List1.ListIndex)
Set rs = New ADODB.Recordset
rs.Open strsql, conn, 1, 3
Text1.Text = rs.Fields("memo")
End Sub
2.熟练使用ado对象,方便
3.使用存储过程或者触发器存储过程例子
放datagrid和5个textboxOption Explicit
Private mConn As ConnectionPrivate Sub Command1_Click()
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Dim param As ADODB.Parameter
Set cmd = New ADODB.Command
cmd.ActiveConnection = mConn
cmd.CommandText = "insert_users"
cmd.CommandType = adCmdStoredProc
Set param = cmd.CreateParameter("truename", adChar, adParamInput, 20, Trim(txttruename.Text))
cmd.Parameters.Append param
Set param = cmd.CreateParameter("regname", adChar, adParamInput, 20, Trim(txtregname.Text))
cmd.Parameters.Append param
Set param = cmd.CreateParameter("pwd", adChar, adParamInput, 20, Trim(txtpwd.Text))
cmd.Parameters.Append param
Set param = cmd.CreateParameter("sex", adChar, adParamInput, 20, Trim(txtsex.Text))
cmd.Parameters.Append param
Set param = cmd.CreateParameter("email", adChar, adParamInput, 20, Trim(txtemail.Text))
cmd.Parameters.Append param
Set rs = cmd.Execute
Command2_Click
End SubPrivate Sub Command2_Click()
Dim rs As ADODB.Recordset
Dim cmd As ADODB.Command
Dim param As ADODB.Parameter
Set rs = New ADODB.Recordset
Set cmd = New ADODB.Command
cmd.ActiveConnection = mConn
cmd.CommandText = "select_users"
cmd.CommandType = adCmdStoredProc
mConn.CursorLocation = adUseClient '设置为客户端 Set rs = cmd.Execute() MsgBox rs.RecordCount
Set DataGrid1.DataSource = rs
DataGrid1.RefreshEnd SubPrivate Sub Form_Load()
'open the connection
Set mConn = New Connection
mConn.ConnectionString = "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=TestUser;Data Source=yang"
mConn.Open
Dim rs As New ADODB.Recordset
'Set rs = New ADODB.Recordset
rs.Open "users", mConn, adOpenStatic, adLockPessimistic
MsgBox rs.RecordCountEnd SubPrivate Sub Form_Unload(Cancel As Integer) mConn.Close
Set mConn = Nothing
End Sub
以下是存储过程.写在sqlserver中存储过程
CREATE PROCEDURE insert_users @truename char(20), @regname char(20),@pwd char(20),@sex char(20),@email char(20)
AS
insert into users(truename,regname,pwd,sex,email) values(@truename,@regname,@pwd,@sex,@email)
GOCREATE PROCEDURE select_users
AS
select * from users
GO
只是在使用ADO对象时要记的引用ADO对象的驱动引擎。方法:你首先要在VB菜单中:
“工程”-->“引用”-->“Microsoft AxtiveX Data Objects 2.X Library”
注:2.X为版本号,如果你机子上有高版本的就用高版本的,如:2.5或2.6的。
给你推荐几个好文:
http://search.csdn.net/expert/topic/51/5105/2003/4/30/1726332.xml
http://search.csdn.net/expert/topic/51/5105/2003/4/29/1719681.xml
http://search.csdn.net/expert/topic/51/5105/2003/4/29/1720128.xml