Database

Re: display data in crystal report

25-Jul-07 13:35

Stick to the C# or VB forum. Pick either one of those two and stick with the replies that come, if any.

"The clue train passed his station without stopping." - John Simmons / outlaw programmer

leckey25-Jul-07 14:50

leckey

25-Jul-07 14:50

he's a frequent cross poster and must be punished.

_____________________________________________
Flea Market! It's just like...it's just like...A MINI-MALL!

Re: display data in crystal report

Paul Conrad25-Jul-07 14:55

25-Jul-07 14:55

leckey wrote:
he's a frequent cross poster and must be punished

I am starting to get very cranky about these cross posts the past few weeks and idiotic questions, so it time to ...

[Emeril voice] Kick it up a notch [/Emeril voice] Laugh | :laugh:

"It's only that urgent if you have to pee.." - Dave Kreskowiak

Format Column

Shrita25-Jul-07 11:05

Shrita

25-Jul-07 11:05

How do I change one format column to another format?.Please any help is appreciated

Sabina

Re: Format Column

okkishore25-Jul-07 20:39

Xml and Xls as Data for SSRS [modified]

25-Jul-07 20:39

do mean changing one column datatype format to antoher datatype format
then

alter table tablename alter column columnname newdatatype

example:
sno is int then changing int to float
alter table student alter column sno float

Katusha260125-Jul-07 2:16

Katusha2601

25-Jul-07 2:16

Where from can I get my data faster for viewing it throw Report in SSRS: from xml or from Excel-file?

Anyone!!!!!!!!!!!!

problem in Take Data Offline Using Microsoft Synchronization Services for ADO.NET

Vikas Shirsath25-Jul-07 1:55

Vikas Shirsath

25-Jul-07 1:55

I trying to make my demo for
"Take Data Offline Using Microsoft Synchronization Services for ADO.NET"
I am using SQL Server 2005 on a server side and SQl Express on Client side, I have created my "Orcast" Database on both side ( Server as well as Client). but when I click on "Synchronize" but it will not work. In Demo they are used one "pub.sdf" file on client side , & I have created Database on client side ,they have given Path of that file , I have to give database connection string Like (its for client)
"Data Source=USIL-1;Initial Catalog=Orcast;Integrated Security=True";
it's urgent

Regards
Vikas Shirsath

stored procedure

Dhyanga25-Jul-07 1:48

Dhyanga

25-Jul-07 1:48

i have some errors while implementing stored procedure for sql server 2005 in my web form which i have design using vb dot net 2005. my code is like this:

i have made stored procedure like this:

ALTER PROCEDURE UpdateMemberInfo
@FName varchar(50),
@LName varchar(50),
@Password varchar(50),
@MemberName varchar(50)
AS
update T_Members set FirstName = @FName,LastName = @LName ,Password = @Password where MemberName = @MemberName

RETURN

Now i have to call this stored procedure in the click event of the button. So, i put the code like this in the button:

co.startconn()
cmd = New SqlCommand("UpdateMemberInfo", co.oConn)
cmd.CommandType = Data.CommandType.StoredProcedure
Try
para = New SqlParameter("@FName", fname)
cmd.Parameters.Add(para)

para = New SqlParameter("@LName", lname)
cmd.Parameters.Add(para)

para = New SqlParameter("@Password", pass)
cmd.Parameters.Add(para)

Catch
Response.Write(Err.Description)
End Try

Try
cmd.ExecuteNonQuery()
Catch
Response.Write(Err.Description)
End Try

But still there is error like this:
Procedure or function 'UpdateMemberInfo' expects parameter '@FName', which was not supplied.

suchitamanandhar@hotmail.com

Re: stored procedure

Pete O'Hanlon25-Jul-07 1:57

Pete O'Hanlon

25-Jul-07 1:57

You haven't set the SqlDbType or the size for any of your parameters. You must set them.

Plus, you haven't actually assigned any values to the parameters, and you haven't actually put any condition on your update statement so all rows will be affected.

Deja View - the feeling that you've seen this post before.

Re: stored procedure Ans for Que

Vikas Shirsath25-Jul-07 2:02

Vikas Shirsath

25-Jul-07 2:02

create command object cmd
Cmd.parameter.add("@FName",SqlDB.Nvarchar).value =fname(you value)
Cmd.parameter.add("@LName",SqlDB.Nvarchar).value =Lname(you value)
.
.
then execute you command
cmd.ExecuteNonQuery();

Regards
Vikas Shirsath

Simple Begin-Commit-Rollback Transaction

dabuskol25-Jul-07 1:37

dabuskol

25-Jul-07 1:37

Can somebody help to correct my SQL statement below with the correct transaction syntax?

	<br />
        BEGIN TRANSACTION<br />
<br />
        DECLARE @TMPKEY varchar(10) <br />
	DECLARE @NEWKEY varchar(10)<br />
	<br />
	SET @TMPKEY = '0707436'<br />
	SET @NEWKEY  = '070700058'<br />
	<br />
        Insert Into nnccdtrequestDetail<br />
		(Requestno,RequestItemNo,DetailStatus)<br />
	select @NEWKEY,RequestItemNo,DetailStatus from nnccdtrequestdetail<br />
	Where RequestNo = @TMPKEY<br />
	   <br />
	   IF @@TRANCOUNT  = 1<br />
	      COMMIT TRANSACTION<br />
	<br />
	   IF @@TRANCOUNT <> 1<br />
	     ROLLBACK TRANSACTION

