OleDB Questions

[KingArgyle]

Okay, after banging my head against a brick wall for the last 3 hours, I finally gave up and just coded my Insert Statement manually. Here's the question.

Can somebody post some working OleDB code for .NET that actually sets the values for a Prepared Statement's Parameters. Should have been as simple as defining the variables in the Insert string, setting the variables, and executing the prepare. However, the Parm.Value never takes, it is always Null.

Anyway, I'd rather use the Prepare than hard coding the insert string, because right now it only works on MSDE or a database that supports the CAST statement (Access doesn't).

[cginzel]

KingArgyle,

Can you post a sample of what you want to accomplish?  I haven't specifically used prepared statements in .Net, but I know they can be a real bear in Java!

I'm curious why you need a cast statement...

-Charles

[KingArgyle]

The only reason I used the CAST statement, was to make sure that a Date in String format, was converted to the DateTime format.

Basically, I was trying to do what I typically do with JDBC. I deleted the code that I had and changed it so that I don't need to rely on Prepare to create the necessary insert string. The only problem is that it is now coded to work only with MSDE, which is not what I want the long run.

I had something similar to this coded:

string sqlString = "insert into (programme_oid, capture_source_oid, status, filename, recording_type, recording_group, manual_start_time, manual_end_time, manual_channel_oid, quality_level, pre_pad_minutes, post_pad_minutes) values ( '@programOID', , 1, 0, '@Title', 0, 0, '@StartTime', '@EndTime', '@ChannelOID', 1, 0, 0)";

System.Data.OleDb.OleDbCommand dbCommand = new System.Data.OleDb.OleDbCommand(null, dbConnection);
dbCommand = sqlString;
System.Data.OleDb.OleDbParameter parm1 = new System.Data.OleDb.OleDbParamater("@programOID", System.Data.OleDb.OleDbType.Integer);
parm1.Value = program.getOID();
dbCommand.Parameters.Add(parm1);
.
.
.
dbCommand.Prepare();
dbCommand.ExecuteNonQuery();

For each of the parameters, I create the Parameter Object, set the value and Add it to the Parameter Collection. I took a look at some code on MSDN, and the example didn't work that they showed on the there. You have to have the single quotes around the variable otherwise the frameworkd doesn't find the variables. What is happening is that the Parameter object is being created correct, but the Value property is always NULL, and never gets set. No Exceptions are thrown, as I have the above code in in try / catch. I've made sure that the parameter data types are the correct value.

[cginzel]

I found the following example at http://msdn.microsoft.com/library....pic.asp

Public Sub OleDbCommandPrepare()
   Dim id As Integer = 20
   Dim desc As String = "myFirstRegion"
   Dim rConn As OleDbConnection = New OleDbConnection("Provider=SQLOLEDB;Data Source=localhost;Integrated Security=SSPI;Initial Catalog=Northwind;&quot
   rConn.Open()
   Dim command As OleDbCommand = New OleDbCommand("", rConn)

   ' Create and prepare an SQL statement.
   command.CommandText = "insert into Region (RegionID, RegionDescription) values (@id, @desc)"
   command.Parameters.Add("@id", id)
   command.Parameters.Add("@desc", desc)
   command.Prepare() ' Calling Prepare after having set the Commandtext and parameters.
   command.ExecuteNonQuery()

   ' Change parameter values and call ExecuteNonQuery.
   command.Parameters(0).Value = 21
   command.Parameters(1).Value = "mySecondRegion"
   command.ExecuteNonQuery()
End Sub

which seems to be somewhat different than what you have...

Looks like their adding the parameter values via a local variable and then subsequent executions of the insert do direct replacement of the values...  Maybe the variable must be used first to allocate memory?

Is this the code you were looking at before @MSDN?

-Charles

[cginzel]

In some other coding I've done using C# and ADO I have code that looks like the following...

conn.Open();
SQLiteCommand check = conn.CreateCommand();
check.CommandText = "select count(*) from items where title=? or link=?";
check.CreateAndAddUnnamedParameters();

SQLiteCommand insert = conn.CreateCommand();
insert.CommandText = "insert into items values (?, ?, ?)";
insert.CreateAndAddUnnamedParameters();

//loop thru the RSS items..
IDbTransaction trans = conn.BeginTransaction();

NewsItem story = handler.NextItem();
while (story != null && !backgroundWorker.CancellationPending) {
check.Parameters[0].Value = story.Title;
check.Parameters[1].Value = story.Link;

//if this story title or link is not in the database, add it!
String iresult = (String)check.ExecuteScalar();
if (iresult.Equals("0&quot) {
count++;

insert.Parameters[0].Value = story.Title;
insert.Parameters[1].Value = story.Link;
insert.Parameters[2].Value = story.Description;

insert.ExecuteNonQuery();
}

story = handler.NextItem();
}

trans.Commit();
conn.Close();

Perhaps things would work more easier for you if you were to use ADO. In my case, I'm using a SQLite database and the Finisar.SQLite ADO class at http://sourceforge.net/projects/adodotnetsqlite

Though, I'm not sure how database independant ADO is at runtime...

-Charles

[KingArgyle]

Yeah, I tried the first example, during that three hour frustration marathon, didn't work.

Unfortunatelly, the OLEDB doesn't contain the CreateUnamedParamters method. I'm pretty sure it is something really simple and obvious I'm not doing right, which is the frustrating part.

[jasonf]

Hope this helps....  It's code that I just wrote today for a client, but shows how I was able to use parameters using OLEDB:

<table border="0" align="center" width="95%" cellpadding="0" cellspacing="0"><tr><td>Code Sample </td></tr><tr><td id="CODE">            System.Data.OleDb.OleDbConnection conn = new System.Data.OleDb.OleDbConnection(ConnectionString);

           conn.Open();

           System.Data.OleDb.OleDbCommand cmd = new System.Data.OleDb.OleDbCommand();
           cmd.Connection = conn;

           cmd.CommandText = "insert into RawRequests (filename, filedate, xml) values (?, ?, ?)";
           cmd.Prepare();

           cmd.Parameters.Add("filename", System.Data.OleDb.OleDbType.VarChar, 255);
           cmd.Parameters.Add("filedate", System.Data.OleDb.OleDbType.Date);
           cmd.Parameters.Add("xml", System.Data.OleDb.OleDbType.VarChar);

           foreach (ListViewItem i in this.listView1.Items)
           {
               string d = i.SubItems[1].Text;
               string f = i.SubItems[2].Text;

               cmd.Parameters[0].Value = f;
               cmd.Parameters[1].Value = DateTime.Parse(d);
               cmd.Parameters[2].Value = "test1234";

               cmd.ExecuteNonQuery();
           }[/QUOTE]

OLEDB uses question marks as parameter placeholders (so don't worry about SQL-Server @parameter style names).

The parameter names themselves don't matter--they're used for item-level access to the collection as a convienence to the developer.

[KingArgyle]

Thanks. I'll give that a shot.

[sub]

I've found dates can cause some of these prepared statements to fail if they contain a millisecond portion. Reconstructing a new date from (year, month, day, hour, minutes, second) and using that instead resolves the problem.

[cginzel]

Hey sub, is there any possibility to changing GB-PVR to use an argument for the current date and time instead of using the now() function in your queries? That would allow us to more easily use MSDE as a backend DB (you probably saw the discussion on this subject in this other thread http://gbpvr.com/cgi-bin....st=40).

-Charles