sql select statement - "where" breaking 2

[chipk]

Hello all. I have the following code, with which I am attempting to select record in an Access database matching today's date, and for right now I'm simply writing that to the console. The problem I am having is that whenever I add the "where" clause, it breaks my program. If I take that one part out, and simply leave "select * from Mri2009", then it works like a champ.

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;

namespace MRILogToMRICT
{
    class Program
    {
        static void Main(string[] args)
        {
            string MyToday = DateTime.Today.ToString("G");
            OleDbConnection MyConnection = new OleDbConnection(
           @"Provider=Microsoft.Jet.OLEDB.4.0; Data Source = W:\inetpub\[URL unfurl="true"]wwwroot\ute_301_parsed\test.mdb");[/URL]
            MyConnection.Open();

            OleDbCommand MyCommand = new OleDbCommand("SELECT * FROM Mri2009 " + "WHERE DOS = ?", MyConnection); // + "WHERE DOS = 'MyToday'", MyConnection);
            

MyCommand.Parameters.AddWithValue("@DOS", OleDbType.Date.Equals(MyToday));
//also tried: just "DOS" without the @ - saw that on some web site so thought I'd give it a try.
            try
            {
               OleDbDataReader thisReader = MyCommand.ExecuteReader();


                while (thisReader.Read()) {
                    Console.WriteLine("{0} {1} {2} {3} {4} {5} {6} {7} {8} {9} {10}",
                       thisReader.GetValue(0),
                       thisReader.GetValue(1),
                       thisReader.GetValue(2),
                       thisReader.GetValue(3),
                       thisReader.GetValue(4),
                       thisReader.GetValue(5),
                       thisReader.GetValue(6),
                       thisReader.GetValue(7),
                       thisReader.GetValue(8),
                       thisReader.GetValue(9),
                       thisReader.GetValue(10));
                }
            }
            catch (OleDbException ex)
            {
                Console.WriteLine(ex.ToString());
            }
            finally
            {
                MyConnection.Close();
                Console.WriteLine("Connection Closed.");
                Console.WriteLine(MyToday);

            }
        }
    }
}

I was writing the line "MyToday" just to make sure my date variable was being assigned correctly, which it is.

 

[NESWalt]

Try putting the @ in the SELECT statement, not in the Parameters.AddWithValue and specify the field in the WHERE clause. ie

OleDbCommand MyCommand = new OleDbCommand("SELECT * FROM Mri2009 " + "WHERE FIELDNAME = @DOS", MyConnection);

MyCommand.Parameters.AddWithValue("DOS", MyToday);

 

[chipk]

Hi NESWalt, thanks for the reply.

I tried typing that exactly as you have it, and that didn't quite work, but this did, which I don't quite understand because I swear I tried this:

            OleDbCommand MyCommand = new OleDbCommand("SELECT * FROM Mri2009 " + "WHERE DOS = ?", MyConnection); 
            MyCommand.Parameters.AddWithValue("DOS", MyToday);

 

[jmeckley]

chip there are alot of red flags in the connection management above. if this is a once and done throw away code, it's not a problem, but if this is something permanent or part of a larger process you can run into problems.

1. your are opening the connection outside of the try/catch. this is the first place an error can happen if there is a problem establishing a connection.
2. you never dispose of the connection or command. again, if an exception is thrown you can have phantom connections in memory which can lead to performance issues.
3. accessing data data via ordinals won't mean much in a day/week/month. what is ordinal 1? it's always better to access the columns by there name. it explicitly defines what you are accessing.
4. it's always good to get the data and get out as quickly as possible. I would load the reader into a datatable then write the datatable to the console.
5. manage the connection string from the app.config file, not string variable.

var records = new DataTable();
try
{
   var connectionString = ConfigurationManager.ConnectionStrings["key name"].ConnectionString;
   using(var connection = new OleDbConnection(connectionString))
   {
      connection.Open();
      using(var command = connection.CreateCommand())
      {
          command.CommandText = "the sql statement";
          //add parameters
          records.Load(command.ExecuteReader());
      }
   }
}
catch(Exception e)
{
   Console.WriteLine(e.ToString());
}
foreach(DataRow record in records.Rows)
{
   foreach(DataColumn column in records.Columns)
   {
      Console.Write("{0} ", record[column.ColumnName]);
   }
   Console.WriteLine("");
}

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[chipk]

Jason,

I REALLY appreciate the additional feedback. This is a "once and done," quick and dirty, stand-alone program, but I definitely want to learn to do things correctly. I'm an amateur trying to plow my way through learning as I go with C#. Thanks!

 

[jmeckley]

chip I recommend 2 websites that will help apply OOP to .net

http://www.lostechies.com

http://www.codebetter.com

not all content is related to .net. I would say most is though. there are some brilliant minds on these blogs.

Jason Meckley
Programmer
Specialty Bakers, Inc.

faq855-7190
faq732-7259

 

[chipk]

Bookmarked and will definitely check them out. Thanks!