View Single Post
  #1 (permalink)  
Old February 5th, 2011, 02:41 PM
chippie chippie is offline
Registered User
 
Join Date: Feb 2011
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default Weird query error

Hi guys,

Its my first time in the ADO world and I think I've managed to configure everything correctly.

Now when I call to do an update (insert) I keep getting the following error:

Quote:
Incorrect syntax near '?'.
this is caused by the following line:
dataAdapter.Update(dataSet,"Computer");

heres the complete code listing

Code:
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 = @"Data Source=JOHN-VAIO\MSSQLSERVER2008;Initial Catalog=ComputerManagement;Integrated Security=SSPI";
        private SqlDataAdapter dataAdapter;
        private DataSet dataSet;
        private DataTable dataTable;

        SqlConnection connection;
        // Database objects
        private static string Id;
        private static string Name;

        private System.ComponentModel.Container components = null;

        public ActiveDataObjects()
        {
            string commandstring = "select * from Computer";
            connection = new SqlConnection(SQLServerConnection);
            dataAdapter = new SqlDataAdapter(commandstring, connection);
            BuildCommands();

            dataSet = new DataSet();
            dataSet.CaseSensitive = true;
            dataAdapter.Fill(dataSet, "Computer");
            dataTable = dataSet.Tables[0];
        }

        private void BuildCommands()
        {
            SqlConnection connection =
                (SqlConnection)dataAdapter.SelectCommand.Connection;

           // Declare a reusable insert command with parameters
            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");

            // Declare a reusable update command with parameters
            dataAdapter.UpdateCommand = connection.CreateCommand();
            dataAdapter.UpdateCommand.CommandText = "update Computer " +
                "set Name = ? " +
                "where Id = ? ";
            dataAdapter.UpdateCommand.Parameters.Add("Name", SqlDbType.Char, 0, "Name");
            dataAdapter.UpdateCommand.Parameters.Add("Id", SqlDbType.Char, 0, "Id");
			

            
            /*SqlCommand command = connection.CreateCommand();
            command.CommandText = "Select * from Computer;";
            connection.Open();

            SqlDataReader reader = command.ExecuteReader();

            while (reader.Read())
            {
                Console.WriteLine("\t{0}\t{1}",
                    reader[0], reader[1]);
            }
            reader.Close();*/

        }

        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["Id"] = "1".Trim();
            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();
			}
        }
        }
}
I dont see anything wrong with the sql statement! :S
Reply With Quote