Database

How Can I Use Pivot (sql server 2005)

11-Oct-06 3:50

Perhaps you might like to try a forum for the programming language you are using. This does not relate to SQL. It may relate to C++ or C# - you might have better luck in one of those forums.

Upcoming Scottish Developers events:
* UK Security Evangelists On Tour (2nd November, Edinburgh)
* Developer Day Scotland: are you interested in speaking or attending?
My: Website | Blog

Database Access help

eric_tran11-Oct-06 3:01

eric_tran

11-Oct-06 3:01

Good day everyone,

I'm doing a project with database MS Access in VB.Net 2005.

I'm struggling with the connection and coding with database.

Does anyone know where I can get a tutorial of how to connect to database and manipulate with data?

Thanks for your help in advance.

Eric

eric

eric

Re: Database Access help

Frank Kerrigan11-Oct-06 3:10

Frank Kerrigan

11-Oct-06 3:10

Look on the codeproject, i've heard there are loads there.

You want an OLE connection to an access db of the top of my head.

Blog Have I http:\\www.frankkerrigan.com

Re: Database Access help

eric_tran11-Oct-06 3:24

eric_tran

11-Oct-06 3:24

Thanks for your help

Eric

eric

SQL -Cursors

si_6910-Oct-06 23:42

si_69

10-Oct-06 23:42

Hi
i want to do something like this with a cursor, where the table name is passed to it

DECLARE tablecursor CURSOR FOR
select forename, surname, title1
from '+@mytable+'

OPEN tablecursor

FETCH NEXT FROM tablecursor into @forename, @surname, @title1
WHILE @@FETCH_STATUS = 0

BEGIN

-- do something etc

FETCH NEXT FROM tablecursor into @forename, @surname, @title1
END
CLOSE tablecursor
DEALLOCATE tablecursor

i guess i could do something like

set @string = 'select forename, surname, title1 from '+@mytable

exec (@string)

and then store this output in a temporary table, and get my cursor data from there

but that seems a long winded way about it, can any one advise on a better solution

thanks

si

Re: SQL -Cursors

Private_Void11-Oct-06 3:31

Private_Void

11-Oct-06 3:31

I think the way I did it is the way you described as "long winded". I went on a six month period of created dynamic stored procs. Sorry if this is no help but I did try. Frown | :(

There may be another way, but the only way I was able to accomplish this was;

DECLARE @myTable varchar(30)
DECLARE @NewTable TABLE(
firstname varchar(30)
,lastname varchar(30)
,title varchar(30))
DECLARE @Select VARCHAR(100)

SET @myTable = 'person.contact'

SET @Select = 'SELECT firstname, lastname, title FROM ' + @myTable

INSERT @NewTable (firstname, lastname, title)
EXEC (@Select)

DECLARE tablecursor CURSOR FOR
select firstname, lastname, title from @NewTable

hamidreza esmaeily10-Oct-06 22:07

hamidreza esmaeily

10-Oct-06 22:07

Why do not work correct select command with pivot in vitual stdio 2005?(I connected to sqlserver2005)

Re: How Can I Use Pivot (sql server 2005)

Colin Angus Mackay11-Oct-06 3:02

Re: some logic mistake...need [help]URGENT..help..

11-Oct-06 3:02

hamidreza esmaeily wrote:
Why do not work correct select command with pivot in vitual stdio 2005?(I connected to sqlserver2005)

Perhaps if you show us the statement it might help.

Upcoming Scottish Developers events:
* UK Security Evangelists On Tour (2nd November, Edinburgh)
* Developer Day Scotland: are you interested in speaking or attending?
My: Website | Blog

Frank Kerrigan11-Oct-06 2:53

Frank Kerrigan

11-Oct-06 2:53

Use a temp table to make things easier

select dept, vendor, salesvalue
into #temp
from mytable

select dept, Sum(salevalue)
from #temp
group by dept

you get the idea , I hope.

Blog Have I http:\\www.frankkerrigan.com

database problem

With_problem10-Oct-06 21:24

With_problem

10-Oct-06 21:24

I am using this code to store data in msaccess database but when i try to open the table it is not tthere
,and while opening the mdb file it will always ask for conversion if i click the convert database then also no table is present.
1)What should i chage in this code to open in msAccess2003 Can anybody help me to solve my problem?

2)For storing i have not done any odbc/oledb (DSN)connection is that needed.If yes how to do the connection?
3)Is this code is right?

<br />
#ifdef _DEBUG<br />
	afxDump<<"before opening tha datbase"<<"\n";<br />
#endif<br />
	db.Create("C:\\ODLDATA.mdb");<br />
	db.Open("C:\\ODLDATA.mdb");<br />
#ifdef _DEBUG<br />
	afxDump<<"after database is open"<<"\n";<br />
#endif<br />
	//db.Open("C:\\ODLDATA.mdb");<br />
	// start variable from here<br />
	COleVariant var;<br />
	var.ChangeType(VT_BSTR,NULL);<br />
	<br />
	CString ID,datetimestamp,marketno,bid,ask,marketstate,marketname,Sql;<br />
