Creating folders from last_insert_id()

Hej people,

 

It is possible this question have been answered before but i haven't been able
to find a post. So here goes my question.

I have a SqlDataSource connected to a MySQL database, inserting one row into a
table with an auto_increment column. When a row is inserted i want to create a
folder on the webserver with same name as the newly inserted auto_increment
value.

I been trying to get the id into the Inserted event of the SqlDataSource,
perhaps with some thing like:

 

e.Command.Parameters["id"]

 But i dont know how to constuct a sql statement in MySQL that inserts a row and
return auto id to a Insert Parameter.

 

Any thoughts??

Stored procedures isn't an option!

Hi, add to the end of your SqlDataSource1 Insert command   

Select @NewId = SCOPE_IDENTITY().

 Like this:  

InsertCommand="INSERT INTO [tblaccounts] ([fname],[lname], [address],
[city], [state], [zip], ,[phone],[credits])
VALUES (@fName,@lname, @address, @city, @state, @zip, @email, @phone, @credits);
Select @NewId = SCOPE_IDENTITY()">


Then in your code behind you can get the value like so:  

Protected Sub SqlDataSource1_Inserted(ByVal sender As Object, ByVal e As
System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles
SqlDataSource1.Inserted
        'Get the Id of the newly inserted record
        NewId = e.Command.Parameters("@NewId").Value
    End Sub

 Then you can use the NewId parameter value to create your directory folder.

You may need to use LAST_INSERT_ID() instead for MySql database.

You should be able to double up:

insert into table values (1, 2, 3);
select last_insert_id() as LastInsertID 

 
If you're using the dataset gui in 2005, right click on the table adapter in
question, and add a new insert query.  Change the ExecutionMode or
Type(something like that :)  from NonQuery to Scalar.

smcoxon:Select @NewId = SCOPE_IDENTITY()

As far as i know this a MSSQL function/syntax, and therefor will not work with
MySQL.

 

/Anders

wordracr:select last_insert_id() as LastInsertID 

 

When adding that to the statment i get following error. Don't know what you mean
with if i'm using dataset gui in 2005, but i cant find anyting like
ExecutionMode or Type.
ERROR [23000] [MySQL][ODBC 3.51 Driver][mysqld-4.1.22-community-nt]You have an
error in your SQL syntax; check the manual that corresponds to your MySQL server
version for the right syntax to use near '; select last_insert_id() as
LstInsertID' at line 1