Solved

ODBC Connection Code for MySQL not working in Windows 2000

Posted on 2009-07-14
6
244 Views
Last Modified: 2012-05-07
I have a class in C#.NET 2.0 for an ODBC Connection to MySQL. I use it in various applications I have written. I have two Apps that needs to be placed on a Windows 200 machine but is not working.

The app works on all the XP and Vista Machines but not on the 2000 Machines. Unfortunately upgrading the OS is not an option right now.
============
Microsoft Windows 2000
5.00.2195
Service Pack 4
============
Using IDE: Microsoft Visual Studio 2005 Professional Edition Version 8.0.50727.762 (SP.050727-7600) AND Microsft .NET Framework Version 2.0.50727 SP1.
============


When starting the application it prompts the user to scan there user id. On Return button click I get this error: (SEE Attached Text file "DEBUG.txt").

I can see that there is a problem where the app tries to access the database. (SEE Coded text below for my ODBC connection).

I believe the answers rely in my ODBC class script. But...
Are there any specific configurations that must be setup for the 2000 Machines?
Is there any code that would work in Win2000? Or a modification to my own?

Thanks any help here would be appreciated.
using System;

using System.Collections.Generic;

using System.ComponentModel;

using System.Data;

using System.Drawing;

using System.Text;

using System.Windows.Forms;

 

namespace ShippingTrackingSystem

{

    class clsMySqlODBCConnection

    {

        public int QueryType = 0;

        public System.Data.Odbc.OdbcConnection OdbcCon;

        public System.Data.Odbc.OdbcCommand OdbcCom;

        public System.Data.Odbc.OdbcDataReader OdbcDR;

        public string ConStr;

        //

        private string MySqlServer = "MydbServer";

        private string MySqlPort = "MydbPort";

        private string MySqlDatabase = "MydbName";

        private string MySqlUser = "MydbUser";

        private string MySqlUserPass = "MydbPass";

        private string MySqlOption = "MydbOption";

        //

        public clsMySqlODBCConnection()

        {

            ConStr = "DRIVER={MySQL ODBC 3.51 Driver};SERVER=" + MySqlServer + ";PORT=" + MySqlPort + ";DATABASE=" + MySqlDatabase + ";UID=" + MySqlUser + ";PWD=" + MySqlUserPass + ";OPTION=" + MySqlOption + "";

            OdbcCon = new System.Data.Odbc.OdbcConnection(ConStr);

 

            try

            {

                //txtLog.AppendText("Openning connection...\r\n");

                if (OdbcCon.State == ConnectionState.Closed)

                {

                    OdbcCon.Open();

                }

                //txtLog.AppendText("Connection opened\r\n");

                //MessageBox.Show("Connection opened\r\n");

            }

            catch (System.Data.Odbc.OdbcException Ex)

            {

                //txtLog.AppendText(Ex.Message + "\r\n");

                //MessageBox.Show("Could not access the database.\r\nPlease make sure you completed the fields with the correct information and try again.\r\n\r\nMore details:\r\n" + Ex.Message, "Database connection error", MessageBoxButtons.OK, MessageBoxIcon.Error);

            }

        }

        public void MySqlDisconnection()

        {

            if (OdbcCon.State == ConnectionState.Open)

            {

                OdbcCon.Close();

                //MessageBox.Show("Connected Closed\r\n");

            }

        }

        public void ShowTables()

        {

            if (OdbcCon.State == ConnectionState.Open)

            {

                OdbcCom = new System.Data.Odbc.OdbcCommand("SHOW TABLES", OdbcCon);

                OdbcDR = OdbcCom.ExecuteReader();

                //txtLog.AppendText("Tables inside " + txtDatabase.Text + ":\r\n");

                while (OdbcDR.Read())

                {

                    //txtLog.AppendText(">> " + OdbcDR[0] + "\r\n");

                }

            }

        }

        public string[] MySQLQuery(string qry, int fldidx)

        {

            List<string> list = new List<string>();

 

            string tStr = null;     // the following is not needed: = Convert.ToString(null);

            if (OdbcCon.State == ConnectionState.Open)

            {

                OdbcCom = new System.Data.Odbc.OdbcCommand(qry, OdbcCon);

                if (fldidx >= 0)

                {

                    OdbcDR = OdbcCom.ExecuteReader();

                    while (OdbcDR.Read())

                    {

                        tStr = Convert.ToString(OdbcDR[fldidx]);

                        list.Add(tStr);

                    }

                }

                else

                {

                    OdbcCom.ExecuteNonQuery();

                    QueryType = 1;

                }

            }

            return list.ToArray();

        }

 

        public IDataReader MySQLQuery(string qry)

        {

            if (OdbcCon.State == ConnectionState.Open)

            {

                OdbcCom = new System.Data.Odbc.OdbcCommand(qry, OdbcCon);

                OdbcDR = OdbcCom.ExecuteReader();

            }

            return OdbcDR;

        }

 

        public void MySQLNONQuery(string qry)

        {

            if (OdbcCon.State == ConnectionState.Open)

            {

                OdbcCom = new System.Data.Odbc.OdbcCommand(qry, OdbcCon);

                OdbcCom.ExecuteNonQuery();

                QueryType = 1;

            }

        }

 

        public object InitializeCnnObject()

        {

            // Create and initilize a recordset to represent the User Table

            clsMySqlODBCConnection OpenConnection = new clsMySqlODBCConnection();

            return OpenConnection;

        }

    }

}

Open in new window

DEBUG.txt
0
Comment
Question by:SaltyDawg
  • 3
  • 3
6 Comments
 
LVL 21

Expert Comment

by:Craig Wagner
ID: 24851627
From your stack trace I don't see how it has anything to do with the code you posted. The error is occurring before it ever gets anywhere near you clsMySqlODBCConnection class.

System.NullReferenceException: Object reference not set to an instance of an object.
   at ShippingTrackingSystem.clsEbtronUser.ValidateUser(String user)
   at ShippingTrackingSystem.frmUserID.cmdEnter_Click(Object sender, EventArgs e)

From this stack trace I can see that the user clicks cmdEnter, which then calls a method called ValidateUser on clsEbtronUser, and that method is throwing the NullReferenceException.

I think you're looking in the wrong place for the error. What happens if the user doesn't enter anything and then clicks cmdEnter?
0
 
LVL 1

Author Comment

by:SaltyDawg
ID: 24851910
i validate for a null or trimmed zero lengthed value. If this occurs the user is prompted to enter a value. if anything is entered it is then passed through a query string to the database. I traced the variables used and the query string it creates and they all hold its values when being passed. While I can't really debug this on the 2000 machine, I checked this by using a messagebox to display the values. The messageboxes all held their values going into the MySQLQuery function.
        private void cmdEnter_Click(object sender, EventArgs e)

        {

            clsShippingTrackingSystem TrackingSystem = new clsShippingTrackingSystem();

            clsEbtronUser EbtronUser = new clsEbtronUser();
 

            bool UserAccess = false;
 

            ScanUserID = txtScanUserId.Text.Trim();
 

            if (ScanUserID.Length > 0)

            {

                TrackingSystem.SetUserString(ScanUserID);
 

                MessageBox.Show(ScanUserID);
 

                UserString = TrackingSystem.GetUserString();

                MessageBox.Show(UserString);
 

                TrackingSystem.SetUserAccess(EbtronUser.ValidateUser(UserString));
 
 

                if (!TrackingSystem.GetUserAccess())

                {

                    lblUserValidated.Text = "User Not Validated";

                }

                else

                {

                    frmUserID.ActiveForm.Close();

                }

            }

            else

            {

                lblUserValidated.Text = "Please Enter an ID Value";

            }

        }

        public bool ValidateUser(string user)

        {

            clsMySqlODBCConnection OpenConnection = new clsMySqlODBCConnection();

            clsShippingTrackingSystem TrackingSystem = new clsShippingTrackingSystem();

            //

            string UserID = null;

            string PermissionLevel = null;

            string strQry = "Select Program_Permissions.Id, Program_Permissions.Permission_Level FROM User, Program_Permissions WHERE User.UserString = '" + user + "' AND Program_Permissions.Id = User.Id AND Program_Permissions.Program_Id = '" + Convert.ToString(TrackingSystem.GetProgramID()) + "' LIMIT 0, 1";

            //

            MessageBox.Show(strQry);

            //

            OpenConnection.MySQLQuery(strQry);

            while (OpenConnection.OdbcDR.Read())

            {

                UserID = Convert.ToString(OpenConnection.OdbcDR["Id"]);

                PermissionLevel = Convert.ToString(OpenConnection.OdbcDR["Permission_Level"]);

                TrackingSystem.SetUserID(UserID);

                TrackingSystem.SetUserString(user);

                TrackingSystem.SetUserPermission(PermissionLevel);

                //

                OpenConnection.MySqlDisconnection();

                return true;

            }

            OpenConnection.MySqlDisconnection();

            return false;

        }

