Solved

UPDATE in ODBC

Posted on 2009-05-08
6
1,332 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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
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

The Eight Noble Truths of Backup and Recovery

How can IT departments tackle the challenges of a Big Data world? This white paper provides a roadmap to success and helps companies ensure that all their data is safe and secure, no matter if it resides on-premise with physical or virtual machines or in the cloud.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
SQL 2005 - Memory Table Column Names 11 75
Delete from table 6 47
How to place a condition in a filter criteria in t-sql? 12 77
JavaFX TableView not displaying correctly 3 55
If you use Adobe Reader X it is possible you can't open OLE PDF documents in the standard. The reason is the 'save box mode' in adobe reader X. Many people think the protected Mode of adobe reader x is only to stop the write access. But this fe…
How to install Selenium IDE and loops for quick automated testing. Get Selenium IDE from http://seleniumhq.org Go to that link and select download selenium in the right hand columnThat will then direct you to their download page.From that page s…
The viewer will learn how to synchronize PHP projects with a remote server in NetBeans IDE 8.0 for Windows.
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.

840 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