<br />
	recset.Open(AFX_DAO_USE_DEFAULT_TYPE,"SELECT * FROM T_Quote",NULL);<br />
     // ADD DATA IN DATABASE<br />
	Sql = "SELECT * FROM T_Quote";<br />
	recset.AddNew();<br />
	recset.SetFieldValue("ID","RequestId");<br />
	recset.SetFieldValue("DateTimeStamp","Timestamp");<br />
	recset.SetFieldValue("MarketNo","a");<br />
	recset.SetFieldValue("Bid","Bid");<br />
	recset.SetFieldValue("Ask","Ask");<br />
	recset.SetFieldValue("MarketName","Market");<br />
	recset.SetFieldValue("MarketState","d");<br />
	recset.Update();<br />
	recset.Close();<br />
	db.Close();<br />
    <br />
//database code end here<br />
<br />

How do I connect ASP.NET page to Lotus notes to send a mail ?

justprashant10-Oct-06 20:14

justprashant

10-Oct-06 20:14

Hi ,

I'm developing a web based system(ASP.NET with C#) ...where in I have to export the data in the database onto an excel sheet and mail using Lotus Notes ..

Can anyone help me with this ....... ?
Can I connect the .Net framework to Lotus Notes ?

Re: How do I connect ASP.NET page to Lotus notes to send a mail ?

Frank Kerrigan11-Oct-06 2:54

Frank Kerrigan

11-Oct-06 2:54

Use the SMTP server on the lotus notes server, which is the same as emailing to any SMTP server.

Blog Have I http:\\www.frankkerrigan.com

Re: How do I connect ASP.NET page to Lotus notes to send a mail ?

User 19428926-Oct-06 2:33

User 194289

26-Oct-06 2:33

Yes, there is an API that you can use. You need to install the Notes Client on your server, then create a Interop Wrapper for the dll that contains the API (I think it was nlsxbe.dll, but it has been a while)

Regards,

Wiebe Tijsma

------------------------------
spoon? what spoon?

http://www.netindustry.nl

modified 22-Nov-21 21:01pm.

movie CATALOGUE

flemmard10-Oct-06 17:17

10-Oct-06 17:17

Hi all,
i need to create a movie database catalogue .CATALOGUE of movies, actors and directors.
Since i have the basic knowledge of SQL , can anyone help me in this case ? It would be better if u guyz help me here ....How can i add photos to this database ??
thnx

Colin Angus Mackay11-Oct-06 3:00

11-Oct-06 3:00

flemmard wrote:
i need to create a movie database catalogue .CATALOGUE of movies, actors and directors.
Since i have the basic knowledge of SQL , can anyone help me in this case ?

Homework assignment? Think about what you want to store and then work out how to store it. For example: Actors are people. Directors are people. An actor can also be a director. Should you have a people table? An actor can appear in many films. A film has many actors. Should films and actors be joined somehow? If so, what type of join do you need? A director can direct many films. Are there any films with multiple directors? What type of join for directors to films? People have names. Films have names. Films have ratings (U, PG, 12, 15, 18 - or whatever it is in your country).

flemmard wrote:
How can i add photos to this database ??

You haven't mentioned WHAT database you are using.

Upcoming Scottish Developers events:
* UK Security Evangelists On Tour (2nd November, Edinburgh)
* Developer Day Scotland: are you interested in speaking or attending?
My: Website | Blog

flemmard11-Oct-06 14:45

11-Oct-06 14:45

thnx guyz....btw, i accpet itz a homework.....but still i m trying to know atleast something from u all as i juz started off with SQL....btw, i m totally confused with the idea , which u gave me ...can u explain with tables (diagram) plzzzz...and for the foto, wat do u mean by blob ?

i m using MYSQL for my homework ....

thnx again guyz

Colin Angus Mackay11-Oct-06 21:38

11-Oct-06 21:38

flemmard wrote:
thnx guyz btw itz i m u juz plzzzz foto wat

You know it takes more time to interpret what you are saying if you use this bizzare "txtspk". Please write in proper English, the majority of people here that can help you are professionals.

thnx = thanks
guyz = guys
btw = by the way,
itz = its
i m = I'm
u = you
juz = just
plzzzz = please
foto = photo
wat = what

flemmard wrote:
i accpet itz a homework

Then you'll have to actually learn it. If I just give you the answer then you don't learn. You learn by doing.

flemmard wrote:
an u explain with tables (diagram)

This forum does not offer the facility to draw diagrams. ASCII art is the best you can get.

flemmard wrote:
for the foto, wat do u mean by blob

BLOB is a standard abbreviation for Binary Large OBject. Many database systems have a column type known as BLOB, image, binary, varbinary etc. for this. Usually it means a column that is able to accept upto 2Gb per row.

Now, why don't you go back and try and work out a data model for your movie catalog. The rules are:

