Hi all
i am getting vales from checkbox of other page and splitting it and storing it in array then inserting it to database
out of the selected values its inserting only one value
i am getting why??

first i am getting values from checkbox of previous page

conno=request.form("some_name")

Then i am splitting it and storing it in word array
cono1=Split(conno,",")
the highlighted values are also displaying the values correctly
but only one value gets inserted

Thanks and regards
RAvi


then i am inserting it into database

The sql query works fine in sql server

if(flag = true) then
for i=0 to uBound(cono1)
conn.execute("INSERT into tblContainers (Container_ID,Container_No,Seal_No,Carrier,Entered _By,Entered_Date,Origin,Arrival_Date,Arrival_Time) SELECT CONVERT(VARCHAR(8), GETDATE(), 112) + '-' + SUBSTRING(com.container_no, 1, 11) + '-' + SUBSTRING(CAST(NEWID() AS VARCHAR(40)), 1, 3) AS Container_ID,SUBSTRING(com.container_no, 1, 11) AS Container_No,com.Seal_No,com.Carrier,'IGuardTracki ng' AS Entered_By,GETDATE() AS Entered_Date,cotr.Origin,(cast(CONVERT(datetime, com.Arrival_Date, 101) AS varchar(20))) aS Arrival_Date,CONVERT(VARCHAR(20), com.Arrival_Date,109) AS Arrival_Time FROM DehartGroup.dbo.Container_Master AS com inner JOIN DehartGroup.dbo.Container_Tracing AS cotr ON (cotr.Container_No ='"+cono1(i)+"') and (com.Container_No='"+cono1(i)+"') ")
response.write(cono1(i))
Next
end if

In ASP, isn't the concatenator an ampersand (&) not the plus symbol (+)?

Store the SQL string in a variable and then print that out to the page.

Test each propduced statement directly against the database to determine whether it's a problem with the SQL or with the connection object.

Hi thanks for reply
i chage it to + and printed the query on the page
the query is printing correctly
but only the first value gets inserted

i=0
Do While(i<=uBound(cono1))
set conn=Server.createobject("adodb.connection")
conn.Open ConnString
sqlstr="INSERT into tblContainers (Container_ID,Container_No,Seal_No,Carrier,Entered _By,Entered_Date,Origin,Arrival_Date,Arrival_Time) SELECT CONVERT(VARCHAR(8), GETDATE(), 112) + '-' + SUBSTRING(com.container_no, 1, 11) + '-' + SUBSTRING(CAST(NEWID() AS VARCHAR(40)), 1, 3) AS Container_ID,SUBSTRING(com.container_no, 1, 11) AS Container_No,com.Seal_No,com.Carrier,'IGuardTracki ng' AS Entered_By,GETDATE() AS Entered_Date,cotr.Origin,(cast(CONVERT(datetime, com.Arrival_Date, 101) AS varchar(20))) aS Arrival_Date,CONVERT(VARCHAR(20), com.Arrival_Date,109) AS Arrival_Time FROM DehartGroup.dbo.Container_Master AS com inner JOIN DehartGroup.dbo.Container_Tracing AS cotr ON (cotr.Container_No =com.Container_No) and(com.Container_No='"&cono1(1)&"')"

SET rs = conn.execute(sqlstr)
response.write(sqlstr)
response.write(cono1(1))
i=i+1

Loop

end if

Thanks and regards
RAvi

If you run the SELECT part of the resultant statements against your database, do you get any rows returned?

yes i am geeting the rows on running the select part...
one more thing ,i f i give conol(0) i.e hard code values of i,
it works only for the first value ,if i give i=2 or 3 the value doesnt gets inserted

Thanks and regards
RAvi

What I'm trying to ascertain is whether the issue is because your query may be flawed (the inner join filtering out the records, perhaps) or whether it is a problem in the connection not being established (i.e. the queries not running at all).


...in fact your join looks flawed
should read

hi thanks for reply
I changed that also
stil the result is same...

Thanks and regards
RAvi

Can you post the results of your response.writes?

hi

INSERT into tblContainers (Container_ID,Container_No,Seal_No,Carrier,Entered _By,Entered_Date,Origin,Arrival_Date,Arrival_Time) SELECT CONVERT(VARCHAR(8), GETDATE(), 112) + '-' + SUBSTRING(com.container_no, 1, 11) + '-' + SUBSTRING(CAST(NEWID() AS VARCHAR(40)), 1, 3) AS Container_ID,SUBSTRING(com.container_no, 1, 11) AS Container_No,com.Seal_No,com.Carrier,'IGuardTracki ng' AS Entered_By,GETDATE() AS Entered_Date,cotr.Origin,(cast(CONVERT(datetime, com.Arrival_Date, 101) AS varchar(20))) aS Arrival_Date,CONVERT(VARCHAR(20), com.Arrival_Date,109) AS Arrival_Time FROM DehartGroup.dbo.Container_Master AS com inner JOIN DehartGroup.dbo.Container_Tracing AS cotr ON (cotr.Container_No =com.Container_No) where com.Container_No='OOLU1710690' OOLU1710690INSERT into tblContainers (Container_ID,Container_No,Seal_No,Carrier,Entered _By,Entered_Date,Origin,Arrival_Date,Arrival_Time) SELECT CONVERT(VARCHAR(8), GETDATE(), 112) + '-' + SUBSTRING(com.container_no, 1, 11) + '-' + SUBSTRING(CAST(NEWID() AS VARCHAR(40)), 1, 3) AS Container_ID,SUBSTRING(com.container_no, 1, 11) AS Container_No,com.Seal_No,com.Carrier,'IGuardTracki ng' AS Entered_By,GETDATE() AS Entered_Date,cotr.Origin,(cast(CONVERT(datetime, com.Arrival_Date, 101) AS varchar(20))) aS Arrival_Date,CONVERT(VARCHAR(20), com.Arrival_Date,109) AS Arrival_Time FROM DehartGroup.dbo.Container_Master AS com inner JOIN DehartGroup.dbo.Container_Tracing AS cotr ON (cotr.Container_No =com.Container_No) where com.Container_No=' HLXU4196629' HLXU4196629INSERT into tblContainers (Container_ID,Container_No,Seal_No,Carrier,Entered _By,Entered_Date,Origin,Arrival_Date,Arrival_Time) SELECT CONVERT(VARCHAR(8), GETDATE(), 112) + '-' + SUBSTRING(com.container_no, 1, 11) + '-' + SUBSTRING(CAST(NEWID() AS VARCHAR(40)), 1, 3) AS Container_ID,SUBSTRING(com.container_no, 1, 11) AS Container_No,com.Seal_No,com.Carrier,'IGuardTracki ng' AS Entered_By,GETDATE() AS Entered_Date,cotr.Origin,(cast(CONVERT(datetime, com.Arrival_Date, 101) AS varchar(20))) aS Arrival_Date,CONVERT(VARCHAR(20), com.Arrival_Date,109) AS Arrival_Time FROM DehartGroup.dbo.Container_Master AS com inner JOIN DehartGroup.dbo.Container_Tracing AS cotr ON (cotr.Container_No =com.Container_No) where com.Container_No=' HJCU4221384' HJCU4221384

Data Transferd successfully


check this out

Thanks and regards
RAvi

*brainwave*

Will mean only a single round trip!

Hi
i tried that also here goes my code

