Databases
StackOverflow2013
Postsdbo
PO
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PO
    primarykey
    data
    text
    <p>After some digging I was able to find a solution to this problem. Here is my solution to query from a PostGresql database using the NPGSQL data connector, ASP.NET (VB.NET), Javascript, and AJAX. In my application the AJAX function called "ajaxyThing" receives a value from a custom control in an OpenLayers map but you could pass a value from anything into the function.</p> <p>Here is the code from the Ajax function in my javascript file:</p> <pre><code>function ajaxyThing (selectedFT) { var xmlhttp; //CREATE THE VARIABLE TO HOLD THE XMLHTTPRequest OBJEcT if (window.XMLHttpRequest) {// code for IE7+, Firefox, Chrome, Opera, Safari THESE BROWSERS SUPPORT THE XMLHTTPRequest OBJECT xmlhttp=new XMLHttpRequest(); //CREATE THE XMLHTTPRequest OBJECT } else {// code for IE6, IE5 THESE BROWSERS DO NOT SUPPORT THE XMLHTTPRequest OBJECT AND NEED AND ACTIVEXOBJECT xmlhttp=new ActiveXObject("Microsoft.XMLHTTP"); //CREATE THE ActiveXObject } //When using Async = true specify a function to execute when the reponse is ready in the onradystatechange event xmlhttp.onreadystatechange = function () { if (xmlhttp.readyState == 4 &amp;&amp; xmlhttp.status == 200) { document.getElementById("divAttributes").innerHTML = xmlhttp.responseText; } } //TO SEND A REQUEST TO A SERVER, WE USE THE open() AND send() METHODS OF THE XMLHttpRequest object xmlhttp.open("GET", "Handler.ashx?q="+ selectedFT, true); xmlhttp.send(); } </code></pre> <p>Then in Microsoft Visual Web Developer Express 2010 I created a Web Handler (.ashx) file and passed the variable from my AJAX function into it. I pass the variable "selectedFT" into the Visual Studio Web Handler. Here is the code from the web handler:</p> <pre><code>&lt;%@ WebHandler Language="VB" Class="Handler" %&gt; Imports System Imports System.Web Imports Npgsql Imports System.Data Public Class Handler : Implements IHttpHandler Public Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequest context.Response.Expires = -1 Dim q = context.Request.QueryString("q") Dim pgConnection As NpgsqlConnection = New NpgsqlConnection Dim pgCommand As NpgsqlCommand = New NpgsqlCommand Dim pgConnectionString As String Dim sda As NpgsqlDataAdapter Dim ds As DataSet ds = New DataSet pgConnectionString = "Server=localhost;Port=5432;Userid=myPostGresUserId;Database=myDatabaseName;password=myPassword;Protocol=3;SSL=false;Pooling=true;MinPoolSize=1;MaxPoolSize=20;Encoding=UNICODE;Timeout=15;SslMode=Disable" pgConnection.ConnectionString = pgConnectionString pgConnection.Open() pgCommand.Connection = pgConnection pgCommand.CommandType = CommandType.Text pgCommand.CommandText = "SELECT * FROM ""myTable"" WHERE ""myValue"" = '" &amp; q &amp; "';" sda = New NpgsqlDataAdapter(pgCommand) sda.Fill(ds) If pgConnection.FullState = ConnectionState.Open Then context.Response.Write("&lt;table&gt;") Dim dr As DataRow Dim dt As DataTable dt = ds.Tables(0) For Each dr In dt.Rows context.Response.Write("&lt;tr&gt;&lt;td&gt;&lt;b&gt;Column #1 Name:&lt;/b&gt;&lt;/td&gt;&lt;td&gt;" &amp; dr.Item(0) &amp; "&lt;/td&gt;&lt;/tr&gt;") context.Response.Write("&lt;tr&gt;&lt;td&gt;&lt;b&gt;Column #2 Name:&lt;/b&gt;&lt;/td&gt;&lt;td&gt;" &amp; dr.Item(1) &amp; "&lt;/td&gt;&lt;/tr&gt;") context.Response.Write("&lt;tr&gt;&lt;td&gt;&lt;b&gt;Column #3 Name:&lt;/b&gt;&lt;/td&gt;&lt;td&gt;" &amp; dr.Item(2) &amp; "&lt;/td&gt;&lt;/tr&gt;") Next ds.Dispose() context.Response.Write("&lt;/table&gt;") Else context.Response.Write("pgConnection did not open successfully.") End If pgConnection.Close() End Sub Public ReadOnly Property IsReusable() As Boolean Implements IHttpHandler.IsReusable Get Return False End Get End Property End Class </code></pre> <p>I won't claim that this is the best solution to the problem - but it works. I would be open to any suggestions you may have to make this more elegant. Thanks!</p>
    singulars
    1. This table or related slice is empty.
    1. POConnect to PostgreSQL using VB.NET, AJAX and NPGSQL
      singulars
      1. PTQuestion
    1. PTAnswer
    1. This table or related slice is empty.
    1. USTodd Krueger
    plurals
    1. This table or related slice is empty.
    1. This table or related slice is empty.
    1. POConnect to PostgreSQL using VB.NET, AJAX and NPGSQL
      singulars
      1. PTQuestion
    1. This table or related slice is empty.
    1. VO
      singulars
      1. PO
      1. This table or related slice is empty.
      1. VTAcceptedByOriginator
    1. COI would suggest you to use NpgsqlParameter instead of doing string concatenation when building your query. By using concatenation you are susceptible to sql injection problems. More info about how to use NpgsqlParameter can be found in the user manual: http://manual.npgsql.org I hope it helps.
      singulars
      1. PO
      1. USFrancisco Junior
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload