Unable to add records into database due to output.inserted id error

Question

Hi everyone I have this code to try to add a record to my rider table in the database but when i run the code the following error occurs , may I know how do I fix this error?:

System.Data.SqlClient.SqlException: 'Cannot insert the value NULL into column 'RiderID', table 'bikestop.dbo.Rider'; column does not allow nulls. INSERT fails.'

using System;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Linq;
using System.Web;


namespace bikestop
{
    public partial class bikestop_Register : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {

        }

        public int addRider()
        {
            string strConn = ConfigurationManager.ConnectionStrings
                ["BikeStop_DBString"].ToString();
            SqlConnection conn = new SqlConnection(strConn);
            SqlCommand cmd = new SqlCommand(
                "Insert INTO Rider (Name,RiderUsername,Password) " +
                "OUTPUT INSERTED.RiderID " +
                "VALUES (@Name,@RiderUsername,@Password)", conn);
            cmd.Parameters.AddWithValue("@Name", txt_Name.Text);
            cmd.Parameters.AddWithValue("@RiderUsername", txt_RiderUsername.Text);
            cmd.Parameters.AddWithValue("@Password", txt_RiderPw.Text);
            conn.Open();
            int id = (int)cmd.ExecuteScalar();
            conn.Close();
            return id;
        }

        protected void btn_Register_Click(object sender, EventArgs e)
        {

            addRider(); 
        }
    }
}

Here is the code for the aspx file for the above code:

<%@ Page Title="" Language="C#" MasterPageFile="~/bikestop_LoggedOut.Master" AutoEventWireup="true" CodeBehind="bikestop_Register.aspx.cs" Inherits="bikestop.bikestop_Register" %>
<asp:Content ID="Content1" ContentPlaceHolderID="head" runat="server">
    <style type="text/css">
    .auto-style1 {
        width: 195px;
    }
</style>
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" runat="server">
    <table class="w-100">
    <tr>
        <td class="auto-style1">Name</td>
        <td>
            <asp:TextBox ID="txt_Name" runat="server"></asp:TextBox>
        </td>
    </tr>
    <tr>
        <td class="auto-style1">Username</td>
        <td>
            <asp:TextBox ID="txt_RiderUsername" runat="server"></asp:TextBox>
        </td>
    </tr>
    <tr>
        <td class="auto-style1">Password</td>
        <td>
            <asp:TextBox ID="txt_RiderPw" runat="server" TextMode="Password"></asp:TextBox>
        </td>
    </tr>
    <tr>
        <td class="auto-style1">&nbsp;</td>
        <td>
            <asp:Button ID="btn_Register" runat="server" Text="Register" OnClick="btn_Register_Click" />
        </td>
    </tr>
</table>
</asp:Content>

Edit : Here is how the rider table looks :

and here is the code used to create it :

CREATE TABLE Rider(

RiderID int  NOT NULL,
Name varchar(10) NOT NULL,
RiderUsername varchar(250) NOT NULL,
Password varchar(250) NOT NULL,
Primary Key (RiderID)
)

You need auto increment for identity column [link](https://stackoverflow.com/questions/1049210/adding-an-identity-to-an-existing-column) — SouXin, Oct 31 '18 at 14:35

score 1 · Answer 1 · answered Oct 31 '18 at 15:08

You are not inserting ID or you don't have auto-increment (so SQL cannot know which value to insert in your ID).

You can create your table like this:

CREATE TABLE Rider (
RiderID int NOT NULL PRIMARY KEY IDENTITY(1,1),
Name varchar(10) NOT NULL,
RiderUsername varchar(250) NOT NULL,
Password varchar(250) NOT NULL
)

and you will have auto-increment on ID column.

If you already have data, you cannot alter column to user identity(1,1), but you have a couple of options:

- Create a new table with identity(1,1) and drop the existing table

- Create a new column with identity(1,1) and drop the existing column

Note: You'll need to handle relations, but this is the cleanest way. I would probably go with second option.

More information about IDENTITY(1,1) on THIS LINK

Unable to add records into database due to output.inserted id error

1 Answers1