inserting blanks or nulls via VB6 ADODB

[maverickmonster]

When inserting null's or blank into a table via VB6 front end into access using ADODB the vb code bombs out and says

field'ad.name cannot be a zero length string

I have this problem on inserting blank or null into dates, numbers and the above mentioned strings fields in databases.

Is this a VB6 issue or a access issue ?

The code below is the inserting into the table.

Which = "INSERT INTO ad VALUES (  '" & maxnum & "','" & ad & "','" & txtBPdmember_key & "','" & txtAD(0) & "','" & txtAD(1) & "','" & txtAD(2) & "')"

Set RSad = New ADODB.Recordset
RSad.Open Which, cn, adOpenKeyset, adLockPessimistic

 

[petermeachem]

Dates and numbers should be ok, strings can be set (in Access) to allow zero length or not.
You don't need a recordset, just
cn.execute which

Also, better really to name the fields

which = "INSERT INTO ad (DayOfMonth,RouteNumber,RefValue) VALUES

or whatever

http://www.accuflight.com

http://www.lettersdirect.co.uk

http://www.ourhouseinportugal.co.uk/

 

[bjd4jc]

There is a property on all of your fields that is Allow Zero Length. This needs to be set to Yes.

To insert a blank date, you will need to use the NULL keyword on

INSERT INTO ad (DateField) VALUES (NULL)

 

[tazuk]

Are the fields you are attempting to insert nulls into set as nullable fields within Access? The error you are receiving suggests that they are not.

HTH

TazUk

Blue-screening PCs since 1998

 

[maverickmonster]

bjd4jc - I see this zero length field for text field in the database but not numerical fields.

If IsNumeric(txtAHD(0)) = False Then txtAHD(0) = Null
If IsNumeric(txtAHD(1)) = False Then txtAHD(1) = Null
If IsNumeric(txtAHD(2)) = False Then txtAHD(2) = Null
If txtAHD(3) = "" Then txtAHD(3) = Null
If IsDate(txtAHD(4)) = False Then txtAHD(4) = Null

I am using this error routine to make sure the inputs are ok and then using the which and insert from above to but the txtahd's into the table. When inserting Null in to date or numerical it comes up with a datatype mismatch.

 

[petermeachem]

If you are inserting NULLS, you don't want the single quotes.

So instead of
Which = ... ,'" & txtAD(2) & "' ..."

try
dim c as string
If not IsNumeric(txtAHD(2)) Then
c= Null
else
c= "'" & txtAHD(2) & "'"
endif

Which = ... ," & c & " ..."

Neater if made into a function.

http://www.accuflight.com

http://www.lettersdirect.co.uk

http://www.ourhouseinportugal.co.uk/

 

[maverickmonster]

Here is what i currently have :

If Not IsNumeric(txtAHD(0)) Then
 ahd0 = Null
Else
 ahd0 = "'" & txtAHD(0) & "'"
End If
    
If Not IsNumeric(txtAHD(1)) Then
 ahd1 = Null
Else
 ahd1 = "'" & txtAHD(1) & "'"
End If

If Not IsNumeric(txtAHD(2)) Then
 ahd2 = Null
Else
 ahd2 = "'" & txtAHD(2) & "'"
End If

If Not IsDate(txtAHD(4)) Then
 ahd4 = Null
Else
 ahd4 = "'" & txtAHD(4) & "'"
End If


Which = "INSERT INTO AhD VALUES (  '" & maxnum & "','" & ahd & "','" & txtBPdmember_key & "'," & ahd0 & "," & ahd1 & "," & ahd2 & ",'" & txtAHD(3) & "'," & ahd4 & " )"

If i leave the text blank "txtahd(3)" this is fine and will insert it . Although if numbers or date is blank there is a syntax error in INSERT into statement

 

[bjd4jc]

You will want the word NULL to appear in your concatenated SQL Statment (without quotes around it).

Setting a variable = Null won't do you any good.

>>I see this zero length field for text field in the database but not numerical fields

This is only on text fields. You will need to insert NULL as mentioned above.

 

[maverickmonster]

But how can i interchange this (from straight null in the insert and to the user input varible) depending on whether the user inputs are bad or not !

If the user wants to enter information in any of the fields this is fine it will be input and inserted , if they wish to leave it blank then nothing should insert into the table.

So how can i swap the user input for null's if needed

 

[petermeachem]

If not IsNumeric(txtAHD(2)) Then
c= " Null "
else
c= "'" & txtAHD(2) & "'"
endif

Which = ... ," & c & " ..."

Or alternatively, build Which selectively and skip blank inputs.

http://www.accuflight.com

http://www.lettersdirect.co.uk

http://www.ourhouseinportugal.co.uk/

 

[maverickmonster]

Thanks very much

I would have your children (Thats how happy i am )

Cheers

Have a beer on me

Maverick Monster