Database

Re: Help on Crystal Report

9-Apr-07 3:36

sooreeagt wrote:
i want it in SQL...

All RDBMS support SQL. Did you mean Microsoft SQL Server?

If you meant SQL Server then there is no exact conversion to the format you want. Be aware that it is the responsibility of the Presentation layer to format data, not the database.

Upcoming events:
* Edinburgh: Web Security Conference Day for Windows Developers (12th April)
* Glasgow: Introduction to AJAX (2nd May), SQL Server, Mock Objects

Never write for other people. Write for yourself, because you have a passion for it. -- Marc Clifton

My website

Help on Crystal Report

kigali9-Apr-07 0:01

kigali

9-Apr-07 0:01

Hey to Crystal Report users
i have made report with crystal report viewer and crystal report tool but before i get the report wich one i can print.it is asking me the DATABASE password which i dont want the End user to know ?
Please help me

i just want the user to click on print button and get the report without the database authentication

ok
job

vivek-g9-Apr-07 1:34

vivek-g

9-Apr-07 1:34

ON button click.

-- 1. First make the object of the report (say AA.rpt)
Dim obj As New AA
-- 2. Pass the database (userid,pwd, server, Database name)
obj.SetDatabaseLogon("sa", "sa", "10.179.10.38", "PUBS")
-- 3. Use the object of crystal report viewer.
crViewer.ReportSource = obj
crViewer.Visible = True

vivek

Get all the tables in the database

ArchaBhandare8-Apr-07 22:08

ArchaBhandare

8-Apr-07 22:08

Is there a sql query which will help me get the names of all the tables in the database.

Colin Angus Mackay9-Apr-07 0:05

9-Apr-07 0:05

SELECT * FROM INFORMATION_SCHEMA.TABLES

Aaron VanWieren9-Apr-07 6:16

Aaron VanWieren

9-Apr-07 6:16

If you are doing it from code you could use the GetSchema("Databases") command off of the sqlconnection.

_____________________________________________________________________

Our developers never release
code. Rather, it tends to escape, pillaging the countryside all around.

The Enlightenment Project (paraphrased comment)

Visit Me at GISDevCafe

Balagurunathan S11-Apr-07 2:57

Balagurunathan S

11-Apr-07 2:57

Hi..

Try the following query..

select * from sysobjects where xtype='u'

Balaguru

sql comparison help needed

ArchaBhandare16-Apr-07 1:55

ArchaBhandare

16-Apr-07 1:55

Nothing is working. My db name is siebeldb and presently there is only 1 table in it, namely 'orders'. But knowing that siebeldb is the database, how do I get the names of the tables it contains. Also when I know a table, how do I get the fieldnames and fieldtypes in the table.

neodeaths8-Apr-07 21:21

Re: sql comparison help needed

8-Apr-07 21:21

izzit possible to do this?

(SELECT NAME FROM STAFF)//first statement
-

(SELECT NAME FROM HUMAN WHERE NAME LIKE 'A')//2nd statement

of coz this is not the actual statment but my staement is somewhat alike

i can generate a table of the infomation i dont need :
ABC
AAA

and i can also generate all the infomation

ABC
AAA
BBB

however what i need is :

BBB

Krish - KP8-Apr-07 21:58

Krish - KP

8-Apr-07 21:58

the query to be used is ....

SELECT name FROM staff WHERE name NOT IN (SELECT name FROM hanuman WHERE name LIKE 'A' )

Regards
KP

how to compare date in sql statement

neodeaths8-Apr-07 19:44

Re: how to compare date in sql statement

8-Apr-07 19:44

in my previous question i asked how to get date

so now i was wondering how can i compare date?

SELECT NAME, DATE_TIME
FROM STUFF
WHERE ()<-- not sure

do i use DATE_TIME > get_date()?

or something else please help

_mubashir8-Apr-07 21:17

_mubashir

8-Apr-07 21:17

Hi,
you can use the following WHERE clause,

MyDate = CONVERT(DATETIME, '2007-03-27 00:00:00', 102)
or
MyDate = getdate()

Mubashir

Every job is a self portrait of the person who did it.

Re: how to compare date in sql statement

Krish - KP8-Apr-07 21:55

