Member Avatar for hell_tej

Hi,

I m inserting Record in Access using VB6 SQL statement as follows

Dim str As String
On Error GoTo solve
str = "INSERT INTO [compinfo]([cID],[cname],[address],[add2],[city],[Postno],[mob],[phno],[faxno],[email],[workday],[offtime],[saldate],[duedate],[amount],[paytyp],[web],[type],[expdat],[charge]) values (" & "'" & txtcomid.Text & "'" & _
            "," & "'" & txtcomnam.Text & "'" & "," & "'" & txtcomadd.Text & "'" & "," & "'" & txtcomadd2.Text & "'" & _
            "," & "'" & txtcity.Text & "'" & _
            "," & txtposton.Text & _
            "," & "'" & txtmob.Text & "'" & _
            "," & "'" & txtphone.Text & "'" & _
            "," & txtfax.Text & _
            "," & "'" & txtemail.Text & "'" & "," & "'" & Comwday.Text & "'" & _
            "," & "'" & txtofftim.Text & "'" & _
            "," & Comsaldat.Text & _
            "," & "'" & DTPpay.Value & "'" & _
            "," & Txtamount.Text & _
            "," & "'" & Compaytyp.Text & "'" & _
            "," & "'" & txtwebadd.Text & "'" & _
            "," & "'" & Comtype.Text & "'" & _
            "," & "#" & expdat.Value & "#" & _
            "," & "'" & Comchar.Text & "'" & ")"
    
            
con.Execute str
solve:
If Err.Number = -2147217900 Then
MsgBox "Enter All the Fields...", vbCritical
On Error GoTo solve
Else
MsgBox "Your Record Has Been Addedd Successfully . . . ", vbInformation
End If

But this statement does not allows me Leave Blank Text boxes means
it does not allows me to insert NULL value
if i leave any textbox BLANK it generate ERROR -2147217900:(

HOW to solve this Problem, In my project it is not necessary to insert all records.
when we leave blank Text box it genrate Error:'(

So PLEASE send me code or IDEA to solve this problem
Thanks for Reading My Question

  • 3 Contributors
  • 2 Replies
  • 266 Views
  • 22 Hours Discussion Span
  • Latest Post Latest Post by debasisdas

Dani AI

Generated

As suggested, the table design is the first thing to check: a field must allow NULLs (Required = No) for a NULL to be stored, and text fields also have an "Allow Zero Length" setting that controls whether an empty string ("") is permitted instead of NULL. 's prompt about constraints is also relevant: validation rules, Required, and unique/index constraints will cause INSERTs to fail even if the SQL is syntactically correct.

The root cause in this thread is the unconditional string-building of the INSERT: wrapping every control in quotes or date delimiters produces invalid SQL or type mismatches when a control is blank. Numeric fields cannot receive quoted empty strings, date fields use #date# literals in Access, and the SQL NULL literal must be used without quotes or delimiters. Two reliable fixes are (a) stop concatenating raw values and use a recordset or parameterized command, or (b) when building SQL, explicitly emit the token NULL for empty values.

Example using an ADODB.Recordset (robust and simple for VB6 + Access):

Dim rs As New ADODB.Recordset
rs.Open "compinfo", con, adOpenKeyset, adLockOptimistic, adCmdTable
rs.AddNew
If Len(Trim(txtcomid.Text)) > 0 Then rs("cID") = txtcomid.Text Else rs("cID") = Null
If Len(Trim(txtcomnam.Text)) > 0 Then rs("cname") = txtcomnam.Text Else rs("cname") = Null
If IsNumeric(Trim(txtposton.Text)) Then rs("Postno") = CLng(txtposton.Text) Else rs("Postno") = Null
If Len(Trim(expdat.Text)) > 0 And IsDate(expdat.Text) Then rs("expdat") = CDate(expdat.Text) Else rs("expdat") = Null
rs.Update
rs.Close

If continuing with SQL string building, centralize value formatting so blanks become the NULL literal and strings are properly escaped:

Function SqlVal(s As String, isText As Boolean) As String
  If Len(Trim(s)) = 0 Then
    SqlVal = "NULL"
  Else
    If isText Then SqlVal = "'" & Replace(s, "'", "''") & "'" Else SqlVal = s
  End If
End Function

Final notes: prefer parameterized commands to avoid SQL injection and datatype headaches; verify each Access field's Required/Allow Zero Length/Validation Rule; and format Access date literals with #mm/dd/yyyy# only when an actual date is provided — otherwise use NULL.

Recommended Answers

All 2 Replies

Member Avatar for khess

Look in your Access database. Check the attributes of the fields for the columns...check allow NULL values. This should solve it. Then you can insert a NULL value easily.

Member Avatar for debasisdas

what happens if you insert NULL to the field ?

is there any constraints on the database table fields.

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.