Problem with Datagrid updating using VB (ASP)

[junglist]

Hi guys,
I've been trying to implement an editable datagrid and i have been
succesful up to the point where i can update my datagrid row by row.
However what used to happen was that once i updated one row, all of
them were updated so i immediatelly figured out that i have to include
the id of every entry in the update statement. This is where the
problem is raised. My database is an Access database. The table i am
updating contains a Date field which is filled in by the Now()
function Access provides, which inserts the current date and time. I
believe that is what creates the problem. I don't know how to update
the the Date field in my UPDATE statement. Can you please help? I've
included my sub.

Thank you in advance




Public Sub grid_Update(sender As Object, e As
DataGridCommandEventArgs)


Dim strUnits As String = CType(e.Item.FindControl( "txtUnits"),
TextBox).Text
Dim strid As String = CType(e.Item.FindControl( "ID"), Label).Text

' Dim strid As Integer =
Convert.ToInteger(e.Item.Cells[1].Controls[0].Text)
' Dim strid as String = "36"


' test.text = strid


DBConnection = New OleDbConnection(strconnect)
DBcommand=New OleDbCommand
DBcommand.commandtext="Update holding set units='" + strUnits +"'
where holdid = '" + strid + "' ;"

Dbcommand.commandtype=commandtype.Text

Dbcommand.connection=dbconnection
DBConnection.Open()
DBCommand.ExecuteNonQuery()
DBConnection.Close()

grid.EditItemIndex = -1


BindData()


End Sub

 

[Scott M.]

When you are populating your DataSet in the first place, are you pulling
this date field down into the DataSet? If so, in your update command, you
need a WHERE clause that checks the date field value from the record being
updated in the DataSet to the date field of the record in the original data
source. You never need to know the actual value.

 

[junglist]

I am not sure i understand what you are telling me. I am not pulling
down my date field value in the datagrid. Whenever i add an entry to
the database the date field is filled in by Access as i've set it to
use the now() function access provides in orderto fill it in with the
time and date of the update. Whenever i am updating, since i only
update certain values from the table, the date filed is not updated to
correspond with the update time. I believe that is why i get a data
mismatch error whenever i try to update the table with my current
update statement. I am waiting for your opinion.

Thank you

When you are populating your DataSet in the first place, are you pulling
this date field down into the DataSet? If so, in your update command, you
need a WHERE clause that checks the date field value from the record being
updated in the DataSet to the date field of the record in the original data
source. You never need to know the actual value.

Hi guys,
I've been trying to implement an editable datagrid and i have been
succesful up to the point where i can update my datagrid row by row.
However what used to happen was that once i updated one row, all of
them were updated so i immediatelly figured out that i have to include
the id of every entry in the update statement. This is where the
problem is raised. My database is an Access database. The table i am
updating contains a Date field which is filled in by the Now()
function Access provides, which inserts the current date and time. I
believe that is what creates the problem. I don't know how to update
the the Date field in my UPDATE statement. Can you please help? I've
included my sub.

Thank you in advance




Public Sub grid_Update(sender As Object, e As
DataGridCommandEventArgs)


Dim strUnits As String = CType(e.Item.FindControl( "txtUnits"),
TextBox).Text
Dim strid As String = CType(e.Item.FindControl( "ID"), Label).Text

' Dim strid As Integer =
Convert.ToInteger(e.Item.Cells[1].Controls[0].Text)
' Dim strid as String = "36"


' test.text = strid


DBConnection = New OleDbConnection(strconnect)
DBcommand=New OleDbCommand
DBcommand.commandtext="Update holding set units='" + strUnits +"'
where holdid = '" + strid + "' ;"

Dbcommand.commandtype=commandtype.Text

Dbcommand.connection=dbconnection
DBConnection.Open()
DBCommand.ExecuteNonQuery()
DBConnection.Close()

grid.EditItemIndex = -1


BindData()


End Sub

 

[Scott M.]

I understand what you are saying. Where is the data that your DataGrid is
displaying coming from? Access right? And since each record in Access has
a field automatically populated with the now() function, then that field
should be copied and brought down into your disconnected copy of the data
(probably stored in a DataSet, right?). You don't have to display this data
in the DataGrid, but you must bring it down from the original database.

When you do an update of a particular record, your local copy of the data
(DataSet) will have the original date value for that record. So when you
push your changes back up to the original database, your WHERE clause can
have something like:

WHERE localDataSetRowDateColumnValue = originalDatabaseDateColumnValue

Essentially, the trick is to bring down the primary key field for all your
rows into the local data copy and then compare that value (which wouldn't
have changed) to the original when doing any modifications.

I am not sure i understand what you are telling me. I am not pulling
down my date field value in the datagrid. Whenever i add an entry to
the database the date field is filled in by Access as i've set it to
use the now() function access provides in orderto fill it in with the
time and date of the update. Whenever i am updating, since i only
update certain values from the table, the date filed is not updated to
correspond with the update time. I believe that is why i get a data
mismatch error whenever i try to update the table with my current
update statement. I am waiting for your opinion.

Thank you

When you are populating your DataSet in the first place, are you pulling
this date field down into the DataSet? If so, in your update command,
you
need a WHERE clause that checks the date field value from the record
being
updated in the DataSet to the date field of the record in the original
data
source. You never need to know the actual value.

Hi guys,
I've been trying to implement an editable datagrid and i have been
succesful up to the point where i can update my datagrid row by row.
However what used to happen was that once i updated one row, all of
them were updated so i immediatelly figured out that i have to include
the id of every entry in the update statement. This is where the
problem is raised. My database is an Access database. The table i am
updating contains a Date field which is filled in by the Now()
function Access provides, which inserts the current date and time. I
believe that is what creates the problem. I don't know how to update
the the Date field in my UPDATE statement. Can you please help? I've
included my sub.

Thank you in advance




Public Sub grid_Update(sender As Object, e As
DataGridCommandEventArgs)


Dim strUnits As String = CType(e.Item.FindControl( "txtUnits"),
TextBox).Text
Dim strid As String = CType(e.Item.FindControl( "ID"), Label).Text

' Dim strid As Integer =
Convert.ToInteger(e.Item.Cells[1].Controls[0].Text)
' Dim strid as String = "36"


' test.text = strid


DBConnection = New OleDbConnection(strconnect)
DBcommand=New OleDbCommand
DBcommand.commandtext="Update holding set units='" + strUnits +"'
where holdid = '" + strid + "' ;"

Dbcommand.commandtype=commandtype.Text

Dbcommand.connection=dbconnection
DBConnection.Open()
DBCommand.ExecuteNonQuery()
DBConnection.Close()

grid.EditItemIndex = -1


BindData()


End Sub

 

[junglist]

Hi Scott,
first of all thank you for helping me out. I fully understand what
ur telling me. I do pull down all the fields in my dataset. however
the primary key is not the Date field. it is an ID field and i do
compare the one in the dataset with the one in the database. the
problem , i believe, is the way i reference to that id field, because
i get a data mismatch error. The id is a long integer. If i hard
code the vaue of the id in mycode and update the field with that id,
then everything works fine. If i try to retrieve directly from the
dataset (e.Item.Cells[1] or e.Item.Cells[1].Controls(0)) in the update
statement, or i assign its value to a variable then i get the data
mismatch error. After trying everything i got to the conclusion that
i might have to do with the date field. that is why i mentioned date.
Anyway thank you.

I understand what you are saying. Where is the data that your DataGrid is
displaying coming from? Access right? And since each record in Access has
a field automatically populated with the now() function, then that field
should be copied and brought down into your disconnected copy of the data
(probably stored in a DataSet, right?). You don't have to display this data
in the DataGrid, but you must bring it down from the original database.

When you do an update of a particular record, your local copy of the data
(DataSet) will have the original date value for that record. So when you
push your changes back up to the original database, your WHERE clause can
have something like:

WHERE localDataSetRowDateColumnValue = originalDatabaseDateColumnValue

Essentially, the trick is to bring down the primary key field for all your
rows into the local data copy and then compare that value (which wouldn't
have changed) to the original when doing any modifications.

I am not sure i understand what you are telling me. I am not pulling
down my date field value in the datagrid. Whenever i add an entry to
the database the date field is filled in by Access as i've set it to
use the now() function access provides in orderto fill it in with the
time and date of the update. Whenever i am updating, since i only
update certain values from the table, the date filed is not updated to
correspond with the update time. I believe that is why i get a data
mismatch error whenever i try to update the table with my current
update statement. I am waiting for your opinion.

Thank you

When you are populating your DataSet in the first place, are you pulling
this date field down into the DataSet? If so, in your update command,
you
need a WHERE clause that checks the date field value from the record
being
updated in the DataSet to the date field of the record in the original
data
source. You never need to know the actual value.

Hi guys,
I've been trying to implement an editable datagrid and i have been
succesful up to the point where i can update my datagrid row by row.
However what used to happen was that once i updated one row, all of
them were updated so i immediatelly figured out that i have to include
the id of every entry in the update statement. This is where the
problem is raised. My database is an Access database. The table i am
updating contains a Date field which is filled in by the Now()
function Access provides, which inserts the current date and time. I
believe that is what creates the problem. I don't know how to update
the the Date field in my UPDATE statement. Can you please help? I've
included my sub.

Thank you in advance




Public Sub grid_Update(sender As Object, e As
DataGridCommandEventArgs)


