insert query in auto indexing primary key

Question

I have a table called WorkPerUser with the following columns:

id  int 
username    nchar(50)
RMANumber   int
Charge  real

The "id" is the primary key with auto-increment of 1.

My code behind is:

 Protected Sub btnSubmit_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnSubmit.Click


        'Dim temp_id As String
        'temp_id = Session("se_userid")


        'SQL connection String and Query 

        Dim connectionString As String = ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString
        Dim insertSql As String = "INSERT INTO [WorkPerUser] (id,username,RMANumber,Charge)" & " values (@id,@username,@rmanumber,@charge)"

        'Create SQL connection
        Dim con As New SqlConnection(connectionString)

        'Create SQl command and parameters
        Dim cmd As New SqlCommand()
        cmd.Connection = con
        cmd.CommandType = CommandType.Text
        cmd.CommandText = insertSql

        Dim id As New SqlParameter("@id", SqlDbType.Text)
        id.Value =
        cmd.Parameters.Add(id)

        Dim username As New SqlParameter("@username", SqlDbType.NChar)
        username.Value = Session("userName")
        cmd.Parameters.Add(username)

        Dim rmanumber As New SqlParameter("@rmanumber", SqlDbType.Int)
        rmanumber.Value = txtRMA.Text.ToString()
        cmd.Parameters.Add(rmanumber)

        Dim charge As New SqlParameter("@charge", SqlDbType.Real)
        charge.Value = txtCharge.Text.ToString()
        cmd.Parameters.Add(charge)

        Try
            con.Open()
            cmd.ExecuteNonQuery()
            lblMsg.Text = "Job Inserted"
        Catch ex As SqlException
            Dim errorMessage As String = "Error registering job"
            errorMessage += ex.Message

            Throw New Exception(errorMessage)
        Finally
            con.Close()
        End Try


    End Sub

As you may have noticed, I left the id.Value = empty because I do not know what to send to the database. Does anything need to be changed in the query or somewhere else?

score 2 · Accepted Answer · edited May 23 '17 at 12:12

2

If the Id is truly an autoincrement (identity) column, you won't have to insert into it manually. Just omit the Id portion

Dim insertSql As String = "INSERT INTO [WorkPerUser] (username,RMANumber,Charge)" & " values (@username,@rmanumber,@charge)"

You don't have to specify the id. You may want to get the last ID that was inserted. It is usually through SELECT SCOPE_IDENTITY() and this is tricky with inline SQL.

Check these links:

edited May 23 '17 at 12:12

Community

1
1

answered Jan 25 '13 at 14:02

codingbiz

26,179
8
59
96

Thank you. It worked. Just one more question. As you can see, I commented out the part where temp_id= session("se_userid"), and I was using id.value= temp_id to send to the database. the thing is that all the database column "id" was getting, was "000-000.." and not the actual userId from aspnet_Users. Any ideas on that? – serge Jan 25 '13 at 14:11
What are you trying to do since you are no longer inserting into the Id column? Are you trying to get the currently logged in user? If true, that should be `temp_id = Membership.GetUser().ProviderUserKey.ToString()`. If it's something else, let me know – codingbiz Jan 25 '13 at 14:37
I was trying to insert the actual userid generated by asp.net into the id column. When I checked the output on a label, it was ok, but when I was sending it in the database, the 34-char guid, was only zeros. (I already retrieved the userid and stored it in session) – serge Jan 25 '13 at 22:05

insert query in auto indexing primary key

1 Answers1