473,549 Members | 2,588 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Problem with Datagrid updating using VB (ASP)

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(sen der As Object, e As
DataGridCommand EventArgs)
Dim strUnits As String = CType(e.Item.Fi ndControl( "txtUnits") ,
TextBox).Text
Dim strid As String = CType(e.Item.Fi ndControl( "ID"), Label).Text

' Dim strid As Integer =
Convert.ToInteg er(e.Item.Cells[1].Controls[0].Text)
' Dim strid as String = "36"
' test.text = strid
DBConnection = New OleDbConnection (strconnect)
DBcommand=New OleDbCommand
DBcommand.comma ndtext="Update holding set units='" + strUnits +"'
where holdid = '" + strid + "' ;"

Dbcommand.comma ndtype=commandt ype.Text

Dbcommand.conne ction=dbconnect ion
DBConnection.Op en()
DBCommand.Execu teNonQuery()
DBConnection.Cl ose()

grid.EditItemIn dex = -1
BindData()
End Sub
Nov 18 '05 #1
5 2015
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******@hotma il.com> wrote in message
news:76******** *************** ***@posting.goo gle.com...
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(sen der As Object, e As
DataGridCommand EventArgs)
Dim strUnits As String = CType(e.Item.Fi ndControl( "txtUnits") ,
TextBox).Text
Dim strid As String = CType(e.Item.Fi ndControl( "ID"), Label).Text

' Dim strid As Integer =
Convert.ToInteg er(e.Item.Cells[1].Controls[0].Text)
' Dim strid as String = "36"
' test.text = strid
DBConnection = New OleDbConnection (strconnect)
DBcommand=New OleDbCommand
DBcommand.comma ndtext="Update holding set units='" + strUnits +"'
where holdid = '" + strid + "' ;"

Dbcommand.comma ndtype=commandt ype.Text

Dbcommand.conne ction=dbconnect ion
DBConnection.Op en()
DBCommand.Execu teNonQuery()
DBConnection.Cl ose()

grid.EditItemIn dex = -1
BindData()
End Sub

Nov 18 '05 #2
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.nosp am> wrote in message news:<e8******* *******@TK2MSFT NGP11.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******@hotma il.com> wrote in message
news:76******** *************** ***@posting.goo gle.com...
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(sen der As Object, e As
DataGridCommand EventArgs)
Dim strUnits As String = CType(e.Item.Fi ndControl( "txtUnits") ,
TextBox).Text
Dim strid As String = CType(e.Item.Fi ndControl( "ID"), Label).Text

' Dim strid As Integer =
Convert.ToInteg er(e.Item.Cells[1].Controls[0].Text)
' Dim strid as String = "36"
' test.text = strid
DBConnection = New OleDbConnection (strconnect)
DBcommand=New OleDbCommand
DBcommand.comma ndtext="Update holding set units='" + strUnits +"'
where holdid = '" + strid + "' ;"

Dbcommand.comma ndtype=commandt ype.Text

Dbcommand.conne ction=dbconnect ion
DBConnection.Op en()
DBCommand.Execu teNonQuery()
DBConnection.Cl ose()

grid.EditItemIn dex = -1
BindData()
End Sub

Nov 18 '05 #3
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 localDataSetRow DateColumnValue = originalDatabas eDateColumnValu e

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******@hotma il.com> wrote in message
news:76******** *************** ***@posting.goo gle.com...
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.nosp am> wrote in message
news:<e8******* *******@TK2MSFT NGP11.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******@hotma il.com> wrote in message
news:76******** *************** ***@posting.goo gle.com...
> 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(sen der As Object, e As
> DataGridCommand EventArgs)
>
>
> Dim strUnits As String = CType(e.Item.Fi ndControl( "txtUnits") ,
> TextBox).Text
> Dim strid As String = CType(e.Item.Fi ndControl( "ID"), Label).Text
>
> ' Dim strid As Integer =
> Convert.ToInteg er(e.Item.Cells[1].Controls[0].Text)
> ' Dim strid as String = "36"
>
>
> ' test.text = strid
>
>
> DBConnection = New OleDbConnection (strconnect)
> DBcommand=New OleDbCommand
> DBcommand.comma ndtext="Update holding set units='" + strUnits +"'
> where holdid = '" + strid + "' ;"
>
> Dbcommand.comma ndtype=commandt ype.Text
>
> Dbcommand.conne ction=dbconnect ion
> DBConnection.Op en()
> DBCommand.Execu teNonQuery()
> DBConnection.Cl ose()
>
> grid.EditItemIn dex = -1
>
>
> BindData()
>
>
> End Sub

Nov 18 '05 #4
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.nosp am> wrote in message news:<#y******* *******@TK2MSFT NGP15.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 localDataSetRow DateColumnValue = originalDatabas eDateColumnValu e

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******@hotma il.com> wrote in message
news:76******** *************** ***@posting.goo gle.com...
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.nosp am> wrote in message
news:<e8******* *******@TK2MSFT NGP11.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******@hotma il.com> wrote in message
news:76******** *************** ***@posting.goo gle.com...
> 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(sen der As Object, e As
> DataGridCommand EventArgs)
>
>
> Dim strUnits As String = CType(e.Item.Fi ndControl( "txtUnits") ,
> TextBox).Text
> Dim strid As String = CType(e.Item.Fi ndControl( "ID"), Label).Text
>
> ' Dim strid As Integer =
> Convert.ToInteg er(e.Item.Cells[1].Controls[0].Text)
> ' Dim strid as String = "36"
>
>
> ' test.text = strid
>
>
> DBConnection = New OleDbConnection (strconnect)
> DBcommand=New OleDbCommand
> DBcommand.comma ndtext="Update holding set units='" + strUnits +"'
> where holdid = '" + strid + "' ;"
>
> Dbcommand.comma ndtype=commandt ype.Text
>
> Dbcommand.conne ction=dbconnect ion
> DBConnection.Op en()
> DBCommand.Execu teNonQuery()
> DBConnection.Cl ose()
>
> grid.EditItemIn dex = -1
>
>
> BindData()
>
>
> End Sub

