[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

SQL Server Database Problem.

Posted on 2000-03-10
8
Medium Priority
?
395 Views
Last Modified: 2013-11-20
==> I am using SQL Server database where I have a col dt_entry of type date/time with format dd/mm/yyyy. In my application I have one CRecordset derived class for above table say m_pSet. Now I want to filter data on a specific date. I am using Masked CDateEdit Edit control for taking user input. Pl help me to give code like m_pSet->m_strFilter=.......

==> When I am trying to save data by transfering value like m_pSet->m_Name = "sdf" and calling m_pSet->Update() It is giving "RECORDSET IS READ ONLY". The database property has been properly set to allow update,modify,delete etc.

=> Will ExecuteSQL("select * from ab") will be perfectly working in SQL Server as ACCESS ? I am facing some problem of not executing it. What may be the error?

Thanks in advance.
0
Comment
Question by:majorjohn
[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
  • 2
  • 2
  • 2
  • +2
8 Comments
 

Expert Comment

by:balugaa
ID: 2604308
1. the m_strFilter basically provides the where clause of your SELECT statement.
if the date being received is in text format ie. a string then something like

m_strFilter.Format(" dt_entry = #%s#", strDate)

will work.

2.  Some code about how you are editing/adding may be helpful here, to find out why the problem is arising.

3.  Again some code, or more detailed description maybe it is not clear what you are asking    

 
0
 
LVL 5

Expert Comment

by:vachooho
ID: 2605561
1. Your sequence for updateing data should be the following
m_pSet->Edit();
m_pSet->m_Name = "...";
m_pSet->... = ...;
m_pSet->Update();

2. Check CRecordset's GetDefaultSQL() - it should contain ONLY ONE table name or the exact "select ... from ..." clause with ONE table involved in query.

3. SQL Server accept date values only in string form
Thus your m_strFilter should be something like "dt_entry = 'Jan 12, 2000'"

NOTE, that I do not use ## separator as it is needed for ACCESS.

By the way - you can input date values in many ways, as far as they are strings

Look at the following dates

12 Jan 2000
Jan 12 2000
12/01/2000

As I understand from SQL server documnetation it accept all these values and deal correctly with them

However I advice you use default locale formats for date values

thus I use this snippet

COleDateTime dt;
dt = ... // input value from user or get default date/time
m_pSet->m_strFilter.Format("dt_value = '%s'", dt.Format());

this will format date/time using current machine locale information in place it into sql query's filter.

Hope this helps
0
 
LVL 1

Author Comment

by:majorjohn
ID: 2627411
In my SQL Server Database I have set Option checked for APPEND,MODIFY AND DELETE. Pl. see the following :

1) In DOC header class I am declaring an object of CDatabase.(CDatabase mdb)
2) In OnNewDocument() of DOC CPP I am opening the database by
mdb.Open(NULL,FALSE,FALSE, strconnect,.....).
Before that I am creating a new CRecordset Class for my table. and in OnNewDocuement() linking this CRecordset pointer with CDatabase object.

3) Then in OnSave() using
m_Pset->Edit()
m_Pset->m_Name="sfdsdfdsf"
m_Pset->Update()

It is doing everything but only giving Recordset is READONLY.

What to do?
0
Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

 

Expert Comment

by:balugaa
ID: 2627474
Hope this helps:

CDatabase m_Database;

// Name used to register database in Registry;

CString sDBName("MyDatabase");

m_Database.Open((LPCTSTR) sDBName);

CSomeRecordSet foo(&m_Database);

// To edit there has to be a record:
foo.m_strfilter.Format(" bar = barfoo");
 if(foo.Open())
{
   if(!IsEOF)
  {
      //Do Edit Stuff
  }
  else // Do Add Stuff;

}
0
 
LVL 5

Expert Comment

by:vachooho
ID: 2629008
what is in your CRecordset's derived class GetDefaultSQL() member body?
0
 

Expert Comment

by:khyatt
ID: 2631393
Hi,

==> I am using SQL Server database where I have a col dt_entry of type date/time with format dd/mm/yyyy. In my application I have one CRecordset derived class for above table say m_pSet. Now I want to filter data on a specific date. I am using Masked CDateEdit Edit control for taking user input. Pl help me to give code like m_pSet->m_strFilter=.......


As mention in a previous answer, you must make sure that the locale is not interfering with your date structure. I always send date strings to the server in DD MMM YYYY formar (i.e. 12 Jan 2000). This is a foolproof way to send date stuff to SQL.

For a CTime/COleDateTime structure, there's a Format method you can use which is local-proof:

CTime ct;

ct.Format("%d %b %Y ");

Therefore, since m_strFilter is basically a WHERE clause, you can enter:

m_strFilter.Format(" dt_entry = '%s' ", ct.Format("%d %b %Y"));  // Date conversion to dd mmm yyyy


==> When I am trying to save data by transfering value like m_pSet->m_Name = "sdf" and calling m_pSet->Update() It is giving "RECORDSET IS READ ONLY". The database property has been properly set to allow update,modify,delete etc.

If you join tables (i.e. run a query to extract data from more than one table), your recordset will be readonly as it doesn't know how to update more than one table as a time.
Instead of the Update method, you should build SQL UPDATE statements (i.e. UPDATE table1 SET field = value WHERE condition) and send them viw the CDatabase::ExecuteSQL() method. If you use try/catch you will pick up any problems with the execution.
=> Will ExecuteSQL("select * from ab") will be perfectly working in SQL Server as ACCESS ? I am facing some problem of not executing it. What may be the error?

ExecuteSQL() doesn't return any rows (see documentation). Therefore a Select statement sent with ExecuteSQL won't return anything. Use CRecordset or CDAORecordset... if it 's a JET database.

I hope this was useful.
0
 
LVL 1

Accepted Solution

by:
kkarunakar earned 600 total points
ID: 2694818
Hi there..
I am suggesting solution to ur problem and hope it will work.
Answers are in order of question.
1. Pls return COleDateTime object from mask date class. Or if u r getting CString u can get COleDateTime object using ParseDateTime() function.
Now  u can make u r query easily.
U can just make COleDateTime to CString...
U r m_strFilter.
CString cDate = odtDate.Format(...);
m_strFilter.Format("[date] = '%s'",cDate);
Sqlserver compare date with string..
2.Go to recordset constructor change type at the last line to dynaset.
m_nDefaultType = dynaset;
3.ExecuteSql() - It is used for only Insert,delete,update statement..
so it won't work for sql statement
Regards keshav
0
 
LVL 1

Expert Comment

by:kkarunakar
ID: 2695754
Thanks for accepting my answer..
0

Featured Post

Moving data to the cloud? Find out if you’re ready

Before moving to the cloud, it is important to carefully define your db needs, plan for the migration & understand prod. environment. This wp explains how to define what you need from a cloud provider, plan for the migration & what putting a cloud solution into practice entails.

Question has a verified solution.

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

This is to be the first in a series of articles demonstrating the development of a complete windows based application using the MFC classes.  I’ll try to keep each article focused on one (or a couple) of the tasks that one may meet.   Introductio…
Have you tried to learn about Unicode, UTF-8, and multibyte text encoding and all the articles are just too "academic" or too technical? This article aims to make the whole topic easy for just about anyone to understand.
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.
In this video you will find out how to export Office 365 mailboxes using the built in eDiscovery tool. Bear in mind that although this method might be useful in some cases, using PST files as Office 365 backup is troublesome in a long run (more on t…

656 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