Wrox Programmer Forums
|
Pro VB Databases Advanced-level VB coding questions specific to using VB with databases. Beginning-level questions or issues not specific to database use will be redirected to other forums.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Pro VB Databases section of the Wrox Programmer to Programmer discussions. This is a community of software programmers and website developers including Wrox book authors and readers. New member registration was closed in 2019. New posts were shut off and the site was archived into this static format as of October 1, 2020. If you require technical support for a Wrox book please contact http://hub.wiley.com
 
Old May 11th, 2009, 05:15 AM
Authorized User
 
Join Date: Dec 2007
Posts: 21
Thanks: 1
Thanked 0 Times in 0 Posts
Send a message via ICQ to vanik.cz
Unhappy Incorrect syntax near 'GO'

Hello,
Im writing an application which calls stored procedure in MSSQL 2008. This stored procedure inserts line into the table and returns the line number that is generated by SQL.

INSERT INTO ...
SELECT @@IDENTITY AS [PID]

If I run it from management studio, there is no problem, the line is added and right number of line is returned. But if run the same command from VB6 using a ADODB.Connection object, execution of the query fails with the error 'Incorrect syntax near 'GO''
Im using code that is generated by SQL management studio for the stored procedure and for calling it too.

For the line separation Im using the constant vbCrLf. At first look the queries are the same, what can be wrong?
__________________
JVV
 
Old May 11th, 2009, 08:40 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

If you are calling a SP, what are you using vbCrLf for???
__________________
HTH

Gonzalo


================================================== =========
Read this if you want to know how to get a correct reply for your question.
(Took that from Doug signature and he Took that from Peter profile)
================================================== =========
My programs achieved a new certification :
WORKS ON MY MACHINE
================================================== =========
I know that CVS was evil, and now i got the
proof.
================================================== =========
 
Old May 11th, 2009, 09:43 AM
Authorized User
 
Join Date: Dec 2007
Posts: 21
Thanks: 1
Thanked 0 Times in 0 Posts
Send a message via ICQ to vanik.cz
Default

In execution of SP there is two GO - generated by Management studio, GO must be on single line, or not? I will post the code after one hour, when I will arrive at home.
__________________
JVV
 
Old May 11th, 2009, 10:50 AM
Authorized User
 
Join Date: Dec 2007
Posts: 21
Thanks: 1
Thanked 0 Times in 0 Posts
Send a message via ICQ to vanik.cz
Default

There is code of SP

USE [TrapDB]
GO
/****** Object: StoredProcedure [dbo].[LBLPRT_AddPrintJob] Script Date: 05/11/2009 16:46:31 ******/
SETANSI_NULLSON
GO
SET
QUOTED_IDENTIFIERON
GO
-- =============================================
-- Author: <Author,,Name>
-- Create date: <Create Date,,>
-- Description: <Description,,>
-- =============================================
ALTERPROCEDURE [dbo].[LBLPRT_AddPrintJob]
-- Add the parameters for the stored procedure here
@PrintServer nvarchar(255),
@PrinterName nvarchar(255),
@PrintedBy nvarchar(255),
@PrintedFrom nvarchar(255),
@PrintIssued nvarchar(255),
@PrintData binary(4096)
AS
BEGIN
INSERT
INTO LabelPrints(PrintServer,PrinterName,PrintedBy,PrintedFrom,PrintIssued,PrintData)
VALUES (@PrintServer,@PrinterName,@PrintedBy,@PrintedFrom,@PrintIssued,@PrintData)
SELECT@@IDENTITYAS [PID]
END

and the code in VB that I use to call the sp:

Dim cnn As New ADODB.Connection
cnn.Open Text2 'Driver={SQL Server}; Server=FORNAX\SQLEXPRESS; Database=TrapDB; Trusted_Connection=yes

Set WshNetwork = CreateObject("WScript.Network")
ComputerName = WshNetwork.ComputerName
UserName = WshNetwork.UserName

query = "USE [TrapDB] " + vbCrLf
query = query + "GO " + vbCrLf + vbCrLf
query = query + "DECLARE @return_value int " + vbCrLf
query = query + "DECLARE @Data Binary(4096) " + vbCrLf + vbCrLf
query = query + "SET @Data =CONVERT(binary(4096)," + Validate("ahoj") + ") " + vbCrLf + vbCrLf
query = query + "EXEC @return_value = [dbo].[LBLPRT_AddPrintJob] " + vbCrLf
query = query + " @PrintServer = N" + Validate(Text3) + "," + vbCrLf
query = query + " @PrinterName = N" + Validate(Text4) + ", " + vbCrLf
query = query + " @PrintedBy = N" + Validate(UserName) + ", " + vbCrLf
query = query + " @PrintedFrom = N" + Validate(ComputerName) + ", " + vbCrLf
query = query + " @PrintIssued = N" + Validate(Format(Now, "YYYY-MM-DD hh:mm:ss")) + ", " + vbCrLf
query = query + " @PrintData = @Data " + vbCrLf
query = query + "GO " + vbCrLf

Set rstemp = cnn.Execute(query)
Debug.Print rstemp(0)

__________________
JVV
 
Old May 11th, 2009, 11:41 AM
Friend of Wrox
 
Join Date: Jun 2003
Posts: 2,189
Thanks: 5
Thanked 59 Times in 57 Posts
Send a message via MSN to gbianchi
Default

Why don't you call directly the SP is beyond my understand, but try with vbnewline instead of vbcrlf...
__________________
HTH

Gonzalo


================================================== =========
Read this if you want to know how to get a correct reply for your question.
(Took that from Doug signature and he Took that from Peter profile)
================================================== =========
My programs achieved a new certification :
WORKS ON MY MACHINE
================================================== =========
I know that CVS was evil, and now i got the
proof.
================================================== =========
The Following User Says Thank You to gbianchi For This Useful Post:
vanik.cz (May 11th, 2009)
 
Old May 11th, 2009, 03:38 PM
Authorized User
 
Join Date: Dec 2007
Posts: 21
Thanks: 1
Thanked 0 Times in 0 Posts
Send a message via ICQ to vanik.cz
Default

how can I call it directly? vbnewline has the same effect as vbcrlf...
__________________
JVV

Last edited by vanik.cz; May 11th, 2009 at 03:47 PM..
 
Old May 11th, 2009, 04:38 PM
Friend of Wrox
 
Join Date: Nov 2007
Posts: 207
Thanks: 2
Thanked 15 Times in 15 Posts
Default Or...

use a replacement instead of a new line?

Use chr(13) & chr(10) then if you need to read it later just Replace chr(13) & chr(10) with "<br />" or vbNewLine or vbCrLf

that way you don't need to worry what the vb code will affect
__________________
Jason Hall

Follow me on Twitter @jhall2013
 
Old May 11th, 2009, 04:44 PM
Authorized User
 
Join Date: Dec 2007
Posts: 21
Thanks: 1
Thanked 0 Times in 0 Posts
Send a message via ICQ to vanik.cz
Default

Im not sure about that I understand you... I know that I not need to have all the breaks that I have, but Im mad from this problem and I want to have the same code that works in management studio...
__________________
JVV
 
Old May 11th, 2009, 04:46 PM
Authorized User
 
Join Date: Dec 2007
Posts: 21
Thanks: 1
Thanked 0 Times in 0 Posts
Send a message via ICQ to vanik.cz
Default

Sorry for dumb question about direct call of SP, I was get it later :-) Thanks, I will try it...
__________________
JVV
 
Old May 11th, 2009, 04:49 PM
Friend of Wrox
 
Join Date: Nov 2007
Posts: 207
Thanks: 2
Thanked 15 Times in 15 Posts
Default I should have read your code...

I should have read your code better... why are you even using a line break you don't need one?

query = "USE [TrapDB] "
query = query + "GO "
query = query + "DECLARE @return_value int "
query = query + "DECLARE @Data Binary(4096) "
query = query + "SET @Data =CONVERT(binary(4096)," + Validate("ahoj") + ") "
query = query + "EXEC @return_value = [dbo].[LBLPRT_AddPrintJob] "
query = query + " @PrintServer = N" + Validate(Text3) + ","
query = query + " @PrinterName = N" + Validate(Text4) + ", "
query = query + " @PrintedBy = N" + Validate(UserName) + ", "
query = query + " @PrintedFrom = N" + Validate(ComputerName) + ", "
query = query + " @PrintIssued = N" + Validate(Format(Now, "YYYY-MM-DD hh:mm:ss")) + ", "
query = query + " @PrintData = @Data "
query = query + "GO "


This looks like C# so i'm not sure if you need ; after each line since I use vb.net.
__________________
Jason Hall

Follow me on Twitter @jhall2013





Similar Threads
Thread Thread Starter Forum Replies Last Post
Incorrect syntax near '.' meraj ADO.NET 6 February 27th, 2012 01:01 AM
Line 6: Incorrect syntax near garrydawkins SQL Server 2000 2 April 10th, 2006 03:39 PM
Incorrect syntax near '!' sinapra Classic ASP Databases 8 August 25th, 2004 02:15 AM
Incorrect syntax near '`' Hez Classic ASP Databases 3 August 5th, 2004 08:13 AM
Incorrect syntax...... Adam H-W Classic ASP Databases 8 June 21st, 2004 11:14 AM





Powered by vBulletin®
Copyright ©2000 - 2020, Jelsoft Enterprises Ltd.
Copyright (c) 2020 John Wiley & Sons, Inc.