asked on

Executing an SQL statement in Javascript to access forename and surname from SQL Server Database

Hello,

This is what I have in place:

1) The forename and surname of the Windows logged on user in a string.
2) I have "split" the string in JavaScript to get the forename and surname separately in an array

function init(){
            var uname = "<%=Replace(Request.ServerVariables("LOGON_USER"),"Domain\","")%>";
            var arr = uname.split(" ");
            var fname = (arr[0]);
            var sname = (arr[1]);
}

This works fine.

This is what I need (if any of it is possible!) :

1) I want to execute an SQL statement in above JavaScript function to find the user above (forename and surname) in the SQL Server 2005 database. Something like: SELECT * FROM users WHERE Firstname = fname AND Surname = sname (I know this isn't the proper syntax, I'm just trying to get the logic across).
I also need to know how to connect to the database before executing the statement.

2) I then need to check a particular column in that record, but I have two issues with this:
a) I need to have an INSERT INTO query when a page loads (aspx page) and currently have:
<asp:SqlDataSource runat="server" id="SqlDataSource1" ConnectionString="<%$ ConnectionStrings:Phone extensionsViewer %>" ProviderName="<%$ ConnectionStrings:Phone extensionsViewer.ProviderName %>" InsertCommand="INSERT INTO Phone Extensions$ ('column name') VALUES ('True')"></asp:SqlDataSource>
- This doesn't work!! (But I think I can look into this myself)

b) The other issue I have is in regards to number 1 above. I would like, after selecting the record for the user, to check that the column I insert into as in step a, contains the value "true". So something like: SELECT column name FROM users WHERE Firstname = fname AND Surname = sname > from this, check that "column name" = 'true'.

I apologise if I have made this sound really complicated... it does need breaking down in explanation more so please ask me to be clearer if needed, but I would appreciate solutions to any part of the above if not all.

Also, I am using aspx pages.

Thank you in advance.

SOLUTION

brad2575

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

sal-ee

ASKER

brad2575, thanks for that. However, isn't there a way I can use VBScript or ASP to execute the SQL statements then pass the values back into variables and manipulate them in Javascript? I have an SQL select statement that works already on an aspx page that uses a gridview... can't I do something like that and just pass the query results through to Javascript?

brad2575

You can manipulate the data BEFORE it is sent to JavaScript, yes. So when the page is loading you query the database and do whatever you need then set the values to whatever you did on the code.

You just can not execute SQL FROM JavaScript AFTER the page loads. You would have to reload the page to do this.

sal-ee

ASKER

OK, I have something but it's not working... doesn't seem to be connecting to the database to execute the query. My code is:

<script runat="server">
      Sub Page_Load(ByVal Sender as Object, ByVal E as EventArgs)
      End Sub

      Sub Submit_Click(Sender As Object, E As EventArgs)
            Dim uname
            Dim sCommand
            uname = Replace(Request.ServerVariables("LOGON_USER"),"Domain\","")
            Dim DBConn as SQLConnection
            Dim DBUpdate As New SQLCommand
DBConn = New SQLConnection("Data Source=SQLSERVER;Initial Catalog=Phones;Persist Security Info=True;User ID=uid;Password='pw';")
            sCommand = "UPDATE ['Phone Extensions'] SET [DISCLAIMER] = 'False' WHERE ([FIRST NAME] + ' ' + [SURNAME] = 'uname')"
            DBUpdate.CommandText = sCommand
            DBUpdate.Connection = DBConn
            DBUpdate.Connection.Open
            DBUpdate.ExecuteNonQuery()
      End Sub
</script>

I know my UPDATE query works as I ran this separately in the SQL database itself.

Any suggestions?

Thanks =)

brad2575

what is the error you are getting?

ASKER CERTIFIED SOLUTION

sal-ee

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial