Hi Peeps

I have recently created a program using a OleDB connection using dynamic SQL statements, which worked perfectly. I came to put the same program with a few differences onto work's SQL server so everyone could use this program. But for some reason the SQL statements that worked perfectly using OleDB Connection throw constant Syntax errors using a SQL connection. I am using .NET 2003 and C#. I am not new to c# but relatively new to SQL.

Can anyone please explain to me why the SQL statements would thrown errors when being sent through a SQLinsertCommand vs no errors when been sent through an OleDBInsertCommand?

The error is as follows: SqlException: Line1: Syntax error near '?'

The sample of code is shown below: -

string addNum =
"INSERT INTO ContactList ([Address], [FullName] ,[DirectLine] ,[Mobile]) "+
"VALUES(?,?,?,?)";

sqlInsertCommand1.CommandType = CommandType.Text;
sqlInsertCommand1.CommandText = addNum;
sqlInsertCommand1.Connection = connAdd;

sqlInsertCommand1.Parameters.Add("@addressadd",Sql DbType.Char,255,"Address").Value = comboBoxDepartment.Text;
//MORE PARAMETERS DONT WORRY I DIDN'T MISS THEM I KNOW THIS WOULD THROW THIS ERROR

connAdd.Open();
try
{
sqlInsertCommand1.ExecuteNonQuery();
adapterAdd.Fill(dataSet1.ContactList);
dataGrid1.DataSource = dataSet1.ContactList;
}
catch(Exception e1)
{
MessageBox.Show(e1.ToString());
}
connAdd.Close();
sqlInsertCommand1.Parameters.Clear();
}

So hopefully I haven't missed any info out but basically i get errors from this when it works PERFECTLY in virtually the same program using an OleDbCOnnection.

Any help is much appreciated guys.
Kind REgards
Nath

When **** becomes valuable the poor will be born without *******s.

From HELP for the ADO Command object:

"The Microsoft .NET Framework Data Provider for SQL Server does not support the question mark (?) placeholder for passing parameters to a Transact-SQL statement or a stored procedure called by a command of CommandType.Text. In this case, named parameters must be used."

Thus, your VALUES clause must reference the parameters by name, as:

    ...
    VALUES (@Address, @FullName, ...)

(or whatever you want to call them)

Jeff Mason
Custom Apps, Inc.
[email protected]

Thankyou Jeff I will try this immediately and alert of the result. Damn placeholders, I read on the MSDN that they were good to use!

Cheers
Nath

When **** becomes valuable the poor will be born without *******s.

Ok now I get two different errors, because I tried it two different ways:

1: I changed the placeholders to Values, i.e. @Address,@FullName,@DirectLine,@Mobile and then left the parameter declaration the same, i.e.

sqlInsertCommand1.Parameters.Add("@address",SqlDbT ype.Char,255,"Address").Value = comboBoxDepartment.Text;
etc
etc

and the error is as follows:
SQlException: Prepared Statement: @Address,NVarChar(255),@FullName,NVarChar(255) etc Expects Parameters @Address,@FullName etc etc.

So i then changed my parameter declaration to:

sqlInsertCommand1.Parameters.Add("@Address",SqlDbT ype.Char,255,"Address").Value = comboBoxDepartment.Text;

and then i get this error!:

The variable name '@Address' is already in use, variable names must be unique within a Query batch.

The stupid thing asked for that parameter though!

Does this make sense?
Sorry for been annoying!

When **** becomes valuable the poor will be born without *******s.

I'm sorry. I just noticed you are using a dataset. I never (well hardly ever) use the things, so I'm not sure how much help I can be. It looks like you created a strongly typed dataset elsewhere, but you seem to be trying to execute an INSERT statement on an independant command object ('SQLInsertCommand1'), rather than using the data adapter to do the dataset work for you.

I'm going to ignore the DataSet here.

You should be able to construct the command object and add parameters as:

string addNum =
"INSERT INTO ContactList ([Address], [FullName] ,[DirectLine] ,[Mobile]) "+
"VALUES(@Address, @FullName, @DirectLine, @Mobile)";

sqlInsertCommand1.CommandType = CommandType.Text;
sqlInsertCommand1.CommandText = addNum;
sqlInsertCommand1.Connection = connAdd;

sqlInsertCommand1.Parameters.Add("@Address", SqlDbType.Char, 255).Value = comboBoxDepartment.Text;

...(statements for all the other parameters)
etc

Then execute the command object as:

sqlInsertCommand1.ExecuteNonQuery();

That should add one row to the table. Note that I dropped the last parameter on the Add call, as that provides a mapping between a dataset column and the parameter. Your use of the .ExecuteNonQuery on the command object to execute the command means it has nothing to do with the dataset, so that last argument doesn't belong there.

To use the command again to add another row, you must not add the parameters again (that's why you got the already in use error). Just set the value of each named parameter appropriately and execute the command object again.

If you want to make use of the dataset's ability to update ALL the modified rows, you shouldn't use the execute method on the command object, but rather the UPDATE method on the DataAdapter.

Doing this, and constructing a strongly type dataset to support it is way beyond the scope of any discussion we can have here, as that is a .NET/ADO/C# programming issue and not a SQL Server one...

P.S. You probably should use varchar datatypes (instead of char) for your database string data, since I doubt, for example, that an address of yours is exactly 255 characters long. So why store 255 if you usually only need way less than that...

Jeff Mason
Custom Apps, Inc.
[email protected]

Thanks again Jeff, you've been a great help. I will give it a go.

The reason I have been doing it this way is because the dataSet is automatically generated when you use a visual SqlConn and a visual SqlAdapter. I am new with the data side of things and am going to attend courses and what not to increase my knowledge.

I'll alert of the results trying with a commandbuilder

Thanks
Nath

When **** becomes valuable the poor will be born without *******s.

Oh and the sqlCommands, i.e. insert,delete,update,select are automatically generated as well. That's why the command name is so long because you can't change them.

So the SqlInsertCommand1 was automatically generated. Since i'm new to data programming I have just used what's infront of me... Could anyone recommend good books or articles to read on starting data programming?

Thanks Again
Nath

When **** becomes valuable the poor will be born without *******s.