SQL Server Inserting Wrong Values with VBScript

Question

I have a script that uploads items from a CSV. There are 3 columns to the CSV and 3 columns in the database.

Code (nvarchar 255)
name (nvarchar 255)
price (money)

The script is the following

' --- Connect to File.CSV
csvConnStr = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" 
&Server.MapPath("..\upload") &";Extended 
Properties='text;HDR=Yes;FMT=Delimited';"
Set connCSV = Server.CreateObject("ADODB.Connection")
connCSV.Open csvConnStr


set csvCmd = Server.CreateObject("ADODB.Command")
csvCmd.ActiveConnection = connCSV
csvCmd.CommandType = 1
csvCmd.CommandText = "SELECT * FROM upload.csv"
set rsCsv = csvCmd.Execute

csvCode= rsCsv.fields.item(0)
csvName= rsCsv.fields.item(1)
csvPrice = rsCsv.fields.item(2)

Note - I have ommited the loop through the csv as do not feel it is a cause of my problem but can include upon request.

set insertCmd = Server.CreateObject("ADODB.Command")
insertCmd.ActiveConnection = conn 
insertCmd.CommandType = 1
insertCmd.CommandText = "INSERT INTO tblProducts (code, name, price) VALUES (?,?,?)"
insertCmd.Parameters.append(
insertCmd.CreateParameter("@code",200,1,255,csvCode))
insertCmd.Parameters.append(
insertCmd.CreateParameter("@name",200,1,255,csvName))
insertCmd.Parameters.append(
insertCmd.CreateParameter("@price", 6, 1, , csvPrice))
insertCmd.Execute

My CSV has the following structure

Item Code, Item Name, Item Price
00001, First Item, 100
00002, Second Item, 100

The script will insert fine except in the scenario that the Item Code begins with the character 'F'.

Example,

Item Code, Item Name, Item Price
F0445, Item X, 200

Will insert a record but in the database will have the Code = 445

This is only with F, so codes such as

Item Code, Item Name, Item Price
G0445, Item Y, 200
FG0445, Item X, 200

Will insert quite alright. Is F a special character in VBScript? How come it won't insert the whole code?

Do you have single-quotes ' around the varchars in the sql-statement? How does the finished sql statement look like when you run it? — Sourcery
– Sourcery, Commented May 18, 2017 at 9:54
thanks reds. Hi @Sourcery, I do not have single-quotes around the variables in the sql-statement. I printed out the SQL statement and it is inserting the value as the shrunken code. I added a print after csvCode = rsCsv.fields.item(0) and here csvCode = 0445. The read is removing the first few characters (well, only when beginning with F!) — ADoorMarkedPirate
– ADoorMarkedPirate, Commented May 18, 2017 at 10:08
Im thinking that F somehow formats the data, so by using single-quotes so the value is 'F0445' should work to add a varchar to the database? so the end sql should be: INSERT INTO tblProducts (code, name, price) VALUES ('F0445','Item X',200), the single-quotes tells sql that it is text inserted. — Sourcery
– Sourcery, Commented May 18, 2017 at 10:16
Thank you for your reply and time. You are right in that F is formatting the data. I have the document open in excel and it is assuming that F is a function operator, despite me not defining it! If you would like to post an answer @sourcery i will be happy to accept for your time. — ADoorMarkedPirate
– ADoorMarkedPirate, Commented May 18, 2017 at 10:41

Sourcery · Accepted Answer · 2017-05-18 11:24:35Z

1

Answer from comment.

Im thinking that F somehow formats the data, so by using single-quotes so the value is 'F0445' should work to add a varchar to the database? so the end sql should be: INSERT INTO tblProducts (code, name, price) VALUES ('F0445','Item X',200), the single-quotes tells sql that it is text inserted.

Also from your last comment, format the column/cells in Excel to be text.

answered May 18, 2017 at 11:24

Sourcery

6261 gold badge5 silver badges17 bronze badges

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

Collectives™ on Stack Overflow

SQL Server Inserting Wrong Values with VBScript

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related