VB 2005 OleDbException was unhandled "Incorrect syntax near the keyword 'DEFAULT'"

[Stirky76]

Hi

I'm using the following code to extract a single record into dataset but it crashes out with "Incorrect syntax near the keyword 'DEFAULT'.

DA_ins_spr.SelectCommand.Parameters("cap_stuc").Value = _

Trim(s_number.Text)

DS_spr1.Clear()

DA_ins_spr.Fill(DS_spr1)

The Select command SQL is:

SELECT DISTINCT

srs_cap.cap_rouc, srs_cap.cap_stuc, ins_stu.stu_name, ins_stu.stu_had1, ins_stu.stu_had2, ins_stu.stu_had3, ins_stu.stu_had4, ins_stu.stu_had5,

ins_stu.stu_hapc, CONVERT(nvarchar(30), srs_cbo.cbo_endd, 103) AS End_Date

FROM srs_cap INNER JOIN

ins_stu ON srs_cap.cap_stuc = ins_stu.stu_code INNER JOIN

srs_cbo ON srs_cap.cap_crsc = srs_cbo.cbo_crsc AND srs_cap.cap_ayrc = srs_cbo.cbo_ayrc AND srs_cap.cap_blok = srs_cbo.cbo_blok AND

srs_cap.cap_occl = srs_cbo.cbo_occl

WHERE (srs_cap.cap_ayrc = '10/11') AND (srs_cap.cap_stuc = ?) AND (srs_cap.cap_rsp1 = 'F') OR

(srs_cap.cap_ayrc = '10/11') AND (srs_cap.cap_stuc = ?) AND (srs_cap.cap_rsp2 = 'F') OR

(srs_cap.cap_ayrc = '10/11') AND (srs_cap.cap_stuc = ?) AND (srs_cap.cap_rsp3 = 'F')

 

The above works fine in the Query Builder but asks for three parameters but only requires the top one filling in with a value for srs_cap.cap_stuc. But it throws the above error when run within the program. It all worked fine too before our DBA rewrote the SQL for me to get some more complex results but has now gone on holiday! I think it has something to do with it not being fed values for 3 parameters but to my untrained SQL mind they are all the same colmun? So I'm not sure if the SQL can be modifed to only need one parameter or it's better to modify the VB code?

Thanks