ADODB操作ACCESS数据库字段的问题-『提问交流』-AUTOIT CN

卫和谐 发表于 2010-1-19 15:18:41

ADODB操作ACCESS数据库字段的问题

学习871224的数据言库操作时，改写个东东时，发现字段超过8个就出错,原贴：
http://www.autoitx.com/forum.php?mod=viewthread&tid=10554&highlight=%C9%BE
我改写的的代码：#include <ButtonConstants.au3>
#include <GUIConstantsEx.au3>
#include <ListViewConstants.au3>
#include <WindowsConstants.au3>
#include <GUIListView.au3>
#include <GUIConstants.au3>
#include <EditConstants.au3>

Global $nLabel ;定义Label数量
Global $ninput ;定义input数量
Global $tabmain = "tabmain"
Global $Dll, $name_1, $pass_1, $qq_1, $w, $arr, $tblname,$e = 0,$T = "*",$mdb_data_path = "BB.mdb",$mdb_data_pwd = ""

#Region ### START Koda GUI section ### Form=
$Form1 = GUICreate("***XXXX报备系统***", 800, 600, 100, 50)
;查看是否有皮肤
If FileExists("SkinCrafterDll.dll") Then
$search = FileFindFirstFile("*.skf")
If $search <> -1 Then
_SkinGUI("SkinCrafterDll.dll", "Quadro.skf", $Form1)
EndIf
EndIf
GUICtrlCreateGroup("", 5, 0, 790, 325)
$ListView0 = GUICtrlCreateListView("ID | 表名 ", 10, 15, 142, 300)
$ListView1 = GUICtrlCreateListView("| ||| | | | | | ", 155, 15, 635, 300);返回控件标识符(控件ID).
GUICtrlCreateGroup("", 5, 325, 790, 265)
GUICtrlCreateGroup("1、创建数据库", 5, 335, 260, 50)
GUICtrlCreateGroup("2、创建表及字段", 5, 390, 260, 190)
GUICtrlCreateGroup("3、添加、修改、删除数据", 270, 335, 525, 245)
$Button0 = GUICtrlCreateButton("创建数据库", 35, 355, 200, 25, 0)
$Button5 = GUICtrlCreateButton("添加数据", 350, 550, 100, 25, 0)
$Button2 = GUICtrlCreateButton("修改数据", 490, 550, 100, 25, 0)
$Button3 = GUICtrlCreateButton("删除数据", 630, 550, 100, 25, 0)
GUICtrlCreateLabel("输入表名：", 10, 410, 100, 20)
$tblname_1 = GUICtrlCreateInput("", 110, 410, 150, 20)
GUICtrlCreateLabel("输入字段及类型：", 10, 440, 100, 20)
$zdnameandtype_1 = GUICtrlCreateInput("", 10, 465, 250, 80,$ES_MULTILINE)
$Tips = GUICtrlCreateLabel("例如：name char,age int", 110, 440, 150, 20)
GUICtrlSetState($Tips, $GUI_DISABLE)
$Button6 = GUICtrlCreateButton("创建表及定段", 35, 550, 200, 25, 0)
If FileExists($mdb_data_path) Then
read_tabmain()
EndIf
;AdlibEnable("sendmsg",50)
GUIRegisterMsg($WM_NOTIFY, "WM_NOTIFY");为已知的Windows 消息代码(WM_MSG)注册一个用户自定义的函数

