Implicit conversion from data type datetime to int

[7effrey]

Hi, I'm trying to retrieve some information from my database, but somehow i get this error:

Behind code:

public void binddata()
    {
        SqlConnection con = new SqlConnection(ConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString);
              DataSet ds = new DataSet();

        SqlDataAdapter da = new SqlDataAdapter("getRoomNEquip2", con);
        da.SelectCommand.CommandType = CommandType.StoredProcedure;
         da.SelectCommand.Parameters.Add("@Location", SqlDbType.VarChar, 50);

        da.SelectCommand.Parameters["@Location"].Value = DropDownLocation.SelectedItem.Text;


        da.Fill(ds);
        
        ds.Relations.Add("myRelation",
            ds.Tables[0].Columns["ID"],
            ds.Tables[1].Columns["ID"]);

        MeetRoomList.DataSource = ds.Tables[0];
        MeetRoomList.DataBind();

    }

this is my stored procedure:

ALTER PROCEDURE [dbo].[Getroomnequip2] 
	@Location VARCHAR(50)
AS 

  BEGIN 
      CREATE TABLE #temp 
        ( 
           name          VARCHAR(50), 
           id            INT, 
           location      VARCHAR(50), 
           fk_type       VARCHAR(10), 
           fk_equipments INT, 
           TYPE          VARCHAR(10), 
           fk_meetroom   INT, 
           status        CHAR(1), 
           note          TEXT, 
		   DATE			 DATETIME,
           fk_user       INT, 
           reg_date      DATETIME, 
        ) 

      INSERT INTO #temp 
      SELECT roomndato.mrn     AS name, 
             a.id, 
             a.location, 
             a.fk_type, 
             b.fk_equipments, 
             b.TYPE, 
             c.fk_meetroom, 
             c.status, 
             c.note, 
             c.fk_user, 
             c.reg_date, 
             roomndato.maxdate AS DATE 
      FROM   (meetroom AS a 
              FULL JOIN meetroomtypes AS b 
                ON a.fk_type = b.TYPE 
              FULL JOIN equipmentstatus AS c 
                ON b.fk_equipments = c.fk_equipments 
                   AND a.id = c.fk_meetroom) 
             LEFT JOIN (SELECT DISTINCT a.name      AS mrn, 
                                        MAX(c.DATE) AS maxdate 
                        FROM   (meetroom AS a 
                                FULL JOIN meetroomtypes AS b 
                                  ON a.fk_type = b.TYPE 
                                FULL JOIN equipmentstatus AS c 
                                  ON b.fk_equipments = c.fk_equipments 
                                     AND a.id = c.fk_meetroom ) 
                        GROUP  BY a.name) AS roomndato 
               ON a.name = roomndato.mrn 
                  AND c.DATE = roomndato.maxdate 
      WHERE  location = @Location 
      ORDER  BY name, 
                DATE DESC 

      SELECT DISTINCT name, 
                      id, 
                      TYPE 
      FROM   #temp 

      SELECT * 
      FROM   #temp 
  END 

DROP TABLE #temp

I really dont understand why i get the error, hope somebody can help me please

[tlhintoq]

The error message pretty much says it all

error: Implicit conversion from data type datetime to int

Someplace you have an int and you are trying to implicitly convert it to a datetime and the OS doesn't know how to do that.

If you are storing a date as an int in your database you will need to expressly tell your program how to convert it back to a datetime object.

[7effrey]

i know what it means, but the thing is im not trying to convert anything. im just trying to read some data from the database. All my date fields are of the type datetime, and i dont understand why it says im trying to load it as an int.. how can i find out where the problem is?

I think the problem is in my stored procedure

[7effrey]

I found the error. Apparently you must name the table content in the same order as you select them

CREATE TABLE #temp 
        ( 
			 name varchar(50),
			 id , 
             location varchar(50), 
             fk_type varchar(10), 
             fk_equipments int, 
             TYPE varchar(10), 
             fk_meetroom int, 
             status char(1), 
             note text, 
             fk_user int, 
             reg_date DateTime,
             date DateTime, 
        ) 

      INSERT INTO #temp
      SELECT roomndato.mrn     AS name, 
             a.id, 
             a.location, 
             a.fk_type, 
             b.fk_equipments, 
             b.TYPE, 
             c.fk_meetroom, 
             c.status, 
             c.note, 
             c.fk_user, 
             c.reg_date,

[Frinavale]

Hmm I would have said that to fix this problem you should use Explicit Casting when reading the values retrieved from the database. If you are expecting an Int, then specify that using explicit casting.

-Frinny