whorsfall
asked on
ADO.NET Entity Framework - how to execute a "Delete from" SQL statement?
Hi,
I am trying to work out how to execute the command "delete from <table>" using the ADO.NET entity framework.
I have attached the code below but I get the following error.
System.Data.EntitySqlExcep tion was unhandled
Message="The query syntax is not valid., near keyword 'FROM', line 1, column 8."
Source="System.Data.Entity "
Column=8
ErrorContext="keyword 'FROM'"
ErrorDescription="The query syntax is not valid."
Any ideas on how to fix this
Thanks,
Ward
I am trying to work out how to execute the command "delete from <table>" using the ADO.NET entity framework.
I have attached the code below but I get the following error.
System.Data.EntitySqlExcep
Message="The query syntax is not valid., near keyword 'FROM', line 1, column 8."
Source="System.Data.Entity
Column=8
ErrorContext="keyword 'FROM'"
ErrorDescription="The query syntax is not valid."
Any ideas on how to fix this
Thanks,
Ward
mydb = new PIDEntities();
System.Data.Common.DbConnection my_connection = mydb.Connection;
my_connection.Open();
System.Data.Common.DbCommand cmd = (System.Data.Common.DbCommand)my_connection.CreateCommand();
cmd.CommandText = "delete from PidTable";
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
my_connection.Close();
Hi,
And if you are using delete some records from table use code.
Regards,
VSS
And if you are using delete some records from table use code.
Regards,
VSS
mydb = new PIDEntities();
System.Data.Common.DbConnection my_connection = mydb.Connection;
my_connection.Open();
System.Data.Common.DbCommand cmd = (System.Data.Common.DbCommand)my_connection.CreateCommand();
cmd.CommandText = "DELETE FROM PidTable WHERE name = 'Vikram'";
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
my_connection.Close();
Hi,
Check links for more information:
(1) http://www.w3schools.com/sql/sql_delete.asp
(2) http://www.dotnetheaven.com/UploadFile/prvn_131971/delete02232005162822PM/delete.aspx
Regards,
VSS
Check links for more information:
(1) http://www.w3schools.com/sql/sql_delete.asp
(2) http://www.dotnetheaven.com/UploadFile/prvn_131971/delete02232005162822PM/delete.aspx
Regards,
VSS
ASKER
Hi,
Thanks I tried the example you posted - my objective is btw is to delete all the records (clear it out) but not drop the table. So the SQL I would like to get to work is: " DELETE FROM PidTable" or "DELETE * FROM PidTable
Anyway the SQL statement is valid as far as SQL management studio goes.
System.Data.EntitySqlExcep tion was unhandled
Message="The query syntax is not valid., near keyword 'FROM', line 1, column 8."
Source="System.Data.Entity "
Column=8
ErrorContext="keyword 'FROM'"
ErrorDescription="The query syntax is not valid."
Line=1
Thanks I tried the example you posted - my objective is btw is to delete all the records (clear it out) but not drop the table. So the SQL I would like to get to work is: " DELETE FROM PidTable" or "DELETE * FROM PidTable
Anyway the SQL statement is valid as far as SQL management studio goes.
System.Data.EntitySqlExcep
Message="The query syntax is not valid., near keyword 'FROM', line 1, column 8."
Source="System.Data.Entity
Column=8
ErrorContext="keyword 'FROM'"
ErrorDescription="The query syntax is not valid."
Line=1
Have you tried simply "Delete PidTable"
ASKER
Hi,
Thanks for the suggestion "Delete PidTable" returns:
"The query syntax is not valid., near identifier 'PidTable', line 1, column 8."
Ward
Thanks for the suggestion "Delete PidTable" returns:
"The query syntax is not valid., near identifier 'PidTable', line 1, column 8."
Ward
Hi,
I have some questions to ask you:
(1) Can I have details about 1st line in code mydb = new PIDEntities(); ?
(2) You are using database that is not having that particular table PidTable. Also check case of table's name.
(3) Try your code at line 5 as:
[code]
cmd.CommandText = "DELETE * FROM PidTable;'";
[/code]
Regards,
VSS
I have some questions to ask you:
(1) Can I have details about 1st line in code mydb = new PIDEntities(); ?
(2) You are using database that is not having that particular table PidTable. Also check case of table's name.
(3) Try your code at line 5 as:
[code]
cmd.CommandText = "DELETE * FROM PidTable;'";
[/code]
Regards,
VSS
Hi,
Remove that single quotation ( ' ) in code, that is by my mistake.
Regards,
VSS
Remove that single quotation ( ' ) in code, that is by my mistake.
Regards,
VSS
This don't take a long time;
//open this when form loads *suggestion
mydb = new PIDEntities();
System.Data.Common.DbConne ction my_connection = mydb.Connection;
my_connection.Open();
//open this when form loads *suggestion
System.Data.Common.DbComma nd cmd = (System.Data.Common.DbComm and)my_con nection.Cr eateComman d();
//correct usage is with *, but filter it, that enable's not to delete all the records
//
cmd.CommandText = "delete * from PidTable";
//
//The DELETE FROM are only applied to other NamedSql's like MySQL, in MsSQL you must input a value
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
//close this when form close/unload
my_connection.Close();
//close this when form close/unload
BUT, To tell you frankly, you have a unpleasant code, the way you code is unprofessional
Do it in a nice neat way
Use professional standard way of coding;
//open this when form loads *suggestion
mydb = new PIDEntities();
System.Data.Common.DbConne
my_connection.Open();
//open this when form loads *suggestion
System.Data.Common.DbComma
//correct usage is with *, but filter it, that enable's not to delete all the records
//
cmd.CommandText = "delete * from PidTable";
//
//The DELETE FROM are only applied to other NamedSql's like MySQL, in MsSQL you must input a value
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
//close this when form close/unload
my_connection.Close();
//close this when form close/unload
BUT, To tell you frankly, you have a unpleasant code, the way you code is unprofessional
Do it in a nice neat way
Use professional standard way of coding;
http://www.experts-exchange.com/blogs/systan/B_2913-Basic-SqlCommand-using-Insert-Update-Delete.html
http://www.experts-exchange.com/blogs/systan/B_2891-using-dataadapter-as-a-source-commands.html
There are saying's, that there are different ways to kill a chicken, but the problem is the code is not a chicken.
ASKER
Hi,
Thanks for the feedback. Ok by trail an error I was able to get it to work but hopefully somebody can explain it. So I discovered it by accident I guess plus what other were telling me here.
In my sample below I have three function 1 which does not work and the other 2 that do.
It seems if I get back a connection object of: {System.Data.SqlClient.Sql Connection } then all is well
it works.
However if I get a connection object of type: {System.Data.EntityClient. EntityConn ection}
Then no matter what SQL I pass it fails. (This was the type I thought I should be using).
Anyway hopefully somebody can give me an better explination then that would be great.
Big that to all the suggestions so far that has been great.
Ward.
Thanks for the feedback. Ok by trail an error I was able to get it to work but hopefully somebody can explain it. So I discovered it by accident I guess plus what other were telling me here.
In my sample below I have three function 1 which does not work and the other 2 that do.
It seems if I get back a connection object of: {System.Data.SqlClient.Sql
it works.
However if I get a connection object of type: {System.Data.EntityClient.
Then no matter what SQL I pass it fails. (This was the type I thought I should be using).
Anyway hopefully somebody can give me an better explination then that would be great.
Big that to all the suggestions so far that has been great.
Ward.
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Data.Entity;
using System.Data.EntityClient;
using System.Data;
using System.Data.SqlClient;
// --------------------------------------- App.Config ---------------------------------------
// <?xml version="1.0" encoding="utf-8"?>
// <configuration>
// <connectionStrings><add name="PIDEntities" connectionString="metadata=res://*/Model1.csdl|res://*/Model1.ssdl|res://*/Model1.msl;provider=System.Data.SqlClient;provider connection string="Data Source=localhost\SQLEXPRESS;Initial Catalog=CarDB;Integrated Security=True;MultipleActiveResultSets=True"" providerName="System.Data.EntityClient" /></connectionStrings>
// </configuration>
// --------------------------------------- SQL Scripts to generate Table --------------------
/* SQL Scripts to generate table..
*
USE [CarDB]
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[PidTable](
[PID1] [numeric](8, 4) NULL
) ON [PRIMARY]
GO
*/
/* Exception Generated from: fnDelete_Records_Entity()
System.Data.EntitySqlException was unhandled
Message="The query syntax is not valid., near keyword 'FROM', line 1, column 8."
Source="System.Data.Entity"
Column=8
ErrorContext="keyword 'FROM'"
ErrorDescription="The query syntax is not valid."
Line=1
StackTrace:
at System.Data.Common.EntitySql.CqlParser.yyerror(String s)
at System.Data.Common.EntitySql.CqlParser.yyparse()
at System.Data.Common.EntitySql.CqlParser.Parse(String query)
at System.Data.Common.EntitySql.CqlQuery.Parse(String query, ParserOptions parserOptions)
at System.Data.Common.EntitySql.CqlQuery.Compile(String query, Perspective perspective, ParserOptions parserOptions, Dictionary`2 parameters, Dictionary`2 variables, Boolean validateTree)
at System.Data.EntityClient.EntityCommand.MakeCommandTree()
at System.Data.EntityClient.EntityCommand.CreateCommandDefinition()
at System.Data.EntityClient.EntityCommand.TryGetEntityCommandDefinitionFromQueryCache(EntityCommandDefinition& entityCommandDefinition)
at System.Data.EntityClient.EntityCommand.GetCommandDefinition()
at System.Data.EntityClient.EntityCommand.InnerPrepare()
at System.Data.EntityClient.EntityCommand.ExecuteReader(CommandBehavior behavior)
at System.Data.EntityClient.EntityCommand.ExecuteScalar[T_Result](Func`2 resultSelector)
at DBTest1.Program.fnDelete_Records_Entity() in D:\Ward\Documents\Visual Studio 2008\Projects\DBTest1\DBTest1\Program.cs:line 84
at DBTest1.Program.Main(String[] args) in D:\Ward\Documents\Visual Studio 2008\Projects\DBTest1\DBTest1\Program.cs:line 108
at System.AppDomain._nExecuteAssembly(Assembly assembly, String[] args)
at Microsoft.VisualStudio.HostingProcess.HostProc.RunUsersAssembly()
at System.Threading.ExecutionContext.Run(ExecutionContext executionContext, ContextCallback callback, Object state)
at System.Threading.ThreadHelper.ThreadStart()
InnerException:
*/
namespace DBTest1
{
class Program
{
// Reference: http://stackoverflow.com/questions/915329/is-it-possible-to-run-native-sql-with-entity-framework
static void ExecuteSql(System.Data.Objects.ObjectContext c, string sql)
{
var entityConnection = (System.Data.EntityClient.EntityConnection)c.Connection;
// Object type returned below (conn): {System.Data.SqlClient.SqlConnection}
System.Data.Common.DbConnection conn = entityConnection.StoreConnection;
ConnectionState initialState = conn.State;
try
{
if (initialState != ConnectionState.Open)
conn.Open(); // open connection if not already open
using (System.Data.Common.DbCommand cmd = conn.CreateCommand())
{
cmd.CommandText = sql;
cmd.ExecuteNonQuery();
}
}
finally
{
if (initialState != ConnectionState.Open)
conn.Close(); // only close connection if not initially open
}
}
static void fnDelete_Records_Entity()
{
//ObjectContext context = new PIDEntities();
System.Data.Objects.ObjectContext mydb;
mydb = new PIDEntities();
// Object Type returned below (my_connection): {System.Data.EntityClient.EntityConnection}
System.Data.Common.DbConnection my_connection = mydb.Connection;
my_connection.Open();
System.Data.Common.DbCommand cmd = (System.Data.Common.DbCommand)my_connection.CreateCommand();
cmd.CommandText = "DELETE FROM PidTable;";
cmd.CommandType = CommandType.Text;
cmd.ExecuteNonQuery();
my_connection.Close();
}
static void fnDelete_Records_ADO()
{
// Object type returned (myConn): {System.Data.SqlClient.SqlConnection}
SqlConnection myConn = new SqlConnection("Data Source=localhost\\SQLEXPRESS;Initial Catalog=CarDB;Integrated Security=True;database=CarDB");
string str = "DELETE FROM PidTable;";
SqlCommand myCommand = new SqlCommand(str, myConn);
myConn.Open();
myCommand.ExecuteNonQuery();
}
static void fnDelete_Test_Entity2()
{
System.Data.Objects.ObjectContext mydb;
mydb = new PIDEntities();
ExecuteSql(mydb,"DELETE FROM PidTable;");
}
static void Main(string[] args)
{
fnDelete_Records_Entity(); // Generates exception see above.
fnDelete_Records_ADO(); // Works
fnDelete_Test_Entity2(); // Works
}
}
}
Your .Net Framework is not supported.
EntityConnection Class uses Framework 4.0 and you may have a lower version installed.
EntityConnection Class uses Framework 4.0 and you may have a lower version installed.
If you have .Net framework 4 or 3.5 sp1
http://msdn.microsoft.com/en-us/library/bb738561.aspx
Open/Read the links <In This Section>
http://msdn.microsoft.com/en-us/library/bb738561.aspx
Open/Read the links <In This Section>
ASKER
Hi,
I am using Visual Sudio 2008. So can I assume that these features that I am trying to use
are not available using the {System.Data.EntityClient. EntityConn ection}?
Thefore I have to use: {System.Data.SqlClient.Sql Connection }
Thanks,
Ward.
I am using Visual Sudio 2008. So can I assume that these features that I am trying to use
are not available using the {System.Data.EntityClient.
Thefore I have to use: {System.Data.SqlClient.Sql
Thanks,
Ward.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
The EntityFramework is supported in 3.5 SP1, so that should not be your issue.
Why do you want to use the EntityFramework? You want to work with a Concept Data model?
Is that level of abstraction needed in your application?
Why do you want to use the EntityFramework? You want to work with a Concept Data model?
Is that level of abstraction needed in your application?
SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Hey guys big thanks I am going to go through this in detail so I can award multiple points.
Awsome all the help I have got so far.
I realize my code was not the "best practices" as I like to break down the sample into its simpilist form. Then I will do things like open the connection when the form loads etc. and so on.
So big thanks while I digest...
Ward.
Awsome all the help I have got so far.
I realize my code was not the "best practices" as I like to break down the sample into its simpilist form. Then I will do things like open the connection when the form loads etc. and so on.
So big thanks while I digest...
Ward.
Atlast you knew it.
Are we finished here?
ASKER
Hi,
Big thanks for all the help - it was excellent, and thanks for your patience with me here as I wanted to get to a more root cause which I got to in the end :)
Ward.
Big thanks for all the help - it was excellent, and thanks for your patience with me here as I wanted to get to a more root cause which I got to in the end :)
Ward.
Delete statement is used to delete records from table and Drop is used to drop table from database.
Regards,
VSS
Open in new window