Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 15 of 15
  1. #1
    Regular Coder
    Join Date
    Aug 2005
    Posts
    279
    Thanks
    0
    Thanked 0 Times in 0 Posts

    connect to sql server 2000 using asp.net

    Hey guys newbie here. I tried to google for sql connection using asp.net but didn't get what they were talking about.

    I got a page called getData.aspx, and a table name "tblTest" with columns "Name" value "Test".

    I want to retrieve that data from tblsTest by displaying the name "Test". But I don't know how to connect to my database which is MS. Sql server 2000. Where to put that code in vb file or that aspx file? Anyone could help please?

    Thanks

  • #2
    New Coder
    Join Date
    Mar 2007
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts
    You need to set a connection string in your web.config file:

    Code:
    <connectionStrings>
    		<clear/>
    		<add name="LocalSQLServer" connectionString="Persist Security Info=False;User ID=dbUser;pwd=password;Initial Catalog=databaseName;Data Source=sqlserverip;" providerName="System.Data.SqlClient"/>
    	</connectionStrings>
    then in your page you need the
    Imports System.Data.SqlClient Line at the top.
    then you declare a connection where you need it in your code

    and then you can declare a command and execute the query to get it in a datareader
    Code:
    Dim cnnConnexion As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("LocalSQLServer").ConnectionString)
    Dim cnnCommand as New SqlCommand("SELECT * FROM Test", cnnConnexion)
    Dim drReader as DataReader
    
    Try
    cnnConnexion.Open()
    drReader = cmdCommand.ExecuteReader()
    if drReader.HasRows() then
     while drReader.Read()
      'code to do
     end while
    end if
    Catch ex as exception
     'Error handling
    Finally
     cnnConnexion.Close()
    end try
    hope that helps ^^ (this is VB.Net code, btw)

    Oh, and you can also use the connection string directly into the declaration if you dont want to set the web.config file bit like
    Dim cnnConn as new SqlConnection("Persist Security Info=False;User ID=dbUser;pwd=password;Initial Catalog=databaseName;Data Source=sqlserverip;")
    but I'm not 100% sure about that one cuz I never used it
    Last edited by Krokador; 03-29-2007 at 06:35 PM.

  • #3
    Regular Coder
    Join Date
    Aug 2005
    Posts
    279
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Just to clarify that vb script I should put in my getData.aspx, correct? would it allow me to do that?

    Also, in the web config, my SQL server database is using windows credentials, so I will just put my windows USerID and password then?
    Last edited by cgibie; 03-29-2007 at 06:52 PM.

  • #4
    New Coder
    Join Date
    Mar 2007
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts
    to your first question: yeah the code I gave you is to be used either in a codebehind file or in the page between <% %> tags (Though I've never used that approach) In, let's say, the Page_load sub.

    To your second question: Is your database on the local computer or on a remote server?

  • #5
    Regular Coder
    Join Date
    Aug 2005
    Posts
    279
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Yeah it's on my local computer about the database.

  • #6
    Regular Coder
    Join Date
    Aug 2005
    Posts
    279
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello? how can I put the username and password?

  • #7
    New Coder
    Join Date
    Mar 2007
    Posts
    76
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Ah, sorry I didn't have time to come back here to try and help before now (and even now I kinda have a very small span of time).

    I have never tried to use a local database myself. My guess would be something like

    Code:
    Persist Security Info=False;User ID=WindowsUser;pwd=Windowspassword;Initial Catalog=databaseName;Data Source=127.0.0.1;
    or this link might help you:
    http://quickstarts.asp.net/QuickStar.../data/vwd.aspx

  • #8
    Senior Coder nikkiH's Avatar
    Join Date
    Jun 2005
    Location
    Near Chicago, IL, USA
    Posts
    1,973
    Thanks
    1
    Thanked 32 Times in 31 Posts
    Integrated Security=SSPI
    might be worth looking into for you

    If this post contains any code, I may or may not have tested it. It's probably just example code, so no getting knickers in a bunch over a typo, OK? If it doesn't have basic error checking in it, such as object detection or checking if objects are null before using them, put that in there. I'm giving examples, not typing up your whole app for you. You run code at your own risk.
    Bored? Visit
    http://www.kaelisspace.com/

  • #9
    Regular Coder
    Join Date
    Aug 2005
    Posts
    279
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Code:
    <%Imports System.Data.SqlClient %>
    <%@ Page Language="VB" %>
    <% 
        
        Dim cnnConnexion As New SqlConnection(System.Configuration.ConfigurationManager.ConnectionStrings("LocalSQLServer").ConnectionString)
        Dim cnnCommand As New SqlCommand("SELECT * FROM Test", cnnConnexion)
        Dim drReader As DataReader
    
        Try
            cnnConnexion.Open()
            drReader = cnnCommand.ExecuteReader()
            If drReader.HasRows() Then
                While drReader.Read()
                    'code to do
                End While
            End If
        Catch ex As Exception
            'Error handling
        Finally
            cnnConnexion.Close()
        End Try
    %>
    I got the Imports statement as Syntax error, and those classes of sql cannot are undeclared. Could anyone help?

  • #10
    Senior Coder nikkiH's Avatar
    Join Date
    Jun 2005
    Location
    Near Chicago, IL, USA
    Posts
    1,973
    Thanks
    1
    Thanked 32 Times in 31 Posts
    http://www.samspublishing.com/articl...&seqNum=5&rl=1

    <%Imports System.Data.SqlClient %>
    to
    <%@ Import Namespace="System.Data.SqlClient" %>

    If this post contains any code, I may or may not have tested it. It's probably just example code, so no getting knickers in a bunch over a typo, OK? If it doesn't have basic error checking in it, such as object detection or checking if objects are null before using them, put that in there. I'm giving examples, not typing up your whole app for you. You run code at your own risk.
    Bored? Visit
    http://www.kaelisspace.com/

  • #11
    New Coder
    Join Date
    Nov 2006
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hi,

    I know this is of no help, but I really struggled with this topic (see previous posts). Ended up being a convoluted process of trial and error to get anywhere. Have you explicitly stated that you're developing locally? If so, what are you using? Consider downloading VWDE in order to do away with a lot of superfluous code; connection strings and user profiles are automatically created; though deploying it is another matter entirely! For this, forums were of very little help.

    Max01

  • #12
    Regular Coder
    Join Date
    Aug 2005
    Posts
    279
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Thank you it works now. Max I don't know what are you trying to say? nikkih has been very helpful to me here

  • #13
    New Coder
    Join Date
    Nov 2006
    Posts
    26
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Hello,

    I'm not 'trying' to say anything, I simply wrote that I too had a similar problem which was later resolved through a process of trial and error. Forums are indeed a wonderful resource, but were of very little help to me as regards deploying a database.

  • #14
    New to the CF scene
    Join Date
    Apr 2011
    Posts
    1
    Thanks
    0
    Thanked 0 Times in 0 Posts
    Quote Originally Posted by cgibie View Post
    Thank you it works now. Max I don't know what are you trying to say? nikkih has been very helpful to me here
    Oh, I know what he's trying to say. Its been YEARS and I still think "dot net" is a unnecessarily convoluted disaster.

    I long for the days of notepad and classic ASP....... AND YES, it was possible to develop entire SECURE web sites using only that.

  • #15
    New to the CF scene
    Join Date
    May 2011
    Posts
    2
    Thanks
    0
    Thanked 0 Times in 0 Posts
    <configuration>
    <appSettings>
    <add key="con" value="Server=.; Trusted_Connection=True; Database=Database name"/>
    </appSettings>
    <connectionStrings/>


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •