Paramatized Queries don't work

[bri189a]

I've done paramatized queries several times... I have no idea what the problem could be other than possibly one of the MDAC patches from Microsoft is messed up.

 

In short everything gets put into the table in my dataset fine. I put in the OnRowUpdating Event and checked the parameters, and everything still looks great there. But when I open up the database this is what happens.

 

Say the parameters for each row being put in are the following (this is how they show up in the Locals window in the OnRowUpdating Event:

 

SOMEDATA

C#KICKSBUTT

ANYWORD

OTHER

TESTING123

BLAH

ASDFASDF

 

This is what shows up in the database:

SOMEDATA

C#KICKSB

ANYWORDB

OTHERRDB

TESTING12

BLAHING12

ASDFASDF

 

As you can see, the first row has a length of 8th. All the following rows have either been trucated to 8 characters, or if they're less than 8 they are filled up to 8 characters with the remaining letters of the previous word that had 8 characters.

 

I've never seen this before in my life, and as I've said, I've always done parametized queries and never had a problem before. Also on a side note, my FillSchema() only works if I use text SQL, not if I use parametized queries as I use to. With the above, if I don't use paramaters and use static SQL text everything works fine. Also if I call Update() after each row, rather than waiting until all rows are created, everything works fine.

[PlausiblyDamp]

Any chance you could show the relevant code for this? Creating command and it parameters etc? How is the database schema for this table declared?

[bri189a]

Sure PD - I'm really hoping you can help on this...

 

Quick run down, this part of the program collects the domains that are available to it and stores it in a database:

 

this is where the data adapter gets set up, it's part of a class called database... why a class? I use it in serveral locations...

 

               internal DataSet ds;
	internal struct GeneralSettings
	{
		internal int Tracking;
		internal int Collection;
	}
	internal GeneralSettings dbGeneralSettings = new GeneralSettings();
	private bool disposed = false;
	private bool dsDisposed = false;
	private OleDbConnection dbConn;
	private OleDbDataAdapter DOMAINS
internal DataBase(string source, string password)
	{
		if(System.IO.File.Exists(source)==false)
		{
			throw new System.IO.IOException("Invalid database path!");
		}
		cnString = "Provider=Microsoft.Jet.OLEDB.4.0;User ID=Admin;Jet OLEDB:Database Password=" + 
password + ";Data Source=" + source + ";Mode=Share Deny None;Extended Properties=\"\";Jet OLEDB:System 
database=\"\";Jet OLEDB:Registry Path=\"\";Jet OLEDB:Engine Type=5;Jet OLEDB:Database Locking Mode=1;
Jet OLEDB:Global Partial Bulk Ops=2;Jet OLEDB:Global Bulk Transactions=1;Jet OLEDB:Create System Database
=False;Jet OLEDB:Encrypt Database=False;Jet OLEDB:Don't Copy Locale on Compact=False;Jet OLEDB:
Compact Without Replica Repair=False;Jet OLEDB:SFP=False";
		dbConn = new OleDbConnection(cnString);
		ds = new DataSet("MyDatabase");
		ds.Disposed +=new EventHandler(ds_Disposed);	//bad coding could allow - just checking myself!
		//Get settings
		OleDbCommand genSettings = new OleDbCommand("SELECT * FROM GENERAL_SETTINGS", dbConn);
		dbConn.Open();
		OleDbDataReader dr = genSettings.ExecuteReader();
		dr.Read();	//should only be one record;
		dbGeneralSettings.Collection = (int) dr["CollectionSettings"];
		dbGeneralSettings.Tracking = (int) dr["TrackSettings"];
		dr.Close();
		dbConn.Close();
		genSettings.Dispose();
	}
internal void Initialize_DOMAINS()
	{
		if(this.disposed || dsDisposed)
		{
			throw new ObjectDisposedException("DataBase is disposed!");
		}
		DOMAINS = new OleDbDataAdapter();
		//Select Query - has to be SQL otherwise won't work for FillSchema...
		DOMAINS.SelectCommand = new OleDbCommand("SELECT * FROM DOMAINS", dbConn);

		//Add Query
		DOMAINS.InsertCommand = new OleDbCommand("ADD_DOMAIN", dbConn);
		DOMAINS.InsertCommand.CommandType = CommandType.StoredProcedure;
		DOMAINS.InsertCommand.Parameters.Add("DomainName", OleDbType.Char, 50, "DomainName");
		DOMAINS.InsertCommand.Parameters.Add("Track", OleDbType.Boolean, 0, "Track");

		//Update Query
		DOMAINS.UpdateCommand = new OleDbCommand("UPDATE_DOMAIN", dbConn);
		DOMAINS.UpdateCommand.CommandType = CommandType.StoredProcedure;
		DOMAINS.UpdateCommand.Parameters.Add("newDomainName", OleDbType.Char, 50, "DomainName");
		DOMAINS.UpdateCommand.Parameters.Add("newTrack", OleDbType.Boolean, 0, "Track");
		DOMAINS.UpdateCommand.Parameters.Add("Q", OleDbType.Integer, 0, "ID");

		//Delete Query
		DOMAINS.DeleteCommand = new OleDbCommand("DELETE_DOMAIN", dbConn);
		DOMAINS.DeleteCommand.CommandType = CommandType.StoredProcedure;
		DOMAINS.DeleteCommand.Parameters.Add("Q", OleDbType.Integer, 0, "ID");

		//Fill Schema
		DOMAINS.FillSchema(ds, SchemaType.Mapped, "DOMAINS");
		//Fill
		DOMAINS.Fill(ds.Tables["DOMAINS"]);
	}
internal int Update_DOMAINS()
	{
		try
		{
			int ret = DOMAINS.Update(ds.Tables["DOMAINS"]);
			ds.Tables["DOMAINS"].Clear();
			DOMAINS.Fill(ds.Tables["DOMAINS"]);
			return ret;
		}
		catch(OleDbException ex)
		{
			System.Windows.Forms.MessageBox.Show(ex.Message);
			return 0;
		}
	}

 

Now I know a lot of people aren't much on the FillSchema method because it's bloated, but this all runs in the background and is not a concern, however, I tested earlier and set up the schema manually and still had the same issues.

 

Also the UpdateDomains() function... notice I have to use Clear()... this another problem... Column ID is the primary key, this keeps duplicates from being added, however, when I don't call clear, any Domains that were deleted (because of Access side relationships to other tables in other functions), still show up in the DataSet-DataTable("DOMAINS"), unless I run the Clear() method first... again another thing that shouldn't be happening but is. But this is a lower concern to me right now... I'll deal with that another day.

 

The following is in the procedure that uses the above class:

private void frmMain_Load(object sender, System.EventArgs e)
	{
		//Get Settings - temp path while developing
		myDB = new DataBase(Environment.CurrentDirectory + @"\..\..\..\..\Program\data.mdb", "*****");
		myDB.Initialize_DOMAINS();
		GetDomains();
	}
private void GetDomains()
	{
		/* Pulls domains out of the database and adds any new domains found to
		 * the database and adds them with the setting of whether it should
		 * be tracked or not - domains are always added regardless of tracking
		 * options! */
		
		//Pause any other timers
		//Update Status box
		this.notifyIcon.Text = "Refreshing Domain List";
		//Get settings
		bool TrackNew = GetNewDomainSettings();
		//Go through list of domain and if it's not already in the database
		//add it as needed.
		System.IO.Domain [] myDomains = System.IO.Network.GetDomains();
		if(myDomains!=null)
		{
			foreach(System.IO.Domain d in myDomains)
			{
				if(myDB.ds.Tables["DOMAINS"].Select("DomainName='" + d.Name + "'").Length==0)
				{
					//Add the domain
					DataRow r = myDB.ds.Tables["DOMAINS"].NewRow();
					r["DomainName"] = d.Name;
					r["Track"] = TrackNew;
					myDB.ds.Tables["DOMAINS"].Rows.Add(r);
//						myDB.Update_DOMAINS();
				}
			}
			myDB.Update_DOMAINS();
		}
		this.notifyIcon.Text = "Name Later";
		//Resume any other timers
	}

 

If I comment out the Update() outside of the for, and un-comment the Update() inside the for, it works normally and there are no issues...except that the program lags too much, even for running in the background and you shouldn't have to (nor should you) do updating like this with ADO, in my opinion.

 

As I said, in the OnRowUpdating Event, the parameters look exactly as they should.

 

The database is Access the Table ("DOMAINS") is set up as the following:

ID : AutoNumber, Indexed=Yes(No Duplicates)

DomainName : Text, Length=50, Required=Yes, AllowZeroLength=No, Indexed=No, UnicodeCompression=No

Track: Yes/No, Format:True/False

 

The query Looks like this (it's named "ADD_DOMAIN")

 

INSERT INTO DOMAINS (DomainName, Track) VALUES (DomainName, Track);

 

I have relationships with other tables, but I took them out, and had the same issue. If I was new to ADO I'd say it's me, but the way it's acting, I don't know... hope you see something that I don't... thanks a lot!

Edited February 24, 2004 by bri189a