Dabsukol

Re: Simple Begin-Commit-Rollback Transaction

Pete O'Hanlon25-Jul-07 1:53

Pete O'Hanlon

25-Jul-07 1:53

The second @@TRANCOUNT statement is a problem. Basically, the @@Trancount tells you the active number of connections that are present on the connection.

COMMIT TRANSACTION decrements the transaction count, so if you have 1 active transaction before the commit, you have 0 active transactions after. As you can see, this is where your problem occurs - you are testing @@TRANCOUNT <> 1 to call rollback transaction when it is now 0. In otherwords, the rollback will always be attempted.

I would be tempted to rewrite this section as:

Insert Into nnccdtrequestDetail
(Requestno,RequestItemNo,DetailStatus)
select @NEWKEY,RequestItemNo,DetailStatus from nnccdtrequestdetail
Where RequestNo = @TMPKEY

IF @@ERROR = 0
BEGIN
-- There is no error here.
IF @@TRANCOUNT = 1
BEGIN
COMMIT TRANSACTION
END
END
ELSE
BEGIN
-- If I reach this point, there has been an error.
IF @@TRANCOUNT = 1
BEGIN
ROLLBACK TRANSACTION 
END
END

Actually, I would normally not use the @@TRANCOUNT method because I match BEGIN TRANSACTION statements with the relevant closing transaction statements.

Deja View - the feeling that you've seen this post before.

Re: Simple Begin-Commit-Rollback Transaction

Mike Dimmick25-Jul-07 3:49

Mike Dimmick

25-Jul-07 3:49

@@TRANCOUNT is useful if you've got nested transactions, because ROLLBACK TRANSACTION does not roll back to the last BEGIN TRANSACTION but to the first BEGIN TRANSACTION. A nested COMMIT TRANSACTION only decrements @@TRANCOUNT, only when @@TRANCOUNT is reduced to zero by a COMMIT TRANSACTION does the overall transaction get committed.

This surprises a lot of people.

If you need to roll back in the middle of a nested procedure and an error has NOT occurred (presumably you've detected some other logical error, not a physical error raised by SQL Server) you should use RAISERROR to set @@ERROR to something. Maybe you test whether @@TRANCOUNT is greater than zero in an error handler, in case the nested procedure you called has already rolled back. IIRC, you get an error if you try to roll back when the nested transaction count is already zero.

Stability. What an interesting concept. -- Chris Maunder

Re: Simple Begin-Commit-Rollback Transaction [modified]

Pete O'Hanlon25-Jul-07 4:26

Pete O'Hanlon

25-Jul-07 4:26

You're right, and I know this, but there seems to be no indication in the OP of a nested transaction - hence my comments and the modified version of the code.

-- modified at 10:45 Wednesday 25th July, 2007

[Edit]Reading my original post I see that I did mention the transaction count of 1 before the commit transaction leading to a transaction count of 0 afterwards.[/Edit]

Deja View - the feeling that you've seen this post before.

[Message Deleted]

okkishore25-Jul-07 1:27

25-Jul-07 1:27

[Message Deleted]

originSH25-Jul-07 1:34

originSH

25-Jul-07 1:34

okkishore wrote:
how we will recover from database was lost or scatch.

It was deleted? Corrupted? The hard drive was thrown into a vat of acid?

okkishore wrote:
when there no is backrecovery

You mean you wern't keeping backups?

If you've completly lost the database and you have no backup then it's gone and your not getting it back. Maybe in future you'll do backups.

If it's not completly gone you might be able to use an undelete program or fix the corruption.

EDIT: I just checked your post below. Did you try and get rid of the duplicates without backing up and then delete the entire thing by accident? Laugh | :laugh:

originSH25-Jul-07 2:48

originSH

25-Jul-07 2:48

Well the wording you've used is complete nonsense.

Paul Conrad25-Jul-07 9:49

25-Jul-07 9:49

What is the OP asking? I understand that it was an interview question, but how was the database lost?

"The clue train passed his station without stopping." - John Simmons / outlaw programmer

Paul Conrad25-Jul-07 9:51

25-Jul-07 9:51

okkishore wrote:
how we will recover from database was lost or scatch

Huh? What kind of stupid interview question was that? Without backing up the database, losing it or having the disc being scratched is pretty much a nail in the coffin for the database, unless you have very pricey hardware to pull data bits off hard disk platters.

"The clue train passed his station without stopping." - John Simmons / outlaw programmer

deleting duplicate rows

okkishore25-Jul-07 1:13

Re: deleting duplicate rows

25-Jul-07 1:13

how can we delete duplicate rows when we are not using constraints
usings query only

kishore kumar.B

kubben25-Jul-07 2:01

kubben

25-Jul-07 2:01

Select all the field you are concerned about and do a group by with a having
Something like this:
Select col1, col2, col3, col4, col5 from yourTable
group by col1, col2, col3, col4, col5
having count(*) > 1

Hope that helps.
Ben

Re: deleting duplicate rows

okkishore25-Jul-07 2:13