locked
how to auto-increase the int field for new records inserted to DB? RRS feed

  • Question

  • User-1973972796 posted

    Hi, I have a DomainList table which contains 2 fields: DomainNumber (int) and DomainTitle (txt). I need DomainNumber to auto increase when user insert a new Domain in the DomainTitle field, couldn't think of a proper way to achieve so. I will be greatful to any help/advice here. Here is the code that i struggled to wrote and it didn't work.

    Protected Sub BtnSubmit_Click(ByVal sender As Object, ByVal e As EventArgs) Handles BtnSubmit.Click
            Dim aConnection = New OleDbConnection
            Dim aConnectionString As String
            Dim SQLQuery, SQLQuery1 As String
            Dim aCommand, aCommand1 As OleDbCommand
            Dim AddDomain As String = TextDomainnew.Text
            Dim DomainID As Integer
    
            aConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source =" & _
                Server.MapPath("/App_Data/***.accdb")
            aConnection.ConnectionString = aConnectionString
            aConnection = New OleDbConnection(aConnectionString)
            SQLQuery = "Select MAX(DomainNumber) As [DomainID] FROM DomainList"
            aCommand = New OleDbCommand(SQLQuery, aConnection)
            aConnection.Open()
            aCommand.ExecuteNonQuery()
            aConnection.Close()
            DomainID += 1
    
            SQLQuery1 = "Insert Into DomainList Values (" & DomainID & ", '" & AddDomain & "')"
            aCommand1 = New OleDbCommand(SQLQuery1, aConnection)
            aConnection.Open()
            aCommand1.ExecuteNonQuery()
            aConnection.Close()
            lblMessage.Text = "The new domain " + AddDomain + " has been added"
    
        End Sub
    

     

    Tuesday, June 7, 2011 2:56 PM

Answers

  • User1867929564 posted

    This is tested code .I hv 2 column in table.

    string AddDomain = "test";
                OleDbConnection conn = new OleDbConnection();
                OleDbCommand aCommand ;
                 conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath(@"Data\db.mdb") + ";User Id=;Password=;";
    
    
                 string strSql = "INSERT INTO DomainList ( col1,col2 ) SELECT max(col1)+1, '"+ AddDomain+"' FROM DomainList;";
                aCommand = new  OleDbCommand(strSql, conn);
                conn.Open();
                aCommand.ExecuteNonQuery();
                conn.Close();
    
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, June 10, 2011 7:58 AM

