auto increment [resolved]

[digita]

In my Ms Access Database i have set the "Id" Column to Auto Increment. Now i have the following code:

Private Sub DataAdd()
        Dim oAdapter As OleDbDataAdapter
        Dim oBuild As OleDbCommandBuilder
        Dim oDR As DataRow
        Dim strSQL As String
        Dim strConn As String
 
        ' Create New DataRow Object From DataSet
        oDR = moDS.Tables("Gegevens").NewRow()
        oDR.BeginEdit()
 
        ' Load new data into row
        'Firts Tab: Persoonlijk
        oDR("Voorletters") = txtVoorletters.Text
        oDR("Tussenvoegsel") = txtTussenvoegsel.Text
        oDR("Achternaam") = txtAchternaam.Text
        oDR("Aanspreeknaam") = txtAanspreeknaam.Text
        oDR("Geslacht") = txtGeslacht.Text
        oDR("Adres") = txtAdres.Text
        oDR("Postcode") = txtPostcode.Text
        oDR("Plaats") = txtPlaats.Text
        oDR("Provincie") = txtProvincie.Text
        If txtGeboortedatum.Text <> "" Then
            oDR("Geboortedatum") = txtGeboortedatum.Text
        End If
        oDR("Notities") = txtNotities.Text
 
        'Second Tab: Verdere Info
        oDR("Telefoon_Prive") = txtTelefoon_Prive.Text
        oDR("Mobiele_Telefoon") = txtTelefoon_Mobiel.Text
        oDR("Telefoon_Werk") = txtTelefoon_Werk.Text
        oDR("Toestelnummer_Werk") = txtToestelnummer.Text
        oDR("Faxnummer") = txtFaxnummer.Text
        oDR("Email_Adres") = txtEmailAdres.Text
 
        'Third Tab: KTC Info
        oDR("Functie") = txtFunctie.Text
        oDR("Soort_Lid") = txtSoortLid.Text
        oDR("Lidnummer") = txtLidNummer.Text
        oDR("Ftk_Nummer") = txtFtkNummer.Text
        If txtLidSinds.Text <> "" Then
            oDR("Lid_Sinds") = txtLidSinds.Text
        End If
        oDR("Kosten_Lid_Methode") = txtBetaalMethode.Text
        oDR("Kosten_Lid") = txtKostenLid.Text
 
        'Fourth Tab: Kilometer Registratie
        oDR("Kilometer_1982") = txt1982.Text
        oDR("Kilometer_1983") = txt1983.Text
        oDR("Kilometer_1984") = txt1984.Text
        oDR("Kilometer_1985") = txt1985.Text
        oDR("Kilometer_1986") = txt1986.Text
        oDR("Kilometer_1987") = txt1987.Text
        oDR("Kilometer_1988") = txt1988.Text
        oDR("Kilometer_1989") = txt1989.Text
        oDR("Kilometer_1990") = txt1990.Text
 
        oDR("Kilometer_1991") = txt1991.Text
        oDR("Kilometer_1992") = txt1992.Text
        oDR("Kilometer_1993") = txt1993.Text
        oDR("Kilometer_1994") = txt1994.Text
        oDR("Kilometer_1995") = txt1995.Text
        oDR("Kilometer_1996") = txt1996.Text
        oDR("Kilometer_1997") = txt1997.Text
        oDR("Kilometer_1998") = txt1998.Text
        oDR("Kilometer_1999") = txt1999.Text
        oDR("Kilometer_2000") = txt2000.Text
 
        oDR("Kilometer_2001") = txt2001.Text
        oDR("Kilometer_2002") = txt2002.Text
        oDR("Kilometer_2003") = txt2003.Text
        oDR("Kilometer_2004") = txt2004.Text
        oDR("Kilometer_2005") = txt2005.Text
        oDR("Kilometer_2006") = txt2006.Text
        oDR("Kilometer_2007") = txt2007.Text
        oDR("Kilometer_2008") = txt2008.Text
        oDR("Kilometer_2009") = txt2009.Text
        oDR("Kilometer_2010") = txt2010.Text
 
        ' Tell DataRow you are done adding data
        oDR.EndEdit()
        ' Add DataRow to DataSet
        moDS.Tables("Gegevens").Rows.Add(oDR)
 
        Try
            ' Get Connection String
            strConn = ConnectionString()
            ' Build SQL String
            strSQL = "SELECT * FROM Gegevens "
            ' Create New DataAdapter
            oAdapter = _
             New OleDbDataAdapter(strSQL, strConn)
            ' Create CommandBuilder for Adapter
            ' This will build INSERT, UPDATE and DELETE SQL
            oBuild = New OleDbCommandBuilder(oAdapter)
 
            ' Get Insert Command Object
            oAdapter.InsertCommand = oBuild.GetInsertCommand()
 
            ' Submit INSERT statement through Adapter
            oAdapter.Update(moDS, "Gegevens")
            ' Tell DataSet changes to data source are complete
            moDS.AcceptChanges()
 
            ' Reload the list box
            ListLoad()
 
        Catch oException As Exception
            MessageBox.Show(oException.Message)
 
        End Try
    End Sub

This updates the database trough a dataset, but how do auto increment the Id field. If i now run this code it tell me that "Column 'Id' does not allow nulls"

So i think that i have to do something with an auto increment but how do i do that?

[mendhak]

I just rushed through your code. Set the ID field's value to blank.

oDR("ID") = ""

[digita]

yeah, i also thought that would to the trick, but that didn't work


gives me the error

Input string was not in a correct format.Couldn't store <> in Id Column.  Expected type is Int32.

[Mike Hildner]

Haven't tried, just a guess here. I also don't know Access, just Sql Server.

When you have an auto-incrementing column, you just leave out that column name in your insert statement, and the DB takes care of it. Since your sql is "SELECT * FROM Gegevens", of course you're getting the ID field, and when you tell the command builder to make the insert statement for you, it gets included - which you do not want.

I should mention that it is considered bad practice to use SELECT *, instead, you should select the columns you need.

So I think you can do two things. One, specify all the columns you need in your select statement (that is, do not include your ID column). That way, the command builder will not include that column in the insert statement it creates. Two, specify your own insert statement, rather than let the command builder create it for you.

Either way, what you need to do is make sure your insert statement does not include the auto-incrementing ID column name.

[digita]

Thanks for the tips mike, but it didn't work. I did find a solution. in my dataset i needed to state that that the field id is incremental like this:

Private Sub DataSetCreate()
        Dim oAdapter As OleDbDataAdapter
        Dim strSQL As String
        Dim strConn As String
 
        ' Get Connection String
        strConn = ConnectionString()
 
        ' Build SQL String
        strSQL = "SELECT *, Achternaam + ', ' + Voorletters AS FullName FROM Gegevens ORDER BY Achternaam ASC"
 
 
        moDS = New DataSet
        Try
            ' Create New Data Adapter
            oAdapter = New OleDbDataAdapter(strSQL, strConn)
            ' Fill DataSet From Adapter and give it a name
            oAdapter.Fill(moDS, "Gegevens")
            ' Create a Primary Key
            With moDS.Tables("Gegevens")
                .PrimaryKey = New DataColumn() _
                 {.Columns("Id")}
                .Columns("Id").AutoIncrement = True
                .Columns("Id").AutoIncrementStep = 1
            End With
 
 
        Catch oExcept As Exception
            MessageBox.Show(oExcept.Message)
 
        End Try
    End Sub

As you see it was a very simple solution