I need to determine if a field is NULL. I have tried the following, but it odes not find a NULL.
Thanks
John
Protected Sub SqlCheckPreviousUpload_Selected(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlCheckPreviousUpload.Selected
If IsDBNull(SqlDataSource_ExamComplete.FindControl("ExamCorpComplete")) Then
pnConfirmationNeeded.Visible = True
Else
pnPreviouslyCompleted.Visible = True
End If
End Sub
As far as I understand SqlCheckPreviousUpload is your sql datasource and SqlDataSource_ExamComplete is your web control using the SqlCheckPreviousUpload sqldatasource , although the names seem quite weird. If so then just call the databind function of your
SqlDatasource_ExamComplete control before you actually check the value of the templated control as dbnull.
As
Protected Sub SqlCheckPreviousUpload_Selected(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlCheckPreviousUpload.Selected
SqlDataSource_ExamComplete.DataBind() 'call databind function first.
HiddenField ExamCorpComplete=SqlDataSource_ExamComplete.FindControl("ExamCorpComplete") as HiddenField; 'Get the Control you want to check for DBNull. Here I'm assuming that ExamCorpcomplete is a hidden field in your templated control of SqlDataSource_ExamComplete.
If IsNull(ExamCorpComplete.Value) Then //Check for null.. or IsDbNull...I'm not sure of this
pnConfirmationNeeded.Visible = True
Else
pnPreviouslyCompleted.Visible = True
End If
End Sub
I am sorry for the confusion, perhaps I am not actually doing what I want.
First I am trying to use the SqlCheckPreviousUpload to check a field (ExamCorpComplete) in the UserInfo table to see if the user has previously submitted a completion date. This field will either be NULL (has not submitted a date) or have the date he previously
submitted.
If this field is NULL, then I will display a panel (pnConfirmationNeeded) and display a button (btncompleted) that will fire the SqlDataSource_ExamComplete, to insert today’s date into the ExamCompleted field in the UserInfor table.
If not NULL I will display a different panel (pnPreviouslyCompleted) without the btnComplete.
This is probably the long way of doing this, but I have never tried this before and I still don’t understand why I am not catching NULLs.
Thanks again
John
%@ Page Title="" Language="VB" MasterPageFile="~/psb/psb.master" AutoEventWireup="false"
CodeFile="ExamUploaded.aspx.vb" Inherits="psb_ExamUploaded" %>
<asp:Content ID="Content1" ContentPlaceHolderID="head" runat="Server">
</asp:Content>
<asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" runat="Server">
<asp:Label ID="UserIDValue" runat="server" Visible="false"></asp:Label><br />
<asp:Label ID="lblLocalDate" runat="server" Visible="false"></asp:Label><br />
<%-- Check if user has previously recorded an upload--%>
<asp:SqlDataSource ID="SqlCheckPreviousUpload" runat="server" ConnectionString="<%$ ConnectionStrings:New_ASPNET %>"
SelectCommand="SELECT [UserID], [FirstName], [LastName], [ExamCorpComplete] FROM [UserInfo] WHERE ([UserID] = @UserID)">
<SelectParameters>
<asp:ControlParameter ControlID="UserIDValue" Name="UserID" PropertyName="Text" />
</SelectParameters>
</asp:SqlDataSource>
<asp:DetailsView ID="dvPreviousUploadDate" runat="server" Height="50px"
Width="364px" AutoGenerateRows="False" DataKeyNames="UserID"
DataSourceID="SqlCheckPreviousUpload">
<Fields>
<asp:BoundField DataField="UserID" HeaderText="UserID" ReadOnly="True"
SortExpression="UserID" />
<asp:BoundField DataField="FirstName" HeaderText="FirstName"
SortExpression="FirstName" />
<asp:BoundField DataField="LastName" HeaderText="LastName"
SortExpression="LastName" />
<asp:BoundField DataField="ExamCorpComplete" HeaderText="ExamCorpComplete"
SortExpression="ExamCorpComplete" />
</Fields>
</asp:DetailsView>
<%--Display different panels--%>
<asp:Panel ID="pnPreviouslyCompleted" runat="server" Visible="False">
Your confirmation has already been processed.</asp:Panel>
<asp:Panel ID="pnConfirmationNeeded" runat="server" Visible="False">
Please confirm you have Completed the Self-Assessment . This is required to
ensure your assessment will be available to you in the live session.
<asp:Button ID="btnCompleted" runat="server" Text="Self Assessement Completed" Visible="False" /><br />
<br />
</asp:Panel>
<%-- Fire sql with btnComplete--%>
<asp:SqlDataSource ID="SqlDataSource_ExamComplete" runat="server" ConnectionString="<%$ ConnectionStrings:New_ASPNET %>"
UpdateCommand="UPDATE [UserInfo] SET [ExamCorpComplete] = @ExamCorpComplete WHERE [UserID] = @UserID">
<UpdateParameters>
<asp:ControlParameter Name="ExamCorpComplete" ControlID="lblLocalDate" PropertyName="Text" />
<asp:ControlParameter ControlID="UserIDValue" Name="UserID" PropertyName="Text" />
</UpdateParameters>
</asp:SqlDataSource>
<%-- Reserved for error messages --%>
<asp:Label ID="lblMsg1" runat="server" Visible="False" ForeColor="#FF3300" Font-Bold="True"></asp:Label>
<asp:Label ID="lblMsg2" runat="server" Visible="False" BorderStyle="None" CssClass="MessageBox"></asp:Label>
<asp:Label ID="lblMsb3" runat="server" Visible="False"></asp:Label>
</asp:Content>
Partial Class psb_ExamUploaded
Inherits System.Web.UI.Page
'Get Date/Time from DetTime.vb
Dim LocalDate As Date = LocalTime.WriteDateTime()
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
'Check for UserName
If Context.User.Identity.Name Is Nothing Or IsDBNull(Context.User.Identity.Name) Or Context.User.Identity.Name = String.Empty Then
Response.Redirect("~/psb/Login2.aspx")
End If
UserIDValue.Text = Membership.GetUser().ProviderUserKey.ToString()
lblLocalDate.Text = LocalDate
End Sub
Protected Sub SqlCheckPreviousUpload_Selected(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlCheckPreviousUpload.Selected
If IsDBNull(SqlDataSource_ExamComplete.FindControl("ExamCorpComplete")) Then
pnConfirmationNeeded.Visible = True
Else
pnPreviouslyCompleted.Visible = True
End If
End Sub
Protected Sub btnCompleted_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnCompleted.Click
Try
SqlDataSource_ExamComplete.Update()
Catch exception As Exception
lblMsg1.Visible = True
lblMsg1.Text = "ExamCompletion Failed, wait a few minutes and try again. If fails again, please contact us"
Return
Finally
lblMsg2.Visible = True
lblMsg2.Text = "After your results from the self assessment have been verified, an email will be sent to you within 2 days confirming your confidential results will be available at the live session "
End Try
End Sub
End Class
One additional note, the field I am trying to check for NULL is a dateTime field. Is 'isDBNull' the proper way to check? The following is not chatching nulls:
Protected Sub SqlCheckPreviousUpload_Selected(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlCheckPreviousUpload.Selected
If IsDBNull(SqlDataSource_ExamComplete.FindControl("ExamCorpComplete")) Then
pnConfirmationNeeded.Visible = True
Else
pnPreviouslyCompleted.Visible = True
End If
End Sub
I think I'm able to identify your problem. Please try this and let me know.
Protected Sub SqlCheckPreviousUpload_Selected(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlCheckPreviousUpload.Selected
int completed=0;
DataView dv = SqlDataSource_ExamComplete.Select(DataSourceSelectArguments.Empty) as DataView;
if (dv != null)
{
DataRowView drv = dv[0] as DataRowView;
if (drv != null && drv["ExamCorpComplete"] != null)
{
completed=(int)drv["ExamCorpComplete"] ; //I'm assuming that your ExamCorpComplete field is integer type
}
}
If completed<=0 Then
pnConfirmationNeeded.Visible = True
Else
pnPreviouslyCompleted.Visible = True
End If
End Sub
Please forgive me for c# code here.
Don't forget to convert this logic into vb and try it.
This may be a duplicate reply, but, first the ExamCorpComplete field is a Date field, this is causing a problem and I don't know how to address it. The vb code is:
Protected Sub dvUploadDate_DataBound(ByVal sender As Object, ByVal e As System.EventArgs) Handles dvUploadDate.DataBound
Dim completed As Integer = 0
Dim dv As DataView = TryCast(SqlDataSource_ExamComplete.[Select](DataSourceSelectArguments.Empty), DataView)
If dv IsNot Nothing Then
Dim drv As DataRowView = TryCast(dv(0), DataRowView)
If drv IsNot Nothing AndAlso drv("ExamCorpComplete") IsNot Nothing Then
completed = CInt(drv("ExamCorpComplete"))
End If
End If
If completed <= 0 Then
pnConfirmationNeeded.Visible = True
Else
pnPreviouslyCompleted.Visible = True
End If
End Sub
End Class
I guess now you are near to what you are trying to achieve.
Protected Sub dvUploadDate_DataBound(ByVal sender As Object, ByVal e As System.EventArgs) Handles dvUploadDate.DataBound
Dim completed As Boolean= False
Dim dv As DataView = TryCast(SqlDataSource_ExamComplete.[Select](DataSourceSelectArguments.Empty), DataView)
If dv IsNot Nothing Then
Dim drv As DataRowView = TryCast(dv(0), DataRowView)
If drv IsNot Nothing AndAlso drv("ExamCorpComplete") IsNot Nothing Then
completed = True 'I'm assuming that if examcorpcomplete field has any date value then it's complete. You may wanna change the above criteria to validate against some default date value that is set which indicates that the exam hasn't been completed.
End If
End If
If completed =FalseThen
pnConfirmationNeeded.Visible = True
Else
pnPreviouslyCompleted.Visible = True
End If
End Sub
End Class
john1506
Member
319 Points
448 Posts
Check for NULL in SqlSataSource
May 04, 2011 11:27 PM|LINK
I need to determine if a field is NULL. I have tried the following, but it odes not find a NULL.
Thanks
John
Protected Sub SqlCheckPreviousUpload_Selected(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlCheckPreviousUpload.Selected If IsDBNull(SqlDataSource_ExamComplete.FindControl("ExamCorpComplete")) Then pnConfirmationNeeded.Visible = True Else pnPreviouslyCompleted.Visible = True End If End Sub<asp:SqlDataSource ID="SqlCheckPreviousUpload" runat="server" ConnectionString="<%$ ConnectionStrings:New_ASPNET %>" SelectCommand="SELECT [UserID], [FirstName], [LastName], [ExamCorpComplete] FROM [UserInfo] WHERE ([UserID] = @UserID)"> <SelectParameters> <asp:ControlParameter ControlID="UserIDValue" Name="UserID" PropertyName="Text" /> </SelectParameters> </asp:SqlDataSource>cyberbud
Contributor
3298 Points
551 Posts
Re: Check for NULL in SqlSataSource
May 05, 2011 05:28 AM|LINK
Try calling databind method of your SqlDataSource_ExamComplete before checking for dbnull
MCP(.net 3.5)
Nepal
ishwor.cyberbudsonline.com
john1506
Member
319 Points
448 Posts
Re: Check for NULL in SqlSataSource
May 05, 2011 03:26 PM|LINK
I am sorry I don't understand, could you please provide an example?
Thanks
John
cyberbud
Contributor
3298 Points
551 Posts
Re: Check for NULL in SqlSataSource
May 06, 2011 04:13 AM|LINK
As far as I understand SqlCheckPreviousUpload is your sql datasource and SqlDataSource_ExamComplete is your web control using the SqlCheckPreviousUpload sqldatasource , although the names seem quite weird. If so then just call the databind function of your SqlDatasource_ExamComplete control before you actually check the value of the templated control as dbnull.
As
Protected Sub SqlCheckPreviousUpload_Selected(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlCheckPreviousUpload.Selected SqlDataSource_ExamComplete.DataBind() 'call databind function first. HiddenField ExamCorpComplete=SqlDataSource_ExamComplete.FindControl("ExamCorpComplete") as HiddenField; 'Get the Control you want to check for DBNull. Here I'm assuming that ExamCorpcomplete is a hidden field in your templated control of SqlDataSource_ExamComplete. If IsNull(ExamCorpComplete.Value) Then //Check for null.. or IsDbNull...I'm not sure of this pnConfirmationNeeded.Visible = True Else pnPreviouslyCompleted.Visible = True End If End SubMCP(.net 3.5)
Nepal
ishwor.cyberbudsonline.com
john1506
Member
319 Points
448 Posts
Re: Check for NULL in SqlSataSource
May 06, 2011 03:21 PM|LINK
I am sorry for the confusion, perhaps I am not actually doing what I want.
First I am trying to use the SqlCheckPreviousUpload to check a field (ExamCorpComplete) in the UserInfo table to see if the user has previously submitted a completion date. This field will either be NULL (has not submitted a date) or have the date he previously submitted.
If this field is NULL, then I will display a panel (pnConfirmationNeeded) and display a button (btncompleted) that will fire the SqlDataSource_ExamComplete, to insert today’s date into the ExamCompleted field in the UserInfor table.
If not NULL I will display a different panel (pnPreviouslyCompleted) without the btnComplete.
This is probably the long way of doing this, but I have never tried this before and I still don’t understand why I am not catching NULLs.
Thanks again
John
%@ Page Title="" Language="VB" MasterPageFile="~/psb/psb.master" AutoEventWireup="false" CodeFile="ExamUploaded.aspx.vb" Inherits="psb_ExamUploaded" %> <asp:Content ID="Content1" ContentPlaceHolderID="head" runat="Server"> </asp:Content> <asp:Content ID="Content2" ContentPlaceHolderID="ContentPlaceHolder1" runat="Server"> <asp:Label ID="UserIDValue" runat="server" Visible="false"></asp:Label><br /> <asp:Label ID="lblLocalDate" runat="server" Visible="false"></asp:Label><br /> <%-- Check if user has previously recorded an upload--%> <asp:SqlDataSource ID="SqlCheckPreviousUpload" runat="server" ConnectionString="<%$ ConnectionStrings:New_ASPNET %>" SelectCommand="SELECT [UserID], [FirstName], [LastName], [ExamCorpComplete] FROM [UserInfo] WHERE ([UserID] = @UserID)"> <SelectParameters> <asp:ControlParameter ControlID="UserIDValue" Name="UserID" PropertyName="Text" /> </SelectParameters> </asp:SqlDataSource> <asp:DetailsView ID="dvPreviousUploadDate" runat="server" Height="50px" Width="364px" AutoGenerateRows="False" DataKeyNames="UserID" DataSourceID="SqlCheckPreviousUpload"> <Fields> <asp:BoundField DataField="UserID" HeaderText="UserID" ReadOnly="True" SortExpression="UserID" /> <asp:BoundField DataField="FirstName" HeaderText="FirstName" SortExpression="FirstName" /> <asp:BoundField DataField="LastName" HeaderText="LastName" SortExpression="LastName" /> <asp:BoundField DataField="ExamCorpComplete" HeaderText="ExamCorpComplete" SortExpression="ExamCorpComplete" /> </Fields> </asp:DetailsView> <%--Display different panels--%> <asp:Panel ID="pnPreviouslyCompleted" runat="server" Visible="False"> Your confirmation has already been processed.</asp:Panel> <asp:Panel ID="pnConfirmationNeeded" runat="server" Visible="False"> Please confirm you have Completed the Self-Assessment . This is required to ensure your assessment will be available to you in the live session. <asp:Button ID="btnCompleted" runat="server" Text="Self Assessement Completed" Visible="False" /><br /> <br /> </asp:Panel> <%-- Fire sql with btnComplete--%> <asp:SqlDataSource ID="SqlDataSource_ExamComplete" runat="server" ConnectionString="<%$ ConnectionStrings:New_ASPNET %>" UpdateCommand="UPDATE [UserInfo] SET [ExamCorpComplete] = @ExamCorpComplete WHERE [UserID] = @UserID"> <UpdateParameters> <asp:ControlParameter Name="ExamCorpComplete" ControlID="lblLocalDate" PropertyName="Text" /> <asp:ControlParameter ControlID="UserIDValue" Name="UserID" PropertyName="Text" /> </UpdateParameters> </asp:SqlDataSource> <%-- Reserved for error messages --%> <asp:Label ID="lblMsg1" runat="server" Visible="False" ForeColor="#FF3300" Font-Bold="True"></asp:Label> <asp:Label ID="lblMsg2" runat="server" Visible="False" BorderStyle="None" CssClass="MessageBox"></asp:Label> <asp:Label ID="lblMsb3" runat="server" Visible="False"></asp:Label> </asp:Content>Partial Class psb_ExamUploaded Inherits System.Web.UI.Page 'Get Date/Time from DetTime.vb Dim LocalDate As Date = LocalTime.WriteDateTime() Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load 'Check for UserName If Context.User.Identity.Name Is Nothing Or IsDBNull(Context.User.Identity.Name) Or Context.User.Identity.Name = String.Empty Then Response.Redirect("~/psb/Login2.aspx") End If UserIDValue.Text = Membership.GetUser().ProviderUserKey.ToString() lblLocalDate.Text = LocalDate End Sub Protected Sub SqlCheckPreviousUpload_Selected(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlCheckPreviousUpload.Selected If IsDBNull(SqlDataSource_ExamComplete.FindControl("ExamCorpComplete")) Then pnConfirmationNeeded.Visible = True Else pnPreviouslyCompleted.Visible = True End If End Sub Protected Sub btnCompleted_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btnCompleted.Click Try SqlDataSource_ExamComplete.Update() Catch exception As Exception lblMsg1.Visible = True lblMsg1.Text = "ExamCompletion Failed, wait a few minutes and try again. If fails again, please contact us" Return Finally lblMsg2.Visible = True lblMsg2.Text = "After your results from the self assessment have been verified, an email will be sent to you within 2 days confirming your confidential results will be available at the live session " End Try End Sub End Classjohn1506
Member
319 Points
448 Posts
Re: Check for NULL in SqlSataSource
May 06, 2011 09:30 PM|LINK
One additional note, the field I am trying to check for NULL is a dateTime field. Is 'isDBNull' the proper way to check? The following is not chatching nulls:
Protected Sub SqlCheckPreviousUpload_Selected(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlCheckPreviousUpload.Selected If IsDBNull(SqlDataSource_ExamComplete.FindControl("ExamCorpComplete")) Then pnConfirmationNeeded.Visible = True Else pnPreviouslyCompleted.Visible = True End If End SubThanks
John
john1506
Member
319 Points
448 Posts
Re: Check for NULL in SqlSataSource
May 08, 2011 01:02 PM|LINK
I will better define my problem and repost.
cyberbud
Contributor
3298 Points
551 Posts
Re: Check for NULL in SqlSataSource
May 08, 2011 02:51 PM|LINK
I think I'm able to identify your problem. Please try this and let me know.
Protected Sub SqlCheckPreviousUpload_Selected(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlCheckPreviousUpload.Selected int completed=0; DataView dv = SqlDataSource_ExamComplete.Select(DataSourceSelectArguments.Empty) as DataView; if (dv != null) { DataRowView drv = dv[0] as DataRowView; if (drv != null && drv["ExamCorpComplete"] != null) { completed=(int)drv["ExamCorpComplete"] ; //I'm assuming that your ExamCorpComplete field is integer type } } If completed<=0 Then pnConfirmationNeeded.Visible = True Else pnPreviouslyCompleted.Visible = True End If End SubPlease forgive me for c# code here.
Don't forget to convert this logic into vb and try it.
MCP(.net 3.5)
Nepal
ishwor.cyberbudsonline.com
john1506
Member
319 Points
448 Posts
Re: Check for NULL in SqlSataSource
May 08, 2011 09:22 PM|LINK
This may be a duplicate reply, but, first the ExamCorpComplete field is a Date field, this is causing a problem and I don't know how to address it. The vb code is:
Protected Sub dvUploadDate_DataBound(ByVal sender As Object, ByVal e As System.EventArgs) Handles dvUploadDate.DataBound Dim completed As Integer = 0 Dim dv As DataView = TryCast(SqlDataSource_ExamComplete.[Select](DataSourceSelectArguments.Empty), DataView) If dv IsNot Nothing Then Dim drv As DataRowView = TryCast(dv(0), DataRowView) If drv IsNot Nothing AndAlso drv("ExamCorpComplete") IsNot Nothing Then completed = CInt(drv("ExamCorpComplete")) End If End If If completed <= 0 Then pnConfirmationNeeded.Visible = True Else pnPreviouslyCompleted.Visible = True End If End Sub End ClassThanks for taking the time
John
cyberbud
Contributor
3298 Points
551 Posts
Re: Check for NULL in SqlSataSource
May 09, 2011 04:17 AM|LINK
I guess now you are near to what you are trying to achieve.
Protected Sub dvUploadDate_DataBound(ByVal sender As Object, ByVal e As System.EventArgs) Handles dvUploadDate.DataBound Dim completed As Boolean= False Dim dv As DataView = TryCast(SqlDataSource_ExamComplete.[Select](DataSourceSelectArguments.Empty), DataView) If dv IsNot Nothing Then Dim drv As DataRowView = TryCast(dv(0), DataRowView) If drv IsNot Nothing AndAlso drv("ExamCorpComplete") IsNot Nothing Then completed = True 'I'm assuming that if examcorpcomplete field has any date value then it's complete. You may wanna change the above criteria to validate against some default date value that is set which indicates that the exam hasn't been completed. End If End If If completed =FalseThen pnConfirmationNeeded.Visible = True Else pnPreviouslyCompleted.Visible = True End If End Sub End ClassMCP(.net 3.5)
Nepal
ishwor.cyberbudsonline.com