Krish - KP

8-Apr-07 21:55

Data type date is not available in SQL server
however DateTime is available

so if table Stuff is updated with
INSERT INTO stuff VALUES('Mr. X', '01-Jan-2007')
INSERT INTO stuff VALUES('Mr. X', getdate())

and while fetching
SELECT * FROM staff WHERE date_time = getdate()
returns no rows i.e. both rows would not be returned. because getdate() returns date & time
In this case need to take only date protion and compare or compare day, month, year separately.

Regards
KP

Re: how to compare date in sql statement

Colin Angus Mackay9-Apr-07 0:10

transactions and transactionScope

9-Apr-07 0:10

Krish - KP wrote:
SELECT * FROM staff WHERE date_time = getdate()
returns no rows i.e. both rows would not be returned. because getdate() returns date & time

It returns no rows because all dates in the database occur in the past, therefore there is no match. It has nothing to do with the fact that GETDATE() returns the date and time.

If you only stored the date portion then the time portion will always be midnight. You can use that information to your benefit when writing calculation.

steve_rm8-Apr-07 16:45

steve_rm

8-Apr-07 16:45

Hello

VS 2005

I have been looking at transactions to rollback if 1 of my insert or updates does not complete successfully.

Code example:

<br />
dim ts as new transactionScope()<br />
<br />
'Insert a record into table A<br />
'Insert a record into table B <br />
'update table C<br />
ts.Complete()<br />

The above does not work too well with my code. I am wondering is there a better way to manage transactions.

Many thanks for any advice,

Steve

need help with special case when sql got no data

neodeaths8-Apr-07 15:34

Re: need help with special case when sql got no data

8-Apr-07 15:34

hi my questions is that in the case where my datasource have no result (becasue of the parameter there are no result) i want to route it to another sql statment is it possible??

my code below:

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:asdConnectionString1 %>"<br />
            <br />
            SelectCommand="SELECT DISTINCT AuctionSessionID, LocationID, Date_Time, AuctionDescription, Comments FROM AuctionSession WHERE (AuctionSessionID <> (SELECT AuctionSession FROM Registration WHERE (NRIC = @NRIC)))"<br />

Colin Angus Mackay8-Apr-07 16:09

Re: need help with special case when sql got no data

8-Apr-07 16:09

neodeaths wrote:
in the case where my datasource have no result (becasue of the parameter there are no result) i want to route it to another sql statment is it possible??

Yes, it is possible.

You might want to write it as a stored procedure - that way you only have one round trip to the database server and you always get a result set back (assuming, of course, that the fallback produced a result)

neodeaths8-Apr-07 17:49

Re: need help with special case when sql got no data

8-Apr-07 17:49

could you teach me how to do it without using the stored procedure way?

as i am asssumeing i have to put something inside my <asp:sqldatasource> tag

Colin Angus Mackay9-Apr-07 0:04

Re: store proc programming

9-Apr-07 0:04

Why are you so reluctant to use stored procedures? It is a better way.

store proc programming

coolzzzz8-Apr-07 2:05

coolzzzz

8-Apr-07 2:05

Hi,

I have a store procedure added to the database. I would like to try passing a value to the stored proc.

The store proc was as follow:

INSERT INTO DBName(Name, FName, LName, Salary, Active)
SELECT 'Mimic', FName, LName, SUM(Salary), 'A'
FROM DBInfo
WHERE TransactionDate=@TransDate

I would like to pass the 'Mimic' static data from the windows control and value was actually passed from the program itself to the stored proc. The store proc was actually used to insert into DBName table by specifying the Name with 'Mimic' value. My question was how do i implement passing the value from windows form textbox control to the stored proc and the data was inserted the *Name* static value into the database. Anybody can help regards on that?

Is that I have to write stored proc as following:

INSERT INTO DBName(Name, FName, LName, Salary, Active)
SELECT @Name, FName, LName, SUM(Salary), @Active
FROM DBInfo
WHERE TransactionDate=@TransDate

But the problem is that when i pass the data value of @Name & @Active, the program won't allow me to do like that. Is there any mwthods i can implement such of this functionality?

Steven

Colin Angus Mackay8-Apr-07 3:13