Solved

SQL Server Database Problem.

Posted on 2000-03-10
8
386 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
  • 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
 

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
Highfive Gives IT Their Time Back

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

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

Maximize Your Threat Intelligence Reporting

Reporting is one of the most important and least talked about aspects of a world-class threat intelligence program. Here’s how to do it right.

Join & Write a Comment

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…
Introduction: Database storage, where is the exe actually on the disc? Playing a game selected randomly (how to generate random numbers).  Error trapping with try..catch to help the code run even if something goes wrong. Continuing from the seve…
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.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…

747 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

Need Help in Real-Time?

Connect with top rated Experts

14 Experts available now in Live!

Get 1:1 Help Now