[RESOLVED] 80040e14 - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

Transact-SQL (2000)

[RESOLVED] 80040e14

Author

Topic

Pace
Constraint Violating Yak Guru

264 Posts

Posted - 2006-11-24 : 10:29:35

Hi all,

I am getting an insert error.

I am trying to insert the following value into a table using an insert statement via a variable; "FS 12345"

now I get the error 80040e14 it says incorrect syntax near line1: "12345"

The field is a Primary Key but also a foreign key from a related table. The datatype is nvarchar(10)

I appreciate 80040e14 could be one of a thousand things but I was wondering if the experts here had experienced something similar and im hoping the providing the error line will help us to identify where the problem lies.

Any help is greatly appreciated.

Oh hell I might as well enter the code while im here;


            Set cnn = New ADODB.Connection
            cnn.ConnectionString = cnnSQLD
            Set cmd = New ADODB.Command
            cmd.CommandText = "INSERT INTO JobReview (JobNo, [Cust Code], Dept, ReviewDate, Review) " & _
                          "VALUES (" & JobNo & ", " & CusCode & ", " & Dept & ", " & ReviewDate & ",1)"
                          
            cnn.Open
            cmd.ActiveConnection = cnn
            cmd.Execute

Pace

"Impossible is Nothing"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-24 : 10:37:38

For which field you are supplying value - "FS 12345"?

I see none of those values there enclosed in the quotes...not even ReviewDate, when it should be!

Harsh Athalye
India.
"Nothing is Impossible"

Pace
Constraint Violating Yak Guru

264 Posts

Posted - 2006-11-24 : 10:40:51

Hi Harsh,

Thanks for you time.

When I roll over the tooltip show the appropriate value, however the answer to your question is the first field. Im trying to insert "FS 12345" into the job number.

Do you suggest another way to pass these in?

"Impossible is Nothing"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-24 : 10:42:50

Whichever field in your query are varchar, char, nvarchar, datetime etc., enclose values supplied for them in quotes like this:

cmd.CommandText = "INSERT INTO JobReview (JobNo, [Cust Code], Dept, ReviewDate, Review) " & _
                          "VALUES ('" & JobNo & "', " & CusCode & ", " & Dept & ", '" & ReviewDate & "',1)"

Note: I assume rest of the fields are numeric.

Harsh Athalye
India.
"Nothing is Impossible"

Pace
Constraint Violating Yak Guru

264 Posts

Posted - 2006-11-24 : 10:48:11

Ah, I knew I was missing something. Its been a while since I had to code like this, I prefer stored procedures

Im getting another error now but thats besides the point, you have answered this problem.

Thank you very much I owe you a drink

"Impossible is Nothing"

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-24 : 10:54:30

I also share your dislike for dynamically building SQL statements like this...especially after knowing the dangers of SQL Injection !

quote:
Thank you very much I owe you a drink

Thanks for the offer...but FYI, I don't drink.

Harsh Athalye
India.
"Nothing is Impossible"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-11-24 : 22:44:44

>>I prefer stored procedures

Yes. You should always use stored procedure with input parameter than using concatenated sql statements

>>but FYI, I don't drink.

So as me

Madhivanan

Failing to plan is Planning to fail

harsh_athalye
Master Smack Fu Yak Hacker

5581 Posts

Posted - 2006-11-25 : 01:12:15

"So as me"

Hey madhivanan,
Thanks for the company!

Harsh Athalye
India.
"Nothing is Impossible"

madhivanan
Premature Yak Congratulator

22864 Posts

Posted - 2006-11-25 : 01:25:40