Open in new window

0
 
LVL 21

Expert Comment

by:Craig Wagner
ID: 24852054
The call stack doesn't lie, the error is occurring in ValidateUser(). It's probably occurring after the OpenConnection.MySQLQuery(strQry) call.

I'd put another MessageBox after line 47, 50, and 51 to make sure it's getting through all of that code.
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 1

Author Comment

by:SaltyDawg
ID: 24853600
The call stack is not lying and technically it is in ValidateUser(). However it is not actually ValidateUser() where the error occurs. The error is in  public IDataReader MySQLQuery(string qry) in my clsMySqlODBCConnection class, listed in my original post.

It steps into public IDataReader MySQLQuery(string qry) but fails the if statement:
if (OdbcCon.State == ConnectionState.Open).

Apparently the connection is not open. Each variable holds its value up to where it fails. This has to be a problem with ODBC. Currently MySQL Connector/ODBC is installed. Does anyone know if there is there a certain driver needed for Win2000/NT?
        public IDataReader MySQLQuery(string qry)

        {

            if (OdbcCon.State == ConnectionState.Open)

            {

                OdbcCom = new System.Data.Odbc.OdbcCommand(qry, OdbcCon);

                OdbcDR = OdbcCom.ExecuteReader();

            }

            return OdbcDR;

        }

Open in new window

0
 
LVL 21

Accepted Solution

by:
Craig Wagner earned 500 total points
ID: 24855394
Okay, so the exception is occurring on this line:

while (OpenConnection.OdbcDR.Read())

But the reason it is occurring is because OdbcDR is never assigned a value, thus the NullReferenceException.

I notice that the constructor of your data access class has:

            catch (System.Data.Odbc.OdbcException Ex)
            {
                //txtLog.AppendText(Ex.Message + "\r\n");
                //MessageBox.Show("Could not access the database.\r\nPlease make sure you completed the fields with the correct information and try again.\r\n\r\nMore details:\r\n" + Ex.Message, "Database connection error", MessageBoxButtons.OK, MessageBoxIcon.Error);
            }

So any exception that might be occurring when it tries to open the connection is being swallowed. Have you tried uncommenting the code so you can see if an exception is being thrown and what the details of that exception are? I'd recommend replacing the occurrences of Ex.Message with Ex.ToString() so you get the full stack trace again. There might be a clue in that exception (assuming an exception is being thrown, which seems likely given that the connection never opens) that will point you in the right direction.
0
 
LVL 1

Author Comment

by:SaltyDawg
ID: 24860030
I found the issue. It was the ODBC. Turns out I did need the MySQL Connector/ODBC updated drivers for Win2000/NT. These were installed and now everything works.

Thanks
0

Featured Post

Better Security Awareness With Threat Intelligence

See how one of the leading financial services organizations uses Recorded Future as part of a holistic threat intelligence program to promote security awareness and proactively and efficiently identify threats.

Join & Write a Comment

Suggested Solutions

This article is for Object-Oriented Programming (OOP) beginners. An Interface contains declarations of events, indexers, methods and/or properties. Any class which implements the Interface should provide the concrete implementation for each Inter…
A long time ago (May 2011), I have written an article showing you how to create a DLL using Visual Studio 2005 to be hosted in SQL Server 2005. That was valid at that time and it is still valid if you are still using these versions. You can still re…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

22 Experts available now in Live!

Get 1:1 Help Now