ADO ODBC Connection - Null Fields not allowed? 1

[SalemGrafix]

I have been trying to save a record that I want to set the value in the field to NULL, and have been unsuccessful.

I have tried setting the field (ie. txtAmount.text = Null) and saving the record only to get an error message "Run-time error 94: Invalid Use of Null". I have also tried VBNULL, VBNULLCHAR, and VBNULLSTRING. The field allows for Null values, and is an integer field. If the field is already set as null, it has no problem, but if it contains a value, that's when the problem arises.

VBNULL actually doesn't create an error, it sets the field to "1" as the value instead. I have also tried to set the text field to chr$(0) with no luck either.

Is there some type of workaround for this problem? I have read on other sites (doing a Google search) that .NET has a new variable "DBNULL" that can be used, but I'm using VB6 (for many reasons at the moment), and am looking for some type of solution in VB6.

Thanks for any help that can be provided.

 

[zemp]

Which database are you using?

zemp

 

[gmmastros]

Are you building your SQL String in code?

sql = "Insert into Table(Field1,Field) Values(" & txtBox1.text & "," & txtBox2.text & ")"

if so...

sql = "Insert into Table(Field1,Field) Values(" & IIF(txtBox1.text = "", "NULL", txtBox1.Text) & "," & txtBox2.text & ")"

Basically, you want your query to look like...

Insert Into Table(Field) Values(NULL)

Or, for updating...

Update Table
Set Field=NULL
Where IdField = 10



-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SalemGrafix]

MS-SQL 2000, sorry should have put that in there (since I already read on other sites that Access does not allow the usage of null values).

Also, as an addition to the error that I described above:

If I use "" or vbNullString, I get the error:

Run-time error '-2147217842 (80040e4e)': Operation was canceled.'

Thanks again for anyone who can shed some light on this.

 

[gmmastros]

Show us some code and we may be able to help better.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SalemGrafix]

I'm actually using the built in ADO control (MSADO) and using text boxes with the datasource set to the ADO control.

That brings up an interesting idea though, I might be able to populate the field with a number that would never be used (ie. a number out of range that a user would never use for this particular field), and then after the record has been saved/updated, go back and issue "Update Table..." directly, and change the field to null.

Of course, that would lead into my next question. I'm familiar with running SQL Queries (Select, Insert into, Updates, etc.) thru Query Analyzer, but how do you invoke that using VB? Do I use the Jet Access control, or is there an API call that I have to setup for that (or some other way completely unrelated)? Thanks.

 

[SalemGrafix]

The following is a snipet of the code that I'm using to save the record to the database:

frmODBCMain.txtSVTViolationCode.Text = Trim(txtCode.Text)
frmODBCMain.txtSVTViolationDescription.Text = Trim(txtDescription.Text)
If Trim(txtAmount.Text) <> "" Then
frmODBCMain.txtSVTViolationAmount.Text = Format(txtAmount.Text, "##,##0.00")
Else
''''' The following line is where it is causing the problem, setting the field as NULL.
frmODBCMain.txtSVTViolationAmount.Text = vbNullChar
End If
frmODBCMain.adoViolationTypes.Recordset.MoveFirst
frmODBCMain.adoViolationTypes.Refresh

 

[gmmastros]

There are various reasons why I never use bound controls. I hate losing flexibility. Of course, you end up writing more code, but... more code that you have control over. There are some threads around here that talk about bound controls.

Anyway...

Click Project -> References
Add a refernce to Microsoft ActiveX Data Objects 2.x Library

Then, in code, you create a connection object.

Public Sub ExecuteSQL(byval SQL As String)
Dim DB as ADODB.Connection

Set DB = CreateObject("ADODB.Connection")
DB.ConnectionString = "Your Connection String Here"
DB.Open
Call DB.Execute(SQL)
DB.Close
Set DB = Nothing
End Sub

To get data...

Public Function GetRecordset(ByVal SQL As String) As ADODB.Recordset
Dim DB As ADODB.Connection
Dim RS As ADODB.Recordset

Set DB = CreateObject("ADODB.Connection")
DB.ConnectionString = "Your Connection String Here" DB.CursorLocation = adUseClient
DB.CommandTimeout = 0
DB.Open

Set RS = CreateObject("ADODB.Recordset")
Set RS.ActiveConnection = DB
RS.CursorLocation = adUseClient
Call RS.Open(SQL, DB, adOpenStatic, adLockBatchOptimistic)
Set RS.ActiveConnection = Nothing
Set GetRecordset = RS
Set RS = Nothing
DB.Close
Set DB = Nothing
End Function

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom

 

[SalemGrafix]

Good point about the bound controls.

Up until your example, I was wondering how to execute direct SQL commands (such as running Stored Procedure or Direct Queries without bound controls).

Thanks so much for the help, and for the time being (to get this project out) I'll stick with the bound controls and make the modification after it's updated, but as I get some more time, I'll switch them out from the bound controls.

Thanks again for all your help, it's greatly appreciated.

 

[SalemGrafix]

Just tested it out, it works great. I was able to update the record by placing a 0 in the value, and then updating the record using the code you provided with the NULL value afterwards.

Thanks again for all your help. I'll be switching out the rest of the bound controls as time permits, but this definitely was a great work around for the time being.

 

[gmmastros]

Glad to help.

By the way, you use stored procedured in a simlar way. If the stored procedure does not return values, use the execute method. If the SP returns a recordset, use the GetRecordset function.

-George

Strong and bitter words indicate a weak cause. - Fortune cookie wisdom