reading text delimited

S

shank

I have the below code found on an ASP site.

<%
arrName = Split(Request("TextArea"),",")
%>
<%
For i = LBound(arrName) To UBound(arrName)
Response.Write "ID: " & arrName(i) & "<br>"
Next
%>

I'm using a TextArea box to submit the following...
A123,1
B123,2
C123,3
Results are...
ID: A123
ID: 1 B123
ID: 2 C123
ID: 3
I expected something like...
A123 1
B123 2
C123 3
For the sake of understanding, why are the results like above?

In the end, I want to insert these values into a table.
A123 into Field1 .... 1 into Field2
B123 into Field1 .... 2 into Field2
C123 into Field1 .... 3 into Field2

Where do I start?
thanks!
 
M

Mike Brind

shank said:
I have the below code found on an ASP site.

<%
arrName = Split(Request("TextArea"),",")
%>
<%
For i = LBound(arrName) To UBound(arrName)
Response.Write "ID: " & arrName(i) & "<br>"
Next
%>

I'm using a TextArea box to submit the following...
A123,1
B123,2
C123,3
Results are...
ID: A123
ID: 1 B123
ID: 2 C123
ID: 3
I expected something like...
A123 1
B123 2
C123 3
For the sake of understanding, why are the results like above?

In the end, I want to insert these values into a table.
A123 into Field1 .... 1 into Field2
B123 into Field1 .... 2 into Field2
C123 into Field1 .... 3 into Field2

Where do I start?
thanks!
Click to expand...

You have chosen the comma as a delimiter in your example, so split()
breaks the string into an array wherever it sees a comma.

If you are actually inputting content like this:

A123,1
B123,2
C123,3

then the line breaks are what delimit your values. Your code should be
like this:

arrName = Split(Request.Form("TextArea"),chr(13))

Then you need to replace the commas with spaces within your for... next
loop:

For i = 0 To UBound(arrName)
Response.Write "ID: " & replace(arrName(i),","," ") & "<br>"
Next

By the way - you should define the collection in the Request Object you
want to reference (eg Request.Form, Request.QueryString). If you
don't, ASP will search all the collections, starting with
ServerVariables, and that is an expensive process in terms of overhead.
 
S

shank

Mike Brind said:
You have chosen the comma as a delimiter in your example, so split()
breaks the string into an array wherever it sees a comma.

If you are actually inputting content like this:

A123,1
B123,2
C123,3

then the line breaks are what delimit your values. Your code should be
like this:

arrName = Split(Request.Form("TextArea"),chr(13))

Then you need to replace the commas with spaces within your for... next
loop:

For i = 0 To UBound(arrName)
Response.Write "ID: " & replace(arrName(i),","," ") & "<br>"
Next

By the way - you should define the collection in the Request Object you
want to reference (eg Request.Form, Request.QueryString). If you
don't, ASP will search all the collections, starting with
ServerVariables, and that is an expensive process in terms of overhead.
Click to expand...

Thanks! I'm stuck on how to get the data into a table.
Do I need to name the elements first?
I took a shot that Replace(arrName(i),","," ") would give me the values.
Didn'twork.

<%
arrName = Split(Request.Form("TextArea"),chr(13))
%>
<%
Dim DataConn, SQL
Set DataConn = Server.CreateObject("ADODB.Connection")
DataConn.Open MM_STRING

For i = 0 To UBound(arrName)
SQL = "INSERT INTO xtest ([OrderNo], [Qty]) "
SQL = SQL & "VALUES ('" & Replace(arrName(i),","," ") & "') "
DataConn.Execute(SQL)

' Response.Write "ID: " & replace(arrName(i),","," ") & "<br>"
Next
%>
 
M

Mike Brind

shank said:
Mike Brind said:
You have chosen the comma as a delimiter in your example, so split()
breaks the string into an array wherever it sees a comma.

If you are actually inputting content like this:

A123,1
B123,2
C123,3

then the line breaks are what delimit your values. Your code should be
like this:

arrName = Split(Request.Form("TextArea"),chr(13))

Then you need to replace the commas with spaces within your for... next
loop:

For i = 0 To UBound(arrName)
Response.Write "ID: " & replace(arrName(i),","," ") & "<br>"
Next

By the way - you should define the collection in the Request Object you
want to reference (eg Request.Form, Request.QueryString). If you
don't, ASP will search all the collections, starting with
ServerVariables, and that is an expensive process in terms of overhead.
Click to expand...

Thanks! I'm stuck on how to get the data into a table.
Do I need to name the elements first?
I took a shot that Replace(arrName(i),","," ") would give me the values.
Didn'twork.

<%
arrName = Split(Request.Form("TextArea"),chr(13))
%>
<%
Dim DataConn, SQL
Set DataConn = Server.CreateObject("ADODB.Connection")
DataConn.Open MM_STRING

For i = 0 To UBound(arrName)
SQL = "INSERT INTO xtest ([OrderNo], [Qty]) "
SQL = SQL & "VALUES ('" & Replace(arrName(i),","," ") & "') "
DataConn.Execute(SQL)

' Response.Write "ID: " & replace(arrName(i),","," ") & "<br>"
Next
%>
Click to expand...

