The conversion from string "@startingdate" to type'integer' is invalid (VB.NET)

Question

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 10-May-22 16:52pm

nurman09

Updated 10-May-22 19:09pm

v2

Add a Solution

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

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Answer 1 · 2022-05-10T19:09:00

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.