• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 269
  • Last Modified:

Need to convert from OleDb to SQL Express format

Hi,

Is there an easy way to reformat this code which I use with Microsoft Access to use SQL Express instead?
I do remember there being calls which are in common.

Could someone take a shot at it?

thanks,
newbiewb

            public void SynchronizeID()
            {
                  OleDbConnection conn = null;
                  OleDbCommand dbSelectCommand = null;
                  OleDbDataReader reader = null;
                  string sql = null;
                  try
                  {
                        conn = new OleDbConnection();
                        conn.ConnectionString = Handles.Preferences.DMSConnectionString;
                        conn.Open();
                        dbSelectCommand = new OleDbCommand();
                        dbSelectCommand.Connection = conn;

                        sql = "SELECT ID FROM BadgeIDs WHERE BadgeID='" + badgeID + "'";

                        dbSelectCommand.CommandText = sql;
                        reader = dbSelectCommand.ExecuteReader();
                        if ( reader.Read() )
                        {
                              this.id = reader.GetInt32(0);
                        }
                  }
                  catch (Exception ex)
                  {
                        Handles.LogException( "TLLocation.SynchronizeID()", ex, sql );
                  }
                  finally
                  {
                        if ( reader != null )
                              reader.Close();
                        if ( dbSelectCommand != null )
                              dbSelectCommand.Dispose();
                        if ( conn != null )
                        {
                              conn.Close();
                              conn.Dispose();
                        }
                  }
            }
0
newbieweb
Asked:
newbieweb
  • 3
1 Solution
 
hongjunCommented:
try this

            public void SynchronizeID()
            {
                  SqlDbConnection conn = null;
                  SqlDbCommand dbSelectCommand = null;
                  SqlDbDataReader reader = null;
                  string sql = null;
                  try
                  {
                        conn = new SqlDbConnection();
                        conn.ConnectionString = Handles.Preferences.DMSConnectionString;
                        conn.Open();
                        dbSelectCommand = new SqlDbCommand();
                        dbSelectCommand.Connection = conn;

                        sql = "SELECT ID FROM BadgeIDs WHERE BadgeID='" + badgeID + "'";

                        dbSelectCommand.CommandText = sql;
                        reader = dbSelectCommand.ExecuteReader();
                        if ( reader.Read() )
                        {
                              this.id = reader.GetInt32(0);
                        }
                  }
                  catch (Exception ex)
                  {
                        Handles.LogException( "TLLocation.SynchronizeID()", ex, sql );
                  }
                  finally
                  {
                        if ( reader != null )
                              reader.Close();
                        if ( dbSelectCommand != null )
                              dbSelectCommand.Dispose();
                        if ( conn != null )
                        {
                              conn.Close();
                              conn.Dispose();
                        }
                  }
            }
0
 
hongjunCommented:
and of course you need to add

using System.Data.SqlClient;
0
 
newbiewebAuthor Commented:
something's not right.

The compiler can not find anything starting with Sql.

SQLDbConnection or SqlDbConnection or any of the others.

and the help has nothing on any of them.

Are you sure System.Data.SqlClient is the only thing I need?
0
 
hongjunCommented:
Sorry.. Change to below

            public void SynchronizeID()
            {
                  SqlConnection conn = null;
                  SqlCommand dbSelectCommand = null;
                  SqlDataReader reader = null;
                  string sql = null;
                  try
                  {
                        conn = new SqlConnection();
                        conn.ConnectionString = Handles.Preferences.DMSConnectionString;
                        conn.Open();
                        dbSelectCommand = new SqlCommand();
                        dbSelectCommand.Connection = conn;

                        sql = "SELECT ID FROM BadgeIDs WHERE BadgeID='" + badgeID + "'";

                        dbSelectCommand.CommandText = sql;
                        reader = dbSelectCommand.ExecuteReader();
                        if ( reader.Read() )
                        {
                              this.id = reader.GetInt32(0);
                        }
                  }
                  catch (Exception ex)
                  {
                        Handles.LogException( "TLLocation.SynchronizeID()", ex, sql );
                  }
                  finally
                  {
                        if ( reader != null )
                              reader.Close();
                        if ( dbSelectCommand != null )
                              dbSelectCommand.Dispose();
                        if ( conn != null )
                        {
                              conn.Close();
                              conn.Dispose();
                        }
                  }
            }
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

  • 3
Tackle projects and never again get stuck behind a technical roadblock.
Join Now