First off, I got the order that ASP reads the Request Object
collections wrong - it starts off with QueryString, then Form, then
Cookies, followed by ClientCertificate and finally ServerVariables. If
you misspell the name - and it doesn't appear anywhere, it will search
all collections. If you have two items with the same name but in
different collections, it will pick the one that occurs first.

Second, I presume fron the above that A123 1 represents two pieces of
data - A123 being the order number and 1 being the quantity?

If this is the case, you need to do a further split in arrName(i) with
the space as a delimiter to create two separate values prior to
inserting your data.

For i = 0 To UBound(arrName)
myvalues = split(arrName(i),",")
SQL = "INSERT INTO xtest ([OrderNo], [Qty]) "
SQL = SQL & "VALUES ('" & myvalue(0) & "'," & myvalue(1) & ")"
'assuming Qty is a numeric field, and OrderNo is text.
DataConn.Execute(SQL)

Your approach is fraught with danger. You can't guarantee that users
will input data in the format you want. I would recommend separate
form fields for each order and quantity, and server-side validation of
the values you receive.

Also, dynamic sql has its own dangers. If you are using google groups,
do a search for saved parameter queries in this group for some very
useful advice.
 
S

shank

Mike Brind said:
Mike Brind said:
shank wrote:
I have the below code found on an ASP site.

<%
arrName = Split(Request("TextArea"),",")
%>
<%
For i = LBound(arrName) To UBound(arrName)
Response.Write "ID: " & arrName(i) & "<br>"
Next
%>

I'm using a TextArea box to submit the following...
A123,1
B123,2
C123,3
Results are...
ID: A123
ID: 1 B123
ID: 2 C123
ID: 3
I expected something like...
A123 1
B123 2
C123 3
For the sake of understanding, why are the results like above?

In the end, I want to insert these values into a table.
A123 into Field1 .... 1 into Field2
B123 into Field1 .... 2 into Field2
C123 into Field1 .... 3 into Field2

Where do I start?
thanks!

You have chosen the comma as a delimiter in your example, so split()
breaks the string into an array wherever it sees a comma.

If you are actually inputting content like this:

A123,1
B123,2
C123,3

then the line breaks are what delimit your values. Your code should be
like this:

arrName = Split(Request.Form("TextArea"),chr(13))

Then you need to replace the commas with spaces within your for... next
loop:

For i = 0 To UBound(arrName)
Response.Write "ID: " & replace(arrName(i),","," ") & "<br>"
Next

By the way - you should define the collection in the Request Object you
want to reference (eg Request.Form, Request.QueryString). If you
don't, ASP will search all the collections, starting with
ServerVariables, and that is an expensive process in terms of overhead.
Click to expand...

Thanks! I'm stuck on how to get the data into a table.
Do I need to name the elements first?
I took a shot that Replace(arrName(i),","," ") would give me the values.
Didn'twork.

<%
arrName = Split(Request.Form("TextArea"),chr(13))
%>
<%
Dim DataConn, SQL
Set DataConn = Server.CreateObject("ADODB.Connection")
DataConn.Open MM_STRING

For i = 0 To UBound(arrName)
SQL = "INSERT INTO xtest ([OrderNo], [Qty]) "
SQL = SQL & "VALUES ('" & Replace(arrName(i),","," ") & "') "
DataConn.Execute(SQL)

' Response.Write "ID: " & replace(arrName(i),","," ") & "<br>"
Next
%>
Click to expand...

First off, I got the order that ASP reads the Request Object
collections wrong - it starts off with QueryString, then Form, then
Cookies, followed by ClientCertificate and finally ServerVariables. If
you misspell the name - and it doesn't appear anywhere, it will search
all collections. If you have two items with the same name but in
different collections, it will pick the one that occurs first.

Second, I presume fron the above that A123 1 represents two pieces of
data - A123 being the order number and 1 being the quantity?

If this is the case, you need to do a further split in arrName(i) with
the space as a delimiter to create two separate values prior to
inserting your data.

For i = 0 To UBound(arrName)
myvalues = split(arrName(i),",")
SQL = "INSERT INTO xtest ([OrderNo], [Qty]) "
SQL = SQL & "VALUES ('" & myvalue(0) & "'," & myvalue(1) & ")"
'assuming Qty is a numeric field, and OrderNo is text.
DataConn.Execute(SQL)

Your approach is fraught with danger. You can't guarantee that users
will input data in the format you want. I would recommend separate
form fields for each order and quantity, and server-side validation of
the values you receive.

Also, dynamic sql has its own dangers. If you are using google groups,
do a search for saved parameter queries in this group for some very
useful advice.
Click to expand...

I created the following solution from code I found in a forum. It works. I
understand the issues with the dynamic SQL and whatever data the user may
submit. I need a solution where a user can just dump a huge list (hundreds)
of data with Item#s and quantities. It's a convenience thing. They will
generate the list on their end, then cut-n-paste into our site for
processing. The only smarter ways I'm aware of are not as convenient? Please
offer an alternative if you have one.

<%
varTextArea =
Replace(Replace(Replace(Request.Form("TextArea"),Chr(13),","),"
",""),Chr(10),"")
arrName = Split(varTextArea,",")
%>
<%
Dim DataConn, SQL
Set DataConn = Server.CreateObject("ADODB.Connection")
DataConn.Open MM_JSK_STRING

For i = 0 To UBound(arrName) Step 2
SQL = "INSERT INTO xtest ([OrderNo],[Qty]) "
SQL = SQL & "VALUES ('" & arrName(i) & "','" & arrName(i+1) & "') "
DataConn.Execute(SQL)
Next
%>
 
M

Mike Brind

shank said:
Mike Brind said:
shank wrote:
Mike Brind said:
shank wrote:
I have the below code found on an ASP site.

<%
arrName = Split(Request("TextArea"),",")
%>
<%
For i = LBound(arrName) To UBound(arrName)
Response.Write "ID: " & arrName(i) & "<br>"
Next
%>

I'm using a TextArea box to submit the following...
A123,1
B123,2
C123,3
Results are...
ID: A123
ID: 1 B123
ID: 2 C123
ID: 3
I expected something like...
A123 1
B123 2
C123 3
For the sake of understanding, why are the results like above?

In the end, I want to insert these values into a table.
A123 into Field1 .... 1 into Field2
B123 into Field1 .... 2 into Field2
C123 into Field1 .... 3 into Field2

Where do I start?
thanks!

You have chosen the comma as a delimiter in your example, so split()
breaks the string into an array wherever it sees a comma.

If you are actually inputting content like this:

A123,1
B123,2
C123,3

then the line breaks are what delimit your values. Your code should be
like this:

arrName = Split(Request.Form("TextArea"),chr(13))

Then you need to replace the commas with spaces within your for... next
loop:

For i = 0 To UBound(arrName)
Response.Write "ID: " & replace(arrName(i),","," ") & "<br>"
Next

By the way - you should define the collection in the Request Object you
want to reference (eg Request.Form, Request.QueryString). If you
don't, ASP will search all the collections, starting with
ServerVariables, and that is an expensive process in terms of overhead.

--
Mike Brind

Thanks! I'm stuck on how to get the data into a table.
Do I need to name the elements first?
I took a shot that Replace(arrName(i),","," ") would give me the values.
Didn'twork.

<%
arrName = Split(Request.Form("TextArea"),chr(13))
%>
<%
Dim DataConn, SQL
Set DataConn = Server.CreateObject("ADODB.Connection")
DataConn.Open MM_STRING

For i = 0 To UBound(arrName)
SQL = "INSERT INTO xtest ([OrderNo], [Qty]) "
SQL = SQL & "VALUES ('" & Replace(arrName(i),","," ") & "') "
DataConn.Execute(SQL)

' Response.Write "ID: " & replace(arrName(i),","," ") & "<br>"
Next
%>
Click to expand...

First off, I got the order that ASP reads the Request Object
collections wrong - it starts off with QueryString, then Form, then
Cookies, followed by ClientCertificate and finally ServerVariables. If
you misspell the name - and it doesn't appear anywhere, it will search
all collections. If you have two items with the same name but in
different collections, it will pick the one that occurs first.

Second, I presume fron the above that A123 1 represents two pieces of
data - A123 being the order number and 1 being the quantity?

If this is the case, you need to do a further split in arrName(i) with
the space as a delimiter to create two separate values prior to
inserting your data.

For i = 0 To UBound(arrName)
myvalues = split(arrName(i),",")
SQL = "INSERT INTO xtest ([OrderNo], [Qty]) "
SQL = SQL & "VALUES ('" & myvalue(0) & "'," & myvalue(1) & ")"
'assuming Qty is a numeric field, and OrderNo is text.
DataConn.Execute(SQL)

Your approach is fraught with danger. You can't guarantee that users
will input data in the format you want. I would recommend separate
form fields for each order and quantity, and server-side validation of
the values you receive.

Also, dynamic sql has its own dangers. If you are using google groups,
do a search for saved parameter queries in this group for some very
useful advice.
Click to expand...

I created the following solution from code I found in a forum. It works. I
understand the issues with the dynamic SQL and whatever data the user may
submit. I need a solution where a user can just dump a huge list (hundreds)
of data with Item#s and quantities. It's a convenience thing. They will
generate the list on their end, then cut-n-paste into our site for
processing. The only smarter ways I'm aware of are not as convenient? Please
offer an alternative if you have one.

<%
varTextArea =
Replace(Replace(Replace(Request.Form("TextArea"),Chr(13),","),"
",""),Chr(10),"")
arrName = Split(varTextArea,",")
%>
<%
Dim DataConn, SQL
Set DataConn = Server.CreateObject("ADODB.Connection")
DataConn.Open MM_JSK_STRING

For i = 0 To UBound(arrName) Step 2
SQL = "INSERT INTO xtest ([OrderNo],[Qty]) "
SQL = SQL & "VALUES ('" & arrName(i) & "','" & arrName(i+1) & "') "
DataConn.Execute(SQL)
Next
%>
Click to expand...


Try this:

<%
varTextArea = Split(Request.Form("TextArea"),Chr(13))
'This operation gives you an array of single values, so
'varTextArea(0) is A123,1
'varTextArea(1) is B123,2 etc

Dim DataConn, SQL
Set DataConn = Server.CreateObject("ADODB.Connection")
DataConn.Open MM_JSK_STRING

'Now you need to separate the Order No from the Qty for each of the
array values
'The comma is the delimiter. Since you have to do this for each of the
values
'you have to do it within the loop

For i = 0 To UBound(varTextArea)
arrName = Split(varTextArea(i),",")
SQL = "INSERT INTO xtest ([OrderNo],[Qty]) "
SQL = SQL & "VALUES ('" & arrName(0) & "','" & arrName(1) & "') "
DataConn.Execute(SQL)
Next
%>

HTH
 
B

Bob Barrows [MVP]

Mike said:
Dim DataConn, SQL
Set DataConn = Server.CreateObject("ADODB.Connection")
DataConn.Open MM_JSK_STRING

'Now you need to separate the Order No from the Qty for each of the
array values
'The comma is the delimiter. Since you have to do this for each of
the values
'you have to do it within the loop

For i = 0 To UBound(varTextArea)
arrName = Split(varTextArea(i),",")
SQL = "INSERT INTO xtest ([OrderNo],[Qty]) "
SQL = SQL & "VALUES ('" & arrName(0) & "','" & arrName(1) & "') "
DataConn.Execute(SQL)
Next
%>
Click to expand...
Better yet:
Dim DataConn, SQL, cmd, ,ordno,qtyarParms
SQL = "INSERT INTO xtest ([OrderNo],[Qty]) "
SQL = SQL & "VALUES (?,?) "
Set DataConn = Server.CreateObject("ADODB.Connection")
DataConn.Open MM_JSK_STRING
Set cmd=createobject("adodb.command")
with cmd
.commandtext=SQL
.commandtype=1 'adcmdtext
set .acriveconnection=dataconn
end with


'Now you need to separate the Order No from the Qty for each of the
array values
'The comma is the delimiter. Since you have to do this for each of the
values
'you have to do it within the loop

For i = 0 To UBound(varTextArea)
arrName = Split(varTextArea(i),",")
ordno=arrName(0)
qty=arrName(1)
'validate the data here, then
arparms=array(ordno,qty)
cmd.Execute ,arparms,128 'adExecuteNoRecords
Next
%>

Bob Barrows
 
M

Mike Brind

Bob said:
Mike said:
Dim DataConn, SQL
Set DataConn = Server.CreateObject("ADODB.Connection")
DataConn.Open MM_JSK_STRING

'Now you need to separate the Order No from the Qty for each of the
array values
'The comma is the delimiter. Since you have to do this for each of
the values
'you have to do it within the loop

For i = 0 To UBound(varTextArea)
arrName = Split(varTextArea(i),",")
SQL = "INSERT INTO xtest ([OrderNo],[Qty]) "
SQL = SQL & "VALUES ('" & arrName(0) & "','" & arrName(1) & "') "
DataConn.Execute(SQL)
Next
%>
Click to expand...
Better yet:
Dim DataConn, SQL, cmd, ,ordno,qtyarParms
SQL = "INSERT INTO xtest ([OrderNo],[Qty]) "
SQL = SQL & "VALUES (?,?) "
Set DataConn = Server.CreateObject("ADODB.Connection")
DataConn.Open MM_JSK_STRING
Set cmd=createobject("adodb.command")
with cmd
.commandtext=SQL
.commandtype=1 'adcmdtext
set .acriveconnection=dataconn
Click to expand...

**NOTE**
The above line should be set .activeconnection=dataconn
end with


'Now you need to separate the Order No from the Qty for each of the
array values
'The comma is the delimiter. Since you have to do this for each of the
values
'you have to do it within the loop

For i = 0 To UBound(varTextArea)
arrName = Split(varTextArea(i),",")
ordno=arrName(0)
qty=arrName(1)
'validate the data here, then
arparms=array(ordno,qty)
cmd.Execute ,arparms,128 'adExecuteNoRecords
Next
%>
Click to expand...

Thanks Bob.

To shank: Now you have no excuse NOT to use parameters instead of
dynamic sql :)
 
S

shank

Better yet:
Dim DataConn, SQL, cmd, ,ordno,qtyarParms
SQL = "INSERT INTO xtest ([OrderNo],[Qty]) "
SQL = SQL & "VALUES (?,?) "
Set DataConn = Server.CreateObject("ADODB.Connection")
DataConn.Open MM_JSK_STRING
Set cmd=createobject("adodb.command")
with cmd
.commandtext=SQL
.commandtype=1 'adcmdtext
set .acriveconnection=dataconn
end with


'Now you need to separate the Order No from the Qty for each of the
array values
'The comma is the delimiter. Since you have to do this for each of the
values
'you have to do it within the loop

For i = 0 To UBound(varTextArea)
arrName = Split(varTextArea(i),",")
ordno=arrName(0)
qty=arrName(1)
'validate the data here, then
arparms=array(ordno,qty)
cmd.Execute ,arparms,128 'adExecuteNoRecords
Next
%>

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"
Click to expand...
- - - - - - - - - - - - - - - - - - - - - -
The below does not insert any data. I changed what I thought was a few typos
with no luck. It lookd kind of backwards to me. Shouldn't the variables be
filled "before" the SQL statement runs?
thanks!
<%
Dim DataConn,SQL,cmd,orderno,qty,arParms
SQL = "INSERT INTO xtest ([OrderNo],[Qty]) "
SQL = SQL & "VALUES (?,?) " '<-- I
thought this was a typo
'SQL = SQL & "VALUES (" & orderno & "," & qty & ") " '<-- didn't work
either
Set DataConn = Server.CreateObject("ADODB.Connection")
DataConn.Open MM_JSK_STRING
Set cmd=createobject("adodb.command")
with cmd
.commandtext=SQL
.commandtype=1 'adcmdtext
set .activeconnection=DataConn
end with

For i = 0 To UBound(varTextArea)
arrName = Split(varTextArea(i),",")
orderno=arrName(0)
qty=arrName(1)
'validate the data here, then
arParms=array(orderno,qty)
cmd.Execute ,arParms,128 'adExecuteNoRecords
Next
%>
 
B

Bob Barrows [MVP]

shank said:
The below does not insert any data. I changed what I thought was a
few typos with no luck. It lookd kind of backwards to me. Shouldn't
the variables be filled "before" the SQL statement runs?
Click to expand...


:)
Assigning a string to a variable does not run the sql statement.

You "fixed" what was required to make it work.

The question marks are called "parameter markers", or more technically "odbc
parameter markers". They are what allows the Command object to pass the
parameter values into the sql statement.. Change it back to what I posted!

If nothing is getting inserted, then it is likely that your loop is not
running. Put in some response.write statements to verify this:

response.write "About to start looping. said:
For i = 0 To UBound(varTextArea)
arrName = Split(varTextArea(i),",")
orderno=arrName(0)
qty=arrName(1)
'validate the data here, then
arParms=array(orderno,qty)
Click to expand...
response.write "About to insert " & ordno & " and " & _
qty & " into the database table. said:
cmd.Execute ,arParms,128 'adExecuteNoRecords
Next
%>
Click to expand...

Let us know the result.
 
S

shank

Bob Barrows said:
:)
Assigning a string to a variable does not run the sql statement.

You "fixed" what was required to make it work.

The question marks are called "parameter markers", or more technically
"odbc
parameter markers". They are what allows the Command object to pass the
parameter values into the sql statement.. Change it back to what I posted!

If nothing is getting inserted, then it is likely that your loop is not
running. Put in some response.write statements to verify this:


response.write "About to insert " & ordno & " and " & _


Let us know the result.
--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Click to expand...
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - -
I found a couple statements that I missed and also 1 variable.
I'm submitting...
A123,1
B123,1
C123,1
D123,1
E123,1
F123,1
G123,1
This is returned to the screen...
About to start looping.
About to insert A123 and 1 into the database table.

And only 1 record is inserted. It's not looping through all records.
thanks
<%
varTextArea =
Replace(Replace(Replace(Request.Form("TextArea"),Chr(13),","),"
",""),Chr(10),"")
arrName = Split(varTextArea,",")

Dim DataConn,SQL,cmd,orderno,qty,arParms
SQL = "INSERT INTO xtest ([AffNo],[OrderNo],[Qty]) "
SQL = SQL & "VALUES ('" & Session("AffNo") & "',?,?) "
Set DataConn = Server.CreateObject("ADODB.Connection")
DataConn.Open MM_JSK_STRING
Set cmd=createobject("adodb.command")
with cmd
.commandtext=SQL
.commandtype=1 'adcmdtext
set .activeconnection=DataConn
end with

response.write "About to start looping.<br>"
For i = 0 To UBound(varTextArea)
arrName = Split(varTextArea(i),",")
orderno=arrName(0)
qty=arrName(1)
'validate the data here, then
arParms=array(orderno,qty)
response.write "About to insert " & orderno & " and " & _
qty & " into the database table.<br>"
cmd.Execute ,arParms,128 'adExecuteNoRecords
Next
%>
 
B

Bob Barrows [MVP]

shank said:
- - - -
I found a couple statements that I missed and also 1 variable.
I'm submitting...
A123,1
B123,1
C123,1
D123,1
E123,1
F123,1
G123,1
This is returned to the screen...
About to start looping.
About to insert A123 and 1 into the database table.

And only 1 record is inserted. It's not looping through all records.
thanks
Click to expand...

I was going to say that this means varTextArea is not an array, but if that
was the case, UBound(varTextArea) would throw an error, and the loop would
not even begin! Hmm, where does that variable become an array in your code
....?

Never mind ... it looks as if you need to split varTextArea on vbCrLf, loop
through that array, splitting the elements of that array on the "," and
processing the results of that split operation. Like this:

varTextArea =Split(Request.Form("TextArea"), vbCrLf)


Dim DataConn,SQL,cmd,orderno,qty,arParms
SQL = "INSERT INTO xtest ([AffNo],[OrderNo],[Qty]) "
SQL = SQL & "VALUES ('" & Session("AffNo") & "',?,?) "
Set DataConn = Server.CreateObject("ADODB.Connection")
DataConn.Open MM_JSK_STRING
Set cmd=createobject("adodb.command")
with cmd
.commandtext=SQL
.commandtype=1 'adcmdtext
set .activeconnection=DataConn
end with

response.write "About to start looping.<br>"
For i = 0 To UBound(varTextArea)
arrName = Split(varTextArea(i),",")
orderno=arrName(0)
qty=arrName(1)
'validate the data here, then
arParms=array(orderno,qty)
response.write "About to insert " & orderno & " and " & _
qty & " into the database table.<br>"
cmd.Execute ,arParms,128 'adExecuteNoRecords
Next
 
S

shank

Bob Barrows said:
shank said:
- - - -
I found a couple statements that I missed and also 1 variable.
I'm submitting...
A123,1
B123,1
C123,1
D123,1
E123,1
F123,1
G123,1
This is returned to the screen...
About to start looping.
About to insert A123 and 1 into the database table.

And only 1 record is inserted. It's not looping through all records.
thanks
Click to expand...

I was going to say that this means varTextArea is not an array, but if
that
was the case, UBound(varTextArea) would throw an error, and the loop would
not even begin! Hmm, where does that variable become an array in your code
...?

Never mind ... it looks as if you need to split varTextArea on vbCrLf,
loop
through that array, splitting the elements of that array on the "," and
processing the results of that split operation. Like this:

varTextArea =Split(Request.Form("TextArea"), vbCrLf)


Dim DataConn,SQL,cmd,orderno,qty,arParms
SQL = "INSERT INTO xtest ([AffNo],[OrderNo],[Qty]) "
SQL = SQL & "VALUES ('" & Session("AffNo") & "',?,?) "
Set DataConn = Server.CreateObject("ADODB.Connection")
DataConn.Open MM_JSK_STRING
Set cmd=createobject("adodb.command")
with cmd
.commandtext=SQL
.commandtype=1 'adcmdtext
set .activeconnection=DataConn
end with

response.write "About to start looping.<br>"
For i = 0 To UBound(varTextArea)
arrName = Split(varTextArea(i),",")
orderno=arrName(0)
qty=arrName(1)
'validate the data here, then
arParms=array(orderno,qty)
response.write "About to insert " & orderno & " and " & _
qty & " into the database table.<br>"
cmd.Execute ,arParms,128 'adExecuteNoRecords
Next

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Click to expand...
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - -
Shazam! That worked! Thanks!

Now I need to move onto the next phase.
If a record exists WHERE [AffNo] = Session("AffNo") AND [OrderNo] = orderno
I need to UPDATE [Qty] = [Qty] + 1
How do I do this?
I had a vague clue using the previous dynamic SQL.
But he above param code is a new issue.
thanks!
 
B

Bob Barrows [MVP]

Bob said:
SQL = "INSERT INTO xtest ([AffNo],[OrderNo],[Qty]) "
SQL = SQL & "VALUES ('" & Session("AffNo") & "',?,?) "
Click to expand...

Oh wait! You added something.

Look. You have got to stop thinking "concatenate" when inserting values into
your sql statements. The above line should read:
SQL = SQL & "VALUES (?,?,?) "

And this line:
arParms=array(orderno,qty)
Click to expand...

needs to become:
arParms=array(Session("AffNo"),orderno,qty)
 
B

Bob Barrows [MVP]

shank said:
Shazam! That worked! Thanks!

Now I need to move onto the next phase.
If a record exists WHERE [AffNo] = Session("AffNo") AND [OrderNo] =
orderno I need to UPDATE [Qty] = [Qty] + 1
How do I do this?
I had a vague clue using the previous dynamic SQL.
But he above param code is a new issue.
thanks!
Click to expand...

SQL="UPDATE xtest SET [Qty] = [Qty] + 1 " & _
"WHERE [AffNo] = ? AND [OrderNo] = ?"
cmd.CommandText=SQL
arParms=array(Session("AffNo"),orderno)
 
M

Mike Brind

shank said:
- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - -
I found a couple statements that I missed and also 1 variable.
I'm submitting...
A123,1
B123,1
C123,1
D123,1
E123,1
F123,1
G123,1
This is returned to the screen...
About to start looping.
About to insert A123 and 1 into the database table.

And only 1 record is inserted. It's not looping through all records.
thanks
<%
varTextArea =
Replace(Replace(Replace(Request.Form("TextArea"),Chr(13),","),"
",""),Chr(10),"")
Click to expand...

(1)I don't know why you changed it back to this ^^^^^^
arrName = Split(varTextArea,",")
Click to expand...

(2) ^^^^^I don't know why you took this operation back outside fo the
loop
Dim DataConn,SQL,cmd,orderno,qty,arParms
SQL = "INSERT INTO xtest ([AffNo],[OrderNo],[Qty]) "
SQL = SQL & "VALUES ('" & Session("AffNo") & "',?,?) "
Set DataConn = Server.CreateObject("ADODB.Connection")
DataConn.Open MM_JSK_STRING
Set cmd=createobject("adodb.command")
with cmd
.commandtext=SQL
.commandtype=1 'adcmdtext
set .activeconnection=DataConn
end with

response.write "About to start looping.<br>"
For i = 0 To UBound(varTextArea)
arrName = Split(varTextArea(i),",")
Click to expand...

(2) ^^^^ This is where I put it - and you kept it there as well
orderno=arrName(0)
qty=arrName(1)
'validate the data here, then
arParms=array(orderno,qty)
response.write "About to insert " & orderno & " and " & _
qty & " into the database table.<br>"
cmd.Execute ,arParms,128 'adExecuteNoRecords
Next
%>
Click to expand...

(1) The operation you changed back to a replace was the first split
that turned each line into an array element in the code I posted last
night.

It looked for the line break - chr(13) (or vbcrlf, as Bob as posted),
and broke the input apart on that character, giving you an array which
holds each line in the text area as an element.

Each element consists of a string, which contains some text on the
left, a comma and some text on the right. This is what needs to be
split again, one element at a time so that you can insert each part
into your database. So this time, within the loop, the split needed to
be done on the comma - arrName = split(varTextArea,","), which gives
two elemtns in a new array (which is called arrName). You reference
these according to their ordinal position, bearring in mind that arrays
resulting from the split() function start from 0, so arrName(0) is the
order number, and arrName(1) is the quantity.

Bob's put the functions back where they should be, so hopefully his
latest offer will work for you. I can't see any reason why it
shouldn't, but then again, that depends on how much you want to dabble
with the order in which things are done ;-)
 
S

shank

Bob Barrows said:
shank said:
Shazam! That worked! Thanks!

Now I need to move onto the next phase.
If a record exists WHERE [AffNo] = Session("AffNo") AND [OrderNo] =
orderno I need to UPDATE [Qty] = [Qty] + 1
How do I do this?
I had a vague clue using the previous dynamic SQL.
But he above param code is a new issue.
thanks!
Click to expand...

SQL="UPDATE xtest SET [Qty] = [Qty] + 1 " & _
"WHERE [AffNo] = ? AND [OrderNo] = ?"
cmd.CommandText=SQL
arParms=array(Session("AffNo"),orderno)

--
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.
Click to expand...
--------------------------------------------------------------
I've evolved to the following. I can insert, but it will not update if a
similar records exists. I'm assuming I need to do a select to test each
record, then determine if it's an INSERT or UPDATE statement. Please indulge
me a bit longer! I get no errors. Only inserts and no updates.
thanks

<%
varTextArea =Split(Request.Form("TextArea"), vbCrLf)

Dim DataConn,SQL,cmd,orderno,qty,arParms

DataConn.Open MM_JSK_STRING
s = "SELECT [AffNo],[OrderNo],[Qty] FROM xTest WHERE WHERE [AffNo] = ? AND
[OrderNo] = ?"
Set rs = DataConn.Execute(s)

If (rs.EOF) Then
SQL = "INSERT INTO xtest ([AffNo],[OrderNo],[Qty]) "
SQL = SQL & "VALUES (?,?,?) "
Else
SQL="UPDATE xtest SET [Qty] = [Qty] + 1 " & _
"WHERE [AffNo] = ? AND [OrderNo] = ?"
cmd.CommandText=SQL
arParms=array(Session("AffNo"),orderno)
End If

Set DataConn = Server.CreateObject("ADODB.Connection")
DataConn.Open MM_JSK_STRING
Set cmd=createobject("adodb.command")
with cmd
.commandtext=SQL
.commandtype=1 'adcmdtext
set .activeconnection=DataConn
end with

response.write "About to start looping.<br>"
For i = 0 To UBound(varTextArea)
arrName = Split(varTextArea(i),",")
orderno=arrName(0)
qty=arrName(1)
'validate the data here, then
arParms=array(Session("AffNo"),orderno,qty)
response.write "About to insert " & orderno & " and " & _
qty & " into the database table.<br>"
cmd.Execute ,arParms,128 'adExecuteNoRecords
Next
%>
 
B

Bob Barrows [MVP]

shank said:
I've evolved to the following. I can insert, but it will not update
if a similar records exists. I'm assuming I need to do a select to
test each record, then determine if it's an INSERT or UPDATE
statement. Please indulge me a bit longer! I get no errors. Only
inserts and no updates.
thanks
Click to expand...
Give me some clue about what you really want to have happen here. :)

I need to know, step-by-step, data element by data element, what you expect
this code to do.

I can't tell from reading the code which is probably in error.
 
M

Mike Brind

shank wrote:

I've evolved to the following. I can insert, but it will not update if a
similar records exists. I'm assuming I need to do a select to test each
record, then determine if it's an INSERT or UPDATE statement. Please indulge
me a bit longer! I get no errors. Only inserts and no updates.
thanks

<%
varTextArea =Split(Request.Form("TextArea"), vbCrLf)

Dim DataConn,SQL,cmd,orderno,qty,arParms

DataConn.Open MM_JSK_STRING
s = "SELECT [AffNo],[OrderNo],[Qty] FROM xTest WHERE WHERE [AffNo] = ? AND
[OrderNo] = ?"
Set rs = DataConn.Execute(s)

If (rs.EOF) Then
SQL = "INSERT INTO xtest ([AffNo],[OrderNo],[Qty]) "
SQL = SQL & "VALUES (?,?,?) "
Else
SQL="UPDATE xtest SET [Qty] = [Qty] + 1 " & _
"WHERE [AffNo] = ? AND [OrderNo] = ?"
cmd.CommandText=SQL
arParms=array(Session("AffNo"),orderno)
End If

Set DataConn = Server.CreateObject("ADODB.Connection")
DataConn.Open MM_JSK_STRING
Set cmd=createobject("adodb.command")
with cmd
.commandtext=SQL
.commandtype=1 'adcmdtext
set .activeconnection=DataConn
end with

response.write "About to start looping.<br>"
For i = 0 To UBound(varTextArea)
arrName = Split(varTextArea(i),",")
orderno=arrName(0)
qty=arrName(1)
'validate the data here, then
arParms=array(Session("AffNo"),orderno,qty)
response.write "About to insert " & orderno & " and " & _
qty & " into the database table.<br>"
cmd.Execute ,arParms,128 'adExecuteNoRecords
Next
%>
Click to expand...

According to the above code, and your previous posts, what you are
trying to do is to take a series of order numbers with associated
quantities, then checking to see if identical value pairings already
exist in the database. If they do not, you insert a new record. If
they do, you increment the quantity associated with the order number by
1.

Your code needs to be tweaked a bit more to achieve that, but if that
is what you ARE trying to achieve, it makes little sense, for a couple
of reasons.

First, in most applications something like an order number is usually
unique, and can't/shouldn't be used again. Does your application allow
re-use of existing order numbers?

Second, you've hardcoded your update routine to increment the quantity
by 1. The example you posted originally suggested that the quantities
can be any number. If you are re-using existing order numbers,
shouldn't you be incrementing by the value of the new quantity?
 
S

shank

Mike Brind said:
shank wrote:

I've evolved to the following. I can insert, but it will not update if a
similar records exists. I'm assuming I need to do a select to test each
record, then determine if it's an INSERT or UPDATE statement. Please
indulge
me a bit longer! I get no errors. Only inserts and no updates.
thanks

<%
varTextArea =Split(Request.Form("TextArea"), vbCrLf)

Dim DataConn,SQL,cmd,orderno,qty,arParms

DataConn.Open MM_JSK_STRING
s = "SELECT [AffNo],[OrderNo],[Qty] FROM xTest WHERE WHERE [AffNo] = ?
AND
[OrderNo] = ?"
Set rs = DataConn.Execute(s)

If (rs.EOF) Then
SQL = "INSERT INTO xtest ([AffNo],[OrderNo],[Qty]) "
SQL = SQL & "VALUES (?,?,?) "
Else
SQL="UPDATE xtest SET [Qty] = [Qty] + 1 " & _
"WHERE [AffNo] = ? AND [OrderNo] = ?"
cmd.CommandText=SQL
arParms=array(Session("AffNo"),orderno)
End If

Set DataConn = Server.CreateObject("ADODB.Connection")
DataConn.Open MM_JSK_STRING
Set cmd=createobject("adodb.command")
with cmd
.commandtext=SQL
.commandtype=1 'adcmdtext
set .activeconnection=DataConn
end with

response.write "About to start looping.<br>"
For i = 0 To UBound(varTextArea)
arrName = Split(varTextArea(i),",")
orderno=arrName(0)
qty=arrName(1)
'validate the data here, then
arParms=array(Session("AffNo"),orderno,qty)
response.write "About to insert " & orderno & " and " & _
qty & " into the database table.<br>"
cmd.Execute ,arParms,128 'adExecuteNoRecords
Next
%>
Click to expand...

According to the above code, and your previous posts, what you are
trying to do is to take a series of order numbers with associated
quantities, then checking to see if identical value pairings already
exist in the database. If they do not, you insert a new record. If
they do, you increment the quantity associated with the order number by
1.

Your code needs to be tweaked a bit more to achieve that, but if that
is what you ARE trying to achieve, it makes little sense, for a couple
of reasons.

First, in most applications something like an order number is usually
unique, and can't/shouldn't be used again. Does your application allow
re-use of existing order numbers?

Second, you've hardcoded your update routine to increment the quantity
by 1. The example you posted originally suggested that the quantities
can be any number. If you are re-using existing order numbers,
shouldn't you be incrementing by the value of the new quantity?

--
Mike Brind
-------------------------------
what you are trying to do is to take a series of order numbers with
associated quantities, then checking to see if identical value pairings
already exist in the database. If they do not, you insert a new record.
If they do, you increment the quantity associated with the order number by
1<<
Click to expand...
Click to expand...

That is correct!

I'm trying to allow users that know our order system to cut-n-paste large
lists of order#s and quantities for their inventory updates. The AffNo is
their account#. I'm anticipating users creating lists ,then coming back to
them day after day and adding to the list.

You're right! That was an oversight. The Qty should be increased by the new
qty submitted.

thanks!
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Similar Threads

Filename undefined for Blob ? 1
Sort and count word pairs in a string 6
Survey details won't go through using php, ajax, Mysql 0
I dont get this. Please help me!! 2
HCaptcha - How to stop page from refreshing on submit if captcha is not checked/validated 1
ado properties / parameters 7
Song requests 4
how to debug in asp ? 2

Members online

Total: 42 (members: 2, guests: 40)
Robots: 346

Forum statistics

Threads
473,769
Messages
2,569,582
Members
45,065
Latest member
OrderGreenAcreCBD

Latest Threads

Top