[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 408
  • Last Modified:

Calling JavaScript to write SQL in ASP using onChange

Hopefully there is a better way to do this, but what I have is a two tables:
ACTIVE DATA
----------------------------------------------
RecNum   Description         Active
  1             A description          [X]
  4             Something else       [X]

INACTIVE DATA
----------------------------------------------
RecNum   Description          Active
  2             Old Description          [ ]
  3             Something different   [ ]  

I want to have the checkboxes under the Active column be interactive.  If you uncheck the box under Active Data, you do an UPDATE SQL statement setting ACTIVE="N" and if you check the box under Inactive Data, you do an UPDATE SQL statement setting ACTIVE="Y".

The form should submit to itself and not use a submit button.

Should I use: onChange(form1.submit()) and then have a hidden field that tells me which recordnum to update?
0
bigtwig
Asked:
bigtwig
  • 12
  • 11
  • 6
6 Solutions
 
gjutrasCommented:
You've got it.  You need to post back to the server with the information some way, so that the server can update the db.  You'll also need to test on the page if that form variable has a value (meaning the page is being posted back) and to fill in the check boxes appropriately, because to the client it will be a new page.
0
 
bugs021997Commented:
You can do form1.submit() but instead of doing that you can even try connecting to the database using javascript as below

  <script type="text/javascript">
  <!--
  var adOpenDynamic = 2
  var adLockOptimistic = 3

  var conn_str = "..."
  var conn = new   ActiveXObject("ADODB.Connection")
  conn.open(conn_str, "",   "")


  function updateTable(id, val) {
    var html = ''
    // var rs = new ActiveXObject("ADODB.Recordset")
    //rs.open(query, conn, adOpenDynamic, adLockOptimistic)
    conn.execute("UPDATE SQL statement setting ACTIVE='" + val + '")

    //rs.close()
    return html
  }
</script>

<input type="text" value="<%=rowId%>" name="row">
<input type="checkbox" onclick="updateTable(<%=rowId%>,'Y')">


<input type="text" value="<%=rowId%>" name="row">
<input type="checkbox" onclick="updateTable(<%=rowId%>,'N')">
0
 
gjutrasCommented:
this will only work if the client can connect directly to the database and has permissions to do so.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
bugs021997Commented:
For a detailed information on JAVASCRIPT and DATABASE interaction refer to the below mentioned article...

http://michaelsync.net/2006/06/14/javascript-working-with-database/
0
 
bugs021997Commented:
@gjutras

And why do you the client won't be able to connect to the database when @bigtwig can already do that using ASP.

0
 
gjutrasCommented:
it's whether his setup has the db on or behind  the webserver or accessible on a lan whether or not client side script or server side script is needed.  I initially assumed server side and you initially assumed client side, but bitwig didn't really indicate where his db is in relation to the client computers or server.
0
 
bigtwigAuthor Commented:
Bugs, I don't understand this:
 <script type="text/javascript">
  <!--
  var adOpenDynamic = 2
  var adLockOptimistic = 3

  var conn_str = "..."
  var conn = new   ActiveXObject("ADODB.Connection")
  conn.open(conn_str, "",   "")


I've got an INCLUDE file at the top of my .ASP page that does this for me.  Do I need to do this again?
The include file looks like this:
 <%      
 Dim oConn
 Set oConn = Server.CreateObject("ADODB.Connection")
oConn.ConnectionTimeout = 15
oConn.Open "DSN=(dsnname);UID=sa;PWD=(password);DATABASE=(database)"%>



Also, does rowID need to be in a HIDDEN field somewhere to pass it or will JavaScript take care of that because it's being passed?
0
 
bigtwigAuthor Commented:
gjutras: I'm using ASP... I hope that answers your question.  Thanks.
0
 
gjutrasCommented:
Where is the database in relation to the client pc and or web server is the real question?
0
 
bigtwigAuthor Commented:
The database is on the Server and I'm working from a Client PC.  Isn't that how ASP works?
0
 
gjutrasCommented:
I didn't quite ask that correctly.  Can the client pc access the database directly (maybe through a DSN)?  If the client pc can access the db, then you can use bugs code, if the client can't, then you need to submit the form with extra info and deal with it in an if block.
0
 
bugs021997Commented:
@bigtwig

Yes you will have include a new connection string for javascript as below...

<script type="text/javascript">
  <!--
  var adOpenDynamic = 2
  var adLockOptimistic = 3

  var conn_str = "DSN=(dsnname);UID=sa;PWD=(password);DATABASE=(database)"
  var conn = new   ActiveXObject("ADODB.Connection")
  conn.open(conn_str, "",   "")
0
 
bigtwigAuthor Commented:
@bugs,
My input statement looks like this:
<input type='checkbox' name='partActive' id='partActive' CHECKED onclick='form.submit()'")

For the ACTIVE parts, I put CHECKED in the input tag.  For the INACTIVE, I put UNCHECKED.
How would the input tags look for the solution you gave me?
Thanks.
0
 
bugs021997Commented:
<input type='checkbox' name='partActive' id='partActive' CHECKED onclick="updateTable(<%=rowId%>,'Y')")

rowId : is how you uniquely identify the row, like the id in your example it is RecNum...
0
 
bugs021997Commented:
@bigtwig

The reason why we are passing the rowId/RecNum is because we need to identify which RecNum has been clicked and whose ACTIVE needs to be set as Y/N. Your update query will have a WHERE CLAUSE as well...
0
 
bigtwigAuthor Commented:
Why can't I do this:
if document.getElementById("partActive").checked=true then {
   SQLString2 = "UPDATE PartSupport SET PartSupport.Active='Y'";
   SQLString2 = SQLString2 + " WHERE PartSupport.PartSupportID="+id; }
else
   SQLString2 = "UPDATE PartSupport SET PartSupport.Active='N"";
   SQLString2 = SQLString2 + " WHERE PartSupport.PartSupportID="+id; }      
