PLEASE HELP ME !!! I HAD NULL SUCSESS

[Corne]

I have the following querry where i want to save my values from my textboxes back into my database:

'Declare our variables
Dim dbProgram As New ADODB.Connection
Dim rsMain As New ADODB.Recordset

'Open our connection
dbProgram.Open "dsn=Program"

'build the sql update query
sql = "update Main set " & _
"Job_No = '" & txtJob.Text & "', " & _
"Cust = '" & txtCust.Text & "', " & _
"Site = '" & txtSite.Text & "', " & _
"Order_No = '" & txtOrder.Text & "', " & _
"Model = '" & txtModel.Text & "', " & _
"Prefix = '" & txtPrefix.Text & "', " & _
"Serial = '" & txtSerial.Text & "', " & _
"SMR = '" & nonulls(txtSMR.Text) & "', " & _
"DateOpen = '" & txtDateOpen.Text & "', " & _
"DateClosed = '" & txtDateClosed.Text & "', " & _
"DateStart = '" & txtDateStart.Text & "' , " & _
"DateEnd = '" & txtDateEnd.Text & "' , " & _
"Status = '" & chkStatus.Value & "' , " & _
"Remarks = '" & txtRemarks.Text & "'where idJob = " & _
intCurrJob

'execute the query
dbProgram.Execute sql

It work fine but if i have empty textboxes i get an error.(Type mismatch)

My ACCESS database has the following data types:
Text >(strings) OK :) I used allow zero lenght
Number & Date/Time > Problem :( when saving empty values into database i get a type mismatch error.

If you see my querry above at the text box SMR i use the following function:

Function nonulls(s As Variant) As String

If IsNull(s) Or Len(s) = 0 Then
nonulls = ""
Else
nonulls = s
End If

End Function

It does not want to work ,but if i want to replace an empty string with a zero it does work.
ps: I want to keep my textboxes empty and I am using a masked edit control for my dates.

So can anybody help me!! Is there a way to save empty textboxes(integers or longs and dates) into an database. :(

Thanks !!

[Maartin]

As what type is the date fields in the database desinged ? If it is date/time then do not try to save the date as a string.

e.g.
"DateOpen = " & txtDateOpen.Text & ", " & _
"DateClosed = " & txtDateClosed.Text & ", " & _
"DateStart = " & txtDateStart.Text & " , " & _
"DateEnd = " & txtDateEnd.Text & " , "

By using the ' or "" as part of the SQL string that you send to the database indicates to the database that this is a text field but in the database itself it is a date field.


----------------------
Maartin
dinamite@onwe.co.za
-----------------------

P.S format the date fields to the format that the database is expecting.


[This message has been edited by Maartin (edited 01-19-2000).]

[Corne]

Is there a way to save empty textboxes(integers and dates) back into an access database.I want to keep my textboxes empty.
Please check my above post.

Thanks for all your help ! :) :)

[FirstKnight]

Have you tried to use a error handler? Here is your same code with some error trapping, wich may help.

Code:

'Declare our variables
Dim dbProgram As New ADODB.Connection
Dim rsMain As New ADODB.Recordset

'Open our connection
dbProgram.Open "dsn=Program"

'Everything after this line will be checked for errors
On Error Goto ErrorHandler

'build the sql update query
sql = "update Main set " & _
"Job_No = '" & txtJob.Text & "', " & _
"Cust = '" & txtCust.Text & "', " & _
"Site = '" & txtSite.Text & "', " & _
"Order_No = '" & txtOrder.Text & "', " & _
"Model = '" & txtModel.Text & "', " & _
"Prefix = '" & txtPrefix.Text & "', " & _
"Serial = '" & txtSerial.Text & "', " & _
"SMR = '" & nonulls(txtSMR.Text) & "', " & _
"DateOpen = '" & txtDateOpen.Text & "', " & _
"DateClosed = '" & txtDateClosed.Text & "', " & _
"DateStart = '" & txtDateStart.Text & "' , " & _
"DateEnd = '" & txtDateEnd.Text & "' , " & _
"Status = '" & chkStatus.Value & "' , " & _
"Remarks = '" & txtRemarks.Text & "'where idJob = " & _
intCurrJob

'Cancel Error Handling
On Error Goto 0

'execute the query
dbProgram.Execute sql

'Exit the sub to avoid the Error Handler
Exit Sub

'Here is the actual Error Handler
ErrorHandler:
'Check to see if it is a type mismatch error
If Err.Number = 13 Then
'If it is ignore it and carry on
Resume Next
Else
'If not pop up a Message describing the error
MsgBox "Error " & Cstr(Err.Number) & " - " _
& Err.Description
End if

I hope this helps.

[c@lle]

Maybe just try to drop the "field=''" part out of your sql-string. If you update your database and provide only the fields you want to fill in, the database leaves the other fields empty (if this is allowed!)
So if your textbox is empty, don't include the field in your sql-string.
HTH

[Clunietp]

you can also use the VAL function on a textbox -- it will return a 0 if the textbox is empty. This would be perfect for your numeric field items.

Tom

[brownga]

I have a similar question. I'm trying to update a Date\Time field in an Access db. The field currently has a valid date in it, but i want to clear that out when I update. I can't just omit updating this field if the new date is blank because I need to overwrite the existing date with Null or something. I tried saving
""
and
Null

but as the thread says, I had null success with this.
Any ideas?

Thanks,
Greg