Func WM_NOTIFY($hWndGUI, $MsgID, $WParam, $LParam)
Local $tagNMHDR, $Event, $hWndFrom, $IDFrom
Local $tagNMHDR = DllStructCreate("int;int;int", $LParam)
If @error Then Return $GUI_RUNDEFMSG
$IDFrom = DllStructGetData($tagNMHDR, 2);返回数据结构(struct)元素的数据
$Event = DllStructGetData($tagNMHDR, 3)
$tagNMHDR = 0
Switch $IDFrom;选择产生事件的控件
Case $ListView1
Switch $Event; 选择产生的事件
Case $NM_CLICK ; 左击
Case $NM_DBLCLK ; 双击
$Strn = GUICtrlRead(GUICtrlRead($ListView1));列表视图数据
$Strnspin = StringSplit($Strn, "|");以指定分隔符把字符串拆分成若干子串
If $Strnspin > 0 Then
GUICtrlSetData($ninput, StringStripWS($Strnspin, 2));StringStripWS()删去字符串中的"空白符".
GUICtrlSetData($ninput, StringStripWS($Strnspin, 2))
GUICtrlSetData($ninput, StringStripWS($Strnspin, 2))
GUICtrlSetData($ninput, StringStripWS($Strnspin, 2))
GUICtrlSetData($ninput, StringStripWS($Strnspin, 2))
GUICtrlSetData($ninput, StringStripWS($Strnspin, 2))
GUICtrlSetData($ninput, StringStripWS($Strnspin, 2))
GUICtrlSetData($ninput, StringStripWS($Strnspin, 2))
GUICtrlSetData($ninput, StringStripWS($Strnspin, 2))
GUICtrlSetData($ninput, StringStripWS($Strnspin, 2))

EndIf
Case $NM_RCLICK ; 右击
EndSwitch
Case $ListView0
Switch $Event; 选择产生的事件
Case $NM_CLICK ; 左击
Case $NM_DBLCLK ; 双击
$Strn = GUICtrlRead(GUICtrlRead($ListView0))
$Strnspin = StringSplit($Strn, "|")
If $Strnspin > 0 Then
$tabname = $Strnspin
du($tabname)
If $e = 0 Then
adds($w, $arr)
$e = $w
Else
dels($e)
adds($w, $arr)
$e = $w
EndIf
EndIf
Case $NM_RCLICK ; 右击
EndSwitch
EndSwitch
Return $GUI_RUNDEFMSG
EndFunc ;==>WM_NOTIFY

Func sendmsg()
$Strn = GUICtrlRead(GUICtrlRead($ListView1))
$Strnspin = StringSplit($Strn, "|")
If $Strnspin > 0 Then
GUICtrlSetData($name_1, StringStripWS($Strnspin, 2))
GUICtrlSetData($pass_1, StringStripWS($Strnspin, 2))
GUICtrlSetData($qq_1, StringStripWS($Strnspin, 2))
EndIf
EndFunc ;==>sendmsg

GUISetState(@SW_SHOW)
#EndRegion ### END Koda GUI section ###

While 1
Dim $tabname
$Strn = GUICtrlRead(GUICtrlRead($ListView0))
$Strnspin = StringSplit($Strn, "|")
If $Strnspin > 0 Then
$tabname = $Strnspin
EndIf
$nMsg = GUIGetMsg()
Switch $nMsg
Case $GUI_EVENT_CLOSE
Quit()
Case $Button0
data($mdb_data_path, $tabmain)
Case $Button5;写入
If $Strnspin > 0 Then
sx($tabname)
du($tabname)
Else
MsgBox(0, "提示", "请输入相应的数据！")
EndIf
Case $Button2 ;更新
If $Strnspin > 0 Then
up($tabname)
du($tabname)
Else
MsgBox(0, "提示", "请选择要更新的数据！")
EndIf
Case $Button3 ;删除
If $Strnspin > 0 Then
del($tabname)
du($tabname)
Else
MsgBox(0, "提示", "请选择要删除的数据！")
EndIf
Case $Button6 ;创建新表
$tblname = GUICtrlRead($tblname_1) ;读取输入的表名
$zdnameandtype = GUICtrlRead($zdnameandtype_1) ;读取输入的字段
If $tblname <> "" Then
If $zdnameandtype <> "" Then
newtable($mdb_data_path, $tblname, $zdnameandtype)
read_tabmain()
Else
MsgBox(0, "提示", "请输入要创建表的字段及类型！")
EndIf
Else
MsgBox(0, "提示", "请输入要创建表的名称！")
EndIf
EndSwitch
WEnd