0
 
bigtwigAuthor Commented:
I get:
"Error: Safety settings on this computer prohibit accessing a data source on another domain"

on the conn.open(...) statement

Could it be because it's already open?  I have this at the top of the .asp page:
 <%      
Dim oConn
Set oConn = Server.CreateObject("ADODB.Connection")
oConn.ConnectionTimeout = 15
oConn.Open "DSN=(dsnname);UID=sa;PWD=(password);DATABASE=(database)"%>
0
 
gjutrasCommented:
this comes back to my question. Is the database accessable from the client?  From your error, I'd guess no.  If that is the case, you have to submit the form and do the processing server side.
0
 
bigtwigAuthor Commented:
@gjutras: Tell me how I can answer your question.
0
 
gjutrasCommented:
what kind of database is it?  Where is your web server (where you said your db is)?
0
 
bigtwigAuthor Commented:
SQL Server database on a different box than the one I'm using.  I can get to it with Remote Desktop Connection.  It's a dot-6 and I'm at dot-32.
0
 
gjutrasCommented:
ok, connectivity test, open administrtive tools, data sources and go to system dsn and try and make a sql server dsn to try and connect to the database server and the database that you are supposed to be able to use. and at the last screen in the wizard has a test connection button.  Can you connect? Will all the peope who use the web site be able to connect to it? if either question is no, then you have to deal with everything on form.submit.  If you and all clients can connect, then you just have to get your connection string correct and you can use bugs code.
0
 
bigtwigAuthor Commented:
I got "TESTS COMPLETED SUCCESSFULLY!"
0
 
bigtwigAuthor Commented:
If I do onclick(UpdateTable()), will it get to the spot where the connection string is established?
Should the recordset (rs) variables be uncommented?
Should everything go inside the function updateTable?

  <script type="text/javascript">
  <!--
  var adOpenDynamic = 2
  var adLockOptimistic = 3

  var conn_str = "..."
  var conn = new   ActiveXObject("ADODB.Connection")
  conn.open(conn_str, "",   "")


  function updateTable(id, val) {
    var html = ''
    // var rs = new ActiveXObject("ADODB.Recordset")
    //rs.open(query, conn, adOpenDynamic, adLockOptimistic)
    conn.execute("UPDATE SQL statement setting ACTIVE='" + val + '")

    //rs.close()
    return html
  }
</script>
0
 
gjutrasCommented:
I'd set the conn_str by hand to test things out.  The simplest form being
DataSource=serverNameWithDomainName;UID=sa;PWD=(password);DATABASE=(database)

you can usse DSN=dsnName if all clients will have that dsn defined on their systems instead of DataSource =
0
 
gjutrasCommented:
shoot, that won't work if the sql server is in a different domain than yours.  The security model won't let it.
0
 
bigtwigAuthor Commented:
OK, progress.  I changed it to 'Data Source=(dsn name)' and now I get "Data Source name not found and no default driver specified"

What's next?
0
 
gjutrasCommented:
sorry need Provider=SQLOLEDB; in front of DataSource=machineNameWithDomainName

you can't put a dsn name in DataSource= it has to be the internet machine name
0
 
bigtwigAuthor Commented:
It works if I code it like this...
conn.open(conn_str, "(username)", "(password)")

It's hard-coded (above),
conn_str=DataSource=serverNameWithDomainName;UID=sa;PWD=(password);DATABASE=(database)
(without the Provider)
0

Featured Post

Important Lessons on Recovering from Petya

In their most recent webinar, Skyport Systems explores ways to isolate and protect critical databases to keep the core of your company safe from harm.

  • 12
  • 11
  • 6
Tackle projects and never again get stuck behind a technical roadblock.
Join Now