where clause on Update

[Alex_bg]

Hello,

 

If I use a OleDBDataAdapter wizard to generate Update and Insert commands on my select that is :

SELECT Product, [Product group], [Main product], [EIS number], Description, [PCB AN], [sMD lot size], [Finished product], [Machine assembly sequence], [Two side SMD] FROM Products

 

I get the following code for update generated by the wizard:

 

Me.OleDbUpdateCommand1.CommandText = "UPDATE Products SET [Product group] = ?, [Main product] = ?, [EIS nu" & _

"mber] = ?, Description = ?, [PCB AN] = ?, [sMD lot size] = ?, [Finished product]" & _

" = ?, [Machine assembly sequence] = ?, [Two side SMD] = ? WHERE (Product = ?) AN" & _

"D (Description = ? OR ? IS NULL AND Description IS NULL) AND ([EIS number] = ? O" & _

"R ? IS NULL AND [EIS number] IS NULL) AND ([Finished product] = ?) AND ([Machine" & _

" assembly sequence] = ? OR ? IS NULL AND [Machine assembly sequence] IS NULL) AN" & _

"D ([Main product] = ? OR ? IS NULL AND [Main product] IS NULL) AND ([PCB AN] = ?" & _

") AND ([Product group] = ? OR ? IS NULL AND [Product group] IS NULL) AND ([sMD l" & _

"ot size] = ? OR ? IS NULL AND [sMD lot size] IS NULL) AND ([Two side SMD] = ?)"

 

Why is this code like ([Product group] = ? OR ? IS NULL AND [Product group] IS NULL) genrated? What it actually does?

 

If I write the update clause myself it would be with a very short where clause (product is a primary key) like:

 

Me.OleDbUpdateCommand1.CommandText = "UPDATE Products SET [Product group] = ?, [Main product] = ?, [EIS nu" & _

"mber] = ?, Description = ?, [PCB AN] = ?, [sMD lot size] = ?, [Finished product]" & _

" = ?, [Machine assembly sequence] = ?, [Two side SMD] = ? WHERE (Product = ?)

 

What is the difference in the functionality in those two different ways of writing the where clause?

 

I will be glad to get some links that will help me find information about this.

 

Thanks,

Aleksandar

[Nerseus]

Other than very small, simple apps or test projects, I've not known anyone to really use the default SQL generated by ADO.NET. There are three main problems with the auto-generated code:

1. The generic engine just can't make optimized code like someone who knows the system

2. The generic engine makes buggy code. For example, if you have a column named id in a SQL Server, it may build "SELECT id, ..." but that's invalid. You need brackets around the name like "SELECT [id], ..." because id is a reserved word. But not all databases use [].

3. The generic engine is slow - it has to run a query to analyze the DB before it can build the right SQL. That's usually an extra trip to the DB that could be saved if you coded the SQL yourself.

 

Why does it not recognize the Primary Key and use that in the WHERE? I don't know. Sounds like an oportunity to write your own SQL generator, if it's warranted :)

 

-ner