Database Discussion Boards

MemLeak4-Apr-03 8:00

4-Apr-03 8:00

Hi,

I want to call an INSERT into my access database via ADO.

To do that operation I use the Execute fonction.

CString query = "INSERT INTO Clinic
VALUE(ClinicID,ClinicName,Address,City,PostalCode,PhoneNumb,FaxNumber,Email,Notes)";

m_pCmdChange->ActiveConnection = m_pConn;
m_pCmdChange->CommandText = (_bstr_t)query;

// CommandText property already set before function was called.
m_pCmdChange->Execute(NULL, NULL, adCmdText);

Into my table, I have a fields named ClinicID that is a Access AutoNumber.

If I execute the preceding code, it work fine except the fact that I have to enter ClinicID that is a autonumber.

My problem is that if I do an execute() I have to add an imput for each fields including ClinicID.

So my question is How can I add a new record into my table without the obligation to enter ClinicID?

thanks

Everything's beautiful if you look at it long enough...

Re: Autonumber into Access

Chris Meech4-Apr-03 10:16

Chris Meech

4-Apr-03 10:16

Sorry if I'm not understanding correctly, but I think you need to try this

CString query = "insert into clinic
                   (   ClinicName
                      ,Address
                      ,City
                      ,PostalCode
                      ,PhoneNumb
                      ,FaxNumber
                      ,Email
                      ,Notes
                   ) values
                   (   'SomeClinicName'
                      ,'SomeAddress'
                      ,'SomeCity'
                      ,'SomePostalCode'
                      ,'SomePhoneNumb'
                      ,'SomeFaxNumber'
                      ,'SomeEmail'
                      ,'SomeNotes'
                   )";

Chris Meech

"what makes CP different is the people and sense of community, things people will only discover if they join up and join in." Christian Graus Nov 14, 2002.

"AAAAAAAAAHHHHHH!!!!! Those leaks are driving me crazy! How does one finds a memory leak in a garbage collected environment ??! Daniel Turini Nov. 2, 2002.

Re: Autonumber into Access

Waleed Eissa6-Apr-03 2:26

Waleed Eissa

6-Apr-03 2:26

Well, I understand that you use ADO to insert the new record. The following code is stripped from VB code generated by the data form wizard in VB (with some changes), if you look at the code you'll find that no value was entered for the AutoNumber field, so I assume that if you use the INSERT command without specifying a value for the AutoNumber field it should work too (try it on your own).

There is an AutoNumber field customer_id which doesn't appear in the code, the value of that field is assigned automatically.

' ASSUMING YOU ALREADY HAVE A CONNECTION OBJECT db
'

Dim strSQLQuery As String
strSQLQuery = "SELECT Customer.* FROM Customer"

Set tblCustomer = New ADODB.Recordset
tblCustomer.Open strSQLQuery, db, adOpenStatic, adLockOptimistic

tblCustomer.AddNew ' HERE YOU ADD A NEW RECORD

tblCustomer!customer_name = "John"
tblCustomer!district_code = 55
tblCustomer!industry_code = 17
tblCustomer!entity_type_code = 12
tblCustomer!branch_code = 5

f_tblCustomer.Update

(a.k.a. Wal2k)
www.wal2k.com

Beginner Question

Brent Corkum4-Apr-03 5:16

Brent Corkum

4-Apr-03 5:16

Here's my situation. I know nothing about database programming. I know lots about C++ and MFC and I use Visual C++ 6.0 but also have VS.NET 7.0. Someone has given me a machine with SQL Server and database on it. The database is not very big, maybe 1000 records. I want to get the database off the machine and I want to write an MFC application that will access the data and display it. I want to ship the application and I don't want people to have to have SQL or install anything extra to use the application. I'm sort of hoping that I can convert the database to some sort of file (maybe Access) and query it from MFC. The application would ship with the data and all reading and querying from the file would be local to the machine that the application is installed on.

How would people get started with this and does anyone have any ideas on how to proceed.

Thanks,
Brent

Brent Corkum

Re: Beginner Question

Anonymous4-Apr-03 5:58

Anonymous

4-Apr-03 5:58

with such few records you can as well export the data in some sort of flat-file (e.g CSV). Read the file at program start-up into memory as a balanced tree or double-linked list or into a list-control.
Whenever you use some sort of database, you got to deal with a lot of prerequisites (is the proper version of MDAC / ODBC etc. installed?), so this aproach should be easier.

dirk

SQL Server -> trigger -> event -> caught by C# program

zoltan_ie4-Apr-03 0:23

zoltan_ie

4-Apr-03 0:23

In SQL Server is it possible to have a trigger that will throw an event which could be caught by C# program running in the back ground?

Is this even possible? If it is, have u any links to help/examples?

Mystery of the Vanishing Field Data

J. Dunlap3-Apr-03 16:57

J. Dunlap

3-Apr-03 16:57

I'm querying an MS Access database table from an ASP page using ADO. My query returns a number of fields per row. The last few fields are null when I access them from the ASP page, even though in the database they are not.

