May 9, 2007 at 12:19 pm
I need help Updating the table
Function Main()
Dim cn '* As New ADODB Connection
Dim strConn
dim rs
Dim strSQL
Set cn= CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.RecordSet")
' Connection to SQL Server without using ODBC data source
strConn = "Driver={SQL Server};Server=" & DTSGlobalVariables("gvSServer").Value & ";Uid=ErikDBO;Pwd=" & DTSGlobalVariables("gvSPwd").Value & ";Database=" & DTSGlobalVariables("gvSDB").Value
cn.Open strConn
set rs = nothing
compId=DTSGlobalVariables("CompanyInput").Value
strSQL ="Select intId,IntPArentId , txtCaption, txtTitle, intCompanyID from tblNavigation where intCompanyId= "& compId &""
Set rs = cn.Execute(strSQL)
if rs.eof Then
MsgBox ( "There are no records found")
Main=DTSTaskExecResult_Failure
else
'Once we get a record we can create a connection object and do the Update
dim txtTitle
dim strConn2
Dim dcn '* As New ADODB Connection
set dcn =CreateObject( "ADODB.Connection")
Set rs2 = CreateObject("ADODB.RecordSet")
strConn2= "Driver={SQL Server};Server=" & DTSGlobalVariables("gvDServer").Value & ";Uid=ErikDBO;Pwd=" & DTSGlobalVariables("gvDPwd").Value & ";Database=" & DTSGlobalVariables("gvDDB").Value
dcn.open StrConn2
while not rs.eof
intId=rs.Fields("intId").value
intParentId=rs.Fields("intParentId").value
txtCaption=rs.Fields("txtCaption").value
txtTitle=rs.Fields("txtTitle").value
compId=rs.Fields("intCompanyId").Value
newCompId=DTSGlobalVariables("CompanyOutput").Value
Dim strSql2
dim strNewTitle
strNewTitle = Replace(txtTitle, "'", "''")
strNewTitle = replace(strNewTitle, "/", "-")
strNewCaption=Replace(txtCaption, "'", "''")
strNewCaption = replace(strNewCaption, "/", "-")
strSql2=" Create TABLE #TempNavigation (intid int , intParentId int, txtCaption varchar (255) ,txtTitle varchar (255), intCompanyId int)" &_
" Insert into #TempNavigation (intId, intParentId, txtCaption,txtTitle,intCompanyId) Values (" & intId & ", " & intParentId & ", '" & strNewCaption & "', '" & strNewTitle & "', " & compId & ") "&_
" Update dn SET intParentId=dn2.intID FROM tblNavigation dn Inner join #TempNavigation sn on sn.intParentId=dn.intParentId " &_
" Inner join #TempNavigation sn2 on sn.intParentId=sn2.intId" &_
" Inner join tblNavigation dn2 on dn2.txtCaption=sn2.txtCaption and dn2.txtTitle=sn2.txtTitle" &_
" where sn.intCompanyId= " & compId & " and sn2.intCompanyId= " & compId & " AND dn.intCompanyId = " & newCompId & " AND dn2.intCompanyId= " & newCompId &"" &_
" Drop Table #TempNavigation"
dcn.Execute (strSql2)
rs.MoveNext
wend
Msgbox strSql2
set rs = nothing
'cn.close
set cn = nothing
dcn.Close
set dcn=nothing
End if
Main=DTSTaskExecResult_Success
End Function
Web programmer
May 9, 2007 at 1:28 pm
It's kind of hard for us to help you if we don't know what's going wrong with your code!!
May 9, 2007 at 1:39 pm
I am trying to update the table Since I already copied data the Id's on thedestination tables has been changed
Web programmer
May 9, 2007 at 1:44 pm
Why not simply drop the destination table first, and then reimport it?
May 9, 2007 at 1:47 pm
Because the table contains other data
Web programmer
May 9, 2007 at 1:52 pm
What error are you getting?
What is not going on as you'd want it to go?
May 9, 2007 at 1:57 pm
it does not give me any error it just does not update the table
Web programmer
May 9, 2007 at 2:27 pm
Why are you not joining simply on the id column(s)?
Try changing the update query to a select and see if it returns row(s) (which I would think not).
If no rows are returned, start rebuilding the statment one join at the time and run at each table you add. That will tell you where things are going wrong.
May 9, 2007 at 2:37 pm
This is mighty convoluted process for a single statement being sent to the database:
strSql2=" Create TABLE #TempNavigation (intid int , intParentId int, txtCaption varchar (255) ,txtTitle varchar (255), intCompanyId int)" &_
" Insert into #TempNavigation (intId, intParentId, txtCaption,txtTitle,intCompanyId) Values (" & intId & ", " & intParentId & ", '" & strNewCaption & "', '" & strNewTitle & "', " & compId & ") "&_
" Update dn SET intParentId=dn2.intID FROM tblNavigation dn Inner join #TempNavigation sn on sn.intParentId=dn.intParentId " &_
" Inner join #TempNavigation sn2 on sn.intParentId=sn2.intId" &_
" Inner join tblNavigation dn2 on dn2.txtCaption=sn2.txtCaption and dn2.txtTitle=sn2.txtTitle" &_
" where sn.intCompanyId= " & compId & " and sn2.intCompanyId= " & compId & " AND dn.intCompanyId = " & newCompId & " AND dn2.intCompanyId= " & newCompId &"" &_
" Drop Table #TempNavigation"
I'm assuming it is the "Update dn" portion that you are concerned with and what is failing?
As long as you continue to use the same connection you could bust that up into multile statements (it would be easier to debug)
I would recommend you get a set of "test" data and then run a manual test with the above statement in Query Analyzer (or Management Studio). It is most likely one of the "INNER JOINS" is failing to find a match and nothing is pulled back that is capable of actually performing the update.
So my recommendation is manually test each step with test data, outside the DTS area.
James.
May 10, 2007 at 7:25 am
I broke the query down but still unable to update the table
Function Main()
Dim cn '* As New ADODB Connection
Dim strConn
dim rs
Dim strSQL
Set cn= CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.RecordSet")
' Connection to SQL Server without using ODBC data source
strConn = "Driver={SQL Server};Server=" & DTSGlobalVariables("gvSServer").Value & ";Uid=ErikDBO;Pwd=" & DTSGlobalVariables("gvSPwd").Value & ";Database=" & DTSGlobalVariables("gvSDB").Value
cn.Open strConn
set rs = nothing
compId=DTSGlobalVariables("CompanyInput").Value
strSQL ="Select intId,IntPArentId , txtCaption, txtTitle, intCompanyID from tblNavigation where intCompanyId= "& compId &""
Set rs = cn.Execute(strSQL)
if rs.eof Then
MsgBox ( "There are no records found")
Main=DTSTaskExecResult_Failure
else
'Once we get a record we can create a connection object and do the Update
dim txtTitle
dim strConn2
Dim dcn '* As New ADODB Connection
set dcn =CreateObject( "ADODB.Connection")
Set rs2 = CreateObject("ADODB.RecordSet")
strConn2= "Driver={SQL Server};Server=" & DTSGlobalVariables("gvDServer").Value & ";Uid=ErikDBO;Pwd=" & DTSGlobalVariables("gvDPwd").Value & ";Database=" & DTSGlobalVariables("gvDDB").Value
dcn.open StrConn2
while not rs.eof
intId=rs.Fields("intId").value
intParentId=rs.Fields("intParentId").value
txtCaption=rs.Fields("txtCaption").value
txtTitle=rs.Fields("txtTitle").value
compId=rs.Fields("intCompanyId").Value
newCompId=DTSGlobalVariables("CompanyOutput").Value
Dim strSql2
dim strNewTitle
strNewTitle = Replace(txtTitle, "'", "''")
strNewTitle = replace(strNewTitle, "/", "-")
strNewCaption=Replace(txtCaption, "'", "''")
strNewCaption = replace(strNewCaption, "/", "-")
strSql2=" Create TABLE #TempNavigation (intid int , intParentId int, txtCaption varchar (255) ,txtTitle varchar (255), intCompanyId int)"
dcn.Execute(strSql2)
Dim strSql3
strSql3=" Insert into #TempNavigation (intId, intParentId, txtCaption,txtTitle,intCompanyId) Values (" & intId & ", " & intParentId & ", '" & strNewCaption & "', '" & strNewTitle & "', " & compId & ") "
dcn.Execute (strSql3)
Dim strSql4
strSql4=" Update dn SET intParentId=dn2.intID FROM tblNavigation dn" &_
" Inner join #TempNavigation sn on sn.intParentId=dn.intParentId " &_
" Inner join #TempNavigation sn2 on sn.intParentId=sn2.intId" &_
" Inner join tblNavigation dn2 on dn2.txtCaption=sn2.txtCaption and dn2.txtTitle=sn2.txtTitle" &_
" where sn.intCompanyId= " & compId & " and sn2.intCompanyId= " & compId & " AND dn.intCompanyId = " & newCompId & " AND dn2.intCompanyId= " & newCompId &""
dcn.Execute (strSql4)
Dim strSql5
strSql5= " Drop Table #TempNavigation"
dcn.Execute (strSql5)
rs.MoveNext
wend
set rs = nothing
'cn.close
set cn = nothing
dcn.Close
set dcn=nothing
End if
Main=DTSTaskExecResult_Success
End Function
Web programmer
May 10, 2007 at 7:51 am
What JLK said is correct. You need to test your update statement in Query Analyzer. Simply use a select statement instead of an update.
I believe that you'll find that your select returns zero records.
Keep modifying your select until it returns the record you need and then modify your update statement.
And why 'web programmer' are you doing all this inside a DTS?
Write 2 procs for use in your webpage and do your if test in the webpage.
If the 1st proc finds a record then use the second proc to do the update.
Simply pass all your fields to the update proc. A heck of a lot less overhead and the procs would be optimized, where as your dynamic sql is not optimized and requires an recompile each time it is executed.
At least call the procs in the DTS instead of using the dynamic sql.
HTH.
Regards,
Matt
May 10, 2007 at 7:56 am
I am a web programmer I am just doing this on the back end.
I wrote stored procedures and they worked, but the company does not want to use SP. So I put the SP into the query
Web programmer
May 10, 2007 at 8:11 am
Ok, that is a little better. From your code, it appears that only A SINGLE record is being inserted into TEMP table. You then go and join that single record to itself, why? Are you by any chance expecting that temp table will have more than one row? I would GUESS this is the current point of failure.
James.
May 10, 2007 at 8:17 am
How can fix the problem so it will populate all the data
Web programmer
May 10, 2007 at 8:32 am
I'm just guessing based on your code but change the script to look like this: (Please note my comments, marked with '***)
Function Main()
Dim cn '* As New ADODB Connection
Dim strConn
dim rs
Dim strSQL
Set cn= CreateObject("ADODB.Connection")
Set rs = CreateObject("ADODB.RecordSet") '*** SINCE you destroy this 4 lines later WHY BOTHER
' Connection to SQL Server without using ODBC data source
strConn = "Driver={SQL Server};Server=" & DTSGlobalVariables("gvSServer").Value & ";Uid=ErikDBO;Pwd=" & DTSGlobalVariables("gvSPwd").Value & ";Database=" & DTSGlobalVariables("gvSDB").Value
cn.Open strConn
set rs = nothing
compId=DTSGlobalVariables("CompanyInput").Value
strSQL ="Select intId,IntPArentId , txtCaption, txtTitle, intCompanyID from tblNavigation where intCompanyId= "& compId &""
Set rs = cn.Execute(strSQL)
if rs.eof Then
MsgBox ( "There are no records found")
Main=DTSTaskExecResult_Failure
else
'Once we get a record we can create a connection object and do the Update
dim txtTitle
dim strConn2
Dim dcn '* As New ADODB Connection
set dcn =CreateObject( "ADODB.Connection")
Set rs2 = CreateObject("ADODB.RecordSet") '*** WHAT IS THIS FOR IS IT EVER USED****
strConn2= "Driver={SQL Server};Server=" & DTSGlobalVariables("gvDServer").Value & ";Uid=ErikDBO;Pwd=" & DTSGlobalVariables("gvDPwd").Value & ";Database=" & DTSGlobalVariables("gvDDB").Value
dcn.open StrConn2
'*** CREATE THE TEMP TABLE OUTSIDE THE RECORDSET LOOP
Dim strSql2
strSql2=" Create TABLE #TempNavigation (intid int , intParentId int, txtCaption varchar (255) ,txtTitle varchar (255), intCompanyId int)"
dcn.Execute(strSql2)
while not rs.eof
intId=rs.Fields("intId").value
intParentId=rs.Fields("intParentId").value
txtCaption=rs.Fields("txtCaption").value
txtTitle=rs.Fields("txtTitle").value
compId=rs.Fields("intCompanyId").Value
newCompId=DTSGlobalVariables("CompanyOutput").Value
dim strNewTitle
strNewTitle = Replace(txtTitle, "'", "''")
strNewTitle = replace(strNewTitle, "/", "-")
strNewCaption=Replace(txtCaption, "'", "''")
strNewCaption = replace(strNewCaption, "/", "-")
Dim strSql3
strSql3=" Insert into #TempNavigation (intId, intParentId, txtCaption,txtTitle,intCompanyId) Values (" & intId & ", " & intParentId & ", '" & strNewCaption & "', '" & strNewTitle & "', " & compId & ") "
dcn.Execute (strSql3)
rs.MoveNext
wend
set rs = nothing
'*** AT THIS POINT THE TEMP TABLE IS FULLY POPULATED, NOW YOU CAN RUN YOUR UPDATE
Dim strSql4
strSql4=" Update dn SET intParentId=dn2.intID FROM tblNavigation dn" &_
" Inner join #TempNavigation sn on sn.intParentId=dn.intParentId " &_
" Inner join #TempNavigation sn2 on sn.intParentId=sn2.intId" &_
" Inner join tblNavigation dn2 on dn2.txtCaption=sn2.txtCaption and dn2.txtTitle=sn2.txtTitle" &_
" where sn.intCompanyId= " & compId & " and sn2.intCompanyId= " & compId & " AND dn.intCompanyId = " & newCompId & " AND dn2.intCompanyId= " & newCompId &""
dcn.Execute (strSql4)
'*** AND FINALLY DROP THE TEMP TABLE, THOUGH THIS WILL GO AWAY WHEN CONNECTION CLOSED
Dim strSql5
strSql5= " Drop Table #TempNavigation"
dcn.Execute (strSql5)
'cn.close
set cn = nothing
dcn.Close
set dcn=nothing
End if
Main=DTSTaskExecResult_Success
End Function
Viewing 15 posts - 1 through 14 (of 14 total)
You must be logged in to reply to this topic. Login to reply