asp.net,sql-server,asp.net-mvc , Fetching data from remote SQL server


Fetching data from remote SQL server

Question:

Tag: asp.net,sql-server,asp.net-mvc

I have developed a asp.net web application using visual studio 2010. I'm using SQL server 2008 as database. I need to fetch data from one of the remote server through LAN connection and have to store to my database through C# code. I am beginner to web development using asp.net and C#. please tell me a very simple way to solve this.

This is an example which I tried. Is this code OK or wrong please guide me with this?

protected void Button1_Click(object sender, EventArgs e)
{
    SqlConnection con1 = new SqlConnection();
    con1.ConnectionString = @"Data Source=172.16.7.127\inhouse;User ID=****;Password= *****;";
    con1.Open();
    string str = "select * from t_his_events Where patientMRN="+hosp_no.Text;
    SqlDataReader dr;
    SqlCommand myCommand = new SqlCommand(str,con1);
    dr = myCommand.ExecuteReader();
    if (dr.HasRows)
    {
        Console.WriteLine("connection established");
    }


}

Answer:
using System.Data.SqlClient;
SqlConnection conn = new SqlConnection(<connectionstring>);
SqlCommand cmd = conn.CreateCommand();
cmd.CommandText = "SELECT * FROM table";
SqlDataAdapter da = new SqlDataAdapter(cmd);
DataSet ds = new DataSet();
da.Fill(ds);

And now you have a DataSet containing the results of your query. As for determining your , I suggest Connection Strings as a great resource for picking the perfect format.


Related:


INSERT INTO fails due to incorrect conversion T-SQL


sql-server,tsql
My table looks like this: CREATE TABLE MyTable ( TableID INT IDENTITY NOT NULL, ForeignID INT NOT NULL, Value sql_variant NOT NULL, CodeOne VARCHAR(4) NOT NULL, CodeTwo VARCHAR(4) NOT NULL ) I'm trying to do a insert with the following code: INSERT INTO MyTable(ForeignID, Value, CodeOne, CodeTwo) VALUES ( 1,...

Sql injected code is inserted to my database . How to remove it


sql-server
One of my sql table is injected with some html code. It is inserted such that the html tags are inserted after actual data. How to remove this from my table.

Get unique row by single column where duplicates exist


sql,sql-server
I have the following DB table called messages with columns: thread_id, sender_id, receiver_id, date Example: +-----------+------------+-------------+-------------+ | thread_id | sender_id | receiver_id | date | +----------------------+-----------+-----------------+ | 1 | 1 | 2 | 11/06/2015 | | 1 | 2 | 1 | 14/06/2015 | | 1 | 1 | 2...

Implement reference key in SQL Server


sql-server,sql-server-2008
I have a TestPack table that can have n no of Lines associated with it. But a Line may exist without a TestPack. Later on, a Line might be assigned a TestPack#. What this relationship is called and how do I implement this relationship in SQL Server 2008?...

Select count Columns group by No


sql,sql-server
Emp_No Emp_Shift Emp_Date 500 AL 1/5/2015 600 S 2/5/2015 600 H 3/5/2015 500 S 4/5/2015 500 AL 5/5/2015 600 AL 6/5/2015 I need help on this issue , HOW TO RETURN count in 3 Columns >> EX: Emp_No Count Al Count S Count H 500 2 1 0 600 1...

Unable to find the auto created Database


c#,asp.net,asp.net-mvc,entity-framework
I have created simple ASP.NET MVC4 application using EntityFramework Code first approach. The entity class is as below: public class Album { [Key] public int AblumId { get; set; } public decimal Price { get; set; } public string Title { get; set; } } public class MusicContext : DbContext...

Purging Database - Count purged/not-purged tables


mysql,sql,sql-server,database,stored-procedures
So I am working with a database where I will be purging various tables that contain rows that are older than 30days. I have fairly limited knowledge of SQL and wanted to know if there was a certain way to select the row that will be purged and the rows...

Creating a viewmodel on an existing project


c#,asp.net,asp.net-mvc
I am trying to add a viewmodel to a project because I want my view to use two separate models. I've looked at different tutorials trying to learn how to do this but I am having some trouble. Before, the view was strongly binded(typed?) to the Person model, but now...

System.net.http.formatting causing issues with Newtonsoft.json