I'm guessing that this is because:
1) the data in all the fields combined exceeds the row's buffer size.
or
2)the number of fields exceeds the maximum # allowed(unlikely, but possible)

Is one of these correct, and if so, is there any way to either (1) increase buffer size or (2) increase max # of fields?

Note: I have tried re-ordering the fields and always the last few fields are null but no others.

Primary Key

Anthony98873-Apr-03 15:49

Anthony9887

3-Apr-03 15:49

How can I check if the columns in the table has a primary key in ADO for vc++.

Thanks

how to access msde

ajaykalekar3-Apr-03 8:17

ajaykalekar

3-Apr-03 8:17

I am trying to connect to MSDE using vb.net for asp.net

constr="Server=AJAY-KALEKAR\VSdotNet;databaes=mydatabase;user=admin;password=admin"

It is not connecting gives error

AJAY-KALEKAR\ASPNET not valid user
I tried giving access to ASPNET user account the access to the database.

Do i have to log into my machine with ASPNET user account. Currently i login as administrator. Please let me know.

Thanks,
Ajay

Re: how to access msde

Alexander Kojevnikov4-Apr-03 5:09

Alexander Kojevnikov

4-Apr-03 5:09

MSDE supports only windows authentication, and does not support SQL Server authentication (user/password you passes in the connection string). As your ASP.NET page runs inside aspnet_wp.exe process, which runs under YOUR_DOMAIN\ASPNET account, you must grant it access to your database (either from osql.exe tool or from the EM).

Also, you misspelled the "database" key ("databaes").

HTH

Alexandre Kojevnikov
MCP (SQL2K, Win/C#)
Leuven, Belgium

Adding records:O)

MemLeak3-Apr-03 5:01

MemLeak

3-Apr-03 5:01

Hi,

I'm new to ADO and I would like to know how I can add a new record into my database.

Suppose that I have a structure that correspond to my database table named Clinic:

struct Clinic
{
CString name;
CString address;
CString PhoneNumber;
}

My connection and my recordset instance are already created and my connection to the database is opened.

What can I do up to now?

I have already tried:

m_pRS->GetFields()->GetItem("ClinicName")->PutValue(Clinic.name);
m_pRS->GetFields()->GetItem("Address")->PutValue(Clinic.address);
m_pRS->GetFields()->GetItem("PhoneNumber")->PutValue(Clinic.PhoneNumber);

but it doesn't work.

help needed!!;)

Regards

Everything's beautiful if you look at it long enough...

Re: Adding records:O)

Alexander Kojevnikov4-Apr-03 5:13

Alexander Kojevnikov

4-Apr-03 5:13

Try m_pRS->AddNew();

