?
Solved

UPDATE in ODBC

Posted on 2009-05-08
6
Medium Priority
?
1,338 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
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
Get 15 Days FREE Full-Featured Trial

Benefit from a mission critical IT monitoring with Monitis Premium or get it FREE for your entry level monitoring needs.
-Over 200,000 users
-More than 300,000 websites monitored
-Used in 197 countries
-Recommended by 98% of users

 
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 1000 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 1000 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

Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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…
INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
This tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
The viewer will learn how to synchronize PHP projects with a remote server in NetBeans IDE 8.0 for Windows.

762 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