c#,asp.net,asp.net-mvc,json.net
My Windows service is in the same solution as a MVC project. The MVC project uses a reference to SignalR Client which requires Newtonsoft.Json v6 + the Windows service uses System.Net.Http.Formatting, which requires Newtonsoft.Json version 4.5.0.0. I assumed this would not be a problem, as I could just use a...

Take thousand value in SQL


sql,sql-server
I have values like below I need to take only the thousand value in sql. 38,635.123 90,232.89 123,456.47888 I need to take result as below. 635 232 456...

Title search in SQL With replacement of noice words [on hold]


sql,sql-server,sql-server-2008
I have Two tables first us IMG_detail S.NO Title 1 women holding stack of gifts 2 Rear view of a man playing golf 3 Women holding gifts 4 Close-up of a golf ball on a tee 5 Businessman reading a newspaper and smiling and Second is tbl_NoiceWords SN Key 1...

WCF service architecture query


asp.net,architecture,wcfserviceclient
I have an application that consists of a web application, and mutliple windows services, only one windows service is installed depending on what version of the backend sofware is used. Currently, Data is saved by the web app in a database, then the relevant service is installed and this picks...

Fastest way to add a grouping column which divides the result per 4 rows


sql,sql-server,tsql,sql-server-2012
If i have a resultset like this for example (just a list of numbers) : 1,2,3,4,5,6,7,8,9,10,11 and I would like to add a grouping column so i can group them per 4 like this : 1,1,1,1,2,2,2,2,3,3,3 (The last one in this examle does not have a forth element, so that...

How to select next row after select in SQL Server?


sql,sql-server
I have this issue, I need your help: EmpNo Shift_Date Shift1 shift2 stamp_Date stamp_Time stamp_Type 426 2015-04-12 A 2015-04-12 10:09:00.000 I 426 2015-04-15 B C 2015-04-15 23:46:00.000 I 426 2015-04-15 B C 2015-04-15 23:45:00.000 O 426 2015-04-16 OF 2015-04-16 07:02:00.000 O 426 2015-04-17 A 2015-04-17 07:34:00.000 I 426 2015-04-18 A...

onSuccess and onFailure doesn't get fired


javascript,c#,asp.net,webmethod,pagemethods
I have used onSuccess and onFailure in my PageMethod call. However neither of them gets called and the WebMethod doesn't get fired either. alert("1"); PageMethods.LoginUser(onSuccess, onFailure, email, pass); alert("2"); function onSuccess(val) { } function onFailure() { } [WebMethod(EnableSession = true)] public static int LoginUser(string email, string pass) { //Doesn't get...

How do I convert this tSQL statement to LINQ using group by in a sub query


c#,sql-server,linq,tsql
I have the following MSSQL query I am trying to convert to LINQ. I am using entity framework with the following syntax to get at the data. var rv = (from i in DC.TableA select i).ToList(); This is the sql I want to write a C# LINQ query for but...

Can I uniquely identify 2 check boxes so that I can add a different image to each?


html,css,asp.net,checkbox
Currently I am using an image for checked and unchecked checkboxes the html and css is below. <div class="text-field-box text-field-box-mobile radio-button-box small"> <asp:CheckBox ID="ChkOffer1" text=" " runat="server" class="checkbox checkbox-mobile radio" Visible="true" EnableViewState="true"></asp:CheckBox> <% Response.Write(Session("Offer1"))%> </div> and css input[type=checkbox] { display:none; } input[type=checkbox] + label { background-image: url("checkbox-default.png"); background-repeat: no-repeat;...

Connecting to database using Windows Athentication


sql-server,vb.net,authentication,connection-string
I would like to use window authentication in my program to connect to my sql server. users already have certain permissions on the SQL server and I would like to leverage that in my program. The way I currently connect to the server is using this connection string. Dim ConnectionString...

Retrieve data from one table and insert into another table


sql,asp.net,sql-server
I am trying to retrieve data from one table and then insert it into another table. This is a a sample of the first table in which there is the following data. tb1 is the table which consists of data. The two columns Manager and TeamLeader basically means for example...

Access manager information from Active Directory


c#,asp.net,active-directory
Attach is the picture of active directory, which i got from my IT department. Now i want to get the manager information in C#. NOTE: I am able to get all information of user but there isn't any key of manager, but IT department just gave me above attached...

How to make a website work only with https [duplicate]


asp.net,ssl,https
This question already has an answer here: How to force HTTPS using a web.config file 3 answers How do I make a website to work only with https? Is there any method to make my website work only if the protocol is https? For example let me say http://www.mywebsite.com,...

ONLY display certain rows from an inner joined table using a certain colum as a parameter from one of the inner joined tables


sql-server,join
I have two tables, one with various details such as Username, email id, user code and another table with columns which are: UserCode, supervisor email id, active_flag I required an output of a table which only displays the active users i.e. the users with active_flag (Y or N) column as...

Convert Double from String


asp.net,vb.net,visual-studio-2012,converter
When converting string to floating, the converter creates wrong results. ?Global.System.Convert.ToDouble("635705821821928755").ToString("0") "635705821821929000" ?Global.System.Convert.ToSingle("635705821821928755").ToString("0") "635705800000000000" I am working with VB.Net Visual Studio 2012, Framework 4 on ASP.Net Webpage. Is there any solution for converting huge numbers from string into floating?...

Improving work with SQL DataTime


sql,sql-server,database,tsql
I have a business in my Date. I need change date below for each date before 25 of each month I want get 25 of month before and for each date after 25 of each month I want get 25 of the same month. Example : for 2015/06/24 (or 2015/06/06)...

How to use OFFSET and Fetch without Order by in SQL Server


sql-server,sql-server-2012,sql-order-by,fetch,offset
I want use OFFSET and Fetch in my SQL server 2012 query.But without any order by.I can not use order by.Because my sort order will be lost. How can I use OFFSET and Fetch without order by and row number and where in my query? My 2 select tables have...

SQL Customized search with special characters


sql,sql-server,sql-server-2008
I am creating a key-wording module where I want to search data using the comma separated words.And the search is categorized into comma , and minus -. I know a relational database engine is designed from the principle that a cell holds a single value and obeying to this rule...

Foreign key in C#


c#,sql,sql-server,database
I have two tables, A and B, in a dataset in SQL Server; I have created a connection to the dataset in a c# project in visual studio. How can I create a foreign key ( A is the parent) between my two tables ? I want to create the...

check if file is image


c#,asp.net,asp.net-mvc
I want to check if file is image. and then you will see a link where you can see the image. But the link only has to appear if file is link. I try it like this: if (!String.IsNullOrEmpty(item.FileName)) { var file = item.FileName; string[] formats = new string[] {...

Catch concurrency exception in EF6 to change message to be more user friendly


c#,asp.net,.net,entity-framework,entity-framework-6
I am using EF6.1 and i would like to change the message to a more system specific message when the below exception is thrown. Store update, insert, or delete statement affected an unexpected number of rows (0) Now, my problem is i cannot seem to catch the exception? I have...

SQL Server / C# : Filter for System.Date - results only entries at 00:00:00


c#,asp.net,sql-server,date,gridview-sorting
I have a connected SQL Server database in Visual Studio and am displaying its content in a grid. I created a dropdown menu with the column names as selectable options and a text field to filter for specific content, e.g., DropDown = "Start" - Textfield = 14.03.2015 = Filter Column...

TSQL - Error in stored procedure due to conversion failure


sql-server,sql-server-2008,tsql
I have this situation in a stored Procedure: SET @DATE_RELEASE_START = '2015-01-01'; SET @DATE_RELEASE_END = '2015-05-31' SELECT @statement = ' SELECT * FROM (SELECT AFCDENTE, M.ID_MODIFICATION_CODE, COUNT(*) AS Conteggio--, CAST((COUNT(*) * 100/ 15032) AS decimal(10,7)) AS Percentage FROM CIC_LOG_MODIFICHE AS L INNER JOIN ADM_MODIFICATION_CODE AS M ON L.CD_MODIFICATION_CODE = M.CD_MODIFICATION_CODE...

Can someone explain to me how this statement is an exclude?


sql,sql-server,tsql
Can someone tell me how this is an exclude? Assuming that tableID is auto generated and in columnY there can be value of 0 or 1. This statement should exclude everything were columnY has a value of 1. SELECT * FROM [table].[dbo].[one] AS t1 LEFT JOIN [table].[dbo].[one] AS t2 ON...

Third-party security providers like Google, Twitter etc. in ASP.Net


asp.net,authentication
I have created a standard ASP.Net web project in Visual Studio 2013 and enabled authentication. A class called 'StartupAuth.cs' is created auotmatically, with following lines. When the app runs on localhost dev server it throws an exception as pasted in screen shot below the code. I need to have it...

Cannot Browse in sql to backup bak file


sql-server
As the title says I cannot browse and cannot see backup folder. is there any other way to restore bak file ? or how can I fix this ? ...

deployment of a site asp.net and iis


c#,asp.net,iis
I know this is for some of you a stupid question but for me is a real problem. I have never deployed a site before What i have done so far: 1) publish the site from visual studio to a folder. 2) added to iis for testing everything works great...

Setting time limit in SQL Query


sql-server
I want a query to make which return 1 when the time is in between of 10:00 pm - 5:00 pm. Else it should return 0. Select * from table ...

Error connecting to MSSQL using PHP


php,sql-server,pdo,odbc,sqlsrv
I am receiving an error as below: PHP Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[08001]: [Microsoft][ODBC Driver 11 for SQL Server]SQL Server Network Interfaces: Connection string is not valid [87]. ' My codes are as follow: $this->link = new PDO( "sqlsrv:server=$this->serverName:$this->port;Database=$this->db", "$this->uid", "$this->pwd" ); I wish someone can enlighten...

Looping distinct values from one table through another without a join


sql,sql-server,tsql,while-loop
I know looping is not ideal in SQL, but I couldn't think of another way of doing this. I want each distinct row from this Table 1 to have each distinct date and hour produced on Table 2. In other words, Table 2 has the dates between 05/01/2014 through 04/30/2015,...

Recursive Lag Column Calculation in SQL


sql,sql-server,recursion
I am trying to write a procedure that inserts calculated table data into another table. The problem I have is that I need each row's calculated column to be influenced by the result of the previous row's calculated column. I tried to lag the calculation itself but this does not...

Difference between application and module pipelines in Nancy?


c#,asp.net,nancy
I have seen in the documentation of Nancy, sometimes these two are referred distinctively. And also is there a difference in the Before/After hooks of these two pipelines?...

Show/hide tinymce with radio buttons


c#,asp.net,asp.net-mvc,tinymce
I try to show/hide a tinymce with radobutton. Like yes/no. So there are two radio buttons. yes - will show the tiny mce and no will hide the tinymce. I have this: showing tiny mce: <div class="form-group"> @Html.Label(Resources.Entity.Product.PdfMessage, new { @class = "text-bold control-label col-md-2" }) <div class="col-lg-6 col-md-8 col-sm-10...

The column name “FirstName” specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument


sql,sql-server,sql-server-2008
I am getting the following error message when I am trying to do replace null to zero. The column name "jan" specified in the PIVOT operator conflicts with the existing column name in the PIVOT argument. Query below: select * from(select isnull(jan,0),isnull(feb,0),sum(data) as amount )as p pivot( sum(amount) for month...

Default the year based on month value


sql,sql-server
I have a query to display the year value from a table and default the current year. select distinct year(startdate) as syear, year(startdate) as eyear, 1 as sOrder from [TTS].[dbo].[Class] where year(startdate) <> year(getdate()) union all select year(getdate()) as syear, year(getdate()) as eyear, 0 as sOrder order by sOrder asc,...

Join SQL query Results and Get-ChildItem Results


sql-server,sql-server-2008,powershell
Background: I have a directory with a number of files that are imported to SQL server. Task: Creating a PowerShell script which will pick up files within this directory and use the filenames as in the SQL query. Ultimate objective: To display SQL results besides the filenames but the resultset...

Merging two tables into new table by ID and date


sql,sql-server,phpmyadmin
Let' say I have two tables: table 1: col a | col b | col c 1 | 62215 | 21 1 | 62015 | 22 2 | 62215 | 23 2 | 51315 | 24 and table 2: col a | col b| col f 1 | 62015| z...

SQL Multiple LIKE Statements


sql,sql-server,tsql,variables,like
I'm currently working on a report that shows me all post codes covered by our sales team. Each team covers over 100 post codes. What i would like to do is create a report that brings back the clients within the post code. Currently my code looks like this. SELECT...

How do ASP.NET Web APIs work once built with MSBUILD?


c#,asp.net,msbuild
I'm new to coding and spend most of my time in mobile application development with Phonegap, I've been learning how to code ASP.NET Web APIs and I know how it generally all works while having the code open in front of me but --- I was asked a question today...

left join table, find both null and match value


sql,sql-server,join
I have two 2 table t1(years int, numOfppl int), t2(years int, numOfppl int). t1 contains years between 2001 and 2010 , t2 contains years between 2003-2005 and 2007-2010 I want to query a result like that t1.years t1.numOfppl t2.yeras t2.numOfppl 2001 7 null null 2002 6 null null 2003 4...