All replies

  • User-1359474226 posted

    You can better handle it in the database level. You make the domainId field as identity column. Every time a record is inserted the value in the identity column will be incremented. Since you already created the table then you can execute the below code

    ALTER TABLE DomainList ALTER COLUMN DomainNumber IDENTITY (100, 1);

    from the application you can only pass the AddDomain Value.

    Here is a link

    http://msdn.microsoft.com/en-us/library/aa933196(v=sql.80).aspx

    Hope this helps

     An Update from my side to this post. I thought the DB was SQL and specified the solution for SQL, if you are using access db then you need to use the

    AutoNumber field like Dave Suggested.

     

     

     

     

    Tuesday, June 7, 2011 3:20 PM
  • User-1856974186 posted

    If I remember correctly, in Access the data type of the field is AutoNumber, which is the equivalent of SQL Server's IDENTITY column.

    Wednesday, June 8, 2011 5:36 AM
  • User-1973972796 posted

    tks both. It's not easy for me to change the field data type to AutoNumber coz there are some relationships exist among this and others. I am wondering if there is anyway to bypass this and use the MAX statement to achieve this? Something like below (but appearantly i did something wrong so it doesn't work:( ):

            Dim aConnection = New OleDbConnection
            Dim aConnectionString As String
            Dim SQLQuery, SQLQuery1 As String
            Dim aCommand, aCommand1 As OleDbCommand
            Dim AddDomain As String = TextDomainnew.Text
            Dim DomainID As Integer
    
            aConnectionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source =" & _
                Server.MapPath("/App_Data/***.accdb")
            aConnection.ConnectionString = aConnectionString
            aConnection = New OleDbConnection(aConnectionString)
            SQLQuery = "Select MAX(DomainNumber) As DomainID FROM DomainList"
            aCommand = New OleDbCommand(SQLQuery, aConnection)
            aConnection.Open()
            aCommand.ExecuteNonQuery()
            aConnection.Close()
            DomainID += 1
    
            SQLQuery1 = "Insert Into DomainList Values (" & DomainID & ", '" & AddDomain & "')"
            aCommand1 = New OleDbCommand(SQLQuery1, aConnection)
            aConnection.Open()
            aCommand1.ExecuteNonQuery()
            aConnection.Close()
            lblMessage.Text = "The new domain " + AddDomain + " has been added"
    Wednesday, June 8, 2011 12:32 PM
  • User-1359474226 posted

    Okay..you need to use ExecuteScalar to get the value and then ExecuteNonQuery to insert the record. Something like this.

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
            Dim connetionString As String
            Dim cnn As OleDbConnection
            Dim cmd As OleDbCommand
            Dim sql As String
    
            connetionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=Your mdb filename;"
            sql = "Select MAX(DomainID) As DomainID from DomainList"
    
            cnn = New OleDbConnection(connetionString)
            Try
                cnn.Open()
                cmd = New OleDbCommand(sql, cnn)
                Dim domainID As Int32 = Convert.ToInt32(cmd.ExecuteScalar())
               domainID += 1
                cmd.Dispose()
                cnn.Close()
            Catch ex As Exception
                MsgBox("Can not open connection ! ")
            End Try
        End Sub
    
    Wednesday, June 8, 2011 12:50 PM
  • User-1973972796 posted

    i made tiny little changs and the msg "Cannot open connection" pops up when i test-run it.  Not sure why this is happending? Thanks for your patience...

      Protected Sub BtnSubmit_Click(ByVal sender As Object, ByVal e As EventArgs) Handles BtnSubmit.Click
         
            Dim connetionString As String
            Dim cnn As OleDbConnection
            Dim cmd As OleDbCommand
            Dim sql, sql1 As String
            Dim AddDomain As String = TextDomainnew.Text
    
            connetionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source =" & _
                Server.MapPath("/App_Data/***.accdb")
            sql = "Select MAX(DomainID) As DomainID from DomainList"
    
            cnn = New OleDbConnection(connetionString)
            Try
                cnn.Open()
                cmd = New OleDbCommand(sql, cnn)
                Dim domainID As Int32 = Convert.ToInt32(cmd.ExecuteScalar())
                domainID += 1
                sql1 = "Insert Into DomainList Values ('" & domainID & "', '" & AddDomain & "')"
                cmd = New OleDbCommand(sql1, cnn)
                cmd.Dispose()
                cnn.Close()
    
            Catch ex As Exception
                MsgBox("Can not open connection ! ")
            End Try
            lblMessage.Text = "The new domain " + AddDomain + " has been added"
    
        End Sub
    
    Wednesday, June 8, 2011 7:14 PM
  • User3866881 posted

    Hello:)

    I suspect it that you;ve put your accdb file into App_Data, which is a very special folder that I'm afraid you cannot directly to access from that..

    Tow solutions for you to have a try:

    1) Just configure your connection in web.conig and use ConfigurationManager.ConnectionStrings["xxx"].ConnectionString; to read it out.

    Your web.config may look like this part——

    ………………

    <connectionStrings>
      <add
        name="MyConnectionString"
        connectionString="Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\…….accdb"  />
    </connectionStrings>

    2)Just put the accdb file out of App_Data and have a try, please change the connection string dynamically in the codes before you begin to run the app.

    Thx

    Wednesday, June 8, 2011 10:20 PM
  • User1867929564 posted

    refer my query,

    http://forums.asp.net/t/1687107.aspx/1?how+to+customize+the+AutoNumber+

    is this of any help ? 

    Thursday, June 9, 2011 7:01 AM
  • User-1973972796 posted

    Hi Decker! Thanks for your reply. I am using the same file path for accdb file in other pages of this web application, where it all worked fine so i assume it's not the folder structure issue. Hmmmm.....

    Thursday, June 9, 2011 7:24 AM
  • User-1973972796 posted

    Hi KumarHarsh. Thanks for the reference thread, however it looks same to me as the solution from GPankaj :( (well i am still very new in this field! :p )

     I am using MSVB 2010 and Access 2007 for this web app.

    Thursday, June 9, 2011 7:31 AM
  • User-1359474226 posted

    You are geting "Cannot Open connection" because that is a message that get displayed when there is an exception. What is the inner exception?

    I guess you need to close the connection and open the connection again, or reuse the existing connection and execute the non query method.

    Hope this helps

     

    Thursday, June 9, 2011 7:36 AM
  • User1867929564 posted

    No my solution is entirely different from Pankaj.
    I am not telling to make 2 connection and write 2 seperate query.
    In order to understand and test write similar query in mdb query window.

    SQLQuery1 = "Insert Into DomainList (col1,col2)
    Select '" & AddDomain & "' , max(DomainNumber)+1 from DomainList

    I am sure it will work.If it is not running then put break point copy the query and paste it here. 

     

    Thursday, June 9, 2011 7:46 AM
  • User-1359474226 posted

    I think Kumar's suggestion will also work and would be a better approach.

    You can replace your insertion code as like this

    sql1 = "Insert Into DomainList(DomainID,DomainName) Values ('Select MAX(DomainID), '" & AddDomain & "'FROM DomainList)"

     

    Thursday, June 9, 2011 8:03 AM
  • User-1973972796 posted

    thank you both! Feel so happy to see quick response from forum. I made changes to the codes as below and now it shows: 

    error: 

    Server Error in '/' Application.
    
    Syntax error in query expression 'Select MAX(DomainID)+1'.
    
    Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 
    
    Exception Details: System.Data.OleDb.OleDbException: Syntax error in query expression 'Select MAX(DomainID)+1'.
    
    Source Error: 
    
    
    Line 45:         cmd = New OleDbCommand(sql, cnn)
    Line 46:         cnn.Open()
    Line 47:         cmd.ExecuteNonQuery()
    Line 48:         cnn.Close()
    Line 49: 
    
    Source File: C:\Project\WebApplication1 - 0609\WebApplication1\Domain List.aspx.vb    Line: 47 
    
    Stack Trace: 
    
    
    [OleDbException (0x80040e14): Syntax error in query expression 'Select MAX(DomainID)+1'.]
       System.Data.OleDb.OleDbCommand.ExecuteCommandTextErrorHandling(OleDbHResult hr) +1070856
       System.Data.OleDb.OleDbCommand.ExecuteCommandTextForSingleResult(tagDBPARAMS dbParams, Object& executeResult) +247
       System.Data.OleDb.OleDbCommand.ExecuteCommandText(Object& executeResult) +194
       System.Data.OleDb.OleDbCommand.ExecuteCommand(CommandBehavior behavior, Object& executeResult) +58
       System.Data.OleDb.OleDbCommand.ExecuteReaderInternal(CommandBehavior behavior, String method) +167
       System.Data.OleDb.OleDbCommand.ExecuteNonQuery() +113
       WebApplication1.Domain_List.BtnSubmit_Click(Object sender, EventArgs e) in C:\Project\WebApplication1 - 0609\WebApplication1\Domain List.aspx.vb:47
       System.Web.UI.WebControls.Button.OnClick(EventArgs e) +118
       System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +112
       System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +10
       System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +13
       System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +36
       System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5563
    
    Version Information: Microsoft .NET Framework Version:4.0.30319; ASP.NET Version:4.0.30319.1

    Updated Codes - sweet to cut it down to 1 sql query:Innocent

    Protected Sub BtnSubmit_Click(ByVal sender As Object, ByVal e As EventArgs) Handles BtnSubmit.Click
         
            Dim connetionString As String
            Dim cnn As OleDbConnection
            Dim cmd As OleDbCommand
            Dim sql As String
            Dim AddDomain As String = TextDomainnew.Text
    
            connetionString = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source =" & _
                Server.MapPath("/App_Data/***.accdb")
    
            cnn = New OleDbConnection(connetionString)
    
            sql = "Insert Into DomainList(DomainNumber,DomainTitle) Values (Select MAX(DomainID)+1, '" & AddDomain & "'FROM DomainList)"
            cmd = New OleDbCommand(sql, cnn)
            cnn.Open()
            cmd.ExecuteNonQuery()
            cnn.Close()
    
            lblMessage.Text = "The new domain " + AddDomain + " has been added"
    
        End Sub
    
    Thursday, June 9, 2011 2:16 PM
  • User3866881 posted

    Hello all:)
    We're now having a nice discussion with each other! And here's my small point——

    To GPankaj——

    sql = "Insert Into DomainList(DomainNumber,DomainTitle) Values ('Select MAX(DomainID)+1, '" & AddDomain & "'FROM DomainList)"

    This is a wrong statement because you cannot insert a whole statement of SQL like "Select Max(DomainId)+1……" into Domainnumber, whose type is of numeric...

    My suggestion is that maybe you should use another SqlCommand+SqlDataReader to read out the Max(DomainID)+1's result and assign into the sql and to be executed again.

    I'd like to listen to Kumar's suggestion,

    To Kumar:)

    It's turned to you to make a report on your own. We're looking forward to yours.

    Thx

    Thursday, June 9, 2011 9:15 PM
  • User-1359474226 posted

    Okay, my bad there is a slight syntax error. Thanks Decker for pointing it out, You need to remove the values keyword and write the statement as

    Insert Into DomainList(DomainNumber,DomainTitle)  Select MAX(DomainID)+1, '" & AddDomain & "'FROM DomainList. It will work. Instead of directly calling the statement, I would suggest to work with a stored procedure.

    Hope this helps

    Friday, June 10, 2011 1:07 AM
  • User1867929564 posted

    This query will work.It is wrongly written.I already wrote to put breakpoint and copy the sql.

    INSERT INTO DomainList ( col1, col2 )

    SELECT max(col1)+1, 'Test' FROM DomainList;

    here col1 is numeric type.

     try this,

    "Insert Into DomainList(DomainNumber,DomainTitle) 
    Values (Select MAX(DomainID)+1, '" & AddDomain & "' FROM DomainList)"

     

    Friday, June 10, 2011 1:28 AM
  • User-1973972796 posted
    Thanks to all of you for sharing you thoughts. 

    To GPankaj:

    Tried this and it doesn't work still:

    sql = "Insert Into DomainList(DomainNumber,DomainTitle) Select MAX(DomainID)+1, '" & AddDomain & "' FROM DomainList"
    Error:

    Syntax error in query expression 'Select MAX(DomainID)+1'.

    To Decker:

    The suggestion to add one more command and reader is what i tried and queried in my first post...

    To Kumar:

    Sorry, it looks to me your suggestion is same as last time (which is below) that i got the same Syntax error msg.

    sql = "Insert Into DomainList(DomainNumber,DomainTitle) Value (Select MAX(DomainID)+1, '" & AddDomain & "') FROM DomainList"
    Tricky :O
    Friday, June 10, 2011 6:16 AM
  • User1867929564 posted

    This is tested code .I hv 2 column in table.

    string AddDomain = "test";
                OleDbConnection conn = new OleDbConnection();
                OleDbCommand aCommand ;
                 conn.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" + Server.MapPath(@"Data\db.mdb") + ";User Id=;Password=;";
    
    
                 string strSql = "INSERT INTO DomainList ( col1,col2 ) SELECT max(col1)+1, '"+ AddDomain+"' FROM DomainList;";
                aCommand = new  OleDbCommand(strSql, conn);
                conn.Open();
                aCommand.ExecuteNonQuery();
                conn.Close();
    
    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Friday, June 10, 2011 7:58 AM
  • User-1973972796 posted

    That's magic Kumar. Super!!! Kiss

    Friday, June 10, 2011 8:32 AM