Error 170:In correct Syntax- SQL Server and C#
-
I am trying to call a stored procedure from C#. The stored procedure, called create_journal, compiles succesfully on SQL Server and also runs sucessfully when executed in query analyzeer. It takes a datetime input parameter. When I call it from C#, I get an SqlException which says Error 170. Line 1 Incorrect syntax near create_journal_entry The stored Procedure begins as follows
ALTER PROCEDURE create_journal_entry
@eom_date DATETIME --End of month date
ASDECLARE
@next_jrnl_num VARCHAR(20)
The c# code is
private void createJEs()
{
DateTime cutOffDate;
cutOffDate=this.dtpCutOffDate.Value;SqlConnection conn =new SqlConnection(DBConnection.connectionString()); conn.Open(); SqlCommand comm=new SqlCommand("create\_journal\_entry",conn); comm.Parameters.Add("@eom\_date",SqlDbtype.DateTime); comm.parameters\[“@eom\_date”\].value=this.dtpCutOffDate.Value; try { comm.ExecuteNonQuery(); moveFiles(); MessageBox.Show("Data has been successfully sent to the GL.", "Success",MessageBoxButtons.YesNo,MessageBoxIcon.Information); } catch(SqlException ex) { MessageBox.Show("Error "+ex.Number + " "+ ex.Message,"SqlServer error",MessageBoxButtons.OK,MessageBoxIcon.Exclamation); } catch(Exception ex) { MessageBox.Show(ex.Message,"System Error", MessageBoxButtons.OK,MessageBoxIcon.Exclamation); } finally { if (conn!=null) { conn.Close(); } }}
NB this.dtpCutOffDate is a DateTime picker control. Please help as I am going nuts over this. Thanks
-
I am trying to call a stored procedure from C#. The stored procedure, called create_journal, compiles succesfully on SQL Server and also runs sucessfully when executed in query analyzeer. It takes a datetime input parameter. When I call it from C#, I get an SqlException which says Error 170. Line 1 Incorrect syntax near create_journal_entry The stored Procedure begins as follows
ALTER PROCEDURE create_journal_entry
@eom_date DATETIME --End of month date
ASDECLARE
@next_jrnl_num VARCHAR(20)
The c# code is
private void createJEs()
{
DateTime cutOffDate;
cutOffDate=this.dtpCutOffDate.Value;SqlConnection conn =new SqlConnection(DBConnection.connectionString()); conn.Open(); SqlCommand comm=new SqlCommand("create\_journal\_entry",conn); comm.Parameters.Add("@eom\_date",SqlDbtype.DateTime); comm.parameters\[“@eom\_date”\].value=this.dtpCutOffDate.Value; try { comm.ExecuteNonQuery(); moveFiles(); MessageBox.Show("Data has been successfully sent to the GL.", "Success",MessageBoxButtons.YesNo,MessageBoxIcon.Information); } catch(SqlException ex) { MessageBox.Show("Error "+ex.Number + " "+ ex.Message,"SqlServer error",MessageBoxButtons.OK,MessageBoxIcon.Exclamation); } catch(Exception ex) { MessageBox.Show(ex.Message,"System Error", MessageBoxButtons.OK,MessageBoxIcon.Exclamation); } finally { if (conn!=null) { conn.Close(); } }}
NB this.dtpCutOffDate is a DateTime picker control. Please help as I am going nuts over this. Thanks
You have to set your command object to stored proc type. i.e. After this line SqlCommand comm=new SqlCommand("create_journal_entry",conn); Type this comm.CommandType = CommandType.StoredProcedure The default is text so it is reading 'create_journal_entry' as your query.
-
You have to set your command object to stored proc type. i.e. After this line SqlCommand comm=new SqlCommand("create_journal_entry",conn); Type this comm.CommandType = CommandType.StoredProcedure The default is text so it is reading 'create_journal_entry' as your query.