MySQL Style INSERT INTO SET

MySQL allowed INSERT statements that looked like this:

INSERT INTO Table SET col1 = value1, col2 = value2, col3 = value3

Basically this closely matched the format of the UPDATE statements.

Here is why this was good: I could do something like this:

if (RecordExists ==false) { SQLquery ="INSERT INTO Table SET ";}else { SQLquery ="UPDATE Table SET ";}SQLquery +="col1 = value1, ";SQLquery +="col2 = value2, ";SQLquery +="col3 = value3, ";SQLquery +="col4 = value4 ";if (RecordExists ==true) { SQLquery +="WHERE id = " + ourID;}

If I had a good 50 columns then it means I can reuse a sizable chunk of code for both INSERT and UPDATE statements (since both use the same general format). Whereas if I have to use "INSERT INTO Table(columns) VALUES (values)" then I'm look at duplicating a lot of code.

Does MS-SQL support something similar to "INSERT INTO SET"? How are others dealing with this?

Thanks,

Gabe
-------

No. That is a proprietary MySQL syntax.

How do I deal with it? I don't. I don't use SQL concatenation techniques. They make the application/web apps insecure, difficult to port, slow, hard to maintain, and locks the application developer into doing database design (Which isn't good for team development). Not only that but it limits what you can do from the database design, and in multi-application environments where you have many applications using the same data tables, difficult to coordinate since the data-logic has now been moved to each application.

And lastly, for some applications it's simply isn't even an option. For example in many HIPAA applications, since you are allowing applications to retrieve data without the ability to forcably log the request, it's not even an option to do it that way.

|||

Motley, you answered my question and for that I thank you.

The code I was showing was simplified for forum purposes. (I just wanted to demonstrate the overall logic.) In a real situation I would make use of the AddParameter method. But this probably still qualifies as using SQL concactenation and in-line SQL, which I know many consider a sin.

In my experience application developers are almost always are up to their eye-balls in database design anyway...even when working with a DBA. I know I always am. Many database search queries I deal with are tied closely to application conditions and have a lot of processing logic involved in them. I could do that processing logic in C# (which is extremely feature rich, has great development tools available & benefits from access to my application objects) or I could do it in a SQL stored procedure which seems generally clunky & not nearly as robust. Using lots of stored procedures normally just means I have one more place I have to update something or one more person I have to involve for a fix.

I'm not going to pretend to be an expert on this and some of this just comes down to getting the job done. Many of the security advantages you speak about could probably be mimiced by simply having well defined user roles. From what I've read the performance advantages aren't as overwhelming as people seem to believe. I have read that many of the performance enhancements MS-SQL makes to queries are extended to in-line queries as well.

From an application standpoint having a well developed set of database interaction objects becomes a dream. I know a ton of people are going to disagree with me and I may even change my mind on this subject someday. For me personally, most of the stuff you speak of has just proven to be a giant PITA.

Gabe
============

|||What is a PITA?|||And why SQL concatenation techniques make the application/web apps insecure??|||

And why SQL concatenation techniques make the application/web apps insecure??

I would like to use the same style.

|||

Hey

SQL concatenation techniques will result ini sql injection.

The primary form of SQL injection consists of direct insertion of code into user-input variables that are concatenated with SQL commands and executed. A less direct attack injects malicious code into strings that are destined for storage in a table or as metadata. When the stored strings are subsequently concatenated into a dynamic SQL command, the malicious code is executed.

You could take a look atSQL Injection for details.

My INSERT statements works... 8/10 times

I'm using the following code to add some data to a table:
Dim rand As Random = New Random
Dim num As Int32 = rand.Next(10000000)
Dim strConn as string = "......"
Dim sql as string = "INSERT INTO tblitemid (itemid, userid, datetime, supplier, comment, commenttype, uniqueid) VALUES ('" & label1.Text & "', '" & user.identity.name & "', '"& System.DateTime.Now & "','3763' ,'" & textbox1.text & "' , 'C' ,'" & num & "')"
Dim conn as New SQLConnection(strConn)
Dim Cmd as New SQLCommand(sql, conn)
Try
conn.Open()
Catch SQLExp as SQLException
Response.Write ("An SQL Server Error Occurred: " & e.toString())
Finally
cmd.ExecuteNonQuery
conn.Close()
End Try

As far as I can tell the code works fine. But for some odd reason I click the button, the code execute and the page closes as it should, but the data is never inserted into the database. I cant really seem to pick up on any paterns for why this would be happening. As a rough guess I'd say it doesnt insert the data 1 out of every 5 times or so it seems. Anyone every have any experience with this? Any comments would be helpful, cuz I'm at a loss.
If youd like to see more code let me know...
Thanks,
Scottscott, cmd.ExecuteNonQuery() should be in the try, not finally. because if you're getting an sql error, it'll error again when the cmd tries to execute w/o an open connection.

that's one. try stepping through it. Furthermore, don't use close, use conn.Dispose(), and cmd.Dispose(), and equal them to null.

Also, are you sure it's going through that part of the code?|||Are you sure all the vars have the correct data in them? I've not used the .net Rand function yet, should you be seeding it or you may get repeated numbers there too easily?|||I ended up getting it working, thanks guys. Turns out it was some javascript frather down in the code that was f'ing it all up.