C# .NET - Getting Value from Mdb

Asked By Doğancan Kefeli
05-Sep-10 04:53 AM
Hi,
I m try to get a table from Microsoft Access.But, When i m try to get value with like method, i can't get table value.Null table returns.

For example My table; TABLE NAME : INFO
| ID  | Name  | Adress   | checked
 1   John    google.com   true

Mysql command for getting John : SELECT name FROM INFO WHERE 1=1 AND Name LIKE 'J*';
My c# code:
DataTable dt = Dbconnect(sql);
datagrid.source=dt;

private DataTable Dbconnect(string command)
    {
      DataTable dtParameters = new DataTable();
      string pathProject = "Var.mdb";
      string sCnstr = @"Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Registry Path=;Jet OLEDB:Database Locking Mode=1;Data Source=" + pathProject +
     ";Jet OLEDB:Engine Type=5;Provider=Microsoft.Jet.OLEDB.4.0;Jet OLEDB:System database=;Jet OLEDB:SFP=False;persist security info=False;Extended Properties=;Mode=Share Deny None;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Create System Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:Compact Without Replica Repair=False;User ID=Admin;Jet OLEDB:Global Bulk Transactions=1";
      OleDbConnection conn = new OleDbConnection(sCnstr);
      try
      {
      conn.Open();
      OleDbDataAdapter my = new OleDbDataAdapter(command, conn);
      my.Fill(dtParameters);
      return dtParameters;
      }
      finally
      {

      conn.Close();
      conn.Dispose();

      }

 ALSO

if i use sql command like "select * from INFO", i get table and value. This show that there is no problem about connection , Then i try to check query bulding in MS access , sql command returns correct result. But, in c# , result is not returned.

  Mohan Raj Aryal replied to Doğancan Kefeli
05-Sep-10 06:28 AM
Should this be type?

SELECT name FROM INFO WHERE 1=1 AND Name LIKE 'J*';

instead of 

SELECT name FROM INFO WHERE ID=1 AND Name LIKE 'J*';
  Mohan Raj Aryal replied to Doğancan Kefeli
05-Sep-10 06:30 AM
One more correction: 

SELECT name FROM INFO WHERE ID=1 AND Name LIKE 'J%';
  Doğancan Kefeli replied to Mohan Raj Aryal
05-Sep-10 06:33 AM
Thanks for reply;
but i want to get name which begins with J so 1=1 is not important. While i am checking  code(SELECT name FROM INFO WHERE 1=1 AND Name LIKE 'J*'; ) at MS access query builder , i get correct result. In C# tableset, i can't see any row or colum. This is very intresting.. While i try this code: SELECT * FROM INFO, i get all colums :(
  Doğancan Kefeli replied to Mohan Raj Aryal
05-Sep-10 07:36 AM
Thank you Mr. Mohan. I fix problem which is using "(" like that SELECT * FROM name WHERE  ( NAME LIKE 'J%')
Create New Account
help
Summary description for Form1. / / / < / summary> public class Form1 : System.Windows.Forms.Form { private System.Data.OleDb.OleDbCommand oleDbSelectCommand1; private System.Data.OleDb.OleDbCommand oleDbInsertCommand1; private System.Data.OleDb.OleDbCommand oleDbUpdateCommand1; private System.Data.OleDb.OleDbCommand oleDbDeleteCommand1; private System.Data.OleDb.OleDbConnection oleDbConnection1; private System.Data.OleDb OleDbDataAdapter oleDbDataAdapter1; / / / <summary> / / / Required designer variable. / / / < / summary> private System.ComponentModel.Container components = null; public Form1() { / / / / Required
me to do this. . . . i will be very thankful. . . . Populate the Data from database to datatable. Create the columns in listview foreach(DataColumn c in ds.Tables[0].Columns) { / / adding names else { messagebox.Show("no record is available"); } hope this will help you private void sonika() { OleDbConnection con = new OleDbConnection ( "Provider = Microsoft.Jet.OLEDB.4.0;Data Source = D: \ hotel_management.mdb;Persist Security Info = True;Jet OLEDB:Database Password = harpreetrandhawa" ); string query = "Select * from checkintable where cust_name = '" + txt_customerName.Text + "'" ; OleDbDataAdapter da = new
in Updating and inserting hi all, i have 26, 000 records imported from excel to datatable, and im exporting this records to database by looping, while inserting (some 64 records or createobject-to-create-my-recordset-objects.html hello annop, Thanks for reply i am using oledbconnection for connecting oledb dataadapter, oledbcommand to insert and update. upto few records it inserts or updates but after the code which im using for insertion and updation Dim xl_con_str As String = "provider = microsoft.jet.oledb.4.0; data source = '" & excelpath & "'; Extended Properties = 'Excel 8.0; HDR = Yes; IMEX = 1' " Dim conn As New OleDbConnection(xl_con_str) conn.Open() Dim sqlSelect As String = "SELECT * FROM [Compliance Report$];" ' The name of the worksheet followed by $. Dim ad1 = New OleDbDataAdapter(sqlSelect, conn) Dim dt1 As New DataTable ad1.Fill(dset, "child" ) dt1 = dset.Tables( "child" ) conn.Close() Dim i1 As Integer = dt1
Problem reading with Percentage values Hi, I am using OleDB provider to read excel file. I am facing the problem when reading percentage values from Connection String i am using to read excel sheet is ConnectionString = string.Format(@"Provider = Microsoft.Jet.OLEDB.4.0;Data Source = {0};Extended Properties = 'Excel 8.0;HDR = YES;IMEX = 1;MAXSCANROWS xlsx", Title = "Excel Import" }) { dialog.ShowDialog(); string fileName = dialog.FileName; if (!String.IsNullOrEmpty(fileName)) { FileInfo info = new FileInfo(fileName); string ConnectionString = string.Empty; if (info.Extension = = ".xls") { ConnectionString = string.Format(@"Provider = Microsoft.Jet.OLEDB.4.0;Data Source = {0};Extended Properties = 'Excel 8.0;HDR = YES;IMEX = 1;MAXSCANROWS 0;ImportMixedTypes = Text'", fileName); } else if (info.Extension = = ".xlsx") { ConnectionString = string.Format(@"Provider = Microsoft.ACE.OLEDB.12.0;Data Source = {0};Extended
cmd.ExecuteNonQuery() End If PlEASE HELLP! Here is the working code string strProvier = "Provider = Microsoft.Jet.OLEDB.4.0;Data Source = Employees.mdb"; OleDbConnection con = new OleDbConnection(strProvier); OleDbCommand cmd = new OleDbCommand(); int effectedRow = 0; int lineCounter = 0; con.Open(); if ((con is the working example. . Example AspNetCsvToDatabase.aspx <%@ Import Namespace = "System.Data" %> <%@ Import Namespace = "System.Data.OleDb" %> <%@ Page Language = "VB" %> <script runat = "server" > Sub Page_Load(sender As Object , e As EventArgs) Dim dt As DataTable '* ** Read CSV to DataTable * **' dt = CsvCreateDataTable( "csv / " , "customer.csv" ) '* ** Insert to Database * **' InsertToDatabase(dt) End Sub '* ** Convert CSV to DataTable * **' Function CsvCreateDataTable( ByVal strPath, ByVal strFilesName) As DataTable Dim objConn As New OleDbConnection Dim dtAdapter