Visual studio 2010 C# SQLCommand.ExecuteNonQuery SQLException Error converting data type varchar to int

Question

i tried inserting data in my database but my SQLCommand.ExecuteNonQuery throws an SQLException Error "converting data type varchar to int."

this is my aspx.cs

    public void FillDropdownList()
    {
        if (!IsPostBack)
        {
            String[] Semesters = { "DIS R", "DIS M", "DDOA", "DECO R", "DECO L", "CRIA" };
            for (int i = 0; i < Semesters.Length; i++)
            {
                ddSemester.Items.Add(Semesters[i].ToString());
            }
            String[] Periodes = { "2013 P2", "2014 P1", "2014 P2" };
            for (int i = 0; i < Periodes.Length; i++)
            {
                ddPeriode.Items.Add(Periodes[i].ToString());
            }

        }
    }

    protected void Button1_Click(object sender, EventArgs e)
    {
        string Connectionstring = @"Data Source=mossserver1;Initial Catalog=Sharepoint;Integrated Security=SSPI;";
        SqlConnection Datacon = new SqlConnection(Connectionstring);
        SqlCommand Datacommand = new SqlCommand("sprocSemesterkeuze");
        Datacommand.CommandType = CommandType.StoredProcedure;
        Datacommand.Connection = Datacon;

        //de komende statements herhalen tot je alle parameters hebt ingevoerd
        string leerlingnaam = getStringAfterSlash(SPContext.Current.Site.RootWeb.CurrentUser.Name);
        SqlParameter llnaam = new SqlParameter();
        llnaam.ParameterName = "@leerlingnaam"; //bv @artikelnr, maar dat ligt aan wat je in de stored procedure hebt
        llnaam.SqlDbType = SqlDbType.VarChar;  //kan ook een int zijn of whatever je in de database hebt gedefinieerd
        llnaam.Value = (string)leerlingnaam;
        llnaam.Direction = ParameterDirection.Input;
        Datacommand.Parameters.Add(llnaam);

        SqlParameter semesterkeuze = new SqlParameter();
        semesterkeuze.ParameterName = "@semesterkeuze"; //bv @artikelnr, maar dat ligt aan wat je in de stored procedure hebt
        semesterkeuze.SqlDbType = SqlDbType.VarChar;  //kan ook een int zijn of whatever je in de database hebt gedefinieerd
        semesterkeuze.Value = (string)ddSemester.Text;
        semesterkeuze.Direction = ParameterDirection.Input;
        Datacommand.Parameters.Add(semesterkeuze);

        SqlParameter slber = new SqlParameter();
        slber.ParameterName = "@slber"; //bv @artikelnr, maar dat ligt aan wat je in de stored procedure hebt
        slber.SqlDbType = SqlDbType.VarChar;  //kan ook een int zijn of whatever je in de database hebt gedefinieerd
        slber.Value = (string)tbSlber.Text;
        slber.Direction = ParameterDirection.Input;
        Datacommand.Parameters.Add(slber);

        SqlParameter periode = new SqlParameter();
        periode.ParameterName = "@periode"; //bv @artikelnr, maar dat ligt aan wat je in de stored procedure hebt
        periode.SqlDbType = SqlDbType.VarChar;  //kan ook een int zijn of whatever je in de database hebt gedefinieerd
        periode.Value = (string)ddPeriode.Text;
        periode.Direction = ParameterDirection.Input;
        Datacommand.Parameters.Add(periode);

        Datacon.Open();

        Datacommand.ExecuteNonQuery();
        Datacommand.Dispose();
        Datacon.Dispose();



    }

    protected string getStringAfterSlash(string input)
    {
        ArrayList matches = new ArrayList();
        foreach (Match match in Regex.Matches(input, "\\\\"))
        {
            matches.Add(match.Index);
        }
        if (matches.Count > 0)
        {
            return input.Substring((int)matches[matches.Count - 1] + 1);
        }
        return input;
    }

}

}

and this is my aspx file

<%@ Assembly Name="$SharePoint.Project.AssemblyFullName$" %>
<%@ Assembly Name="Microsoft.Web.CommandUI, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %> 
<%@ Register Tagprefix="SharePoint" Namespace="Microsoft.SharePoint.WebControls" Assembly="Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %> 
<%@ Register Tagprefix="Utilities" Namespace="Microsoft.SharePoint.Utilities" Assembly="Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
<%@ Register Tagprefix="asp" Namespace="System.Web.UI" Assembly="System.Web.Extensions, Version=3.5.0.0, Culture=neutral, PublicKeyToken=31bf3856ad364e35" %>
<%@ Import Namespace="Microsoft.SharePoint" %> 
<%@ Register Tagprefix="WebPartPages" Namespace="Microsoft.SharePoint.WebPartPages" Assembly="Microsoft.SharePoint, Version=14.0.0.0, Culture=neutral, PublicKeyToken=71e9bce111e9429c" %>
<%@ Control Language="C#" AutoEventWireup="true" CodeBehind="StudentSemesterKeuzeUserControl.ascx.cs" Inherits="StudentSemesterkeuze.StudentSemesterKeuze.StudentSemesterKeuzeUserControl" %>
<asp:Label ID="lblSemester" runat="server" Text="Kies Semester"  Width = " 100px"></asp:Label>
<asp:DropDownList ID="ddSemester" runat="server">
</asp:DropDownList>
<br />
<asp:Label ID="lblPeriode" runat="server" Text="Kies Periode" Width = " 100px"></asp:Label>
<asp:DropDownList ID="ddPeriode" runat="server">
</asp:DropDownList>
<br />
<asp:Label ID="Label1" runat="server" Text="SLB'er Code"></asp:Label>
<asp:TextBox ID="tbSlber" runat="server"></asp:TextBox>
<br />
<asp:Button ID="Button1" runat="server" Text="Stuur semesterkeuze" 
    onclick="Button1_Click" />

and last but not least, my stored procedure

create procedure sprocSemesterkeuze
@leerlingnaam VARCHAR(255),
@semesterkeuze VARCHAR(255),
@slber          VARCHAR(255),
@periode        VARCHAR(255)
as
begin

Insert into Semesterkeuze(leerlingnummer,semesterkeuze,[status],slber,datum,opmerkingen,periode)
values((select leerlingnummer from ToetsCijfers where leerlingnaam = @leerlingnaam),@semesterkeuze, 'Pending',@slber,CURRENT_TIMESTAMP,null,@periode)


end

i get the exception at Datacommand.ExecuteNonQuery(); in my aspx.cs file. Can someone please help?

this is how my table looks like puu.sh/54Ca5.png

Answer 1

i found the solution. in my selectstatement (select leerlingnummer from ToetsCijfers where leerlingnaam = @leerlingnaam) i added a group by clause (group by leerlingnummer) and it worked.

but i am not sure why. can anyone tell me that?