How to get NULL value from SQL database?

Question

0.00/5 (No votes)

See more:

I have a web project that i am working on and I am having some issues. The first page I have has two links, four buttons and one Label. When the user logs in for the first time it should show two links and two buttons. When the user enters data into the database and comes back to the page it should show two links and three buttons. When the user logs in and clicks the Submit button it should show two links, one label and two buttons. What is happening is that when the user logs in everything is showing. Only when the user enters data into the database does the page work. Please tell me why is this not working?

Page Source:

HTML

<html xmlns="http://www.w3.org/1999/xhtml">
<head id="Head1" runat="server">
    <title></title>
    <style type="text/css">
        .style1
        {
            
        }
        .style5
        {
            font-size: x-large;
            text-align: center;
        }
        .style6
        {
            text-align: center;
        }
        .style7
        {
            width: 407px;
            height: 197px;
        }
        body { 
    background-image: url('Images/bkg-blu.jpg');
    background-attachment: fixed;
    background-position: center;
    
     
}
        .style8
        {
            width: 100%;
        }
    </style>
</head>
<body background="Images/bkg-blu.jpg">
    <form id="form1" runat="server">
    <div>
    
        <table class="style1" align = "center">
            <tr>
                <td style="text-align: center">
                    <img alt="" class="style7" src="Images/f01cd29c-8bf7-45bd-8251-ce25ebd62ddf.gif" /></td>
            </tr>
        </table>
    
    </div>
    <p>
         </p>
    <table class="style1" align = "center">
        <tr>
            <td class="style5">
                Welcome 
                <br />
                <br />
                <asp:Label ID="lblSchool" runat="server"></asp:Label>
                
            </td>
        </tr>
    </table>
    <p>
         
          
        </p>
    <table class="style1" align = "center">
        <tr>
            <td class="style6">
                <asp:HyperLink ID="HyperLink1" runat="server" NavigateUrl="~/FTEForm.aspx" 
                    style="font-weight: 700">General Information and Enrollment Profile (Fall)</asp:HyperLink>
            </td>
        </tr>
        <tr>
            <td class="style6">
                <asp:HyperLink ID="HyperLink2" runat="server" NavigateUrl="~/FTEGradrate.aspx" 
                    style="font-weight: 700">Graduation Rate Information (Fall)</asp:HyperLink>
            </td>
        </tr>
        <tr>
            <td class="style6">
                <asp:Label ID="lblSub" runat="server" ForeColor="#33CC33" 
                    style="font-weight: 700; font-size: large" 
                    Text="Your Enrollment Profile has been Submitted"></asp:Label>
            </td>
        </tr>
        <tr>
            <td style="text-align: center">
                <asp:Button ID="ButtonSubmit" runat="server" onclick="ButtonSubmit_Click" 
                    Text="Submit" Width="100px" />
            </td>
        </tr>
        <tr>
            <td style="text-align: center">
                <asp:Button ID="ButtonFTEWorkSheet" runat="server" 
                    onclick="ButtonFTEWorkSheet_Click" Text="Work Sheet" Width="120px" />
            </td>
        </tr>
        <tr>
            <td class="style6">
                <asp:Button ID="ButtonPrint" runat="server" onclick="ButtonPrint_Click" 
                    Text="Print Completed Report" Width="155px" />
            </td>
        </tr>
        <tr>
            <td class="style6">
                <asp:Button ID="ButtonLogout" runat="server" onclick="ButtonLogout_Click" 
                    Text="Logout" Width="100px" />
            </td>
        </tr>
    </table>
        <asp:Label ID="Label1" runat="server"></asp:Label>
    <table class="style8">
        <tr>
            <td>
    <asp:TextBox ID="TextBoxUser_ID" runat="server" Enabled="False" ReadOnly="True"></asp:TextBox>
            </td>
            <td>
                 </td>
            <td>
                 </td>
        </tr>
        <tr>
            <td>
                <asp:TextBox ID="TextBoxSub" runat="server" Enabled="False" ReadOnly="True"></asp:TextBox>
            </td>
            <td>
                 </td>
            <td>
                 </td>
        </tr>
        <tr>
            <td>
                <asp:TextBox ID="TextBoxFTE40" runat="server" Enabled="False" ReadOnly="True"></asp:TextBox>
            </td>
            <td>
                 </td>
            <td>
                 </td>
        </tr>
        <tr>
            <td>
                <asp:TextBox ID="TextBoxHC50" runat="server" Enabled="False" ReadOnly="True"></asp:TextBox>
            </td>
            <td>
                 </td>
            <td>
                 </td>
        </tr>
        <tr>
            <td>
                <asp:TextBox ID="TextBoxYEAR1" runat="server" Enabled="False" ReadOnly="True"></asp:TextBox>
            </td>
            <td>
                 </td>
            <td>
                 </td>
        </tr>
        <tr>
            <td>
                 </td>
            <td>
                 </td>
            <td>
                 </td>
        </tr>
        <tr>
            <td>
                 </td>
            <td>
                 </td>
            <td>
                 </td>
        </tr>
        <tr>
            <td>
                 </td>
            <td>
                 </td>
            <td>
                 </td>
        </tr>
    </table>
    </form>
</body>
</html>

Code Behind:

C#

using System;
using System.Web;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Data;
using System.Drawing;
using System.Text;
using System.Collections.Generic;
using System.Linq;
using System.Data.SqlClient;
using System.Configuration;
using System.Drawing.Printing;
using System.Web.SessionState;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;

namespace FTEAPP
{
    public partial class FTEWelcome : System.Web.UI.Page
    {
        protected void Page_Load(object sender, EventArgs e)
        {
            SqlConnection con = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["HotConnectionString"].ConnectionString);
            con.Open();

            TextBoxUser_ID.Text = Session["user_id"].ToString();
            SqlCommand scmd = new SqlCommand("Select User_ID, LongName from Table1 where User_ID = '" + TextBoxUser_ID.Text + "'", con);
            SqlDataReader dr = scmd.ExecuteReader();

            if (dr.Read())
            {
                lblSchool.Text = dr["LongName"].ToString();
            }
            dr.Close();
            con.Close();

                if (TextBoxUSer_ID.Text.Trim().Length > 0)
                {
                    SqlConnection con3 = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["HotConnectionString"].ConnectionString);
                    con3.Open();

                    SqlCommand scmd3 = new SqlCommand("Select User_ID, FTE40, HC50, SUBMITTED, FTEYR from Table2 where FTEYR = 2015 AND User_ID = '" + TextBoxUser_ID.Text + "'", con3);
                    SqlDataReader dr3 = scmd3.ExecuteReader();
                    if (dr3.Read())
                    {

                        TextBoxFTE40.Text = dr3["FTE40"].ToString();
                        TextBoxHC50.Text = dr3["HC50"].ToString();
                        TextBoxSub.Text = dr3["SUBMITTED"].ToString();
                        TextBoxYEAR1.Text = dr3["FTEYR"].ToString();
                    }
                    con3.Close();
                    dr3.Close();


                    SqlConnection con7 = new SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings["HotConnectionString"].ConnectionString);
                    con7.Open();

                    SqlCommand level = new SqlCommand("Select User_ID, SUBMITTED, FTEYR from Table2 where User_ID = @User_ID AND FTEYR = 2015", con7);
                    level.Parameters.Add(new SqlParameter("User_ID", TextBoxUser_ID.Text));
                    level.Parameters.Add(new SqlParameter("SUBMITTED", TextBoxSub.Text));
                    level.Parameters.Add(new SqlParameter("FTEYR", TextBoxYEAR1.Text));

                    SqlDataReader reader3 = level.ExecuteReader();
                    DataTable dt1 = new DataTable();
                    dt1.Load(reader3);

                    foreach (DataRow dr1 in dt1.Rows)
                    {
                        string returnedLevel = (dr1[1].ToString());
                        int inst_id = Convert.ToInt32(dr1[0].ToString());
                        Session["inst_id"] = inst_id;

                        if (returnedLevel == "False")
                        {
                            ButtonSubmit.Visible = true;
                            ButtonPrint.Visible = false;
                            lblSub.Visible = false;
                            ButtonFTEWorkSheet.Visible = true;
                        }
                        else if (returnedLevel == "True")
                        {
                            ButtonSubmit.Visible = false;
                            ButtonPrint.Visible = true;
                            lblSub.Visible = true;
                            ButtonFTEWorkSheet.Visible = false;
                        }
                        else if (returnedLevel == "")
                        {
                            ButtonSubmit.Visible = false;
                            ButtonPrint.Visible = false;
                            lblSub.Visible = false;
                            ButtonFTEWorkSheet.Visible = true;
                        }
                    }
                }
            }
        

        protected void ButtonSubmit_Click(object sender, EventArgs e)
        {
            const string commandText = ("UPDATE Table2 SET SUBMITTED = 'True' WHERE User_ID = @User_ID AND FTEYR = 2015;");
            using (SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["HotConnectionString"].ConnectionString))
            using (SqlCommand cmd = new SqlCommand(commandText, con))
            {
                cmd.Parameters.AddWithValue("@User_ID", TextBoxUser_ID.Text);
                cmd.Parameters.AddWithValue("@FTEYR", TextBoxYEAR1.Text);

                con.Open();
                cmd.ExecuteNonQuery();
            }


            if (Page.IsValid)
            {
                int iTextBoxFTE40 = Convert.ToInt32(TextBoxFTE40.Text);
                int iTextBoxHC50 = Convert.ToInt32(TextBoxHC50.Text);


                if ((iTextBoxFTE40 > 40) && (iTextBoxHC50 > 50))
                {
                    TextBoxUSer_ID.Text = Session["user_id"].ToString();
                    ClientScript.RegisterStartupScript(this.GetType(), "onclick", "<script language=javascript>window.open('ReportFormFTE4050.aspx','PrintMe','height=650px,width=950px,scrollbars=1');</script>");
                    ScriptManager.RegisterStartupScript(this, typeof(Page), "myscript", "alert('You have successfully submitted the electronic portion of the General and Enrollment Profile.  Please send your signed Profile along with any required attachments to SACSCOC, Attn: Profiles, 1866 Southern Lane, Decatur, Ga 30033.');location.href='FTEWelcome.aspx';", true);
                }

                else if (iTextBoxHC50 > 50)
                {
                    TextBoxUser_ID.Text = Session["user_id"].ToString();
                    ClientScript.RegisterStartupScript(this.GetType(), "onclick", "<script language=javascript>window.open('ReportFormFTE50.aspx','PrintMe','height=650px,width=950px,scrollbars=1');</script>");
                    ScriptManager.RegisterStartupScript(this, typeof(Page), "myscript", "alert('You have successfully submitted the electronic portion of the General and Enrollment Profile.  Please send your signed Profile along with any required attachments to SACSCOC, Attn: Profiles, 1866 Southern Lane, Decatur, Ga 30033.');location.href='FTEWelcome.aspx';", true);
                }
                else if (iTextBoxFTE40 > 40)
                {
                    TextBoxUSer_ID.Text = Session["user_id"].ToString();
                    ClientScript.RegisterStartupScript(this.GetType(), "onclick", "<script language=javascript>window.open('ReportFormFTE40.aspx','PrintMe','height=650px,width=950px,scrollbars=1');</script>");
                    ScriptManager.RegisterStartupScript(this, typeof(Page), "myscript", "alert('You have successfully submitted the electronic portion of the General and Enrollment Profile.  Please send your signed Profile along with any required attachments to SACSCOC, Attn: Profiles, 1866 Southern Lane, Decatur, Ga 30033.');location.href='FTEWelcome.aspx';", true);
                }
                else if (iTextBoxFTE40 < 40)
                {
                    TextBoxUser_ID.Text = Session["user_id"].ToString();
                    ClientScript.RegisterStartupScript(this.GetType(), "onclick", "<script language=javascript>window.open('ReportFormFTE.aspx','PrintMe','height=650px,width=950px,scrollbars=1');</script>");
                    ScriptManager.RegisterStartupScript(this, typeof(Page), "myscript", "alert('You have successfully submitted the electronic portion of the General and Enrollment Profile.  Please send your signed Profile along with any required attachments to SACSCOC, Attn: Profiles, 1866 Southern Lane, Decatur, Ga 30033.');location.href='WelcomeMenu.aspx';", true);
                }
            }
        }


        protected void ButtonPrint_Click(object sender, EventArgs e)
        {
            if (Page.IsValid)
            {
                int iTextBoxFTE40 = Convert.ToInt32(TextBoxFTE40.Text);
                int iTextBoxHC50 = Convert.ToInt32(TextBoxHC50.Text);


                if ((iTextBoxFTE40 > 40) && (iTextBoxHC50 > 50))
                {
                    TextBoxUser_ID.Text = Session["user_id"].ToString();
                    ClientScript.RegisterStartupScript(this.GetType(), "onclick", "<script language=javascript>window.open('ReportFormFTE4050.aspx','PrintMe','height=650px,width=950px,scrollbars=1');</script>");
                    //ScriptManager.RegisterStartupScript(this, typeof(Page), "myscript", "alert('You have successfully submitted the electronic portion of the General and Enrollment Profile.  Please send your signed Profile along with any required attachments to SACSCOC, Attn: Profiles, 1866 Southern Lane, Decatur, Ga 30033.');location.href='WelcomeMenu.aspx';", true);
                }

                else if (iTextBoxHC50 > 50)
                {
                    TextBoxUser_ID.Text = Session["user_id"].ToString();
                    ClientScript.RegisterStartupScript(this.GetType(), "onclick", "<script language=javascript>window.open('ReportFormFTE50.aspx','PrintMe','height=650px,width=950px,scrollbars=1');</script>");
                    //ScriptManager.RegisterStartupScript(this, typeof(Page), "myscript", "alert('You have successfully submitted the electronic portion of the General and Enrollment Profile.  Please send your signed Profile along with any required attachments to SACSCOC, Attn: Profiles, 1866 Southern Lane, Decatur, Ga 30033.');location.href='WelcomeMenu.aspx';", true);
                }
                else if (iTextBoxFTE40 > 40)
                {
                    TextBoxUSer_ID.Text = Session["user_id"].ToString();
                    ClientScript.RegisterStartupScript(this.GetType(), "onclick", "<script language=javascript>window.open('ReportFormFTE40.aspx','PrintMe','height=650px,width=950px,scrollbars=1');</script>");
                    //ScriptManager.RegisterStartupScript(this, typeof(Page), "myscript", "alert('You have successfully submitted the electronic portion of the General and Enrollment Profile.  Please send your signed Profile along with any required attachments to SACSCOC, Attn: Profiles, 1866 Southern Lane, Decatur, Ga 30033.');location.href='WelcomeMenu.aspx';", true);
                }
                else if (iTextBoxFTE40 < 40)
                {
                    TextBoxUser_ID.Text = Session["user_id"].ToString();
                    ClientScript.RegisterStartupScript(this.GetType(), "onclick", "<script language=javascript>window.open('ReportFormFTE.aspx','PrintMe','height=650px,width=950px,scrollbars=1');</script>");
                    //ScriptManager.RegisterStartupScript(this, typeof(Page), "myscript", "alert('You have successfully submitted the electronic portion of the General and Enrollment Profile.  Please send your signed Profile along with any required attachments to SACSCOC, Attn: Profiles, 1866 Southern Lane, Decatur, Ga 30033.');location.href='WelcomeMenu.aspx';", true);
                }
            }
        }

        protected void ButtonLogout_Click(object sender, EventArgs e)
        {
            Session["New"] = null;

            if (Session["New"] != null)
            {
                Label1.Text += Session["New"].ToString();
            }
            else
            {
                Response.Redirect("FTELogin.aspx");
            }
        }

        protected void ButtonFTEWorkSheet_Click(object sender, EventArgs e)
        {
            TextBoxUser_ID.Text = Session["user_id"].ToString();
            ClientScript.RegisterStartupScript(this.GetType(), "onclick", "<script language=javascript>window.open('ReportFTEWorkSheet.aspx','PrintMe','height=650px,width=950px,scrollbars=1');</script>");
        }
    }
}

