Method Returning only " "

Question

I need return string of DateTime. More this method is returning only null receives an input string, convert to DateTime and i need put DateTime on valor.retorna_date_time string variable.

Code:

   public void Search_DATE(string param_date)
    {
        SqlDataReader objReader;
        SqlCommand objcmd = null;

        vsql = "SELECT [IDCADASTRO],[RGP],[PEIXE],[PESO],[QUANTIDADE],[DATA_REGISTRO] FROM cadastro WHERE DATA_REGISTRO LIKE @DATA_REGISTRO";

        if (this.Conectar())
        {
            try
            {
                DateTime dtParam = DateTime.Parse(param_date);

                objcmd = new SqlCommand(vsql, objCon);
                objcmd.Parameters.Add(new SqlParameter("@DATA_REGISTRO", dtParam));
                objReader = objcmd.ExecuteReader();

                if (objReader.Read())
                {

                    valor.retorna_date_time = objReader.GetString(6);

                }

            }
            catch (SqlException erro)
            {
                throw erro;
            }
            finally
            {
                this.Desconectar();
            }


        }


    }

Input Parameters:

DateTime myDateTime = DateTime.Now;
string DateTimesql = myDateTime.ToString("dd-MM-yyyy");

objSQL.Search_DATE(DateTimesql);

valor.retorna_date_time is a global string variable.

You should also not `throw erro;` but instead just `throw;` inside the `catch` block. This preserves the entire stack trace and exception details. — Der Kommissar, Jul 21 '15 at 18:26
What's your table look like? It's odd to me your parameter name is the same as your column name. — ragerory, Jul 21 '15 at 18:29

score 4 · Answer 1 · answered Jul 21 '15 at 18:21

4

Your SELECT statement returns 6 columns:

[IDCADASTRO],[RGP],[PEIXE],[PESO],[QUANTIDADE],[DATA_REGISTRO]

But IDatareader's GetString(n) method is 0-based, so GetString(6) returns the 7th column, which there isn't.

Change it to GetString(5).

answered Jul 21 '15 at 18:21

Dai

141,631
28
261
374

Nathan · Answer 2 · 2015-07-21T18:50:43.573

0

Since you only seem to need just the last column, you can change your query to:

"SELECT [DATA_REGISTRO] FROM cadastro WHERE DATA_REGISTRO LIKE @DATA_REGISTRO";

And then read it using ExecuteScalar

objcmd = new SqlCommand(vsql, objCon);
objcmd.Parameters.AddWithValue("@DATA_REGISTRO", dtParam);
valor.retorna_date_time = objcmd.ExecuteScalar().ToString();

EDIT:

ExecuteScalar() will return a null reference if the query did not return anything.

You should check if it is null before converting it to string and passing it to valor.retorna_date_time. Do something like:

string returnValue = objcmd.ExecuteScalar() == null ?? String.Empty : objcmd.ExecuteScalar().ToString();

edited Jul 21 '15 at 18:50

answered Jul 21 '15 at 18:27

Nathan

1,220
3
15
26

Error 2 Cannot implicitly convert type 'object' to 'string'. An explicit conversion exists (are you missing a cast?) This error now. – Matheus Saviczki Jul 21 '15 at 18:31
`objcmd.ExecuteScalar().ToString();` See the edited post. – Nathan Jul 21 '15 at 18:32
System.NullReferenceException Object reference not set to an instance of an object. – Matheus Saviczki Jul 21 '15 at 18:37
I don't think that was caused by my answer. You need to revert all your changes before applying any of our suggestions. – Nathan Jul 21 '15 at 18:38
I am making changes in everything according to your changes. – Matheus Saviczki Jul 21 '15 at 18:40
Okay. My bad. The `ExceuteScalar()` method may be returning a null. First, check if the query works (run it on SSMS). Second, do a null check on the `ExecuteScalar()` method before assigning it to `valor.retorna_date_time`. Refer to this post on how to do that: http://stackoverflow.com/questions/3987618/how-to-do-tostring-for-a-possibly-null-object – Nathan Jul 21 '15 at 18:43
Hey I got it. I Change on query LIKE to = and get valor.retorna_date_time = objReader.GetDateTime(0).ToString() ; – Matheus Saviczki Jul 21 '15 at 18:47
Ahhh! That `LIKE` was the reason it wasn't returning anything. – Nathan Jul 21 '15 at 19:01

score 0 · Answer 3 · answered Jul 21 '15 at 18:29

0

Without seeing your table structure, I would try GetOrdinal.

Change

valor.retorna_date_time = objReader.GetString(6)

to

valor.retorna_date_time = objReader.GetOrdinal("DATA_REGISTRO");

answered Jul 21 '15 at 18:29

ragerory

1,360
1
8
27

Its not working too. the table struct is [IDCADASTRO],[RGP] = int,[PEIXE] = varchar,[PESO] = float,[QUANTIDADE] = int,[DATA_REGISTRO] = Date – Matheus Saviczki Jul 21 '15 at 18:33

Method Returning only " "

3 Answers3