Start off with a stored proc to your database work
CREATE PROC dbo.Personnel_Insert
@ID int OUTPUT,
@FirstName nvarchar(100),
@LastName nvarchar(100),
@Email nvarchar(100),
@Password binary(160)
AS
BEGIN
INSERT Personnel(FirstName, LastName, Email, Password)
VALUES (@FirstName, @LastName, @Email, @Password)
SET @ID = SCOPE_IDENTITY()
END
Then call it...
SqlConnection Conn = new SqlConnection(ConnectionString);
Conn.Open();
SqlCommand Command = new SqlCommand("Personnel_Insert", Conn);
Command.CommandType = CommandType.StoredProcedure;
//add in the ID parm, and tell SQL its output parm
SqlParameter parm = Command.Parameters.Add("@ID",SqlDbType.Int);
parm.Direction =ParameterDirection.InputOutput;
//add other parms
Command.Parameters.Add("@FirstName", SqlDbType.NVarChar, "Bob");
Command.Parameters.Add("@LastName", SqlDbType.NVarChar, "Roberts");
Command.Parameters.Add("@Email", SqlDbType.NVarChar, "[EMAIL PROTECTED]");
//note that pwd is a byte array, which is the hashed version of the password
Command.Parameters.Add("@Password", SqlDbType.VarBinary, pwd);
//execute it
Command.ExecuteNonQuery();
//Read in the new ID from the output parm
int ID = (int)Command.Parameters["@ID"];
That gets you through #1 and the first part of #2, repeat insert logic
for rest of #2
For #3...
Create a stored procedure that takes postcode as a parameter.
Create a command as above, set the parameter
But instead of executing the command directly, use a DataAdapter to to
fill a DataSet with your results, like so...
SqlDataAdapter da;
DataSet ds = new DataSet();
da = new SqlDataAdapter(Command);
da.Fill(ds);
You will find the results in the first (and only in this case)
DataTable in the DataSet.
DataTable Branches = ds.Tables[0];
You can use this to fill a list box or a grid or soemthing else to let
the user select a branch.
And for #4, repeat insert logic from step #1
Don't forget to use the System.Data.SqlClient namespace by including
this in your class
using System.Data.SqlClient;
Also, this all incredibly boring and repetitive code to write, you
will find it very useful to create a data access class that handles
all the database minutia (setting up the connection and command,
executing and filling) and includes things like transactions and error
handling.
On 7/1/05, Matthew Macdonald-Wallace <[EMAIL PROTECTED]> wrote:
> Hi all,
>
> I thought I'd introduce myself before I start begging for help!
>
> I'm just about to recommence a career in IT and am currently training
> to get the MCP.net/MCAD/MCSA/MSDBA qualifications. I have a linux
> background and am very keen to see how well I can integrate MS
> products and Open-source alternatives through the .net framework. My
> knowledge of C# and the ASP platform is no-where near as good as my
> skills in other languages, but there we go, that's life! :)
>
> My question is as follows:
>
> I'm currently trying to insert data into a number of tables in a MSSQL
> Server database. My current logic (and this is not yet in code!) goes
> something like this:
>
> 1. Insert the user's Firstname, lastname, emailaddress and password
> into the "personnel" table.
>
> 2. Extract the new users ID from the "personnel" table and use it to
> insert their postaladdress, phonenumber, fax number into the
> "personnelDetails" table.
>
> 3. using the postcode from the "personnelDetails" table, suggest a
> branchId (through a "SELECT * FROM branches WHERE branchPostCode LIKE
> %postcode%" kind of thing...)
>
> 4. add them to the selected branch.
>
>
> If anyone can help me get around this, that would be great. The main
> issue is with step 2 and extracting the person's ID.
>
> Thanks in Advance,
>
> Matt
>
>
>
>
>
>
>
> Yahoo! Groups Links
>
>
>
>
>
>
>
--
Dean Fiala
Very Practical Software, Inc
http://www.vpsw.com
Yahoo! Groups Links
<*> To visit your group on the web, go to:
http://groups.yahoo.com/group/AspNetAnyQuestionIsOk/
<*> To unsubscribe from this group, send an email to:
[EMAIL PROTECTED]
<*> Your use of Yahoo! Groups is subject to:
http://docs.yahoo.com/info/terms/
