RE: Online query to my database

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Ahmet Karaca (AhmetKaraca_at_discussions.microsoft.com)
Date: 12/26/04

  • Next message: Derrick Leggett: "Re: Online query to my database" 
    Date: Sun, 26 Dec 2004 04:29:01 -0800

    thank you for now
    I will try
    Probably I will turn back to you with some questions

    "Girish Sundaram" wrote:

    > Hi ,
    > Please go through the following piece of information which will be useful
    > in your case.
    >
    > ADO can use any OLE DB provider to establish a connection. The provider is
    > specified through the Provider property of the Connection object.
    > Microsoft® SQL Server™ 2000 applications use SQLOLEDB to connect to an
    > instance of SQL Server, although existing applications can also use MSDASQL
    > to maintain backward compatibility.
    >
    > Using the Execute method of the Connection object is one way to execute an
    > SQL statement against a SQL Server data source.
    >
    > The Connection object allows you to:
    >
    > Configure a connection.
    >
    >
    > Establish and terminate sessions with data sources.
    >
    >
    > Identify an OLE DB provider.
    >
    >
    > Execute a query.
    >
    >
    > Manage transactions on the open connection.
    >
    >
    > Choose a cursor library available to the data provider.
    > There are some differences in connection properties between SQLOLEDB and
    > MSDASQL. For information about connection properties for MSDASQL, see the
    > MSDN Library at Microsoft Web site.
    >
    > If you are writing a connection string for use with SQLOLEDB:
    >
    > Use the Initial Catalog property to specify the database.
    >
    >
    > Use the Data Source property to specify the server name.
    >
    >
    > Use the Integrated Security keyword, set to a value of SSPI, to specify
    > Windows Authentication (recommended),
    > or
    > use the User ID and Password connection properties to specify SQL Server
    > Authentication.
    >
    >
    > Security Note When possible, use Windows Authentication. If Windows
    > Authentication is not available, prompt users to enter their credentials at
    > run time. Avoid storing credentials in a file. If you must persist
    > credentials, you should encrypt them with the Win32® crypto API. For more
    > information, see "The Crypto API Function" in the MSDN® Library at this
    > Microsoft Web site.
    >
    > If you are writing a connection string for use with MSDASQL:
    >
    > Use the Database keyword or Initial Catalog property to specify the
    > database.
    >
    >
    > Use the Server keyword or Data Source property to specify the server name.
    >
    >
    > Use the Trusted_Connection keyword, set to a value of yes, to specify
    > Windows Authentication (recommended),
    > or
    > Use the UID keyword or User ID property, and the Pwd keyword or Password
    > property to specify SQL Server Authentication.
    >
    >
    > Security Note When possible, use Windows Authentication. If Windows
    > Authentication is not available, prompt users to enter their credentials at
    > run time. Avoid storing credentials in a file. If you must persist
    > credentials, you should encrypt them with the Win32 crypto API. For more
    > information, see "The Crypto API Function" in the MSDN Library at this
    > Microsoft Web site.
    >
    > For more information about a complete list of keywords available for use
    > with a SQLOLEDB connection string, see Connection Object.
    >
    > Restrictions on Multiple Connections
    > SQLOLEDB does not allow multiple connections. Unlike MSDASQL, SQLOLEDB does
    > not attempt to reconnect when the connection is blocked.
    >
    > Examples
    > A. Using SQLOLEDB to connect to an instance of SQL Server: setting
    > individual properties
    > The following Microsoft Visual Basic® code fragments from the ADO
    > Introductory Visual Basic Sample show how to use SQLOLEDB to connect to an
    > instance of SQL Server.
    >
    > ' Initialize variables.
    > Dim cn As New ADODB.Connection
    > . . .
    > Dim ServerName As String, DatabaseName As String
    >
    > ' Put text box values into connection variables.
    > ServerName = txtServerName.Text
    > DatabaseName = txtDatabaseName.Text
    >
    > ' Specify the OLE DB provider.
    > cn.Provider = "sqloledb"
    >
    > ' Set SQLOLEDB connection properties.
    > cn.Properties("Data Source").Value = ServerName
    > cn.Properties("Initial Catalog").Value = DatabaseName
    >
    > ' Windows NT authentication.
    > cn.Properties("Integrated Security").Value = "SSPI"
    >
    > ' Open the database.
    > cn.Open
    >
    > B. Using SQLOLEDB to connect to an instance of SQL Server: connection
    > string method
    > The following Visual Basic code fragment shows how to use SQLOLEDB to
    > connect to an instance or SQL Server:
    >
    > ' Initialize variables.
    > Dim cn As New ADODB.Connection
    > Dim provStr As String
    >
    > ' Specify the OLE DB provider.
    > cn.Provider = "sqloledb"
    >
    > ' Specify connection string on Open method.
    > ProvStr = "Server=MyServer;Database=northwind;Trusted_Connection=yes"
    > cn.Open provStr
    >
    > C. Using MSDASQL to connect to an instance of SQL Server
    > To use MSDASQL to connect to an instance of SQL Server, use the following
    > types of connections.
    >
    > The first type of connection is based on the ODBC API SQLConnect function.
    > This type of connection is useful in situations where you do not want to
    > code specific information about the data source. This may be the case if
    > the data source could change or if you do not know its particulars.
    >
    > In the code fragment shown, the ConnectionTimeout method sets the
    > connection time-out value to 100 seconds. Next, the data source name, and
    > authentication type are passed as parameters to the Open method of the
    > Connection object, using an ODBC data source named MyDataSource that points
    > to the northwind database on an instance of SQL Server.
    >
    > Dim cn As New ADODB.Connection
    >
    > cn.ConnectionTimeout = 100
    > ' DSN connection
    > ' cn.Open "DSN=MyDataSource;Trusted_Connection=yes;"
    >
    > cn.Close
    >
    > The second type of connection is based on the ODBC API SQLDriverConnect
    > function. This type of connection is useful in situations where you want a
    > driver-specific connection string. To make a connection, use the Open
    > method of the Connection object and specify the driver, server name,
    > authentication type, and database. You can also specify any other valid
    > keywords to include in the connection string. For more information about
    > the keyword list, see SQLDriverConnect.
    >
    > Dim cn As New ADODB.Connection
    >
    > ' Connection to SQL Server without using ODBC data source.
    > cn.Open "Driver={SQL
    > Server};Server=Server1;Database=northwind;Trusted_Connection=yes"
    >
    > cn.Close
    >
    > Using the Connection Object
    > In addition to the Command object, an application can use the Connection
    > object to issue commands, stored procedures, and user-defined functions to
    > a database as if they were native methods on the Connection object. To
    > execute a query without using a Command object, an application can pass a
    > query string to the Execute method of a Connection object.
    >
    > However, a Command object is required if you want to save and re-execute
    > the command text, or use query parameters.
    >
    > To execute a command on the Connection object
    >
    > Assign a name to the command using the Name property of the Command object.
    >
    >
    > Set the ActiveConnection property of the Command object to the connection.
    >
    >
    > Issue a statement where the command name is used as if it were a method on
    > the Connection object, followed by any parameters.
    >
    >
    > Create a Recordset object if any rows are returned.
    >
    >
    > Set the Recordset properties to customize the resulting Recordset.
    > Using the Connection Object to Execute Commands
    > This example shows how to use the Execute method of the Connection object
    > to execute commands.
    >
    > Dim cn As New ADODB.Connection
    > . . .
    > Dim rs As New ADODB.Recordset
    >
    > cmd1 = txtQuery.Text
    > Set rs = cn.Execute(cmd1)
    >
    > After the Connection and Recordset objects are created, the variable cmd1
    > is assigned the value of a user-supplied query string (txtQuery.Text) from
    > a Microsoft Visual Basic® form. The recordset is assigned the results of a
    > query, by calling the Execute method of the Connection object, with the
    > variable cmd1 used as the query string parameter.
    >
    > Using the Recordset Object
    > The Recordset object provides methods for manipulating result sets. It
    > allows you to add, update, delete, and scroll through rows in the recordset.
    >
    > A Recordset object can be created using the Execute method of the
    > Connection or Command object.
    >
    > Each row in a recordset can also be retrieved and updated using the Fields
    > collection and the Field object. Updates on the Recordset object can be in
    > an immediate or batch mode. When a Recordset object is created, a cursor is
    > opened automatically.
    >
    > The Recordset object allows you to specify the cursor type and location for
    > fetching the result set. With the CursorType property, you can specify
    > whether the cursor is read-only, forward-only, static, keyset-driven, or
    > dynamic. Cursor type determines if a Recordset object can be scrolled or
    > updated and affects the visibility of changed rows. By default, the cursor
    > type is read-only and forward-only.
    >
    > An application can specify the location of the cursor with the
    > CursorLocation property. This property allows you to specify whether to use
    > a client or server cursor. The CursorLocation property setting is important
    > when you use disconnected recordsets.
    >
    > The first part of the cmdExecute_Click method in the ADO Introductory
    > Visual Basic Sample shows an example of creating, opening, passing a
    > command string variable to, and positioning the cursor in a recordset.
    >
    > Dim cn As New ADODB.Connection
    > Dim rs As ADODB.Recordset
    > . . .
    > cmd1 = txtQuery.Text
    > Set rs = New ADODB.Recordset
    > rs.Open cmd1, cn
    > rs.MoveFirst
    > . . .
    > ' Code to loop through result set(s)
    >
    > Thanks and regards,
    > Girish Sundaram
    >
    >
    > This posting is provided "AS IS" with no warranties, and confers no rights.
    >
    >

  • Next message: Derrick Leggett: "Re: Online query to my database"

    Relevant Pages

    • RE: SQL Network Interfaces, error: 26
      ... either specify the instance, ... I don't see anywhere in the code where SQL Server 2005 is referenced. ... An error has occurred while establishing a connection to the server. ... under the default settings SQL Server does not allow remote connections. ...
      (microsoft.public.dotnet.framework.aspnet)
    • Re: Error "SQL Server does not allow remote connections"
      ... The application could not connect to the sql server db. ... network is blocking the connection. ... SqlInternalConnectionTds connHandler, Boolean ignoreSniOpenTimeout, Int64 ... integratedSecurity, SqlConnection owningObject) +737554 ...
      (microsoft.public.dotnet.framework.aspnet)
    • Re: Online query to my database
      ... >> specified through the Provider property of the Connection object. ... >> SQL statement against a SQL Server data source. ... >> Use the Initial Catalog property to specify the database. ... >> Create a Recordset object if any rows are returned. ...
      (microsoft.public.sqlserver.connect)
    • Re: SQL Network Interfaces, error: 26
      ... Check the web.config, where should be the "connectionstring" attribute, where they specify where to connect ... A> I do not have SQL Server 2005 installed. ... A> An error has occurred while establishing a connection to the server. ... A> that under the default settings SQL Server does not allow remote ...
      (microsoft.public.dotnet.framework.aspnet)
    • Re: Connection from remote computer to network SQL Server
      ... There is no firewall on the W2K machine acting as the SQL server. ... I tried making the SQL machine a "trusted" on the router. ... connection works. ... To find the IP address of your computer inside the network, ...
      (microsoft.public.access.adp.sqlserver)