Alexandre Kojevnikov
MCP (SQL2K, Win/C#)
Leuven, Belgium

Empty fields

MemLeak3-Apr-03 2:36

MemLeak

3-Apr-03 2:36

Hi, I have some problem with ADO.

I use Access and MFC.

My problem is that I get an error when I try to get data from an empty field in a record. In my table I have FirstName, LastName and PhoneNumber. It's not necessary to put a value into PhoneNumber. So when I try to get the data, I have "invalide data type" error. All my data are CString type.

So I supposed that ADO cannot change something "empty" to CString...

Any ideas?

thanks

Everything's beautiful if you look at it long enough...

Everything's beautiful if you look at it long enough...

Large Field size (>64k)

Ryan Binns1-Apr-03 19:30

Ryan Binns

1-Apr-03 19:30

I'm looking at storing text in a MS Access database, but the amount of text may very easily exceed the 64k restriction on the "memo" data type. What would be the best way of implementing this? Yes, I know I could store the data in a text file and place a filename in the database, but I'd rather keep it all in the database if possible.

Any help would be greatly appreciated

Ryan

Being little and getting pushed around by big guys all my life I guess I compensate by pushing electrons and holes around. What a bully I am, but I do enjoy making subatomic particles hop at my bidding - Roger Wright (2^nd April 2003, The Lounge)

Re: Large Field size (>64k)

Hesham Amin1-Apr-03 22:35

Hesham Amin

1-Apr-03 22:35

Hi,,
try the Ole object type and deal with text as binary..
use AppendChunk function (ADO,DAO) to add text to field.

Database query

pradipta1-Apr-03 19:24

pradipta

1-Apr-03 19:24

I have table (in sql server database) which contains two fields ,Name(varchar) and Date_of_Birth(Datetime)
I want to fetch those records whose birth day is in this week (incuding today(system date)).
Please help me with the query

Re: Database query

Alexander Kojevnikov1-Apr-03 21:01

Alexander Kojevnikov

1-Apr-03 21:01

SELECT t.Name <br />
FROM MyTable AS t <br />
WHERE DATEPART(week,t.Date_of_Birth)=DATEPART(week,GETDATE())

Alexandre Kojevnikov
MCP (SQL2K, Win/C#)
Leuven, Belgium

Re: Database query

pradipta1-Apr-03 21:43

pradipta

1-Apr-03 21:43

thats nice ,but i want only those date of birth which is between today and 7 days after today(not before).
for example today date is 4/1/2003
i want to fetch those date of birth which is today and 7 days from today i.e
the records should be like below()
4/1/2003
4/2/2003
4/3/2003
4/4/2003
4/5/2003
.........

Re: Database query

Jeremy Oldham2-Apr-03 1:28

Jeremy Oldham

2-Apr-03 1:28

pradipta wrote:
but i want only those date of birth which is between today and 7 days after today(not before).

SELECT t.Name <br />
FROM MyTable AS t <br />
WHERE t.Date_of_Birth between GETDATE() AND DATEADD(d, 7, GETDATE())

You might need to manipulate this a bit. It will return exactly 7 days (7 days * 24 hours). The time element may throw the query off. For example, if the current system date/time is 04/02/2002 13:00:00 then a birthday with the following will not appear: 04/02/2002 1:00:00 and 04/02/2002 00:00:00.

Jeremy Oldham

Re: Database query

pradipta3-Apr-03 21:42

pradipta

3-Apr-03 21:42

i think U did not understand the problem.the query u have written only to retrive those DOB which will lie within this week.But DOB means only the month and day will be same whatever may be the year ,for exammple today is 4/4/2003 and DOB of a member 4/4/1975 then today is the birth day of that memeber.
so i want only the dob from database whose day and month will be equal to today's day and month and also those whose b'day will be within 7 days beyond today

just see the example

dob in the data base
DOB(mm/dd/yyyy)
4/4/1976
4/4/1970
4/6/1976
4/8/1999
1/1/1990
1/3/2000
------------
Today is -4/4/2003

i want the the data from database
4/4/1976
4/4/1970
4/6/1976
4/8/1999
because the above dobs will lie on this week

pradipta

Re: Database query

Jeremy Oldham4-Apr-03 1:46

Jeremy Oldham

4-Apr-03 1:46

pradipta wrote:
the query u have written only to retrive those DOB which will lie within this week.But DOB means only the month and day will be same whatever may be the year...

Sorry about that. Who knows where my mind has be drifting. Blush | :O

Try the following:

<br />
SELECT t.myName, cast(cast(Month(getdate()) as varchar(2)) + '/' + cast(Day(getdate())as varchar(2)) + '/' + cast(Year(getdate())as varchar(4)) as datetime), <br />
cast(cast(Month(t.DOB)as varchar(2)) + '/' + cast(Day(t.DOB)as varchar(2)) + '/' + cast(Year(getdate())as varchar(4)) as datetime)<br />
FROM DOB AS t <br />
WHERE datediff(d, <br />
cast(cast(Month(getdate()) as varchar(2)) + '/' + cast(Day(getdate())as varchar(2)) + '/' + cast(Year(getdate())as varchar(4)) as datetime), <br />
cast(cast(Month(t.DOB)as varchar(2)) + '/' + cast(Day(t.DOB)as varchar(2)) + '/' + cast(Year(getdate())as varchar(4)) as datetime)) Between 0 and 6 <br />
<br />

Let me know for sure that this works properly.

Jeremy Oldham

Re: Database query

Alexander Kojevnikov2-Apr-03 2:33

Alexander Kojevnikov

2-Apr-03 2:33

<br />
DECLARE @now AS datetime<br />
SET @now = GETDATE()<br />
DECLARE @today AS datetime<br />
SET @today = CAST(CONVERT(char(8), @now, 112) AS datetime)<br />
SELECT t.Name <br />
FROM MyTable AS t <br />
WHERE DATEDIFF(Day,@today,t.Date_of_Birth) BETWEEN 0 AND 6<br />

112 is ISO format: yyyyMMdd. SQL Server handles conversion from this format to datetime no matter which DATEFORMAT is active.

Alexandre Kojevnikov
MCP (SQL2K, Win/C#)
Leuven, Belgium

.Net portal

Madhuri Mittal1-Apr-03 12:18

Madhuri Mittal

1-Apr-03 12:18

Hello,

I had written a few articles for a major publishing
company which went out of business. Hence I decided to
publish them myself. Couple of articles are on Windows
form properties and one is on writing a DAL in C#. Check
them out:

http://dotnetuser.www2.dotnetplayground.com/

Madhuri Mittal

Re: .Net portal

Nick Parker1-Apr-03 12:26

Nick Parker

1-Apr-03 12:26

Madhuri Mittal wrote:
Check
them out:

Why not submit this as an article on CodeProject, then there is no need to cross-post. Smile | :)

Article Submission Wizard[^]

-Nick Parker

Creating MS Access Database?

BM_CSharp1-Apr-03 11:07

BM_CSharp

1-Apr-03 11:07

Hi, is there any way to create a MS Access database (.mdb) file using C# code and ADO.NET? I've done it in the past using ADOX (ADO Extensions). Does anybody know if I still have to use ADOX to do this?

Thanks,
Barry

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.