Wrox Programmer Forums

Need to download code?

View our list of code downloads.

Go Back   Wrox Programmer Forums > Visual Basic > VB 6 Visual Basic 6 > Pro VB Databases
Password Reminder
Register
Register | FAQ | Members List | Calendar | Search | Today's Posts | Mark Forums Read
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 tens of thousands of software programmers and website developers including Wrox book authors and readers. As a guest, you can read any forum posting. By joining today you can post your own programming questions, respond to other developersí questions, and eliminate the ads that are displayed to guests. Registration is fast, simple and absolutely free .
DRM-free e-books 300x50
Reply
 
Thread Tools Display Modes
  #1 (permalink)  
Old May 11th, 2009, 05:15 AM
Authorized User
 
Join Date: Dec 2007
Location: Liberec, , Czech Republic.
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
Reply With Quote
  #2 (permalink)  
Old May 11th, 2009, 08:40 AM
Friend of Wrox
Points: 7,395, Level: 36
Points: 7,395, Level: 36 Points: 7,395, Level: 36 Points: 7,395, Level: 36
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Capital Federal, , Argentina.
Posts: 2,190
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.
================================================== =========
Reply With Quote
  #3 (permalink)  
Old May 11th, 2009, 09:43 AM
Authorized User
 
Join Date: Dec 2007
Location: Liberec, , Czech Republic.
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
Reply With Quote
  #4 (permalink)  
Old May 11th, 2009, 10:50 AM
Authorized User
 
Join Date: Dec 2007
Location: Liberec, , Czech Republic.
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
Reply With Quote
  #5 (permalink)  
Old May 11th, 2009, 11:41 AM
Friend of Wrox
Points: 7,395, Level: 36
Points: 7,395, Level: 36 Points: 7,395, Level: 36 Points: 7,395, Level: 36
Activity: 0%
Activity: 0% Activity: 0% Activity: 0%
 
Join Date: Jun 2003
Location: Capital Federal, , Argentina.
Posts: 2,190
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.
================================================== =========
Reply With Quote
The Following User Says Thank You to gbianchi For This Useful Post:
vanik.cz (May 11th, 2009)
  #6 (permalink)  
Old May 11th, 2009, 03:38 PM
Authorized User
 
Join Date: Dec 2007
Location: Liberec, , Czech Republic.
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.
Reply With Quote
  #7 (permalink)  
Old May 11th, 2009, 04:38 PM
Friend of Wrox
 
Join Date: Nov 2007
Location: Central Florida, USA.
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
Reply With Quote
  #8 (permalink)  
Old May 11th, 2009, 04:44 PM
Authorized User
 
Join Date: Dec 2007
Location: Liberec, , Czech Republic.
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
Reply With Quote
  #9 (permalink)  
Old May 11th, 2009, 04:46 PM
Authorized User
 
Join Date: Dec 2007
Location: Liberec, , Czech Republic.
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
Reply With Quote
  #10 (permalink)  
Old May 11th, 2009, 04:49 PM
Friend of Wrox
 
Join Date: Nov 2007
Location: Central Florida, USA.
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
Reply With Quote
Reply


Thread Tools
Display Modes

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is Off
HTML code is Off
Trackbacks are Off
Pingbacks are On
Refbacks are Off

Similar Threads
Thread Thread Starter Forum Replies Last Post
Incorrect syntax near '.' meraj ADO.NET 6 February 27th, 2012 12: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



All times are GMT -4. The time now is 12:01 PM.


Powered by vBulletin® Version 3.7.0
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
© 2013 John Wiley & Sons, Inc.