excel 导入access的问题

[chunhan856]

cmd.CommandText = "insert into bkstudent values('" & objSheet.Cells(i, 1) & " ','" & objSheet.Cells(i, 2) & " ','" & objSheet.Cells(i, 3) & " ','" & objSheet.Cells(i, 1) & " ')"
错误提示：未处理的“System.InvalidCastException”类型的异常出现在 microsoft.visualbasic.dll 中。

其他信息: 从类型“Range”到类型“String”的强制转换无效。
我这句想要实现把excel中的数据读取到access中，这样实现对吗，大虾帮帮忙

[vlsm]

不是已经提示很清楚了吗？
objSheet.Cells(i, 1).toString()

[vlsm]

应该是objSheet.Cells(i, 1).Value.tostring()

[chunhan856]

你好： 伊朵儿，谢谢你的回复。提示由于将在索引，主关键字，或关系中创建重复的制，请求对表的改变没有成功。只把excel表中的第一行读入到access 中，你运行你的代码可以吧，使access数据库的原因吗。

[chunhan856]

为什么都不说话阿

[_NET2004]

[Quote=引用 2 楼 vlsm 的回复:]
应该是objSheet.Cells(i, 1).Value.tostring()
[/Quote]

你可以试试，但是我们一般不直接这样读取

[chunhan856]

那怎样读取阿
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim cn As New ADODB.Connection
Dim rs As New ADODB.Recordset
Dim cmd As New ADODB.Command
Dim connectionstring As String
Dim sql As String
'此方法的理解
cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\项目\读入数据\bukao.mdb"
cn.Open()
cn.CursorLocation = ADODB.CursorLocationEnum.adUseClient
sql = "select * from bkstudent"
rs.CursorLocation = ADODB.CursorLocationEnum.adUseClient
rs.Open(sql, cn, ADODB.CursorTypeEnum.adOpenDynamic, ADODB.LockTypeEnum.adLockOptimistic) '可能有错
Dim objExcel As Excel.Application
Dim objBook As Excel.Workbook
Dim objSheet As Excel.Worksheet
Dim strPicName As String
OpenFileDialog1.ShowDialog()
strPicName = OpenFileDialog1.FileName
objExcel = CreateObject("Excel.Application")
objBook = objExcel.Workbooks.Open(strPicName) '代表excel?
objExcel.Visible = True
objSheet = objBook.Worksheets(1)
Dim nRows As Integer
nRows = objExcel.Sheets(1).UsedRange.Rows.Count
TextBox1.Text = nRows - 1
Dim i As Integer

Try
For i = 1 To nRows
rs.AddNew()
cmd.CommandText = "insert into bkstudent values('" & objSheet.Cells(i, 1).GetType.ToString & " ','" & objSheet.Cells(i, 2).GetType.ToString & " ','" & objSheet.Cells(i, 3).GetType.ToString & " ','" & objSheet.Cells(i, 1).GetType.ToString & " ')"
' rs(0) = objSheet.Cells(i, 1) 'item属性为readonly
rs.Update()
rs.MoveNext()
Next
rs.Close()
cn.Close()
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
这是我的源代码，有点不明白，sql语句怎么理解，读取excel语句怎么写啊

[chunhan856]

又出现无法插入行，行必须有列值集

[yiyaoyao58958]

ADODB用错了~~
参考这个代码：

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
		Dim cn As New ADODB.Connection
		Dim rs As New ADODB.Recordset
		Dim cmd As New ADODB.Command

		cn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=E:\项目\读入数据\bukao.mdb"
		cn.Open()

		cmd.ActiveConnection = cn


		Dim objExcel As Microsoft.Office.Interop.Excel.Application
		Dim objBook As Microsoft.Office.Interop.Excel.Workbook
		Dim objSheet As Microsoft.Office.Interop.Excel.Worksheet
		Dim strPicName As String
		Dim OpenFileDialog1 As New OpenFileDialog()
		OpenFileDialog1.ShowDialog()
		strPicName = OpenFileDialog1.FileName
		objExcel = CreateObject("Excel.Application")
		objBook = objExcel.Workbooks.Open(strPicName) '代表excel?  
		objExcel.Visible = True
		objSheet = objBook.Worksheets(1)
		Dim nRows As Integer
		nRows = objExcel.Sheets(1).UsedRange.Rows.Count

		Dim i As Integer
		Try
			For i = 1 To nRows
				rs = New ADODB.Recordset
				cmd.CommandText = "insert into bkstudent values('" & objSheet.Cells(i, 1).GetType.ToString & " ','" & objSheet.Cells(i, 2).GetType.ToString & " ','" & objSheet.Cells(i, 3).GetType.ToString & " ','" & objSheet.Cells(i, 1).GetType.ToString & " ')"
				rs = cmd.Execute
			Next
			cn.Close()
		Catch ex As Exception
			MsgBox(ex.Message)
		End Try
	End Sub

[yiyaoyao58958]

另外：

objSheet.Cells(i, 1).GetType.ToString 

这个区的不是数据

取数据用的是：

objSheet.Range("b1").Value.ToString

循环取：

dim str as string ="ABCDEFG"
objSheet.Range(str.chars(i) &  j).Value.ToString

[Roy]

这个或许对你有帮助,且下面的这个程序测试通过没有问题.(vb.net 2003)

Private Function registerData(ByVal sn As String, ByVal sntype As String, _
ByVal type As String, ByVal qty As String, ByVal user As String, ByVal sdate As String)

Dim Rs As New ADODB.Recordset
Dim Conn As New ADODB.Connection

Try
Dim strConn As String

strConn = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & DataFile & "

' 使用客户端数据游标
Conn.CursorLocation = ADODB.CursorLocationEnum.adUseClient
' 打开Access的连接
Conn.Open(strConn)

' Recordser打开表People
Rs.Open("Select * FROM 表名或Table名", Conn, _
ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic)

Conn.Execute(" Insert Into 表名或Table名" & _
"(栏位) " & _
"VALUES('value') ")

Console.Write("Sorting Result Successfully!!")

Catch ex As Exception
Console.Write(ex.ToString)
Finally
Rs.Close()
Conn.Close()
End Try
End Function

[jimogaoshou]

vb.net能否给个代码，逐行读取，进入表？大虾

[jimogaoshou]

这不是 vb.net环境下的代码吧？