11 Replies - 5246 Views - Last Post: 05 March 2014 - 10:12 AM

#1 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1726
  • View blog
  • Posts: 5,704
  • Joined: 25-September 09

A database insert that appears wrong... works.

Posted 05 March 2014 - 07:00 AM

OK, I started this here because it's not an issue I'm having, it's an observation of working code that is apparently manipulating the insert statement behind the scenes and I would like to hear more peoples thoughts on it and see if anyone has read anything that discusses this happening.

Yesterday, I was helping crabara on this topic: Passing a value between forms upon opening, then calling database.

As you see further down in the topic, I expressed a concern on the INSERT statements and stated that an exception should be thrown. This morning, while laying in that half sleep state where your thoughts seem clearer, it dawned on me that perhaps it was defaulting the INSERT statement to the

INSERT INTO table_name
VALUES (value1,value2,value3,...);


Even though the CommandText specifically held a string in the format of:

INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);


What dawned on me what that parameters were being specified but not used in the values section and perhaps since those parameters held values that the commandtext parser somehow *Fixed* the statement.

So I set up a simple test and lo and behold, it does indeed insert the statement properly

I set up a quick Access database and created a table called molding and just created the fields as text just to see what would happen.

        Dim cmd As New OleDbCommand
        con.Open()
        cmd.Parameters.Add(cmd.CreateParameter).ParameterName = "PartNumber"
        cmd.Parameters.Item("PartNumber").Value = "Able"
        cmd.Parameters.Add(cmd.CreateParameter).ParameterName = "PCRNumber"
        cmd.Parameters.Item("PCRNumber").Value = "Baker"
        cmd.Parameters.Add(cmd.CreateParameter).ParameterName = "Run"
        cmd.Parameters.Item("Run").Value = "Charlie"
        cmd.Parameters.Add(cmd.CreateParameter).ParameterName = "JobNo"
        cmd.Parameters.Item("JobNo").Value = "Dog"
        cmd.Parameters.Add(cmd.CreateParameter).ParameterName = "JobID"
        cmd.Parameters.Item("JobID").Value = "Eagle"

        cmd.CommandText = "INSERT INTO Molding(PartNumber, PCRNumber, Run, JobNo, JobID) VALUES (who, the, hell, cares, anyway)"
        cmd.Connection = con
        cmd.ExecuteNonQuery()
        MsgBox(cmd.CommandText)
        con.Close()


The result was no errors and a row added to the table.

Why would they allow for this to happen? It would seem that you would want the interpreter to fail on this since VALUES was specified. Or perhaps it is deeper than that, to the lower function of what is actually passed to the database regardless of the statement structure.

If anyone has any documentation that explains this, I would be interested in reading it. I've tried a few google searches but apparently, I'm unsure what you would call this to find a topic on it.

Also, I would like to apologize to crabara even though this should be corrected to avoid any confusion from someone reading the code later and having to figure this out, the code does indeed function.

This post has been edited by CharlieMay: 05 March 2014 - 07:02 AM


Is This A Good Question/Topic? 0
  • +

Replies To: A database insert that appears wrong... works.

#2 andrewsw  Icon User is offline

  • say what now
  • member icon

Reputation: 6408
  • View blog
  • Posts: 25,886
  • Joined: 12-December 12

Re: A database insert that appears wrong... works.

Posted 05 March 2014 - 07:42 AM

So it inserts Able, Baker, etc., and not who, the, etc.?

It should fail though, if you don't insert all of the fields, in the correct order.

It's a little concerning though.
Was This Post Helpful? 0
  • +
  • -

#3 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1726
  • View blog
  • Posts: 5,704
  • Joined: 25-September 09

Re: A database insert that appears wrong... works.

Posted 05 March 2014 - 07:56 AM

Yes exactly
VALUES (...) seems to be ignored and the statement is converted to
INSERT INTO Molding(value1, value2...) format

Of course you're correct, if the statement doesn't conform to the table as is always the case when not specifying the VALUES it would fail, but I just found it odd that it converted a format of INSERT INTO table (field, field) VALUES (value, value) to INSERT INTO table (value, value).
Was This Post Helpful? 0
  • +
  • -