Func du_table($tblname)
GUICtrlSendMsg($ListView0, $LVM_DELETEALLITEMS, 0, 0)
$addfld = ObjCreate("ADODB.Connection")
$addfld.Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $mdb_data_path)
$RS = ObjCreate("ADODB.Recordset")
$RS.ActiveConnection = $addfld
$RS.Open("Select " & $T & " From " & $tblname)
While Not $RS.eof And Not $RS.bof
If @error = 1 Then ExitLoop
GUICtrlCreateListViewItem($RS.Fields(0).value & "|" & $RS.Fields(1).value & "|" & $RS.Fields(2).value & "|" & $RS.Fields(3).value, $ListView1)
; MsgBox(0, "", $RS.Fields (1).value);显示表第一个数据
$RS.movenext
WEnd
$RS.close
$addfld.Close
EndFunc ;==>du_table

Func adds($w, $arr) ;建立Label(数量)
For $i = 0 To $w - 1
$y = StringSplit($arr[$i + 1], " ") ;以指定分隔符把字符串拆分成若干子串,返回一个数组,第一个元素($array)保存拆分后子串的数量,其余元素($array,$array 等等)则保存着拆分后的每个字符串
$nLabel[$i] = GUICtrlCreateLabel($y, 280, 355 + 20 * $i, 60, 20)
$ninput[$i] = GUICtrlCreateInput("", 340, 355 + 20 * $i, 450, 20)
Next
EndFunc ;==>adds

Func dels($w) ;清除Label(数量)
For $i = 0 To $w - 1
GUICtrlDelete($nLabel[$i])
GUICtrlDelete($ninput[$i])
Next
EndFunc ;==>dels

Func du($tabname)
$addfld = ObjCreate("ADODB.Connection")
$addfld.Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $mdb_data_path)
$RS = ObjCreate("ADODB.Recordset")
$RS.ActiveConnection = $addfld
$RS.Open("Select " & $T & " From " & $tabmain & " where name = '" & $tabname & "'")
$temp = StringStripWS($RS.Fields(2).value, 2) ;删去字符串中的所有"空白符"
$arr = StringSplit($temp, ",") ;以指定分隔符把字符串拆分成若干子串
$w = $arr
GUICtrlSendMsg($ListView1, $LVM_DELETEALLITEMS, 0, 0)
$addfld = ObjCreate("ADODB.Connection")
$addfld.Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $mdb_data_path)
$RS = ObjCreate("ADODB.Recordset")
$RS.ActiveConnection = $addfld
$RS.Open("Select " & $T & " From " & $tabname)
Dim $n
While Not $RS.eof And Not $RS.bof
If @error = 1 Then ExitLoop
For $i = 0 To $w
;MsgBox(0,"",StringStripWS($RS.Fields ($i).value,2))
$n = $n & StringStripWS($RS.Fields($i).value, 2) & "|"
Next
;MsgBox(0,"",StringTrimRight ($n, 1))
GUICtrlCreateListViewItem(StringTrimRight($n, 1), $ListView1)
$RS.movenext
$n = ""
WEnd
$RS.close
$addfld.Close
Dim $strs = "ID|"
For $x = 1 To $w
$y = StringSplit($arr[$x], " ")
$strs = $strs & $y & "|"
Next
GUICtrlSetData($ListView1, "| ||| | | | | | ")
GUICtrlSetData($ListView1, $strs)
EndFunc ;==>du

Func del($tabname)
$Strn = GUICtrlRead(GUICtrlRead($ListView1))
$Strnspin = StringSplit($Strn, "|")
$addfld = ObjCreate("ADODB.Connection")
$addfld.Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $mdb_data_path)
$sQuery = "DELETE FROM " & $tabname & " IN '" & $mdb_data_path & "' WHERE id" & " = " & $Strnspin
; MsgBox(0,"",$sQuery)
$addfld.execute($sQuery)
$addfld.close
MsgBox(4096, "提示:", $Strnspin & " 删除成功!!!")
EndFunc ;==>del

