Best way to add a row to a SQL Server table?
What is the best way to add a row to a SQL Server table? Assume that stored procedures aren't an option.
There are 3 methods I can think of using ADO .Net:
1. Execute an Insert statement string
e.g. "Insert into Books (Field1, Field2) Values ('" & myval.replace("'","''") & "', '" & myval2.replace("'","''") & "')"
As long as you double any single quotes in the values, this method seems fine.
2. Paramterized Insert statement
You don't need to worry about single quotes using this method, but seems like it would take longer to write.
3. DataSet and SqlDataAdapter.Update
Dim objConnection As New SqlConnection("Connection String")
Dim objAdapter As New SqlDataAdapter
objAdapter.SelectCommand = New SqlCommand("select * from Books", objConnection)
Dim objBuilder As New SqlCommandBuilder(objAdapter)
objConnection.Open()
Dim objDataSet As New DataSet
objAdapter.Fill(objDataSet, "Books")
objDataSet.Tables(0).Rows.Add...
objAdapter.Update(objDataSet, "Books")
objConnection.Close()This method seems fairly quick to write and you don't need to worry about single quotes. Seems like a good method but I haven't used it much.
Which way is best?
|
0 |
|
MikeOtown 
asp.net.object-datasource
16182 articles. 
0 followers.
7 Replies
629 Views
In my opinion the absolute best and safest way to do anything regarding your db interacting with a web app / site is to use stored procedures and pass in the parameters.
If everything happens for a reason what is the reason for this error?
|
|
0 |
|
mpaterson Mike,
Notwithstanding the Store Procedure argument...
Between options 1 and 2 there's been a bunch of talk avoiding option 1 for the reason you mentioned and in particular, SQL injection.
So yeah, Option 2 is the way to go.
|
|
0 |
|
bill From what I can tell, simply doubling any single quotes in strings that come from users eliminates any threats of SQL injection. Can anyone tell me a scenario where a SQL injection attack could happen if all single quotes are replaced with 2 single quotes?
|
|
0 |
|
MikeOtown When you go with option 2, values passed as parms are treated as data rather than as SQL so, the SQL along with the parm isn't expanded into a full SQL string. So even if you don't perform ANY validation on the parm, you are pretty much guaranteed NOT to have an issue.
When you go with Option 1, you really need to make sure that your validation is tight but even then, something could go wrong... Not really an expert on it but to say that you generally have to outsmart every hacker trying to crack your SQL and that's difficult.
You raise a good point about moving from a single to a pair of single quotes and the only thing that comes to mind from the devils perspective is that you'd need to really know the routine used to fix the quotes... For example, what if replacing a single with a pair of singles makes the input string 1 or more characters greater than maximum field size your db is expecting? The hacker plants the seed and makes the last character a single quote and when you replace it with two, when it hits the database it is cropped back to just one?
One thing to consider is that although you might be able to write an air-tight routine to fix the quotes but someone less experienced might not be so gifted... what if the string has so many single quotes that, if for example someone is using a BYTE to loop though the string, it might exceed 255 and jack the code?
Yeah I streching here but hoping to point out that it can be tough to not only out-think the hackers but also to hope that everyone will out-think hackers in the same way.
|
|
0 |
|
bill Did this help?
|
|
0 |
|
bill Ya, no one addressed Option 3.
|
|
0 |
|
MikeOtown Hmmm, didn't see that you were asking for Option 3 to be addressed but instead questioned what peeps consider to be the best option of the 3
I suppose O3 is similar to O2 in that your updates to the data are treated as 'data' rather than to be parsed out into SQL as it is in O1.
So I suppose O2 and O3 are similar in approach and which to chose would be dependant on the situation.Further, I think of O3 strategy as being more applicable towards updating a larger set of records kinda like a mass update scenario. However, I generally don't find myself using DataAdapters to update data, perhaps because, I'm old-school or probably better put, haven't found a situation where it's applicable. The approach generally loads a bunch of records into memory, perhaps even a number of tables - seems expensive. And when you attempt to update the database, there's a process going on behind the scenes to iterate through each of the rows in each of the tables to assess which records are 'dirty' and therefore which source records to update on back-end. Just seems like a lot of effort but, based on your needs, it might be the way to go.
|
|
0 |
|
bill 
Similar Artilces: Everytime I click on a button I want to add a tableRow to a table like this: TextBox TB = new TextBox(); TableCell Cell = new TableCell(); Cell.Controls.Add(TB); TableRow Row = new TableRow(); Row.Cells.Add(Cell); table.Rows.Add(Row);It works the first time, but when I click the button again the last r...
how can i add row to table of sql with vb.net code?
how can i add row to table of sql with vb.net code? You can use System.Data.SqlClient classes : Using con As New SqlConnection(" your connection string goes here ") Dim com As New SqlCommand("Insert into Person (id,name) values(@id,@name) ", con) com.Parameters.AddWithValue("@id", 1) com.Parameters.AddWithValue("@name", "Nomi1") com.Connection.Open...
what is the best way of import from xml file to a sql server tables?
how do i import or what is the best way of import from xml file to a sql server tables?how do i export or what is the best way of export from sql server tables to xml file?thanksIts all about coding! There is a data base type 'xml'.You could create a table with column of type 'xml' read the files as string , as store them as strings. When reading, read the 'xml' field into a string, and build a file then from the string. Cheers,Yani Yani Dzhurov: There is a data base type 'xml'. You could create a table with column of type 'xml' read the files as string , as sto...
Whats the best way to add a new row to multiple tables in a database?
hi!im doing a registration form and at the end when i have all the information i need to put it in a database. I need to add new rows in alot of tables in the database. Whats the best way to do this? i want to do it effectively and minimise any repeating code.This is how i normally add a new row in a table in the database.I create an sqladaptor for the table i want to update Retrieve the content of that table to a dataset. Create a new datarow. Add it to the table in the datasetUsing the sqladaptor i run the update method with the dataset.Do i just repeat those ...
Object reference not set to an instance of an object
Hi! I keep getting object reference not set to an instance of an object when using the Add New Item-wizard to add an empty SQL Server 2005 database. Why does this happend? How can i get it to work? I am running Visual Studio 2005 beta 2....
Best way to implement "add new row" button to a table of data?
I have a table (3 rows, 5 columns) that has different controls in each column. All of the controls will pick up their data (lists and initial values) from a database (right now only 3 of them do). So the user selects an option from a list (stored in a db) for the 1st column, then enters a number in a text box for the 2nd column, again selects from a list in the 3rd column, again selects from a list in the 4th column and enters a number in a text box for the 5th column. I want to have a button called "new row". If the user clicks this, a new row should be added to the table with the...
How to add new row to datagrid like in the SQL server when open table and enter data
hello mate!, could you please tell me how can i achieve a datagrid that adds a new row when ever we click on the first of of the last row.(like in the sql server management studio 2005 in which when we right click a particular table and click open table and enter data beyond the page size of the datagrid) Thanks,Much Obliged & Thanks a Lot,Myself Try: http://geekswithblogs.net/casualjim/articles/51360.aspxCheers,ChrisRemember, you can mark more than 1 post as the answer....
Best way of paging thousands of rows w/ ADO.NET 2.0 & SQL 2005
Hello, I have a requirement of paging thousands of rows in ASP.NET 2.0 in the most efficient + fastest way possible. I do not want to load all rows in memory, cache them and perform paging at the application layer as this is not fast enough for me and not as scalable as it needs to be. From the database, I only want to get the data needed for the current page and nothing else. Is there a recommended way of efficiently paging data with ADO.NET 2.0 Beta 2 and SQL 2005? I know there was paging built-in to the SqlCommand object in previous beta releases but it's been removed due to p...
How can i import records from a sql server table in project1 to another sql server table in project2
Hi, i have a table with all employee bio-data in a completed project. Iam now working on another project with a table that needs the same data and here iam talking about 300 records that rarely change. Instead of re-entering this data in this new table, i want to import the data from the completed project into a table in this new project. Does any one have any idea how to achieve that or is there a better option to do the same. One more thing iam realising here is that iam going to use this same data in very many applications and some one from one department is going to ...
Best way to connect web server to sql server on different machines
HiCan I ask what is the best way to connect a web server running IIS 5.0 to a sql server 2000 database running on different machinesIs the preferred connection type windows authentication from IIS to sql server?I think the options are:1. Use a common domain windows account to connect the 22. Use sql server authentication instead of windows authentication3. I have read about mirroring local accounts on both machines but have not managed to track down concrete examples of how to do it?Any advice is much appreciatedThanks Actually, I've discussed it with the system administrator in the c...
Best way to get MS SQL Server database into SQL Anywhere?
What is the easiest way to get a MS SQL Server database into SQL Anywhere? The only way I've been able to do it is to export into a text or dbase file and import, table by table, but my database has around 200 tables and this isn't a workable strategy! Replies by email would be much appreciated. Sarah Howarth Platinum technology, inc. howarth@platinum.com Sarah, There is two ways off the top of my head. The first is to use the scripting options for MS SQL Server and see if that works, that may take some direct manipulation of the Syntax though. The second is to u...
SQL Server Reporting Services for SQL SERVER 2000 AND Visual Studio.NET 2005 .NET 2.0
Hi, Which version of SQl Server Reporting Services will work with SQL SERVER 2000 in pararrel with ASP.NET 2.0 (.NET 2.0 framework)? Thanks, Azam HighOnCodingWanna get high! Hi, Okay I got the answer. Yes, we can use the SQL SERVER 2005 Reporting Services with SQL SERVER 2000. HighOnCodingWanna get high! ...
I imported a SQL Table into SQL DataBase, But I can not update this table even with SQL Server management Studio
I imported a SQL Table into SQL DataBase, But I can not update this table even with SQL Server management Studio When I change any data on mentioned table above, Red exclamation sign appears left of the record . How can I correct this problem? Thanks. Try running the UPDATE via Query Analyzer...and see if you get an error.***********************Dinakar NethiLife is short. Enjoy it.***********************...
Why I cannot create/edit tables in VS.Net Server explorer for SQL Server 2000 Developer local database?
I found that when I install MSDE, then I can create/edit database objects for MSDE database in Visual Studio 2003 Server Explorer, but when I try to create/edit database objects under the default database created by the SQL Server 2000 installation, I cannot do these tasks as no option for these tasks appear when I right-click on the database object in Server Explorer. Anyone knows why this is happening?sun21170 I reproed your issue. And I found in MSDN it is said the Server Explorer can: -Open and view Server Explorer -Add a data connection -Add a server -Drag a...
Web resources about - Best way to add a row to a SQL Server table? - asp.net.object-datasource
Server farm - Wikipedia, the free encyclopediaA server farm or server cluster is a collection of computer servers usually maintained by an enterprise to accomplish server needs far beyond ...
Price Drop: MyAudioStream Pro UPnP audio player and streamer: gather your music collection from your ...MyAudioStream Pro UPnP audio player and streamer: gather your music collection from your PC, NAS, UPnP servers, Windows Media Player or iTunes ...
... stock to Market Outperform from Market Perform, assigning a $45 price target, righting that the company will strengthen its hold on its server ...
Server shipments, vendor revenue go up in Q3 2015In the third quarter of 2015, worldwide server shipments grew 9.2 percent from the third quarter of 2014, while vendor revenue increased 7.5 ...
An exploit and other "serious issues" have been detected.
Latest Windows Server 2016 beta shows off Microsoft's new containersMicrosoft launched the fourth technical preview for the next version of Windows Server on Thursday, giving the public access to its new Hyper-V ...
Senator Wonders If Hillary Is Covering Legal Expenses For Tech Firm That Managed Her Email Server
With Intel expected to launch its next-generation server platform Purley in the second half of 2017, vendors such as Hewlett-Packard (HP), Dell ...
Server Files Sexual Harassment Complaint Against Pigalle Restaurant... restaurant Pigalle is being accused of sexual harassment by a waitress , who says that management implied her jeans were "too much." The server ...
Wondering how Hyper-V containers work in Microsoft's upcoming server software release? Intrepid administrators and developers can get an early ...