Question CommandText

[Rossmc]

I am using an OleDbCommand object to which I add a number of OleDbParameter objects as required by my UPDATE query.

When I execute the UPDATE query I get - for example - a Datatype mismatch, but without any indication of which parameter in the query is at fault.

If I could access the actual query as executed, I could paste it directly into my databases query analyser, run it and see where the problem lies quickly and easily. The only command text I can get at is with the parameter placeholders in place rather than the actual values.

Is there a way to get at the actual command that is executed?

 

[Tom]

Putting your update statment on here would be helpful. Taking a wild guess here, I'm assuming it has to do with the order you are assigning the values to your parameters. Access could care less what name you give a parameter, it compleltly ignores the names and goes instead by the order you assigned your paremeters. This means you must pass the values to your update statement in the same exact order they fall as a parameter.

 

[cjard]

I am using an OleDbCommand object to which I add a number of OleDbParameter objects as required by my UPDATE query.

When I execute the UPDATE query I get - for example - a Datatype mismatch, but without any indication of which parameter in the query is at fault.

If I could access the actual query as executed, I could paste it directly into my databases query analyser, run it and see where the problem lies quickly and easily. The only command text I can get at is with the parameter placeholders in place rather than the actual values.

Is there a way to get at the actual command that is executed?

verify that the number of, and order of addition of your parameters and their data types are identical to the query. Note that access does not support named parameters (all named parameters behave like they are nameless) and reuse of the values.. i.e. to avoid confusing yourself dont write this:

...SELECT * FROM table WHERE col = @val or col2 =@val...
cmd.Parameters.AddWithValue("@col", value) 'value added once, because access will reuse it right? wrong


Write this:
...SELECT * FROM table WHERE col = ? or col2 = ?...
cmd.Parameters.AddWithValue("nameisirrelevant", value) 'once for each ? mark
cmd.Parameters.AddWithValue("nameisirrelevant2", value) 'once for each ? mark

At the very least, if you have named your parameters and dont want to change the query:
...SELECT * FROM table WHERE col = @val or col2 =@val...
cmd.Parameters.AddWithValue("nameisirrelevant", value) ' value added twice, once for each param regardless of its name
cmd.Parameters.AddWithValue("nameisirrelevant2", value) 'value added twice, once for each param regardless of its name

 

[Rossmc]

Well, that made for some interesting reading, but neither of you actually answered my question. I don't really care about any specific error, or even the reasons or mechanics behind it.

However, if there is an error (any error for any reason), is there a way to see the command as access saw it when it was executed?

Here is an example of how crazy this is making me:
------------------------------------------------
data below is a structure, i have checked and they are all returning a value of the expected type.

Dim Subject As New OleDbParameter("@Subject", data.Subject)
Dim Author As New OleDbParameter("@Author", data.Author)
Dim Comments As New OleDbParameter("@Comments", data.Comments)
Dim OrdsOnly As New OleDbParameter("@OrdsOnly", data.OrdsOnly)
Dim Listed As New OleDbParameter("@Listed", data.IncludeListed)
Dim Foreign As New OleDbParameter("@Foreign", data.IncludeForeign)

Dim oCmd As OleDbCommand
oCmd = MyCommand("UPDATE eExplore SET Subject = @Subject, Author = @Author, Comments = @Comments, OrdsOnly = @OrdsOnly, Listed = @Listed, Foreign = @Foreign WHERE ID = " & ID)

oCmd.Parameters.Add(Subject)
oCmd.Parameters.Add(Author)
oCmd.Parameters.Add(Comments)
oCmd.Parameters.Add(OrdsOnly)
oCmd.Parameters.Add(Listed)
oCmd.Parameters.Add(Foreign)

ocmd.ExecuteNonQuery

This currently gives me a SYNTAX error (returned by Access).

Now, If I remove just the last parameter (@Foreign) and change the oCmd = line to read:
oCmd = MyCommand("UPDATE eExplore SET Subject = @Subject, Author = @Author, Comments = @Comments, OrdsOnly = @OrdsOnly, Listed = @Listed WHERE ID = " & ID)

It works? What the hell? Those last three parameters OrdsOnly, Listed, and Foreign are all booleans and they map exactly and in order to what is in the table?

 

[Rossmc]

Arrrgh!

Do I feel like an idiot? 'Foreign' is an illegal column name in access - well in an OLEDB JET query anyway! I either enclose it in [] in the query string or change the column name in the db.

Coding - much like driving - should just be left alone some days.

 

[cjard]

I'd love to know why you parameterise the whole query, and then string concatenate the ID on the end..

"UPDATE eExplore SET [Subject] = ?, [Author] = ?, [Comments] = ?, [OrdsOnly] = ?, [Listed] = ? WHERE [id] = ? "

cmd.Parameters.AddWithValue("sub",subject.Text)
...
cmd.Parameters.AddWithValue("id",id.Text)

-
I also wish to note the following:

1)If you'd included your query in your original post, it would have helped
2)If you'd used Visual Studio to create your data access layer, this wouldnt have happened, because it [brackets field names] for you

 

[Rossmc]

Just want to say - sincerely - thanks for the replies. Was having a bad day!

I left the last parameter off the query I posted as I knew that was working and is in fact the only reason I am using parameters in the first place. I persist a bunch of objects into a binary array which I store in a OLE Object field type in the database, as such:

Dim ms As New MemoryStream
Dim bf As New BinaryFormatter
Dim ba As Byte()

bf.Serialize(ms, Filters) 'Filters=top object of a bunch of nested collection objects.
ba = ms.ToArray

Dim Contents As New OleDbParameter("@Contents", ba)

Then this:
"UPDATE eExplore SET CatID = @CatID, Title = @Title, Subject = @Subject, Author = @Author, Comments = @Comments, OrdsOnly = @OrdsOnly, Listed = @Listed, ForeignListed = @ForeignListed, Delisted = @Delisted, Suspended = @Suspended, Shortcuts = @Shortcuts, Contents = @Contents WHERE ID = " & ID

I guess all those fields could in fact be directly used in the command text - rather than parameters - except the Contents field which I don't think I can get in the table any other way.

Anyway, it's all working now and once again thanks for the help.

 

[cjard]

I guess all those fields could in fact be directly used in the command text - rather than parameters

Never, ever, ever use anything other than parameters to do data access

Just incase you missed that:

If you ever write any query, anywhere that ever uses any data that could have come from something a user put into the system, always, always, always parameterize it


Not using parameters is like giving users the source code to your app and telling them to make any changes they want before running it