Adding Auto-Increment ID in both parent and child table

[Eric Dahlvang]

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

[myg l]

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

[Eric Dahlvang]

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

[myg l]

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

[Eric Dahlvang]

@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

[myg l]

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

[Eric Dahlvang]

It depends on where you are executing it from. If you call it from the SQL Query Analyzer, then: declare @iIdentity int exec InsertStaffAccount 'John Smith','Male','123 5th Street','CEO',@iIdentity out 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

[myg l]

yeah, I'm trying to call it from VB.NET is there any difference?

[Eric Dahlvang]

@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

[myg l]

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