Results 1 to 8 of 8
  1. #1
    Join Date
    Feb 2003
    Posts
    15

    Red face Unanswered: Retrieving the new record ID (MYSQL - ASP)

    Can any help with this little problem I'm generally used to working with ASP/SQLServer but need to use MYSQL this time.

    When I insert a new record I use the following method to retrieve the auto increment ID of that row.

    strSQL = Mytable
    strDB = My Database connection string

    Set rsData = Server.CreateObject("ADODB.Recordset")
    RSData.CursorLocation = 3 ' adUseClient
    RSData.Open strTable, strDB, adOpenKeySet, adLockOptimistic, adCmdTable

    RSData.addNew

    RSData("Company_Name") = "My new company"

    RSData.Update

    strNewCompany_ID = RSData("Company_ID")
    strNewCompany_Name = RSData("Company_Name")

    RSData.Close
    Set RSData = Nothing

    This method works fine with SQLServer but with MySQL it just retrieves the ID and Company name of the first row in the database.

    The new record is inserted fine - the db might be used by a high number of users so I don't just get the MAX ID number after a new insert.

    Thanks

  2. #2
    Join Date
    Feb 2003
    Posts
    17

    Re: Retrieving the new record ID (MYSQL - ASP)

    Originally posted by nmd
    Can any help with this little problem I'm generally used to working with ASP/SQLServer but need to use MYSQL this time.

    When I insert a new record I use the following method to retrieve the auto increment ID of that row.

    strSQL = Mytable
    strDB = My Database connection string

    Set rsData = Server.CreateObject("ADODB.Recordset")
    RSData.CursorLocation = 3 ' adUseClient
    RSData.Open strTable, strDB, adOpenKeySet, adLockOptimistic, adCmdTable

    RSData.addNew

    RSData("Company_Name") = "My new company"

    RSData.Update

    strNewCompany_ID = RSData("Company_ID")
    strNewCompany_Name = RSData("Company_Name")

    RSData.Close
    Set RSData = Nothing

    This method works fine with SQLServer but with MySQL it just retrieves the ID and Company name of the first row in the database.

    The new record is inserted fine - the db might be used by a high number of users so I don't just get the MAX ID number after a new insert.

    Thanks
    Check it out.
    http://www.mysql.com/doc/en/Miscella...functions.html
    LAST_INSERT_ID([expr])
    http://www.mysql.com/doc/en/mysql_insert_id.html
    MYSQ_INSERT_ID()

    Try running these after your query, to find the last auto_inc id generated.
    DD
    Last edited by progress; 02-06-03 at 19:26.

  3. #3
    Join Date
    Feb 2003
    Posts
    15

    Re: Retrieving the new record ID (MYSQL - ASP)

    Originally posted by progress
    Check it out.
    http://www.mysql.com/doc/en/Miscella...functions.html
    LAST_INSERT_ID([expr])
    http://www.mysql.com/doc/en/mysql_insert_id.html
    MYSQ_INSERT_ID()

    Try running these after your query, to find the last auto_inc id generated.
    DD
    I'll give these a go but can you help me work out why my previous method does not work.

    When I run the site off of my local IIS server with latest MySQL Drivers and still refering to the remote MySQL database the method works.

    But when I run the site remotely on an Apache Server (Chilisoft - ASP) where the MySQL DB is also located it does not work.

    Could this be related to the MySQL drivers on the Apache Server or Chilisoft ASP not being able to perform the function.

    Thanks for your previous advice....

  4. #4
    Join Date
    Feb 2003
    Posts
    17

    Re: Retrieving the new record ID (MYSQL - ASP)

    Originally posted by nmd
    I'll give these a go but can you help me work out why my previous method does not work.

    When I run the site off of my local IIS server with latest MySQL Drivers and still refering to the remote MySQL database the method works.

    But when I run the site remotely on an Apache Server (Chilisoft - ASP) where the MySQL DB is also located it does not work.

    Could this be related to the MySQL drivers on the Apache Server or Chilisoft ASP not being able to perform the function.

    Thanks for your previous advice....
    Check to see that you have privlages in MySQL with remote users. I believe that you have to have a root users with a host of % for remote access.
    ASP should be able to run this, along with Apache. I run Apache, PHP, and MySQL myself and have not had any problems. I will try and dig up some more ASP information here.
    DD

  5. #5
    Join Date
    Feb 2003
    Posts
    15

    Re: Retrieving the new record ID (MYSQL - ASP)

    Originally posted by progress
    Check to see that you have privlages in MySQL with remote users. I believe that you have to have a root users with a host of % for remote access.
    ASP should be able to run this, along with Apache. I run Apache, PHP, and MySQL myself and have not had any problems. I will try and dig up some more ASP information here.
    DD
    Thanks for your previous advice and untill I can find out where the problem is I'm going to use the following method:

    db = my connection string

    Conn.Open db

    Conn.Execute ("INSERT INTO Company_Details (Company_Name) VALUES('tester')")

    Set strCompany_ID = Conn.Execute("SELECT Company_ID FROM Company_Details WHERE Company_ID = last_insert_id()")

    Conn.Execute ("INSERT INTO Client_Transactions (Company_ID, Company_Name) VALUES(last_insert_id(), 'tester')")

    Set strTransaction_ID = Conn.Execute("SELECT Transaction_ID FROM Client_Transactions WHERE Transaction_ID = last_insert_id()")

    strNewCompany_ID = strCompany_ID("Company_ID")
    strNewTransaction_ID = strTransaction_ID("Transaction_ID")

    Conn.Close

    Can you see any problems that may occur from using this method where there may be a high number of users on the website.


    With regards to the MySQL user I am using it with host % - when I run the site from my own IIS server connecting to the remote MySQL DB and this user it works fine.

    It just when I run the site on the apache server using the same connection string same DB and user it just returns the first record in the table rather than holding its position at the record it has just inserted and returning that records ID.

    The MySQL driver on the apache server is 2.50.28.so and on my IIS server it is 2.50.39.00 could this be having an effect or maybe the ASP support of Chilisoft is causing a problem...

    ggrrrggrr deadlines

    Thanks again and any stuff you can find on ASP would be helpfull

  6. #6
    Join Date
    Feb 2003
    Posts
    17

    Re: Retrieving the new record ID (MYSQL - ASP)

    Originally posted by nmd
    Thanks for your previous advice and untill I can find out where the problem is I'm going to use the following method:

    db = my connection string

    Conn.Open db

    Conn.Execute ("INSERT INTO Company_Details (Company_Name) VALUES('tester')")

    Set strCompany_ID = Conn.Execute("SELECT Company_ID FROM Company_Details WHERE Company_ID = last_insert_id()")

    Conn.Execute ("INSERT INTO Client_Transactions (Company_ID, Company_Name) VALUES(last_insert_id(), 'tester')")

    Set strTransaction_ID = Conn.Execute("SELECT Transaction_ID FROM Client_Transactions WHERE Transaction_ID = last_insert_id()")

    strNewCompany_ID = strCompany_ID("Company_ID")
    strNewTransaction_ID = strTransaction_ID("Transaction_ID")

    Conn.Close

    Can you see any problems that may occur from using this method where there may be a high number of users on the website.


    With regards to the MySQL user I am using it with host % - when I run the site from my own IIS server connecting to the remote MySQL DB and this user it works fine.

    It just when I run the site on the apache server using the same connection string same DB and user it just returns the first record in the table rather than holding its position at the record it has just inserted and returning that records ID.

    The MySQL driver on the apache server is 2.50.28.so and on my IIS server it is 2.50.39.00 could this be having an effect or maybe the ASP support of Chilisoft is causing a problem...

    ggrrrggrr deadlines

    Thanks again and any stuff you can find on ASP would be helpfull
    Depending on what type of table you are using you could lock the the table during this transaction.

  7. #7
    Join Date
    Feb 2003
    Posts
    15

    Re: Retrieving the new record ID (MYSQL - ASP)

    Originally posted by progress
    Depending on what type of table you are using you could lock the the table during this transaction.
    I'm using the standard MyISAM table type - my intention for the table is for there to be quite alot of company records held, about 10,000 at the moment but when we allow companies to enter their own details on line this could increase quite alot.

    There could be times when there may be many companies trying to add their company( so a new row ) and when companies may want to update their details.

    So I need to be able to lock the table for inserts when a new record is added but still allow others to update their details at the same time.

    How would I go about locking the table during transactions with a adodb.connection but still allow updates of other records and which table type should I use (InnoDB, BDB) not had any experience of these.

    Thanks for helping with this..

  8. #8
    Join Date
    Feb 2003
    Posts
    17

    Re: Retrieving the new record ID (MYSQL - ASP)

    Originally posted by nmd
    I'm using the standard MyISAM table type - my intention for the table is for there to be quite alot of company records held, about 10,000 at the moment but when we allow companies to enter their own details on line this could increase quite alot.

    There could be times when there may be many companies trying to add their company( so a new row ) and when companies may want to update their details.

    So I need to be able to lock the table for inserts when a new record is added but still allow others to update their details at the same time.

    How would I go about locking the table during transactions with a adodb.connection but still allow updates of other records and which table type should I use (InnoDB, BDB) not had any experience of these.

    Thanks for helping with this..
    You would have to use a InnoDB table type. I am not as experience with ASP as I wish I was. Since I am imagining that each transaction the companies are going to be performing will be rather fast, locking the table and unlocking shouldn't be a problem.

    Check here for great information about it.

    http://www.mysql.com/doc/en/LOCK_TABLES.html

    In the company I work with we manage very high traffic databases, with Tables that hold 500,000,000 + records. The systems we have built are very fast and this is the strategy that we attack with.

    DD

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •