Database

Counting the Records in a Database for a Insert Statement

2-Aug-07 7:21

Ok what programming language. If it's .NET then use the System.Net.Mail.SmtpClient to send the mail out.

Sunset Towers2-Aug-07 3:25

Re: Counting the Records in a Database for a Insert Statement

2-Aug-07 3:25

How can I make this below statement work for MSSQL 2005

UPDATE TABLE
SET FIELD2 = (SELECT COUNT(*) FROM TABLE)
WHERE FIELD1 = @Parameter

I'm not sure if it will work as is. I was sort of hoping there was an eval fuction, but I haven't found one.

kubben2-Aug-07 3:29

Re: Counting the Records in a Database for a Insert Statement

2-Aug-07 3:29

This should work:

Declare @cnt int
Select @cnt = count(*) from table

UPDATE TABLE 
SET FIELD2 = @cnt
WHERE FIELD1 = @Parameter

Ben

Sunset Towers2-Aug-07 3:37

Re: Counting the Records in a Database for a Insert Statement

2-Aug-07 3:37

Thanks that looks exactly like what I need to do.

Sunset Towers2-Aug-07 5:10

Re: Counting the Records in a Database for a Insert Statement

2-Aug-07 5:10

Ok Is there something I need to do to make it do these statements in a .NET application using a SqlCommand. Possible following each command set with the word GO

Declare @cnt int
GO
SELECT @cnt = COUNT(*) from TABLE
GO

or a \n

kubben2-Aug-07 5:22

Re: Counting the Records in a Database for a Insert Statement

2-Aug-07 5:22

It would be better to put the code into a stored procedure and call the procedure from the sqlCommand object.

Ben

kubben2-Aug-07 5:43

Re: Counting the Records in a Database for a Insert Statement

2-Aug-07 5:43

In general SQLcommands in .net don't like the hard core sql stuff. They just want a select, update, insert, delete or call of a stored procedure.

Ben

Sunset Towers2-Aug-07 6:01

Re: Counting the Records in a Database for a Insert Statement

2-Aug-07 6:01

Ok I maybe calling the

DECLARE @cnt int
Select @cnt = count(*) from PlaceInReferralLine

from more than one place so I'm assuming the best thing to do would be put just this code in
the stored procedure.

I'll give that a try, because the orginal code and the stuff I modified kept coming up with a SYNTAX ERROR near @cnt. Of course they couldn't atleast tell me which one was causing the problem from the debugger. Hehe.

Ok. While I am used to getting and retrieving data from different databases I'm still what I consider to be an extreme novice as I only know how to do the basic select, insert, etc.. stuff

So now for a few more questions

1. How do I get it to return the count from the store procedure.

CREATE PROCEDURE dbo.StoredProcedure1
DECLARE @cnt int
Select @cnt = count(*) from TABLE
RETURN @cnt <-I'm assuming

or maybe more simply

CREATE PROCEDURE dbo.StoredProcedure1
RETURN Select count(*) from TABLE

All this is being done in Visual Web Developer from MS.

2. Then I assume to get the data from the stored procedure

INSERT INTO TABLE
(col1, col2)
VALUES ('value1', dpo.StoredProcedure1)

kubben2-Aug-07 6:29

Re: Counting the Records in a Database for a Insert Statement

2-Aug-07 6:29

Ok, there are several ways of doing it. I would guess the easiest is to just return an output parameters. so your SP looks like:

CREATE PROCEDURE dbo.StoredProcedure1 
  @param1 int,
  @cnt int output
as
Select @cnt = count(*) from TABLE where column = @param1
RETURN 0
<pre>

So then when you call the stored procedure you will have to add two sqlparameters to your sqlcommand.  The @cnt needs to be marked as direction = output

After you call to executenonquery on the stored procedure you can then check the parameters and get the output value.  Then use this value when doing the insert or update.

You can't actually call a stored procedure in an insert statement.  
You can call a function, but there are other limitation with functions.

Hope that helps.
Ben

Sunset Towers2-Aug-07 7:18

Re: Counting the Records in a Database for a Insert Statement

2-Aug-07 7:18

Ok. I'm beginning to wonder if I shouldn't just do the entire set of procedures in a single stored procedure because I can't take the chance that during the UPDATE or INSERT operations that the record count will change. So maybe I should do something similar to the following.

<br />
create procedure dpo.StoredProcedure1<br />
        @cnt int = 0<br />
        @AccountID int<br />
        @NewAccountID int<br />
        AS<br />
        SELECT @cnt = Count(*) FROM PlaceInReferralLine<br />
        //Used to make sure this Prodcedure has not already been done      <br />
        SELECT ReferredBy FROM UserAccounts WHERE AccountID = @NewAccountID<br />
        //Some IF statement so that if the value of the result is a null value, empty    <br />
        //string processing continues otherwise it would return "0"<br />
        <br />
        //Select the account that is in top postition<br />
        SELECT AccountID FROM PlaceInReferralLine WHERE PlaceInReferralLine = 1<br />
        <br />
        //Set the NewAccountID's referer to AccountID<br />
        UPDATE UserAccounts SET ReferredBy = @AccountID WHERE AccountID = @NewAccountID<br />
<br />
        //Add the new account to PlaceInReferralLine<br />
        INSERT INTO PlaceInReferralLine (AccountID, PlaceInReferralLine) VALUES (@NewAccountId , @cnt)<br />
        //Subtract 1 from all AccountID's PlaceInReferralLine                <br />
        UPDATE PlaceInReferralLine SET PlaceInReferralLine = PlaceInReferralLine - 1<br />
                <br />
        //Move the Referer back to the back of the line.<br />
        UPDATE PlaceInReferralLine SET PlaceInReferralLine = @cnt WHERE PlaceInReferralLine = @AccountID<br />
RETURN 1<br />

RETURN 1 <- Intended for the entire procedure being successful. Which I guess for .NET to do this I would need to execute as a SqlCommand.ExecuteScalar() operation.

Sorry if there is any confusion with both a col and table name PlaceInReferralLine

kubben2-Aug-07 7:20

Re: Counting the Records in a Database for a Insert Statement

2-Aug-07 7:20

Yes that would be a good idea. Since you are going to just have one stored procedure you should remove the @cnt input parameter and add
Declare @cnt int
after the AS
and before the Select @cnt

You do not have to do an executeScaler to get a returncode. You always get a return code when executing a stored procedure. You can still use executenonquery.

Ben

Sunset Towers2-Aug-07 7:22

Re: Counting the Records in a Database for a Insert Statement

2-Aug-07 7:22

Ok now to just go and learn how to use the result sets so I can do my if statements.

kubben2-Aug-07 7:25

Re: Counting the Records in a Database for a Insert Statement

2-Aug-07 7:25

If you use executenonquery the returncode is the result of that call. So you don't need to worry about result sets.

Ben

Sunset Towers2-Aug-07 7:37

Re: Counting the Records in a Database for a Insert Statement

2-Aug-07 7:37

I made a second error with the parameters. The only one I will want to pass is the @NewAccountID. The @AccountID must be set by

SELECT AccountID FROM PlaceInReferralLine WHERE PlaceInReferralLine = 1

since there is no posibility of there everybeing more than one of the PlaceInReferralLine being equal to 1.

I also need to make sure

SELECT ReferredBy FROM UserAccounts WHERE AccountID = @NewAccountID

is empty or null before I continue to process. Otherwise the ReferredBy column could be changed.
I supposed I could to this at the program level rather than at the SQL Server level.

kubben2-Aug-07 7:51

Re: Counting the Records in a Database for a Insert Statement

2-Aug-07 7:51

I think I would do this as well in the sql stored procedure. It would be the most efficent there.

Ben

Sunset Towers2-Aug-07 8:24

Re: Counting the Records in a Database for a Insert Statement

2-Aug-07 8:24

Ok I've looked, I probably don't know exactly what I need so can you point me in teh right direction for information on how to use the results from the select statements directly within the stored procedure.

kubben2-Aug-07 8:28

Re: Counting the Records in a Database for a Insert Statement

2-Aug-07 8:28

There are several things you can do. I think you said you didn't want the record to exist. I think the best way to do that would be:

if (Select count(*) from table where column = @param) = 0
begin
end

If the record might exist, but the value for the field is null you can do:

 if (select isnull(column2,'') from table where column = @param) = ''
Begin

End

Ben

Sunset Towers2-Aug-07 9:35

Re: Counting the Records in a Database for a Insert Statement

2-Aug-07 9:35

Ok so how would I do something like

@AccountID = Select AccountID FROM Table where Col2 = 1;

kubben2-Aug-07 13:15

Transaction in dotNET and SqlServer

2-Aug-07 13:15

Declare @accountID int
select @accountid = accountid form table where col2 = 1

There are no ; in sql.

Ben

Developer6112-Aug-07 1:28

Developer611

2-Aug-07 1:28

Hi there .
During transaction is began , I inserted new data in table , until I don't call commit() or rollback() functions , another processes can not access to this table.
I mean, if i try to add a new data in a table with transaction and before closing transaction(commit or rollback) another process can't read that table; then a exception will occurred.

what is the problem?

DMASTER

Colin Angus Mackay2-Aug-07 1:38

Colin Angus Mackay

2-Aug-07 1:38

Developer611 wrote:
what is the problem?

There is no problem. A transaction shouldn't last long enough for there to be a problem. I'm guessing the other process times out waiting for the table to become available. And while it is in use in a transaction you have made it unavailable.

The solution is to make your transactions shorter.

Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...

"I wouldn't say boo to a goose. I'm not a coward, I just realise that it would be largely pointless."

My website

Developer6112-Aug-07 1:56

Developer611

2-Aug-07 1:56

Thanks dear Colin . But how can I do this in short time . Did you have any solutions ?

DMASTER

Colin Angus Mackay2-Aug-07 7:50

Colin Angus Mackay

2-Aug-07 7:50

Developer611 wrote:
But how can I do this in short time . Did you have any solutions ?

What the heck are you actually doing? A transaction should start, the query(ies) run and the transaction committed or rolledback. What else are you doing in there that make it take so long?!

If you start a transaction perform some queries, wait for user input, do some more queries then finally end the transaction then you have some serious redesigning to do.

SalarSoft3-Aug-07 21:50

SalarSoft

3-Aug-07 21:50

I have 5 or more queries that run in same time and one of queries take along time to done.
I have to do all of this queries in a transaction.
I have no way to do this in separate transactions.

I think transactions work with temporary tables that mean changes will apply to original tables after commit. This means that we can use original tables while a transaction is running!?! Confused | :confused: