Dataset problem, please help

I am trying to load data from my database through a linq command

here is my code

DataTable dtBooking = new DataTable();

dtBooking = (GetGeneric.GetInstance<AdminDriverHelper>().GetDriversHolidays(""));

//dtBooking.Columns.Add("DriverID", Type.GetType("System.Int64"));

//dtBooking.Columns.Add("DriverDate", Type.GetType("System.DateTime"));

//dtBooking.Columns.Add("DriverName", Type.GetType("System.String"));

//int idCount = 1;

for (int i = 1; i <= 5; i++)

{

DataRow dr;

dr = dtBooking.NewRow();

dr["DriverID"] = dtBooking.Rows[0]["DriverID"];

dr["DriverName"] = dtBooking.Rows[0]["DriverName"];

dr["DriverDate"] = dtBooking.Rows[0]["DriverDate"];

dtBooking.Rows.Add(dr);

}

return dtBooking;

}

but I get this error

System.ArgumentException: Column '' does not belong to table Table1.

I am sure its is how I am calling the sproc, but I am unsure

Can anyone help?

Re: Dataset problem, please help

dag78

for (int i = 1; i <= 5; i++)

        {

            DataRow dr;

            dr = dtBooking.NewRow();

            dr["DriverID"] = dtBooking.Rows[0]["DriverID"];

            dr["DriverName"] = dtBooking.Rows[0]["DriverName"];

            dr["DriverDate"] = dtBooking.Rows[0]["DriverDate"];



            dtBooking.Rows.Add(dr);

        }

You are already getting the data into the datatable dtBooking. What is the necessity of looping through it and adding new rows again? I guess that loop is redundant..

Re: Dataset problem, please help

so should I just have this?

DataTable dtBooking = new DataTable();

dtBooking = (GetGeneric.GetInstance<AdminDriverHelper>().GetDriversHolidays(""));

return dtBooking;

if so how does it loop through all results?

Re: Dataset problem, please help

If you are getting all records in a datatable from GetDriversHolidays function, you should just return the datatable.

In case if you want to use a loop to iterate datatable rows, you can do it this way

foreach (DataRow row in dtBooking.Rows)
{
    //all values in the current row
    object[] values = row.ItemArray;
}

Re: Dataset problem, please help

Now I ge tthis error

My full code to follow

Column '' does not belong to table Table1.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.ArgumentException: Column '' does not belong to table Table1.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Re: Dataset problem, please help

page load

protected void Page_Load(object sender, EventArgs e)
    {
        try
        {
            if (!Page.IsPostBack)
            {
                SortStr = "";
                GetDrivers();
            }
        }
        catch { }

        Calendar1.EventStartDateColumnName = "DriverDate";
        Calendar1.EventEndDateColumnName = "DriverDate";
        Calendar1.EventHeaderColumnName = "DriverName";

        Calendar1.EventSource = GetEvents();
    }

function

private DataTable GetEvents()
    {

        DataTable dtBooking = new DataTable();
        dtBooking = (GetGeneric.GetInstance<AdminDriverHelper>().GetDriversHolidays(""));

        foreach (DataRow row in dtBooking.Rows)
        {
            //all values in the current row
            object[] values = row.ItemArray;
        }

        return dtBooking;
    }

aspx page code

<%@ Register TagPrefix="ECalendar" Namespace="ExtendedControls" Assembly="EventCalendar" %>

<ECalendar:EventCalendar ID="Calendar1" runat="server" BackColor="White" BorderColor="Silver"
            BorderWidth="1px" Font-Names="Verdana"
            Font-Size="9pt" ForeColor="Black" Height="500px"
            Width="100%" FirstDayOfWeek="Monday" NextMonthText="Next >" PrevMonthText="< Prev" SelectionMode="DayWeekMonth" ShowGridLines="True" NextPrevFormat="ShortMonth"
            ShowDescriptionAsToolTip="True" BorderStyle="Solid" EventDateColumnName="" EventHeaderColumnName="" >
            <SelectedDayStyle BackColor="#333399" ForeColor="White" />
            <TodayDayStyle BackColor="#CCCCCC" />
            <SelectorStyle BorderColor="#404040" BorderStyle="Solid" />
            <DayStyle HorizontalAlign="Left" VerticalAlign="Top" Wrap="True" />
            <OtherMonthDayStyle ForeColor="#999999" />
            <NextPrevStyle Font-Size="8pt" ForeColor="#333333" Font-Bold="True" VerticalAlign="Bottom" />
            <DayHeaderStyle BorderWidth="1px" Font-Bold="True" Font-Size="8pt" />
            <TitleStyle BackColor="White" BorderColor="Black" BorderWidth="4px" Font-Bold="True"
                Font-Size="12pt" ForeColor="#333399" HorizontalAlign="Center" VerticalAlign="Middle" />
        </ECalendar:EventCalendar>

