Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

Register Log in
  • Congratulations Mike Lewis on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Input Number return info for html display(in HTML forum too)

Status
Not open for further replies.
chaznbs

chaznbs

IS-IT--Management
Jun 5, 2015
23
US
I guess someone thinks this may belong here versus HTML forums. I highlight the section I am having trouble with

I have included my html code for many other eyes to help me figure this out.

Javascript works. One side by on_click and other side by on_change.

Issue is when calling my vbscript.

This is a modified version of a working script that just displayed some data a client wanted to see, but now they want to calculate it.

I think I am pretty close but my mind is just so burnt that I could you some helpful advise as to what I am missing.

Thank you
CHAZ



<HTML>
<HEAD><TITLE>Simple Validation</TITLE>
<style type="text/css">
input { float: center; }
</style>

<script type="text/javascript">

function Mcalc(){
PRC = 0
AVG = 0

if (document.MARGIN.PPrice.value > "")
{ PRC = document.MARGIN.PPrice.value };
document.MARGIN.PPrice.value = eval(PRC);

if (document.MARGIN.AvgCost.value > "")
{ AVG = document.MARGIN.AvgCost.value };
document.MARGIN.AvgCost.value = eval(AVG);

MRG = (PRC - AVG) / PRC * 100;

document.MARGIN.NMargin.value = dm(eval(MRG));
}

function Pcalc(){
MRG = 0
AVG = 0

if (document.MARGIN.PMargin.value > "")
{ MRG = document.MARGIN.PMargin.value };
document.MARGIN.PMargin.value = eval(MRG);

if (document.MARGIN.AvgCost.value > "")
{ AVG = document.MARGIN.AvgCost.value };
document.MARGIN.AvgCost.value = eval(AVG);

PRC = AVG/(1-MRG/100);

document.MARGIN.NPrice.value = dm(eval(PRC));
}

function dm(amount) {
string = "" + amount;
dec = string.length - string.indexOf('.');
if (string.indexOf('.') == -1)
return string + '.00';
if (dec == 1)
return string + '00';
if (dec == 2)
return string + '0';
if (dec > 3)
return string.substring(0,string.length-dec+3);
return string;
}
</script>


</HEAD>
<BODY onLoad="document.MARGIN.txtItem.focus()">

