Solved

UPDATE in ODBC

Posted on 2009-05-08
6
1,336 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
Creating Instructional Tutorials  

For Any Use & On Any Platform

Contextual Guidance at the moment of need helps your employees/users adopt software o& achieve even the most complex tasks instantly. Boost knowledge retention, software adoption & employee engagement with easy solution.

 
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

Enroll in May's Course of the Month

May’s Course of the Month is now available! Experts Exchange’s Premium Members and Team Accounts have access to a complimentary course each month as part of their membership—an extra way to increase training and boost professional development.

Question has a verified solution.

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

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
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 tutorial covers a step-by-step guide to install VisualVM launcher in eclipse.
The viewer will learn how to use and create new code templates in NetBeans IDE 8.0 for Windows.

732 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