asp.net to sql server

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.
LVL 1
lynntonAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

lynntonAuthor Commented:
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
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

nurbekCommented:
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
sajuksCommented:
@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
lynntonAuthor Commented:
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
nurbekCommented:
MyConnection = New SqlConnection("Server=Aron1;Database=pubs;User UD=sa;Password=asdasd;Trusted_Connection=Yes")

for appropriate connection check

http://www.connectionstrings.com/
0
nurbekCommented:
User UD

should be
User ID

0
lynntonAuthor Commented:
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
nurbekCommented:
<script runat="server">
should be below (follow my posting :) )
of the
<%@ Import Namespace="System.Data.SqlClient" %>
0
lynntonAuthor Commented:
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
nurbekCommented:
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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
lynntonAuthor Commented:
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
nurbekCommented:
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
lynntonAuthor Commented:
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
nurbekCommented:
dont understand clearly :(

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


0
lynntonAuthor Commented:
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
nurbekCommented:
why dont you create a view from sql server then call that view?

sql=select * from myView

maybe it makes simpler?
0
lynntonAuthor Commented:
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
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft Development

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.