?
Solved

SQL Server Database Problem.

Posted on 2000-03-10
8
Medium Priority
?
394 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
Get MongoDB database support online, now!

At Percona’s web store you can order your MongoDB database support needs in minutes. No hassles, no fuss, just pick and click. Pay online with a credit card. Handle your MongoDB database support now!

 

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

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

Introduction: Ownerdraw of the grid button.  A singleton class implentation and usage. Continuing from the fifth article about sudoku.   Open the project in visual studio. Go to the class view – CGridButton should be visible as a class.  R…
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…
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.
Do you want to know how to make a graph with Microsoft Access? First, create a query with the data for the chart. Then make a blank form and add a chart control. This video also shows how to change what data is displayed on the graph as well as form…
Suggested Courses
Course of the Month10 days, 4 hours left to enroll

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