Prev: insert ReadText Data into temp table
Next: Inline view with a left join
From: Stephen on 20 May 2010 10:00
Hello all,

Well I am pretty novice on this...
One of my customer use SQL for their primary Database.
That Database is use for their ERP software, now they ask me to manipulate a
table inside so, with that manipulation we should change value of this
table. They use a script with an Excel spreadsheet see the following.

SS-1164 Sp0001 145.23 Jane Doe EXEC ABCUUSERLEAVETIME 'SS-1164',
'Sp0001', 145.227419354839

Column A Column B Column C Column D
Column E

The script that they use ask them to copy the entire E column into the
Window from the script, after clicking the Start button it change the table
adding in this exemple 145.22 to the number in the Database.
The question is actually it calculate the value in the Database and add
145.22 to it. So if in the table ABCUUSERLEAVETIME already contain 2 then
145.22+2=147.22, it work but instead we want to replace (kill) the value 2
into the database and replace it with 145.22.

Fell free to suggest any idea that can help me

Best regards to all of you

Here is the script...

<form name="form_requetes" method="post" action="majheures2.asp">
Entrez ici les requetes (Colonne <strong>E</strong> de votre fichier
EXCEL):
<br>
<textarea name="requetes" cols="50" rows="20"> </textarea>
<br>
<br>
<input type="submit" name="OK" value="OK">
</form>

and majheures.asp...

<%
Dim myTextAreaVariable
'Request.form("requetes")
'MyTextAreaVariable = Request.Form("requetes")

'connexion � la bd
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.open "PROVIDER=SQLOLEDB;DATA SOURCE=mtl00025;User
id=tenrox;Password=tenrox;Initial Catalog=Tenroxr85"


myarray = split(myTextAreaVariable,chr(10))

For Each str in myarray
if (mid(str,1,4) = "EXEC") then
response.write("Traitement de: " & str & "<br>")
objConn.Execute (str)
end if

Next

response.write("<br><br> Le traitement est termine.")

%>


From: John Bell on 20 May 2010 12:12
On Thu, 20 May 2010 10:00:39 -0400, "Stephen"
<stephanedespres(a)hotmail.com> wrote:

>Hello all,
>
>Well I am pretty novice on this...
>One of my customer use SQL for their primary Database.
>That Database is use for their ERP software, now they ask me to manipulate a
>table inside so, with that manipulation we should change value of this
>table. They use a script with an Excel spreadsheet see the following.
>
> SS-1164 Sp0001 145.23 Jane Doe EXEC ABCUUSERLEAVETIME 'SS-1164',
>'Sp0001', 145.227419354839
>
>Column A Column B Column C Column D
>Column E
>
>The script that they use ask them to copy the entire E column into the
>Window from the script, after clicking the Start button it change the table
>adding in this exemple 145.22 to the number in the Database.
>The question is actually it calculate the value in the Database and add
>145.22 to it. So if in the table ABCUUSERLEAVETIME already contain 2 then
>145.22+2=147.22, it work but instead we want to replace (kill) the value 2
>into the database and replace it with 145.22.
>
>Fell free to suggest any idea that can help me
>
>Best regards to all of you
>
>Here is the script...
>
><form name="form_requetes" method="post" action="majheures2.asp">
> Entrez ici les requetes (Colonne <strong>E</strong> de votre fichier
>EXCEL):
> <br>
> <textarea name="requetes" cols="50" rows="20"> </textarea>
> <br>
> <br>
> <input type="submit" name="OK" value="OK">
></form>
>
>and majheures.asp...
>
><%
>Dim myTextAreaVariable
>'Request.form("requetes")
>'MyTextAreaVariable = Request.Form("requetes")
>
>'connexion � la bd
>Set objConn = Server.CreateObject("ADODB.Connection")
>objConn.open "PROVIDER=SQLOLEDB;DATA SOURCE=mtl00025;User
>id=tenrox;Password=tenrox;Initial Catalog=Tenroxr85"
>
>
>myarray = split(myTextAreaVariable,chr(10))
>
>For Each str in myarray
> if (mid(str,1,4) = "EXEC") then
> response.write("Traitement de: " & str & "<br>")
> objConn.Execute (str)
> end if
>
>Next
>
>response.write("<br><br> Le traitement est termine.")
>
>%>
>


Hi

You need to look at the what the procedure ABCUUSERLEAVETIME does. You
may want to update the procedure or if it is used elsewhere may have
to create a new procedure.

Somewhere in the procedure I would expect and update statement to
change column in ABCUUSERLEAVETIME.

if this says something like

UPDATE ABCUUSERLEAVETIME
SET col1 = @param1
WHERE col2 = @param2

you should update this to be

UPDATE ABCUUSERLEAVETIME
SET col1 = col1 + @param1
WHERE col2 = @param2

Post the DDL (definition) for ABCUUSERLEAVETIME if you require more
details.

John
 | 
Pages: 1
Prev: insert ReadText Data into temp table
Next: Inline view with a left join