Avatar of deanlee17
deanlee17
 asked on

Inseting nothing into date field

Hi All,

I have the following code, works fine when the user adds a date but if they dont I get .....

String was not recognized as a valid DateTime


            If String.IsNullOrEmpty(FlxHeader(FlxHeader.SelectedIndex, 4).ToString) Then
                querypart = ""
            Else
                querypart = Format(Convert.ToDateTime(FlxHeader(FlxHeader.SelectedIndex, 4).Text), "yyyy-MM-dd").ToString
            End If

How can I get around this? The SQL server database is set to allow nulls.

Thanks
Visual Basic.NET.NET Programming

Avatar of undefined
Last Comment
Cluskitt

8/22/2022 - Mon
Cluskitt

I'm assuming you're sending your query using concatenation. If that is the case, then simply, instead of sending an empty string, send a null string. For example:
query = "INSERT INTO MyTable VALUES ("
If String.IsNullOrEmpty(FlxHeader(FlxHeader.SelectedIndex, 4).ToString) Then
                query = query & "NULL"
            Else
                query = query & "'" & Format(Convert.ToDateTime(FlxHeader(FlxHeader.SelectedIndex, 4).Text), "yyyy-MM-dd").ToString & "'"
            End If
query = query & ",OtherValues)"
deanlee17

ASKER
Not concatenation no. Sorry I maybe should have posted my complete code...

  Dim sqlConnection1 As New System.Data.SqlClient.SqlConnection("XXX")

        Dim cmd As New System.Data.SqlClient.SqlCommand
        cmd.CommandType = System.Data.CommandType.Text

Try

  If String.IsNullOrEmpty(FlxHeader(FlxHeader.SelectedIndex, 4).ToString) Then

                querypart = ""
            Else
                querypart = Format(Convert.ToDateTime(FlxHeader(FlxHeader.SelectedIndex, 4).Text), "yyyy-MM-dd").ToString
  End If

    cmd.CommandText = "INSERT INTO WPFtblSmartQuoteHeader(AstuteRef, DateRef) VALUES('" & FlxHeader(FlxHeader.SelectedIndex, 8).Text & "','" & querypart & "')"


            cmd.Connection = sqlConnection1

            sqlConnection1.Open()
            cmd.ExecuteNonQuery()
            sqlConnection1.Close()



        Catch Ex As Exception
            MsgBox(Ex.Message)
        End Try
deanlee17

ASKER
Using...

query = query & "NULL"

Did not fix the problem?
Experts Exchange has (a) saved my job multiple times, (b) saved me hours, days, and even weeks of work, and often (c) makes me look like a superhero! This place is MAGIC!
Walt Forbes
Cluskitt

Actually, you are using concatenation. You would be better off using parameters, but using your code, you can do:

Try

  If String.IsNullOrEmpty(FlxHeader(FlxHeader.SelectedIndex, 4).ToString) Then

                querypart = "NULL"
            Else
                querypart = "'" & Format(Convert.ToDateTime(FlxHeader(FlxHeader.SelectedIndex, 4).Text), "yyyy-MM-dd").ToString & "'"
  End If

    cmd.CommandText = "INSERT INTO WPFtblSmartQuoteHeader(AstuteRef, DateRef) VALUES('" & FlxHeader(FlxHeader.SelectedIndex, 8).Text & "'," & querypart & ")"

