CODEWITHSUNDEEP
c#sqlvisual-studio-2008
Anna T
Anna T

Reputation: 1027

DateTime parameter issue

I tested this method for the string parameter alone, and it worked perfectly. So I am sure there is a mistake in setting up the parameter of type DateTime (datapubl), which I added later. Thank you in advance!

By the way, the CatalogCreateFilmsTest sproc was executed and works OK.

Anna

public static bool CreateFilmTest(string nume, string datapubl)
        {
            DbCommand com = GenericDataAccess.CreateCommand();
            com.CommandText = "CatalogCreateFilmTest";

            DbParameter param = com.CreateParameter();
            param.ParameterName = "@nume";
            param.Value = nume;
            param.DbType = DbType.String;
            param.Size = 200;
            com.Parameters.Add(param);

            param = com.CreateParameter();
            param.ParameterName = "@datapubl";
            param.Value = datapubl;
            param.DbType = DbType.DateTime;
            com.Parameters.Add(param);


            int result = -1;

            try
            {
                result = GenericDataAccess.ExecuteNonQuery(com);

            }
            catch
            {
                // 
            }

            return (result >= 1);

        }

EDIT: The problem is the stored procedure is not even executed (it should insert rows into a table but doesn't) . No error, but not the correct result either.

EDIT: Chris, here is the full example:

CREATE PROCEDURE CatalogCreateFilmTest(
@nume nvarchar(1500),
@datapubl datetime
)
AS
INSERT INTO Filme
(nume, datapubl) 
VALUES
(@nume, @datapubl)
;
GO

which works for:

EXEC CatalogCreateFilmTest 'achu', '';

I then call CreateFilmTest like this:

bool success = FilmsAccess.CreateFilmTest(newNume.Text, null);

or:

bool success = FilmsAccess.CreateFilmTest(newNume.Text, DateTime.Now.ToString());

In both cases, the ExecuteNonQuery doesn't run.

Upvotes: 2

Views: 2399

Answers (3)

phadaphunk
phadaphunk

Reputation: 13313

You really should add this line :

    com.CommandType = CommandType.StoredProcedure;

Is it possible that your DateTime is empty when you pass it ?

If it is you should test to make sure it's not and if it is, pass it like this :

    com.Parameters.AddWithValue("@datapubl, emptyDate == null ? DBNull.Value : (object)emptyDate);

Im sure this is the problem.

Hope this will help.

EDIT :

Replace this whole block :

        param = com.CreateParameter();
        param.ParameterName = "@datapubl";
        param.Value = datapubl;
        param.DbType = DbType.DateTime;
        com.Parameters.Add(param);

By something like this :

       if(Date is null)
       {
         com.Parameters.AddWithValue("@datapubl", emptyDate == null ? DBNull.Value (object)emptyDate);
       }
       else
       {
         com.Parameters.AddWithValue("@datapubl", [Your Date Variable Here]);
       }

Upvotes: 2

Ignacio Gómez
Ignacio Gómez

Reputation: 1637

Try this and let me know how it works for you:

    param = new DBParameter("@datapubl", System.Data.DbType.DateTime);
    param.Value = datapubl;
    com.Parameters.Add(param)

Hope it helps

Upvotes: 0

Chris Gessler
Chris Gessler

Reputation: 23123

I don't believe you can reuse the same variable due to the reference in the collection...

        DbParameter param1 = com.CreateParameter(); 
        param1.ParameterName = "@nume"; 
        param1.Value = nume; 
        param1.DbType = DbType.String; 
        param1.Size = 200; 
        com.Parameters.Add(param1); 


        DbParameter param2 = com.CreateParameter(); 
        param2.ParameterName = "@datapubl"; 
        param2.Value = datapubl; 
        param2.DbType = DbType.DateTime; 
        com.Parameters.Add(param2);

Also note, that you can use syntax like so with .net 4 (and maybe 3.5 too).

com.Parameters.Add(
  com.CreateParameter()
  { 
    ParameterName = "@datapubl", 
    Value = datapubl, 
    DbType = DbType.DateTime
  }
);

It also might help to know what the exception is by filling in your catch block:

catch(Exception ex)
{ 
  System.Diagnostics.Debug.WriteLine(ex.Message); 
}

EDIT:

I believe I see the issue now... (and should have seen it before).

Change your method signature to:

public static bool CreateFilmTest(string nume, DateTime datapubl)

And try executing the following:

bool success = FilmsAccess.CreateFilmTest(newNume.Text, DateTime.Now);

To pass null, you'll have to pass Convert.DbNull provided your column allows null.

In order to pass strings as DateTime values, you'll have to format them a specific way, i.e. YYYYMMDD however, I recommend letting the underlying DbCommand object take care of that.

Upvotes: 4

Related Questions