p2p.wrox.com Forums

p2p.wrox.com Forums (http://p2p.wrox.com/index.php)
-   ADO.NET (http://p2p.wrox.com/forumdisplay.php?f=109)
-   -   Weird query error (http://p2p.wrox.com/showthread.php?t=82502)

chippie February 5th, 2011 02:41 PM

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

Imar February 5th, 2011 03:22 PM

Hi there,

SQL Server doesn't use the ? for parameters, but @parameterName. So, this should work:

"insert into Computer " +
"(Id, Name) " +
"values " +
"(@id, @name)";

You may need to repeat the @ in the call to Parameters.Add. Can't recall if you have to, or can leave it out there.

Cheers,

Imar

chippie February 6th, 2011 09:17 AM

Brilliant! thanks it works a treat :)

akaas.website February 13th, 2012 06:09 AM

give paramemeters names instead of "?"

like:

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

// Declare a reusable insert command with parameters
dataAdapter.InsertCommand = connection.CreateCommand();
dataAdapter.InsertCommand.CommandText =
"insert into Computer " +
"(Id, Name) " +
"values " +
"(@ID, @Name)";
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 = @Name" +
"where Id = @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();*/

}



Regards,
Akaas Developer
http://www.questions-interviews.com/...hnologies.aspx


All times are GMT -4. The time now is 02:11 AM.

Powered by vBulletin®
Copyright ©2000 - 2019, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.