(Resolved) SET IDENTITY_INSERT tblName ON

[FastEddie]

I am trying to save data from a form into a SQL database and in this case I can't use a stored procedure, which I think would take care of the problem.

Anyway I am trying to save a new record back to the database and I having a problem because the first field is an identity field set to auto increment. I found a sample online, which using a SET IDENTITY_INSERT tblName ON (it was a very clear sample) but I haven't been able to get it to work with my code. This is what I have so far:

By the way the identity field is called uID

Try
            Con.Open()
            trn = Con.BeginTransaction
            cmd.Connection = Con
            cmd.Transaction = trn
            With cmd
                .CommandText = _
                    "SET IDENTITY_INSERT tblRewards ON; " & _
                    "Insert Into tblRewards(" & _
                    "LastName, " & _
                    "FirstName, " & _
                    "Address1, " & _
                    "Address2, " & _
                    "City, " & _
                    "State, " & _
                    "ZIP, " & _
                    "Phone, " & _
                    "Email" & _
                    ") Values(" & _
                    "'" & strLastName & "', " & _
                    "'" & strLastName & "', " & _
                    "'" & strAddress1 & "', " & _
                    "'" & strAddress2 & "', " & _
                    "'" & strCity & "', " & _
                    "'" & strState & "', " & _
                    "'" & strZip & "', " & _
                    "'" & strPhone & "', " & _
                    "'" & strEmail & "')"
                .ExecuteNonQuery()
                trn.Commit()
            End With
        Catch ex As Exception
            MessageBox.Show(ex.Message & vbNewLine & ex.Source)
        Finally
            Con.Close()
            Con.Dispose()
            trn.Dispose()
        End Try

[MetalKid]

If you have an identity field, do not even mention that column in your insert statement. The database will handle the creation of that field automatically. You don't have to do anything special.

[techgnome]

I don't even see an ID field in the query.... what WAS the error you were getting?

-tg

[FastEddie]

Originally I left the uID field out because I expected SQL Server to just increment it automatically for me like Access does. However when I ran it this is what I get:

Cannot insert the value NULL into column 'uID', table
'Rewards.dbo.tblRewards'; column does not allow nulls. INSERT fails.

The statement has been terminated.

This is the exact same SQL above minus the line about Set Identity. The error seems to me to indicate that it needs something for the unique field, which is why I went to Google and came up with the Set Identity.

[stanav]

Are you sure that uID field is an identity field or is it just a primary field and gets mistaken for identity field?

[FastEddie]

stanav I was absolutely 100% sure that it was set as an Identity Field. There wasn't any doubt in my mind but I checked anyways, because you brought it up, and you nailed it. It wasn't. It is now and the code works fine.

Thanks for pointing out the (not so) obvious to me.