[highlight #FCAF3E]<script language="vbscript" type="text/vbscript">
Function RETRIEVE()

dim dbconnection, sqlrs, itemno, ITEM

Const CONNECT_STRING = "provider=SQLOLEDB.1;Presist Security info=True;User ID=sa;Initial Catalog=DEMODATA;Data Source=SERVER;password=PASSWORD"

Set WshShell = WScript.CreateObject("WScript.Shell")
set objFSO = CreateObject("Scripting.FileSystemObject")
Set dbconnection = createobject("ADODB.connection")
Set sqlrs = createobject("ADODB.Recordset")

dbconnection.open CONNECT_STRING

Itemno = document.MARGIN.txtItem.value

sqlrs.open "SELECT * FROM [VI_USR_MARGIN] where ITEM_NO = '"&Itemno&"' AND LOC_ID='MAIN'", CONNECT_STRING
If sqlrs.EOF Then
Wscript.Echo "Invalid Item. Please Re-Enter."
Else
document.MARGIN.Description.value = sqlrs("DESCR")
document.MARGIN.AvgCost.value = sqlrs("AVG_COST")
document.MARGIN.Price.value = sqlrs("PRC_1")
document.MARGIN.Margin.value = sqlrs("MARGIN")
END IF

sqlrs.close
dbconnection.close

End Function
</script>[/highlight]


<H3>Margin Calculator</H3><HR>
<FORM method=post action='' NAME="MARGIN">
<table>
<tr>
<td align="left">Item Number:</td>
<td align="left"><INPUT NAME="txtItem" TYPE="TEXT" SIZE="10" autofocus></td>
<td align="left">Item Description: </td>
<td align="left"><INPUT NAME="DESCRIPTION" TYPE="TEXT" SIZE="30" readonly tabindex="-1"></td>
</tr>

<tr>
[highlight #FCAF3E]<td align="left"><INPUT TYPE="BUTTON" VALUE="Retrieve Item" onclick ="call RETRIEVE()"></td>[/highlight]
<td align="left"> </td>
<td align="left">Average Cost: </td>
<!-- <td align="left"><INPUT NAME="AvgCost" TYPE="TEXT" SIZE="10" readonly tabindex="-1" ></td> -->
<td align="left"><INPUT NAME="AvgCost" TYPE="TEXT" SIZE="10"></td>
</tr>

<tr>
<td align="left"><BR> </td>
</tr>

<tr>
<td align="left">Current Price: </td>
<td align="left"><INPUT NAME="Price" TYPE="TEXT" SIZE="10" readonly tabindex="-1"></td>
<td align="left">Current Margin: </td>
<td align="left"><INPUT NAME="Margin" TYPE="TEXT" SIZE="10" readonly tabindex="-1"></td>
</tr>

<tr>
<td align="left"><BR> </td>
</tr>

<tr>
<td align="left">Proposed Price: </td>
<td align="left"><INPUT NAME="PPrice" TYPE="TEXT" SIZE="10" onchange="Mcalc()"></td>
<td align="left">Proposed Margin: </td>
<td align="left"><INPUT NAME="PMargin" TYPE="TEXT" SIZE="10"></td>
</tr>

<tr>
<td align="left"></td>
<td align="left"><INPUT TYPE="BUTTON" VALUE="Calc New Margin" onclick ="Mcalc()" ></td>
<td align="left"></td>
<td align="left"><INPUT TYPE="BUTTON" VALUE="Calc New Price" onclick ="Pcalc()" ></td>
</tr>

<tr>
<td align="left"><BR> <BR></td>
</tr>

<tr>
<td align="left">New Margin: </td>
<td align="left"><INPUT NAME="NMargin" TYPE="TEXT" SIZE="10" readonly tabindex="-1"></td>
<td align="left">New Price: </td>
<td align="left"><INPUT NAME="NPrice" TYPE="TEXT" SIZE="10" readonly tabindex="-1"></td>
</tr>

<tr>
<td align="left"><BR> <BR><BR></td>
</tr>

<tr>
<td align="left"><INPUT TYPE="RESET" STYLE="width:100" VALUE="Clear Form" onclick="document.MARGIN.txtItem.focus()"></td>
<td align="left"><INPUT TYPE="BUTTON" STYLE="width:100" VALUE="Copy Data" onclick="document.MARGIN.txtNPrice.select()"></td>
<td align="left">&nbsp; </td>
<td align="left">&nbsp; </td>
<td align="left"><INPUT TYPE="BUTTON" STYLE="width:100" VALUE="Exit" onclick="self.close()"></td>
</tr>
</table>


</FORM>
</BODY>
</HTML>
 
Sort by date Sort by votes
The highlighted.

For somereason I cannot get the on_click to run to the vbscript

Just using the vbscript grab database info to display it in the html window
 
Upvote 0 Downvote
In your code you have:
sqlrs.open "SELECT * FROM [VI_USR_MARGIN] where ITEM_NO = '"&Itemno&"' AND LOC_ID='MAIN'",

You cannot use an apostrophe in VBScript code as it creates a comment. So your line is actually interpreted as
sqlrs.open "SELECT * FROM [VI_USR_MARGIN] where ITEM_NO =

I hope that helps.

Regards,

Mark

No trees were harmed in posting this message, however a significant number of electrons were terribly inconvenienced.

Check out my scripting solutions at
Work SMARTER not HARDER.
 
Upvote 0 Downvote
That why they are inside the quotes. SQL needs them
 
Upvote 0 Downvote
Well, I am still trying different thing I find. No luck.

All this needs to do is connect this stand alone form to the clients sql database when the button is clicked to pull the item and display the info. if there is a better way please advise.

I am not a programmer and the parts that work are working well. And I have far exceeded the 5 hours my bosses allotted me for this job.

Took this on to help develop those skill alittle.
 
Upvote 0 Downvote
1) Are you using Internet Explorer?

2) Hit F12 (or go to Tools and click Developer tools). Click the "Script" tab, reload your page and click the "Retrieve Item" button again. Do you get any errors?
 
