Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 6 of 6
  1. #1
    New to the CF scene
    Join Date
    Sep 2005
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts

    auto increment using mysql & ASP.net

    Hello ALL,

    Need HELP with my coding. I am new to ASP and MYSQL. I am trying to get the value of my auto increment ID so I could pass it on to my other tables. I tried to use Last_Insert_ID but with no luck. I am not sure if I used it right? Also I am not sure if it's necessary for me to open and close my connection everytime that I have to do a query??? datagrid??? Any help would greatly be appreciated.

    here's what I have:

    Dim ConnStr As String = "Driver={MySQL ODBC 3.51 Driver};Server=localhost;Database=db;uid=user;pwd=pword;option=3
    Dim con As OdbcConnection = New OdbcConnection(ConnStr)
    Dim SQL as String = "INSERT INTO Table1(field2,field3,field4,....) Values ('"+field2+"',.......)
    Dim cmd As OdbcCommand = New OdbcCommand(SQL, con)
    con.Open()
    dgrTm.DataSource = cmd.ExecuteReader(CommandBehavior.CloseConnection Or CommandBehavior.SingleResult)
    dgrTm.DataBind()

    Dim SQL1 as String = "SELECT Last_Insert_ID() as mID from Table1"
    Dim cmd1 as OdbcCommand = New OdbcCommand(SQL1, con)
    con.Open()
    dgrTm.DataSource = cmd1.ExecuteReader(CommandBehavior.CloseConnection Or CommandBehavior.SingleResult)
    dgrTm.DataBind()

    Dim SQL2 as String = "INSERT INTO Table2(T2_ID, T2_Name, T2_Mid .....)
    VALUES ('"+mID+"', '"+name+"', .......)

    Dim cmd2 as OdbcCommand = New OdbcCommand(SQL2, con)
    con.Open()
    dgrTm.DataSource = cmd2.ExecuteReader(CommandBehavior.CloseConnection Or CommandBehavior.SingleResult)
    dgrTm.DataBind()

    Dim SQL3 as String = "INSERT INTO Table3(T3_ID, T3_Email, T3_Phone)
    VALUES ('"+mID+"', '"+email+"',.......)

    Dim cmd3 as OdbcCommand = New OdbcCommand(SQL3, con)
    con.Open()
    dgrTm.DataSource = cmd3.ExecuteReader(CommandBehavior.CloseConnection Or CommandBehavior.SingleResult)
    dgrTm.DataBind()


    Thanks!

  • #2
    Regular Coder
    Join Date
    Jun 2005
    Posts
    804
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You don't specify a table; just use SELECT LAST_INSERT_ID(); (the alias is ok, though). I don't know ASP, but you shouldn't have to close and reopen connections between queries (but be sure to call LAST_INSERT_ID() right after the INSERT query you want to check). There's probably an ASP function that will return the last id, too -- you may wish to use it instead.

  • #3
    New to the CF scene
    Join Date
    Sep 2005
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks for the reply. I've tried that already but It didn't work. I also tried using just one open() and Closed() statement but it's only taking my last query.

  • #4
    Senior Coder NancyJ's Avatar
    Join Date
    Feb 2005
    Location
    Bradford, UK
    Posts
    3,169
    Thanks
    19
    Thanked 65 Times in 64 Posts
    cant remember if I've ever used this on mysql but it works on sql server and it cant hurt
    "SELECT @@identity" returns the value of the identity field of the last record to be inserted.

  • #5
    Regular Coder
    Join Date
    Jun 2005
    Posts
    804
    Thanks
    0
    Thanked 0 Times in 0 Posts
    On the db side, you should be fine. I'll have to defer to someone else to make sure your ASP's ok, though. You might ask a mod to move this to the ASP forum.

  • #6
    New to the CF scene
    Join Date
    Sep 2005
    Posts
    3
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thanks nancy and Kid charming. I finally got it to work by using executescalar

    Dim ConnStr As String = "Driver={MySQL ODBC 3.51 Driver};Server=localhost;Database=db;uid=user;pwd=pwd;option=3"

    Dim com As New OdbcCommand
    com.Connection = New OdbcConnection(ConnStr)
    com.CommandText = "INSERT INTO Table1( f1, f2, ...) Values(.....)
    Dim memID As String
    com.Connection.Open()
    com.ExecuteNonQuery()
    com.CommandText = "SELECT Last_Insert_ID()"
    memID = CStr(com.ExecuteScalar())
    com.Connection.Close()

    ........


  •  

    Posting Permissions

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