By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,495 Members | 1,110 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,495 IT Pros & Developers. It's quick & easy.

Problem while calling Ms Access Query from C# .net

P: 6
Hi,

Will anybody please help me?

I have a MS Access Database with 1 Tabel and 2 queries.
e.g.

Tabel "client": Client containing fields name,address,city,state,ziip

query1 "allClient": Select * from client;
query2 "SelectedClient": Select * from client where name Like '%ab%';

And I am using this database from my application developed in c# .net.

When I am reading table "Client" or query1 "allClient" through OleDbDataReader it's working perfectly.

But when I am using the same way with query2 "selectedClient" the datareader is empty eventhough there are some records in query result. The query is working nice in MS Access but not in my application.

Thanks,
Rajesh
Sep 13 '07 #1
Share this Question
Share on Google+
10 Replies


MMcCarthy
Expert Mod 10K+
P: 14,534
Change query2 as follows:

query2 "SelectedClient": Select * from client where name Like '*ab*';

The wildcard in access is "*" not "%"
Sep 14 '07 #2

P: 6
Change query2 as follows:

query2 "SelectedClient": Select * from client where name Like '*ab*';

The wildcard in access is "*" not "%"

Hi mmccarthy,

I tried like '*ab*' also but it's still not working...
When i am running in MS Access it's working very nice..
e.g. There is a query in Access "SelectedClient" : Select * from client where name Like '*ab*';

And again i am running query from c# .net "Select FName,LName from SelectedClient" and it's not working...

would you please help me?

Regards,
Rajesh
Sep 15 '07 #3

MMcCarthy
Expert Mod 10K+
P: 14,534
If it works in Access your problem is probably in C#. I'll see if I can get someone to look at it.
Sep 15 '07 #4

Frinavale
Expert Mod 5K+
P: 9,731
Hi mmccarthy,

I tried like '*ab*' also but it's still not working...
When i am running in MS Access it's working very nice..
e.g. There is a query in Access "SelectedClient" : Select * from client where name Like '*ab*';

And again i am running query from c# .net "Select FName,LName from SelectedClient" and it's not working...

would you please help me?

Regards,
Rajesh
When you say that it's not working, is there an exception or some sort of error showing up?

Could you please post the snippet of code you're using to connect to your database?

Thanks,

-Frinny
Sep 17 '07 #5

Plater
Expert 5K+
P: 7,872
query from c# .net "Select FName,LName from SelectedClient" and it's not working...
I would guess that it doesn't like to select from a query like that.
I would guess that it trys to find a table called "SelectedClient" (instead of looking for another object, i.e. a query) and since it can't find a "table" with that name it errors?
Is that correct?

If not, try doing:
"Select * from SelectedClient" and see if anything shows up?
Sep 17 '07 #6

P: 6
When you say that it's not working, is there an exception or some sort of error showing up?

Could you please post the snippet of code you're using to connect to your database?

Thanks,

-Frinny
Hi Frinny,

The code snippet is :
Expand|Select|Wrap|Line Numbers
  1.     FillReportGrid("Select FName,LName,SSN,City,State,Agi,CreatedDate,ReturnDate From ClientInfo"); //is Working.....
  2.     FillReportGrid("Select FName,LName,SSN,StreetAddr,Zip,City,State From ClientInfo1");
  3. /* is not working From C#.....but working in Access...
  4.  
  5.     ClientInfo Table :
  6.  
  7.     Field Name       Datatype
  8.     FName             Text
  9.     LName             Text
  10.     SSN                 Number
  11.     StreetAddr         Text
  12.     Zip                 Number
  13.     City             Text
  14.     State             Text
  15.     Agi                 Number
  16.     CreatedDate         Date/Time
  17.     ReturnDate         Date/Time
  18.  
  19.     ClientInfo1 Query:
  20.     SELECT * FROM ClientInfo WHERE LName Like '*f*';
  21.     */
  22.     public void FillReportGrid(String query)
  23.         {
  24.             try
  25.             {
  26.                 DBConnect.ConnectDataBase();
  27.                 OleDbCommand cmdGetUsers = new System.Data.OleDb.OleDbCommand();
  28.                 cmdGetUsers.Connection = DBConnect.accessConn;
  29.  
  30.                 dataGridReport.Rows.Clear();
  31.  
  32.                 if (SelectClauseBuilder().Length > 0)
  33.                 {
  34.                     cmdGetUsers.CommandText = query;
  35.  
  36.                     OleDbDataReader readerData = cmdGetUsers.ExecuteReader();
  37.  
  38.                     int rowNo = 0;
  39.  
  40.                     if (readerData.HasRows)
  41.                     {
  42.                         while (readerData.Read())
  43.                         {
  44.                             dataGridReport.Rows.Add();
  45.  
  46.                             for (int colNo = 0; colNo < readerData.VisibleFieldCount; colNo++)
  47.                             {
  48.                                 if (dataGridReport.Rows[rowNo].Cells[colNo].Visible)
  49.                                 {
  50.                                     dataGridReport.Rows[rowNo].Cells[colNo].Value = readerData[colNo].ToString();
  51.                                 }
  52.                             }
  53.  
  54.                             rowNo++;
  55.                         }
  56.                     }
  57.  
  58.                     readerData.Close();
  59.                 }
  60.  
  61.                 DBConnect.DisConnectDataBase();
  62.             }
  63.             catch (Exception e)
  64.             {
  65.                 System.Console.WriteLine(e.StackTrace.ToString());
  66.             }
  67.         }
  68.  
  69.     public class DBConnect
  70.     {
  71.         public static System.Data.OleDb.OleDbConnection accessConn;
  72.  
  73.         public DBConnect()
  74.         {
  75.         }
  76.  
  77.         public static void ConnectDataBase()
  78.         {
  79.             accessConn = new System.Data.OleDb.OleDbConnection();
  80.             accessConn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + System.Windows.Forms.Application.StartupPath + "\\Report.mdb";
  81.             try
  82.             {
  83.                 accessConn.Open();
  84.             }
  85.             catch (System.Data.OleDb.OleDbException e)
  86.             {
  87.                 MessageBox.Show(String.Format("Database Error: {0}", e.Message), "WinTree Application");
  88.             }
  89.             catch (System.Exception e)
  90.             {
  91.                 MessageBox.Show(String.Format("Error: {0}", e.Message), "WinTree Application");
  92.             }
  93.         }
  94.  
  95.         public static OleDbDataReader executeQuery(string str)
  96.         {
  97.             OleDbCommand command = new OleDbCommand();
  98.             command.Connection = accessConn;
  99.             command.CommandText = str;
  100.             return command.ExecuteReader();
  101.         }
  102.  
  103.         public static void executeNonQuery(string str)
  104.         {
  105.             try
  106.             {
  107.                 OleDbCommand command = new OleDbCommand();
  108.                 command.Connection = accessConn;
  109.                 command.CommandText = str;
  110.                 command.ExecuteNonQuery();
  111.             }
  112.             catch (Exception ex)
  113.             {
  114.                 MessageBox.Show(ex.ToString());
  115.             }
  116.         }
  117.  
  118.         public static void DisConnectDataBase()
  119.         {
  120.             if (accessConn != null)
  121.             {
  122.                 accessConn.Close();
  123.             }
  124.         }
  125.     }
  126.  
Thanks,
Rajesh
Sep 23 '07 #7

Frinavale
Expert Mod 5K+
P: 9,731
From the looks of this everything should work fine....
Except there's one mystery function....

What does your "SelectClauseBuilder()" function do??

Your If statement:
If SelectClauseBuilder().Length > 0 .....

Is executed before any of your database stuff happens.
Could there be an error in this function's logic?

-Frinny
Sep 24 '07 #8

P: 6
From the looks of this everything should work fine....
Except there's one mystery function....

What does your "SelectClauseBuilder()" function do??

Your If statement:
If SelectClauseBuilder().Length > 0 .....

Is executed before any of your database stuff happens.
Could there be an error in this function's logic?

-Frinny
Hi Frinny,

I have removed the code line "SelectClauseBuilder()" and still it's not working...
Is there any problem while calling a Query from MSAccess?
because It's working fine with tables but there is a porblem only with query where i have used the where clause e.g. where FirstName like "*ab*";

Thanks,
Rajesh
Sep 27 '07 #9

Plater
Expert 5K+
P: 7,872
Are saved queries in ms access considered "stored procedures"?
Sep 27 '07 #10

MMcCarthy
Expert Mod 10K+
P: 14,534
Are saved queries in ms access considered "stored procedures"?
The simple answer is yes.

Have a look at this article.


http://www.stardeveloper.com/article...1050101&page=1
Sep 27 '07 #11

Post your reply

Sign in to post your reply or Sign up for a free account.