Help - fields not updating

  • Thread starter Thread starter Newbie
  • Start date Start date
N

Newbie

Hi,
I have a SQL 2000 database that I am populating from a form in Access XP
I am using an ADO command to insert a record into the table.
The table is being populated except for two fields - what am I doing wrong.

the two fields that are not being updated are
Table def:
Passed = bit
OpSeq = smallint

Form
OpSeq =textbox - I have tried as both number and with no formatting
chkpassed = checkbox to return True/False

here is a snippet of the code

cmdFirstOff.Parameters.Append cmdFirstOff.CreateParameter("@OpSeq",
adVarChar, adParamInput, me.OpNo)
cmdFirstOff.Parameters.Append
cmdFirstOff.CreateParameter("@Comments", adVarChar, adParamInput, 300,
Me.txtComments)
cmdFirstOff.Parameters.Append cmdFirstOff.CreateParameter("@PartNo",
adChar, adParamInput, 30, Me.PartNo)
cmdFirstOff.Parameters.Append
cmdFirstOff.CreateParameter("@Customer", adChar, adParamInput, 8,
Me.CustomerID)
cmdFirstOff.Parameters.Append cmdFirstOff.CreateParameter("@Passed",
adVariant, adParamInput, me.chkpassed)

I have tried @OpSeq as a adInteger and I have tried chkpassed as an
adBoolean but nothing seems to work


Any ideas where I am going wrong?

Thanks
 
New:

You probably have to use a data conversion function when passing the
parameter as in; access text boxes really return string values; for the bit
field SQL is a 0 / 1 combination where Access controls are 0 / -1, so using
ABS() should solve that:

cmdFirstOff.Parameters.Append cmdFirstOff.CreateParameter("@OpSeq",
adSmallInt, adParamInput, CInt(me.OpNo))

cmdFirstOff.Parameters.Append cmdFirstOff.CreateParameter("@Passed",
adBoolean, adParamInput, ABS(CBool(me.chkpassed))
 
Thanks for that but it still doesn't work :-(

Even though when I hover over the CInt(me.OpNo) and the
ABS(CBool(me.chkpassed)) variables they return a value it still tries to
insert NULL into the table

Any other ideas?

Thanks again
Al
 
In the following line, you are declaring the parameter as adVarChar and have not included the
length so it is interpreting me.OpNo as the length and the value is NULL. Below I have inserted
a '1' for the length:

cmdFirstOff.Parameters.Append cmdFirstOff.CreateParameter("@OpSeq", _
adVarChar, adParamInput, 1,me.OpNo)

The other parameter has a similar problem.

I Hope this helps.
 
Stupid mistake - thanks for spotting it

Al


Al Reid said:
In the following line, you are declaring the parameter as adVarChar and have not included the
length so it is interpreting me.OpNo as the length and the value is NULL. Below I have inserted
a '1' for the length:

cmdFirstOff.Parameters.Append cmdFirstOff.CreateParameter("@OpSeq", _
adVarChar, adParamInput, 1,me.OpNo)

The other parameter has a similar problem.

I Hope this helps.

--
Al Reid

"It ain't what you don't know that gets you into trouble. It's what you know
for sure that just ain't so." --- Mark Twain
Click to expand...
 
Back
Top