Get Auto Incrementing PK From MySql During Insert Using DataAdapter

[Auguy]

I'm having trouble retrieving the UserPK auto incrementing Identity back from MySql during the insert. I've tried a couple of things, but it confuses me and didn't work. Here is my Insert code

' Create the InsertCommand.
    command = New MySqlCommand( _
        "INSERT INTO UserID (UserID, FirstName, LastName, AllowLogin, UserPswrd " _
        & ", Notes, EditCount, IsActive)" _
        & " VALUES (@UserID, @FirstName, @LastName, @AllowLogin, @UserPswrd " _
         & ", @Notes, @EditCount, @IsActive)", connection)

    ' Add the parameters for the InsertCommand.
    command.Parameters.Add("@UserID", MySqlDbType.VarChar, 10, "UserID")
    command.Parameters.Add("@FirstName", MySqlDbType.VarChar, 15, "FirstName")
    command.Parameters.Add("@LastName", MySqlDbType.VarChar, 20, "LastName")
    command.Parameters.Add("@AllowLogin", MySqlDbType.Int24, 11, "AllowLogin")
    command.Parameters.Add("@UserPswrd", MySqlDbType.VarChar, 47, "UserPswrd")
    command.Parameters.Add("@Notes", MySqlDbType.VarChar, 500, "Notes")
    command.Parameters.Add("@EditCount", MySqlDbType.Int24, 11, "EditCount")
    command.Parameters.Add("@IsActive", MySqlDbType.Int16, 4, "IsActive")

    adapter.InsertCommand = command

What is the proper way to get the PK back from MySql without issuing a separate command?

Auguy
Sylvania/Toledo Ohio

 

[WomanPro]

Well I don't agree with the varchar type for the UserID, you mentioned that you want it to be auto increment, so it shouldn't be a string or char type, but integer. When you say varchar it recognizes '1' as a character, not 1 as an number. There is big diffrence between them. I think there is difference in ASCII code too.

See that example plese:
CREATE TABLE Persons
(
ID int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (ID)
)

INSERT INTO Persons (FirstName,LastName)
VALUES ('Lars','Monsen')

To insert a new record into the "Persons" table, we will NOT have to specify a value for the "ID" column (a unique value will be added automatically) because it is auto increment.

That means that in your example, you don't have to use that statement for ID
command.Parameters.Add("@UserID", MySqlDbType.VarChar, 10, "UserID")

and you have to go to the create table query to change type of ID field as I saw you above.

Hope it helps

 

[WomanPro]

However I would like to say too, that if UserID is your primary key, then you have to determine a field for UserName which would be a varchar type, but you have to understand, that are 2 different things. Username could be for example "WildRose" but UserID will start from 1 automatically, without you have to care about that.

 

[Auguy]

Sorry my Primary Key is not the UserID, it is UserPK. I did not show or indicate that. I understand I do not pass the primary key as I have done this many times with SQL using stored procedures that return the scope_identity. I need to get the UserPK back form MySql, that is what I'm struggling with. I should probably rename UserID to UserCode.

Auguy
Sylvania/Toledo Ohio

 

[jebenson]

Well, the easiest way to get the identity back is like so:

Dim SQLStr As String = "INSERT INTO UserID (UserID, FirstName, LastName, AllowLogin, UserPswrd " _
& ", Notes, EditCount, IsActive)" _
& " VALUES (@UserID, @FirstName, @LastName, @AllowLogin, @UserPswrd " _
& ", @Notes, @EditCount, @IsActive)[red]; Select LAST_INSERT_ID()[/red]"

Dim command As MySqlCommand = New MySqlCommand(SQLStr, connection)

' Add the parameters for the InsertCommand.
command.Parameters.Add("@UserID", MySqlDbType.VarChar, 10, "UserID")
command.Parameters.Add("@FirstName", MySqlDbType.VarChar, 15, "FirstName")
command.Parameters.Add("@LastName", MySqlDbType.VarChar, 20, "LastName")
command.Parameters.Add("@AllowLogin", MySqlDbType.Int24, 11, "AllowLogin")
command.Parameters.Add("@UserPswrd", MySqlDbType.VarChar, 47, "UserPswrd")
command.Parameters.Add("@Notes", MySqlDbType.VarChar, 500, "Notes")
command.Parameters.Add("@EditCount", MySqlDbType.Int24, 11, "EditCount")
command.Parameters.Add("@IsActive", MySqlDbType.Int16, 4, "IsActive")

Dim LastID As Integer = -9

LastID = CInt(command.ExecuteScalar())


However, I don't know how you can get this to work when the command is assigned to an adapter, like you have.



I used to rock and roll every night and party every day. Then it was every other day. Now I'm lucky if I can find 30 minutes a week in which to get funky. - Homer Simpson

Arrrr, mateys! Ye needs ta be preparin' yerselves fer Talk Like a Pirate Day!

 

[Auguy]

Thanks, I think I tried that, but I will try again. Maybe I didn't have the code just right.


Auguy
Sylvania/Toledo Ohio

 

[WomanPro]

Does this help?
INSERT INTO Customers (CustomerName, Country)
SELECT SupplierName, Country FROM Suppliers
WHERE Country='Germany';

 

[softhemc]

You could simply append to the insert statement a select statement that returns the keycolumn using a where clause that consists of the data supplied in the values clause. This will return the keycolumn's value for a record which exactly matches the record you have just inserted.

 

[Auguy]

Thanks to all that posted. I've been away for a few days and will try and get back to this shortly.

Auguy
Sylvania/Toledo Ohio