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:

VB
<pre>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
'.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
.ExecuteNonQuery()
End With
Posted
Updated 10-May-22 19:09pm
v2
Comments
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:
SQL
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