?
Solved

asp.net to sql server

Posted on 2005-02-24
19
Medium Priority
?
365 Views
Last Modified: 2013-11-25
Hi,

Kindly guide me on how to connect to the sql server. I'm using web matrix download at asp.net

here's the code, kindly help me create the connection and load a table to a recordset.

<html>
<head>
</head>
<body>
    <form runat="server">
        <!-- Insert content here -->
    </form>
</body>
</html>


Thanks.
0
Comment
Question by:lynnton
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 9
  • 8
  • 2
19 Comments
 
LVL 13

Expert Comment

by:nurbek
ID: 13390581
0
 
LVL 1

Author Comment

by:lynnton
ID: 13390590
experts,

Please try to guide me by filling the blanks. Connecting to sql server part; querying a table and moving to a recordset.

<html>
<head>
</head>
<body>
    <form runat="server">
        <!-- Insert content here -->
    </form>
</body>
</html>

Thanks.
0
Efficient way to get backups off site to Azure

This user guide provides instructions on how to deploy and configure both a StoneFly Scale Out NAS Enterprise Cloud Drive virtual machine and Veeam Cloud Connect in the Microsoft Azure Cloud.

 
LVL 13

Expert Comment

by:nurbek
ID: 13390648
try this

<%@ Page Language="VB" Debug="true" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<script runat="server">
      Sub Page_Load(Sender As Object, E As EventArgs)
        Dim ds As DataSet
        Dim MyConnection As SqlConnection
        Dim MyCommand As SqlDataAdapter
        MyConnection = New SqlConnection("server=(local);database=deneme;Trusted_Connection=yes")
            MyCommand = New SqlDataAdapter("select * from mytable", MyConnection)
            ds = new DataSet()        
            MyCommand.Fill(ds, "DataSetName")
            MyDataGrid.DataSource=ds.Tables("DataSetName").DefaultView
            MyDataGrid.DataBind()
End Sub

</script>
<html><head>Test</head>
<body>
    <ASP:DataGrid id="MyDataGrid" runat="server" Width="700"  ShowFooter="false"  EnableViewState="false"></ASP:DataGrid>
</body>
</html>
0
 
LVL 33

Expert Comment

by:sajuks
ID: 13390657
@lynnton  if u r using the webmatrix ide then in design view you could use the steps outlined here and view the html code in the editor itself.

http://www.asp.net/webmatrix/guidedtour/section3/binddatagrid.aspx
0
 
LVL 1

Author Comment

by:lynnton
ID: 13390678
nurbek,

Perfect! one question though, what the complete syntax for a remote sql server? we need to input user and password too.

MyConnection = New SqlConnection("server=(local);database=deneme;Trusted_Connection=yes")


Thanks.
0
 
LVL 13

Expert Comment

by:nurbek
ID: 13390702
MyConnection = New SqlConnection("Server=Aron1;Database=pubs;User UD=sa;Password=asdasd;Trusted_Connection=Yes")

for appropriate connection check

http://www.connectionstrings.com/
0
 
LVL 13

Expert Comment

by:nurbek
ID: 13390716
User UD

should be
User ID

0
 
LVL 1

Author Comment

by:lynnton
ID: 13390727
nurbek,

Can this be a syntax error ? Thanks.

Compilation Error
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.

Compiler Error Message: BC30037: Character is not valid.
Source Error:
Line 4:  <script runat="server">
Line 5:  
Line 6:      <%@ Page Language="VB" Debug="true" %>
Line 7:      <%@ Import Namespace="System.Data" %>
Line 8:      <%@ Import Namespace="System.Data.SqlClient" %>
Source File: C:\NewFile.aspx    Line: 6
0
 
LVL 13

Expert Comment

by:nurbek
ID: 13390743
<script runat="server">
should be below (follow my posting :) )
of the
<%@ Import Namespace="System.Data.SqlClient" %>
0
 
LVL 1

Author Comment

by:lynnton
ID: 13390881
nurbek,

So close yet these hurdles keep on comming...

<%@ Page Language="VB" %>
<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SqlClient" %>
<script runat="server">

    <%@ Page Language="VB" Debug="true" %>
    <script runat="server">
    <%@ Import Namespace="System.Data" %>




Compilation Error
Description: An error occurred during the compilation of a resource required to service this request. Please review the following specific error details and modify your source code appropriately.

Compiler Error Message: BC30037: Character is not valid.

Source Error:
Line 4:  <script runat="server">
Line 5:  
Line 6:      <%@ Page Language="VB" Debug="true" %>
Line 7:      <script runat="server">
Line 8:      <%@ Import Namespace="System.Data" %>
Source File: C:\NewFile.aspx    Line: 6
0
 
LVL 13

Accepted Solution

by:
nurbek earned 2000 total points
ID: 13390902
try like this, i think you are confusing where to put "<script runat="server">"

<%@ Page Language="VB" Debug="true" %>
<%@ Import Namespace="System.Data" %>
<%@ Import Namespace="System.Data.SqlClient" %>
<script runat="server">
      Sub Page_Load(Sender As Object, E As EventArgs)
        Dim ds As DataSet
        Dim MyConnection As SqlConnection
        Dim MyCommand As SqlDataAdapter
        MyConnection = New SqlConnection("Server=Aron1;Database=pubs;User ID=sa;Password=asdasd;Trusted_Connection=Yes")
          MyCommand = New SqlDataAdapter("select * from mytable", MyConnection)
          ds = new DataSet()        
          MyCommand.Fill(ds, "DataSetName")
          MyDataGrid.DataSource=ds.Tables("DataSetName").DefaultView
          MyDataGrid.DataBind()
End Sub

</script>
<html><head>Test</head>
<body>
    <ASP:DataGrid id="MyDataGrid" runat="server" Width="700"  ShowFooter="false"  EnableViewState="false"></ASP:DataGrid>
</body>
</html>
0
 
LVL 1

Author Comment

by:lynnton
ID: 13391109
nurbek,

Works like a charm. Thanks

one question though, what those the lines below do (what are their roles)


<%@ import Namespace="System.Data" %>
<%@ import Namespace="System.Data.SqlClient" %>
--------------------------------------------------------------------
ds = new DataSet()        
MyCommand.Fill(ds, "DataSetName")
ds.Tables("DataSetName").DefaultView
0
 
LVL 13

Expert Comment

by:nurbek
ID: 13391162
you need inmport namespaces in order to follow functions easiliy
If you do not import the namespace then you need to write full path

Dim MyConnection As System.Data.SqlClient.SqlConnection

as for dataset

It represents the database in memory, you can load the database into dataset and work on it

0
 
LVL 1

Author Comment

by:lynnton
ID: 13392027
nurbek,

One last, how can i run two queries and displaying the last one ?

sql=select * into #temp from table1
run query

sql=select a.*, b.* from #temp as a, table1 as b
run query

show to datagrid...

Thanks.
0
 
LVL 13

Expert Comment

by:nurbek
ID: 13392231
dont understand clearly :(

first query for what? maybe there is no need for first query?


0
 
LVL 1

Author Comment

by:lynnton
ID: 13392307
nurbek,

My apology for the bad sample, it a more complex query that needs to be place on a temporary table first.

hope you understand :-)

Thanks.
0
 
LVL 13

Expert Comment

by:nurbek
ID: 13392342
why dont you create a view from sql server then call that view?

sql=select * from myView

maybe it makes simpler?
0
 
LVL 1

Author Comment

by:lynnton
ID: 13392378
nurbek,

Kindly see below for the whole query. I'm not sure what would be the best approach but user will input a date
and click a button (all in asp.net) then it will run this query and export to excel (which I'll reseach on how...)

Thanks.

---------------------------------------------1
select operatorid,messagetype,workstation,workstationtime into #tempworkstation2 from workstation where workstationtime between '2/22/2005' and '2/23/2005' and (messagetype ='workstation login' or  messagetype ='workstation logout') order by workstation,workstationtime
---------------------------------------------2
SELECT m.operatorid, m.workstation,
     m.workstationtime as login, (SELECT TOP 1 workstationtime  FROM #tempworkstation2 s
    WHERE s.workstation = m.workStation AND s.workstationtime > m.workstationtime
    ORDER BY workstationtime ASC) AS logout INTO #tempresult
 FROM #tempworkstation2 m WHERE messagetype='Workstation Login' AND EXISTS (SELECT 1  FROM #tempworkstation2 s
                   WHERE s.workstation = m.workStation AND s.workstationtime > m.workstationtime
                         AND s.messagetype = 'Workstation Logout')
ORDER BY operatorid, workstation ASC

----------------------------------------------3
select b.teamleader,b.name,a.* from #tempresult a left join team b on a.operatorid=b.operatorid order by a.operatorid,b.teamleader
----------------------------------------------4
drop table #tempworkstation2, #tempresult
0

Featured Post

On Demand Webinar: Networking for the Cloud Era

Did you know SD-WANs can improve network connectivity? Check out this webinar to learn how an SD-WAN simplified, one-click tool can help you migrate and manage data in the cloud.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

I recently decide that I needed a way to make my pages scream on the net.   While searching around how I can accomplish this I stumbled across a great article that stated "minimize the server requests." I got to thinking, hey, I use more than one…
I would like to start this tip/trick by saying Thank You, to all who said that this could not be done, as it forced me to make sure that it could be accomplished. :) To start, I want to make sure everyone understands the importance of utilizing p…
This is Part 3 in a 3-part series on Experts Exchange to discuss error handling in VBA code written for Excel. Part 1 of this series discussed basic error handling code using VBA. http://www.experts-exchange.com/videos/1478/Excel-Error-Handlin…
Have you created a query with information for a calendar? ... and then, abra-cadabra, the calendar is done?! I am going to show you how to make that happen. Visualize your data!  ... really see it To use the code to create a calendar from a q…

800 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question