Solved

UPDATE in ODBC

Posted on 2009-05-08
6
1,325 Views
Last Modified: 2013-12-14
When using Open() to run a database query with ODBC, it appears that only SELECT can be used in the query string.  I tried using UPDATE, and it returned debug assertion error dbcore.cpp line 3282.  However, when I call CanUpdate(), it returns true.

I once read that in order to make record changes on your database, you first need to do a SELECT query, set the new values in your program and exchange the data.  Is this correct?  If so, it seems long-winded.  Is there a way that UPDATE can be used instead?
0
Comment
Question by:reidy_boy
6 Comments
 
LVL 40

Expert Comment

by:mrjoltcola
ID: 24335779
Not true. All valid SQL statements are fair game in ODBC.

Please post your code so we can see?
0
 
LVL 16

Expert Comment

by:brad2575
ID: 24335866
Does the ID you are using in the ODBC have permissions to do an update on the database?

If so you should just be able to do an update no problem.
0
 
LVL 1

Author Comment

by:reidy_boy
ID: 24335922
I have attached the cpp and h class files, and the code snippet that contains the update query.
CRecordset PositiveResultSet(theApp.GetDatabase() );

CDBVariant Variant;

TCHAR Message[2000];

 

	if (sAcmeConfig[ACME_STAFF_COLUMN] == "")

		return;

 

	sprintf (Message, "UPDATE LECTDETS SET AcmeMiles='10' WHERE SetId='2008/2009' AND LecturerId='ESN'");

	PositiveResultSet.Open(CRecordset::snapshot, Message);

Open in new window

AcmeCorporation.cpp.txt
AcmeCorporation.h.txt
0
Complete VMware vSphere® ESX(i) & Hyper-V Backup

Capture your entire system, including the host, with patented disk imaging integrated with VMware VADP / Microsoft VSS and RCT. RTOs is as low as 15 seconds with Acronis Active Restore™. You can enjoy unlimited P2V/V2V migrations from any source (even from a different hypervisor)

 
LVL 19

Expert Comment

by:alb66
ID: 24336545
Try to use CRecordset::dynaset instead of CRecordset::snapshot
0
 
LVL 19

Accepted Solution

by:
alb66 earned 250 total points
ID: 24336573
I think you don't need a recordset.

Try also theApp.GetDatabase()->ExecuteSQL( Message );
0
 
LVL 39

Assisted Solution

by:itsmeandnobodyelse
itsmeandnobodyelse earned 250 total points
ID: 24365954
>>>> it appears that only SELECT can be used in the query string.
That is nearly true. You also could pass a table name or space what would cause the GetDefaultSQL() member function of your CRecordset derived class being called. It is also possible to pass a call to a stored procedure in the Open()-

>>>> Try also theApp.GetDatabase()->ExecuteSQL( Message );

alb66 is right. For update/insert/delete sql statements you need to use CDataBase::ExecuteSQL rather than a CRecordset member function. The point is that these statements don't return a recordset. Hence they were independent of a recordset and could be called with any open connection (what is the CDatabase instance).

But of course you could make delete, update and insert on a CRecordset as well. For that you need to derive from CRecordset - best by using the class wizard. Then you get a own recordset class generated which fully was connected to an existing table. When calling Open on an instance of that class it would read in either all the records of that table or filtered by the m_strFilter where clause. In any case you  now could move within that record set using any of teh Move member functions and either call AddNew or Edit at the current position - both were finished by an Update - or call Delete member function  on a position which would remove the current record.
0

Featured Post

Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Introduction: Dialogs (2) modeless dialog and a worker thread.  Handling data shared between threads.  Recursive functions. Continuing from the tenth article about sudoku.   Last article we worked with a modal dialog to help maintain informat…
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
This video will show you how to get GIT to work in Eclipse.   It will walk you through how to install the EGit plugin in eclipse and how to checkout an existing repository.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

12 Experts available now in Live!

Get 1:1 Help Now