Connect to a SQL Server

[papa_k]

using the below code i am trying to connect to a SQL Server in ASP.net -

 

Dim cnnConnection As New ADODB.Connection()
       Dim strConnection As String
       Dim strSQL As String

       Dim rstTest As New ADODB.Recordset()

       strConnection = ConfigurationSettings.AppSettings("ConnectionString")
       TextBox1.Text = strConnection

       cnnConnection.Open(strConnection)

 

but i get the following error - how do solve / find out more informtation about retifying this problem? here is the error -

 

Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

 

Exception Details: System.Runtime.InteropServices.COMException: Multiple-step OLE DB operation generated errors. Check each OLE DB status value, if available. No work was done.

 

Source Error:

 

An unhandled exception was generated during the execution of the current web request. Information regarding the origin and location of the exception can be identified using the exception stack trace below.

[samsmithnz]

first of all can we see your connectionstring.

 

Secondly have you considered using ADO.NET instead of the old ADODB.

 

It would look like this:

       Dim objSQLConn As SqlConnection

       objSQLConn = New SqlConnection
       'set the connection string from the web.config file
       objSQLConn.ConnectionString = ConfigurationSettings.AppSettings.Get("ConnectionString")
       'Open a connection to the SQL database
       objSQLConn.Open()

[papa_k]

what do i need to add (project references?) - as when i try the lines

 

Dim objSQLConn As SqlConnection

       objSQLConn = New SqlConnection
       'set the connection string from the web.config file
       objSQLConn.ConnectionString = ConfigurationSettings.AppSettings.Get("ConnectionString")
       'Open a connection to the SQL database
       objSQLConn.Open()

 

is says that the SqlConnection is not defined and i get the squigerly line under the words that are not 'defined'.

[PlausiblyDamp]

At the top of the file add

Imports System.Data
Imports System.Data.SqlClient

[samsmithnz]

Sorry papa_k, always forget that import stuff :D

[papa_k]

i really really appreciate your help with this guys - those two post have progressed me a huge amount as before i was only getting error messages that i could not decifyer (spelling) - could i be really really cheeky and ask how you now use the SQLConnection (as i am used the ADODB) to create a recordset with information from the database.

 

You help really is much appreciated.

[Procaine]

Create a Command object, setting the command text (the SQL code) and the database connection.

 

Then you do myCommand.ExecuteReader(). This returns a DataReader object. The datareader object is similar to a recordset, but it's forward-only. If you need more control over the datareader, you can use the DataSet object.

 

Check out the MSDN docs for more information about System.Data and related objects.

[samsmithnz]

Disclaimer: Please Keep in mind that these code samples are cut and pasted out of my code, and may have errors because of how my code is structured (and the fact i'm not trying to show you too much complexity)

 

here you'll have to forget just about everything you ever did with recordsets. I really struggled over this for quite some time. This article really helped me (I still have it linked after all this time :))

 

http://www.sitepoint.com/print/992

 

But the basic two ways to extract data are through a datareader (a forward readonly 'data extractor' (jsut made that word up sorry), and a Dataset.

 

Here is an example of how to do each. I'm sure that someone will post here with a better way (in their opinion) of doing my command objects.

 

DataReader:

       Dim objSQLCmd As SqlCommand
       Dim objSQLDR As SqlCommand

           With objSQLCmd 
               .CommandType = CommandType.StoredProcedure 'The command tpye
               .CommandText = "sp_itr_GetOfficeGroupList" 'The SP
               .Parameters.Add("@ecode", SqlDbType.Char, 5) 'Setup the parameter
               .Parameters.Item(0).Value = strUsername(1) 'Pass the actual parameter value
               .Connection = objSQLConn 'Assign a connection

               'Execute the SP and assign the result of the SP to the Datareader
               objSQLDR = .ExecuteReader()
           End With

 

then to extract the data

           Do While objSQLDR .Read() = True
               'use the data (objSQLDR("fieldname"))
               
           Loop

 

DataSet:

       Dim objSQLCmd As SqlCommand
       Dim objSQLDA As SqlDataAdapter
       Dim objDataSet As DataSet

           'Create the command object
           objSQLCmd = New SqlCommand
           With objSQLCmd
               .Connection = objSQLConn ' the SQL connection
               .CommandType = CommandType.StoredProcedure
               .CommandText = "GetEmployees" 'Stored Procedure to use
               'The name parameter passed into the stored procedure
               .Parameters.Add("@EName", SqlDbType.VarChar, 50)
               .Parameters.Item(0).Value = strName
           End With

           'Initialise the data adapter with the command object
           objSQLDA = New SqlDataAdapter(objSQLCmd)

           'Fill the dataset from the dataadapter
           objDataSet = New DataSet
           objSQLDA.Fill(objDataSet, "mydatasetname")

 

normally to extract the data from a dataset you'd bind it to a control (in this example a datagrid)

       dgAPWorkflow.DataSource = objDataSet 
       dgAPWorkflow.DataBind()

Edited February 4, 2004 by samsmithnz

[papa_k]

BRILLIANT!!!!

 

Thanks to this forum and its members!!!

 

Excellet!!! - A much appreciating Papa_K.