about the code listed here
http://www.eggheadcafe.com/conversationwarning.aspx?messageid=29873947&groupid=1345
its work 100%, but the update effect all rows in the datagrid that i use.
please tell me how can i make the effect only on the changed row.
thanks
v-lli wrote:
Hi Parameswaran,Base on my understanding, you use a data adapter to fill a
04-May-07
Hi Parameswaran,
Base on my understanding, you use a data adapter to fill a data table. You
set the DataSource of a binding source to the data table and then bind the
binding source to a datagridview. After you update in the datagridview, you
use the data adapter to save the changes to the database. However, the
changes aren't saved to database. If I'm off base, please feel free to let
me know.
but the above is sucessfuly executed.
What's the kind of update in the datagridview you have performed, insert,
update or delete? If there're inserted rows in the data table, it's
required that the data adapter has a InsertCommand; if there're updated
rows, the data adapter is required to have a UpdateCommand; if there're
deleted rows, the data adapter is required to have a DeleteCommand.
I think a possible reason why no records updated in the database in your
application is that the changes in the datagridview haven't been committed
to the underlying data table, when you're trying to save the changes to the
database. In fact, when the data grid view loses the focus, the changed
data will be committed to the underlying data table. We could also call the
BindingSource.EndEdit method to apply the pending changes to the underlying
data source. I suggest that you call the BindingSource.EndEdit method
before you save the changes to the database.
I performed a test on this issue, and all works fine. Since Oracle is a 3rd
party product, I use SqlServer Express as the database in my test.
I create a table in the database, named 'Student'. The table has three
columns:
ID int Not Null (Primary Key),
Name varchar(50) Null,
Age int Null
The following is the code in my test. It requires that you add a
DataGridView and a Button on the form.
public partial class Form1 : Form
{
BindingSource bs = new BindingSource();
DataTable table = new DataTable();
SqlConnection conn = new SqlConnection();
SqlDataAdapter da = new SqlDataAdapter();
private void Form1_Load(object sender, EventArgs e)
{
bs.DataSource = table;
this.dataGridView1.DataSource = bs;
string connstr = "Data Source = .\\sqlexpress; integrated
security = true;Initial catalog = TestDataBase";
conn.ConnectionString = connstr;
string selectsql = "select * from student";
da.SelectCommand = new SqlCommand(selectsql,conn);
SqlCommand insercommand = new SqlCommand("insert into
student(id,name,age) values(@id,@name,@age)", conn);
insercommand.Parameters.Add("@id", SqlDbType.Int,4,"ID");
insercommand.Parameters.Add("@name", SqlDbType.VarChar, 50,
"Name");
insercommand.Parameters.Add("@age", SqlDbType.Int, 4, "Age");
da.InsertCommand = insercommand;
SqlCommand updatecommand = new SqlCommand("update student set
name=@name,age=@age where (id=@id)", conn);
updatecommand.Parameters.Add("@id", SqlDbType.Int,4,"ID");
updatecommand.Parameters.Add("@name",SqlDbType.VarChar,50,"Name");
updatecommand.Parameters.Add("@age",SqlDbType.Int,4,"Age");
da.UpdateCommand = updatecommand;
SqlCommand deletecommand = new SqlCommand("delete student where
id=@id", conn);
deletecommand.Parameters.Add("@id", SqlDbType.Int, 4, "ID");
da.DeleteCommand = deletecommand;
conn.Open();
da.Fill(table);
conn.Close();
}
// click the button to save the changes to the database
private void button1_Click(object sender, EventArgs e)
{
da.Update(table);
}
}
As your second question, you could add the columns which are to users to
the data table and specify the Expression property of the columns to make
them computed data columns, if necessay. For more information on expression
syntax, you may read the following MSDN document:
'DataColumn.Expression Property'
http://msdn2.microsoft.com/en-us/library/system.data.datacolumn.expression(V
S.71).aspx
As for the columns that are to the database, you may not bind them to the
columns in the data grid view. To do this, you could add columns in the
datagridview at design time and set the DataPropertyName property of the
data grid view columns to the columns in the data table that you'd like to
show in the data grid view.
Note that in this case, you should also set the AutoGenerateColumns
property of the datagridview to false in code before you set the
datagridview's DataSource property. It ensures that columns won't be
created automatically when the DataSource or DataMember properties are set.
Hope this helps.
If you have anything unclear, please feel free to let me know.
Sincerely,
Linda Liu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
Previous Posts In This Thread:
On Thursday, May 03, 2007 7:05 AM
Kanna wrote:
Datagridview vb.Net (Update Insert delete)
Hi
Operating System: Xp
Language :Vb.Net 2005
Connetion :Oledb
Provider :OraOLEDB.Oracle
Oracle Version :9i
1.
My datagridview has constant columns. I am trying to insert data in to
one of my oracle table as the user press a button. If the user press Update I
have to update the coressponding fields. like wise I have to delete the
seleted fields
I have written the following code for Inserting the datagrid view
Dim gstrOledbDataSource As String = "Provider=OraOLEDB.Oracle;Data
Source=PDTORCL;User Id=sales10g;Password=sales10g;"
Dim conn As New OleDb.OleDbConnection
Dim lOledtada As New OleDb.OleDbDataAdapter
Dim ltranOledb As OleDb.OleDbTransaction
Dim lstrsql As String
Dim ldtItemData As DataTable
Dim bindingdataSource As New BindingSource()
ldtItemData = New DataTable
Dim ds As New DataSet()
Dim command As OleDb.OleDbCommand
lstrsql = "select * from SLS_MSTPV_DODTYPE"
dgview.DataSource = bindingdataSource
lOledtada.SelectCommand = New OleDb.OleDbCommand(lstrsql, conn)
'lOledtada.Fill(ldtItemData)
command = New OleDb.OleDbCommand( _
"INSERT INTO SLS_MSTPV_DODTYPE (TYPE_CODE, TYPE_DESC) " & _
"VALUES (@TYPE_CODE, @TYPE_DESC)", conn)
command.Parameters.Add("@TYPE_CODE", OleDb.OleDbType.Char, 10,
"TYPE_CODE")
command.Parameters.Add("@TYPE_DESC", OleDb.OleDbType.Char, 15,
"TYPE_DESC")
lOledtada.InsertCommand = command
Dim table As New DataTable()
lOledtada.Fill(table)
bindingdataSource.DataSource = table
lOledtada.Update(table)
What is annoying me is that there is no records updated in the database but
the above is sucessfuly executed.
2.
Is It possible to map this column in the datagridview. Since some of the
columns in my datagridview are to the users(not to the database egAmount).
Some of the columns are to the database(eg: accountdetails). So i ll take the
necessary columns and send it to the databse
Please guide me on the above
Parameswaran.N
On Friday, May 04, 2007 12:32 AM
v-lli wrote:
Hi Parameswaran,Base on my understanding, you use a data adapter to fill a
Hi Parameswaran,
Base on my understanding, you use a data adapter to fill a data table. You
set the DataSource of a binding source to the data table and then bind the
binding source to a datagridview. After you update in the datagridview, you
use the data adapter to save the changes to the database. However, the
changes aren't saved to database. If I'm off base, please feel free to let
me know.
but the above is sucessfuly executed.
What's the kind of update in the datagridview you have performed, insert,
update or delete? If there're inserted rows in the data table, it's
required that the data adapter has a InsertCommand; if there're updated
rows, the data adapter is required to have a UpdateCommand; if there're
deleted rows, the data adapter is required to have a DeleteCommand.
I think a possible reason why no records updated in the database in your
application is that the changes in the datagridview haven't been committed
to the underlying data table, when you're trying to save the changes to the
database. In fact, when the data grid view loses the focus, the changed
data will be committed to the underlying data table. We could also call the
BindingSource.EndEdit method to apply the pending changes to the underlying
data source. I suggest that you call the BindingSource.EndEdit method
before you save the changes to the database.
I performed a test on this issue, and all works fine. Since Oracle is a 3rd
party product, I use SqlServer Express as the database in my test.
I create a table in the database, named 'Student'. The table has three
columns:
ID int Not Null (Primary Key),
Name varchar(50) Null,
Age int Null
The following is the code in my test. It requires that you add a
DataGridView and a Button on the form.
public partial class Form1 : Form
{
BindingSource bs = new BindingSource();
DataTable table = new DataTable();
SqlConnection conn = new SqlConnection();
SqlDataAdapter da = new SqlDataAdapter();
private void Form1_Load(object sender, EventArgs e)
{
bs.DataSource = table;
this.dataGridView1.DataSource = bs;
string connstr = "Data Source = .\\sqlexpress; integrated
security = true;Initial catalog = TestDataBase";
conn.ConnectionString = connstr;
string selectsql = "select * from student";
da.SelectCommand = new SqlCommand(selectsql,conn);
SqlCommand insercommand = new SqlCommand("insert into
student(id,name,age) values(@id,@name,@age)", conn);
insercommand.Parameters.Add("@id", SqlDbType.Int,4,"ID");
insercommand.Parameters.Add("@name", SqlDbType.VarChar, 50,
"Name");
insercommand.Parameters.Add("@age", SqlDbType.Int, 4, "Age");
da.InsertCommand = insercommand;
SqlCommand updatecommand = new SqlCommand("update student set
name=@name,age=@age where (id=@id)", conn);
updatecommand.Parameters.Add("@id", SqlDbType.Int,4,"ID");
updatecommand.Parameters.Add("@name",SqlDbType.VarChar,50,"Name");
updatecommand.Parameters.Add("@age",SqlDbType.Int,4,"Age");
da.UpdateCommand = updatecommand;
SqlCommand deletecommand = new SqlCommand("delete student where
id=@id", conn);
deletecommand.Parameters.Add("@id", SqlDbType.Int, 4, "ID");
da.DeleteCommand = deletecommand;
conn.Open();
da.Fill(table);
conn.Close();
}
// click the button to save the changes to the database
private void button1_Click(object sender, EventArgs e)
{
da.Update(table);
}
}
As your second question, you could add the columns which are to users to
the data table and specify the Expression property of the columns to make
them computed data columns, if necessay. For more information on expression
syntax, you may read the following MSDN document:
'DataColumn.Expression Property'
http://msdn2.microsoft.com/en-us/library/system.data.datacolumn.expression(V
S.71).aspx
As for the columns that are to the database, you may not bind them to the
columns in the data grid view. To do this, you could add columns in the
datagridview at design time and set the DataPropertyName property of the
data grid view columns to the columns in the data table that you'd like to
show in the data grid view.
Note that in this case, you should also set the AutoGenerateColumns
property of the datagridview to false in code before you set the
datagridview's DataSource property. It ensures that columns won't be
created automatically when the DataSource or DataMember properties are set.
Hope this helps.
If you have anything unclear, please feel free to let me know.
Sincerely,
Linda Liu
Microsoft Online Community Support
==================================================
Get notification to my posts through email? Please refer to
http://msdn.microsoft.com/subscriptions/managednewsgroups/default.aspx#notif
ications.
Note: The MSDN Managed Newsgroup support offering is for non-urgent issues
where an initial response from the community or a Microsoft Support
Engineer within 1 business day is acceptable. Please note that each follow
up response may take approximately 2 business days as the support
professional working with you may need further investigation to reach the
most efficient resolution. The offering is not appropriate for situations
that require urgent, real-time or phone-based interactions or complex
project analysis and dump analysis issues. Issues of this nature are best
handled working with a dedicated Microsoft Support Engineer by contacting
Microsoft Customer Support Services (CSS) at
http://msdn.microsoft.com/subscriptions/support/default.aspx.
==================================================
This posting is provided "AS IS" with no warranties, and confers no rights.
On Saturday, May 05, 2007 1:29 AM
Kanna wrote:
Hi, Thanks for your reply. Its working fineParameswaran.
Hi,
Thanks for your reply. Its working fine
Parameswaran.N
"Linda Liu [MSFT]" wrote:
On Thursday, June 25, 2009 11:24 AM
alem haile wrote:
how to update delete save and add in vb net 2005
You are required to be a member to post replies. After logging in or becoming a member, you will be redirected back to this page.
Submitted via EggHeadCafe - Software Developer Portal of Choice
Xcompress - IIS HTTP Compression
http://www.eggheadcafe.com/tutorials/aspnet/c5a74a2e-01b5-4b6c-95c7-861a0909f404/xcompress--iis-http-comp.aspx