locked
Newbie Query a database using javascript or ajax RRS feed

  • Question

  • User-1386307799 posted

    Sorry, im brand new to ASP, ive searched the forums but cant seem to find a definitive answer.

    I've a web form with a formview that when the user tabs into each field I want to fill in a paragraph of help text on the right of the screen.  I want the help text for each field to be stored in a database table as there are 4 elements, areaTitle, areaHelpText, fieldTitle and FieldHelp text.  My question is:

    Can I add a javascript function that accesses the sql database and brings back the 4 elements to populate them on the screen? (if so any examples)

    Or

    Would I use the built in ajax controls and put the help fields in a updatePanel, accessesing the sql table in the page on load? If so how do I partial postback the panel on the blur of the field I am displaying the help for in javascript?

    OR

    Do I write a webservice to bring back the help fields calling it from the field onBlur event? Ive tried this using the XMLHTTP cjavascript classes but cant seem to get it quite right so would appreciate any examples?

    Thanks in advance to anybody who replies, as I said this is my first application so would appreciate some guidance on the best way and some examples if anybody has any!

    Friday, November 21, 2014 3:49 AM

Answers

  • User-1386307799 posted

    DEVELOPED A SOLUTION!!!

    getHelpInformation.aspx

    <%@ Page Language="VB" AutoEventWireup="false" CodeFile="getHelpInformation.aspx.vb" Inherits="getHelpInformation" %>

    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

    <html xmlns="http://www.w3.org/1999/xhtml">
    <head id="Head1" runat="server">
    <title>Test Call help database from jQuery</title>
    <script type="text/JavaScript"
    src="http://ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.min.js">
    </script>
    <script type="text/JavaScript">
    function getHelp(helpField) {
    PageMethods.getHelp(helpField, OnSuccess, OnFailure);
    }
    function OnSuccess(helpString) {
    if (helpString) {
    $('#helpAreaTitle').text(helpString.split("<>")[0])
    $('#helpAreaText').text(helpString.split("<>")[1])
    $('#helpFieldTitle').text(helpString.split("<>")[2])
    $('#helpFieldText').text(helpString.split("<>")[3])
    }
    }
    function OnFailure(error) {
    alert("error");
    }

    </script>
    </head>
    <body>
    <form id="form1" runat="server">
    <asp:ScriptManager ID="scripman1" runat="server" EnablePageMethods="True">
    </asp:ScriptManager>
    <div>
    Surname: <asp:TextBox ID="appSurname" runat="server" onFocus='getHelp(this.id)'></asp:TextBox>
    <div id='helpAreaTitle'></div>
    <div id='helpAreaText'></div>
    <div id='helpFieldTitle'></div>
    <div id='helpFieldText'></div>


    </form>
    </body>
    </html>

    getHelpInformation.aspx.vb

    Imports System.Data.Sql
    Imports System.Data.SqlClient

    Partial Class getHelpInformation
    Inherits System.Web.UI.Page

    <System.Web.Services.WebMethod()> _
    Public Shared Function getHelp(ByVal fieldName As String) As String

    Dim thisConnection As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True")
    Dim returnString As String

    'Create Command object
     Dim thisCommand As New SqlCommand("SELECT * FROM Help Where fieldName='" & fieldName & "'", thisConnection)

    Try

    ' Open Connection
    thisConnection.Open()
    returnString = "Connection Opened"

    ' Execute Query
    Dim thisReader As SqlDataReader = thisCommand.ExecuteReader()

    While (thisReader.Read())
    returnString = thisReader.GetValue(1) & "<>" & thisReader.GetValue(2) & "<>" & thisReader.GetValue(3) & "<>" & thisReader.GetValue(4)
    End While

    Catch ex As SqlException
    ' Display error
    returnString = "Error: " & ex.ToString()
    Finally
    ' Close Connection
    thisConnection.Close()
    End Try

    Return returnString

    End Function

    End Class

    Obviously I could return an object but I just wanted to get the concept working before doing it right, thanks for the pointers Kevin and Chetan

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, November 26, 2014 9:34 AM

All replies

  • User1918509225 posted

    Hi markpj,

    I suggest that you can just use ajax to call the server side method , in the server method ,you can get the data from database,then return to client side.

    I sugges that you can refer to the links below whihc instruct how to use jquey ajax call server side method:

    http://www.morgantechspace.com/2014/01/Call-Server-side-method-from-JQuery-in-ASP-NET.html

    http://www.aspsnippets.com/Articles/Calling-server-side-methods-using-JavaScript-and-JQuery-in-ASP.Net.aspx

    Best Regards,

    Kevin Shen.

    Sunday, November 23, 2014 10:36 PM
  • User-1386307799 posted

    Thanks for the replies, looks like jQuery is the way forward then.  I take it there is no other way?

    *Update... I've tried it in jQuery and bizarrely it works sometimes but not others, Ive tried setting up another tst page based on the fade example above and again, works sometimes but not others? Any ideas?

    Tuesday, November 25, 2014 6:18 AM
  • User1918509225 posted

    Hi markpj,

    I am glad that you have tried the jquery  in your project.please mark current thread whose answer help you.

    For your new issue ,i suggest that you can post new thread for it.For one thread just contain one topic whihc can be convievent for other member who may have same issue like you.

    Best Regards,

    Kevin Shen.

    Wednesday, November 26, 2014 5:06 AM
  • User-1386307799 posted

    Hi Kevin,

    Thanks, both threads helped but neither actually helped me move forward (as the jQuery, something else im new to, doesnt seem to function as expected consistently).  Ill keep plugging away and if I find something that works then ill post the answer and mark as appropriate

    Mark

    Wednesday, November 26, 2014 7:55 AM
  • User1918509225 posted

    Hi markpj,

    Have you tried to download the demo which from the link below which works very well in my side:
    http://www.aspsnippets.com/Articles/Calling-server-side-methods-using-JavaScript-and-JQuery-in-ASP.Net.aspx

    Best Regards,

    Kevin Shen.

    Wednesday, November 26, 2014 8:17 AM
  • User-1386307799 posted

    Hi,

    Yes I tried but clicking the button doesn't actually return anything.  I then tried another jQuery function to fade a div as a test and that works sporadically (seemingly only if I put an alert before the jQuery???)

    Im now looking at doing it in a vb webMethod but I cant connect to the local database and its just returning an [object] [object] error!

    Thanks again

    Mark

    Wednesday, November 26, 2014 9:26 AM
  • User-1386307799 posted

    DEVELOPED A SOLUTION!!!

    getHelpInformation.aspx

    <%@ Page Language="VB" AutoEventWireup="false" CodeFile="getHelpInformation.aspx.vb" Inherits="getHelpInformation" %>

    <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd">

    <html xmlns="http://www.w3.org/1999/xhtml">
    <head id="Head1" runat="server">
    <title>Test Call help database from jQuery</title>
    <script type="text/JavaScript"
    src="http://ajax.googleapis.com/ajax/libs/jquery/1.7.2/jquery.min.js">
    </script>
    <script type="text/JavaScript">
    function getHelp(helpField) {
    PageMethods.getHelp(helpField, OnSuccess, OnFailure);
    }
    function OnSuccess(helpString) {
    if (helpString) {
    $('#helpAreaTitle').text(helpString.split("<>")[0])
    $('#helpAreaText').text(helpString.split("<>")[1])
    $('#helpFieldTitle').text(helpString.split("<>")[2])
    $('#helpFieldText').text(helpString.split("<>")[3])
    }
    }
    function OnFailure(error) {
    alert("error");
    }

    </script>
    </head>
    <body>
    <form id="form1" runat="server">
    <asp:ScriptManager ID="scripman1" runat="server" EnablePageMethods="True">
    </asp:ScriptManager>
    <div>
    Surname: <asp:TextBox ID="appSurname" runat="server" onFocus='getHelp(this.id)'></asp:TextBox>
    <div id='helpAreaTitle'></div>
    <div id='helpAreaText'></div>
    <div id='helpFieldTitle'></div>
    <div id='helpFieldText'></div>


    </form>
    </body>
    </html>

    getHelpInformation.aspx.vb

    Imports System.Data.Sql
    Imports System.Data.SqlClient

    Partial Class getHelpInformation
    Inherits System.Web.UI.Page

    <System.Web.Services.WebMethod()> _
    Public Shared Function getHelp(ByVal fieldName As String) As String

    Dim thisConnection As New SqlConnection("Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\Database.mdf;Integrated Security=True;User Instance=True")
    Dim returnString As String

    'Create Command object
     Dim thisCommand As New SqlCommand("SELECT * FROM Help Where fieldName='" & fieldName & "'", thisConnection)

    Try

    ' Open Connection
    thisConnection.Open()
    returnString = "Connection Opened"

    ' Execute Query
    Dim thisReader As SqlDataReader = thisCommand.ExecuteReader()

    While (thisReader.Read())
    returnString = thisReader.GetValue(1) & "<>" & thisReader.GetValue(2) & "<>" & thisReader.GetValue(3) & "<>" & thisReader.GetValue(4)
    End While

    Catch ex As SqlException
    ' Display error
    returnString = "Error: " & ex.ToString()
    Finally
    ' Close Connection
    thisConnection.Close()
    End Try

    Return returnString

    End Function

    End Class

    Obviously I could return an object but I just wanted to get the concept working before doing it right, thanks for the pointers Kevin and Chetan

    • Marked as answer by Anonymous Thursday, October 7, 2021 12:00 AM
    Wednesday, November 26, 2014 9:34 AM