|
Hi,
I've got a table (whith an index on the CarId field (IdentityIncrement). Now I'd like to write a stored procedure which inserts a new record into this table and returns the CarId of the newly generated record. But I don't know how to do this w/o using a SELECT after the INSERT. Can anyone pls. help?
Thanks in advance.
Matthias
If eell I ,nust draw to your atenttion to het fakt that I can splel perfrectly well - i;ts my typeying that sukcs.
(Lounge/David Wulff)
www.emvoid.de
|
|
|
|
|
Create your stored procedure specifying which parameter(s) is/are output parameters like this
CREATE PROCEDURE GetInformation
(
@SomeKey int,
@SomeResult int OUTPUT
)
AS
SELECT @SomeResult = AnswerField
FROM MyTable
WHERE ThePrimaryKey = @SomeKey
GO
Then in your .NET application when you specify the parameters for the stored procedure, make sure you specify which parameter(s) is/are output.
SqlParameter someResult = new SqlParameter("@SomeKey", SqlDbType.Int);
someResult.Direction = ParameterDirection.Output;
When you have run your query, you can then get the value of the output parameter with
int result = (int)someResult.Value;
Does this help?
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
Not getting the response you want from a question asked in an online forum: How to Ask Questions the Smart Way!
|
|
|
|
|
After the insert @@identity will contain the new CarId value.
e.g.
CREATE PROCEDURE InsCar
@carType int
AS
BEGIN
insert into car ( CarType, AddDtTm ) values( @carType, GetDate() )
select @@identity as UID
END
GO
[EDIT]
... or, instead of 'select @@identity ...' specify an OUTPUT parameter as Colin mentioned.
[/EDIT]
...cmk
Save the whales - collect the whole set
|
|
|
|
|
hi all,
i want to run the below query on a Ms-Access database
CREATE TABLE products (
product_no integer PRIMARY KEY,
name text,
price numeric DEFAULT 9.99
);
when i run it, its seems that access doesnt like the DEFAULT 9.99 for some reason
however if i run the query in SQL Server it works fine, but i need to run it on
an access database
ne1 any ideas of a way round this ???
thanks
si
|
|
|
|
|
You can use the MS-Access table designer screen to set the column's default value.
If you want to do this programatically then try using the "Default" property in the ADOX Column Properties collection. The SQL syntax in MS-Access does not seem to support this.
Andy
|
|
|
|
|
You need to make Access Table/Queries compatiable with SQL ANSI.
Go to Tools -> Options -> Table/Queries Tab
Now, check the "This database" checkbox under "SQL Server compatiable syntax" option on bottom right side.
Now, you would be able to run this query.
Sanjay Sansanwal
www.sansanwal.com
|
|
|
|
|
Hiya I have a script that I want run when installing my app.
How do I do this??
Thanks.
|
|
|
|
|
|
Colin
You are a star!!
Cheers.
|
|
|
|
|
Hi !
I have a problem. I need to update my database from a xml dataset file but it doesn't work.
1)I fill my dataset with the dataAdapter.
2)I change some data in my datagrid
3)I save my dataset with de writeXml method. ( this part is ok)
4)I fill my dataset with the xml file, readXml (this part is ok)
5) Update ( It doesn't work. my update command is ok and I have no error)
I also tried the getChange method, and saved the result into a dataset saved in xml format. The modified rows(from the dataset created with the getChange method) are insert in the database (not update).
I need help 
Thanks
|
|
|
|
|
|
Thr problem with this is that you need to save the XML using a "DiffGram" model. By default, this is not used in the WriteXML and when you Save the XML file and Read it back in, the DataSet thinks there are no changes to be updated. However, one last Quirk, you also need to save the Schema into a different file as well, since you cannot save both the Schema AND the DiffGram into one file.
|
|
|
|
|
Hi
I have a dataset with one table (with 5 columns). Now, I want to extract the DISTINCT values of two columns (OrgID, OrgDesc) and bind it to a combo box.
How can I do that? Please advice. Thanks
PJ
Follow your goals, Means will follow you ---Gandhi---
|
|
|
|
|
Check
http://support.microsoft.com/default.aspx?scid=kb;EN-US;326176
Sanjay Sansanwal
www.sansanwal.com
|
|
|
|
|
thanks .. works great. 
Follow your goals, Means will follow you ---Gandhi---
|
|
|
|
|
So you would think a SQL expert like me *ok you didn't have to laugh so loud* would already know this, but I didn't and got burnt.
SmallDateTime rounds seconds to the nearest minute. Fair enough. I didn't know but hey, we have DateTime so lets change the column datatype to that.
Only existing records are not affected. WTF! They act like the column is still a SmallDateTime. Manually adding in a second to the value doesn't help existing records either alas.
So I convert to NVarchar as a temporary hack and use GetDate. Charming, still no seconds. WTF!
So my question is; apart from recreating the whole column and loosing that data, how do I get what was a former SmallDateTime and is now a DateTime to store seconds on existing rows?
I am sure this is one of those Design Features too.
regards,
Paul Watson
Bluegrass
South Africa
Christopher Duncan wrote:
"I always knew that somewhere deep inside that likable, Save the Whales kinda guy there lurked the heart of a troublemaker..."
Crikey! ain't life grand?
|
|
|
|
|
I never knew that either. Thanks for the heads up.
A proposal to getting it working might be to create a new column, copy the data to the new column, then remove the old column and finally change the name of the column. That way, at least you get to keep the data.
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
Not getting the response you want from a question asked in an online forum: How to Ask Questions the Smart Way!
|
|
|
|
|
I think you must be having a blond moment. The datetime type has been used by many people, and it works just fine. Perhaps your particular view of the data is not showing the seconds, but they most certainly are there.
my blog
|
|
|
|
|
Hi,
I tried to create my first stored function in SQL: I wrote this:
CREATE FUNCTION dbo.Function1<br />
(<br />
@Username varchar(50)<br />
)<br />
RETURNS TABLE<br />
AS<br />
RETURN ( SELECT * FROM Users WHERE Username = @Username)
and I wanted to retrieve all data from record in the database about this user.
I am absolute newbie in this, and I have this code to save data to the database:
Dim cmd As New SqlClient.SqlCommand("RegisterUser", vilemConn)<br />
cmd.CommandType = CommandType.StoredProcedure<br />
cmd.Parameters.Add("@Username", txtUsername.Text)<br />
cmd.Parameters.Add("@Password", inputPassword1.Value)
My question is: How can I get data from the database? (I want to save them to the structure.)
And the second question: When I tried this with Run inline function in VS.NET and when I set the value of @Username="blablabla" it got the result, but when I set this value to NULL it said
No rows affected.
No more results.
(0 row(s) returned), despite I have there (for testing purposes only, there will never be any NULL Username ) few NULL values?
|
|
|
|
|
Be careful of the differences between a "User Defined Function" (what you have here) and a "Stored Procedure". (There is no such thing as a "Stored Function")
User Defined Functions (UDF) are more restrictive and can only use deterministic statements and functions. They cannot change the state of the database either. However they can still be useful as they can be inserted into complex SELECT statements to reduce the complexity and make them easier to read. But there is a performance penalty for this as the query optimiser cannot optimise what is in the UDF with the rest of the SELECT statement.
If you want to use a UDF from .NET code you need to wrap it up in a SELECT like this:
SELECT * FROM dbo.Function1(@UserName)
As to your questions. The .NET code you've provided does not match with the SQL code above it, so I don't really know how to answer.
chodicimrkev wrote:
How can I get data from the database? (I want to save them to the structure.)
Also, I am not sure what you mean by "the structure". What structure? Do you want to read the data into a dataset, or your own objects or what?
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
Not getting the response you want from a question asked in an online forum: How to Ask Questions the Smart Way!
|
|
|
|
|
Thank you for your answer, you saved me for the second time When you help me with stored procedure last time, I decided to make more user-friendly interface - I decided to add possibility of editing of existing records - My idea was to make a function ReadUserData, which will be called in two cases: 1)During the editing 2)After successful registration to show all data again. So I created data structure that will be used to save of the data of edited user.
Private Structure UserData<br />
Dim strRecordID As Integer<br />
Dim strAllowed As Integer<br />
Dim strName As String<br />
Dim strSurname As String<br />
Dim strClass As String<br />
Dim strEmail As String<br />
Dim strUsername As String<br />
Dim strPassword As String<br />
Dim strSkin As String<br />
Dim strImagePath As String<br />
End Structure
What I want is to get the data from db to this structure, then I need to update it. I haven't a clue how to do it so I decided to try to make a function:
ALTER FUNCTION dbo.Function1<br />
(<br />
@Username varchar(50)<br />
)<br />
RETURNS TABLE<br />
AS<br />
RETURN ( SELECT * FROM Users WHERE Username = @Username)
Although I don't know how to retrieve data from db, so I was thinking about something like:
Dim cmd As New SqlClient.SqlCommand("RegisterUser", vilemConn)<br />
cmd.CommandType = CommandType.StoredProcedure<br />
cmd.Parameters.Add("@Username", txtUsername.Text)<br />
cmd.Parameters.Add("@Password", inputPassword1.Value)
but in the other way - to get the values of these parameters.
Thanks a lot
|
|
|
|
|
First, an optimisation tip. Although I'm thinking it may not be important in VB.NET as the ByVal and ByRef must be declared in method parameters - In C# classes are by reference by default and structures are by value by default. However, I still think the general rule is sound that a structure should always be small (no more than 16 bytes is one figure I've heard). So, for something like this a class would be better (in my opinion).
Looking at the structure you've given. I'm assuming that RecordId is the primary key of the table. If that is the case then UserName should have a unique constraint applied to it also to make sure that two users with the same UserName do not get inserted in the database.
Disclaimer: I've tried to write in VB.NET, which isn't my language - I've not done any VB since version 3, so please be aware that there may be errors in the syntax.
To get the data from the database create a stored procedure that is similar to this:
CREATE PROCEDURE GetUserDetails(@UserName varchar(64), @Password varchar(64))
AS
-- This should always return zero or one records only.
SELECT RecordId, Allowed, Name, Surname, Class, Email, UserName, Password, Skin, ImagePath
FROM Users
WHERE Username = @UserName
AND Password = @Password
GO Then in your .NET application some code like this:
Dim cmd As New SqlClient.SqlCommand("GetUserDetails", vilemConn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@Username", txtUsername.Text)
cmd.Parameters.Add("@Password", inputPassword1.Value)
' The data from the Select can be returned in to a DataSet or through a DataReader
' As you are populating your own structure it is probably better to use the DataReader
Dim dataReader As cmd.ExecuteDataReader()
Dim details As New UserData()
If dataReader.Read()
Then
' A record has returned from the Stored Procedure
' The numbers in the GetInt32 / GetString methods refer to
' the position of the field. So, care must be taken to SELECT
' the fields in the same order in the stored procedure.
details.strRecordId = dataReader.GetInt32(0)
details.strAllowed= dataReader.GetInt32(1)
details.strName = dataReader.GetString(2)
details.strSurname = dataReader.GetString(3)
details.strClass = dataReader.GetString(4)
details.strEmail = dataReader.GetString(5)
details.strUserName = dataReader.GetString(6)
details.strPassword = dataReader.GetString(7)
details.strSkin = dataReader.GetString(8)
details.strImagePath = dataReader.GetString(9)
Else
' There is no record, therefore the user name and password don't match
' TODO: Put code here that will handle the failed login details.
End If
As this is quite a long reply... I'll continue in part two (Inserting the data) in a little while...
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
Not getting the response you want from a question asked in an online forum: How to Ask Questions the Smart Way!
|
|
|
|
|
Okay - Now for part two: Inserting the data
I've abbreviated the code here, because it gets a little repetative, you should get the idea.
Create a stored procedure like this:
CREATE PROCEDURE UpdateUserDetails(@RecordID int, @Allowed int, @Name varchar(64), @Surname varchar(64),
@Class varchar(64), @Email varchar(64), @Username varchar(64), @Password varchar(64),
@Skin varchar(64), @ImagePath varchar(64))
AS
-- Check to make sure the data exists already to be updated.
IF EXISTS(SELECT * FROM Users WHERE UserName = @UserName AND Password = @Password)
BEGIN
-- Perform the update, all fields, except UserName and Password are updated here
UPDATE Users
SET Allowed = @Allowed, Name = @Name, Surname = @Surname -- and so on....
WHERE UserName = @UserName AND Password = @Password
END
ELSE
BEGIN
-- THere is no existing data to be updated, Raise and error - This will cause
-- a SqlException to be thrown in the .NET Application.
RAISERROR('The User does not exist', 16, 1);
-- Alternatively, an INSERT could be performed here.
END
GOIn the .NET application you would write some code similar to this:
Dim cmd As New SqlClient.SqlCommand("UpdateUserDetails", vilemConn)
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add("@Username", details.strUserName)
cmd.Parameters.Add("@Password", details.strPassword)
' Keep adding parameters until all the parameters that the stored procedure takes are in.
' For consistency the parameters should be in the same order they appear in the stored procedure
' definition.
A final note. RecordId appears to be the Primary Key for the Users table. It is not a good idea to update that. It can cause all sorts of problems with Foreign Key relationships and depending on how you have your SQL Server set up it would most likely cause an error.
Does this help?
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
Not getting the response you want from a question asked in an online forum: How to Ask Questions the Smart Way!
|
|
|
|
|
You're a database genius, again thank you very much. I really regard all your help and the time you devoted to it. I wouldn't be able to continue in this project without you.
The first part has just started working, I am going to try the second part (tomorrow, now I go to bed ). At the end of the development I will publish the whole project in Code Project.
Bye.
|
|
|
|
|
chodicimrkev wrote:
You're a database genius
Thanks. 
Although I would like to just say that I've worked for over 12 years with various database systems and I am a firm believer that pratice makes perfect.
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell
Not getting the response you want from a question asked in an online forum: How to Ask Questions the Smart Way!
|
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
