Wrox Programmer Forums
|
Visual Basic 2008 Essentials If you are new to Visual Basic programming with version 2008, this is the place to start your questions.
Welcome to the p2p.wrox.com Forums.

You are currently viewing the Visual Basic 2008 Essentials 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 June 18th, 2010, 05:20 AM
Authorized User
 
Join Date: Dec 2009
Posts: 69
Thanks: 6
Thanked 0 Times in 0 Posts
Default SQL Statement

I am trying to insert data into Excel using SQL statement. The data is provided by users through a form. I keep getting a syntax error with the code in bold and cannot find anything wrong with it.

<sJobNo = Microsoft.VisualBasic.Right(cmbCity.Text, 1) & sYearMonth & Microsoft.VisualBasic.Right("00" & sSequence, 2)>

<Me.txtProjectNo.Text = sJobNo>

<projCommand.CommandText = "Insert into [ProjectNumber$] (Number) Values '" & txtProjectNo.Text & "'">

Please note that sJobNo is an expression.

I would appreciate your help.

Thanks
Khalil
 
Old June 18th, 2010, 05:41 AM
Friend of Wrox
 
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

Hi

Can you try with the following

<projCommand.CommandText = "Insert into [ProjectNumber$] (Number) Values (" & txtProjectNo.Text & ")">

Assuming the (Number) to be Integer/Numeric datatype

Cheers
Shasur
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips &amp; Tricks (http://www.vbadud.blogspot.com)
 
Old June 18th, 2010, 07:15 AM
Authorized User
 
Join Date: Dec 2009
Posts: 69
Thanks: 6
Thanked 0 Times in 0 Posts
Default SQL Statement

Thanks for the reply but it did not work. I should also mention that sJobNo cannot be a number or integer since it does concatenate many characters; it has to be string.
 
Old June 18th, 2010, 08:44 AM
Friend of Wrox
 
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

Can you post the error you are getting?

Cheers
Shasur
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips &amp; Tricks (http://www.vbadud.blogspot.com)
 
Old June 19th, 2010, 04:38 AM
Authorized User
 
Join Date: Dec 2009
Posts: 69
Thanks: 6
Thanked 0 Times in 0 Posts
Default SQL Statement

The error i am getting is:

"Syntax error in INSERT INTO statement."

Regards
Khalil
 
Old June 19th, 2010, 09:57 PM
Friend of Wrox
 
Join Date: Sep 2005
Posts: 812
Thanks: 1
Thanked 53 Times in 49 Posts
Default

Hi Khalil

The statement projCommand.CommandText = "Insert into [ProjectNumber$] (Number) Values ('" & txtProjectNo.Text & "'")>

looks fine assuming there is a sheet - ProjectNumber in the Excel you have specified in the connection string.

Are there any chances that some separators like comma come in sJobNo

You can try printing the insert into statement in immediate window and check

Cheers
Shasur
__________________
C# Code Snippets (http://www.dotnetdud.blogspot.com)

VBA Tips &amp; Tricks (http://www.vbadud.blogspot.com)
 
Old June 19th, 2010, 10:22 PM
Authorized User
 
Join Date: Dec 2009
Posts: 69
Thanks: 6
Thanked 0 Times in 0 Posts
Default SQL Statement

Hi Shasur,

I have in my code a field called "Number", which I changed to "JobNo" and it is working fine. The problem has been resolved. Although I am not sure of the reason, I am guessing that Number is a word used by the language and should not use it in my project. I cannot find any other explanation. Please correct me if I am wrong. Thank you very much for your help.

Regards
Khalil





Similar Threads
Thread Thread Starter Forum Replies Last Post
convert a SQL Statement from MS Access to a SQL Corey Access 6 March 28th, 2007 12:33 PM
SQL Statement SAMBUSA SQL Language 2 December 1st, 2004 11:10 AM
Help with SQL statement acdsky SQL Language 3 June 18th, 2004 04:53 AM
Sql Statement help morpheus Classic ASP Basics 0 March 9th, 2004 10:55 AM
T-SQL statement sam78_my SQL Language 1 September 29th, 2003 02:14 AM





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