SELECT LAST_INSERT_ID() in ExecuteScalar() returns 0 only RSS

• • Reply
  

  EdKaufmann

  Member

  270 Points

  245 Posts

  SELECT LAST_INSERT_ID() in ExecuteScalar() returns 0 only

  Apr 02, 2010 07:40 AM|LINK

  thanks for your time and hints

  my environment; W7 Ultimate, VS2008, MySQL 5.1.45, Connector .Net 6.2.2

  .aspx environment: a DropDownList (DDL) triggers a DetailsView (DV). All functions (select, edit, new ...etc) work fine.

  Now, in the same .aspx page I use Event "SQLDSDVKlient_Inserted" to get the Last inserted record ID using "ExecuteScalar" method! This to fill the DV with the just created record (see code below). Below code runs 'through' without any error msg, however:

  Problem: I always receive 0 (zero) in GetID = cmd.ExecuteScalar().

  When checking the DB the just created record using DVs NEW command has been added correctly! with a valid auto-incremented value in the ID field !

  ? What am I missing here!?!?

  Thanks for your time and help, ed

   

   Protected Sub SqlDSDVKlient_Inserted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlDSDVKlient.Inserted
          Dim connString As String
          Dim cnn As MySqlConnection
          Dim cmd As MySql.Data.MySqlClient.MySqlCommand
          Dim sql As String
          Dim GetID As Int16

          connString = "server=W7;User Id=root;password=xyz;Persist Security Info=True;database=slp;Allow User Variables=True;old guids = true"

          sql = "SELECT LAST_INSERT_ID()"

          cnn = New MySqlConnection(connString)

          cnn.Open()
          cmd = New MySql.Data.MySqlClient.MySqlCommand(sql, cnn)
          ' cmd.ExecuteNonQuery() ' ----> needed?
          GetID = cmd.ExecuteScalar()
          cmd.Dispose()
          cnn.Close()
          Response.Write(" ID last record " & GetID)

          SqlDSDVKlient.SelectParameters("ID").DefaultValue = GetID
          SqlDSDVKlient.DataBind()
          DVKlient.ChangeMode(DetailsViewMode.Edit)
          DVKlient.DataBind()
    End Sub

• • Reply
  

  Das.Sandeep

  Star

  10652 Points

  1897 Posts

  Re: SELECT LAST_INSERT_ID() in ExecuteScalar() returns 0 only

  Apr 02, 2010 08:08 AM|LINK

  Check your insert is entering itno database, use below :

  http://msdn.microsoft.com/en-us/library/system.data.sqlclient.sqlcommand.executescalar.aspx
  

  newProdID = Convert.ToInt32(cmd.ExecuteScalar())
  
  What is this sql = "SELECT LAST_INSERT_ID()"
  
  use : sql = "SELECT MAX(ID) FROM TABLE " or
   sql = "SELECT CAST(scope_identity() AS int)";

  
  

  Please let me know if you need more help.
  
  Please give us feedback no matter whether you get your answer.
  Please "Mark as Answer" if it's useful for you.
  
  Regards,
  
  Sandeep
  • Edited by Das.Sandeep on Apr 02, 2010 08:10 AM
  • Marked as answer by EdKaufmann on Apr 02, 2010 03:56 PM
• • Reply
  

  arefinn

  Contributor

  4992 Points

  750 Posts

  Re: SELECT LAST_INSERT_ID() in ExecuteScalar() returns 0 only

  Apr 02, 2010 08:09 AM|LINK

  Replace this line sql = "SELECT LAST_INSERT_ID()" with following lines

  sql = "SELECT @@IDENTITY"

  or

  sql = "SELECT SCOPE_IDENTITY()"

  “MARK AS ANSWER” IF IT HELPS YOU
• • Reply
  

  EdKaufmann

  Member

  270 Points

  245 Posts

  Re: SELECT LAST_INSERT_ID() in ExecuteScalar() returns 0 only

  Apr 02, 2010 03:55 PM|LINK

  Hi Sandeep, thanks for your time -- your suggestion works, some info

  This works just fine:         sql = "SELECT MAX(ID) FROM Client"

  What was this:   'sql = "SELECT LAST_INSERT_ID()" ; according to the MySQL doc it should bring the highest ID per connection!?

  Itested with both statements; both work!

         GetID = Convert.ToInt32(cmd.ExecuteScalar())
          GetID = cmd.ExecuteScalar()

  anyway thanks again for this ...... might be you can help also on the following. Of course now I would like to bring back, based on this last record ID, this very record in the DV?

  Normally the DDL triggers the appropriate record in the DV using the ControParameter. How do I simulate this using the GetID value? As you see I fill the SelectParameter with GetID BUT somhow there is no '''postback'''!

  I might open another thread but might be you have an idea!

  thanks again for your time, ed

  SqlDSDVKlient.SelectParameters("ID").DefaultValue = GetID
          SqlDSDVKlient.DataBind()
          DVKlient.DataBind()

  DropDownList DS:
  <asp:SqlDataSource ID="SqlDSKlient" runat="server"
              ConnectionString="<%$ ConnectionStrings:slpConnectionString %>"
              ProviderName="<%$ ConnectionStrings:slpConnectionString.ProviderName %>"
              SelectCommand="SELECT ID, Name, Vorname, Concat(Name, ' ',Vorname) AS VorNach, KlientNr, Geburtsdatum, Kanton, Ambulant_Stationaer, Diagnose, Andere_Stoerungen, Kostentraeger FROM client ORDER BY Name, Vorname">
          </asp:SqlDataSource>

  DetilsView DS:
  <asp:SqlDataSource ID="SqlDSDVKlient" runat="server"
              ConnectionString="<%$ ConnectionStrings:slpConnectionString %>"
              DeleteCommand="DELETE FROM client WHERE (ID = @ID)"
              InsertCommand="INSERT INTO client (ID, Name, Vorname, KlientNr, Geburtsdatum, Kanton, Ambulant_Stationaer, Diagnose, Andere_Stoerungen, Kostentraeger) VALUES (@ID, @Name, @Vorname, @KlientNr,@Geburtsdatum, @Kanton, @Ambulant_Stationaer, @Diagnose, @Andere_Stoerungen, @Kostentraeger)"
              ProviderName="<%$ ConnectionStrings:slpConnectionString.ProviderName %>"
              SelectCommand="SELECT ID, Name, Vorname, Concat(Vorname, ' ', Name) AS VorNach, KlientNr, Geburtsdatum, Kanton, Ambulant_Stationaer, Diagnose, Andere_Stoerungen, Kostentraeger FROM client WHERE (ID = @ID)"
              UpdateCommand="UPDATE client SET Name = @Name, Vorname = @Vorname, KlientNr = @KlientNr, Geburtsdatum = @Geburtsdatum, Kanton = @Kanton, Ambulant_Stationaer = @Ambulant_Stationaer, Diagnose = @Diagnose, Andere_Stoerungen = @Andere_Stoerungen, Kostentraeger = @Kostentraeger WHERE (ID = @ID)">
              <DeleteParameters>
                  <asp:Parameter Name="ID" Type="Int32" />
              </DeleteParameters>
              <SelectParameters>
                      <asp:ControlParameter ControlID="DDLKlient" Name="ID" PropertyName="SelectedValue" Type="Int32" />
              </SelectParameters>

  ........ etc

  • Marked as answer by EdKaufmann on Apr 03, 2010 06:56 AM