|
Hi!
Your stored proc could look like this:
create procedure AddNewPerson
@Name varchar(50)
as
set nocount on
insert into StaffAccountTable ( StaffName ) values ( @Name )
insert into Login ( StaffID, ... ) values ( @@identity, ... )
go
Hope this helps.
Regards,
Rainer
Rainer Stropek
Visit my blog at http://www.cubido.at/rainers
|
|
|
|
|
Hi Rainer,
thanks for the reply
but it seemed having some prob:
I had tried the stored procedure u had specify, but is generate error says that no null value can be inserted into the Login Table. Alright, I had set the StaffID in child table of Login AS Not Allow Null, I guess tis is the prob, but yet if i changed the setting to Set as Allow Null, the result the same- IT does not insert the auto-generated StaffID in (Staff Record)Parent Record to the (Login)child table.
Here is my Procedure
Create PROCEDURE InsertStaffAccount
@StaffName varchar(20),
@Gender char(10),
@Address varchar(50),
@Position varchar(20),
@Identity int OUT
AS
SET nocount On
INSERT INTO StaffAccount(StaffName,Gender,Address,Position) VALUES (@StaffName,@Gender,@Address,@Position)
INSERT INTO StaffLogin(StaffID,Password) VALUES (@@Identity,@Identity)
SET @Identity = SCOPE_IDENTITY()
go
any Idea??
|
|
|
|
|
It looks to me like the @Identity output parameter is the problem. It is NULL at the time that you insert into StaffLogin.
Are you trying to put a password into the StaffLogin table?
What value do you want the password to be?
----------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
yes, the password is one of the field in staffLogin table.
My intention is Once a new Staff Record is added, StaffID will be autogenerated and saved the new record in Staff Table.
In the same time, I wish the StaffLogin Table will be populated with the new StaffID as autogenerated in Staff Table and together with the password. Of course, since it is a new staff record, the Stafflogin table does not have the value of staffID and the password yet, it is added as a new record only in the same time as New Staff Record is added in the Staff Account. How is this possible?
The value of the password for the initial add record purpose is similar as the staffID which is auto-generated in the Staff Table, then i will add code to allow user to change the vaue of password on their first login.How is this possible?
thanks again
best regard
amy
|
|
|
|
|
CREATE PROCEDURE InsertStaffAccount
@StaffName VARCHAR(20),
@Gender CHAR(10),
@Address VARCHAR(50),
@Position VARCHAR(20),
@Identity INT OUT
AS
SET NOCOUNT ON
INSERT INTO StaffAccount(StaffName,Gender,Address,Position) VALUES (@StaffName,@Gender,@Address,@Position)
SET @Identity = SCOPE_IDENTITY()
INSERT INTO StaffLogin(StaffID,Password) VALUES (@Identity ,str(@Identity))
GO
----------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Hie EricDV
Thanks alot for the help. It really workos. The auto-generated staffID does populate the Login Table. But there is one weird thing, why the paswword seemed to have extra space infront of the StaffID? Eg. *****10011 * represent the space why is it so?
how to eliminate the extra space in front of the password??
thanks
best regard
amygal
|
|
|
|
|
LTRIM(STR(@Identity))
You're welcome. Isn't this fun?
----------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
hey EricDV
It's really cool, =) juz a simple line of code. very bravo of u
Everything working fine now exept there is still some small msg box pop up.
A msgbox pop up with " Procedure or function 'InsertStaffAccount expect parameter '@staffName' which was not supplied. Is that a bug?
how to make it dissapear as evryhign seemed to work fine now
thanks again
very kind and helpful of u
=)
best regards
amygal
|
|
|
|
|
@myg@l wrote: A msgbox pop up with " Procedure or function 'InsertStaffAccount expect parameter '@staffName' which was not supplied. Is that a bug?
Yes. a few posts back, you defined your stored proc with these parameters:
@StaffName varchar(20),
@Gender char(10),
@Address varchar(50),
@Position varchar(20),
@Identity int OUT
You need to supply them when you call the procedure.
----------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
hie again
=)
yea i had added the stored procedure with the parameters above, but i cant get u bout to supply them when i call the procedure
any example??
*_*
amygal
|
|
|
|
|
It depends on where you are executing it from. If you call it from the SQL Query Analyzer, then:
declare @iIdentity int<br />
<br />
exec InsertStaffAccount 'John Smith','Male','123 5th Street','CEO',@iIdentity out<br />
<br />
print @iIdentity
Are you trying to call it from C++,C#,VB.Net,ASP.NET???
----------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
yeah, I'm trying to call it from VB.NET is there any difference?
|
|
|
|
|
@myg@l wrote: yeah, I'm trying to call it from VB.NET is there any difference?
Not much...
Dim conn As New SqlConnection
conn.ConnectionString = "Data Source=(local);" & _
"Initial Catalog=mytestdb;" & _
"Integrated Security=SSPI"
Dim cmd As New SqlCommand
cmd.Connection = conn
cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "InsertStaffAccount"
Dim prm1 As New SqlParameter("@StaffName", SqlDbType.VarChar, 20)
prm1.Direction = ParameterDirection.Input
cmd.Parameters.Add(prm1)
prm1.Value = "MyStaffName"
Dim prm2 As New SqlParameter("@Gender", SqlDbType.Char, 10)
prm2.Direction = ParameterDirection.Input
cmd.Parameters.Add(prm2)
prm2.Value = "MyGender"
Dim prm3 As New SqlParameter("@Address", SqlDbType.VarChar, 50)
prm3.Direction = ParameterDirection.Input
cmd.Parameters.Add(prm3)
prm3.Value = "MyAddress"
Dim prm4 As New SqlParameter("@Position", SqlDbType.VarChar, 20)
prm4.Direction = ParameterDirection.Input
cmd.Parameters.Add(prm4)
prm4.Value = "MyPosition"
Dim prm5 As New SqlParameter("@Identity", SqlDbType.VarChar, 20)
prm5.Direction = ParameterDirection.Output
cmd.Parameters.Add(prm5)
conn.Open()
cmd.ExecuteNonQuery()
conn.Close()
MsgBox("Returned ID=" + prm5.Value)
--EricDV Sig---------
Some problems are so complex that you have to be highly intelligent and well informed just to be undecided about them.
- Laurence J. Peters
|
|
|
|
|
Hey Eric, I thankss alot. It works perfectly.....Thanks for the hessle all the way. It is rather fun to try out !!
tc and hav a nice day
see ya around
=)
best regards
amygal
|
|
|
|
|
sir i want to learn xml i am familier with ado.net and sql server please guide me with starting stage i am totaly known with xml please help me
|
|
|
|
|
|
If you just want a general introduction into XML and it's related technologies check out W3Schools.com[^]
|
|
|
|
|
i use this code to store image in my database
*********************
Dim DR As DataRow
DR = Ds_products1.employees_dir_sp.NewRow
SqlInsertCommand1.Parameters("@emp_fname").Value = txtfname.Text
SqlInsertCommand1.Parameters("@emp_mname").Value = txtmname.Text
SqlInsertCommand1.Parameters("@emp_photo").Value = PictureBox1.Image
DR("emp_fname") = SqlInsertCommand1.Parameters("@emp_fname").Value
DR("emp_mname") = SqlInsertCommand1.Parameters("@emp_mname").Value
DR("emp_photo") = SqlInsertCommand1.Parameters("@emp_photo").Value
Ds_products1.employees_dir_sp.Rows.Add(DR)
da_empdir.Update(Ds_products1.employees_dir_sp)
************************************************
so when i click the save button that message apeear
"object must implement iconvertible"
ma_refay
|
|
|
|
|
|
Hi there,
Is it possible to get the last value updated in a table after an update operation has been conducted
Any help would be greatly appreciated
leo
Kathmandu, Nepal
|
|
|
|
|
Begin tran
update statement.....
.....
rollback tran
vivek
|
|
|
|
|
anj1983 wrote: Is it possible to get the last value updated in a table after an update operation has been conducted
A column of type TIMESTAMP can be used for it, because each time when a row is modified, the timestamp's value get chnged. Moreover it stores value in binary number. Alternately you can use a column of type DATETIME, and programmatically modifiy its value when updation takes place. Using this technique you trace recent update operation.
Regards.
_____________________________
Success is not something to wait for, its something to work for.
|
|
|
|
|
I'm having a problem pulling data from sql 2005 on my local machine in an asp.net 2 project. I've set up sql with windows authentication, and i've tried adding logins with sql authorization and rights etc. but to no avail. The connection finds the server, db etc. but gives an security/auth error when executing the query. Do I have to reinstall sql or what???
Regards,
Tintin
|
|
|
|
|
Hi!
You have to set your SQL 2005 service to mixed mode authentication (do that in the property window of your server in management studio: Right-click your server in object explorer, Properties, Security, SQL Server and Windows Authentication mode).
If you do not change to mixed mode you cannot use sql authentication.
Regards,
Rainer.
Rainer Stropek
Visit my blog at http://www.cubido.at/rainers
|
|
|
|
|
Hi ,
pls help me out with this code
insert into chk values('notwking',convert(datetime,getdate(),101))
the row gets inserted into the table(type varchar, type datetime).
but if i try to retrive that record by this query
select * from chk where dt between getdate() and getdate()+20
i dont get that tuple
but i get the same with this query
select * from chk where dt between getdate()-1 and getdate()+20
i thnk the problem is with the time part which is inserted during insertion.
is there any solution.Pls help me out
Thnks in advance
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