Dim conno
Dim sqlstr
Dim str_in
conno=request.form("some_name")
Dim ConnString1
ConnString1 = "Driver=SQL Server;server=sakee;uid=sa;pwd=admin;initial catalog=iguard"
set conn1=server.createobject("adodb.connection")
conn1.Open ConnString1
Dim flag,i,cono1
flag = false
if(inStr(conno,","))then
cono1=Split(conno,",")
flag = true
end if
Set conn1=Nothing
Set rs1 = Nothing
if(flag = false)then
Set rs1=conn1.execute("INSERT into tblContainers (Container_ID,Container_No,Seal_No,Carrier,Entered _By,Entered_Date,Origin,Arrival_Date,Arrival_Time) SELECT CONVERT(VARCHAR(8), GETDATE(), 112) + '-' + SUBSTRING(com.container_no, 1, 11) + '-' + SUBSTRING(CAST(NEWID() AS VARCHAR(40)), 1, 3) AS Container_ID,SUBSTRING(com.container_no, 1, 11) AS Container_No,com.Seal_No,com.Carrier,'IGuardTracki ng' AS Entered_By,GETDATE() AS Entered_Date,cotr.Origin,(cast(CONVERT(datetime, com.Arrival_Date, 101) AS varchar(20))) aS Arrival_Date,CONVERT(VARCHAR(20), com.Arrival_Date,109) AS Arrival_Time FROM DehartGroup.dbo.Container_Master AS com INNER JOIN DehartGroup.dbo.Container_Tracing AS cotr ON (cotr.Container_No = com.Container_No) AND (cotr.Seal_No = com.Seal_No) AND (com.Container_No='"&conno&"') ")
set rs1=nothing
end if

str_in = ""

For i=0 to uBound(cono1)
str_in = "'" & cono1(i) & "',"
Next

If uBound(cono1) > 0 Then
str_in = Left(str_in, Len(str_in) - 1)
End If
set conn=Server.createobject("adodb.connection")
conn.Open ConnString
sqlstr="INSERT into tblContainers (Container_ID,Container_No,Seal_No,Carrier,Entered _By,Entered_Date,Origin,Arrival_Date,Arrival_Time) SELECT CONVERT(VARCHAR(8), GETDATE(), 112) + '-' + SUBSTRING(com.container_no, 1, 11) + '-' + SUBSTRING(CAST(NEWID() AS VARCHAR(40)), 1, 3) AS Container_ID,SUBSTRING(com.container_no, 1, 11) AS Container_No,com.Seal_No,com.Carrier,'IGuardTracki ng' AS Entered_By,GETDATE() AS Entered_Date,cotr.Origin,(cast(CONVERT(datetime, com.Arrival_Date, 101) AS varchar(20))) aS Arrival_Date,CONVERT(VARCHAR(20), com.Arrival_Date,109) AS Arrival_Time FROM DehartGroup.dbo.Container_Master AS com inner JOIN DehartGroup.dbo.Container_Tracing AS cotr ON (cotr.Container_No =com.Container_No) where com.Container_No in ( " & str_in & ")"
SET rs = conn.execute(sqlstr)
%>


But still the same result
Only one values gets inserted
Thanks and regards
RAVI Naik

Can you show me the resultant sqlstr from the last section?

hi

INSERT into tblContainers (Container_ID,Container_No,Seal_No,Carrier,Entered _By,Entered_Date,Origin,Arrival_Date,Arrival_Time) SELECT CONVERT(VARCHAR(8), GETDATE(), 112) + '-' + SUBSTRING(com.container_no, 1, 11) + '-' + SUBSTRING(CAST(NEWID() AS VARCHAR(40)), 1, 3) AS Container_ID,SUBSTRING(com.container_no, 1, 11) AS Container_No,com.Seal_No,com.Carrier,'IGuardTracki ng' AS Entered_By,GETDATE() AS Entered_Date,cotr.Origin,(cast(CONVERT(datetime, com.Arrival_Date, 101) AS varchar(20))) aS Arrival_Date,CONVERT(VARCHAR(20), com.Arrival_Date,109) AS Arrival_Time FROM DehartGroup.dbo.Container_Master AS com inner JOIN DehartGroup.dbo.Container_Tracing AS cotr ON (cotr.Container_No =com.Container_No) where com.Container_No in ( ' AMFU8548442')

Thanks
RAvi

Is there meant to be a space before AMFU8548442?

hi
There is space ...
but after removing the space also its not working,,

Thanks
RAvi