VB

[Savitha S]

I need some help on calling a user defined function written in vb,in sql through ADO.
I have written a function call removezipcode() in vb.
I need to call this in a batch update statement.
eg.
adoconnect.execute "update table1 set column1=removezipcode(column1)"
It is not recognizing the removezipcode() function.

[Gek]

try:


strSQL = "SELECT column1 FROM table1"
set rec1 = con1.execute(strSQL) ' or something like that

do while not rec1.eof
strSQL = "UPDATE table1 set column1=" & removezipcode(rec1!column1)
call con1.execute(strSQL)
rec1.movenext
loop

rec1.close

[Gek]

uups, don't forget the where clause in the update statement like I did.

Daniel

[remcoploeg]

The where code has also a variable. So where do I put the where code with the variable???

Remco Ploeg
[email protected]

[Sephozzy]

Try this:


Dim SQL as string
SQL = "update table1 set column1 = '" & removezipcode & "'"
adoconnect.execute(SQL)




Don't forget the ' if you're working with text.

[Sephozzy]

Try this:


Dim SQL as string
SQL = "update table1 set column1 = '" & removezipcode(column1) & "'"
adoconnect.execute(SQL)




Don't forget the ' if you're working with text. (the colorcoding in the forum makes you think it's a comment but it actually works.

[Savitha S]

Thank You for your replay.
But, I had written this code.
But the problem with this code is it consumes lot of time as it has to move through the recordset.
I need a solution where I need not move through the recordset

[arunnura]

hai savi
this was the command u used
**********
adoconnect.execute "update table1 set column1=removezipcode(column1)"
***********
use as follow
*********
if the function returns a string then use this

adoconnect.execute "update table1 set column1='" & removezipcode(column1) & "'"



if the function returns a number then use this

adoconnect.execute "update table1 set column1= " & removezipcode(column1)



********

reply
arun,madhan,sathish

[Savitha S]

Thank You,
The problem with this code is it recognizes column1 as a variable and not a field in the table.
I have written code to extract the zipcode from the address which is the function removezipcode.
what i'm trying to do is to pass the data in column1 as an argument to the function and extract the zipcode and update to the column1

[Gek]

your table hopefully has a unique key. then you can try the following:


strSQL = "SELECT column1, ID FROM table1"
set rec1 = con1.execute(strSQL) ' or something like that

do while not rec1.eof
strSQL = "UPDATE table1 set column1=" & _
removezipcode(rec1!column1) & " WHERE ID=" & rec1!ID
call con1.execute(strSQL)
rec1.movenext
loop
rec1.close

[Gek]

what dbms are your working with?
if it's sql server you could try to write a stored prozecure with the same functionality as "removezipcode".

Daniel