conn.Execute Problem

I need to insert records into the 2 tables which are stored in SQL Server
The code :

....
....
sql1 = "Insert into TBL1 values" _
& "('" & idc & "','" & eng & "','" & chin & "','" _
& birth & "','" & add1 & "','" & add2 & "','" _
& add3 & "','" & idk & "','" & sex & "','" _
& phone & "','" & nat & "','" & photoPath & "','" _
& entry & "','" & leave & "','" & st & "','" _
& remark & "')"
sql2 = "Insert into TBL2 values" _
& "('" & id & "','" & gengn & "','" & gchinn & "','" _
& relation & "','" & gaddr & "','" & gphone & "','" _
& gphonew & "','" & mobile & "')"

'Enter the first record
conn.Execute sql1
conn.Execute sql2



The error message : "Method 'Execute ' of object 'Connection' failed"
Why?
I have checked the sql statements by inserting one variable by one. It works well, however,when I insert the variables up to, say 8, the program can't run and generated the above error statement

I don't know what happen. I think my sql statments are o.k.
I also tried to only executing sql2, i.e. conn.execute sql2 works well. It can insert values by sql2 to a referred table. But sql1 can't insert values to a referred table

Since you are not specifying the field names in your insert statement, make very sure that you are listing the values in the order that the fields appear in the table. Normally I specify the field names in an insert line (that way I don't have to specify values for all fields if I don't want).

Example:

Table1
Field - Field1 int
Field - Field2 varchar(10)
Field - Field3 int

1. Insert Into Table1 Values (1, 'A string.', 2)
2. Insert Into Table1 (Field1, Field3) Values (1, 2)
3. Insert Into table1 Values (1, 2)
4. Insert Into Table1 Values ('A string', 1, 2)

Provided that all of the fields are set in the database to allow Nulls, statements 1 and 2 will run with no problem. Statement 3 will cause an error - Type mismatch - parameter 2 does not match the datatype of Field2 in the table.
Statement 4 will also fail, because the values are not in the correct order. When SQL attempts to insert the first parameter 'A string' into Field1 which is an int you will get a type mismatch.

Hope this helps.
Specte

If birth is a date, make sure that is is the correct form. You should check that on your sql server. Standard, you should enter a date in the form of yyyy/mm/dd, unless your sql server is configured differently. Try using a format.

Another thing to find out what really went wrong is to do a debug.print of sql1, and run the exact statement in query analyser (the tool that ships with SQL server). This will give you a detailed error message. Once you get that, the solution's probably not far away.

Tom Cannaerts
[email protected]

The best way to escape a problem, is to solve it.

Its basically the datatype mismatch which is creating problems for u.

See to whther quotes u are using matches and check whether it has been given to number columns.Use appropriate conversion function with the value list and if all these are okay then

CHECK the constraints (or rules) , defaults , triggers attached to TBL1

- Srikanth

I'd recommend adding some error handling code to
cycle trough YourConnection.Errors collection
and get their description. Then maybe you'd get more sensible/detailed diagnosis in place of
"Method 'Execute ' of object 'Connection' failed"

Good luck