Posted 11-Aug-15 10:16am

Computer Wiz99

Updated 12-Aug-15 7:35am

v3

Add a Solution

Comments

Maciej Los 11-Aug-15 17:45pm

Have you tried to use Linq To DataSet?
Please, provide more details about you want to achieve.

[no name] 12-Aug-15 12:17pm

Your subject and your question are hard to bring together for me.
What is the Problem? Any error....- message?

Yes: The Problem that some code works with db which contains data and on the other side does not work with empty db/table I remember :)

1 solution

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Afzaal Ahmad Zeeshan · Answer 1 · 2015-08-11T10:28:00

Solution 1

You mean to get the records where a column is null? Sure, the following SQL would retrieve result only when the column (provided column, any column can also be multiple) is null.

SQL

Select User_ID, SUBMITTED, FTEYR 
from Table2 
where User_ID = @User_ID 
AND FTEYR = 2015
AND another_column IS NULL -- IS NULL is equal to the == null

I did not (want to) mess up with your query, so I added that WHERE clause part at the end of it. You can add the column name which you want to get values for when null, at another_column. It simply checks whether the column is having a value or not (null). An opposite for IS NULL is IS NOT NULL. Which returns the value when the record is having a value, any type of value.

Read more about IS NULL or IS NOT NULL here[^].

Posted 11-Aug-15 10:28am

Afzaal Ahmad Zeeshan

Comments

Computer Wiz99 12-Aug-15 11:14am

Thanks but it is not working. Funny thing is that this code works on another project I have but having issues with this one.

Afzaal Ahmad Zeeshan 12-Aug-15 12:37pm

You may consider sharing why is it not working. Simply stating "not working" doesn't help us understand the problem. :-)

Computer Wiz99 12-Aug-15 13:36pm

I have updated my codes.

Afzaal Ahmad Zeeshan 12-Aug-15 14:37pm

You still did not answer my question. Please tell me the problem? Would you consider debugging the application?

Computer Wiz99 12-Aug-15 14:43pm

The problem is that when a user logs in for the first time there should be two links and two buttons showing. It is not. The user logs in and everything is showing. Two links, four buttons and one Label.

Afzaal Ahmad Zeeshan 12-Aug-15 15:03pm

As I said, debug your application. Set a few breakpoints and then execute the application logic. You will be able to find out what goes wrong and where.

Computer Wiz99 12-Aug-15 15:05pm

I have do that about four times today and noting happens. No error nor details on what is causing the code not to work right.

Afzaal Ahmad Zeeshan 12-Aug-15 15:07pm

That is why breakpoints are set up. You click to continue executing each statement. This way it gives you a chance to manipulate the memory and variables. Allowing you to change the state of application and notice what is happending underground.