Database connection string error!

[Acidburn]

Hi guys,

I'm trying to get to grips with ADO, so I've installed SQL server 2008 and made a database and placed a table called Computers in it.

Now ive got the following class...

using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.OleDb;
using System.Data;
using System.Data.SqlClient;

namespace WindowsWMIQueryDLL
{
    class ActiveDataObjects
    {
        String SQLServerConnection = "Provider=SQLOLEDB.1;Data Source=local;Initial Catalog=ComputerManagement;Integrated Security=SSPI;";
        private OleDbDataAdapter dataAdapter;
        private DataSet dataSet;
        private DataTable dataTable;
        private OleDbConnection conn;

        // Database objects
        private static string Id;
        private static string Name;
        //private static string Phonenum;

        private System.ComponentModel.Container components = null;

        public ActiveDataObjects()
        {
            string commandstring = "select * from Computer";

            // The link between the sql command and the database connection
            dataAdapter = new OleDbDataAdapter(commandstring, SQLServerConnection);
           // SqlConnection conn = new SqlConnection("Data Source=(local);Initial Catalog=ComputerManagement;Integrated Security=SSPI"); 

            BuildCommands();

            dataSet = new DataSet();
            dataSet.CaseSensitive = true;
            dataAdapter.Fill(dataSet, "Computer");
        }

        private void BuildCommands()
        {
            // Use the select command's connection again
            OleDbConnection connection =
                (OleDbConnection)dataAdapter.SelectCommand.Connection;

            // Declare a reusable insert command with parameters
            dataAdapter.InsertCommand = connection.CreateCommand();
            dataAdapter.InsertCommand.CommandText =
                "insert into Computer " +
                "(Name) " +
                "values " +
                "(?)";
            dataAdapter.InsertCommand.Parameters.Add("Name", OleDbType.Char, 0, "Name");
        }

        public void insert(String name)
        {
        // create a new row, populate it
			DataRow newRow = dataTable.NewRow();
			
			// Note: must Trim strings of trailing spaces for Oracle
			newRow["Name"] = name.Trim();
			
			
			// update the database
			try
			{
				dataSet.Tables["Computer"].Rows.Add(newRow);
				dataAdapter.Update(dataSet,"Computer");
				dataSet.AcceptChanges();
			}
			catch (OleDbException ex)
			{
				dataSet.RejectChanges();
			}
        }
        }
}

but whenever i try to connect to my database i get an exception saying...

[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied.

But the server does exist! I've been playing with it and im currenty logged in using the browers. :S

[Momerath]

Remove the "Provider..." from your connections string and get rid of all the OleDB objects. Or if you must you OleDB (and its limitations) the provider should be "SQLNCLI10" for SQL Server 2008.

[Acidburn]

Hi Momerath,

thanks for your prompt post I've updated the code to reflect your comments. I now use SqlConnections.

The other issue now is i get the following error from this SQL command...

Incorrect syntax near '?'.

this is caused by...

dataAdapter.InsertCommand = connection.CreateCommand();
            dataAdapter.InsertCommand.CommandText =
                "insert into Computer " +
                "(Id, Name) " +
                "values " +
                "(?, ?)";
            dataAdapter.InsertCommand.Parameters.Add("Id", SqlDbType.NVarChar, 16, "Id");
            dataAdapter.InsertCommand.Parameters.Add("Name", SqlDbType.NVarChar, 16, "Name");

but i dont see anything wrong with that statement?

[Momerath]

SQL Server connections used named parameters, rather than the '?' of an OleDB connection. Change the command to "INSERT INTO Computer (Id, Name) VALUES (@Id, @Name)" By using named parameters you can add them in any order and the system will figure it out. It also cuts down on errors in case you do add them in the 'wrong' order.

Also, in the statement above you don't have to capitalize the SQL commands, it's just a habit I have to help me know what is part of the command and what is a table/field name :)

[Mitja Bonca]

Whole code would look like (as Momerath explained):

dataAdapter.InsertCommand = connection.CreateCommand();
            dataAdapter.InsertCommand.CommandText =
                "insert into Computer (Id, Name) values (@id, @name)";
            dataAdapter.InsertCommand.Parameters.Add("@d", SqlDbType.NVarChar, 16, "Id");
            dataAdapter.InsertCommand.Parameters.Add("@name", SqlDbType.NVarChar, 16, "Name");