The conversion from String "@StartingDate" to type'Integer' is invalid-CodePudding

I've googled that error(in title) but I can't seem to get it to help me. My goal is to insert all the data value into database table. My query code;

Try
For Each value As Type.Structures.R_Warehouse In dataList
Dim sql As String = String.Empty
sql &= "DELETE FROM" & Common.Constant.TBL_R_WAREHOUSE & ""
sql &= "WHERE InvDate = HMCS.dbo.f_GetInvDate()"
sql &= "AND InvID = @InvID AND InvDate = HMCS.dbo.f_GetInvDate()"    

sql &= "INSERT INTO" & Common.Constant.TBL_H_WHSTRANS & ""
sql &= " SELECT (SELECT dbo.f_GetNewTransID(substring(convert(varchar,getdate(),112),3,6)) as ID)"
sql &= ",@InvID "
sql &= ",@ItemID "
sql &= ",@Qty "   
sql &= ",@StartingDate "
sql &= ",@SANote "
sql &= ",convert(varchar,GETDATE(),120)"
sql &= ",'" & Setting.SettingHelper.HostName & "'"

Using cmd As New SqlCommand(sql.ToString, cn, tran)
With cmd
.Parameters.Add("@InvID", SqlDbType.VarChar).Value = value.InvID
.Parameters.Add("@ItemID", SqlDbType.Int).Value = value.ItemID
.Parameters.Add("@Qty", SqlDbType.Decimal, 4).Value = value.Qty
    
If IsDBNull("@StartingDate") = False Then
.Parameters.Add("@StartingDate", SqlDbType.Date, 8).Value = value.StartingDate
End If
    
.Parameters.Add("@SANote", SqlDbType.VarChar).Value = value.SANote
.ExecuteNonQuery()
End With

But it always gives me an error info

The conversion from String "@StartingDate" to type'Integer' is invalid.

Any help would be greatly appreciated.

CodePudding user response：

Even though my question in the comments wasn't answered, I think I can glean enough information from what was posted to work out what's going on and what should be done. It seems that we're just not going to get a clear explanation so a best guess is all I can provide.

It appears that the point of the If statement that is causing the problem is to insert NULL into the database if value.StartingDate is not set and insert the value that is set otherwise. As it stands, it appears that value.StartingDate is type Date. Note that Date and DateTime are the same type, just as Integer and Int32 are the same type. In that case, it will ALWAYS have a value. Just as an Integer variable will be zero by default, so a Date variable will be #1/01/0001 00:00:00# by default. If you insist on using a Date property there then you need to look for that default value and use DBNull.Value if you find it. That could be done with an If block:

If value.StartingDate = Nothing Then
    .Parameters.Add("@StartingDate", SqlDbType.Date).Value = DBNull.Value
Else
    .Parameters.Add("@StartingDate", SqlDbType.Date).Value = value.StartingDate
End If

It would be better to use the If operator though:

.Parameters.Add("@StartingDate", SqlDbType.Date).Value = If(value.StartingDate = Nothing,
                                                            CObj(DBNull.Value),
                                                            value.StartingDate)

Note that I have used Nothing for comparison there because Nothing is always the default for value type variables. What that actually corresponds to depends on the type. For numeric types, it corresponds to zero, while it corresponds to Date.MinValue for the Date type.

That will work, but it's not the best solution.

A better solution would be to declare the property type Date?, i.e. Nullable(Of Date) rather than Date. If you want a variable to be able to contain no value then you should use a nullable type. That way, instead of the property containing Date.MinValue by default, it will genuinely contain Nothing, i.e. no value. It will only contain an actual Date value if you assign one. You can then do this:

.Parameters.Add("@StartingDate", SqlDbType.Date).Value = If(value.StartingDate.HasValue,
                                                            value.StartingDate.Value,
                                                            CObj(DBNull.Value))

Note that CObj is used because the If operator requires both potential return values to be the same type or one type inherit the other. In this case, neither DBNull nor Date inherit the other, so at least one must be cast as Object. Everything inherits Object, so any other return value will be valid. This means that the return type of If is Object, but that's fine because it is being assigned the Value property of a parameter, which is also of type Object.

CodePudding user response：

There are quite a few things in the code in the question that I would do differently - please don't take my code as being the paragon of VB.NET, but I think it is something closer to what you intend to do.

I had to guess at what you were trying to do with the SQL transaction.

Adding a New SqlParameter With {...} might seem tedious, but look at how easy it is to know what each parameter is for. Easy-to-read code pays for itself in ease of getting it right and maintenance.

If you are using a version of Visual Studio that doesn't allow multi-line strings then you really need to update it. To one that also uses interpolated strings ;)

