|
Blue_Boy wrote: select top 1 * from Customer order by CustID desc
if you use a simple select like this, you could get the wrong record when by many users at the same time, instead you can use:
select * from Customer where CustID = SCOPE_IDENTITY()
Alexei Rodriguez
|
|
|
|
|
@@IDENTITY returns last generated auto increment number
with which you can get the last custid inserted into the table.
Regards
KP
|
|
|
|
|
Krish - KP wrote: @@IDENTITY returns last generated auto increment number
Yes, but for what table? If you have a large multi user database handling many transactions at once there is no guarantee that it will return the identity of the last row inserted in the scope your running in. It might return the identity for some other code running on some other table that just happens to be running at the same time as your code.
You should use SCOPE_IDENTITY() to ensure that you get the identity of the last insert in your scope (and no one else's scope)
Upcoming events:
* Glasgow: Mock Objects, SQL Server CLR Integration, Reporting Services, db4o, Dependency Injection with Spring ...
* Reading: Developer Day 5
Ready to Give up - Your help will be much appreciated.
My website
|
|
|
|
|
Hi,
I want to create an user in sql server.
My program create a database, then it create a user for that database .
The problem is that I can't see the tables from the database with the user.
please help.
thanks.
|
|
|
|
|
Sounds like you need to give database reader role to your new user.
Ben
|
|
|
|
|
SQL Server has security. In order to be able to SELECT from tables, the user account in the database has to be granted permissions. Use the GRANT statement to set the permissions.
I normally make ordinary users members of the public role and limit that role to only being allowed to do the minimal set of operations that the application requires. By default, public has no permissions to do anything.
|
|
|
|
|
|
Hi all!
There is a question. i am facing difficulting in writting a query for selecting record in database.i am explaining whole scenerio of current problem:
i have a table StockDelta having attributes as follows:
stockDeltaID(primary key),
productID(forighn key from product table(productID, productName, totalQuantity etc) ),
Delta(the change in quantity of stock of a product, if the quantity increases the value of delta will be positive and if the quantity decreases value of delta will be negative),
isStockAdded(bool value indicating if the changed amount was basically the added stock), isSecondarySales(bool value indicating if the changed amount was basically the sencondary sales),
isDamages(bool value indicating if the changed amount was basically the damaged amount),
isFreeGifts(bool value indicating if the changed amount was basically the amount of freeGift)
now i have to generate a Stock report(using crystal reports), the report will take a parameter in case of daily report and two parameters in case of monthly report. the information that we want to show in report is as follows:
The columns of report should be
"Opening Stock"(representing quantity at start of month/day). we will get this column by Sum(delta) where date < pdate-1 //pdate is parameter date
"Stock Added"(representing quantity purchased during month/day), we will get this column by Sum( Delta) where date = pdate and isStockAdded = true
"Secondry Sales"(representing the sale out of a produt on a specific month/day), we will get this column by Sum(delta) where date = pdate and isSecondarySales = true
"Closing Stock"(representing the net quantity of a product in stock on a specific month/day), we'll get this column by Sum (delta) where date < pdate
"Damages"(from 1st of a month till a specific day or in a whole month), we get this column by quering Sum(delta) where date between 1st of month till pdate and isDamages = true
"FreeGifts"(representing the quantity of freegifts from 1st till that day of a month or for whole month). we will get this column by quering Sum(quantity of Freegifts) where date between 1st of month till pdate and isFreeGift = true
hope you people have grasped what i said.. i have tried my best to figure out query for this report but maybe at this student level i am unable to come with the right solution. i am stuck on it and request you people to please consider the problem if you get some time.
Thanks in advance
shaz jazz
|
|
|
|
|
Hi Shaz
I have done the SQL for the opening-balance and stock-added results:
SELECT SD.productId,
SUM(CASE
WHEN SD.deltaDate < @StartDate THEN SD.Delta
ELSE 0
END) AS OpeningStock,
SUM(CASE
WHEN SD.deltaDate >= @StartDate AND SD.isStockAdded = 1 THEN SD.Delta
ELSE 0
END) AS StockAdded
FROM StockDelta SD
WHERE deltaDate < @EndDate
GROUP BY productId
ORDER BY productId I'll let you figure out how to do the remaining columns.
The @StartDate and @EndDate bits refer to the parameters that you need to supply. The above should work for SQL-Server and Oracle. If you are using MS-Access then you will need to use the IIF function. An alternative approach would be to use sub-queries.
Regards
Andy
|
|
|
|
|
Hi,
I have a problem with a user.
I have the connection string:
MsSQL_conn1.ConnectionString = "Server = " & frmMain.txtMyServer.Text<br />
MsSQL_conn1.ConnectionString += "; User=RWIN" & "; Password=AVM" & "; Database =" & frmMain.txtMyDatabase.Text & ";Connect Timeout=30; Integrated Security = SSPI"
When I try to connect from my computer where I have installed the Sql Server it works.I can log on Sql Server with my user.
But when I try to connect with my program to another computer where is installed Sql Server I have the error:
Login failed for user 'LEXI-DEVELOP4\Guest'.
Please help.
Thanks
|
|
|
|
|
|
Can you please tell me about a query which I could use for the purpose of removing all the repetition(Repeated Records) in a table. I am trying with this one but its not working properly.
delete from EXCHANGE_STOCKS
where STOCK_ID not in (select distinct SYMBOL,EXCHANGE,(select top 1 STOCK_ID from EXCHANGE_STOCKS where SYMBOL = es.SYMBOL and EXCHANGE = es.EXCHANGE) as STOCK_ID
from EXCHANGE_STOCKS as es)
|
|
|
|
|
Add an Identity column to the table. for ex. colA
then execute this query
SELECT * FROM TblA a WHERE ColA < (SELECT Max(ColA) FROM TblA b WHERE b.Stock_ID = a.Stock_ID) ORDER BY a.Stock_ID
this will fetch all records except one from the duplicates
Regards
KP
|
|
|
|
|
How to authenticate the sql server
UNIQUE MUSKAN
|
|
|
|
|
what does it mean by authenticate the sql server?
Regards,
Sylvester G
sylvester_g_m@yahoo.com
|
|
|
|
|
You mean how do you authenticate a user using sql server authentication as aposed to windows authentication?
Or how do you authenticate against sql server?
Either way:
http://www.connectionstrings.com/[^]
|
|
|
|
|
i want insert like this text.mohan's.how can i insert this in sqlserver.plz send me.its urgent
|
|
|
|
|
use 'mohan'''s' and insert the value just play with apostrophee until the entire value becomes a single string!
Gautham
|
|
|
|
|
thank u .but how can i concate in asp.net for insert
|
|
|
|
|
refer to message below with subject
"Problem with special characters in SqlParameter"
similary usage is explained as what you are looking for,
in different ways ...
Regards
KP
|
|
|
|
|
if you are using asp.net with C# then try
TextBox1.Text.Replace("'","''");
I Love SQL
|
|
|
|
|
Please help
I want to create stored procedure using two tables &
Want to commit data of first table & rollback .
Thnx
CARe CAREER
|
|
|
|
|
are the DML operations are done one after the other in batch or jumbled
Regards
KP
|
|
|
|
|
I want to create a stored procedure in which while inserting or updating a record if a flag is set then i have to insert some other values in the parent table and based on the id which is auto generated by the parent table i have to insert that value into the child table How can i perform this.
I have got already two procedures for inserting and updating the data into the tables.
Best Regards,
M. J. Jaya Chitra
|
|
|
|
|
IF @flag = <value>
BEGIN
<insert data into master table>
<insert data into child table>
END
@@IDENTITY returns last generated value in Identity column.
Regards
KP
|
|
|
|