By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
443,705 Members | 2,017 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 443,705 IT Pros & Developers. It's quick & easy.

Getting the Null value error

P: 67
Hi guys i am here with my another probelm please help me.trying insert the value into the data base but getting the null value error .I am getting thsi error
Expand|Select|Wrap|Line Numbers
  1. Cannot insert the value NULL into column 'EmployeeID', table 'Accomplishments.dbo.Accomplishment'; column does not allow nulls. INSERT fails. The statement has been terminated. 
  2.  
  3.  
and my code is this
Expand|Select|Wrap|Line Numbers
  1. using System;
  2. using System.Collections;
  3. using System.ComponentModel;
  4. using System.Data;
  5. using System.Drawing;
  6. using System.Web;
  7. using System.Web.SessionState;
  8. using System.Web.UI;
  9. using System.Web.UI.WebControls;
  10. using System.Web.UI.HtmlControls;
  11. using System.Data.SqlClient;
  12. using System.Data.SqlTypes;
  13. using System.DirectoryServices;
  14.  
  15. namespace Accomplishments
  16. {
  17.     /// <summary>
  18.     /// Summary description for Accomplishment.
  19.     /// </summary>
  20.     public class Accomplishment : System.Web.UI.Page
  21.     {
  22.         protected System.Web.UI.WebControls.TextBox txtFirstName;
  23.         protected System.Web.UI.WebControls.TextBox txtLastName;
  24.         protected System.Web.UI.WebControls.TextBox txtdatecreated;
  25.         protected System.Web.UI.WebControls.TextBox txtProject;
  26.         protected System.Web.UI.WebControls.TextBox txtdescription;
  27.         protected System.Web.UI.WebControls.TextBox Login;
  28.         protected System.Web.UI.WebControls.TextBox txtLogin;
  29.         protected System.Web.UI.WebControls.DropDownList drpProject;
  30.         protected System.Web.UI.WebControls.TextBox txtdept;
  31.         protected System.Web.UI.WebControls.RequiredFieldValidator Project;
  32.         protected System.Web.UI.WebControls.DropDownList drpMonth;
  33.         protected System.Web.UI.WebControls.TextBox txtemployee;
  34.         protected System.Web.UI.WebControls.Button cmdSubmit;
  35.  
  36.         private void Page_Load(object sender, System.EventArgs e)
  37.         {
  38.             txtdatecreated.Text = DateTime.Now.ToShortDateString();
  39.             // Put user code to initialize the page here
  40.             if(Session["Login"].ToString() != null)
  41.             {
  42.                 this.txtLogin.Text=Session["Login"].ToString();
  43.             }
  44.  
  45.             if(Session["fn"].ToString() != null)
  46.             {
  47.                 this.txtFirstName.Text=Session["fn"].ToString();
  48.             }
  49.  
  50.             if(Session["sn"].ToString() != null)
  51.             {
  52.                 this.txtLastName.Text=Session["sn"].ToString();
  53.             }
  54.  
  55.             if(Session["department"].ToString() != null)
  56.             {
  57.                 this.txtdept.Text=Session["department"].ToString();
  58.             }
  59.  
  60.         }
  61.  
  62.         #region Web Form Designer generated code
  63.         override protected void OnInit(EventArgs e)
  64.         {
  65.             //
  66.             // CODEGEN: This call is required by the ASP.NET Web Form Designer.
  67.             //
  68.             InitializeComponent();
  69.             base.OnInit(e);
  70.         }
  71.  
  72.         /// <summary>
  73.         /// Required method for Designer support - do not modify
  74.         /// the contents of this method with the code editor.
  75.         /// </summary>
  76.         private void InitializeComponent()
  77.         {    
  78.             this.cmdSubmit.Click += new System.EventHandler(this.cmdSubmit_Click);
  79.             this.Load += new System.EventHandler(this.Page_Load);
  80.  
  81.         }
  82.         #endregion
  83.  
  84.  
  85.  
  86.         private void cmdSubmit_Click(object sender, System.EventArgs e)
  87.         {
  88.  
  89.  
  90.             SqlConnection oConn=new SqlConnection(System.Configuration.ConfigurationSettings.AppSettings["oConn"]);
  91.             SqlDataAdapter da=new SqlDataAdapter("dbo.win_insertName_test", oConn);
  92.             da.SelectCommand.CommandType=CommandType.StoredProcedure; 
  93.  
  94.             da.SelectCommand.Parameters.Add(new SqlParameter("@FirstName",txtFirstName.Text));
  95.             da.SelectCommand.Parameters.Add(new SqlParameter("@LastName",txtLastName.Text));
  96.             da.SelectCommand.Parameters.Add(new SqlParameter("@AccompDesc",txtdescription.Text));
  97.             da.SelectCommand.Parameters.Add(new SqlParameter("@datecreated",txtdatecreated.Text));
  98.             da.SelectCommand.Parameters.Add(new SqlParameter("@deptname",txtdept.Text));
  99.             da.SelectCommand.Parameters.Add(new SqlParameter("@ProjectId",drpProject.SelectedItem.Value));
  100.  
  101.             SqlParameter employeeid = new SqlParameter("@employeeID", SqlDbType.Int); 
  102.             employeeid.Direction = ParameterDirection.Output; 
  103.             da.SelectCommand.Parameters.Add(employeeid);
  104.  
  105.  
  106.             SqlParameter newid = new SqlParameter("@newID", SqlDbType.Int); 
  107.             newid.Direction = ParameterDirection.Output; 
  108.             da.SelectCommand.Parameters.Add(newid);
  109.  
  110.  
  111.             oConn.Open();
  112.             da.SelectCommand.ExecuteNonQuery();
  113.             oConn.Close();
  114.  
  115.  
  116.         }
  117.     }
  118.  
  119.  
  120. }
  121.  
  122.  
  123.  
  124.  
and the store procedure is this
Expand|Select|Wrap|Line Numbers
  1. CREATE PROCEDURE dbo.win_insertName_test
  2.  
  3. (
  4.  
  5.  @FirstName varchar(25),
  6.  
  7.  @LastName varchar(25), 
  8.  
  9.  @DeptName Varchar(25),
  10.  
  11.  @Accompdesc Varchar(3000),
  12.  
  13.  @datecreated datetime,
  14.  
  15.  @projectID int, 
  16. @NewID int output ,
  17.   @employeeID int output
  18.  
  19.  
  20. )
  21.  
  22.  
  23. AS
  24.  
  25. IF EXISTS(SELECT EmployeeID FROM Employee WHERE FirstName = @firstName and LastName=@LastName)
  26.  
  27. BEGIN
  28.  
  29. --This means it exists, return it to your application
  30. set @employeeid=@newid
  31. SELECT 'This record already exists!'
  32.  
  33. Insert Into Accomplishment(EmployeeID,AccompDesc,ProjectID,DateCreated)
  34.  
  35. values(@employeeid,@AccompDesc,@ProjectID,@DateCreated)
  36.  
  37. END
  38.  
  39. ELSE
  40.  
  41. BEGIN
  42.  
  43. --This means the record isn't in there already, add it
  44.  
  45. SELECT 'Add this Record'
  46.  
  47. INSERT into Employee(FirstName, LastName, DeptName) VALUES(@FirstName, @LastName,@DeptName)
  48.  
  49. Set @NewID=Scope_Identity();
  50.  
  51. END
  52.  
  53.  
  54. GO
  55.  
  56.  
  57.  
Oct 18 '07 #1
Share this Question
Share on Google+
2 Replies


Plater
Expert 5K+
P: 7,872
Please do not double post (http://www.thescripts.com/forum/thread721761.html )
You already had some good answers given there.

@EmployedID is marked as output, but never appears to get a value?
You do a SELECT statement with it before it gets a value (so it's still null)
You also try and use @NewID before it gets a value too (also marked as output)
Oct 18 '07 #2

P: 67
i am really sorry i should check first before post the question once again i am sorry
Oct 18 '07 #3

Post your reply

Sign in to post your reply or Sign up for a free account.