where I have mistake?

<%


csv="file.csv"
set fs = createobject("Scripting.FileSystemObject")
set f = fs.opentextfile(server.mappath(csv))

while not f.atEndOfStream
line = split(f.readline, ",")
SQLstr = "insert into database (firm, type, model, cat, price, cat_1) values
("

for i = lbound (line) to ubound(line) - 1

SQLstr = SQLstr & "'" & line(i) & "'"

next
i=ubound(line)

SQLstr = SQLstr & ")"


Response.Write SQLstr

Set RS = my_Conn.Execute(SQLstr)
wend
f.close


%>

file.csv


"3Com","C0100031","3C-PC-TX-CBL","Fast Etherlink PC Card Cable
100BaseTX","27.21","cable"
"3Com","C0100203","3C16965","SuperStack II Switch Matrix
Cable","224.12","cable"

The error is

Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
Column count doesn't match value count at row 1
/test/Default.asp, line 44


number of column is the same like in query?

Re: where I have mistake?

See responses inline.
Zibi wrote:
> <%
>
>
> csv="file.csv"
> set fs = createobject("Scripting.FileSystemObject")
> set f = fs.opentextfile(server.mappath(csv))
>
> while not f.atEndOfStream
> line = split(f.readline, ",")
> SQLstr = "insert into database (firm, type, model, cat, price, cat_1)
> values ("
>
> for i = lbound (line) to ubound(line) - 1
>
> SQLstr = SQLstr & "'" & line(i) & "'"
>
> next
> i=ubound(line)
>
> SQLstr = SQLstr & ")"
>
>
> Response.Write SQLstr
>
> Set RS = my_Conn.Execute(SQLstr)

Do not use a recordset object to execute a sql statement that does not
return records. This is very wasteful of system rewources. A simple

my_Conn.Execute SQLstr,,129

will suffice


> wend
> f.close
>
>
> %>
>
> file.csv
>
>
> "3Com","C0100031","3C-PC-TX-CBL","Fast Etherlink PC Card Cable
> 100BaseTX","27.21","cable"
> "3Com","C0100203","3C16965","SuperStack II Switch Matrix
> Cable","224.12","cable"
>
> The error is
>
> Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
> Column count doesn't match value count at row 1
> /test/Default.asp, line 44
>
>
> number of column is the same like in query?

You've got a "Response.Write SQLstr" statement in there. How can we debug
your sql statement without seeing the result of the response.write? You also
neglected to tell us the type and version of database you are using. This is
almost always relevant!

You have some numeric data in there: "27.21" and "224.12", What is the
datatype of the column into which you are inserting these numbers?

Bob Barrows

--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: where I have mistake?

U¿ytkownik "Bob Barrows [MVP]" napisa³ w
wiadomo¶ci news:%23j4MgoARFHA.3076@tk2msftngp13.phx.gbl...
>> Set RS = my_Conn.Execute(SQLstr)
>
> Do not use a recordset object to execute a sql statement that does not
> return records. This is very wasteful of system rewources. A simple
>

Thanks for yor reply. Right, I don,t use recordset, I was copy only part of
another script, because I was lazy later I'll change it
>
> You've got a "Response.Write SQLstr" statement in there. How can we debug
> your sql statement without seeing the result of the response.write? You
> also neglected to tell us the type and version of database you are using.
> This is almost always relevant!
>

I use mysql,

<%


csv="file.csv"
set fs = createobject("Scripting.FileSystemObject")
set f = fs.opentextfile(server.mappath(csv))

while not f.atEndOfStream
line = split(f.readline, ",")
SQLstr = "insert into database (firm, type, model, cat, price, cat_1) values
("

for i = lbound (line) to ubound(line)-1

SQLstr = SQLstr & ",'" & line(i) & "'"

next
i=ubound(line)

SQLstr = SQLstr & ")"

result= SQLstr


%>



<% = Replace("insert into test.database (firm, type, model, cat, price,
cat_1) values (,'", "values (,", "VALUES (") %>


<%
Response.Write result
Response.Write "

"

Now could be ok.but I don't why repeat - ('insert into test.baza (firm,
type, model, cat, price, cat_1) values


insert into test.database (firm, type, model, cat, price, cat_1) VALUES
('insert into test.database(firmfirm, type, model, cat, price, cat_1) values
(,'3Com','C0100031','3C-PC-TX-CBL','Fast Etherlink PC Card Cable
100BaseTX','27.73','cable')

values (,'3Com','C0100031','3C-PC-TX-CBL','Fast Etherlink PC Card Cable
100BaseTX','27.73','cable')
^
Here is problem
Sorry for my english

Re: where I have mistake?

You did not tell me the datatype of the price column. I will assume it is
numeric.

Zibi wrote:
> U¿ytkownik "Bob Barrows [MVP]" napisa³ w
> wiadomo¶ci news:%23j4MgoARFHA.3076@tk2msftngp13.phx.gbl...
>>> Set RS = my_Conn.Execute(SQLstr)
>>
>> Do not use a recordset object to execute a sql statement that does
>> not return records. This is very wasteful of system rewources. A
>> simple
>
> Thanks for yor reply. Right, I don,t use recordset, I was copy only
> part of another script, because I was lazy later I'll change it
>>
>> You've got a "Response.Write SQLstr" statement in there. How can we
>> debug your sql statement without seeing the result of the
>> response.write? You also neglected to tell us the type and version
>> of database you are using. This is almost always relevant!
>>
>
> I use mysql,
>
> <%
>
>
> csv="file.csv"
> set fs = createobject("Scripting.FileSystemObject")
> set f = fs.opentextfile(server.mappath(csv))
>
> while not f.atEndOfStream
> line = split(f.readline, ",")
> SQLstr = "insert into database (firm, type, model, cat, price, cat_1)
> values ("
>
> for i = lbound (line) to ubound(line)-1
>
> SQLstr = SQLstr & ",'" & line(i) & "'"
>
> next
> i=ubound(line)
>
> SQLstr = SQLstr & ")"
>
> result= SQLstr
>
>
> %>
>
>
>
> <% = Replace("insert into test.database (firm, type, model, cat,
> price, cat_1) values (,'", "values (,", "VALUES (") %>
>
>
> <%
> Response.Write result
> Response.Write "

"
>
> Now could be ok.but I don't why repeat - ('insert into test.baza
> (firm, type, model, cat, price, cat_1) values
>
>
> insert into test.database (firm, type, model, cat, price, cat_1)
> VALUES ('insert into test.database(firmfirm, type, model, cat, price,
> cat_1) values (,'3Com','C0100031','3C-PC-TX-CBL','Fast Etherlink PC
> Card Cable 100BaseTX','27.73','cable')
>
> values (,'3Com','C0100031','3C-PC-TX-CBL','Fast Etherlink PC Card
> Cable 100BaseTX','27.73','cable')
> ^
> Here is problem
> Sorry for my english

Does mysql support batch queries? If so, here is what I would do (assuming
queries in a batch are delimited by semicolons):

'this is untested code that may contain typos

csv="file.csv"
set fs = createobject("Scripting.FileSystemObject")
set f = fs.opentextfile(server.mappath(csv))
dim str, val, sVals, sSql
str = "insert into test.database (firm, type, model, cat, price, cat_1)" & _
" values("
do until f.atEndOfStream
line = split(f.readline, ",")
for i = 0 to 5
val = line(i)
'handle numeric value
if i=4 then
val = replace(val,"""","")
else
val = replace(val,"""","'")
end if
if len(sVals) = 0 then
sVals = val
else
sVals = sVals & "," & val
end if
next
if len(sSQL) = 0 then
sSQL = str & sVals & ")"
else
sSQL = ";" & str & sVals & ")"
end if
sVals=""
loop
response.write replace(sSQL,";",";
") & "
"
my_Conn.Execute sSQL,,129
my_Conn.close: set my_Conn = nothing


If mysql does not support batch queries, then I would do this:

dim sSQL,cmd
set cmd=createobject("adodb.command")
sSQL="insert into test.database (firm, type, model, cat, price, cat_1)" & _
" values(?,?,?,?,?,?)"
cmd.CommandText = sSQL
set cmd.ActiveConnection = my_Conn
csv="file.csv"
set fs = createobject("Scripting.FileSystemObject")
set f = fs.opentextfile(server.mappath(csv))
dim line
do until f.atEndOfStream
line=join(f.readline, ",")
line(4) = CSng(line(4))
cmd.Execute ,line,129
loop
set cmd=nothing
my_Conn.close: set my_Conn = nothing

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: where I have mistake?

Bob Barrows [MVP] wrote:
>>
>> for i = lbound (line) to ubound(line)-1

Here is a mistake. This line should be:
for i = lbound (line) to ubound(line)

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: where I have mistake?

U¿ytkownik "Bob Barrows [MVP]" napisa³ w
wiadomo¶ci news:ecuBw6BRFHA.3544@TK2MSFTNGP12.phx.gbl...
> Bob Barrows [MVP] wrote:
>>>
>>> for i = lbound (line) to ubound(line)-1
>
> Here is a mistake. This line should be:
> for i = lbound (line) to ubound(line)
>
I check this - it is the same. I mean comma in first value - is
(,'3Com',............ should be ('3Com',

Re: where I have mistake?

U¿ytkownik "Bob Barrows [MVP]" napisa³ w
wiadomo¶ci news:%23mMga1BRFHA.3788@tk2msftngp13.phx.gbl...
> You did not tell me the datatype of the price column. I will assume it is
> numeric.
>
I has no meaning for me now all is varchar - this is temporary table.

Re: where I have mistake?

Zibi wrote:
> U¿ytkownik "Bob Barrows [MVP]" napisa³ w
> wiadomo¶ci news:ecuBw6BRFHA.3544@TK2MSFTNGP12.phx.gbl...
>> Bob Barrows [MVP] wrote:
>>>>
>>>> for i = lbound (line) to ubound(line)-1
>>
>> Here is a mistake. This line should be:
>> for i = lbound (line) to ubound(line)
>>
> I check this - it is the same. I mean comma in first value - is
> (,'3Com',............ should be ('3Com',

You did not utilize the code I gave you. Here it is again:

csv="file.csv"
set fs = createobject("Scripting.FileSystemObject")
set f = fs.opentextfile(server.mappath(csv))
dim str, val, sVals, sSql
str = "insert into test.database (firm, type, model, cat, price, cat_1)" & _
" values("
do until f.atEndOfStream
line = split(f.readline, ",")
for i = 0 to 5
val = line(i)
'handle numeric value
if i=4 then
val = replace(val,"""","")
else
val = replace(val,"""","'")
end if
if len(sVals) = 0 then
sVals = val
else
sVals = sVals & "," & val
end if
next
if len(sSQL) = 0 then
sSQL = str & sVals & ")"
else
sSQL = ";" & str & sVals & ")"
end if
sVals=""
loop
response.write replace(sSQL,";",";
") & "
"
my_Conn.Execute sSQL,,129
my_Conn.close: set my_Conn = nothing


Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: where I have mistake?

Zibi wrote:
> U¿ytkownik "Bob Barrows [MVP]" napisa³ w
> wiadomo¶ci news:%23mMga1BRFHA.3788@tk2msftngp13.phx.gbl...
>> You did not tell me the datatype of the price column. I will assume
>> it is numeric.
>>
> I has no meaning for me now all is varchar - this is temporary table.

Then do this (no need to use split):

csv="file.csv"
set fs = createobject("Scripting.FileSystemObject")
set f = fs.opentextfile(server.mappath(csv))
dim str, val, sVals, sSql
str = "insert into test.database (firm, type, model, cat, price, cat_1)" & _
" values("
do until f.atEndOfStream
sVals = f.readline
if len(sSQL) = 0 then
sSQL = str & sVals & ")"
else
sSQL = ";" & str & sVals & ")"
end if
loop
response.write replace(sSQL,";",";
") & "
"
my_Conn.Execute sSQL,,129
my_Conn.close: set my_Conn = nothing

Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"

Re: where I have mistake?

>> U¿ytkownik "Bob Barrows [MVP]" napisa³ w
>> wiadomo¶ci news:ecuBw6BRFHA.3544@TK2MSFTNGP12.phx.gbl...


Many thanks for your help!
It's working