我最近在做一个程序，开发环境是VB，用来取得、EXCEL文件上的数据，然后将数据传入到SQL数据库中
我的方法是
Dim objExcelFile As Excel.Application
Dim objWorkBook As Excel.Workbook
Dim objImportSheet As Excel.Worksheet


'打开EXCEL文件

Set objExcelFile = New Excel.Application
objExcelFile.DisplayAlerts = False
Set objWorkBook = objExcelFile.Workbooks.Open("d:\11.xls")
Set objImportSheet = objWorkBook.Sheets(1)
cdblLastColNum = objImportSheet.UsedRange.Columns.Count
cdblLastRowNum = objImportSheet.UsedRange.Rows.Count

'打开记录集，这个是我做的一个连接SQL的模块
Call OpenCon

'循环浏览EXCEL文件并将其中的数据导入到数据库中的ASTMB表中
For intCountI = 1 To 2365
For intCountL = 1 To 63
If Trim$(objImportSheet.Cells(intCountI, intCountL).Value) = "" Then
TempString = "null"
Else
TempString = objImportSheet.Cells(intCountI, intCountL).Value
select case intCountL
case 1
变量 = TempString
…………
end select

将EXCEL上的数据一个个传入变量，然后用连接SQL的模块用insert语句把变量插入到数据库中。到这我就碰到问题了
数据库里有numeric型的数据类型，我从EXCEL上取的值因该全部都是String型。于是我通过Cdbl函数将取得的值转化成DOUBLE型，传入SQL却报错。当初我以为不应该转为DOUBLE，而应该是single型，所以改了下，还是报错。于是我试了下，发现只要是给SQL表里的numeric类型传值，只要是变量就报错，常量是一点问题也没有。


请教个位高手到底是为什么，怎么解决？
万分感谢。小弟初到贵地，还请赐教。

[此贴子已经被作者于2006-6-7 11:47:01编辑过]

没人会么？
自己顶一下~~~~~~~~~~~~~

汗~~没人回答么？

人工顶下
期待高人的回答

SQL语句肯定没问题
问题就出在我更新数据库时里面有几列的数据类型是numeric
而我试了下，在VB中设置成双精度或者单精度都会报错。把SQL里的数据类型换成VARCHAR就没问题了。
问题就是不能换啊。
所以只有找出VB中对应SQL中numeric的数据类型。