Try to put spaces after commas to make the code easier to read.

My attempt at getting the code in the question to be what you need is:

Imports System.Data.SqlClient
Imports System.Data.SqlTypes

' This is only for the example on SO.
Namespace Type.Structures
    Class R_Warehouse
        Property InvID As String
        Property ItemID As Integer
        Property Qty As Decimal
        Property StartingDate As Date?
        Property SANote As String
    End Class
End Namespace

Module Module1

    ' This is only for the example on SO.
    Friend Class Common
        Friend Class Constant
            Friend Shared TBL_R_WAREHOUSE As String
            Friend Shared TBL_H_WHSTRANS As String
        End Class
    End Class

    ' This is only for the example on SO.
    Friend Class Setting
        Friend Class SettingHelper
            Friend Shared Property HostName As String
        End Class
    End Class

    Sub X() ' I had no better name for this method.
        Dim dataList As New List(Of Type.Structures.R_Warehouse) ' Only for this SO code.
        Dim connStr = "your connection string goes here" ' Only for this SO code.

        Dim sqlDelete = $"DELETE FROM [{Common.Constant.TBL_R_WAREHOUSE}] WHERE InvDate = HMCS.dbo.f_GetInvDate() AND InvID = @InvID"

        'TODO: Put the column names in as indicated.
        Dim sqlInsert = $"INSERT INTO [{Common.Constant.TBL_H_WHSTRANS}]
                                 VALUES(put the column names here)
                                 SELECT (SELECT dbo.f_GetNewTransID(SUBSTRING(CONVERT(VARCHAR, GETDATE(), 112), 3, 6)) As ID)
                                               ,@InvID
                                               ,@ItemID
                                               ,@Qty
                                               ,@StartingDate
                                               ,@SANote
                                               ,CONVERT(VARCHAR, GETDATE(), 120)
                                               ,@HostName"

        Using conn As New SqlConnection(connStr)
            conn.Open()

            'TODO: Consider the scope of the transaction - maybe a transaction is needed individually for each item in dataList instead of all of them in one go.
            Dim tran = conn.BeginTransaction("OverallTransaction")

            Try

                For Each rwItem As Type.Structures.R_Warehouse In dataList

                    Using cmd As New SqlCommand(sqlDelete, conn, tran)
                        cmd.Parameters.Add(New SqlParameter With {.ParameterName = "@InvId", .SqlDbType = SqlDbType.VarChar, .Value = rwItem.InvID})
                        cmd.ExecuteNonQuery()

                    End Using

                    Using cmd As New SqlCommand(sqlInsert, conn, tran)
                        'TODO: Set the .Size parameter for the VarChar to match the column in the database.
                        cmd.Parameters.Add(New SqlParameter With {.ParameterName = "@InvID", .SqlDbType = SqlDbType.VarChar, .Value = rwItem.InvID})
                        cmd.Parameters.Add(New SqlParameter With {.ParameterName = "@ItemID", .SqlDbType = SqlDbType.Int, .Value = rwItem.ItemID})
                        'TODO: Set the .Precision and .Scale values to match the column in the database.
                        cmd.Parameters.Add(New SqlParameter With {.ParameterName = "@Qty", .SqlDbType = SqlDbType.Decimal, .Precision = 8, .Scale = 4, .Value = rwItem.Qty})

                        Dim startDateParam = New SqlParameter With {.ParameterName = "@StartingDate", .SqlDbType = SqlDbType.Date}

                        ' The MinValue and MaxValue checks may or may not be needed.
                        If rwItem.StartingDate.HasValue AndAlso
                            rwItem.StartingDate > SqlDateTime.MinValue AndAlso
                            rwItem.StartingDate < SqlDateTime.MaxValue Then
                            startDateParam.Value = rwItem.StartingDate
                        Else
                            startDateParam.Value = DBNull.Value
                        End If

                        cmd.Parameters.Add(startDateParam)

                        'TODO: Set the .Size parameter for the VarChar to match the column in the database.
                        cmd.Parameters.Add(New SqlParameter With {.ParameterName = "@SANote", .SqlDbType = SqlDbType.VarChar, .Value = rwItem.SANote})

                        'TODO: Set the .Size parameter for the VarChar to match the column in the database.
                        cmd.Parameters.Add(New SqlParameter With {.ParameterName = "@HostName", .SqlDbType = SqlDbType.VarChar, .Value = Setting.SettingHelper.HostName})

                        cmd.ExecuteNonQuery()

                    End Using
                Next

                tran.Commit()

            Catch ex As Exception
                tran.Rollback()
                ' Do something useful
            End Try

        End Using

    End Sub

End Module