Using the SqlCommand object, records can be inserted/updated/deleted in the underlying data source (i.e. the SQL Server database table) directly by using some code like this:
Dim strSQL As String
Dim sqlCmd As SqlCommand
Dim sqlConn As SqlConnection
strSQL = "INSERT INTO MyTable (Col1, Col2) VALUES........."
sqlConn = New SqlConnection("........")
sqlCmd = New SqlCommand(strSQL, sqlConn)
But if the SqlDataAdapter object is used to insert/update/delete records in the data source, is it
ALWAYS necessary to first make the necessary changes in the
DataSet for the data source to reflect the changes?
Thanks
RON
*********
If you have an apple & I have an apple & we exchange our apples, then each of us will still have only 1 apple but if you have an idea & I have an idea & we exchange our ideas, then each of us will have 2 ideas
Im confused.. what exactly do you mean? Well for me i only use
SqlDataAdapter to Fetch or Retrieved data from database and fill in my DataSet/DataTable through adapter..
Let me try to clarify it. The code I have shown in my first post (which doesn't use
SqlDataAdapter) inserts a row in the data source....correct? The same row can also be inserted using
SqlDataAdapter like this:
Dim dSet As DataSet
Dim dRow As DataRow
Dim dTable As DataTable
Dim sqlCmd As SqlCommand
Dim sqlConn As SqlConnection
Dim sqlDapter As SqlDataAdapter
sqlConn = New SqlConnection("..........")
sqlDapter = New SqlDataAdapter
sqlCmd = New SqlCommand("SELECT * FROM MyTable", sqlConn)
sqlDapter.SelectCommand = sqlCmd
dSet = New DataSet
sqlDapter.Fill(dSet, "MyTable")
dTable = New DataTable
dTable = dSet.Tables("MyTable")
When I run the above script, a new row gets added to the data source, correct? But if the blue lines in the above code are commented, then the
INSERT SQL command doesn't add a new row in the data source.
So is it ALWAYS necessary to first make the changes in the
DataTable for the data source to reflect the change when SqlDataAdapter is used?
Thanks
RON
*********
If you have an apple & I have an apple & we exchange our apples, then each of us will still have only 1 apple but if you have an idea & I have an idea & we exchange our ideas, then each of us will have 2 ideas
Not necessarily.. what i mean is that you can INSERT data to your database without using sqlDataAdapter like
using System.Text;
using System.Data;
using System.Data.SqlClient;
private void InsertData()
{
using (SqlConnection conn = new SqlConnection("YOUR CONNECTION STRING");
{
StringBuilder sb = new StringBuilder(string.Empty);
sb.AppendFormat("'{0}','{1}','{2}'",TextBox1.Text, TextBox2.Text); // But I would Suggest to use SP for security purposes
sql = string.Format("INSERT INTO Table1 (FirstName,FamilyName) VALUES ({0});", sb.ToString());
// or you can use this line below or you may use your Parameterized queries
Vinz, I don't think you have understood my question yet. I know that data can be inserted
WITHOUT using SqlDataAdapter (& that's what I have shown in the code in my first post) but
IFSqlDataAdapter is used (as shown in the code in my follow-up), is it necessary to update the
DataSet/DataTable first & then update the data source?
As I said in my previous post, if the blue lines in the code in that post are commented, then the
INSERT SQL statement doesn't insert a new row in the data source. This is the reason why I am asking whether it is mandatory to update the
DataTable first before updating the data source?
Thanks
RON
*********
If you have an apple & I have an apple & we exchange our apples, then each of us will still have only 1 apple but if you have an idea & I have an idea & we exchange our ideas, then each of us will have 2 ideas
My Apology.. I got it now... Well im not really sure about that because im not doing that before.. As what i have observed in your code you are just Updating the values from your Database and not in the DataTable so thats why its mandatory to have that line
you have commented out.. So if you would try to omit that line the changes doesn't reflect in your datasource as what you have noticed.. If you want to update your DataSource without that line you have commnted then try to fill again your DataSet with the
Updated values from the DataBase...
It's OK...happens sometimes...after all, to err is human...
So if you would try to omit that line the changes doesn't reflect in your datasource as what you have noticed.
Yes....that's right. The DataTableHAS to be updated first for the data source to reflect the changes when using the
SqlDataAdapter object.
Thanks
RON
*********
If you have an apple & I have an apple & we exchange our apples, then each of us will still have only 1 apple but if you have an idea & I have an idea & we exchange our ideas, then each of us will have 2 ideas
Since this post comes up among the first ones on google when looking for "sqldataadapter/insert/data", I wanted to reply to it. If the question is: Why is it needed to specify the values twice (when the new DataRow is created and also when the parameters
are defined):
Then the answer is "No, you don't need to indicate this twice". Specifying the values twice is not only a minor annoyance. It might also mean a high risk for a Data Integrity bug like in this example where the data is set directly. But the "duplicated" one
is not when you add the DataRow. That is a standard way to insert a new row of data when using a Data Adapter and it is easier this way when adding multiple rows. The duplicated code that should be removed is when you define the parameter. The parameter value
does not need to be specified here. To avoid this the SourceColumn property needs to be specified. It is the way to indicate that the data will not be provided when the parameters are defined but somewhere else (when creating the new DataRow). Example with
VB2010:
RN5A
Participant
961 Points
406 Posts
Using SqlDataAdapter To Insert/Update/Delete Rows
Mar 17, 2008 12:54 AM|LINK
Dim strSQL As String
Dim sqlCmd As SqlCommand
Dim sqlConn As SqlConnection
strSQL = "INSERT INTO MyTable (Col1, Col2) VALUES........."
sqlConn = New SqlConnection("........")
sqlCmd = New SqlCommand(strSQL, sqlConn)
sqlConn.Open()
sqlCmd.ExecuteNonQuery()
sqlConn.Close()
But if the SqlDataAdapter object is used to insert/update/delete records in the data source, is it ALWAYS necessary to first make the necessary changes in the DataSet for the data source to reflect the changes?
RON
*********
If you have an apple & I have an apple & we exchange our apples, then each of us will still have only 1 apple but if you have an idea & I have an idea & we exchange our ideas, then each of us will have 2 ideas
vinz
All-Star
127087 Points
17946 Posts
MVP
Re: Using SqlDataAdapter To Insert/Update/Delete Rows
Mar 17, 2008 01:16 AM|LINK
Im confused.. what exactly do you mean? Well for me i only use SqlDataAdapter to Fetch or Retrieved data from database and fill in my DataSet/DataTable through adapter..
MessageBox Controls for WebForms | Blog | Twitter | Linkedin
RN5A
Participant
961 Points
406 Posts
Re: Using SqlDataAdapter To Insert/Update/Delete Rows
Mar 17, 2008 06:56 AM|LINK
Dim dSet As DataSet
Dim dRow As DataRow
Dim dTable As DataTable
Dim sqlCmd As SqlCommand
Dim sqlConn As SqlConnection
Dim sqlDapter As SqlDataAdapter
sqlConn = New SqlConnection("..........")
sqlDapter = New SqlDataAdapter
sqlCmd = New SqlCommand("SELECT * FROM MyTable", sqlConn)
sqlDapter.SelectCommand = sqlCmd
dSet = New DataSet
sqlDapter.Fill(dSet, "MyTable")
dTable = New DataTable
dTable = dSet.Tables("MyTable")
dRow = dTable.NewRow
dRow(0) = 1
dRow(1) = "peter"
dTable.Rows.Add(dRow)
sqlCmd = New SqlCommand("INSERT INTO MyTable (Col1, Col2) VALUES (@Val1, @Val2)", sqlConn)
With sqlCmd
.Parameters.Add("@Val1", SqlDbType.Int).Value = 1
.Parameters.Add("@Val2", SqlDbType.VarChar, 50).Value = "peter"
End With
sqlDapter.InsertCommand = sqlCmd
sqlDapter.Update(dSet.Tables("MyTable"))
When I run the above script, a new row gets added to the data source, correct? But if the blue lines in the above code are commented, then the INSERT SQL command doesn't add a new row in the data source.
So is it ALWAYS necessary to first make the changes in the DataTable for the data source to reflect the change when SqlDataAdapter is used?
RON
*********
If you have an apple & I have an apple & we exchange our apples, then each of us will still have only 1 apple but if you have an idea & I have an idea & we exchange our ideas, then each of us will have 2 ideas
vinz
All-Star
127087 Points
17946 Posts
MVP
Re: Using SqlDataAdapter To Insert/Update/Delete Rows
Mar 17, 2008 07:56 AM|LINK
Not necessarily.. what i mean is that you can INSERT data to your database without using sqlDataAdapter like
using System.Text;
using System.Data;
using System.Data.SqlClient;
private void InsertData()
{
using (SqlConnection conn = new SqlConnection("YOUR CONNECTION STRING");
{
StringBuilder sb = new StringBuilder(string.Empty);
sb.AppendFormat("'{0}','{1}','{2}'",TextBox1.Text, TextBox2.Text); // But I would Suggest to use SP for security purposes
sql = string.Format("INSERT INTO Table1 (FirstName,FamilyName) VALUES ({0});", sb.ToString());
// or you can use this line below or you may use your Parameterized queries
// sql = "INSERT INTO Table1 (Col1,Col2) VALUES ('" + TextBox1.Text + "', '" + TextBox2.Text + "')";
conn.Open();
SqlCommand cmd = new SqlCommand(sql, conn);
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
conn.Close();
}
}
protected void Button1_Click(object sender, EventArgs e)
{
InsertData();
}
MessageBox Controls for WebForms | Blog | Twitter | Linkedin
RN5A
Participant
961 Points
406 Posts
Re: Using SqlDataAdapter To Insert/Update/Delete Rows
Mar 18, 2008 02:12 PM|LINK
As I said in my previous post, if the blue lines in the code in that post are commented, then the INSERT SQL statement doesn't insert a new row in the data source. This is the reason why I am asking whether it is mandatory to update the DataTable first before updating the data source?
RON
*********
If you have an apple & I have an apple & we exchange our apples, then each of us will still have only 1 apple but if you have an idea & I have an idea & we exchange our ideas, then each of us will have 2 ideas
vinz
All-Star
127087 Points
17946 Posts
MVP
Re: Using SqlDataAdapter To Insert/Update/Delete Rows
Mar 19, 2008 12:26 AM|LINK
My Apology.. I got it now... Well im not really sure about that because im not doing that before.. As what i have observed in your code you are just Updating the values from your Database and not in the DataTable so thats why its mandatory to have that line you have commented out.. So if you would try to omit that line the changes doesn't reflect in your datasource as what you have noticed.. If you want to update your DataSource without that line you have commnted then try to fill again your DataSet with the Updated values from the DataBase...
MessageBox Controls for WebForms | Blog | Twitter | Linkedin
RN5A
Participant
961 Points
406 Posts
Re: Using SqlDataAdapter To Insert/Update/Delete Rows
Mar 19, 2008 02:27 AM|LINK
RON
*********
If you have an apple & I have an apple & we exchange our apples, then each of us will still have only 1 apple but if you have an idea & I have an idea & we exchange our ideas, then each of us will have 2 ideas
vinz
All-Star
127087 Points
17946 Posts
MVP
Re: Using SqlDataAdapter To Insert/Update/Delete Rows
Mar 19, 2008 02:35 AM|LINK
Yes Exactly.. :)
MessageBox Controls for WebForms | Blog | Twitter | Linkedin
Jelgab
Member
8 Points
4 Posts
Re: Using SqlDataAdapter To Insert/Update/Delete Rows
Jul 18, 2011 09:26 PM|LINK
Since this post comes up among the first ones on google when looking for "sqldataadapter/insert/data", I wanted to reply to it. If the question is: Why is it needed to specify the values twice (when the new DataRow is created and also when the parameters are defined):
First, while adding a new DataRow:
dRow(1) = "peter" ...
Second while defining parameters:
.Parameters.Add("@Val2", SqlDbType.VarChar, 50).Value = "peter"
Then the answer is "No, you don't need to indicate this twice". Specifying the values twice is not only a minor annoyance. It might also mean a high risk for a Data Integrity bug like in this example where the data is set directly. But the "duplicated" one is not when you add the DataRow. That is a standard way to insert a new row of data when using a Data Adapter and it is easier this way when adding multiple rows. The duplicated code that should be removed is when you define the parameter. The parameter value does not need to be specified here. To avoid this the SourceColumn property needs to be specified. It is the way to indicate that the data will not be provided when the parameters are defined but somewhere else (when creating the new DataRow). Example with VB2010:
.Parameters.Add(New SqlParameter With {.ParameterName = "@Val2", .SqlDbType = SqlDbType.VarChar, .Size = 50, .SourceColumn = "FirstName" } )