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
– Ehsan, Commented Mar 26, 2014 at 9:45
Iv'e removed the C# tag as it didn't seem relevant. Read up on how to parameterize your queries - this way you don't need to remember to mangle and escape strings. — StuartLC
– StuartLC, Commented Mar 26, 2014 at 9:48

Community · Accepted Answer · 2017-05-23 10:26:02Z

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, 2017 at 10:26

CommunityBot

11 silver badge

answered Mar 26, 2014 at 9:47

Ehsan

32.8k6 gold badges61 silver badges66 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Knaģis Over a year ago

this is not a good advice - parameterized queries should be used instead.

Ehsan Over a year ago

@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.

sikha Over a year ago

@Ehsan thanks for your suggestion single quotes does the trick...its something wrong to use this???

Ehsan Over a year ago

@sikha yes it is. See the links in my answer of the alternative that should be used. And accept it if it was helpful.

Collectives™ on Stack Overflow

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

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related