insert into TableName SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'c:\MSOffice\Access\Samples\northwind.mdb';'admin';'mypwd', Orders) a where r_te_sfzh =...
insert into TableName SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'c:\MSOffice\Access\Samples\northwind.mdb';'admin';'mypwd', Orders) a where r_te_sfzh =...
谢谢斑竹。 可我的ACCESS数据库没有密码啊?怎么写呢?
where后的条件是限定数据源,既规定要从ACCESS数据库中取的数据
insert into TableName SELECT * FROM OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'c:\MSOffice\Access\Samples\northwind.mdb';'admin';'', Orders) a where r_te_sfzh =...
语法测试通过: "insert into r_techr select * from OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'D:\sjscxz\sjscxz\data\jsjlk.mdb', r_techr) a where a.R_te_sfzh = '320106521002123' and c_us_code = '320100000003'" 但报错: 未处理的“System.Data.SqlClient.SqlException”类型的异常出现在 system.data.dll 中其他信息:系统错误。
请帮忙再看看上面的问题,是什么原因导致SqlException?先谢谢了
我用VB.net,本来上传数据想用松散性的文件传输,但考虑数据的一致性和完整性,采用紧密性的事务处理,下面贴出我的全部代码: Private Function F_SendCommData(ByVal qydm As String, ByRef strReturn As String) Dim dbCn As SqlConnection '与SQL SERVER数据库连接对象 Dim dbCn1 As SqlConnection '与SQL SERVER数据库连接对象 Dim oleCn As OleDbConnection '与ACCESS数据库连接对象 Dim dbCm As New SqlCommand() 'SQL命令对象 Dim dbTrans As SqlTransaction '事物对象 Dim dbDs As DataSet Dim oleDs As DataSet Dim dbDa As SqlDataAdapter Dim oleDa As OleDbDataAdapter Dim i As Integer dbCn = New SqlConnection(ClsConnServer.ConnStr) dbCn.Open() dbCn1 = New SqlConnection(ClsConnServer.ConnStr) dbCn1.Open() oleCn = New OleDbConnection(ClsConnStr.ConnStr) oleCn.Open() dbTrans = dbCn.BeginTransaction(IsolationLevel.ReadCommitted) dbCm.Connection = dbCn dbCm.Transaction = dbTrans Try '****企业基本情况变更表(e_basec)中状态为“企业”的数据 ‘略.... '****企业技术人员申报表中(r_techr)状态为“企业”的数据。 oleDs = New DataSet() oleDa = New OleDbDataAdapter("select * from r_techr where r_te_sbzt = '企业' and c_us_code = '" & qydm & "'", oleCn) oleDa.Fill(oleDs, "r_techr") If oleDs.Tables("r_techr").DefaultView.Count > 0 Then For i = 0 To oleDs.Tables("r_techr").DefaultView.Count - 1 Dim sfzh, sblb As String Dim int_flag As Integer = 0 sfzh = oleDs.Tables("r_techr").Rows(i)("r_te_sfzh") sblb = oleDs.Tables("r_techr").Rows(i)("r_te_sblb") '和服务器上人员正式表以及人员申报表做比较(按身份证),如果申报类别不为“调动”且在正式表中有记录且记录的c_us_code不等于用户ID dbDs = New DataSet() dbDa = New SqlDataAdapter("select * from r_tech where R_te_sfzh = '" & sfzh & "' and c_us_code <> '" & qydm & "' ", dbCn1) dbDa.Fill(dbDs, "r_tech") If dbDs.Tables("r_tech").DefaultView.Count > 0 And sblb <> "调动" Then ClsReturnData.strError += "错误,身份证号为'" & sfzh & "'的人员不能申报新注册!" + Chr(13) int_flag = 1 End If dbDs = New DataSet() dbDa = New SqlDataAdapter("select * from r_techr where R_te_sfzh = '" & sfzh & "' and c_us_code = '" & qydm & "' ", dbCn1) dbDa.Fill(dbDs, "r_techr") If dbDs.Tables("r_techr").DefaultView.Count > 0 Then ClsReturnData.strError += "错误,身份证号为'" & sfzh & "'的人员不能申报新注册!" + Chr(13) int_flag = 1 End If If int_flag = 0 Then dbCm.CommandText = "insert into r_techr select * from OPENROWSET('Microsoft.Jet.OLEDB.4.0', '" & ClsConnStr.UpPath & "jsjlk.mdb', r_techr) a where a.R_te_sfzh = '" & sfzh & "' and c_us_code = '" & qydm & "'" dbCm.ExecuteNonQuery() End If Next End If dbTrans.Commit()
Catch e As Exception dbTrans.Rollback() ClsReturnData.strError += e.ToString() + Chr(13) ClsReturnData.strError += "Neither record was written to database." + Chr(13) Finally dbCn.Close() End Try strReturn = ClsReturnData.strError End Function
大力自己试一下,如果自己都通不过的话,就不要再以同样的msdn上的答案唬人家了。
to sjun66(无处不在): 字段名字不一样,应该没有关系啊,只要结构一样 insert into SQLSERVER.表名 select * from OPENROWSET('Microsoft.Jet.OLEDB.4.0', '*.mdb';'Admin';'',ACCESS.表名) a where 条件 即可 ////////////// 如果结构不一样: insert into SQLSERVER.表名(字段A,字段B) select 字段A的对应,字段B的对应 from OPENROWSET('Microsoft.Jet.OLEDB.4.0', '*.mdb';'Admin';'',ACCESS.表名) a where 条件/////我有一个问题请教各位大虾: 1、用sp_addlinksrever过程在sqlserver的master库的sysserver表添加server后(要SA的权限) USE PUBS GO -- To use named parameters: EXEC sp_addlinkedserver @server = 'ROMOTEJSJL', @provider = 'Microsoft.Jet.OLEDB.4.0', @srvproduct = 'OLE DB Provider for Jet' --@datasrc = 'd:\sjscxz\sjscxz\data\jsjlk.mdb' GO 2、事实证明可以不要下面的操作 --USE master --GO --EXEC sp_addlinkedsrvlogin 'ROMOTEJSJL','false' 3、更新 use jsjlk go insert into r_techr(C_us_code,R_te_xm) select a.C_us_code,a.R_te_xm from OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'D:\sjscxz\sjscxz\data\jsjlk.mdb';'Admin';'', r_techr) a where a.R_te_sfzh = '320106521002123' and a.c_us_code = '320100000003' GO 4、删除连接 USE PUBS GO EXEC sp_dropserver 'ROMOTEJSJL', 'droplogins' GO以上在sql qurey中运行完全正常 ????但我要在程序代码中实现,并且添加连接后只执行3的代码,程序报错: MSDTC on server 'ZHANGTB' is unavailable. //// 'ZHANGTB'是我SQL SERVER中对SQLOLEDB的连接。帮忙啊,我快急疯了。。
可我的ACCESS数据库没有密码啊?怎么写呢?
"insert into r_techr select * from OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'D:\sjscxz\sjscxz\data\jsjlk.mdb', r_techr) a where a.R_te_sfzh = '320106521002123' and c_us_code = '320100000003'"
但报错:
未处理的“System.Data.SqlClient.SqlException”类型的异常出现在 system.data.dll 中其他信息:系统错误。
Private Function F_SendCommData(ByVal qydm As String, ByRef strReturn As String)
Dim dbCn As SqlConnection '与SQL SERVER数据库连接对象
Dim dbCn1 As SqlConnection '与SQL SERVER数据库连接对象
Dim oleCn As OleDbConnection '与ACCESS数据库连接对象
Dim dbCm As New SqlCommand() 'SQL命令对象
Dim dbTrans As SqlTransaction '事物对象
Dim dbDs As DataSet
Dim oleDs As DataSet
Dim dbDa As SqlDataAdapter
Dim oleDa As OleDbDataAdapter
Dim i As Integer
dbCn = New SqlConnection(ClsConnServer.ConnStr)
dbCn.Open()
dbCn1 = New SqlConnection(ClsConnServer.ConnStr)
dbCn1.Open()
oleCn = New OleDbConnection(ClsConnStr.ConnStr)
oleCn.Open()
dbTrans = dbCn.BeginTransaction(IsolationLevel.ReadCommitted)
dbCm.Connection = dbCn
dbCm.Transaction = dbTrans
Try
'****企业基本情况变更表(e_basec)中状态为“企业”的数据
‘略....
'****企业技术人员申报表中(r_techr)状态为“企业”的数据。
oleDs = New DataSet()
oleDa = New OleDbDataAdapter("select * from r_techr where r_te_sbzt = '企业' and c_us_code = '" & qydm & "'", oleCn)
oleDa.Fill(oleDs, "r_techr")
If oleDs.Tables("r_techr").DefaultView.Count > 0 Then
For i = 0 To oleDs.Tables("r_techr").DefaultView.Count - 1
Dim sfzh, sblb As String
Dim int_flag As Integer = 0
sfzh = oleDs.Tables("r_techr").Rows(i)("r_te_sfzh")
sblb = oleDs.Tables("r_techr").Rows(i)("r_te_sblb")
'和服务器上人员正式表以及人员申报表做比较(按身份证),如果申报类别不为“调动”且在正式表中有记录且记录的c_us_code不等于用户ID
dbDs = New DataSet()
dbDa = New SqlDataAdapter("select * from r_tech where R_te_sfzh = '" & sfzh & "' and c_us_code <> '" & qydm & "' ", dbCn1)
dbDa.Fill(dbDs, "r_tech")
If dbDs.Tables("r_tech").DefaultView.Count > 0 And sblb <> "调动" Then
ClsReturnData.strError += "错误,身份证号为'" & sfzh & "'的人员不能申报新注册!" + Chr(13)
int_flag = 1
End If
dbDs = New DataSet()
dbDa = New SqlDataAdapter("select * from r_techr where R_te_sfzh = '" & sfzh & "' and c_us_code = '" & qydm & "' ", dbCn1)
dbDa.Fill(dbDs, "r_techr")
If dbDs.Tables("r_techr").DefaultView.Count > 0 Then
ClsReturnData.strError += "错误,身份证号为'" & sfzh & "'的人员不能申报新注册!" + Chr(13)
int_flag = 1
End If
If int_flag = 0 Then
dbCm.CommandText = "insert into r_techr select * from OPENROWSET('Microsoft.Jet.OLEDB.4.0', '" & ClsConnStr.UpPath & "jsjlk.mdb', r_techr) a where a.R_te_sfzh = '" & sfzh & "' and c_us_code = '" & qydm & "'"
dbCm.ExecuteNonQuery()
End If
Next
End If
dbTrans.Commit()
Catch e As Exception
dbTrans.Rollback()
ClsReturnData.strError += e.ToString() + Chr(13)
ClsReturnData.strError += "Neither record was written to database." + Chr(13)
Finally
dbCn.Close()
End Try
strReturn = ClsReturnData.strError
End Function
字段名字不一样,应该没有关系啊,只要结构一样
insert into SQLSERVER.表名 select * from OPENROWSET('Microsoft.Jet.OLEDB.4.0', '*.mdb';'Admin';'',ACCESS.表名) a where 条件 即可
//////////////
如果结构不一样:
insert into SQLSERVER.表名(字段A,字段B) select 字段A的对应,字段B的对应 from OPENROWSET('Microsoft.Jet.OLEDB.4.0', '*.mdb';'Admin';'',ACCESS.表名) a where 条件/////我有一个问题请教各位大虾:
1、用sp_addlinksrever过程在sqlserver的master库的sysserver表添加server后(要SA的权限)
USE PUBS
GO
-- To use named parameters:
EXEC sp_addlinkedserver
@server = 'ROMOTEJSJL',
@provider = 'Microsoft.Jet.OLEDB.4.0',
@srvproduct = 'OLE DB Provider for Jet'
--@datasrc = 'd:\sjscxz\sjscxz\data\jsjlk.mdb'
GO
2、事实证明可以不要下面的操作
--USE master
--GO
--EXEC sp_addlinkedsrvlogin 'ROMOTEJSJL','false'
3、更新
use jsjlk
go
insert into r_techr(C_us_code,R_te_xm) select a.C_us_code,a.R_te_xm from OPENROWSET('Microsoft.Jet.OLEDB.4.0', 'D:\sjscxz\sjscxz\data\jsjlk.mdb';'Admin';'', r_techr) a where a.R_te_sfzh = '320106521002123' and a.c_us_code = '320100000003'
GO
4、删除连接
USE PUBS
GO
EXEC sp_dropserver 'ROMOTEJSJL', 'droplogins'
GO以上在sql qurey中运行完全正常
????但我要在程序代码中实现,并且添加连接后只执行3的代码,程序报错:
MSDTC on server 'ZHANGTB' is unavailable.
////
'ZHANGTB'是我SQL SERVER中对SQLOLEDB的连接。帮忙啊,我快急疯了。。