Upvote 0 Downvote
That has been the biggest help so far.

It is the select statement.

Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another.

Thank goodness. A new place to look
 
Upvote 0 Downvote
Before trying to open record set, you can write the select statement, to see if everything is ok:
Code: 
...
Itemno = document.MARGIN.txtItem.value

sql_stmt="SELECT * FROM [highlight][VI_USR_MARGIN][/highlight] where ITEM_NO = '" _
         & itemno&"' AND LOC_ID='MAIN'"

Wscript.echo sql_stmt 

sqlrs.open sql_stmt, CONNECT_STRING, 1, 3
...

Btw I don't know what is the highligted item [VI_USR_MARGIN]. It's any convention I don't know ?
I'm using in the SQL always the convention MYSCHEMA.MYTABLE.

To see where is the error, you can try to run your VBscript outside of the webpage on the command line, like
Code: 
C:\Work>cscript /NoLogo [i]your_script_file.vbs[/i]

 
Upvote 0 Downvote
That was the name of the view it is pulling data from since it comes from 3 different tables
 
Upvote 0 Downvote
OK, but with view we use the same convention as with a table, i.e MYSCHEMA.MYVIEW.
Why are the brackets [] there? (I don't know ... myabe it's a convention od your DB)

 
Upvote 0 Downvote
That was how my vbs code was written for another project that works fine.

This exploration into html and java with vbs has been an experience
 
Upvote 0 Downvote
RETRIEVE() is a function, so the CALL should not be used. If I remove it
Code: 
 <td align="left"><INPUT TYPE="BUTTON" VALUE="Retrieve Item" onclick =[highlight]RETRIEVE()[/highlight]></td>
and comment everything in the function except the instruction for creating and printing SQL statement
Code: 
<script language="vbscript" type="text/vbscript">
Function RETRIEVE()
 
dim dbconnection, sqlrs, itemno, ITEM
 
Const CONNECT_STRING = "provider=SQLOLEDB.1;Presist Security info=True;User ID=sa;Initial Catalog=DEMODATA;Data Source=SERVER;password=PASSWORD"
 
'Set WshShell = WScript.CreateObject("WScript.Shell")
'set objFSO = CreateObject("Scripting.FileSystemObject")
'Set dbconnection = createobject("ADODB.connection")
'Set sqlrs = createobject("ADODB.Recordset")
 
'dbconnection.open CONNECT_STRING
 
Itemno = document.MARGIN.txtItem.value
sql_stmt = "SELECT * FROM [VI_USR_MARGIN] where ITEM_NO = '"&Itemno&"' AND LOC_ID='MAIN'"
MsgBox sql_stmt, 1, 3

'sqlrs.open , CONNECT_STRING, 1, 3

' If sqlrs.EOF Then 
'Wscript.Echo "Invalid Item. Please Re-Enter."
'Else
' document.MARGIN.Description.value = sqlrs("DESCR")
' document.MARGIN.AvgCost.value = sqlrs("AVG_COST")
' document.MARGIN.Price.value = sqlrs("PRC_1")
' document.MARGIN.Margin.value = sqlrs("MARGIN")
' END IF
 
'sqlrs.close
' dbconnection.close
 
End Function
 </script>
then when I write in the input box named Item Number: an value 123 and press the button Retrieve Item, then a message box works and shows the SQL statement which seems to be OK.
Code: 
SELECT * FROM [VI_USR_MARGIN] where ITEM_NO = '123' AND LOC_ID='MAIN'
 
Upvote 0 Downvote
mikrom:
1) the brackets [] escape non standard characters or reserved words in access, sql server etc
2) I already mentioned about removing "call"
3) OP is successfully executing the function, but has a specific error message now; "Arguments are of the wrong type, are out of acceptable range, or are in conflict with one another", possibly due to ITEM_NO or LOC_ID not being text values
 
Upvote 0 Downvote
Sorry I did not notice that.
With the wrong type, IMO the solution would be to try to execute the sql statetemt directly on the database client and to look at the types of the affected fields.
 
Upvote 0 Downvote
Maybe the itemno should be without apostrophes, i.e 123 and not '123'
 
Upvote 0 Downvote
Ugh... What a pain.

My test code for funtion work if run as a vbs file:

on error resume next

dim dbconnection, sqlrs, itemno, sqlrs2

const CONNECT_STRING = "provider=SQLOLEDB.1;Presist Security info=True;User ID=sa;Initial Catalog=DEMODATA;Data Source=SERVER;password=PASSWORD"

Set WshShell = WScript.CreateObject("WScript.Shell")
set objFSO = CreateObject("Scripting.FileSystemObject")
Set dbconnection = createobject("ADODB.connection")
Set sqlrs = createobject("ADODB.Recordset")
Set sqlrs2 = createobject("ADODB.Recordset")

dbconnection.Open CONNECT_STRING

itemno = "999999"

sqlrs.open "SELECT * FROM [IM_INV] where ITEM_NO = '"&itemno&"' AND LOC_ID='MAIN'", CONNECT_STRING
sqlrs2.open "SELECT * FROM [VI_USR_MARGIN] where ITEM_NO = '"&itemno&"'", CONNECT_STRING
If sqlrs.EOF Then
Wscript.Echo "Invalid Item. Please Re-Enter."
Else
msgbox ("Item Description "&sqlrs2("DESCR")& vbCrLf & "Current Average Cost: $"& ROUND(sqlrs2("AVG_COST"),4)& vbCrLf & "Current Margin: "&ROUND(sqlrs2("MARGIN"),4)&"%"&""),0,"Item Number: "&sqlrs("ITEM_NO")
END IF

sqlrs.close
dbconnection.close

As soon as I use it inside the html file I get nothing. No error in IE either when I f12 it.

Is there some other way to run?

I know its a VBS forum but is there a way to get it to work in javascript?

Anything will help.

html file is being stored on a separate computer but it does not get called up in web format. HTML file get call up directly.

I will provide whatever info I can for the use of the extra brainpower. I am so far from being a programmer and my mind is fried.
 
Upvote 0 Downvote
VBScript/WScript .vbs files run in a Windows Script Host (WSH) context not in a browser.

( Windows key + R or Start -> Run )


but is there a way to get it to work in javascript?
Click to expand...
Nope, javascript cannot connect directly to a database, and a remote javascript is not allowed to access anything on the local computer other than cookies and history locations.

Chris.

Indifference will be the downfall of mankind, but who cares?
Time flies like an arrow, however, fruit flies like a banana.
Webmaster Forum
 
Upvote 0 Downvote
Ok that is why the box doesn't popup. But that was only for testing anyways.

Is that also why it wont read into SQL and fill the variables?

Would doing something like writing to a text file and read it into vbscript work better then read the results into another text file to go to html through javascript?

Again I am so not a programmer. Just trying to find a way
 
Upvote 0 Downvote
Status
Not open for further replies.

Similar threads

bdwilcox
  • Locked
  • Question
Script seeing input variable value as blank but sees hard-coded value 4
Replies
6
Views
577
bdwilcox
bdwilcox
bdwilcox
  • Locked
  • Question
Validate that HTA text input boxes aren't blank on submission
Replies
1
Views
203
bdwilcox
bdwilcox
bdwilcox
  • Question
Trying to adapt VBScript to search if machine is in an AD group 6
Replies
8
Views
861
SoftwareRT
SoftwareRT
lunter
  • Locked
  • Question
How to create vbArray+vbByte (8209) in *.vbs stript
Replies
0
Views
168
lunter
lunter
TvdH1981
  • Locked
  • Question
vbs script to create folders + subfolders by input foldernames 1
2
Replies
32
Views
862
mikrom
mikrom

Part and Inventory Search

Sponsor

Back
Top