Func up($tabname)
$Strn = GUICtrlRead(GUICtrlRead($ListView1))
$Strnspin = StringSplit($Strn, "|")
$addfld = ObjCreate("ADODB.Connection")
$addfld.Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $mdb_data_path)
$RS = ObjCreate("ADODB.Recordset")
$RS.ActiveConnection = $addfld
$RS.Open("Select " & $T & " From " & $tabmain & " where name = '" & $tabname & "'")
$temp = StringStripWS($RS.Fields(2).value, 2)
$arr = StringSplit($temp, ",")
$w = $arr
Dim $h
For $i = 1 To $w
$arrs = StringSplit($arr[$i], " ")
$h = $h & $arrs & " = '" & GUICtrlRead($ninput[$i - 1]) & "',"
Next
$sQuery = "update " & $tabname & " set " & StringTrimRight($h, 2) & "' WHERE id" & " = " & $Strnspin
;MsgBox(0,"",$sQuery)
$addfld.execute($sQuery)
$addfld.close
MsgBox(4096, "提示:", $Strnspin & " 更新成功!!!")
EndFunc ;==>up

Func sx($tabname)
$addfld = ObjCreate("ADODB.Connection")
$addfld.Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $mdb_data_path)
$RS = ObjCreate("ADODB.Recordset")
$RS.ActiveConnection = $addfld
$RS.Open("Select " & $T & " From " & $tabmain & " where name = '" & $tabname & "'")
$temp = StringStripWS($RS.Fields(2).value, 2)
$arr = StringSplit($temp, ",")
$w = $arr
Dim $k, $m
For $i = 1 To $w
$y = StringSplit($arr[$i], " ")
$k = $k & $y & ","
$m = $m & GUICtrlRead($ninput[$i - 1]) & "','"
Next
;MsgBox(0,"","insert into "& $tabname &"("& StringTrimRight ( $k,1) &") values('"& StringTrimRight ( $m,3) &"')")
$addfld = ObjCreate("ADODB.Connection")
$addfld.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & $mdb_data_path & ";Jet Oledb:Database Password=" & $mdb_data_pwd)
$addfld.Execute("insert into " & $tabname & "(" & StringTrimRight($k, 1) & ") values('" & StringTrimRight($m, 3) & "')")
$addfld.close
MsgBox(4096, "提示:", " 成功写入数据库！")
EndFunc ;==>sx

Func read_tabmain()
GUICtrlSendMsg($ListView0, $LVM_DELETEALLITEMS, 0, 0) ;向指定控件发送消息
$addfld = ObjCreate("ADODB.Connection")
$addfld.Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $mdb_data_path)
$RS = ObjCreate("ADODB.Recordset")
$RS.ActiveConnection = $addfld
$RS.Open("Select * from tabmain")
While Not $RS.eof And Not $RS.bof
If @error = 1 Then ExitLoop
GUICtrlCreateListViewItem($RS.Fields(0).value & "|" & StringStripWS($RS.Fields(1).value, 2), $ListView0)
; MsgBox(0, "", $RS.Fields (1).value);显示表第一个数据
$RS.movenext
WEnd
$RS.close
$addfld.Close
EndFunc ;==>read_tabmain

Func data($mdb_data_path, $tabmain)
If Not FileExists($mdb_data_path) Then
$newMdb = ObjCreate("ADOX.Catalog")
$newMdb.Create("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $mdb_data_path)
$newMdb.ActiveConnection.Close;建数据库
$addtbl = ObjCreate("ADODB.Connection")
$addtbl.Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $mdb_data_path)
$addtbl.Execute("CREATE TABLE " & $tabmain)
$addtbl.Close;建表
$addfld = ObjCreate("ADODB.Connection")
$addfld.Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $mdb_data_path)
$addfld.Execute("ALTER TABLE " & $tabmain & " ADD id identity(1, 1) primary key,name char,zdnameandtype char");id identity(1, 1) primary key为自动编号和主键
$addfld.Close;建列表名
MsgBox(64, "提示", "建立数据库成功", 5)
Else
MsgBox(64, "提示", "你已经建立了一个数据库了", 5)
EndIf
EndFunc ;==>data

Func newtable($mdb_data_path, $tblname, $zdnameandtype)
$addtbl = ObjCreate("ADODB.Connection")
$addtbl.Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $mdb_data_path)
;添加表之前查一下是否表明存在
$RS = ObjCreate("ADODB.Recordset")
$RS.ActiveConnection = $addtbl
$RS.Open("select * from tabmain where name = '" & $tblname & "'")
If Not $RS.eof And Not $RS.bof Then
MsgBox(0, "提示", "新建失败，改表明已存在！")
Else
$addtbl.Execute("CREATE TABLE " & $tblname)
$addtbl.Close;建表
$addfld = ObjCreate("ADODB.Connection")
$addfld.Open("Provider=Microsoft.Jet.OLEDB.4.0; Data Source=" & $mdb_data_path)
$addfld.Execute("ALTER TABLE " & $tblname & " ADD id identity(1, 1) primary key," & $zdnameandtype) ;id identity(1, 1) primary key为自动编号和主键
$addfld.Close;建列表名
$addfld = ObjCreate("ADODB.Connection")
$addfld.Open("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & $mdb_data_path & ";Jet Oledb:Database Password=" & $mdb_data_pwd)
$addfld.Execute("insert into tabmain (name,zdnameandtype) values('" & $tblname & "','" & $zdnameandtype & "')")
$addfld.close;新建的表放到该表中
EndIf
EndFunc ;==>newtable

Func _SkinGUI($SkincrafterDll, $SkincrafterSkin, $Handle)
$Dll = DllOpen($SkincrafterDll)
DllCall($Dll, "int:cdecl", "InitLicenKeys", "wstr", "1", "wstr", "", "wstr", "1@1.com", "wstr", "1")
DllCall($Dll, "int:cdecl", "InitDecoration", "int", 1)
DllCall($Dll, "int:cdecl", "LoadSkinFromFile", "wstr", $SkincrafterSkin)
DllCall($Dll, "int:cdecl", "DecorateAs", "int", $Handle, "int", 25)
DllCall($Dll, "int:cdecl", "ApplySkin")
EndFunc ;==>_SkinGUI

Func Quit()
GUISetState(@SW_HIDE)
DllCall($Dll, "int:cdecl", "DeInitDecoration")
DllCall($Dll, "int:cdecl", "RemoveSkin")
DllClose($Dll)
;DirRemove(@AppDataDir&"\skin",1)
Exit
EndFunc ;==>Quit研究了一下，未找到原因，提示第319行“$addfld.Execute("ALTER TABLE " & $tblname & " ADD id identity(1, 1) primary key," & $zdnameandtype) ;id identity(1, 1) ”这行出错，请高手帮助解决下！

卫和谐 发表于 2010-1-20 11:00:46

这个请大家帮忙看下，正在做个东西！！！

卫和谐 发表于 2010-1-21 11:46:07

怎么没人关心我的问题，太生气了:face (6):

renly 发表于 2010-3-9 20:19:12

回复 3# 卫和谐

我也是同样问题估计是数据类型匹配问题查查看

wgboy 发表于 2010-3-12 08:35:33

都一样，，我也出现运行到$addfld.Execute这一步出错。不知道是不是真的跟数据类型有关系。

yeqing880 发表于 2010-3-12 10:25:35

数据类型为 name char 与 age int

下面的类型填写name char 或者 age int 就可以了

l5mygirl 发表于 2010-3-19 09:57:47

厉害啊,,,能不能帮忙
http://www.autoitx.com/thread-13673-1-1.html

298311657 发表于 2010-3-19 12:01:23

本帖最后由 298311657 于 2010-3-19 12:02 编辑

$addfld.Execute("CREATE TABLE " & $tabmain & " id int identity(1, 1) primary key,name char,zdnameandtype char")
试试这样看，建表与添加字段一起

gdmgb520 发表于 2010-4-5 16:50:14

额，待会看看

页: [1]

AUTOIT CN's Archiver

ADODB操作ACCESS数据库字段的问题