Dim strUnits As String = CType(e.Item.FindControl( "txtUnits"),
TextBox).Text
Dim strid As String = CType(e.Item.FindControl( "ID"), Label).Text

' Dim strid As Integer =
Convert.ToInteger(e.Item.Cells[1].Controls[0].Text)
' Dim strid as String = "36"


' test.text = strid


DBConnection = New OleDbConnection(strconnect)
DBcommand=New OleDbCommand
DBcommand.commandtext="Update holding set units='" + strUnits +"'
where holdid = '" + strid + "' ;"

Dbcommand.commandtype=commandtype.Text

Dbcommand.connection=dbconnection
DBConnection.Open()
DBCommand.ExecuteNonQuery()
DBConnection.Close()

grid.EditItemIndex = -1


BindData()


End Sub

 

[Scott M.]

Are you letting the user change the primary key field via the DataGrid?
Probably not, right? So, why would you be retrieving the ID field from the
DataGrid at all? Why not just pull it from the DataSet for the given row
that is being updated? Of course, you will have a mismatch if you pull it
from the DataGrid because e.Item.Cells..... isn't going to intrinsically
know the data type.

Have you tried to cast the ID?

Hi Scott,
first of all thank you for helping me out. I fully understand what
ur telling me. I do pull down all the fields in my dataset. however
the primary key is not the Date field. it is an ID field and i do
compare the one in the dataset with the one in the database. the
problem , i believe, is the way i reference to that id field, because
i get a data mismatch error. The id is a long integer. If i hard
code the vaue of the id in mycode and update the field with that id,
then everything works fine. If i try to retrieve directly from the
dataset (e.Item.Cells[1] or e.Item.Cells[1].Controls(0)) in the update
statement, or i assign its value to a variable then i get the data
mismatch error. After trying everything i got to the conclusion that
i might have to do with the date field. that is why i mentioned date.
Anyway thank you.

I understand what you are saying. Where is the data that your DataGrid
is
displaying coming from? Access right? And since each record in Access
has
a field automatically populated with the now() function, then that field
should be copied and brought down into your disconnected copy of the data
(probably stored in a DataSet, right?). You don't have to display this
data
in the DataGrid, but you must bring it down from the original database.

When you do an update of a particular record, your local copy of the data
(DataSet) will have the original date value for that record. So when you
push your changes back up to the original database, your WHERE clause can
have something like:

WHERE localDataSetRowDateColumnValue = originalDatabaseDateColumnValue

Essentially, the trick is to bring down the primary key field for all
your
rows into the local data copy and then compare that value (which wouldn't
have changed) to the original when doing any modifications.

I am not sure i understand what you are telling me. I am not pulling
down my date field value in the datagrid. Whenever i add an entry to
the database the date field is filled in by Access as i've set it to
use the now() function access provides in orderto fill it in with the
time and date of the update. Whenever i am updating, since i only
update certain values from the table, the date filed is not updated to
correspond with the update time. I believe that is why i get a data
mismatch error whenever i try to update the table with my current
update statement. I am waiting for your opinion.

Thank you



When you are populating your DataSet in the first place, are you
pulling
this date field down into the DataSet? If so, in your update command,
you
need a WHERE clause that checks the date field value from the record
being
updated in the DataSet to the date field of the record in the original
data
source. You never need to know the actual value.

Hi guys,
I've been trying to implement an editable datagrid and i have been
succesful up to the point where i can update my datagrid row by row.
However what used to happen was that once i updated one row, all of
them were updated so i immediatelly figured out that i have to
include
the id of every entry in the update statement. This is where the
problem is raised. My database is an Access database. The table i am
updating contains a Date field which is filled in by the Now()
function Access provides, which inserts the current date and time.
I
believe that is what creates the problem. I don't know how to
update
the the Date field in my UPDATE statement. Can you please help? I've
included my sub.

Thank you in advance




Public Sub grid_Update(sender As Object, e As
DataGridCommandEventArgs)


Dim strUnits As String = CType(e.Item.FindControl( "txtUnits"),
TextBox).Text
Dim strid As String = CType(e.Item.FindControl( "ID"), Label).Text

' Dim strid As Integer =
Convert.ToInteger(e.Item.Cells[1].Controls[0].Text)
' Dim strid as String = "36"


' test.text = strid


DBConnection = New OleDbConnection(strconnect)
DBcommand=New OleDbCommand
DBcommand.commandtext="Update holding set units='" + strUnits +"'
where holdid = '" + strid + "' ;"

Dbcommand.commandtype=commandtype.Text

Dbcommand.connection=dbconnection
DBConnection.Open()
DBCommand.ExecuteNonQuery()
DBConnection.Close()

grid.EditItemIndex = -1


BindData()


End Sub