Avatar of sal-ee
sal-eeFlag for United Kingdom of Great Britain and Northern Ireland 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.
DatabasesJavaScriptScripting Languages

Avatar of undefined
Last Comment
sal-ee

8/22/2022 - Mon
SOLUTION
brad2575

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
ASKER
sal-ee

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.
ASKER
sal-ee

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 =)
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy
brad2575

what is the error you are getting?

ASKER CERTIFIED SOLUTION
sal-ee

Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
or
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question