reading text delimited

[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!

[Mike Brind]

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

[shank]

"Mike Brind" <pa*******@hotmail.com> wrote in message
news:11**********************@i39g2000cwa.googlegr oups.com...

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
%>

[Mike Brind]

shank wrote:

"Mike Brind" <pa*******@hotmail.com> wrote in message
news:11**********************@i39g2000cwa.googlegr oups.com...

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
%>

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.

--
Mike Brind

[shank]

"Mike Brind" <pa*******@hotmail.com> wrote in message
news:11**********************@j52g2000cwj.googlegr oups.com...

shank wrote:

"Mike Brind" <pa*******@hotmail.com> wrote in message
news:11**********************@i39g2000cwa.googlegr oups.com...

>
> 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
%>

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.

--
Mike Brind

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"),C hr(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
%>

[Mike Brind]

shank wrote:

"Mike Brind" <pa*******@hotmail.com> wrote in message
news:11**********************@j52g2000cwj.googlegr oups.com...

shank wrote:
"Mike Brind" <pa*******@hotmail.com> wrote in message

news:11**********************@i39g2000cwa.googlegr oups.com...
>
> 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
%>

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.

--
Mike Brind

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"),C hr(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
%>

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

--
Mike Brind

[Bob Barrows [MVP]]

Mike Brind wrote:

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
%>

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"

[Mike Brind]

Bob Barrows [MVP] wrote:

Mike Brind wrote:

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
%>
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

**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
%>

Thanks Bob.

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

--
Mike Brind

[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"

- - - - - - - - - - - - - - - - - - - - - -
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
%>

[Bob Barrows [MVP]]

shank wrote:

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?

:-)
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.<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 " & ordno & " and " & _
qty & " into the database table.<br>" cmd.Execute ,arParms,128 'adExecuteNoRecords
Next
%>

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.

[shank]

"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:uN*************@TK2MSFTNGP10.phx.gbl...

shank wrote:

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?

:-)
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.<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 " & ordno & " and " & _
qty & " into the database table.<br>"

cmd.Execute ,arParms,128 'adExecuteNoRecords
Next
%>

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.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - -
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"),C hr(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
%>

[Bob Barrows [MVP]]

shank wrote:

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

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.

[shank]

"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:Oa**************@TK2MSFTNGP10.phx.gbl...

shank wrote:

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

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.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - - - - - - - -
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!

[Bob Barrows [MVP]]

Bob Barrows [MVP] wrote:

SQL = "INSERT INTO xtest ([AffNo],[OrderNo],[Qty]) "
SQL = SQL & "VALUES ('" & Session("AffNo") & "',?,?) "
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)

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

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

[Bob Barrows [MVP]]

shank wrote:

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!

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.

[Mike Brind]

shank wrote:

"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:uN*************@TK2MSFTNGP10.phx.gbl...

shank wrote:

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?

:-)
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.<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 " & ordno & " and " & _
qty & " into the database table.<br>"

cmd.Execute ,arParms,128 'adExecuteNoRecords
Next
%>

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.

- - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - -
- - - -
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"),C hr(13),","),"
",""),Chr(10),"")

(1)I don't know why you changed it back to this ^^^^^^
arrName = Split(varTextArea,",")
(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),",")
(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
%>

(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 ;-)

--
Mike Brind

[shank]

"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:OU**************@tk2msftngp13.phx.gbl...

shank wrote:

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!

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.

--------------------------------------------------------------
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
%>

[Bob Barrows [MVP]]

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

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

[Mike Brind]

shank wrote:

<snip>

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
%>

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

Second

[shank]

"Mike Brind" <pa*******@hotmail.com> wrote in message
news:11*********************@i39g2000cwa.googlegro ups.com...

shank wrote:

<snip>

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
%>

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<<
That is correct!
most applications something like an order number is usually unique, and
can't/shouldn't be used again<<
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.
Second, you've hardcoded your update routine to increment the quantity by
1.<<

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

thanks!

[Mike Brind]

shank wrote:

"Mike Brind" <pa*******@hotmail.com> wrote in message
news:11*********************@i39g2000cwa.googlegro ups.com...

shank wrote:

<snip>

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
%>

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.

Possible correction - I would guess that you are not looking for
identical entries on both order number AND quantity, you are just
looking to see if the order number already exists in the database
before performing an Update on the quantity for that order number,
rather than Inserting a complete new row. Is that correct?

--
Mike Brind

[shank]

"Mike Brind" <pa*******@hotmail.com> wrote in message
news:11**********************@j33g2000cwa.googlegr oups.com...

shank wrote:

"Mike Brind" <pa*******@hotmail.com> wrote in message
news:11*********************@i39g2000cwa.googlegro ups.com...

>
> shank wrote:
>
> <snip>
>
>> 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
>> %>
>
> 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.
>

Possible correction - I would guess that you are not looking for
identical entries on both order number AND quantity, you are just
looking to see if the order number already exists in the database
before performing an Update on the quantity for that order number,
rather than Inserting a complete new row. Is that correct?

--
Mike Brind

--------------------------------
The match would be AffNo and OrderNo.
That criteria would define whether the record is inserted or updated.
It associates the product to the user ordering it.
There will be many users adding to the database, day after day.
thanks!

[Mike Brind]

shank wrote:

"Mike Brind" <pa*******@hotmail.com> wrote in message
news:11**********************@j33g2000cwa.googlegr oups.com...

shank wrote:

"Mike Brind" <pa*******@hotmail.com> wrote in message
news:11*********************@i39g2000cwa.googlegro ups.com...
>
> shank wrote:
>
> <snip>
>
>> 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
>> %>
>
> 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.
>

Possible correction - I would guess that you are not looking for
identical entries on both order number AND quantity, you are just
looking to see if the order number already exists in the database
before performing an Update on the quantity for that order number,
rather than Inserting a complete new row. Is that correct?

--
Mike Brind

--------------------------------
The match would be AffNo and OrderNo.
That criteria would define whether the record is inserted or updated.
It associates the product to the user ordering it.
There will be many users adding to the database, day after day.
thanks!

This should do it:

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

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

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

For i = 0 To UBound(varTextArea)
arrName = Split(varTextArea(i),",")
orderno=arrName(0)
qty=arrName(1)
'validate data
s = "SELECT [OrderNo],[Qty] FROM xTest WHERE " & _
"[AffNo] = ? AND [OrderNo] = ?"
arrParms=array(Session("AffNo"),orderno)
cmd.commandtext=s
Set rs = cmd.Execute(,arrParms)

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

cmd.commandtext=SQL
cmd.Execute ,arParms,128 'adExecuteNoRecords
rs.close : set rs = nothing
Next
'clean up

There's no data validation within this code, and others might find a
couple of ways to improve its efficiency etc.

--
Mike Brind

[shank]

"Mike Brind" <pa*******@hotmail.com> wrote in message
news:11**********************@p10g2000cwp.googlegr oups.com...

shank wrote:

"Mike Brind" <pa*******@hotmail.com> wrote in message
news:11**********************@j33g2000cwa.googlegr oups.com...

>
> shank wrote:
>> "Mike Brind" <pa*******@hotmail.com> wrote in message
>> news:11*********************@i39g2000cwa.googlegro ups.com...
>> >
>> > shank wrote:
>> >
>> > <snip>
>> >
>> >> 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
>> >> %>
>> >
>> > 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.
>> >
>
> Possible correction - I would guess that you are not looking for
> identical entries on both order number AND quantity, you are just
> looking to see if the order number already exists in the database
> before performing an Update on the quantity for that order number,
> rather than Inserting a complete new row. Is that correct?
>
> --
> Mike Brind

--------------------------------
The match would be AffNo and OrderNo.
That criteria would define whether the record is inserted or updated.
It associates the product to the user ordering it.
There will be many users adding to the database, day after day.
thanks!

This should do it:

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

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

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

For i = 0 To UBound(varTextArea)
arrName = Split(varTextArea(i),",")
orderno=arrName(0)
qty=arrName(1)
'validate data
s = "SELECT [OrderNo],[Qty] FROM xTest WHERE " & _
"[AffNo] = ? AND [OrderNo] = ?"
arrParms=array(Session("AffNo"),orderno)
cmd.commandtext=s
Set rs = cmd.Execute(,arrParms)

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

cmd.commandtext=SQL
cmd.Execute ,arParms,128 'adExecuteNoRecords
rs.close : set rs = nothing
Next
'clean up

There's no data validation within this code, and others might find a
couple of ways to improve its efficiency etc.

--
Mike Brind

---------------------------------------
This is great! Thanks!