By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
424,678 Members | 2,377 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 424,678 IT Pros & Developers. It's quick & easy.

Problem with Datagrid updating using VB (ASP)

P: n/a
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
Nov 18 '05 #1
Share this Question
Share on Google+
5 Replies


P: n/a
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" <th******@hotmail.com> wrote in message
news:76**************************@posting.google.c om...
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

Nov 18 '05 #2

P: n/a
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

"Scott M." <s-***@nospam.nospam> wrote in message news:<e8**************@TK2MSFTNGP11.phx.gbl>...
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" <th******@hotmail.com> wrote in message
news:76**************************@posting.google.c om...
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

Nov 18 '05 #3

P: n/a
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.
"junglist" <th******@hotmail.com> wrote in message
news:76**************************@posting.google.c om...
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

"Scott M." <s-***@nospam.nospam> wrote in message
news:<e8**************@TK2MSFTNGP11.phx.gbl>...
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" <th******@hotmail.com> wrote in message
news:76**************************@posting.google.c om...
> 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

Nov 18 '05 #4

P: n/a
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.

"Scott M." <s-***@nospam.nospam> wrote in message news:<#y**************@TK2MSFTNGP15.phx.gbl>...
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.
"junglist" <th******@hotmail.com> wrote in message
news:76**************************@posting.google.c om...
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

"Scott M." <s-***@nospam.nospam> wrote in message
news:<e8**************@TK2MSFTNGP11.phx.gbl>...
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" <th******@hotmail.com> wrote in message
news:76**************************@posting.google.c om...
> 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

Nov 18 '05 #5

P: n/a
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?
"junglist" <th******@hotmail.com> wrote in message
news:76************************@posting.google.com ...
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.

"Scott M." <s-***@nospam.nospam> wrote in message
news:<#y**************@TK2MSFTNGP15.phx.gbl>...
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.
"junglist" <th******@hotmail.com> wrote in message
news:76**************************@posting.google.c om...
>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
>
>
>
> "Scott M." <s-***@nospam.nospam> wrote in message
> news:<e8**************@TK2MSFTNGP11.phx.gbl>...
>> 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" <th******@hotmail.com> wrote in message
>> news:76**************************@posting.google.c om...
>> > 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

Nov 18 '05 #6

This discussion thread is closed

Replies have been disabled for this discussion.