1. Each column must contain a single piece of information. e.g. A person's name should be split out into first name, middle names, surname, or an address should be split out so that Street, Town, County, Country, Postcode are in separate columns. NOTE: In case you are tempted, dates have their own column type and are regarded as a single piece of information - do not split out the day, month and year.

2. Each table has a primary key that, by itself, can uniquely identify the row. Typically, this means creating a column that increments on each insert. It is calledn an IDENTITY column in SQL Server - MYSQL may have another name for it.

3. All information in a row is dependent on the primary key. In other words, if you have a table representing a person then First name, Surname and Date of Birth are valid columns, Address columns are not and should be put in a different table because you can have more than one person staying at the same address. If you keep address in the same table as the person you will end up having the same address repeated several times in the one table. So, if you find you have the same information repeated in the table several times then it is a candidate for removal to a new table (or perhaps another existing table)

If you want help with your data model, you have to prove that you at least tried it. So, have a go at creating a data model and then post it here and we can help you on any problems you may be having, or we can point out areas that don't quite fit that you don't see yet. That way you will learn as you go rather than just accept a pre-done data model without really understanding it.

I hope this helps.

Upcoming Scottish Developers events:
* UK Security Evangelists On Tour (2nd November, Edinburgh)
* Developer Day Scotland: are you interested in speaking or attending?
My: Website | Blog

flemmard13-Oct-06 16:15

13-Oct-06 16:15

HI ,
Can you tell me the given relational schema and the SQL are correct ? Need i to add anything ??

Relational Schema<br />
<br />
This Relational Schema consists of 3 entities and 2 relations.<br />
We use natural attributes as the primary keys of each entities, and we assume the following attributes are unique within it's relational schema.<br />
Name of Actor;<br />
Name of Director;<br />
(Title, Year) of Movie.<br />
<br />
<br />
CREATE TABLE Movie (<br />
Title varchar(100), <br />
Year int(4), <br />
Genre varchar(100), <br />
Country varchar(100), <br />
Plot varchar(500), <br />
Poster varchar(1000)<br />
PRIMARY KEY (Title, Year));<br />
<br />
CREATE TABLE Actor (<br />
Name varchar (100) PRIMARY KEY, <br />
DOB date , <br />
Gender varchar(1),<br />
Nationality varchar(100));<br />
<br />
CREATE TABLE Director (<br />
Name varchar (100) PRIMARY KEY, <br />
DOB date, <br />
Gender varchar(1),<br />
Nationality varchar(100));<br />
 <br />
CREATE TABLE Act (<br />
Movie_title varchar(100), <br />
Movie_year int(4), <br />
Actor_name varchar(100), <br />
PRIMARY KEY (Movie_title, Movie_year, Actor_name),<br />
FOREIGN KEY ( Movie_title, Movie_year) References Movie(Name, Year),<br />
FOREIGN KEY (Actor_name) References Actor (Name));<br />
<br />
CREATE TABLE Direct (<br />
Movie_title varchar(100), <br />
Movie_year int(4), <br />
Director_name varchar(100), <br />
PRIMARY KEY (Movie_title, Movie_year, Director_name),<br />
FOREIGN KEY (Movie_title, Movie_year) References Movie(Name, Year),<br />
FOREIGN KEY (Director_name) References Director (Name));<br />
<br />

Colin Angus Mackay13-Oct-06 17:02

13-Oct-06 17:02

Here is my critique.

flemmard wrote:
We use natural attributes as the primary keys of each entities, and we assume the following attributes are unique within it's relational schema

As this is an academic exercise this is acceptable. However, in the real world "natural attributes" are often a poor choice unless they are numbers because keys should be as short as possible so that searches on the key waste as little time as possible pulling data off the disk. The use of variable length columns in a key is also prone to performance problems in a real world application.

The tables Act and Direct are poorly named. As this is an academic exercise this may not matter much. In the real world it is better to have the many-to-many intermediate tables named after their source tables (e.g. MovieActor) so that the schema is easier to maintain.

What happens if you have a film where the same person is a Director and an Actor? (e.g. Star Trek Insurrection. Jonathan Frakes is both the Director and the Actor playing Cmdr. Riker.) You may wish to normalise this better. (A tell-tale sign is that the structure of the Actor and Director table are the same) In a real world scenario it may be acceptable to denormalise the model somewhat for performance advantages, but maybe not for an academic exercise where correctness of model is more important than performance of model.

Country is a varchar(100) - There are roughly 200 countries in the world, the data in this column will be repeated a lot. (The same goes for Genre and Nationality) Consider refactoring this to a separate lookup table with a small-integer as the key. However, if you are having "natural attributes for keys" then there is not really any benefit - I mostly use counters, identity columns and Guids for keys.

Upcoming Scottish Developers events:
* UK Security Evangelists On Tour (2nd November, Edinburgh)
* Developer Day Scotland: are you interested in speaking or attending?
My: Website | Blog

flemmard13-Oct-06 17:19