Nov 18 '05 #5
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******@hotma il.com> wrote in message
news:76******** *************** *@posting.googl e.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.nosp am> wrote in message
news:<#y******* *******@TK2MSFT NGP15.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 localDataSetRow DateColumnValue = originalDatabas eDateColumnValu e

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******@hotma il.com> wrote in message
news:76******** *************** ***@posting.goo gle.com...
>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.nosp am> wrote in message
> news:<e8******* *******@TK2MSFT NGP11.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******@hotma il.com> wrote in message
>> news:76******** *************** ***@posting.goo gle.com...
>> > 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(sen der As Object, e As
>> > DataGridCommand EventArgs)
>> >
>> >
>> > Dim strUnits As String = CType(e.Item.Fi ndControl( "txtUnits") ,
>> > TextBox).Text
>> > Dim strid As String = CType(e.Item.Fi ndControl( "ID"), Label).Text
>> >
>> > ' Dim strid As Integer =
>> > Convert.ToInteg er(e.Item.Cells[1].Controls[0].Text)
>> > ' Dim strid as String = "36"
>> >
>> >
>> > ' test.text = strid
>> >
>> >
>> > DBConnection = New OleDbConnection (strconnect)
>> > DBcommand=New OleDbCommand
>> > DBcommand.comma ndtext="Update holding set units='" + strUnits +"'
>> > where holdid = '" + strid + "' ;"
>> >
>> > Dbcommand.comma ndtype=commandt ype.Text
>> >
>> > Dbcommand.conne ction=dbconnect ion
>> > DBConnection.Op en()
>> > DBCommand.Execu teNonQuery()
>> > DBConnection.Cl ose()
>> >
>> > grid.EditItemIn dex = -1
>> >
>> >
>> > BindData()
>> >
>> >
>> > End Sub

Nov 18 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

7
4854
by: Brian Conway | last post by:
Can anyone give me an example of how I can loop through a datagrid and have it run a stored procedure each time it loops?
3
4922
by: Kevin Pedersen | last post by:
Hello, I am using an editable datagrid. After I save the changes the datagrid shows the old values. I've read the posts about the Page_Load and not binding the datagrid each time. The SQL that is being sent to the database is correct and the changes are eventually being made. If I refresh the page after the update then the new values...
3
2198
by: D. Shane Fowlkes | last post by:
I have a Datagrid which in theory, should allow you to edit and update the records. I've stripped my test page down so that it's only attempting to update one field - "description". Yet when I make the changes and click Update, it reverts back to the old value. I'm stumped! I've also checked the db table and it is infact not updating. I'm...
1
3773
by: Harry Devine | last post by:
I have a DataGrid that is configured to use the Edit/Update/Cancel concept correctly. My grid shows values from 5 database fields. I only need to update that last 4 fields. The last field is a Yes/No value in Access. Using the OleDbCommand, if I do not consider the Yes/No field, the ExecuteNonQuery command, using my UPDATE SQL statement,...
1
1670
by: headbig003 | last post by:
Hello I put template column in datagrid. Below is the html code I used. <asp:datagrid id="dgAnswer" runat="server" Width="360px" AutoGenerateColumns="False" DataKeyField="ansID"> <Columns> <asp:TemplateColumn HeaderText="Answer Text"> <ItemTemplate> <asp:TextBox id="txtItem" runat="server" Width="201px" Text='<%#
4
1237
by: A P | last post by:
Hi! I'm a new user of VB.NET 2003 and still learning its feature, one of them is datagrid. I am using Access 2002 Database on my project and trying to update values of a table using datagrid with Edit,Update,Cancel Button. I have tried following the sample code on the MSDN library but Update command is not updating. Edit and cancel field is...
1
1231
by: Erland | last post by:
Hi all, I have created a simple database table in ACCESS named as "Users" that contains only two fields "UserID" ( Primary key )and "Password". I am having problems while UPDATING the records in DataGrid. When i click Edit button in datagrid and edit my values it actually doesn't update the vales with newly entered values rather it keep on...
2
4359
by: toddw607 | last post by:
Hi Everyone! I have a ASP.NET webform that brings data in from SQL Server 2000 and displays it on a page. I want to update the webpage while in IE using the following code: <%@ Import Namespace="System.Data" %> <%@ Import Namespace="System.Data.SqlClient" %> <%@ Import Namespace="System.Data.OleDb" %> <html>
8
3055
by: Brock | last post by:
I am trying to populate a Crystal Report from data in my DataGrid. The reason for this is that I want the user to be able to change values without updating the database, but still have their report reflect the values they anticipate committing to see hypothetical totals of columns from a set of records. These records are displaying properly...
0
7726
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. ...
0
7967
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that...
1
7485
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For...
0
7819
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the...
0
3505
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in...
0
3488
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
1953
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system
1
1064
muto222
by: muto222 | last post by:
How can i add a mobile payment intergratation into php mysql website.
0
772
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.