从EXCEL读出来的的确是string型
但是SQL里面是numeric型
我用CDbl（）函数转化也会报错
也试过用CDec()函数转化
还是不行~~~~~~~~~`

人工顶下

[CODE]
'程序代码

Private Sub Command1_Click()
'打开EXCEL文件

Set objExcelFile = New Excel.Application
objExcelFile.DisplayAlerts = False
Set objWorkBook = objExcelFile.Workbooks.Open("d:\11.xls")
Set objImportSheet = objWorkBook.Sheets(1)
cdblLastColNum = objImportSheet.UsedRange.Columns.Count
cdblLastRowNum = objImportSheet.UsedRange.Rows.Count

'打开记录集
Call OpenCon

'循环浏览EXCEL文件并将其中的数据导入到数据库中的ASTMB表中
For intCountI = 1 To 2365
For intCountL = 1 To 63
If Trim$(objImportSheet.Cells(intCountI, intCountL).Value) = "" Then
TempString = ""
Else
TempString = objImportSheet.Cells(intCountI, intCountL).Value
Select Case intCountL
Case 1
company = TempString
Case 2
creator = TempString
Case 3
usr_group = TempString
Case 4
create_date = TempString
Case 5
modifier = TempString
Case 6
modidate = TempString
Case 7
flag = "0"
Case 8
mb001 = TempString
Case 9
mb002 = TempString
Case 10
mb003 = TempString
Case 11
mb004 = TempString
Case 12
mb005 = TempString
Case 13
mb006 = TempString
Case 14
mb007 = TempString
Case 15
mb008 = TempString
Case 16
mb009 = TempString
Case 17
mb010 = TempString
Case 18
mb011 = TempString
Case 19
mb012 = "1"
Case 20
mb013 = TempString
Case 21
If TempString = "" Then
mb014 = "0"
Else
mb014 = CDbl(TempString)
End If
Case 22
If TempString = "" Then
mb015 = "0"
Else
mb015 = CDbl(TempString)
End If
Case 23
mb016 = TempString
Case 24
mb017 = TempString
Case 25
mb018 = TempString
Case 26
If TempString = "" Then
mb019 = "0"
Else
mb019 = CDbl(TempString)
End If
Case 27
If TempString = "" Then
mb020 = "0"
Else
mb020 = CDbl(TempString)
End If
Case 28
mb021 = "0"
Case 29
mb022 = "0"
Case 30
mb023 = TempString
Case 31
mb024 = TempString
Case 32
mb025 = TempString
Case 33
mb026 = "0"
Case 34
mb027 = "0"
Case 35
mb028 = TempString
Case 36
If TempString = "" Then
mb029 = "0"
Else
mb029 = CDbl(TempString)
End If
Case 37
mb030 = TempString
Case 38
mb031 = TempString
Case 39
mb032 = TempString
Case 40
mb033 = TempString
Case 41
mb034 = "0"
Case 42
mb035 = TempString
Case 43
mb036 = TempString
Case 44
mb037 = TempString
Case 45
mb038 = TempString
Case 46
mb039 = TempString
Case 47
mb040 = TempString
Case 48
mb041 = "0"
Case 49
mb042 = TempString
Case 50
mb043 = TempString
Case 51
mb044 = TempString
Case 52
mb045 = TempString
Case 53
mb046 = TempString
Case 54
mb047 = TempString
Case 55
mb048 = TempString
Case 56
mb049 = "0"
Case 57
mb050 = TempString
Case 58
mb051 = TempString
Case 59
mb052 = "0"
Case 60
mb053 = "0"
Case 61
mb054 = TempString
Case 62
mb055 = TempString
Case 63
mb056 = "0"
End Select
End If
Next intCountL

'插入到SQL数据库中
Call OpenRes("insert into ASTMB Values('','','','','','','" + flag + "','" + mb001 + "','" + mb002 + "','" + mb003 + "','" + mb004 + "','" + mb005 + "','" + mb006 + "','" + mb007 + "','" + mb008 + "','" + mb009 + "','" + mb010 + "','" + mb011 + "','" + mb012 + "','" + mb013 + "','" + mb014 + "','" + mb015 + "','" + mb016 + "','" + mb017 + "','" + mb018 + "','" + mb019 + "','" + mb020 + "','" + mb021 + "','" + mb022 + "','" + mb023 + "','" + mb024 + "','" + mb025 + "','" + mb026 + "','" + mb027 + "','" + mb028 + "','" + mb029 + "','" + mb030 + "','" + mb031 + "','" + mb032 + "','" + mb033 + "','" + mb034 + "','" + mb035 + "','" + mb036 + "','" + mb037 + "','" + mb038 + "','" + mb039 + "','" + mb040 + "','" + mb041 + "','" + mb042 + "','" + mb043 + "','" + mb044 + "','" + mb045 + "','" + mb046 + "','" + mb047 + "','" + mb048 + "','" + mb049 + "','" + mb050 + "','" + mb051 + "','" + mb052 + "','" + mb053 + "','" + mb054 + "','" + mb055 + "','" + mb056 + "')")
Next intCountI

'关闭打开的对象

mycon.Close
objExcelFile.Quit
Set objWorkBook = Nothing
Set objImportSheet = Nothing
Set objExcelFile = Nothing
MsgBox ("导入ASTMB表成功！")
End Sub

[/CODE]
[CODE]
‘模块代码
Public mycon As New ADODB.Connection
Public myres As New ADODB.Recordset
Public strsql As String
Public Function OpenCon()
If mycon.State <> adStateOpen Then
'如果mycon没有打开 就
mycon.Open "Provider=SQLOLEDB.1;Password=sa;Persist Security Info=True;User ID=sa;Initial Catalog=fingu;Data Source=TAN"
End If
End Function
Public Function OpenRes(ByVal strsql As String)
If myres.State = adStateOpen Then
myres.Close
'如果打开了就关掉
End If
With myres
.CursorLocation = adUseClient '在客户端上运行
.CursorType = adOpenDynamic '动态游标
.Open strsql, mycon, , , adCmdText '用SQL语句打开记录集
End With
End Function

Public Function Tra(ByVal strsql As String)
mycon.BeginTrans
mycon.Execute (strsql)
mycon.CommitTrans
End Function

[/CODE]

[此贴子已经被作者于2006-6-7 11:42:18编辑过]

其中变量mb014,mb015,mb019,mb020,mb029在SQL数据库里是numeric型，。
我将这几个数据在VB里定义为double型

其他的均为CHAR型或者VARCHAR型
就报数据类型不匹配，然后当我把数据库里的numeric类型换成VARCHAR就没问题
现在问题是数据库是不可更改的
各位斑竹，大虾来指点下吧~~~~~~~~

[此贴子已经被作者于2006-6-7 11:44:39编辑过]

人工置顶~~~~~~