Empty fields in the form

[Guest_imported]

hi ,
I have created a data entry form in visual basic....
And from there i am inserting the data into the database table through
insert into ** ** values ( here the fields.txt is given )

Now if the data entry operator leaves any fields empty this does not work since it requires value for every field written in the insert statement..
Now the task is to check whether the field is empty or not and then suitably calling the insert statement..
I have been doing it like this
f Len(txtaddress.Text) = 0 Then
txtaddress.Text = " "
End If
If Len(txtcity.Text) = 0 Then
txtcity.Text = " "
End If
If Len(txtphonenumber.Text) = 0 Then
txtphonenumber.Text = "0"
End If
If Len(txtemail.Text) = 0 Then
txtemail.Text = " "
End If
If Len(txtoccupation.Text) = 0 Then
txtoccupation.Text = " "
End If
If Len(txtrefby.Text) = 0 Then
txtrefby.Text = " "
End If

i.e i check the length of the field and if it is 0 then i put a space in it and the simple one line insert into statement works...
I know that this is an inefficient approach.. Can anybody tell me a better method... for this..

 

[ElusiveCoder]

Not sure exactly what you're up to, but have you considered creating an array of control references, and looping through it.

You might find the isnull() function of interest as well.

 

[WHM]

I do not think that you are too far off, as "exclusiveprogrammer" mentioned using control arrays would make your code a lot more compact, but not necisarily any more efficient in execution. You might also consider implementing the code into your Insert Functions; ie.:

Private Function Insert1(txtToInsert() as String, TableName as String, FieldNames() as string) as Boolean
dim Sql as string
dim items as string
dim cnt as integer
dim Valid as Boolean
Valid=False
Sql="INSERT INTO " & TableName & " ("

for cnt=0 to ubound(txtToInsert)
If len(trim(txtToInsert))=0 then

Else
if valid=false then
Sql=sql & FieldNames(cnt)
items= " " & txtToInsert(cnt)
Else
Sql= sql & "," & FieldNames(cnt)
items= items & ",'" & txtToInsert(cnt) & "' "
end if
Valid=True
end if
Next cnt
if valid=true then
sql=sql & &quot SELECT " & items & ";"
db.execute Sql
Insert1=True
End if
end function



You could of course go farther with this, but this should give you the idea.

Hunter