This will insert either (for example) '2012-07-02' or NULL (without ')

If you change to parameters, you will need to use DBNull.Value instead.
Cluskitt

Notice that you have to remove the ' from the CommandText and add it only to the variable if it's a date. If not, you have to insert NULL without any '
deanlee17

ASKER
Excellent and thanks for the pointers. However its still throwing the same error, for some reason.

This is WPF, but surely that makes no difference?
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Cluskitt

I don't think it should. Did you copy my CommandText modification as well? You have to remove the ' that you had on the date part (since you're already providing them on the variable). If not, the NULL will be sent as a string instead (and the date will have 2 ', causing the code not to work even with valid dates).
ASKER CERTIFIED SOLUTION
Ark

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
deanlee17

ASKER
Yup im 100% with you and copied it exactly, ommitting the ' 's
deanlee17

ASKER
Ok, im gonna try using parameters...
I started with Experts Exchange in 2004 and it's been a mainstay of my professional computing life since. It helped me launch a career as a programmer / Oracle data analyst
William Peck
Cluskitt

I personally prefer to use:
cmd.Parameters.Add("@val2", SqlDbType.VarChar, 10).Value = ...
This way, you specify which type of variable is to be inserted and VB itself performs the conversion when applicable.
deanlee17

ASKER
Ok its highlighted every @ sign and said 'experssion expected?
Ark

If u'r 100% sure about field types and you VB types match - let VB convert system.types to SQL types. BTW, in your query you can validate date prior:
cmd.CommandText = "INSERT INTO BlahBlah(Field1, Field2) VALUES(@val1,@val2)"

cmd.Parameters.AddWithValue(@val1,FlxHeader(FlxHeader.SelectedIndex, 8).Text)

Dim dt As date
If Date.TryParse(FlxHeader(FlxHeader.SelectedIndex, 4).Text,dt) Then
     cmd.Parameters.AddWithValue(@val2, dt)
Else 'Incorrect string - either empty or non-date
    cmd.Parameters.AddWithValue(@val2, DBNull.Value)
End If

cmd.executenonquery 

Open in new window

Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Cluskitt

You are using VB.Net, aren't you? Are you using Visual Studio? The various code samples provided should work correctly, one way or the other.
Do you have these at the top of the code behind file?:
Imports System.Data
Imports System.Data.SqlClient
Ark

Oops, sorry
Of course cmd.Parameters.AddWithValue("@val2", DBNull.Value) 'see quotes around @val2
deanlee17

ASKER
Cluskitt: I am using visual studio and I am importing both of those files already.

Ark, I had added the quotes after i posed. It inserts a date value fine, but is its blank it says...

Conversion from type string "" to type 'Date' is not valid
All of life is about relationships, and EE has made a viirtual community a real community. It lifts everyone's boat
William Peck
Ark

 Dim sqlConnection1 As New System.Data.SqlClient.SqlConnection("XXX")

    Dim cmd As New System.Data.SqlClient.SqlCommand
    cmd.CommandType = System.Data.CommandType.Text
    cmd.CommandText = "INSERT INTO WPFtblSmartQuoteHeader(AstuteRef, DateRef) VALUES(@val1,@val2)"
    cmd.Parameters.AddWithValue("@val1",FlxHeader(FlxHeader.SelectedIndex, 8).Text)
Try
    Dim dt As date
    If Date.TryParse(FlxHeader(FlxHeader.SelectedIndex, 4).Text,dt) Then
         cmd.Parameters.AddWithValue("@val2", dt)
    Else 'Incorrect string - either empty or non-date
        cmd.Parameters.AddWithValue("@val2", DBNull.Value)
    End If
    cmd.Connection = sqlConnection1

    sqlConnection1.Open()
    cmd.ExecuteNonQuery()
    sqlConnection1.Close()

    Catch Ex As Exception
            MsgBox(Ex.Message)
        End Try 

Open in new window

deanlee17

ASKER
Ark you nailed it, pal.
deanlee17

ASKER
Ok so apparently you use parameters as its more secure, so it that only the case when you specify val properties? such as...

cmd.Parameters.Add("@val2", SqlDbType.VarChar, 10).Value = ...
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Ark

cmd.Parameters.AddWithValue let VB convert System.Type into appropriate SQL type
cmd.Parameters.Add("@val2", SqlDbType.VarChar, 10).Value =... explicitly set the SQL type
So,
cmd.Parameters.AddWithValue(@x,"some long string") will be converted to nvarchar(MAX) and then SQL server will truncate it to appropriate size (or throw exception)
cmd.Parameters.Add("@val2", SqlDbType.DateTime).Value ="Not a time" - will throw exception from within VB
deanlee17

ASKER
Ok excellent, so the very nature of using parameters ensures a certain level of security because it passes parameters and not string literals?
deanlee17

ASKER
Very last question, if i wanted to add a set date instead of null, how would i do this,

I tried replacing

'cmd.Parameters.AddWithValue("@val4", DBNull.Value)

with

cmd.Parameters.AddWithValue("@val4", "1900-01-01", dt)

but error :(
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.
rwheeler23
Cluskitt

remove the dt. Just cmd.Parameters.AddWithValue("@val4", "1900-01-01")

The biggest advantage of using parameters is that, if you use a ' in the string, the code itself replaces it into a ''. That way, if you try to send the value:
some value I write');drop table1;drop table2;
You'll get:
insert into table1 values ('some value I write');drop table1;drop table2;')

Using concatenation, this would let the user drop a couple tables or even do worse things. Using parameters, what would happen is:
insert into table1 values ('some value I write'');drop table1;drop table2;')
Notice that the ' was doubled and now the whole line is a string. Code isn't executed this way.
deanlee17

ASKER
Thank you sir.

Are you guys happy with split points? As you have both been so useful!
Cluskitt

You should accept as answer the first complete solution to your problem. Then, if you want, you can also accept as assist any useful information that was provided to you. I don't mind not receiving points, as long as you have a working solution.
Get an unlimited membership to EE for less than $4 a week.
Unlimited question asking, solutions, articles and more.
Ark

Thanks for points, glad I could help. Just a comment:
remove the dt. Just cmd.Parameters.AddWithValue("@val4", "1900-01-01")
dt is important!. AddWithValue converts System.Type to SqlDbType, so "1900-01-01" will be converted to nvarchar. Then SQL server check field data type and try cast string to DateTime. Though most servers accepts yyyy-MM-dd format, but in some cases they may be configured to specific locale and not accept it. Proof solution is to use SQL CONVERT or CAST functions to convert string to date. But when u'r using dt as value, parameter is passing directly as DateTime.

Regards
Ark
Cluskitt

I wasn't aware of that. I never use AddWithValue. Also, I never use yyyy-MM-dd. To make sure it is always compatible, I always use yyyyMMdd