locked
Vb: SQL Both Insert and Select at same time.. RRS feed

  • Question

  • I am using following to save some data in SQL from VB.

    Query = "INSERT INTO [dbo].[ClientMaster] VALUES((SELECT ISNULL(MAX(ClientID)+1,100000) FROM ClientMaster), @RegDate, @Status, @ClientName)"
            SqlCmd = New SqlCommand(Query, ConnC())
    
            SqlCmd.Parameters.AddWithValue("@RegDate", d2.Value)
            SqlCmd.Parameters.AddWithValue("@Status", c3.Text)
            SqlCmd.Parameters.AddWithValue("@ClientName", t4.Text)
                  '//Execute the query
            SqlCmd.ExecuteNonQuery()
            MessageBox.Show("Data sucessfully saved for Id: "????", "Sucess", MessageBoxButtons.OK, MessageBoxIcon.Information)


    Now my query is that i want to know the client id in which this data has bee stored.

     i mean replace ???? with client id.

    1 possibility is getting it  with following new query

    SELECT MAX(ClientID) FROM ClientMaster

    but what if some more data is saved at the same time.

    So is there any possibility of getting ID at the same time insert command is run to avoid any possibility of picking wrong Client id..

      

    Pradeep Yadav (Social MSDN)


    • Edited by pk.yadav Monday, May 28, 2018 7:14 AM more info..
    Monday, May 28, 2018 7:11 AM

Answers

  • Seems that you do not prefer the automatic assignment of new ClientID, which is usually recommended. Then try this query:

    "DECLARE @id AS int; SELECT @id = ISNULL(MAX(ClientID)+1,100000) FROM ClientMaster; INSERT … VALUES(@id, …); SELECT @id"

    Use ExecuteScalar (or ExecuteReader), which should return the value of @id.

    Probably you will have to switch to recommended solutions

    Monday, May 28, 2018 7:47 AM

All replies

  • Seems that you do not prefer the automatic assignment of new ClientID, which is usually recommended. Then try this query:

    "DECLARE @id AS int; SELECT @id = ISNULL(MAX(ClientID)+1,100000) FROM ClientMaster; INSERT … VALUES(@id, …); SELECT @id"

    Use ExecuteScalar (or ExecuteReader), which should return the value of @id.

    Probably you will have to switch to recommended solutions

    Monday, May 28, 2018 7:47 AM
  • Hi pk.yadav,

    According to your description, you just want to get new id while inserting new record in sql server, I find one thread that you can take a look:

    https://stackoverflow.com/questions/9052324/getting-id-after-insert-in-sql-server

    Hope it is helpful to you.

    Best Regards,

    Cherry


    MSDN Community Support
    Please remember to click "Mark as Answer" the responses that resolved your issue, and to click "Unmark as Answer" if not. This can be beneficial to other community members reading this thread. If you have any compliments or complaints to MSDN Support, feel free to contact MSDNFSF@microsoft.com.

    Monday, May 28, 2018 10:41 AM
  • http://forums.devshed.com/net-development-87/tu-scope_identity-587284.html

    You should be able top duplicate the final answer in vb.net code, which is simplistic in nature. That's if the primary-key for the inserted record is an auto incremented Identity primary-key column

    string insertedID = "";
    
    string query = "INSERT INTO users" +
                     " (name, lastname)" +
                     " VALUES (@Name, @LastName);" +
                     " SELECT SCOPE_IDENTITY();";
    
    using (var dbconn = new SqlConnection("your connection string here") )
    using (var dbcm = new SqlCommand(query, dbconn) )
    {
        dbcm.Parameters.Add("@Name", SqlDbType.VarChar, 50).Value = txtName.Text;
        dbcm.Parameters.Add("@LastName", SqlDbType.VarChar, 50).Value = txtLastName.Text;
    
        dbconn.Open();
        insertedID = dbcm .ExecuteScalar().ToString();
    }

    Monday, May 28, 2018 10:45 AM
  • issue is i will be using it for generating Invoice no.. and in case of autogenerate identity, when an row is deleted it still generate next id which will create an issue of missing invoice numbers.. as we need to have Invoice no in proper sequence..

    still thanks for help..


    Pradeep Yadav (Social MSDN)

    Tuesday, May 29, 2018 6:12 AM
  • thanks for your help this works..

    Pradeep Yadav (Social MSDN)

    Tuesday, May 29, 2018 6:17 AM
  • issue is i will be using it for generating Invoice no.. and in case of autogenerate identity, when an row is deleted it still generate next id which will create an issue of missing invoice numbers.. as we need to have Invoice no in proper sequence..

    still thanks for help..


    Pradeep Yadav (Social MSDN)


    This makes no sense. So the next sequential count is at 10,000 and you delete a record that had a invoice ID of 10. How is that going to knock the invoice number next count number assigned out of sequence which would be 10001? 
    Tuesday, May 29, 2018 12:40 PM
  • issue is i will be using it for generating Invoice no.. and in case of autogenerate identity, when an row is deleted it still generate next id which will create an issue of missing invoice numbers.. as we need to have Invoice no in proper sequence..

    still thanks for help..


    Pradeep Yadav (Social MSDN)


    This makes no sense. So the next sequential count is at 10,000 and you delete a record that had a invoice ID of 10. How is that going to knock the invoice number next count number assigned out of sequence which would be 10001? 
    No that can be very important, I don't know where you live, but in my state invoice numbers need to have a not disturbed sequence starting every reporting year with 1. 

    Success
    Cor

    Tuesday, May 29, 2018 1:20 PM
  • issue is i will be using it for generating Invoice no.. and in case of autogenerate identity, when an row is deleted it still generate next id which will create an issue of missing invoice numbers.. as we need to have Invoice no in proper sequence..

    still thanks for help..


    Pradeep Yadav (Social MSDN)


    This makes no sense. So the next sequential count is at 10,000 and you delete a record that had a invoice ID of 10. How is that going to knock the invoice number next count number assigned out of sequence which would be 10001? 

    No that can be very important, I don't know where you live, but in my state invoice numbers need to have a not disturbed sequence starting every reporting year with 1. 

    Success
    Cor


    If it's that important, then don't delete the record, just like your property taxes kept by your jurisdiction, county government, doesn't delete your property taxes paid for a given year. So rules are to not delete any records that an application uses. Or at the very least, there is a limit on how long records can be keep that are most likely warehoused, like insurance records based on policy number. 
    Tuesday, May 29, 2018 2:45 PM