Inseting nothing into date field

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)"

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

Using...

query = query & "NULL"

Did not fix the problem?

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.

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 '

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?

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).

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

Ok, im gonna try using parameters...

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.

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

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 

Select allOpen in new window

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

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

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

 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 

Select allOpen in new window

Ark you nailed it, pal.

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 = ...

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

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

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 :(

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.

Thank you sir.

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

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.

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

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