Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > .NET > Other .NET > ADO.NET
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
ADO.NET For discussion about ADO.NET.  Topics such as question regarding the System.Data namespace are appropriate.  Questions specific to a particular application should be posted in a forum specific to the application .
Welcome to the p2p.wrox.com Forums.

You are currently viewing the ADO.NET section of the Wrox Programmer to Programmer discussions. This is a community of tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developers’ questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Search this Thread Display Modes
  #1 (permalink)  
Old February 5th, 2011, 02:41 PM
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
  #2 (permalink)  
Old February 5th, 2011, 03:22 PM
Imar's Avatar
Wrox Author
Points: 72,038, Level: 100
Points: 72,038, Level: 100 Points: 72,038, Level: 100 Points: 72,038, Level: 100
Activity: 100%
Activity: 100% Activity: 100% Activity: 100%
 
Join Date: Jun 2003
Location: Utrecht, Netherlands.
Posts: 17,080
Thanks: 80
Thanked 1,587 Times in 1,563 Posts
Default

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
__________________
Imar Spaanjaars
http://Imar.Spaanjaars.Com
Follow me on Twitter

Author of Beginning ASP.NET 4.5 : in C# and VB, Beginning ASP.NET Web Pages with WebMatrix
and Beginning ASP.NET 4 : in C# and VB.
Did this post help you? Click the button below this post to show your appreciation!
Reply With Quote
  #3 (permalink)  
Old February 6th, 2011, 09:17 AM
Registered User
 
Join Date: Feb 2011
Posts: 3
Thanks: 0
Thanked 0 Times in 0 Posts
Default

Brilliant! thanks it works a treat :)
Reply With Quote
  #4 (permalink)  
Old February 13th, 2012, 06:09 AM
Registered User
Points: 30, Level: 1
Points: 30, Level: 1 Points: 30, Level: 1 Points: 30, Level: 1
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: May 2011
Posts: 6
Thanks: 0
Thanked 1 Time in 1 Post
Default

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
Reply With Quote
Reply


Thread Tools Search this Thread
Search this Thread:

Advanced Search
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Weird Error Message jeremy1048 Access 2 April 8th, 2008 04:52 AM
Weird Syntax Error mjhoagland Classic ASP Professional 3 May 5th, 2007 04:25 AM
VWD Weird Error bmains Visual Studio 2005 1 May 2nd, 2005 09:46 AM
Weird Combobox Error kpowers@wppisys.org ADO.NET 0 October 1st, 2003 10:43 AM



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


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