3

I am developing a simple savings account application where I am able to simulate deposit, withdrawal and transfer of money. I have a button called "Deposit" that I can click and data will be recorded in my database.

To make that insert statement I am using SQL parameters and this is the code: 

    SqlCon = New SqlConnection
    SqlCon.ConnectionString = "............"
    Try
        Query = "INSERT INTO Transacoes(tpAccount, dateTransaction, descrTransaction, amoutTransaction, balanceTransaction)
                 VALUES(@tpAccount, @dateTransaction, @ddescrTransaction, @amoutTransaction, @balanceTransaction)"

        SqlCon.Open()
        SqlCmd = New SqlCommand(Query, SqlCon)

        With SqlCmd.Parameters
            .Add("@tpAccount", SqlDbType.Char).Value = cbTipoConta.Text
            .Add("@dateTransaction", SqlDbType.DateTime).Value = txtDate.Text
            .Add("@descrTransaction", SqlDbType.Char).Value = cmdDepositar.Text
            .Add("@amoutTransaction", SqlDbType.Int).Value = txtDeposito.Text
            .Add("@balanceTransaction", SqlDbType.Int).Value = txtBalance.Text
        End With

        SqlCmd.ExecuteNonQuery()
        SqlCon.Close()
    Catch ex As Exception
        MsgBox(ex.Message)
    End Try
End Sub

In this table I have an "ID" column but I don't want it to display on SQL parameters and shows me an error:

Cannot insert the value NULL into column 'Id'`.

I should autoincrement the Id and then this will work fine? Or what is the best way to solve this?

Improve this question
2
  • Yes you should make ID column as Auto Increment Commented Jun 30, 2016 at 10:32
  • Ok thank you. Almost forgot it @JaydipJ Commented Jun 30, 2016 at 10:39

3 Answers 3

Reset to default
1

You should autoincrement the ID, I always use ID column with Identity(1,1) even though it is not needed at the moment. It is always nice to select specific row fast and when the ID is autoincremented, then you have only one where condition.

Improve this answer
Sign up to request clarification or add additional context in comments.

Comments

0

In order to be able to omit the ID field in your insert statement you need to set an auto increment (identity) on that field.

REF: https://technet.microsoft.com/en-gb/library/ms188263(v=sql.105).aspx

Improve this answer

Comments

0

Identity is not the only solution in this case. The other one is named sequence. In your case you can simply create a new sequence like in this example

create sequence dbo.id_sequence as int
start with 0  
increment by 1;

More information about create of sequence you can find here

If you have a sequence you can use it as a defalut value for any column in table what you can see in example on point D here. You can even use more sequences to work with more than one column in a table if you want.

Difference between identinty and sequence is that you can use an identity only for one column (with sequence or even sequences you can do it with many columns in one table) and for set identity you need to recreate table (the alter is not allowed in this case) or remove column with identity and create a new one with it (and you need to be aware about constraints and relations with deleted column). With sequences you can simply alter the table in any moment with any column you need.

With sequences you can update or insert a value to a column (with identity you can't) so if you use this part of your work to simulate something it colud be a good idea to make a possibility of using original ids (if you need it of course) instead of that created by identity.

Improve this answer

Comments

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.