Asp Syntax Error Or Access Violation
Contents |
help? Post your question and get tips & solutions from a community of 418,430 IT Pros & Developers. It's quick & easy. syntax error or access violation 1071 specified key was too long Syntax error or access violation P: 1 J W I have an
Syntax Error Or Access Violation 1068 Multiple Primary Key Defined
ASP page in which there is embedded SQL. I am trying to use variables in the SQL but am
Syntax Error Or Access Violation 1064
getting the error 'syntax error or access violation'. In the past, I have successfully used variables for table names and within WHERE clauses in SQL in an ASP page. This time,
Syntax Error Or Access Violation 1142 Select Command Denied To User
I am trying to use variables in the SELECT portion of an INSERT statement. (1) The following code works in a SQL stored procedure: DECLARE @BatchId uniqueidentifier SELECT @BatchId = NEWID() INSERT INTO sgCustomASPBatch SELECT @BatchId,e.EngagementId,GETDATE() FROM Engagement e WHERE e.Name like '%ASP' (2) Here is the same code in ASP which gets the syntax error: dim BatchId ' get new id for syntax error or access violation 1065 query was empty batch sSQL2 = "SELECT NEWID() 'NewId' " set rst2 = oUtility2.Execute(Session("ReportsDSN"),sSQL2) BatchId = rst2("NewId") set rst2 = nothing sSQL2 = "INSERT INTO sgCustomASPBatch " & _ "SELECT " & BatchId & _ " , EngagementId, GETDATE() " & _ "FROM Engagement e " & _ "WHERE e.EngagementId in ('" & pEngId & "') " set rst2 = oUtility2.Execute(Session("ReportsDSN"),sSQL2) (3) Without the variable for BatchId, this code works in ASP: sSQL2 = "INSERT INTO sgCustomASPBatch " & _ "SELECT NEWID(), EngagementId, GETDATE() " & _ "FROM Engagement e " & _ "WHERE e.EngagementId in ('" & pEngId & "') " set rst2 = oUtility2.Execute(Session("ReportsDSN"),sSQL2) What do I need to change in the syntax regarding the variable BatchId to make example 2 above work in ASP? Thanks. Judy Dec 4 '06 #1 Post Reply Share this Question 2 Replies Expert 100+ P: 692 shweta123 write the sql as this sSQL2 = "INSERT INTO sgCustomASPBatch " & _ "SELECT " & '" & BatchId & "' & _ " , EngagementId, GETDATE() " & _ "FROM Engagement e " & _ "WHERE e.EngagementId in ('" & pEngId & "'
here for a quick overview of the site Help Center Detailed answers to any questions you might have Meta Discuss the workings and policies of this site About Us Learn more about Stack Overflow the company Business Learn syntax error or access violation 1286 unknown storage engine 'innodb' more about hiring developers or posting ads with us Stack Overflow Questions Jobs Documentation Tags syntax error or access violation 1066 not unique table/alias Users Badges Ask Question x Dismiss Join the Stack Overflow Community Stack Overflow is a community of 4.7 million programmers, just like you, syntax error or access violation 1091 helping each other. Join them; it only takes a minute: Sign up Parameterized Insert statement with transaction throwing an error up vote 1 down vote favorite I had a parametrized insert statement that was working well and I https://bytes.com/topic/asp-classic/answers/573013-syntax-error-access-violation needed to add a select statement and wrap it all in a transaction in order to get data for one of the fields for the insert. I'm not sure if I've hit a limitation with ASP/ADO or if I've just got something syntactically wrong. Here's my code: set oSQLCommand = Server.CreateObject("ADODB.Command") with oSQLCommand .ActiveConnection = conn .CommandType = 1 .CommandText = "set nocount on " &_ "begin transaction " &_ "declare @docid integer " &_ "begin " http://stackoverflow.com/questions/341761/parameterized-insert-statement-with-transaction-throwing-an-error &_ "set @docid = (SELECT MAX(id+1) AS docid FROM draft_main) " &_ "INSERT INTO draft_details (id, main_id, blah) " &_ "VALUES ( ?, @docid, ?)" &_ "end " &_ "commit" .Parameters(0).value = c_id .Parameters(1).value = "blah blah" .execute end with set oSQLCommand = nothing When I run that code, I get this error message that's triggered when it tries to set the value of Parameter(0) Microsoft OLE DB Provider for SQL Server (0x80004005) Syntax error or access violation Any idea what's wrong? I have plenty of experience with ASP and SQL but none with stored procedures. Is this code so painfully close to a stored procedure I should just move it over and call it good? Thanks. sql asp-classic share|improve this question asked Dec 4 '08 at 19:31 Curtis 299315 add a comment| 1 Answer 1 active oldest votes up vote 2 down vote accepted Try putting ; after each individual statement. e.g. .CommandText = "set nocount on; " &_ "begin transaction; " &_ "declare @docid integer; " &_ "begin " &_ "set @docid = (SELECT MAX(id+1) AS docid FROM draft_main); " &_ "INSERT INTO draft_details (id, main_id, blah) " &_ "VALUES ( ?, @docid, ?);" &_ "end ;" &_ "commit;" share|improve this answer answered Dec 4 '08 at 19:39 Andrew Rollings 10.3k43948 Also, this would be better as a call to a stored proc
Tips/Tricks Top Articles Beginner Articles Technical Blogs Posting/Update Guidelines Article Help Forum Article Competition Submit an article or tip Post your Blog quick answersQ&A Ask a Question View Unanswered Questions View All Questions... http://www.codeproject.com/Questions/60972/Syntax-error-or-access-violation C# questions Linux questions ASP.NET questions SQL questions VB.NET questions discussionsforums All Message Boards... http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=68083 Application Lifecycle> Running a Business Sales / Marketing Collaboration / Beta Testing Work Issues Design and Architecture ASP.NET JavaScript C / C++ / MFC> ATL / WTL / STL Managed C++/CLI C# Free Tools Objective-C and Swift Database Hardware & Devices> System Admin Hosting and Servers Java .NET Framework Android iOS Mobile SharePoint Silverlight syntax error / WPF Visual Basic Web Development Site Bugs / Suggestions Spam and Abuse Watch features Competitions News The Insider Newsletter The Daily Build Newsletter Newsletter archive Surveys Product Showcase Research Library CodeProject Stuff communitylounge Who's Who Most Valuable Professionals The Lounge The Insider News The Weird & The Wonderful The Soapbox Press Releases Non-English Language > General Indian Topics General Chinese Topics help What is 'CodeProject'? syntax error or General FAQ Ask a Question Bugs and Suggestions Article Help Forum Site Map Advertise with us About our Advertising Employment Opportunities About Us Ask a Question All Questions All Unanswered FAQ Syntax error or access violation Rate this: Please Sign up or sign in to vote. See more: VB VB.NET hi..... Given below is my code to insert data int a table : USER. Dim myInsertQuery As String = "INSERT INTO USER (USERID,PASSWORD) Values('roll', 'name')" Dim mycommand As New OdbcCommand(myInsertQuery) mycommand.Connection = myConnection mycommand.ExecuteNonQuery() But the code is not working properly...i am getting the error message given below .....can any one suggest a solution please,thanks in advance.. 'ERROR [42000] [Sybase][ODBC Driver][Adaptive Server Anywhere] 'Syntax error or access violation: near 'USER' in INTO [USER] Posted 23-Feb-10 17:58pm vidhyaej482 Updated 23-Feb-10 18:16pm Sandeep Mewara505.3K v2 Add a Solution 3 solutions Top Rated Most Recent Rate this: Please Sign up or sign in to vote. Solution 1 Accept Solution Reject Solution When you name tables after keywords, you should put them in []. Dim myInsertQuery As String = "INSERT INTO [USER] (USERID,PASSWORD) Values('roll', 'name')" Permalink Posted 23-Feb-10 18:18pm Christian Graus660.9K Rate this: Please Sign up or sign in to vote. Solut
SQL Server experts to answer whatever question you can come up with. Our new SQL Server Forums are live! Come on over! We've restricted the ability to create new threads on these forums. SQL Server Forums Profile | ActiveTopics | Members | Search | ForumFAQ Register Now and get your question answered! Username: Password: Save Password Forgot your Password? All Forums SQL Server 2005 Forums High Availability (2005) Syntax error or access violation Reply to Topic Printer Friendly Author Topic vnative Starting Member USA 2 Posts Posted-06/20/2006: 11:32:49 Hello,I have 2 database, both on SQL 2000 with SP4 and we are trying to setup transactional replication. Snapshot works just fine, but as you know it's not very efficient. With the transactional method, we get an "Syntax error or access violation" error on tables with spaces. I know spaces are BAD, but this is a large database that was recently upsized from an MDB file. Changing the table names would break the application, so it is important that we keep the table names as is. Replication seems to work fine on tables without spaces. Does anyone know a work-around for this issue?Thanks in advance. nathans Aged Yak Warrior USA 938 Posts Posted-06/28/2006: 20:49:00 Well, the workaround is to rename the table Ill have to do some testing to see if I can find another way around this. Although a stretch, this KB could be relevant. http://support.microsoft.com/kb/887165/en-usIm going to setup Trans Replication between two test servers using [Northwind].[Order Details] table to see if I can reproduce.Nathan Skerl Edited by - nathans on 06/28/2006 20:50:16 nathans Aged Yak Warrior USA 938 Posts Posted-06/28/2006: 20:52:54 And is that the exact error message you receive? When does it complain?Nathan Skerl vnative Starting Member USA 2 Posts Posted-06/28/2006: 22:29:57 Yes, the error message I get is: "Syntax error or access violation". Not very informative. You'd think that MS could put the table names in square [] brackets when running the script. It happens when it reaches tables with spaces. The tables without seem to work fine. I'm right now in the process of renaming tables because I don't have any other solutions. Thanks for helping - I appreciate it. Topic Reply to Topic Printer Friendly Jump To: Select Forum General SQL Server Forums New to SQL Server Programming New to SQL Server Administration Script Library Data Corruption Issues Database Design and Application Architecture SQL Server 2012 Forums Transact-SQL (2012) SQL Server Administration (2012) SSIS and Import/Export (2012) Analysis Server and Reporting Services (2012) Replication (2012) Availability Groups and DR (2012)