The below code works, but is truncating part of the inserted data.
AffNo is numeric and inserts fine
orderno is alphanumeric and gets truncated at 6 digits
qty is numeric and inserts fine
Not being familiar with the ADO properties, I've tried changing the
following line numbers without success. I've tried 200 and 201 per http://www.w3schools.com/ado/prop_type.asp#datatypeenum
cmd.Execute ,arParms,129 'adExecuteNoRecords
What should that line be?
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 BO WHERE " & _
"[AffNo] = ? AND [OrderNo] = ?"
arParms=array(Session("AffNo"),orderno)
cmd.commandtext=s
Set rs = cmd.Execute(,arParms)
If (rs.EOF) Then
SQL = "INSERT INTO BO ([AffNo],[OrderNo],[Qty]) " & _
"VALUES ('" & Session("AffNo") & "',?,?) "
arParms=array(orderno,qty)
Else
SQL="UPDATE BO SET [Qty] = [Qty] + " & qty & _
" WHERE [AffNo] = ? AND [OrderNo] = ?"
arParms=array(Session("AffNo"),orderno)
End If
cmd.commandtext=SQL
cmd.Execute ,arParms,129 'adExecuteNoRecords
rs.close
set rs = nothing
Next
%> 7 1624
shank wrote: The below code works, but is truncating part of the inserted data. AffNo is numeric and inserts fine orderno is alphanumeric and gets truncated at 6 digits
What is the size of the orderno field in your database?
qty is numeric and inserts fine
Not being familiar with the ADO properties, I've tried changing the following line numbers without success. I've tried 200 and 201 per http://www.w3schools.com/ado/prop_type.asp#datatypeenum
cmd.Execute ,arParms,129 'adExecuteNoRecords
What should that line be?
That's exactly what it should be. The article you are reading is irrelevant.
You are not setting data type properties in this line. The 129 is a
combination of two constants: adCmdText and adExecuteNoRecords - you should
familiarize yourself with the ADO documentation at http://msdn.microsoft.com/library/en...dooverview.asp
What I need to know is:
1. What database are you using?
2. What are the datatypes of the fields in your sql statement? (not the
Format property if you are using Access - just the data types and sizes)
3. How have you verified that the values are not being truncated earlier in
the process (hint - use some response.write statements)?
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 BO WHERE " & _ "[AffNo] = ? AND [OrderNo] = ?"
If all you are doing is seeing if this record exists, there is no need to
return more than one field:
s = "SELECT [OrderNo] FROM BO WHERE " & _
arParms=array(Session("AffNo"),orderno) cmd.commandtext=s Set rs = cmd.Execute(,arParms)
If (rs.EOF) Then
I would be closing this recordset here:
dim DoInsert
If rs.eof then DoInsert = true
rs.close:set rs = nothing
If DoInsert then
SQL = "INSERT INTO BO ([AffNo],[OrderNo],[Qty]) " & _ "VALUES ('" & Session("AffNo") & "',?,?) "
Why are you concatenating this value in instead of utilizing your arParms
array? Do this:
SQL = "INSERT INTO BO ([AffNo],[OrderNo],[Qty]) " & _
"VALUES (?,?,?) "
arParms=array(Session("AffNo"), orderno,qty)
Else SQL="UPDATE BO SET [Qty] = [Qty] + " & qty & _ " WHERE [AffNo] = ? AND [OrderNo] = ?" arParms=array(Session("AffNo"),orderno)
Why are you concatenating qty instead of utilizing the arParms array? Again,
do this:
SQL="UPDATE BO SET [Qty] = [Qty] + ? "
" WHERE [AffNo] = ? AND [OrderNo] = ?"
arParms=array(qty,Session("AffNo"),orderno)
End If
cmd.commandtext=SQL cmd.Execute ,arParms,129 'adExecuteNoRecords rs.close set rs = nothing Next %>
--
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]" <re******@NOyahoo.SPAMcom> wrote in message
news:%2****************@TK2MSFTNGP14.phx.gbl... shank wrote: The below code works, but is truncating part of the inserted data. AffNo is numeric and inserts fine orderno is alphanumeric and gets truncated at 6 digits
What is the size of the orderno field in your database?
qty is numeric and inserts fine
Not being familiar with the ADO properties, I've tried changing the following line numbers without success. I've tried 200 and 201 per http://www.w3schools.com/ado/prop_type.asp#datatypeenum
cmd.Execute ,arParms,129 'adExecuteNoRecords
What should that line be?
That's exactly what it should be. The article you are reading is irrelevant. You are not setting data type properties in this line. The 129 is a combination of two constants: adCmdText and adExecuteNoRecords - you should familiarize yourself with the ADO documentation at http://msdn.microsoft.com/library/en...dooverview.asp
What I need to know is: 1. What database are you using? 2. What are the datatypes of the fields in your sql statement? (not the Format property if you are using Access - just the data types and sizes) 3. How have you verified that the values are not being truncated earlier in the process (hint - use some response.write statements)?
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 BO WHERE " & _ "[AffNo] = ? AND [OrderNo] = ?"
If all you are doing is seeing if this record exists, there is no need to return more than one field: s = "SELECT [OrderNo] FROM BO WHERE " & _
arParms=array(Session("AffNo"),orderno) cmd.commandtext=s Set rs = cmd.Execute(,arParms)
If (rs.EOF) Then
I would be closing this recordset here:
dim DoInsert If rs.eof then DoInsert = true rs.close:set rs = nothing
If DoInsert then
SQL = "INSERT INTO BO ([AffNo],[OrderNo],[Qty]) " & _ "VALUES ('" & Session("AffNo") & "',?,?) "
Why are you concatenating this value in instead of utilizing your arParms array? Do this:
SQL = "INSERT INTO BO ([AffNo],[OrderNo],[Qty]) " & _ "VALUES (?,?,?) " arParms=array(Session("AffNo"), orderno,qty)
Else SQL="UPDATE BO SET [Qty] = [Qty] + " & qty & _ " WHERE [AffNo] = ? AND [OrderNo] = ?" arParms=array(Session("AffNo"),orderno)
Why are you concatenating qty instead of utilizing the arParms array? Again, do this:
SQL="UPDATE BO SET [Qty] = [Qty] + ? " " WHERE [AffNo] = ? AND [OrderNo] = ?" arParms=array(qty,Session("AffNo"),orderno)
End If
cmd.commandtext=SQL cmd.Execute ,arParms,129 'adExecuteNoRecords rs.close set rs = nothing Next %>
-- 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"
--------------------------------
What I need to know is:
1. What database are you using?
SQL
2. What are the datatypes of the fields in your sql statement? (not the
Format property if you are using Access - just the data types and sizes)
AffNo numeric
OrderNo varChar(20)
Qty numeric
3. How have you verified that the values are not being truncated earlier in
the process (hint - use some response.write statements)?
Yes
thanks
shank wrote: What I need to know is: 1. What database are you using? SQL
Really? Then you should be using a stored procedure for this.
Also, you should be providing better DDL: http://www.aspfaq.com/5006
2. What are the datatypes of the fields in your sql statement? (not the Format property if you are using Access - just the data types and sizes) AffNo numeric
What are the precision and scale of this column? You haven't just set the
column to numeric without setting the precision and scale have you?
Look up data types in Books Online.
I am going to assume for the sake of example that they are (8,2).
OrderNo varChar(20) Qty numeric
3. How have you verified that the values are not being truncated earlier in the process (hint - use some response.write statements)? Yes
In QA, run this script to create the procedure in your database:
CREATE PROCEDURE UpdateBO (
@AffNo numeric(8,2),
@orderno varchar(20),
@Qty numeric(8,20)) AS
BEGIN
SET NOCOUNT ON
UPDATE BO SET Qty = Qty + @Qty
WHERE [AffNo] = @AffNo AND [OrderNo] = @orderno)
IF @@ROWCOUNT = 0
INSERT INTO BO (AffNo,OrderNo,Qty)
VALUES (@AffNo,@OrderNo,@Qty)
END
go
In ASP:
<%
Dim DataConn,orderno,qty,varTextArea
Set DataConn = Server.CreateObject("ADODB.Connection")
DataConn.Open MM_JSK_STRING
varTextArea =Split(Request.Form("TextArea"), vbCrLf)
For i = 0 To UBound(varTextArea)
arrName = Split(varTextArea(i),",")
orderno=arrName(0)
qty=arrName(1)
'validate data
DataConn.UpdateBO Session("AffNo"),orderno,qty
Next
DataConn.Close: Set DataConn=Nothing
%>
Bob Barrows
--
Microsoft MVP - ASP/ASP.NET
Please reply to the newsgroup. This email account is my spam trap so I
don't check it very often. If you must reply off-line, then remove the
"NO SPAM"
Bob Barrows [MVP] wrote: CREATE PROCEDURE UpdateBO ( @AffNo numeric(8,2), @orderno varchar(20), @Qty numeric(8,20)) AS
Oops - make that
@Qty numeric(8,2)) AS
--
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"
THANKS!!! Works great!
"Bob Barrows [MVP]" <re******@NOyahoo.SPAMcom> wrote in message
news:uz**************@TK2MSFTNGP14.phx.gbl... shank wrote: What I need to know is: 1. What database are you using? SQL
Really? Then you should be using a stored procedure for this.
Also, you should be providing better DDL: http://www.aspfaq.com/5006
2. What are the datatypes of the fields in your sql statement? (not the Format property if you are using Access - just the data types and sizes) AffNo numeric
What are the precision and scale of this column? You haven't just set the column to numeric without setting the precision and scale have you? Look up data types in Books Online.
I am going to assume for the sake of example that they are (8,2).
OrderNo varChar(20) Qty numeric
3. How have you verified that the values are not being truncated earlier in the process (hint - use some response.write statements)? Yes In QA, run this script to create the procedure in your database:
CREATE PROCEDURE UpdateBO ( @AffNo numeric(8,2), @orderno varchar(20), @Qty numeric(8,20)) AS BEGIN SET NOCOUNT ON UPDATE BO SET Qty = Qty + @Qty WHERE [AffNo] = @AffNo AND [OrderNo] = @orderno) IF @@ROWCOUNT = 0 INSERT INTO BO (AffNo,OrderNo,Qty) VALUES (@AffNo,@OrderNo,@Qty) END go
In ASP: <% Dim DataConn,orderno,qty,varTextArea
Set DataConn = Server.CreateObject("ADODB.Connection") DataConn.Open MM_JSK_STRING
varTextArea =Split(Request.Form("TextArea"), vbCrLf)
For i = 0 To UBound(varTextArea) arrName = Split(varTextArea(i),",") orderno=arrName(0) qty=arrName(1) 'validate data DataConn.UpdateBO Session("AffNo"),orderno,qty Next DataConn.Close: Set DataConn=Nothing %>
Bob Barrows -- Microsoft MVP - ASP/ASP.NET Please reply to the newsgroup. This email account is my spam trap so I don't check it very often. If you must reply off-line, then remove the "NO SPAM"
> In ASP: <% Dim DataConn,orderno,qty,varTextArea
Set DataConn = Server.CreateObject("ADODB.Connection") DataConn.Open MM_JSK_STRING
varTextArea =Split(Request.Form("TextArea"), vbCrLf)
For i = 0 To UBound(varTextArea) arrName = Split(varTextArea(i),",") orderno=arrName(0) qty=arrName(1) 'validate data DataConn.UpdateBO Session("AffNo"),orderno,qty Next DataConn.Close: Set DataConn=Nothing %>
Bob Barrows -- Microsoft MVP - ASP/ASP.NET Please reply to the newsgroup. This email account is my spam trap so I don't check it very often. If you must reply off-line, then remove the "NO SPAM"
=======================================
I'm having an issue with the below line. If the user allows his cursor to
add a blank line at the end of list he submits, the qty of the very last
item is doubled. How do I remove blank lines? thanks!
A1,1
B2,2
C3,3
A4,4
A5,5
A6,6
A7,7
A8,8
A9,9<-- if the cursor stops here - no problem
<-- if the cursor stops here - qty of A9 becomes 18
varTextArea =Split(Request.Form("TextArea"), vbCrLf)
<%
Dim DataConn,orderno,qty,varTextArea
Set DataConn = Server.CreateObject("ADODB.Connection")
DataConn.Open MM_JSK_STRING
varTextArea =Split(Request.Form("TextArea"), vbCrLf)
For i = 0 To UBound(varTextArea)
arrName = Split(varTextArea(i),",")
orderno=arrName(0)
qty=arrName(1)
'validate data
DataConn.stp_RES_InsertBO Session("AffNo"),orderno,qty
Next
DataConn.Close: Set DataConn=Nothing
%>
shank wrote: I'm having an issue with the below line. If the user allows his cursor to add a blank line at the end of list he submits, the qty of the very last item is doubled. How do I remove blank lines? thanks!
A1,1 B2,2 C3,3 A4,4 A5,5 A6,6 A7,7 A8,8 A9,9<-- if the cursor stops here - no problem <-- if the cursor stops here - qty of A9 becomes 18
Use an If statement to check the values of the variables, only running the
stored procedure if the values are valid. (That's what I meant by "
'validate data").
Am I missing something? This seems very obvious ...
--
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" This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics
by: Marcin Grzębski |
last post by:
I red MSDN article of C# 2.0 this week... and i found
very strange syntax for properties e.g.:
public int MyIntValue {
get {
// ...
}
protected set {
// ...
}
|
by: News.microsoft.com.DotNet |
last post by:
Hi,
Hope someone can help me out with this. I have a CC3 sharp app that needs to
export data to an excel file. Problem is the version of excel can be 97 to
2003.
Anyway, have been able to use...
|
by: Pablo Ricco |
last post by:
Hi!
I have this problem...
In Visual Basic I am work with ADODB and the next code work perfectly...
Dim cmd as New ADODB.Command
cmd.Properties(15).Value = 2
....
But in .NET..
|
by: Joel Reinford |
last post by:
I would like to build a class that has properties which can be accessed by
string names or index numbers in the form of MyClass.Item("LastName"). The
string names or item index values would be...
|
by: Perre Van Wilrijk |
last post by:
Hi there,
When I started using VB6, I used to write classes with properties and
functions as following ...
Private lngf1 As Long
Private strf2 As String
Public Property Get f1() As Long...
|
by: Rene |
last post by:
We all know that we can't call custom methods or properties form generic
type parameters (<T>) by default because the compiler will complain about
his. For example, the following won't normally...
|
by: VMI |
last post by:
WHy are the get/set properties so useful? I know they're used so that I don't
directly access a variable, but why is that so useful? What would the
difference be between using it directly and using...
|
by: fiaolle |
last post by:
Hi
I have a combobox wich I set the Datasource,Displaymember and Valuemember at
design time. I'm using a Dataset's table. But when I run the form the
combobox isn't filled. Can't I set properties...
|
by: Larry Bud |
last post by:
I'm writing a class to create a specifically formatted fixed width
file. It's 800 characters wide, consisting of approx 30 fields.
So I need to pass 30 variables, maybe 10 are required. Should...
|
by: tshad |
last post by:
In VB 2008, I have a user control added to the page in the PageLoad event -
but the properties are causing me an error.
The program (TakeSurveyTest.aspx) using the control (ContactInfo):
<%@...
|
by: Charles Arthur |
last post by:
How do i turn on java script on a villaon, callus and itel keypad mobile phone
|
by: emmanuelkatto |
last post by:
Hi All, I am Emmanuel katto from Uganda. I want to ask what challenges you've faced while migrating a website to cloud.
Please let me know.
Thanks!
Emmanuel
|
by: BarryA |
last post by:
What are the essential steps and strategies outlined in the Data Structures and Algorithms (DSA) roadmap for aspiring data scientists? How can individuals effectively utilize this roadmap to progress...
|
by: Sonnysonu |
last post by:
This is the data of csv file
1 2 3
1 2 3
1 2 3
1 2 3
2 3
2 3
3
the lengths should be different i have to store the data by column-wise with in the specific length.
suppose the i have to...
|
by: Hystou |
last post by:
There are some requirements for setting up RAID:
1. The motherboard and BIOS support RAID configuration.
2. The motherboard has 2 or more available SATA protocol SSD/HDD slots (including MSATA, M.2...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However,...
|
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows...
|
by: tracyyun |
last post by:
Dear forum friends,
With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new...
| |