No value given for one or more required parameters

[acapulco]

I have the following error when i execute an sql insert statement. Kindly help me know what caused the error and how can i rectify.
[h=3]No value given for one or more required parameters[/h]The code with the sql statement is

sqlText = "INSERT INTO " & strDBTable & "(" _
& "LZ, HHNO, QNO, [DATE], TEAM, CLUSTER, " _
& "CLUSNAME, District, HHSIZETODAY, HHSIZELAST30, U5s, HHHSEX, CGMSTAT, CGLEDUC, " _
& "RESIDE, IDP6MTHS, IDPsHOSTED, HHNETS, NETSOURCE, " _
& "CFDFREQ, FREQMILK, " _
& "VITA, MEASVAC, POLIOVAC, IMMCARD, AGE, " _
& "SEX, OEDEMA, HEIGHT, WEIGHT, MUAC, " _
& "DIAR, PNEUMONIA, FEVER, MEASLES, SLEPTNET, " _
& "HFAC, SFP, ADULTAGE, TETVAC, ADULTMUAC, " _
& "PHYSIO, MOTMQNET, PROGMOTH, ADULTILL, ILLM_OTH, HFAC_MOT, " _
& "YNOHFAC_M, NOAS_OTH, ADULTAGE1, TETVAC1, ADULTMUAC1, " _
& "PHYSIO1, MOTMQNET1, PROGMOTH1, ADULTILL1, ILLM_OTH1, HFAC_MOT1, YNOHFAC_M1, NOAS_OTH1, ADULTAGE2, " _
& "TETVAC2, ADULTMUAC_2, PHYSIO2, MOTMQNET2, PROGMOTH2, " _
& "ADULTILL2, ILLM_OTH2, HFAC_MOT2, YNOHFAC_M2, NOAS_OTH2, BFAST, SNACK1, LUNCH, SNACK2, " _
& "DINNER, SNACK3, CEREAL_CH, MILK_CH, " _
& "MEATS_CH, EGGS_CH, LEGUMES_CH, " _
& "VitAFVs_CH, OtherFVs_CH, FGPS_CH, BFAST4, " _
& "SNACK1_4, LUNCH4, SNACK2_4, " _
& "DINNER4, SNACK3_4, CEREALS, LEGUMES, MILK, VIT_A_VEG, VEGES, OTHERVEG, FRUITS, OTHERFRT, " _
& "ORGANMEAT, MEAT, EGGS, FISH, OIL, ROOTS, SWEETS, COFFEE, FOODGRPS, EATOUT, MAINFDSO, " _
& "FOODAIDFREQ, WATERSO, WATERSO_Other, YNOSAFEWAT, " _
& "YNOWAT_Other, DAYSNOWAT, RESNOWAT, TIMETKEN, JERCAN20, " _
& "JERICAN5, DRUM, HAAN, OTHCONTAINER, OTHCONTAINER_S, TOTALLTS, " _
& "WATTREAT, BOILING, CHLORINE, FILTER, DECANT, " _
& "SUN, WATTREAT_Other, WATERBUY, TOILTYPE, YNOLAT, " _
& "SHARING, WASHAGNT, NO_Info, MASSMED, PRINTMED, " _
& "PSNLCOMM, GRPMEET, RECTIME" _
& DB_TYPE_FIELDS _
& ") VALUES(" _
& "@LZ, @HHNO, @QNO, @DATE, @TEAM, @CLUSTER, " _
& "@CLUSNAME, @District, @HHSIZETODAY, @HHSIZELAST30, @U5s, @HHHSEX, @CGMSTAT, @CGLEDUC, " _
& "@RESIDE, @IDP6MTHS, @IDPsHOSTED, @HHNETS, @NETSOURCE, " _
& "@CFDFREQ, @FREQMILK, " _
& "@VITA, @MEASVAC, @POLIOVAC, @IMMCARD, @AGE, " _
& "@SEX, @OEDEMA, @HEIGHT, @WEIGHT, @MUAC, " _
& "@DIAR, @PNEUMONIA, @FEVER, @MEASLES, @SLEPTNET, " _
& "@HFAC, @SFP, @ADULTAGE, @TETVAC, @ADULTMUAC, " _
& "@PHYSIO, @MOTMQNET, @PROGMOTH, @ADULTILL, @ILLM_OTH, @HFAC_MOT, " _
& "@YNOHFAC_M, @NOAS_OTH, @ADULTAGE1, @TETVAC1, @ADULTMUAC1, " _
& "@PHYSIO1, @MOTMQNET1, @PROGMOTH1, @ADULTILL1, @ILLM_OTH1, @HFAC_MOT1, @YNOHFAC_M1, @NOAS_OTH1, @ADULTAGE2, " _
& "@TETVAC2, @ADULTMUAC_2, @PHYSIO2, @MOTMQNET2, @PROGMOTH2, " _
& "@ADULTILL2, @ILLM_OTH2, @HFAC_MOT2, @YNOHFAC_M2, @NOAS_OTH2, @BFAST, @SNACK1, @LUNCH, @SNACK2, " _
& "@DINNER, @SNACK3, @CEREAL_CH, @MILK_CH, " _
& "@MEATS_CH, @EGGS_CH, @LEGUMES_CH, " _
& "@VitAFVs_CH, @OtherFVs_CH, @FGPS_CH, @BFAST4, " _
& "@SNACK1_4, @LUNCH4, @SNACK2_4, " _
& "@DINNER4, @SNACK3_4, @CEREALS, @LEGUMES, @MILK, @VIT_A_VEG, @VEGES, @OTHERVEG, @FRUITS, @OTHERFRT, " _
& "@ORGANMEAT, @MEAT, @EGGS, @FISH, @OIL, @ROOTS, @SWEETS, @COFFEE, @FOODGRPS, @EATOUT, @MAINFDSO, " _
& "@FOODAIDFREQ, @WATERSO, @WATERSO_Other, @YNOSAFEWAT, " _
& "@YNOWAT_Other, @DAYSNOWAT, @RESNOWAT, @TIMETKEN, @JERCAN20, " _
& "@JERICAN5, @DRUM, @HAAN, @OTHCONTAINER, @OTHCONTAINER_S, @TOTALLTS, " _
& "@WATTREAT, @BOILING, @CHLORINE, @FILTER, @DECANT, " _
& "@SUN, @WATTREAT_Other, @WATERBUY, @TOILTYPE, @YNOLAT, " _
& "@SHARING, @WASHAGNT, @NO_Info, @MASSMED, @PRINTMED, " _
& "@PSNLCOMM, @GRPMEET, @RECTIME" _
& DB_TYPE_VALUES _
& ")

 

