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
