问题:[microsoft][odbc microsoft access driver]标准表达式中数据类型不匹配
原因:Sql语句书写格式不标准
解决办法:
如: string sqlstr="select * from student where studentId>='" + Convert.ToInt32(this.textBox1.Text)+"'";
应改为:string sqlstr="select * from student where studentId>=" + Convert.ToInt32(this.textBox1.Text);
private void button1_Click(object sender, System.EventArgs e)
{
DataSet myDS; myDS=new DataSet(); myDS=cgtdb.GetDataFromAccess(accessDbname,"admin","123","select * from student where studentId>=" + Convert.ToInt32(this.textBox1.Text),"student" ); if(myDS!=null)
{
this.dataGrid1.DataSource =myDS.Tables[0]; MessageBox.Show(myDS.Tables[0].Rows[0][0].GetType().ToString() ) ;
} 
}Public Function DataTransition_ADODBCon() As ADODB.Connection Dim conn As New ADODB.Connection() Try ReadIniFile() conn.ConnectionString = "Provider=SQLOLEDB;Data Source='" & Trim(SqlServerName_Str_Pub) & " ';Initial Catalog='" & Trim(SqlInitDB_Str_Pub) & "';Integrated Security=SSPI;" & " ," & " '" & Trim(SqlUserName_Str_Pub) & "' " & " , " & " '" & Trim(SqlUserPassWord_Str_Pub) & "'" & ", " & " -1" Catch MsgBox(" MyOleConStr 错误号: " & Err.Number.ToString & " 错误描述 : " & Err.Description.ToString) End Try DataTransition_ADODBCon = conn End Function Public Function SqlConStr() As String Dim sqlconstring As String = "" Try ReadIniFile() sqlconstring = "data source=" & Trim(SqlServerName_Str_Pub) & "; initial catalog=" & Trim(SqlInitDB_Str_Pub) & "; persist security info=true; user id=" & Trim(SqlUserName_Str_Pub) & "; Password=" & Trim(SqlUserPassWord_Str_Pub) & "; workstation id=" & Trim(SqlServerName_Str_Pub) & "; packet size=4096" Catch MsgBox("错误号: " & Err.Number.ToString & " 错误描述 : " & Err.Description.ToString) End Try Return sqlconstring End Function Public Function SqlConStr2() As String Dim sqlconstring As String = "" Try ReadIniFile() sqlconstring = Trim(SqlServerName_Str_Pub) & "\" & Trim(SqlInitDB_Str_Pub) & "\" & Trim(SqlUserName_Str_Pub) & "\" & Trim(SqlUserPassWord_Str_Pub) Catch MsgBox("SqlConStr 错误号: " & Err.Number.ToString & " 错误描述 : " & Err.Description.ToString) End Try Return sqlconstring End Function '*************************************************************************************************************************************************' '1.2.连接ACCESS数据库 '*************************************************************************************************************************************************' Public Function Access_OleDBCon(ByVal DBPath As String) As OleDbConnection Dim OleCon As OleDbConnection = New OleDbConnection() 'Dim DBPath As String = "d:\mrfu\DriverManager.mdb" OleCon.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" & DBPath & "'" Return OleCon End Function '*************************************************************************************************************************************************' '对ACCESS数据库进行操作 '*************************************************************************************************************************************************' Public Function Access_ADODBConNeedPassword(ByVal DBPathAndDatabaseName As String, ByVal UserID As String, ByVal Password As String, ByVal SqlStr As String) As String Dim FlagStr As String = "0" Dim ConnectionString As String = "" ConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)};" & _ "DBQ=" & DBPathAndDatabaseName & ";DefaultDir=;" & _ "UID=" & UserID & ";" & _ "PWD=" & Password & ";" Dim Adocon As ADODB.Connection Try Adocon = New ADODB.Connection Adocon.ConnectionString = ConnectionString Adocon.ConnectionTimeout = 120 Adocon.CommandTimeout = 160 Adocon.Open() Adocon.Execute(SqlStr) Adocon.Close() Adocon = Nothing FlagStr = "1" Catch If Adocon.State = ConnectionState.Open Then Adocon.Close() End If FlagStr = "0" MsgBox(Err.Description.ToString, MsgBoxStyle.Exclamation, "错误提示") End Try Return FlagStr End Function '*************************************************************************************************************************************************' '访问ACCESS数据库,并返回小批量数据 '*************************************************************************************************************************************************' Public Function Access_GetDataReturnArrayList(ByVal DBPathAndDatabaseName As String, ByVal UserID As String, ByVal Password As String, ByVal SqlStr As String, ByVal TableName As String, ByVal FiledsName As String) As ArrayList Dim TempArrayList As ArrayList TempArrayList = Nothing Dim rs As ADODB.Recordset Dim ConnectionString As String = "" ConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)};" & _ "DBQ=" & DBPathAndDatabaseName & ";DefaultDir=;" & _ "UID=" & UserID & ";" & _ "PWD=" & Password & ";" Dim Adocon As ADODB.Connection Try Adocon = New ADODB.Connection Adocon.ConnectionString = ConnectionString Adocon.ConnectionTimeout = 120 Adocon.CommandTimeout = 160 Adocon.Open() rs = New ADODB.Recordset rs.Open(SqlStr, Adocon, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic, CInt(ADODB.CommandTypeEnum.adCmdText)) TempArrayList = New ArrayList '如果记录集为空,输出一个错误信息 If (rs.BOF Or rs.EOF) Then Adocon.Close() Adocon = Nothing rs = Nothing MsgBox("没有找到任何记录,请检查你的" & TableName & " 表 ", MsgBoxStyle.Information, "系统消息") Return TempArrayList End If '循环存入数据 While (Not rs.EOF) TempArrayList.Add(rs.Fields(FiledsName).Value.ToString()) rs.MoveNext() End While Adocon.Close() Adocon = Nothing rs = Nothing Catch If Adocon.State = ConnectionState.Open Then Adocon.Close() End If MsgBox(Err.Description.ToString, MsgBoxStyle.Exclamation, "错误提示") End Try Return TempArrayList End Function '*************************************************************************************************************************************************' '访问ACCESS数据库,并返回数据集 '*************************************************************************************************************************************************' Public Function GetDataFromAccess(ByVal DBPathAndDatabaseName As String, ByVal UserID As String, ByVal Password As String, ByVal SqlStr As String, ByVal TableName As String) As DataSet Dim custDA As OleDbDataAdapter = New OleDbDataAdapter Dim custDS As DataSet = New DataSet Dim rs As ADODB.Recordset Dim ConnectionString As String = "" ConnectionString = "DRIVER={Microsoft Access Driver (*.mdb)};" & _ "DBQ=" & DBPathAndDatabaseName & ";DefaultDir=;" & _ "UID=" & UserID & ";" & _ "PWD=" & Password & ";" Dim Adocon As ADODB.Connection Try Adocon = New ADODB.Connection Adocon.ConnectionString = ConnectionString Adocon.ConnectionTimeout = 120 Adocon.CommandTimeout = 160 Adocon.Open() rs = New ADODB.Recordset rs.Open(SqlStr, Adocon, ADODB.CursorTypeEnum.adOpenKeyset, ADODB.LockTypeEnum.adLockOptimistic, CInt(ADODB.CommandTypeEnum.adCmdText)) '如果记录集为空,输出一个错误信息 If (rs.BOF Or rs.EOF) Then Adocon.Close() Adocon = Nothing rs = Nothing MsgBox("没有找到任何记录,请检查你的" & TableName & " 表 ", MsgBoxStyle.Information, "系统消息") Return custDS End If '存入dataset数据 custDA.Fill(custDS, rs, "Customers") Adocon.Close() Adocon = Nothing rs = Nothing Catch If Adocon.State = ConnectionState.Open Then Adocon.Close() End If MsgBox(Err.Description.ToString, MsgBoxStyle.Exclamation, "错误提示") End Try Return custDS End Function