locked
Save to database + get ID of latest input.. RRS feed

  • Question

  • User513620916 posted

    Well, hello again..

    My problem can be summed up to the title of the topic..

    I can't seem to get the ID from the latest input, with
    the following code:

    private Boolean saveToDatabase(String pTable, int pTotalPrice, params String[] pInformation)
    {
            ...
    
            String connectString = WebConfigurationManager.ConnectionStrings["connect"].ConnectionString;
            MySqlConnection myConnection = new MySqlConnection(connectString);
            MySqlCommand myCommand = new MySqlCommand(sqlStatement, myConnection);
    
            for (int i = 0; i < mAttributes.Count; i++)
                myCommand.Parameters.AddWithValue("@" + Convert.ToString(mAttributes[i]), pInformation[i + 2]);
    
            try
            {
                using (myConnection)
                {
                    myConnection.Open();
                    myCommand.ExecuteNonQuery();
                    mCustomerID = (int)myCommand.ExecuteScalar();
                }
            }
            catch (MySqlException pMySqlException)
            {
                //Do Nothing for now...
            }
    
            if (mCustomerID != -1)
                return true;
            else
                return false;
    }


    The problem isnt in the sqlStatement, it's written in the way it's suppose to be.. My problem lies at the following:

    mCustomerID = (int)myCommand.ExecuteScalar();

    Anyone have any ideas?

    Best regards
    Richard

    Sunday, January 2, 2011 1:40 PM

Answers

  • User2050872319 posted

    Hello Richard,

    Well, obviously without actual sql statements it's quite difficult to figure out what is supposed to happen.

    But I can see a few issues straight away.

    1. Parameter marker in mysql is not @ sign but a ? mark instead.

    2. It seems that you try to execute a single sql query in 2 different ways (not sure what it's for). If your sql statement was an insert and you need to get autoincrement value, then you'd better use myCommand.InsertId property.

    I.e.

       
    using (myConnection)  
    { 
          myConnection.Open();  
          myCommand.ExecuteNonQuery();  
          mCustomerID = myCommand.InsertId;  
    }  
    

    Hope this helps

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, January 2, 2011 3:39 PM

All replies

  • User1843221445 posted

    what does the method returns ? true or false ?

    you should debug and see the value of the global variable mCustomerID during and after the method completes

    Sunday, January 2, 2011 3:26 PM
  • User2050872319 posted

    Hello Richard,

    Well, obviously without actual sql statements it's quite difficult to figure out what is supposed to happen.

    But I can see a few issues straight away.

    1. Parameter marker in mysql is not @ sign but a ? mark instead.

    2. It seems that you try to execute a single sql query in 2 different ways (not sure what it's for). If your sql statement was an insert and you need to get autoincrement value, then you'd better use myCommand.InsertId property.

    I.e.

       
    using (myConnection)  
    { 
          myConnection.Open();  
          myCommand.ExecuteNonQuery();  
          mCustomerID = myCommand.InsertId;  
    }  
    

    Hope this helps

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Sunday, January 2, 2011 3:39 PM
  • User513620916 posted

    When it comes to the true or false question, that comes into place in another class where it decides if something should get allocated a new object or not.. You can say that, that method is used for storing to a database and it's suppose to do that twice. If the same method returns true twice then it will be sent back "through the chains" of the application/solution that it did and the shoppingcart will be emptied..

    When it comes to @ statement.. I chose that because the course literature states that. (Beginning ASP.NET 3.5 in C# 2008 - Page 523)

    2. It seems that you try to execute a single sql query in 2 different ways (not sure what it's for).
    2 different ways?
    IF you are thinking about myCommand.ExecuteNonQuery, from what I've read that is the whole insertion into the database
    and mCustomerID is to get the latest ID of the table where my information was last inserted into.

     

    It looks like the following line was all I needed:

    mCustomerID = (int)myCommand.LastInsertedId;

    @ - seem to work, so I'm not changing that..

    Thank you!

    Best regards
    Richard

    Sunday, January 2, 2011 5:21 PM
  • User-1871404500 posted

    hi ,

    i have a form1 which include information about a custumer and i have  to save an image in database (SQL server)  for each one and i need to get the ID of latest input that i can save it because i should passed to the next form

    my probelm is that evry time i run when i check the table image i found my image added but when i check the table product the reference of the id of image is always = '0000'

    pls how can i solve it.. even i tried to add this line  '  int ID = (int)command.LastInsertedId'; // ===> i get erreur that a using  directive or assembly reference is missed !!!!!<===
         

    this is my code for saving into the datbase :

    =================================================================

    private int SaveToDB(string chemin, string imgname, byte[] imgbin)
        {
            //use the web.config to store the connection string
            SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["ApplicationServices"].ConnectionString);
            SqlCommand command = new SqlCommand("INSERT INTO Image (Chemin,ToolTip,IMG) VALUES  ( @Chemin, @ToolTip, @IMG ) " , connection);

            SqlParameter param1 = new SqlParameter("@Chemin", SqlDbType.VarChar, 50);
            param1.Value = chemin;
            command.Parameters.Add(param1);

            SqlParameter param2 = new SqlParameter("@ToolTip", SqlDbType.VarChar, 50);
            param2.Value = imgname;
            command.Parameters.Add(param2);

            SqlParameter param3 = new SqlParameter("@IMG", SqlDbType.Image);
            param3.Value = imgbin;
            command.Parameters.Add(param3);


            connection.Open();
            int numRowsAffected = command.ExecuteNonQuery();
           // int ID = (int)command.LastInsertedId;

            connection.Close();

            return numRowsAffected;
        }

    ======================================

    and this one for the button Upload :

    ===========================

    protected void UploadBtn_Click(Object sender, EventArgs e)
        {
            if (Page.IsValid) //save the image
            {
                HttpPostedFile MyFile;
                MyFile = UploadFile.PostedFile;
                Stream imgStream = MyFile.InputStream;
                int imgLen = MyFile.ContentLength;

                string imgName = NomTB.Text;
                byte[] imgBinaryData = new byte[imgLen];
                int n = imgStream.Read(imgBinaryData, 0, imgLen);
                string chemin = Server.MapPath(MyFile.FileName);
                int RowsAffected = SaveToDB(chemin, imgName, imgBinaryData);
                if (RowsAffected > 0)
                {
                   
                    Response.Write("<BR>The Image was saved");
                }
                else
                {
                    Response.Write("<BR>An error occurred uploading the image");
                }
            }

    =======================

    and this is the function to save all information :

    =================================

    public void CreerChambre( string pNomProduit,   string pType,  string pDescriptionProduit, ...... , string pPathPhoto,System.Guid idIm)

    {  [.....]

    p1.ID_IMAGE = idIm;
                 
            foreach (DAL.Image I in pLstImages)
                {
                    p1.ID_IMAGE = I.ID;
                    I.Produit.Add(p1);
                }

    }
     

    =========

    could You help plss !!

    Friday, August 26, 2011 5:49 AM