Page 1 of 2 12 LastLast
Results 1 to 15 of 24
  1. #1
    Join Date
    Dec 2008
    Posts
    36

    Unanswered: Only one values is inserting into database

    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

  2. #2
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Code:
    ON (cotr.Container_No ='"+cono1(i)+"') and (com.Container_No='"+cono1(i)+"') ")
    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.
    George
    Home | Blog

  3. #3
    Join Date
    Dec 2008
    Posts
    36
    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

  4. #4
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    If you run the SELECT part of the resultant statements against your database, do you get any rows returned?
    George
    Home | Blog

  5. #5
    Join Date
    Dec 2008
    Posts
    36
    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

  6. #6
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    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
    Code:
    ON (cotr.Container_No =com.Container_No) and(com.Container_No='"&cono1(1)&"')"
    should read
    Code:
        ON cotr.Container_No = com.Container_No
    WHERE  com.Container_No= <your value>
    George
    Home | Blog

  7. #7
    Join Date
    Dec 2008
    Posts
    36
    hi thanks for reply
    I changed that also
    stil the result is same...

    Thanks and regards
    RAvi

  8. #8
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Can you post the results of your response.writes?
    George
    Home | Blog

  9. #9
    Join Date
    Dec 2008
    Posts
    36
    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

  10. #10
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    *brainwave*

    Code:
    Dim str_in
        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
    
    sqlstr = "......WHERE com.Container_No IN (" & str_in & ")"
    Will mean only a single round trip!
    George
    Home | Blog

  11. #11
    Join Date
    Dec 2008
    Posts
    36
    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

  12. #12
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Can you show me the resultant sqlstr from the last section?
    George
    Home | Blog

  13. #13
    Join Date
    Dec 2008
    Posts
    36
    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

  14. #14
    Join Date
    Jan 2007
    Location
    UK
    Posts
    11,434
    Provided Answers: 10
    Is there meant to be a space before AMFU8548442?
    George
    Home | Blog

  15. #15
    Join Date
    Dec 2008
    Posts
    36
    hi
    There is space ...
    but after removing the space also its not working,,

    Thanks
    RAvi

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •