[Solved] ExecuteNonQuery Error: Column name or number of supplied values does not match table definition

George616 says:

using (SqlCommand cmd = new SqlCommand("INSERT INTO Users VALUES(@email,@pass,@con_pass,@UserRole,@CreatedBy,@CreateDate)"))

Change above code with below. Define the column name in Insert query.

using (SqlCommand cmd = new SqlCommand("INSERT INTO Users (email, pass, con_pass, UserRole, CreatedBy, CreateDate) VALUES(@email,@pass,@con_pass,@UserRole,@CreatedBy,@CreateDate)"))

Hi George616,

Check this example. Now please take its reference and correct your code as per the column name in database.

HTML

Login

<table>
    <tr>
        <td>Email</td>
        <td>
            <asp:TextBox ID="txtEmail" runat="server"></asp:TextBox></td>
    </tr>
    <tr>
        <td>Password</td>
        <td>
            <asp:TextBox ID="txtPassword" runat="server" TextMode="Password"></asp:TextBox></td>
    </tr>
    <tr>
        <td colspan="2" align="center">
            <asp:Button ID="btnLogin" runat="server" OnClick="OnLogin" Text="Log In" /></td>
    </tr>
</table>

MemberInvite

<table>
    <tr>
        <td>Email:</td>
        <td>
            <asp:TextBox ID="txtEmail" runat="server"></asp:TextBox></td>
    </tr>
    <tr>
        <td colspan="2" align="center">
            <asp:Button ID="btnInvite" runat="server" Text="Invite" OnClick="OnInvite" /></td>
    </tr>
</table>

NewTeamSignUp

<table>
    <tr>
        <td>Email:</td>
        <td>
            <asp:TextBox ID="txtEmail" runat="server"></asp:TextBox></td>
    </tr>
    <tr>
        <td>Password:</td>
        <td>
            <asp:TextBox ID="txtPassword" runat="server" TextMode="Password"></asp:TextBox></td>
    </tr>
    <tr>
        <td>Confirm Password:</td>
        <td>
            <asp:TextBox ID="txtConPass" runat="server" TextMode="Password"></asp:TextBox></td>
    </tr>
    <tr>
        <td colspan="2" align="center">
            <asp:Button ID="btnSignUp" runat="server" Text="Sign Up" OnClick="OnSignUp" /></td>
    </tr>
</table>

Namespaces

C#

using System.Configuration;
using System.Data;
using System.Data.SqlClient;
using System.Net;
using System.Net.Mail;

VB.Net

Imports System.Configuration
Imports System.Data
Imports System.Data.SqlClient
Imports System.Net
Imports System.Net.Mail

Code

C#

Login

protected void OnLogin(object sender, EventArgs e)
{
    int userId = 0;
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand("SELECT Uid FROM Users WHERE email = @Email AND pass = @Pass"))
        {
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.AddWithValue("@Email", txtEmail.Text.Trim());
            cmd.Parameters.AddWithValue("@Pass", txtPassword.Text.Trim());
            cmd.Connection = con;
            con.Open();
            userId = Convert.ToInt32(cmd.ExecuteScalar());
            con.Close();
        }
    }

    if (userId > 0)
    {
        Session["UserID"] = userId;
        Response.Redirect("MemberInvite.aspx");
    }
    else
    {
        Response.Redirect(Request.Url.AbsoluteUri);
    }
}

MemberInvite

protected void OnInvite(object sender, EventArgs e)
{
    using (MailMessage mm = new MailMessage("sender@gmail.com", txtEmail.Text))
    {
        mm.Subject = "Account Registration.";
        string body = "Hello " + txtEmail.Text.Trim() + ",";
        body += "<br /><br />Please click the following link to register";
        body += "<br /><a href = '" + Request.Url.AbsoluteUri.Replace("MemberInvite.aspx", "NewTeamSignUp.aspx?Id=" + Session["UserID"]) + "'>Click here for Sign up</a>.";
        body += "<br /><br />Thanks";
        mm.Body = body;
        mm.IsBodyHtml = true;
        SmtpClient smtp = new SmtpClient();
        smtp.Host = "smtp.gmail.com";
        smtp.EnableSsl = true;
        NetworkCredential NetworkCred = new NetworkCredential("sender@gmail.com", "<password>");
        smtp.UseDefaultCredentials = true;
        smtp.Credentials = NetworkCred;
        smtp.Port = 587;
        smtp.Send(mm);
    }
}

NewTeamSignUp

protected void OnSignUp(object sender, EventArgs e)
{
    string invitedBy = GetInvitedBy(Convert.ToInt32(Request.QueryString["Id"]));
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand(@"INSERT INTO Users (email, pass, con_pass, UserRole, CreatedBy, CreatedDate) 
                                                    VALUES (@email,@pass,@con_pass,@UserRole,@CreatedBy,@CreatedDate)"))
        {
            using (SqlDataAdapter sda = new SqlDataAdapter())
            {
                cmd.CommandType = CommandType.Text;
                cmd.Parameters.AddWithValue("@email", txtEmail.Text.Trim());
                cmd.Parameters.AddWithValue("@pass", txtPassword.Text.Trim());
                cmd.Parameters.AddWithValue("@con_pass", txtConPass.Text.Trim());
                cmd.Parameters.AddWithValue("@UserRole", 'U');
                cmd.Parameters.AddWithValue("@CreatedBy", invitedBy);
                cmd.Parameters.AddWithValue("@CreatedDate", DateTime.Now);
                cmd.Connection = con;
                con.Open();
                cmd.ExecuteNonQuery();
                con.Close();
            }
        }
    }
}

private string GetInvitedBy(int id)
{
    string invitedBy = "";
    string constr = ConfigurationManager.ConnectionStrings["constr"].ConnectionString;
    using (SqlConnection con = new SqlConnection(constr))
    {
        using (SqlCommand cmd = new SqlCommand("SELECT email FROM Users WHERE Uid = @Id"))
        {
            cmd.CommandType = CommandType.Text;
            cmd.Parameters.AddWithValue("@Id", id);
            cmd.Connection = con;
            con.Open();
            invitedBy = Convert.ToString(cmd.ExecuteScalar());
            con.Close();
        }
    }

    return invitedBy;
}

VB.Net

Login

Protected Sub OnLogin(ByVal sender As Object, ByVal e As EventArgs)
    Dim userId As Integer = 0
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(constr)
        Using cmd As SqlCommand = New SqlCommand("SELECT Uid FROM Users WHERE email = @Email AND pass = @Pass")
            cmd.CommandType = CommandType.Text
            cmd.Parameters.AddWithValue("@Email", txtEmail.Text.Trim())
            cmd.Parameters.AddWithValue("@Pass", txtPassword.Text.Trim())
            cmd.Connection = con
            con.Open()
            userId = Convert.ToInt32(cmd.ExecuteScalar())
            con.Close()
        End Using
    End Using

    If userId > 0 Then
        Session("UserID") = userId
        Response.Redirect("MemberInvite.aspx")
    Else
        Response.Redirect(Request.Url.AbsoluteUri)
    End If
End Sub

MemberInvite

Protected Sub OnInvite(ByVal sender As Object, ByVal e As EventArgs)
    Using mm As MailMessage = New MailMessage("sender@gmail.com", txtEmail.Text)
        mm.Subject = "Account Registration."
        Dim body As String = "Hello " & txtEmail.Text.Trim() & ","
        body += "<br /><br />Please click the following link to register"
        body += "<br /><a href = '" & Request.Url.AbsoluteUri.Replace("MemberInvite.aspx", "NewTeamSignUp.aspx?Id=" & Session("UserID")) & "'>Click here for Sign up</a>."
        body += "<br /><br />Thanks"
        mm.Body = body
        mm.IsBodyHtml = True
        Dim smtp As SmtpClient = New SmtpClient()
        smtp.Host = "smtp.gmail.com"
        smtp.EnableSsl = True
        Dim NetworkCred As NetworkCredential = New NetworkCredential("sender@gmail.com", "<password>")
        smtp.UseDefaultCredentials = True
        smtp.Credentials = NetworkCred
        smtp.Port = 587
        smtp.Send(mm)
    End Using
End Sub

NewTeamSignUp

Protected Sub OnSignUp(ByVal sender As Object, ByVal e As EventArgs)
    Dim invitedBy As String = GetInvitedBy(Convert.ToInt32(Request.QueryString("Id")))
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(constr)
        Using cmd As SqlCommand = New SqlCommand("INSERT INTO Users (email, pass, con_pass, UserRole, CreatedBy, CreatedDate) VALUES (@email, @pass, @con_pass, @UserRole, @CreatedBy, @CreatedDate)")
            Using sda As SqlDataAdapter = New SqlDataAdapter()
                cmd.CommandType = CommandType.Text
                cmd.Parameters.AddWithValue("@email", txtEmail.Text.Trim())
                cmd.Parameters.AddWithValue("@pass", txtPassword.Text.Trim())
                cmd.Parameters.AddWithValue("@con_pass", txtConPass.Text.Trim())
                cmd.Parameters.AddWithValue("@UserRole", "U"c)
                cmd.Parameters.AddWithValue("@CreatedBy", invitedBy)
                cmd.Parameters.AddWithValue("@CreatedDate", DateTime.Now)
                cmd.Connection = con
                con.Open()
                cmd.ExecuteNonQuery()
                con.Close()
            End Using
        End Using
    End Using
End Sub

Private Function GetInvitedBy(ByVal id As Integer) As String
    Dim invitedBy As String = ""
    Dim constr As String = ConfigurationManager.ConnectionStrings("constr").ConnectionString
    Using con As SqlConnection = New SqlConnection(constr)
        Using cmd As SqlCommand = New SqlCommand("SELECT email FROM Users WHERE Uid = @Id")
            cmd.CommandType = CommandType.Text
            cmd.Parameters.AddWithValue("@Id", id)
            cmd.Connection = con
            con.Open()
            invitedBy = Convert.ToString(cmd.ExecuteScalar())
            con.Close()
        End Using
    End Using

    Return invitedBy
End Function