its been driving me nuts for 3 days now

Re: Dataset problem, please help

Im guessing I may not be getting any data

Re: Dataset problem, please help

dag78

if so how does it loop through all results?

Hello :)

You can try this

foreach(DataRow row in dt.Rows)

{

foreach(DataColumn c in dt.Columns)

{

//Do thisrow[c]

}

Re: Dataset problem, please help

Slight Progress

this code

private DataTable GetEvents()
    {
       

        DataTable dt = new DataTable();
        dt = GetGeneric.GetInstance<AdminDriverHelper>().GetDriversHolidays("");

        dt.Columns.Add("Id", Type.GetType("System.Int64"));
        dt.Columns.Add("EventStartDate", Type.GetType("System.DateTime"));
        dt.Columns.Add("EventEndDate", Type.GetType("System.DateTime"));
        dt.Columns.Add("EventHeader", Type.GetType("System.String"));

        // int idCount = 1;
        DataRow dr;

        for (int i = dt.Rows.Count - 1; i >= 0; i--)
        {
            DataRow row = dt.Rows[i];
            dr = dt.NewRow();
            dr["Id"] = Convert.ToInt64(dt.Rows[i]["DriverID"]);
            dr["EventStartDate"] = Convert.ToDateTime(dt.Rows[i]["DriverDate"]);
            dr["EventEndDate"] = Convert.ToDateTime(dt.Rows[i]["DriverDate"]);
            dr["EventHeader"] = Convert.ToString(dt.Rows[i]["DriverName"]);
             dt.Rows.Add(dr);
        }
        return dt;
    }

now gives me this error

Server Error in '/A Davies' Application.

Object cannot be cast from DBNull to other types.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code. 

Exception Details: System.InvalidCastException: Object cannot be cast from DBNull to other types.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.


Stack Trace:

[InvalidCastException: Object cannot be cast from DBNull to other types.]
   System.DBNull.System.IConvertible.ToDateTime(IFormatProvider provider) +56
   System.Convert.ToDateTime(Object value) +32

Re: Dataset problem, please help

dag78
Object cannot be cast from DBNull to other types.

Hello again

Your error means that you are trying to convert a DbNull.Value to another field as its value. That's forbidden and you can do something like this:

for (int i = dt.Rows.Count - 1; i >= 0; i--)
        {
            DataRow row = dt.Rows[i];
            dr = dt.NewRow();
            dr["Id"] = Convert.ToInt64(dt.Rows[i]["DriverID"]==DbNull.Value?"0",dt.Rows[i]["DriverID"]);
            dr["EventStartDate"] = Convert.ToDateTime(dt.Rows[i]["DriverDate"]==DbNull.Value?DateTime.Now:dt.Rows[i]["DriverDate"]);
            dr["EventEndDate"] = Convert.ToDateTime(dt.Rows[i]["DriverDate"]==DbNull.Value?DateTime.Now:dt.Rows[i]["DriverDate"]);
            dr["EventHeader"] = Convert.ToString(dt.Rows[i]["DriverName"]==DbNull.Value?"":dt.Rows[i]["DriverName"]);
             dt.Rows.Add(dr);
        }
All in all, plz make sure that your value to be converted must fit for the type that accepts the value

Re: Dataset problem, please help

using this

DataTable dt = new DataTable();
        dt = GetGeneric.GetInstance<AdminDriverHelper>().GetDriversHolidays("");

        dt.Columns.Add("Id", Type.GetType("System.Int64"));
        dt.Columns.Add("EventStartDate", Type.GetType("System.DateTime"));
        dt.Columns.Add("EventEndDate", Type.GetType("System.DateTime"));
        dt.Columns.Add("EventHeader", Type.GetType("System.String"));

        // int idCount = 1;
        DataRow dr;

        for (int i = dt.Rows.Count - 1; i >= 0; i--)
        {
            DataRow row = dt.Rows[i];
            dr = dt.NewRow();
            dr["Id"] = Convert.ToInt64(dt.Rows[i]["DriverID"]==DBNull.Value?"0":dt.Rows[i]["DriverID"]);
            dr["EventStartDate"] = Convert.ToDateTime(dt.Rows[i]["DriverDate"]==DBNull.Value?DateTime.Now:dt.Rows[i]["DriverDate"]);
            dr["EventEndDate"] = Convert.ToDateTime(dt.Rows[i]["DriverDate"]==DBNull.Value?DateTime.Now:dt.Rows[i]["DriverDate"]);
            dr["EventHeader"] = Convert.ToString(dt.Rows[i]["DriverName"]==DBNull.Value?"":dt.Rows[i]["DriverName"]);
             dt.Rows.Add(dr);
        }

        return dt;

I still get this

Object cannot be cast from DBNull to other types.

Description: 
An unhandled exception occurred during the execution of the current web 
request. Please review the stack trace for more information about the error and 
where it originated in the code. 

Exception Details: 
System.InvalidCastException: Object cannot be cast from DBNull to other 
types.

Source Error:

An unhandled exception was generated during the execution of the 
current web request. Information regarding the origin and location of the 
exception can be identified using the exception stack trace below.

Stack Trace:

[InvalidCastException: Object cannot be cast from DBNull to other types.]
   System.DBNull.System.IConvertible.ToDateTime(IFormatProvider provider) +56
   System.Convert.ToDateTime(Object value) +32
   ExtendedControls.EventCalendar.EventCalendarDayRender(Object sender, DayRenderEventArgs e) +321
   System.Web.UI.WebControls.Calendar.OnDayRender(TableCell cell, CalendarDay day) +385
   System.Web.UI.WebControls.Calendar.RenderDays(HtmlTextWriter writer, DateTime firstDay, DateTime visibleDate, CalendarSelectionMode selectionMode, Boolean buttonsActive, Boolean useAccessibleHeader) +3071
   System.Web.UI.WebControls.Calendar.Render(HtmlTextWriter writer) +1019
   System.Web.UI.Control.RenderControlInternal(HtmlTextWriter writer, ControlAdapter adapter) +27
   System.Web.UI.Control.RenderControl(HtmlTextWriter writer, ControlAdapter adapter) +99
   System.Web.UI.Control.RenderControl(HtmlTextWriter writer) +25

Re: Dataset problem, please help

This is my sproc too

ALTER PROCEDURE [dbo].[usp_GetDriversHoliday]

	@DATE NVARCHAR(12)=NULL

WITH RECOMPILE
	
AS
SET NOCOUNT ON
BEGIN TRY
	SELECT D.DriverID,
				D.HoliDay as DriverDate,
				D.HoliDay as DriverDate2,
				(A1.FName + ' ' + A1.LName) AS DriverName
	FROM tbl_DriverHolidayPlanner AS D INNER JOIN tbl_Driver AS A1
	ON D.DriverID=A1.DriverID

END TRY
BEGIN CATCH
	
END CATCH

Re: Dataset problem, please help

Hello again

Have a try like this:

SELECT          IsNUll(D.DriverID, 0) as DriverId,
                                IsNull(D.HoliDay,getdate()) as DriverDate,
                                IsNull(D.HoliDay,getdate()) as DriverDate2,
                                (A1.FName + ' ' + A1.LName) AS DriverName

Then plz run the select singly to check whether there are some null values. That'll be OK.

Thx

Re: Dataset problem, please help

I still get this and there are no nulls in the DB

Object cannot be cast from DBNull to other types.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidCastException: Object cannot be cast from DBNull to other types.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[InvalidCastException: Object cannot be cast from DBNull to other types.]
   System.DBNull.System.IConvertible.ToDateTime(IFormatProvider provider) +56
   System.Convert.ToDateTime(Object value) +32

Re: Dataset problem, please help

Since You've used what I've saidIsNullHow can you still get Null error???

I don't understand.....:(

Would you mind telling me which statement throws the error???

Thx again.

Re: Dataset problem, please help

I wish I knew - its driving me daft
Ok I have this as my sproc

SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
ALTER PROCEDURE [dbo].[usp_GetDriversHoliday]

	@DATE NVARCHAR(12)=NULL

WITH RECOMPILE
	
AS
SET NOCOUNT ON
BEGIN TRY
SELECT          IsNUll(D.DriverID, 0) as DriverID,
                                IsNull(D.HoliDay,getdate()) as DriverDate,
                                IsNull(D.HoliDay,getdate()) as DriverDate2,
                                (A1.FName + ' ' + A1.LName) AS DriverName
	FROM tbl_DriverHolidayPlanner AS D INNER JOIN tbl_Driver AS A1
	ON D.DriverID=A1.DriverID

END TRY
BEGIN CATCH

This is my code behind code

   protected void Page_Load(object sender, EventArgs e)
    {
        try
        {
            if (!Page.IsPostBack)
            {
                SortStr = "";
                GetDrivers();

            }
        }
        catch { }

        Calendar1.EventStartDateColumnName = "EventStartDate";
        Calendar1.EventEndDateColumnName = "EventEndDate";
        Calendar1.EventHeaderColumnName = "EventHeader";

        Calendar1.EventSource = GetEvents();
    }

    private DataTable GetEvents()
    {


        DataTable dt = new DataTable();
        dt = GetGeneric.GetInstance<AdminDriverHelper>().GetDriversHolidays("");

        dt.Columns.Add("Id", Type.GetType("System.Int64"));
        dt.Columns.Add("EventStartDate", Type.GetType("System.DateTime"));
        dt.Columns.Add("EventEndDate", Type.GetType("System.DateTime"));
        dt.Columns.Add("EventHeader", Type.GetType("System.String"));

        // int idCount = 1;
        DataRow dr;

        for (int i = dt.Rows.Count - 1; i >= 0; i--)
        {
            DataRow row = dt.Rows[i];
            dr = dt.NewRow();
            dr["Id"] = Convert.ToInt64(dt.Rows[i]["DriverID"]==DBNull.Value?"0":dt.Rows[i]["DriverID"]);
            dr["EventStartDate"] = Convert.ToDateTime(dt.Rows[i]["DriverDate"]==DBNull.Value?DateTime.Now:dt.Rows[i]["DriverDate"]);
            dr["EventEndDate"] = Convert.ToDateTime(dt.Rows[i]["DriverDate2"]==DBNull.Value?DateTime.Now:dt.Rows[i]["DriverDate2"]);
            dr["EventHeader"] = Convert.ToString(dt.Rows[i]["DriverName"]==DBNull.Value?"":dt.Rows[i]["DriverName"]);
             dt.Rows.Add(dr);
        }

        return dt;
    }

This is my helper code
    public DataTable GetDriversHolidays(string date)
    {
        ArrayList param = new ArrayList();
        param.Add(date);
        return GetGeneric.GetInstance<DAL>().Return_dt_SPp("usp_GetDriversHoliday", param);
    }

I dont understand why it gives any nulls, as when I run the sproc maually I Get a table of full results

Re: Dataset problem, please help

full stack error

Object cannot be cast from DBNull to other types.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidCastException: Object cannot be cast from DBNull to other types.

Source Error:

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

Stack Trace:

[InvalidCastException: Object cannot be cast from DBNull to other types.]
   System.DBNull.System.IConvertible.ToDateTime(IFormatProvider provider) +56
   System.Convert.ToDateTime(Object value) +32
   ExtendedControls.EventCalendar.EventCalendarDayRender(Object sender, DayRenderEventArgs e) +321
   System.Web.UI.WebControls.Calendar.OnDayRender(TableCell cell, CalendarDay day) +385
   System.Web.UI.WebControls.Calendar.RenderDays(HtmlTextWriter writer, DateTime firstDay, DateTime visibleDate, CalendarSelectionMode selectionMode, Boolean buttonsActive, Boolean useAccessibleHeader) +3071
   System.Web.UI.WebControls.Calendar.Render(HtmlTextWriter writer) +1019
   System.Web.UI.Control.RenderControlInternal(HtmlTextWriter writer, ControlAdapter adapter) +27
   System.Web.UI.Control.RenderControl(HtmlTextWriter writer, ControlAdapter adapter) +99
   System.Web.UI.Control.RenderControl(HtmlTextWriter writer) +25
   System.Web.UI.Control.RenderChildrenInternal(HtmlTextWriter writer, ICollection children) +134
   System.Web.UI.Control.RenderChildren(HtmlTextWriter writer) +19
   System.Web.UI.Control.Render(HtmlTextWriter writer) +10
   System.Web.UI.Control.RenderControlInternal(HtmlTextWriter writer, ControlAdapter adapter) +27
   System.Web.UI.Control.RenderControl(HtmlTextWriter writer, ControlAdapter adapter) +99
   System.Web.UI.Control.RenderControl(HtmlTextWriter writer) +25
   System.Web.UI.Control.RenderChildrenInternal(HtmlTextWriter writer, ICollection children) +134
   System.Web.UI.Control.RenderChildren(HtmlTextWriter writer) +19
   System.Web.UI.Control.Render(HtmlTextWriter writer) +10
   System.Web.UI.Control.RenderControlInternal(HtmlTextWriter writer, ControlAdapter adapter) +27
   System.Web.UI.Control.RenderControl(HtmlTextWriter writer, ControlAdapter adapter) +99
   System.Web.UI.Control.RenderControl(HtmlTextWriter writer) +25
   System.Web.UI.Control.RenderChildrenInternal(HtmlTextWriter writer, ICollection children) +134
   System.Web.UI.Control.RenderChildren(HtmlTextWriter writer) +19
   System.Web.UI.UpdatePanel.RenderChildren(HtmlTextWriter writer) +234
   System.Web.UI.Control.Render(HtmlTextWriter writer) +10
   System.Web.UI.UpdatePanel.Render(HtmlTextWriter writer) +34
   System.Web.UI.Control.RenderControlInternal(HtmlTextWriter writer, ControlAdapter adapter) +27
   System.Web.UI.Control.RenderControl(HtmlTextWriter writer, ControlAdapter adapter) +99
   System.Web.UI.Control.RenderControl(HtmlTextWriter writer) +25
   System.Web.UI.Control.RenderChildrenInternal(HtmlTextWriter writer, ICollection children) +134
   System.Web.UI.Control.RenderChildren(HtmlTextWriter writer) +19
   System.Web.UI.HtmlControls.HtmlForm.RenderChildren(HtmlTextWriter writer) +163
   System.Web.UI.HtmlControls.HtmlContainerControl.Render(HtmlTextWriter writer) +32
   System.Web.UI.HtmlControls.HtmlForm.Render(HtmlTextWriter output) +51
   System.Web.UI.Control.RenderControlInternal(HtmlTextWriter writer, ControlAdapter adapter) +27
   System.Web.UI.Control.RenderControl(HtmlTextWriter writer, ControlAdapter adapter) +99
   System.Web.UI.HtmlControls.HtmlForm.RenderControl(HtmlTextWriter writer) +40
   System.Web.UI.Control.RenderChildrenInternal(HtmlTextWriter writer, ICollection children) +134
   System.Web.UI.Control.RenderChildren(HtmlTextWriter writer) +19
   System.Web.UI.Control.Render(HtmlTextWriter writer) +10
   System.Web.UI.Control.RenderControlInternal(HtmlTextWriter writer, ControlAdapter adapter) +27
   System.Web.UI.Control.RenderControl(HtmlTextWriter writer, ControlAdapter adapter) +99
   System.Web.UI.Control.RenderControl(HtmlTextWriter writer) +25
   System.Web.UI.Control.RenderChildrenInternal(HtmlTextWriter writer, ICollection children) +134
   System.Web.UI.Control.RenderChildren(HtmlTextWriter writer) +19
   System.Web.UI.Page.Render(HtmlTextWriter writer) +29
   System.Web.UI.Control.RenderControlInternal(HtmlTextWriter writer, ControlAdapter adapter) +27
   System.Web.UI.Control.RenderControl(HtmlTextWriter writer, ControlAdapter adapter) +99
   System.Web.UI.Control.RenderControl(HtmlTextWriter writer) +25
   System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1266

Re: Dataset problem, please help

FYI

When I run the sproc I get a return value 0

Is this the root cause?

Re: Dataset problem, please help

Dag78,

How are you converting the List returned by your stored proc to a Datatable? When I convert the List using the code from http://www.c-sharpcorner.com/UploadFile/VIMAL.LAKHERA/LINQResultsetToDatatable06242008042629AM/LINQResultsetToDatatable.aspx

I am able to run your code without issue. Try adding this function and passing your list there to be converting before performaing the datatable operations.

Thanks

Terrell AN

Re: Dataset problem, please help

That is what I am trying to work out

as it must be how I create the list

can you show the code you used to test my example?