|
it
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
reading text delimited
<% arrName = Split(Request("TextArea"),",") %> <% For i = LBound(arrName) To UBound(arrName) Response.Write "ID: " & arrName(i) & "<br>" I'm using a TextArea box to submit the following...Next %> 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! shank wrote:
Show quote > I have the below code found on an ASP site. You have chosen the comma as a delimiter in your example, so split()> > <% > 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! 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>" By the way - you should define the collection in the Request Object youNext 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
Show quote
"Mike Brind" <paxton***@hotmail.com> wrote in message Thanks! I'm stuck on how to get the data into a table.news:1142372144.124894.240770@i39g2000cwa.googlegroups.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 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) Show quote ' Response.Write "ID: " & replace(arrName(i),","," ") & "<br>" Next %> shank wrote:
Show quote > "Mike Brind" <paxton***@hotmail.com> wrote in message First off, I got the order that ASP reads the Request Object> news:1142372144.124894.240770@i39g2000cwa.googlegroups.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 > %> 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
Show quote
"Mike Brind" <paxton***@hotmail.com> wrote in message I created the following solution from code I found in a forum. It works. I news:1142375040.387380.137810@j52g2000cwj.googlegroups.com... > > shank wrote: >> "Mike Brind" <paxton***@hotmail.com> wrote in message >> news:1142372144.124894.240770@i39g2000cwa.googlegroups.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 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 %> shank wrote:
Show quote > "Mike Brind" <paxton***@hotmail.com> wrote in message Try this:> news:1142375040.387380.137810@j52g2000cwj.googlegroups.com... > > > > shank wrote: >> "Mike Brind" <paxton***@hotmail.com> wrote in message > >> news:1142372144.124894.240770@i39g2000cwa.googlegroups.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"),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 > %> <% 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 Mike Brind wrote:
Show quote > Dim DataConn, SQL, cmd, ,ordno,qtyarParms> 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: 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" Bob Barrows [MVP] wrote:
Show quote > Mike Brind wrote: **NOTE**> > > > 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 The above line should be set .activeconnection=dataconn Show quote > end with Thanks Bob.> > > '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 > %> > To shank: Now you have no excuse NOT to use parameters instead of dynamic sql :-) -- Mike Brind
Show quote
> Better yet: The below does not insert any data. I changed what I thought was a few typos > 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" - - - - - - - - - - - - - - - - - - - - - - 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 %> shank wrote:
> The below does not insert any data. I changed what I thought was a Assigning a string to a variable does not run the sql statement.> few typos with no luck. It lookd kind of backwards to me. Shouldn't > the variables be filled "before" the SQL statement runs? :-) 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) response.write "About to insert " & ordno & " and " & _> arrName = Split(varTextArea(i),",") > orderno=arrName(0) > qty=arrName(1) > 'validate the data here, then > arParms=array(orderno,qty) qty & " into the database table.<br>" > cmd.Execute ,arParms,128 'adExecuteNoRecords Let us know the result.> 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.
Show quote
"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message I found a couple statements that I missed and also 1 variable.news:uNVfRfESGHA.792@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'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 %> shank wrote:
Show quote > - - - - I was going to say that this means varTextArea is not an array, but if that> 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 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.
Show quote
"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message Shazam! That worked! Thanks!news:Oan9BJFSGHA.2176@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. > - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - - 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! shank wrote:
> Shazam! That worked! Thanks! SQL="UPDATE xtest SET [Qty] = [Qty] + 1 " & _> > 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! "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.
Show quote
"Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message I've evolved to the following. I can insert, but it will not update if a news:OU1sccFSGHA.2276@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. -------------------------------------------------------------- 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 %> shank wrote:
> I've evolved to the following. I can insert, but it will not update Give me some clue about what you really want to have happen here. :-)> 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 > 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. shank wrote:
<snip> Show quote > I've evolved to the following. I can insert, but it will not update if a According to the above code, and your previous posts, what you are> 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 > %> 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
Show quote
"Mike Brind" <paxton***@hotmail.com> wrote in message That is correct!news:1142457046.464607.98760@i39g2000cwa.googlegroups.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<< >>most applications something like an order number is usually unique, and I'm trying to allow users that know our order system to cut-n-paste large >>can't/shouldn't be used again<< 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 You're right! That was an oversight. The Qty should be increased by the new >>1.<< qty submitted. thanks! shank wrote:
Show quote > "Mike Brind" <paxton***@hotmail.com> wrote in message Possible correction - I would guess that you are not looking for> news:1142457046.464607.98760@i39g2000cwa.googlegroups.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. > > 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
Show quote
"Mike Brind" <paxton***@hotmail.com> wrote in message The match would be AffNo and OrderNo.news:1142459714.310680.255990@j33g2000cwa.googlegroups.com... > > shank wrote: >> "Mike Brind" <paxton***@hotmail.com> wrote in message >> news:1142457046.464607.98760@i39g2000cwa.googlegroups.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 -------------------------------- 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! shank wrote:
Show quote > "Mike Brind" <paxton***@hotmail.com> wrote in message This should do it:> news:1142459714.310680.255990@j33g2000cwa.googlegroups.com... > > > > shank wrote: > >> "Mike Brind" <paxton***@hotmail.com> wrote in message > >> news:1142457046.464607.98760@i39g2000cwa.googlegroups.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! 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
Show quote
"Mike Brind" <paxton***@hotmail.com> wrote in message This is great! Thanks!news:1142501967.891787.256020@p10g2000cwp.googlegroups.com... > > shank wrote: >> "Mike Brind" <paxton***@hotmail.com> wrote in message >> news:1142459714.310680.255990@j33g2000cwa.googlegroups.com... >> > >> > shank wrote: >> >> "Mike Brind" <paxton***@hotmail.com> wrote in message >> >> news:1142457046.464607.98760@i39g2000cwa.googlegroups.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 --------------------------------------- Bob Barrows [MVP] wrote:
> SQL = "INSERT INTO xtest ([AffNo],[OrderNo],[Qty]) " Oh wait! You added something.> SQL = SQL & "VALUES ('" & Session("AffNo") & "',?,?) " 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. shank wrote:
Show quote > "Bob Barrows [MVP]" <reb01501@NOyahoo.SPAMcom> wrote in message (1)I don't know why you changed it back to this ^^^^^^> news:uNVfRfESGHA.792@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"),Chr(13),",")," > ",""),Chr(10),"") > arrName = Split(varTextArea,",") (2) ^^^^^I don't know why you took this operation back outside fo theloop Show quote > (2) ^^^^ This is where I put it - and you kept it there as well> 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) (1) The operation you changed back to a replace was the first split> 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 > %> 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  |
|||||||||||||||||||||||
Other interesting topics