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

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)

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.

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)

The error i am getting is:

"Syntax error in INSERT INTO statement."

Regards
Khalil

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)

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