Click here to Skip to main content
15,353,872 members
Please Sign up or sign in to vote.
1.00/5 (1 vote)
See more:
I've googled that error(in title) but I can't seem to get it to help me. My goal is to insert deleted data into another database table.

But it always give me an error info The conversion from String "@StartingDate" to type'Integer' is invalid.
Any help would be greatly Appreciated.

What I have tried:

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
'.Parameters.Add("@StartingDate", SqlDbType.DateTime).Value = value.StartingDate
If IsDBNull("@StartingDate") = False Then
.Parameters.Add("@StartingDate", SqlDbType.Date, 8).Value = value.StartingDate
End If
.Parameters.Add("@SANote", SqlDbType.VarChar).Value = value.SANote
End With
Updated 10-May-22 19:09pm
Richard Deeming 12-May-22 11:36am
If IsDBNull("@StartingDate") = False Then

The constant string "@StartingDate" will never be DBNull. Whatever you think this line is doing, you're wrong.

sql &= ",'" & Setting.SettingHelper.HostName & "'"

Why do you suddenly stop doing things properly, and resort to string concatenation? Your code is now vulnerable to SQL Injection[^]. NEVER use string concatenation/interpolation to build a SQL query. ALWAYS use a parameterized query.

1 solution

SImple: you don't list the column names into which you are putting your values, so SQL has to work with the current column order and assume that's what you wanted.

If the starting date isn't the fifth column in your DB table design, then it'll try to insert it into whatever column is the fifth, find it's an integer, try to convert the value and fail.

The best solution is always to specifically list the columns as part of the INSERT statement:
INSERT INTO MyTable (myColumn1, myColumn2) VALUES (@C1, @C2)
And then the column order becomes irrelevant and your code works even if you change the DB table design later.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900