[Menthos]

The error message is telling you exactly what the problem is... search through that 'painful' query and you'll find an error.

Doubt you'll find anyone willing to trawl through that query matching the fields and params - although you might want to check your DB_TYPE_FIELDS / VALUES vars.

 

[acapulco]

Thanks Methos

In my Access database table, there are only four required fields and in my SQL, the required fields are suplied with values.

after walking through the code, I realize that when I remove some fields and their parameters, the query succeeds.
What could these fields be bringing to the code, I checked the data types, they match

Regards.

 

[InertiaM]

It may sound a daft question, but you did actually pass the values for the parameters, didnt you? You havent included that part of the code in your post, so I'm just checking

 

[acapulco]

Hi Inertia, I run functions that insert text, number or date to command e.g InsertText(dbCmd, _form.GetFormControlByName("TEAM").Value, "@TEAM")
Hope that answers you, does it?

 

[cjard]

Just cheking.. please tell me that function does something like this:

InsertText(dbCmd as DbCommand, val as String, par as String)

  dbCmd.Parameters(par).Value = val

and NOT something like this:

InsertText(dbCmd as DbCommand, val as String, par as String)

  dbCmd.CommandText.Replace(par, val)

Things to check:
You really need to populate every parameter.. cant just say "insert into table(c1,c2) values(@v1,@v2)" and then only only do one parameters:
cmd.Parameters.AddWithValue("@v2", "v2 value")
cmd.ExecuteNonQuery

If youre using access, it doesnt use named parameters, ou have to add the params in hte same order as in the query.. the fac tu can see names is meaningless.. the driver converts them all to ? marks internally..

You cannot reuse parameter names (because there are no named params) so INSERT INTO person(first,last) VALUES(@p,@p), in access will still need 2 parameters of the same value and differnet names adding:
cmd.Parameters.AddWithValue("@p_anything", "same value")
cmd.Parameters.AddWithValue("@p_anything2", "same value")

Some column names in access can also be reserved words in access or .net and may cause problems (stuff like Date, Time, System etc)

When you provide a OleDbCommand object with a query, it may build a parameters collection for you.. If you AddWIthValue you may end up with more parameters than intended


Tbh, Youd do a lot better to jsut read the microsoft recommended tutorials for how to do your data access and do it Microsoft;s way.. See the DW3 link in my signature for more info