[OleDB] Computer Generated SQL statement or my own?

[Denaes]

Ok, I thought I was getting the hang of things. I think SQL is pretty simple when you use it to do pretty simple things.

 

Then VB.Net goes and throws me for a loop.

 

I used the Server Explorer to connect to my database and dragged the connection and data adapter onto my form, it generates the SQL itself. Now I don't use these components because I need my adaptors available pupblicly, not form level, but not having to type out 15 fieldnames in some cases.

 

This is my personal SQL statement to delete a row:

 

"DELETE FROM AppUser WHERE AppUserID = ?"

 

Then I configure the parameter for AppUserID.

 

This is what was automatically generated by the data adaptor:

 

"DELETE FROM Instructors WHERE (InstructorID = ?) " & _
           "AND (Address = ? OR ? IS NULL AND Address IS NULL) AND " & _
           "(AvailabilityNotes = ? OR ? IS NULL AND AvailabilityNotes IS NULL) " & _
           "AND (Available_Friday = ? OR ? IS NULL AND Available_Friday IS NULL) " & _
           "AND (Available_Monday = ? OR ? IS NULL AND Available_Monday IS NULL) " & _
           "AND (Available_Saturday = ? OR ? IS NULL AND Available_Saturday IS NULL) " & _
           "AND (Available_Sunday = ? OR ? IS NULL AND Available_Sunday IS NULL) AND " & _
           "(Available_Thursday = ? OR ? IS NULL AND Available_Thursday IS NULL) AND " & _
           "(Available_Tuesday = ? OR ? IS NULL AND Available_Tuesday IS NULL) AND " & _
           "(Available_Wedsday = ? OR ? IS NULL AND Available_Wedsday IS NULL) AND " & _
           "(City = ? OR ? IS NULL AND City IS NULL) AND (Contact01 = ? OR ? IS NULL " & _
           "AND Contact01 IS NULL) AND (Contact02 = ? OR ? IS NULL AND Contact02 IS NULL) " & _
           "AND (Contact03 = ? OR ? IS NULL AND Contact03 IS NULL) AND (FirstName = ? OR ? " & _
           "IS NULL AND FirstName IS NULL) AND (HourlyRate = ? OR ? IS NULL AND HourlyRate IS NULL) " & _
           "AND (LastName = ? OR ? IS NULL AND LastName IS NULL) AND (State = ? OR ? IS NULL " & _
           "AND State IS NULL) AND (Status = ? OR ? IS NULL AND Status IS NULL) AND (Title = ? " & _
           "OR ? IS NULL AND Title IS NULL) AND (Zip = ? OR ? IS NULL AND Zip IS NULL)"

 

followed by no less than 40 parameters. And this is for a table with like 15 fields.

 

Is there a reason for this much code to delete a row from the table? This is really scaring me.

 

I know updating and inserting have a bit of code, but deleting and selection? I've also noticed that VB.Net will select every single field of the table individually rather than just using a *.

 

Can anyone shed any light on this?

[PlausiblyDamp]

The autogenerated code is playing it safe - it will only delete the record if none of the fields have been changed on the DB since the data was loaded in the DataSet - if you right click on the DataAdapter there is an option to configure the DataAdapter - under there there is an opton for 'Optimistic locking' select that and see the difference.