Leading zeros are missing when inserting data into sql server in asp.net

Question

I am working on project where i am inserting productid into one of table.But here is something strange. If productid is like(002374) then in table its saving like(2374),missing leading zeros.

I have checked variable fetching correct productid(002374). Datatype of column is nvarchar(255). And productid variable is of string type.

It looks something wrong with datatype or different thing.

Here is my code:

productid = dt.Rows(i)(3)
item = dt.Rows(i)(0)
amount = dt.Rows(i)(1)
qty = dt.Rows(i)(2)
Custid = Session("customerid")
Total = Session("price")

Notes = Session("Notes")

Dim con1 As New Data.SqlClient.SqlConnection
con1.ConnectionString = ConfigurationManager.ConnectionStrings("ConnStringDb1").ConnectionString
Dim strConnString1 As String = ""

strConnString1 &= "INSERT INTO Weborder_Details   (OrderID,Qty,Cost,UnitPrice,ProductID,ItemDescription)  VALUES  ('" & result2 & "','" & qty & "', " & amount & "," & amount & "," & productid & ",'" & item & "'); "

Dim cmd2 As SqlClient.SqlCommand = New SqlClient.SqlCommand(strConnString1, con1)
con1.Open()

cmd.Connection = con1
cmd.CommandType = CommandType.Text

cmd2.ExecuteNonQuery()

Please tell me what i need to solve this issue.

if product is nvarchar, then why are you not passing it in single quotes? "," & productid & ",'" should be ",'" & productid & "','" — Ehsan, Mar 26 '14 at 09:45
Iv'e removed the C# tag as it didn't seem relevant. Read up on how to [parameterize your queries](http://stackoverflow.com/questions/5468425/how-do-parameterized-queries-help-against-sql-injection) - this way you don't need to remember to mangle and escape strings. — StuartLC, Mar 26 '14 at 09:48

score 1 · Accepted Answer · edited May 23 '17 at 10:26

1

I would strongly recommend that you use parameterized query here. You can read the reasoning here. However, for your problem You are passing productid as integer to database, change this

strConnString1 &= "INSERT INTO Weborder_Details   (OrderID,Qty,Cost,UnitPrice,ProductID,ItemDescription)  VALUES  ('" & result2 & "','" & qty & "', " & amount & "," & amount & "," & productid & ",'" & item & "'); "

to

//note the single quotes around product id
strConnString1 &= "INSERT INTO Weborder_Details   (OrderID,Qty,Cost,UnitPrice,ProductID,ItemDescription)  VALUES  ('" & result2 & "','" & qty & "', " & amount & "," & amount & ",'" & productid & "','" & item & "'); "

edited May 23 '17 at 10:26

Community

1
1

answered Mar 26 '14 at 09:47

Ehsan

31,833
6
56
65

this is not a good advice - parameterized queries should be used instead. – Knaģis Mar 26 '14 at 09:48
1

@Knaģis agreed to what you have said, though i haven't advised him to write that code. I was updating my answer when you commented. – Ehsan Mar 26 '14 at 09:51
@Ehsan thanks for your suggestion single quotes does the trick...its something wrong to use this??? – sikha Mar 26 '14 at 10:01
@sikha yes it is. See the links in my answer of the alternative that should be used. And accept it if it was helpful. – Ehsan Mar 26 '14 at 10:07

Leading zeros are missing when inserting data into sql server in asp.net

1 Answers1