DateTime parameter issue

Question

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.

Remove the try/catch block (that's a bad technique) and please explain exactly what the problem is. — senfo
– senfo, Commented Jun 12, 2012 at 12:24
To further diagnose your issue, I'd have to see the stored proc and what values you're passing into the proc. — Chris Gessler
– Chris Gessler, Commented Jun 12, 2012 at 12:38
Could it be something as simple as setting com.CommandType = CommandType.StoredProcedure? You say that the ExecuteNonQuery doesn't run. Do you mean that the line of code isn't executed? Or that the query is run but doesn't do anything? Also, if the data type in SQL is DateTime, then you probably don't want to pass a string. — Jim Mischel
– Jim Mischel, Commented Jun 12, 2012 at 13:25

Chris Gessler · Accepted Answer · 2012-06-12 13:18:11Z

4

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.

edited Jun 12, 2012 at 13:18

answered Jun 12, 2012 at 12:26

Chris Gessler

23.2k8 gold badges59 silver badges86 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Anna T Over a year ago

Thank you Chris! I must say that I've been reusing the same variable for parameters in the past similarly and it works. I still think this is related to handling /setting a special type parameter (date type). I added the specifics in the initial question and requested. Good day.

Damien_The_Unbeliever Over a year ago

+1 for the edit at the end - it's treating datetimes as strings that gets people into these kind of messes.

Damien_The_Unbeliever Over a year ago

@AnnaT - You should also apply @PhaDaPhunk's answer, and change the CommandType of your com object.

phadaphunk · Accepted Answer · 2012-06-12 15:13:47Z

2

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]);
       }

edited Jun 12, 2012 at 15:13

answered Jun 12, 2012 at 14:10

phadaphunk

13.4k17 gold badges77 silver badges109 bronze badges

2 Comments

phadaphunk Over a year ago

In your exemple you Exec with an empty Date " ".

Anna T Over a year ago

Thanks very much, PhaDaPhunk, I did add the com.CommandType = CommandType.StoredProcedure; line - but inside GenericDataAccess.CreateCommand(), which method definition I didn't include here for simplicity, but great idea! As for your second suggestion, it sounds interesting but I was unsure exactly where to place that.

Ignacio Gómez · Accepted Answer · 2012-06-12 13:29:57Z

0

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

edited Jun 12, 2012 at 13:29

answered Jun 12, 2012 at 12:27

Ignacio Gómez

1,6374 gold badges24 silver badges41 bronze badges

2 Comments

Anna T Over a year ago

Thanks nachogsiri! I can't unfortunately use this, because in my situation the parameter is specified as linked/associated with a certain command --> DbParameter param = commandx.CreateParameter(); whereas in your code, that doesn't show. So I checked the DbCommand class and I haven't found an SqLiteParameter method there.

Ignacio Gómez Over a year ago

My bad, thought you were in SQLite. Bad reading, I will modify that.

Collectives™ on Stack Overflow

DateTime parameter issue

3 Answers 3

3 Comments

EDIT :

2 Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

3 Comments

EDIT :

2 Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related