Can't update a record using ADO.Net

Can't update a record using ADO.Net

Post by Andrew Alfo » Sat, 20 Jul 2002 23:19:08



I'm new to ADO.Net and I am having trouble updating a record in the
database.  I don't get any errors when using the code below, however
no changes are made to the specific record.  ADO.Net seems a step
backwards from ADO.  Not sure that I like this!

MY CODE:
- - - - - - - - - - - - - - - - - - - - -
Dim cnPrintegra as SQLConnection
Dim cmdUsers as SQLDataAdapter
Dim dsPrintegra as Dataset
Dim tblUsers as DataTable

' Create a connection to the data source.
cnPrintegra = New SQLConnection("server=SQL_DB;Initial
Catalog=UserDB;User ID=;Password=;")

'Fill the command objects with data from data connection                
cmdUsers = New SQLDataAdapter("SELECT * FROM tblUsers WHERE Email = '"
& Email.Text & "' AND Password = '" & Password.Text & "'" ,
cnPrintegra)

dsPrintegra = New Dataset
cmdUsers.Fill(dsPrintegra, "tblUsers")

tblUsers = New DataTable
tblUsers = dsPrintegra.Tables("tblUsers")

...
... 'Other Code
...

'UPDATE THE RECORD HERE                
dsPrintegra.Tables("tblUsers").Rows(0).item("LastLogin") = Now()
cmdUsers.Update(dsPrintegra)

- - - - - - - - - - - - - - - - -

That's pretty much going by documentation from MSDN library. Like I
said...no errors...and I know that it is accessing the record... I can
pull from the dataset; just can't update it.

You help is GREATLY appreciated!!!

Andrew D. Alford
CFC Print Solutions, Inc.

 
 
 

Can't update a record using ADO.Net

Post by Bill » Sun, 21 Jul 2002 08:35:30


You'll get better response to your ADO.NET questions on
microsoft.public.dotnet.framework.adonet.

You're about 1/4 way there. Unlike ADO classic, ADO.NET does not conger the
UPDATE/INSERT/DELETE SQL for you unless you ask it to. Even then the
mechanisms ADO.NET uses are fairly crude when compared to the flexibility
you get with ADO classic.
You're going to have to code the UpdateCommand, InsertCommand and
DeleteCommand either manually (best performance, best flexibility) or using
the DataAdapter Configuration Wizard, or the CommandBuilder class.

Yes, there are lots of other options--you can code the UPDATE statements
yourself and simply execute them from an ADO Command object (SqlCommand,
OleDbCommand...)/

All of these are discussed in detail (with lots of examples) in my book
"ADO.NET and ADO Examples and Best Practices..."
http://www.betav.com/Files/Books/current_titles.htm

hth
--
William (Bill) Vaughn
Author, Trainer, Mentor
Microsoft Pacwest Regional Director
Beta V Corporation
www.betav.com

Quote:> I'm new to ADO.Net and I am having trouble updating a record in the
> database.  I don't get any errors when using the code below, however
> no changes are made to the specific record.  ADO.Net seems a step
> backwards from ADO.  Not sure that I like this!

> MY CODE:
> - - - - - - - - - - - - - - - - - - - - -
> Dim cnPrintegra as SQLConnection
> Dim cmdUsers as SQLDataAdapter
> Dim dsPrintegra as Dataset
> Dim tblUsers as DataTable

> ' Create a connection to the data source.
> cnPrintegra = New SQLConnection("server=SQL_DB;Initial
> Catalog=UserDB;User ID=;Password=;")

> 'Fill the command objects with data from data connection
> cmdUsers = New SQLDataAdapter("SELECT * FROM tblUsers WHERE Email = '"
> & Email.Text & "' AND Password = '" & Password.Text & "'" ,
> cnPrintegra)

> dsPrintegra = New Dataset
> cmdUsers.Fill(dsPrintegra, "tblUsers")

> tblUsers = New DataTable
> tblUsers = dsPrintegra.Tables("tblUsers")

> ...
> ... 'Other Code
> ...

> 'UPDATE THE RECORD HERE
> dsPrintegra.Tables("tblUsers").Rows(0).item("LastLogin") = Now()
> cmdUsers.Update(dsPrintegra)

> - - - - - - - - - - - - - - - - -

> That's pretty much going by documentation from MSDN library. Like I
> said...no errors...and I know that it is accessing the record... I can
> pull from the dataset; just can't update it.

> You help is GREATLY appreciated!!!

> Andrew D. Alford
> CFC Print Solutions, Inc.


 
 
 

Can't update a record using ADO.Net

Post by Andrew Alfo » Wed, 24 Jul 2002 03:32:56


Thanks for your help.  I researched the CommandBuilder and it worked
for me just fine.  I think I will check out your book very soon.

Thanks again!

Andrew D. Alford
CFC Print Solutions, Inc.


> You'll get better response to your ADO.NET questions on
> microsoft.public.dotnet.framework.adonet.

> You're about 1/4 way there. Unlike ADO classic, ADO.NET does not conger the
> UPDATE/INSERT/DELETE SQL for you unless you ask it to. Even then the
> mechanisms ADO.NET uses are fairly crude when compared to the flexibility
> you get with ADO classic.
> You're going to have to code the UpdateCommand, InsertCommand and
> DeleteCommand either manually (best performance, best flexibility) or using
> the DataAdapter Configuration Wizard, or the CommandBuilder class.

> Yes, there are lots of other options--you can code the UPDATE statements
> yourself and simply execute them from an ADO Command object (SqlCommand,
> OleDbCommand...)/

> All of these are discussed in detail (with lots of examples) in my book
> "ADO.NET and ADO Examples and Best Practices..."
> http://www.betav.com/Files/Books/current_titles.htm

> hth
> --
> William (Bill) Vaughn
> Author, Trainer, Mentor
> Microsoft Pacwest Regional Director
> Beta V Corporation
> www.betav.com


> > I'm new to ADO.Net and I am having trouble updating a record in the
> > database.  I don't get any errors when using the code below, however
> > no changes are made to the specific record.  ADO.Net seems a step
> > backwards from ADO.  Not sure that I like this!

> > MY CODE:
> > - - - - - - - - - - - - - - - - - - - - -
> > Dim cnPrintegra as SQLConnection
> > Dim cmdUsers as SQLDataAdapter
> > Dim dsPrintegra as Dataset
> > Dim tblUsers as DataTable

> > ' Create a connection to the data source.
> > cnPrintegra = New SQLConnection("server=SQL_DB;Initial
> > Catalog=UserDB;User ID=;Password=;")

> > 'Fill the command objects with data from data connection
> > cmdUsers = New SQLDataAdapter("SELECT * FROM tblUsers WHERE Email = '"
> > & Email.Text & "' AND Password = '" & Password.Text & "'" ,
> > cnPrintegra)

> > dsPrintegra = New Dataset
> > cmdUsers.Fill(dsPrintegra, "tblUsers")

> > tblUsers = New DataTable
> > tblUsers = dsPrintegra.Tables("tblUsers")

> > ...
> > ... 'Other Code
> > ...

> > 'UPDATE THE RECORD HERE
> > dsPrintegra.Tables("tblUsers").Rows(0).item("LastLogin") = Now()
> > cmdUsers.Update(dsPrintegra)

> > - - - - - - - - - - - - - - - - -

> > That's pretty much going by documentation from MSDN library. Like I
> > said...no errors...and I know that it is accessing the record... I can
> > pull from the dataset; just can't update it.

> > You help is GREATLY appreciated!!!

> > Andrew D. Alford
> > CFC Print Solutions, Inc.

 
 
 

1. VB6,Access97,ADO - Update query doesn't update all records

 I am using VB6 and Access 97 in an application and took the opportunity to
 start learning how to incorporate ADO into an VB application.  The
 database  contains several tables and one linked table (to another Access
97
 database).  I designed an update query in Access that uses data in the
 linked table to update a field in one of the other tables.   It works fine
 when I execute it from the Access environment.  However, when I try to
 execute the same query using ADO, not all of the records are updated
 unless  I run the query twice.  The records that are missed the first time
are not
 consistent (sometimes all records are updated).  I think I have tried all
 possible ways to execute the query using ADO to no avail.  Here is the
 basic  code I am using;

 {defined in a general module}
 strConnVOCdbs = "Provider=Microsoft.Jet.OLEDB.4.0; " _
         & "Data Source=" & pathVOCdbs & "; " _
         & "Persist Security Info=False"

 {defined in declaration of Form1}
 Public WithEvents connVOCdbs As ADODB.Connection

 {defined in a procedure on Form 1}
 Set connVOCdbs = New ADODB.Connection
 connVOCdbs.Open strConnVOCdbs

 Dim cmd As ADODB.Command
 Set cmd = New ADODB.Command
 Set cmd.ActiveConnection = connVOCdbs

 With cmd
     .Parameters.Append .CreateParameter("iAnRunSeq", adInteger,
adParamInput, 0)
     .Parameters("iAnRunSeq") = tAnRunSeq
     .CommandText = "update_Results_CalStdConc" 'the name of  the  query in
the Access database
     .CommandType = adCmdStoredProc
     .Execute , , adExecuteNoRecords
     .Parameters.Delete ("iAnRunSeq")
 End With

 Here is SQL statement executed by the Access query:

 sqlS = "PARAMETERS iAnRunSeq Long;" _
     & "UPDATE RunLog INNER JOIN (Results INNER JOIN (AnRuns INNER JOIN
 QCTrueValues ON (AnRuns.MethodID = QCTrueValues.MethodID) AND
 (AnRuns.RunType = QCTrueValues.RunType)) ON Results.Parameter =
 QCTrueValues.compound) ON (RunLog.RunLogSeq = Results.RunLogSeq) AND
 (AnRuns.AnRunSeq = RunLog.AnRunSeq) AND (RunLog.SamType =
 QCTrueValues.samtype) AND (RunLog.LabSampleID = QCTrueValues.Level) SET
 Results.[Value] = [QCTrueValues]![truevalue], Results.ValueUnit =
 [QCTrueValues]![units] " _
     & "WHERE ((([Results]![Value])<>[QCTrueValues]![truevalue]) AND
 ((AnRuns.AnRunSeq)=[iAnRunSeq]) AND ((RunLog.SamType)='cal') AND
 ((Results.ParaType)='co') AND ((Results.ValueType)='conc'));"

 I have tried substituting the sql statement for .CommandText but the
 results  are the same.

 I sure would appreciate any insight you can provide.  Thank you for your
 time and consideration.

Dan Hillman

2. TQuery

3. Converting from ADO Record Sets to a ADO.NET record set object

4. Checking Database Space

5. Updating text Data using ADO.NET and VFP OLEDB

6. Active Directory and SQL

7. ADO Won't update records??

8. BCP and Temporary Tables

9. ADO.NET Component and VB6 client - ADO.Net or ADO 2.7

10. Trigger doesn't fire properly when Update is used against more than one record

11. Joining tables / using another record's value if record doesn't exist

12. Named Pipes connection problem via ASP.NET using ADO.NET

13. Slow Record Addition using VC++ CRecordSet's AddNew and Update funtions