shank said:Mike Brind said:shank wrote:
I've evolved to the following. I can insert, but it will not update if a
similar records exists. I'm assuming I need to do a select to test each
record, then determine if it's an INSERT or UPDATE statement. Please
indulge
me a bit longer! I get no errors. Only inserts and no updates.
thanks
<%
varTextArea =Split(Request.Form("TextArea"), vbCrLf)
Dim DataConn,SQL,cmd,orderno,qty,arParms
DataConn.Open MM_JSK_STRING
s = "SELECT [AffNo],[OrderNo],[Qty] FROM xTest WHERE WHERE [AffNo] = ?
AND
[OrderNo] = ?"
Set rs = DataConn.Execute(s)
If (rs.EOF) Then
SQL = "INSERT INTO xtest ([AffNo],[OrderNo],[Qty]) "
SQL = SQL & "VALUES (?,?,?) "
Else
SQL="UPDATE xtest SET [Qty] = [Qty] + 1 " & _
"WHERE [AffNo] = ? AND [OrderNo] = ?"
cmd.CommandText=SQL
arParms=array(Session("AffNo"),orderno)
End If
Set DataConn = Server.CreateObject("ADODB.Connection")
DataConn.Open MM_JSK_STRING
Set cmd=createobject("adodb.command")
with cmd
.commandtext=SQL
.commandtype=1 'adcmdtext
set .activeconnection=DataConn
end with
response.write "About to start looping.<br>"
For i = 0 To UBound(varTextArea)
arrName = Split(varTextArea(i),",")
orderno=arrName(0)
qty=arrName(1)
'validate the data here, then
arParms=array(Session("AffNo"),orderno,qty)
response.write "About to insert " & orderno & " and " & _
qty & " into the database table.<br>"
cmd.Execute ,arParms,128 'adExecuteNoRecords
Next
%>
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.
--------------------------------Mike Brind said:Mike Brind said: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?
shank said:--------------------------------Mike Brind said: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?
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 said:--------------------------------Mike Brind said:shank wrote:
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?
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.
Want to reply to this thread or ask your own question?
You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.