The identifier cannot be an empty string Error

[paulthepaddy]

Well Guys The Title Says it all, im getting the error 'The identifier cannot be an empty string' and i cannot for the life of me work this out.
i am hoping i have just been up to long programming and need to sleep, but i really cant find anything wrong in the code

The Table Does Have An ID field But it is on Auto so as far as i know figure out it cant be the ID column

Dim SQLQuery = <sql>INSERT INTO OrderNumbers (OrderNumber, Dealership_ID, Department_ID, Cars_Reg, Date, Amount, Paid) VALUES (@OrderNumber, @Dealership_ID, @Department_ID , @Cars_Reg, @Date, @Amount, @Paid)"</sql>
Select Case IsConnectionAvailable()
            Case False
                Using Connection As New SqlServerCe.SqlCeConnection(My.Settings.ClientImageConnectionString)
                    Using Command As New SqlServerCe.SqlCeCommand(SQLQuery, Connection)
                        With Command.Parameters
                            .AddWithValue("@OrderNumber", Me.OrderNumber)
                            .AddWithValue("@Dealership_ID", Me.DealerID)
                            .AddWithValue("@Department_ID", Me.DepartmentID)
                            .AddWithValue("@Cars_Reg", Me.Reg)
                            .AddWithValue("@Date", Me.DateDone)
                            .AddWithValue("@Amount", Me.Amount)
                            .AddWithValue("@Paid", Me.Paid)
                        End With
                        'Try
                        Connection.Open()
                        [COLOR=#ff0000]Command.ExecuteNonQuery()[/COLOR] 'Error Happening Here The identifier cannot be an empty string Error
                        Connection.Close()
                        'Catch ex As Exception
                        'MessageBox.Show(ex.Message)
                        'End Try

                        For Each item In WorkDone
                            item.AddToDataBase()
                        Next
                    End Using
                End Using

 

[IanRyder]

Hi,

There are a few comments I could make, but the one thing that seems to stick out at the moment is this:-

.AddWithValue("@OrderNumber", Me.OrderNumber)
.AddWithValue("@Dealership_ID", Me.DealerID)
.AddWithValue("@Department_ID", Me.DepartmentID)
.AddWithValue("@Cars_Reg", Me.Reg)
.AddWithValue("@Date", Me.DateDone)
.AddWithValue("@Amount", Me.Amount)
.AddWithValue("@Paid", Me.Paid)

What control types are these on the Form? DateDone could be a DateTimePicker so that should be OK, but what about all the others? If these are all TextBoxes or ComboBoxes etc, then you are assigning Control Objects as your parameters when you should be assigning the Contents of the controls to the Parameter. i.e:-

.AddWithValue("@Cars_Reg", Me.Reg.Text)

Just be careful with the Me.Amount control if this TextBox.Text value contains a Currency Symbol since you need to get rid of this before saving to a numeric value in a Database.

Hope that helps.

Cheers,

Ian

[Edit] Slight glitch, if you do have a DateTimePicker, you should use DateTimePicker.Value

 

[paulthepaddy]

hi ian thanks for the reply, but unfortunately that isn't the problem. the values are all coming from within the class variables, and i have checked with breakpoints to make sure their is actual values being passed, all is what is expected.

all the paramaters have a value before being passed to the command

Public Class OrderNumber
#Region "Properties"
    Private _Reg As String
    Private _DateDone As Date
    Private _OrderNumber As String

    Property DealerID As Integer

    Property DepartmentID As Integer

    Property Reg As String
        Get
            Return _Reg
        End Get
        Set(value As String)
            _Reg = StrConv(value, VbStrConv.Uppercase)
        End Set
    End Property

    Property OrderNumber As String
        Get
            Return _OrderNumber
        End Get
        Set(value As String)
            _OrderNumber = StrConv(value, VbStrConv.Uppercase)
        End Set
    End Property

    Property DateDone As Date
        Get
            Return _DateDone
        End Get
        Set(value As Date)
            _DateDone = CDate(Format(value, "dd/MM/yyyy"))
        End Set
    End Property

    Property Amount As Integer

    Property Paid As Boolean

    Public Property WorkDone As New BindingList(Of WorkDone)

#End Region


    Public Sub New()

    End Sub

    Public Sub New(strReg As String)

        Me.Reg = strReg

    End Sub

    Public Sub New(ByVal OrderNumber As String, ByVal DealerID As Integer, ByVal DepartmentID As Integer, ByVal Reg As String, ByVal DateD As Date, ByVal Amount As Integer, ByVal Paid As Boolean)
        With Me
            .OrderNumber = OrderNumber
            .DealerID = DealerID
            .DepartmentID = DepartmentID
            .Reg = Reg
            .DateDone = DateD
            .Amount = Amount
            .Paid = Paid
        End With
    End Sub


    Public Function AddToDataBase() As Integer
        Dim SQLQuery = <sql>INSERT INTO OrderNumbers (OrderNumber, Dealership_ID, Department_ID, Cars_Reg, Date, Amount, Paid) VALUES (@OrderNumber, @Dealership_ID, @Department_ID , @Cars_Reg, @Date, @Amount, @Paid)"</sql>
        Select Case IsConnectionAvailable() ' ALWAYS USES FALSE(USING LOCAL FOR TESTING)
            Case False
                Using Connection As New SqlServerCe.SqlCeConnection(My.Settings.ClientImageConnectionString)
                    Using Command As New SqlServerCe.SqlCeCommand(SQLQuery, Connection)
                        With Command.Parameters
                            .AddWithValue("@OrderNumber", Me.OrderNumber)
                            .AddWithValue("@Dealership_ID", Me.DealerID)
                            .AddWithValue("@Department_ID", Me.DepartmentID)
                            .AddWithValue("@Cars_Reg", Me.Reg)
                            .AddWithValue("@Date", Me.DateDone)
                            .AddWithValue("@Amount", Me.Amount)
                            .AddWithValue("@Paid", Me.Paid)
                        End With
                        'Try
                        Connection.Open()
[COLOR=#ff0000]                        Command.ExecuteNonQuery()[/COLOR]
                        Connection.Close()
                        'Catch ex As Exception
                        'MessageBox.Show(ex.Message)
                        'End Try

                        For Each item In WorkDone
                            item.AddToDataBase()
                        Next
                    End Using
                End Using
            Case True
                Using Connection As New SqlClient.SqlConnection(My.Settings.ServerImageConnectionString)
                    Using Command As New SqlClient.SqlCommand(SQLQuery, Connection)
                        With Command.Parameters
                            .AddWithValue("@OrderNumber", Me.OrderNumber)
                            .AddWithValue("@Dealership_ID", Me.DealerID)
                            .AddWithValue("@Department_ID", Me.DepartmentID)
                            .AddWithValue("@Cars_Reg", Me.Reg)
                            .AddWithValue("@Date", Me.DateDone)
                            .AddWithValue("@Amount", Me.Amount)
                            .AddWithValue("@Paid", Me.Paid)
                        End With
                        Try
                            Connection.Open()
                            Command.ExecuteNonQuery()
                            Connection.Close()
                        Catch ex As Exception
                            MessageBox.Show(ex.ToString)
                        End Try
                        For Each item In WorkDone
                            item.AddToDataBase()
                        Next
                    End Using
                End Using

        End Select
        Return +1
    End Function

End Class

 

[paulthepaddy]

to put it nicely..... all the bad foul words that have come out of my mount in the last 30 seconds, wouldn't leave much for you to think of lol.

ARE YOU READY FOR IT!!!!!!

 Dim SQLQuery = <sql>INSERT INTO OrderNumbers (OrderNumber, Dealership_ID, Department_ID, Cars_Reg, Date, Amount, Paid) VALUES (@OrderNumber, @Dealership_ID, @Department_ID , @Cars_Reg, @Date, @Amount, @Paid)[COLOR=#ff0000][SIZE=5]"[/SIZE][/COLOR]</sql>

I have honestly been looking at the code for about 2 Hours 30 mins trying to get my head around this, i new this code worked as i have been using the same layout/style of code for all my objects going into my DB, i have probably looked at that Query about 20-30 times checking the parameters and column names for spelling mistakes and ect.

my bad, think a nice cupa tea is in order

 

[IanRyder]

Ha, ha, ha , ha,

I have just spent the past hour or so going over this, trying to find the reason why this was failing and could only come up with some ideas about why the Database was not liking the SQL query for one reason or another.

I was about 2 seconds away from posting a load of rubbish when I saw your latest post and Bust Out Laughing!

Jesus, even I missed that one!

Well done for getting there on your own and I am going to make an appointment with the opticians.

Cheers,

Ian

 

[IanRyder]

BTW,

You do not need to use the old style StrConv function in .NET since this:-

_Reg = StrConv(value, VbStrConv.Uppercase)

Can be replaced with this:-

_Reg = value.ToUpper

Ian

 

[paulthepaddy]

ano i couldn't believe it, i was looking at it for AGES and i new the parameters where all ok, i new the way i was using the code was fine as i have previously used it.

because i was using

command("SQL Query as string",connection)
rather than
dim SQLQuery
Command(SQLQuery,Conection)
copied the " when copy & Paste.

i really appricate the effort and time you guys put into helping people on this forum.

i really couldn't have gotten far with this project without the help from this forums, you should see my first attempt at this software, even i look back at it now and then and think, my goood it was bad

_reg = value.ToUpper i only discovered this a few days ago, when ever im closer to being finished finished il go through all the code and tidy it all up, i am messy if iv honest but as iv said before no proper background for programming so i prob have loads of bad habbits
i like to use propercase alot so i have just got into the habbit of using strConv