#4 andrewsw  Icon User is offline

  • say what now
  • member icon

Reputation: 6408
  • View blog
  • Posts: 25,886
  • Joined: 12-December 12

Re: A database insert that appears wrong... works.

Posted 05 March 2014 - 08:06 AM

Access has two versions of the INSERT INTO statement:

Quote

Syntax
Multiple-record append query:
INSERT INTO target [(field1[, field2[, ]])] [IN externaldatabase] SELECT [source.]field1[, field2[, ] FROM tableexpression
Single-record append query:
INSERT INTO target [(field1[, field2[, ]])] VALUES (value1[, value2[, ])


INSERT INTO :MSDN
Perhaps 3 if we consider 'SELECT * FROM' as a third version.

This is pure speculation but I'm guessing that the first part is parsed and the VALUES clause then becomes invalid and is, foolishly, ignored. Microsoft might call it a feature ;) but it looks like a bug to me.
Was This Post Helpful? 0
  • +
  • -

#5 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1726
  • View blog
  • Posts: 5,704
  • Joined: 25-September 09

Re: A database insert that appears wrong... works.

Posted 05 March 2014 - 09:24 AM

View Postandrewsw, on 05 March 2014 - 10:06 AM, said:

This is pure speculation but I'm guessing that the first part is parsed and the VALUES clause then becomes invalid and is, foolishly, ignored. Microsoft might call it a feature ;)/>/> but it looks like a bug to me.


I agree, to me, this leave too much room for errors and oversight.

I prefer specifying fields and values in my insert statements for this reason alone.

Imagine that statement like this (where you expect to use a parameter)
"INSERT INTO Molding(PartNumber, PCRNumber, Run, JobNo, JobID) VALUES ('who', 'the', 'hell', 'cares', JobID);" (of course I'm being extreme here as one would expect to only define the parameter they would use in the statement, but...)
This would return no errors and a row result of
who | the | hell | cares | Able


since the parameters were defined and the first one was valued with "Able" it has now determined that the only parameter is the JobID in the values and uses the first value (even though it is called PartNumber in the definition. Of course knowing this about Access, it makes sense that Able would be used since there are no real "Named Parameters" with OLEDB but since it could make sense of the entire Insert statement, it now uses the VALUES side.

I cannot ever see this as more of a feature than a bug

This post has been edited by CharlieMay: 05 March 2014 - 09:25 AM

Was This Post Helpful? 0
  • +
  • -

#6 crabara  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 71
  • Joined: 03-March 14

Re: A database insert that appears wrong... works.

Posted 05 March 2014 - 09:36 AM

View PostCharlieMay, on 05 March 2014 - 07:56 AM, said:

Yes exactly
VALUES (...) seems to be ignored and the statement is converted to
INSERT INTO Molding(value1, value2...) format

Of course you're correct, if the statement doesn't conform to the table as is always the case when not specifying the VALUES it would fail, but I just found it odd that it converted a format of INSERT INTO table (field, field) VALUES (value, value) to INSERT INTO table (value, value).



Ok I may be misunderstanding here and that is highly likely. However, I think your saying that if the the statement doesn't conform to the table it will fail, eg. if I miss updating a field.

I don't know that it is converting to a INSERT INTO (value, value) because I'm ignoring certain columns, my table has more columns than just the ones I'm inserting. When I previously tried the statement of INSERT INTO Molding(value, value) of only the values I wanted to update, I got an error.

So it doesn't make sense to me that it would convert to a statement that previously didn't work. It was my understanding that I needed to specify the columns that were to be updated in order to not receive an error.

I'm probably not understand and way out of my league, but just wanted to point out the fact that my table has more fields than just the ones I'm inserting.
Was This Post Helpful? 0
  • +
  • -

#7 andrewsw  Icon User is offline

  • say what now
  • member icon

Reputation: 6408
  • View blog
  • Posts: 25,886
  • Joined: 12-December 12

Re: A database insert that appears wrong... works.

Posted 05 March 2014 - 09:38 AM

@CharlieMay I suppose it relates back to the way the Access Application uses parameters:

SELECT tblStaff.FirstName, tblStaff.Surname
FROM tblStaff
WHERE (((tblStaff.FirstName)=[NewFirstname]) AND ((tblStaff.Surname)=[NewSurname]));

and a botched attempt to make it accept more formal parameters in line with other databases.

This post has been edited by andrewsw: 05 March 2014 - 09:40 AM

Was This Post Helpful? 0
  • +
  • -

#8 andrewsw  Icon User is offline

  • say what now
  • member icon

Reputation: 6408
  • View blog
  • Posts: 25,886
  • Joined: 12-December 12

Re: A database insert that appears wrong... works.

Posted 05 March 2014 - 09:44 AM

@crabara

"INSERT INTO Assembly(PartNumber,PCRNumber,Run,JobNo) VALUES(txtPart.Text,pcr,run,txtJobNo.Text);"

Are these the first four fields of your table, and in this order?

I'm stilling going with a bug though and this shouldn't work.

This post has been edited by andrewsw: 05 March 2014 - 09:45 AM

Was This Post Helpful? 0
  • +
  • -

#9 crabara  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 71
  • Joined: 03-March 14

Re: A database insert that appears wrong... works.

Posted 05 March 2014 - 09:47 AM

They are indeed the first four fields of my table, but they are not in that order.
Was This Post Helpful? 0
  • +
  • -

#10 crabara  Icon User is offline

  • D.I.C Head

Reputation: 1
  • View blog
  • Posts: 71
  • Joined: 03-March 14

Re: A database insert that appears wrong... works.

Posted 05 March 2014 - 09:53 AM

This may be of note, I need to have the fields in the same order of the parameters otherwise the values will go to the incorrect field. I had an issue writing this that the JobNo and the Run were switched in my DB. I couldn't figure out why and the only issue I could see was that I set the parameter for Run before the parameter for JobNo.

But I called it Molding (partnumber,pcr,jobno,run) this caused the values to be switched, so I switched jobno and run and my values were in the correct spot.
Was This Post Helpful? 0
  • +
  • -

#11 CharlieMay  Icon User is offline

  • This space intentionally left blank
  • member icon

Reputation: 1726
  • View blog
  • Posts: 5,704
  • Joined: 25-September 09

Re: A database insert that appears wrong... works.

Posted 05 March 2014 - 10:03 AM

Always define your parameters in the order they appear in the statement with OLEDB.

If you've ever looked at CommandBuilder statements they appear like

INSERT INTO MyTable(field1, field2) VALUES (?, ?)

Since this is how it is interpreted, the parameter is not named, it just holds a place for the parameter collection to fill in. The first ? gets the first parameter (regardless of the name), the second ? gets the next parameter

Even if you were to use
INSERT INTO MyTable(field1, field2) VALUES (@parm1, @parm2)

If if you define and fill the collection with @parm2 before @parm1, then field1 will hold the value @parm2 has.

This is a little off topic to what your issue is because something behind the scenes is taking your incorrect insert statement and building it to work by converting a Table(field) VALUES (value) INSERT Statement into just a Table(value) statement because of where you're using your parameters.

If you defined your insert statment with values it wouldn't matter what column position it is in the database but you still would have to define your parameters in the order they appear in the statement.
Was This Post Helpful? 0
  • +
  • -

#12 andrewsw  Icon User is offline

  • say what now
  • member icon

Reputation: 6408
  • View blog
  • Posts: 25,886
  • Joined: 12-December 12

Re: A database insert that appears wrong... works.

Posted 05 March 2014 - 10:12 AM

I suppose it boils down to OLEDB not supporting named parameters. Microsoft have covered themselves, and stated this, with:

MSDN said:

The OLE DB .NET Provider does not support named parameters for passing parameters to an SQL statement or a stored procedure called by an OleDbCommand when CommandType is set to Text. In this case, the question mark (?) placeholder must be used.

OledbCommand.Parameters :MSDN

and further here:

Quote

Provider: System.Data.OleDb
Uses positional parameter markers indicated by a question mark (?).


Named parameters have worked with OLEDB, having ensured that they are supplied in the same order as in the statement. But.. we shouldn't be doing this!

This post has been edited by andrewsw: 05 March 2014 - 10:13 AM

Was This Post Helpful? 0
  • +
  • -

Page 1 of 1