Link to home
Create AccountLog in
Avatar of HLRosenberger
HLRosenbergerFlag for United States of America

asked on

SQL, ADO.NET and database updates

IN classic VB, there was a collection of field names when you opened a table.  Does anything like this exist in ADO.NET?   I have a table with 40 fields in it,  5 of the fields are varchar(2000).   I'm concerned about creating such a large SQL INSERT statement.  Lots of fields to deal with, lots of data.

What are alternatives?
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

>>Does anything like this exist in ADO.NET?  <<
It depends how you "opened the table".  For example, if it is a DataTable then it has a Columns collection.

>>I'm concerned about creating such a large SQL INSERT statement. <<
So would I be.  Especially if it is SQL Serve 2000.  The maximum allowed is 8060.

>>What are alternatives? <<
Normalizing the data.  For the most part tables rarely need to have more than a dozen columns.  
Avatar of HLRosenberger

ASKER

So, if I use a Columns collection, and update it that way, I do not need to use an SQL INSERT.  
>>So, if I use a Columns collection, and update it that way, I do not need to use an SQL INSERT.   <<
I have no idea.  I would never consider doing that.  It sounds way to painful, let alone an enormous security hole.
>>What are alternatives? <<
But to get back to your question, the correct way to handle this (providing you are not using SQL Server 2000) is to pass the values into a Stored Procedure that handles the INSERT using the ADO.NET ExecuteNonQuery method.
This is not a WEB app, just a standalone desktop app used in house, so there is no security issue.

I'm using server 2008.  Stored Proc idea is an option.  Thanks
>>This is not a WEB app<<
I did not say it was and it does not have to be.  There is simply no reason users should have SELECT/INSERT/UPDATE/DELETE permissions on Production tables. Period.
Thanks.  Can you provide an example of how to pass the parms to a Stored proc from VB.NET code?  BY using this method, it eliminates the issue of having 40 fields in the table and 6 varchar(2000) fields?
ASKER CERTIFIED SOLUTION
Avatar of Anthony Perkins
Anthony Perkins
Flag of United States of America image

Link to home
membership
Create an account to see this answer
Signing up is free. No credit card required.
Create Account
Then I'm back to square onie.
The author asked a question, I answered it.  They then had a follow-up for which they apparently did not like the answer.