Link to home
Start Free TrialLog in
Avatar of cbarber22
cbarber22

asked on

Add SQL Query & Variable to SharePoint ASPX Page

I have created an aspx page on a sharepoint 2013 site. I have customized the pages css, buttons, and layout exactly as I need. My goal now is to add a sql connection to a db on our network to pull data based on the user logged in. I have written the sql query to basically be "Select "field" From DB Where SharePointUserloggedinname like 'db.table.field' . I have tested this query in sql management studio, and it works perfectly. My question, is how do I add this sql query portion into my aspx page, set the query result to a variable and then display the queries results(variable) to display via a text field/paragraph tag/or other.

I have added the namespaces, and c# portion provided below into my aspx page but I am unsure how & where to place the c# code to connect to the sql db, set the variable, and then call that variable to display in a field further down the page.

`<%@ Import Namespace="System;"%>
<%@ Import Namespace="System.Collections.Generic;"%>
<%@ Import Namespace="System.ComponentModel;"%>
<%@ Import Namespace="System.Data;"%>
<%@ Import Namespace="System.Drawing;"%>
<%@ Import Namespace="System.Linq;"%>
<%@ Import Namespace="System.Text;"%>
<%@ Import Namespace="System.Threading.Tasks;"%>
<%@ Import Namespace="System.Windows.Forms;"%>
<%@ Import Namespace="System.Data.SqlClient;"%>
<%@ Import Namespace="System.Web.UI.Page" %>


<%@ public partial class _Default : System.Web.UI.Page
{
private SqlDataReader reader = null;
public SqlDataReader Reader { get { return reader; } set { reader = value; } }
protected void Page_Load(object sender, EventArgs e)
{
string connectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
SqlConnection connection = new SqlConnection(connectionString);
connection.Open();

SqlCommand command = new SqlCommand("SELECT [totalHours] FROM [DB].[dbo].[TABLE] Where [DB].[dbo].[TABLE].[column] like 'persons name') = @variable1", connection);
command.Parameters.Add(new SqlParameter("variable1", "anonymous"));

Reader = command.ExecuteReader();
}
}
%>

Open in new window

Any ideas or thoughts would be appreciated.
Avatar of Kyle Abrahams, PMP
Kyle Abrahams, PMP
Flag of United States of America image

You're missing the @ symbol:


command.Parameters.Add(new SqlParameter("@variable1", "anonymous"));
Avatar of cbarber22
cbarber22

ASKER

Thank you, where would i place that block of code provided above, as well as how would i call this variable further down in my aspx page.
ASKER CERTIFIED SOLUTION
Avatar of Kyle Abrahams, PMP
Kyle Abrahams, PMP
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
thank you sorry for the delay