|
I ended up reading the record back . The quick and nasty way was by a table read , the better way is via a stored procedure
Create a stored procedure to insert and then return the autoint .At least you can handle concurrent ionserts in the SQL in the sproc which is better than in embedded SQL in an app.
|
|
|
|
|
If the table has a Primary Key that is set to autoincrement, all you need to do is:
INSERT INTO TABLE (COLUMN1, COLUMN2) VALUES (VALUE1, VALUE2); SELECT @@IDENTITY
This will return the ID of the last affected row.
|
|
|
|
|
I am also needing the primary key returned because I'm adding other dependent data to database.
My db is Access and I tried the "..SELECT @@ IDENTITY" to my command and the program threw an error. The Access db is in 2000 format. Any suggestions on getting this to work?
may your code be error free
|
|
|
|
|
Hi,
The above query is working fine. But how will I get the return value ? Shall I put as return @@identity?
Pls help.
Meeram395
|
|
|
|
|
A. This only works in SQL Server.
B. This is really bad practice. You shouldn't use @@Identity because there are problems with using it with triggers. Use Scope_Identity() instead.
Deja View - the feeling that you've seen this post before.
|
|
|
|
|
|
my application is based on just one independent table there's no relations ..
what's the best choise to build this database app.
i mean which db engine you recommend to use , SQL , access ,
i don't know , please help
many regards
|
|
|
|
|
Go for Access, i think its a better Option for you right now. but its nice to get familiar with SQL, because later maybe by any reason you might want to upgrade to relational Database System.So like that it wont be an effort to Change, what do you think
Vuyiswa
|
|
|
|
|
i have updated a wrong set of data in SQl, how do i restore it, would i restore them based on the Query that i have Done. PLease help.
here is the Update Statement
update property
set attrib_code = substring(attrib_code,1,6)+'19'+ substring(attrib_code,9,2)
where
substring(attrib_code,1,1)='1' and
substring(attrib_code,3,1)='1'
and substring(Attrib_code,9,2)='00'
I WAS sUPPOSED TO MAKE THE LAST PAIR 19
Vuyiswa
Vuyiswa
|
|
|
|
|
you might use triggers for the update operation and in that trigger using the inserted and deleted tables you can retsore the previous values if required.
Gautham
|
|
|
|
|
Hi to all,
I want to write storedprocedure which take Name or Year as Parameter and Display Records Order by Name or Year.
Note:From UI User may select Orderby Name or Year.
Mkanchha
|
|
|
|
|
We can't write this for you because we don't know what your database schema is. If this is the only two orders you may want, I'd write a proc for each, otherwise I believe you need to build your SQL as a string and execute it.
Christian Graus - Microsoft MVP - C++
Metal Musings - Rex and my new metal blog
"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )
|
|
|
|
|
Pass "1" or "2" to the database
and
using simple if statement sort accordingly.
Regards,
Arun Kumar.A
|
|
|
|
|
Or try this:
select *
from
tblName
order by
case
WHEN @parmaeter=1 then
Name
else
Year
end ASC
Regards,
Arun Kumar.A
|
|
|
|
|
Thanks for your Replay.
I get the SP it's comple successfully but while execute it giving the error.
THIS IS MY sp
-----------------
ALTER proc Test
(@iOrder char(1))
as
select * from tblabc
order by
case WHEN @iOrder='1' then vsName
else
nYOP
end ASC
--------------
EXEC TEST '1'
--------------------
Error:
Msg 8114, Level 16, State 5, Procedure Test, Line 4
Error converting data type varchar to numeric.
------------------------------------------------------
Please Tellme where I made Mistake
Mkanchha
|
|
|
|
|
Sorry , I have forgotten to tell you that the above statement will work only if
both the columns have same datatype.
try this:
select * from tabeName
order by
case when @a=1 then col1 end,
case when @a=2 then col2 end
Hope, this helps you.
Regards,
Arun Kumar.A
|
|
|
|
|
Thanks, Now it's working Perfect
MKanchha
|
|
|
|
|
hi guys'
I have repaired my windows xp and i can't reinstall the sql 2005 desktop engine. I have removed all previous installation folders.....but still having prob to install new instance...
can someone give me better solution...rather than formatting the xp...
|
|
|
|
|
Hello,
I was exploring user defined datatypes in sql server 2005. I tried making this type, a primary key but I am afraid that option is disabled.
Now is this not possible or are there any properties that needs to be set first ?
Looking for reply.
Regards,
Aims
|
|
|
|
|
I found the following on MSDN:
In SQL Server 2005 SP1, UDT fields must use native serialization or be persisted in order to be indexed.
and
You can create indexes on CLR user-defined type columns if the type supports binary ordering.
I don't really think that UDTs are really meant to be primary keys.
|
|
|
|
|
Hi chaps,
I've got a question about security. I understand that you don't want a user to be able to trash your database, so parameterised SQL / Stored procs are the sensible way of doing things. But i'm wondering more about permission based security, and at what locations it should be checked.
Lets say you have a web app, you would security check the following at the back end:
* View Record
* Perform Action
But what structure would you put in place to validate functionality on the Layer side of things and catch things further down the chain? Would this have to be performed on a per function basis? In which case, would it prohibit the use of ORM / Full Record Updating and merely refine the database access down to a rigid API?
Cheers
Tris
-------------------------------
Carrier Bags - 21st Century Tumbleweed.
|
|
|
|
|
Normally you have two choices when you connect to a sql database. Sql user and NT authenticated user. You can do both of these with a web app. Either way you should only have one user. It is in your best interest to have that one user only have rights to execute stored procedures. So if you want to select something, call a stored procedure. If you want to insert update delete something, call a stored procedure. That way if your security gets compromised you limit your exposure since that user can only execute stored procedures. It may seem like more work, but in the end it really makes things a lot more secure and you know exactly what your web site can and can't do since all the code is in the stored procedures.
I am not sure if that answered your question.
Ben
|
|
|
|
|
Hi Ben,
I'm more interested in providing a security saftey net in each layer of the application. My problem is that, using full table insert / updates, even with stored procs, the only place to reliably catch illegal incoming data (based on permissions), is on post back. In the event of a security loop hole, i'd like to have something that can catch it in the database layer, which would probably involve an ACL, in which case, IF a user has privelages for that particular record, how do you determine that they have for instance, unlocked a locked thread that should be locked.
Cheers
Tris
-------------------------------
Carrier Bags - 21st Century Tumbleweed.
|
|
|
|
|
Are we talking about database access here or have we moved on to a different subject? The most secure web app to sql access I can think of:
Create a new app pool for your web app.
Create a windows authenticated username and password.
Set the identity of the app pool to be that windows user.
Set your sql server to authenticate via windows authentication only, no sql users and passwords.
Only give stored procedure rigths to that windows user that the app pool runs as.
So at this point there isn't any way that anyone could get the password of that windows user since it isn't stored anywhere. It isn't in the connection string etc.
The web application runs as that identity, but it is just a normal windows user that only has access to stored procedures on the sql server. So at that point I don't know what else you want. It seems pretty secure to me, but then again perhaps I am missing something.
Ben
|
|
|
|
|
Hi Ben,
Thanks for the reply, but it wasn't a question on securing web apps in the traditional sense, but on a 3 tier architecture where the Database has its own permission table above and beyond an authenticated user.
The Web app would have its security checks, which it determines by querying the DBLayer security functions, then when invoking a function in the DBLayer it would internally query the security functions and if the permissions are not valid, raise an exception. That would make 2 security checks, as opposed to one.
This would however make it difficult to use full table access with raw CRUD, and instead limit any manipulation to fine granularity stored procs.
I was just wondering about the practicality of that.
Tris
-------------------------------
Carrier Bags - 21st Century Tumbleweed.
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
