NULL leaves an blank space

[rwn]

I'm using this sql statement to set a field to NULL if "N" exists in the Lab Table.Hold field. However, when this sql statement runs it appears a space is being set in the field vs having the field be completely NULL. SImiliar to prssing the space button on the key board is what is occurring.


sSQL = "UPDATE [Lab Table] SET [Lab Table].Hold= NULL WHERE [Lab Table].Hold='N';"

 

[RiverGuy]

Where are you showing that it's updating the column with a space? Are you looking at the .CommandText property in debug, or in the database? The statement seems fine to me, so you might want to check and see if your .CommandText property is not being set properly based on your string.

 

[rwn]

I see the space in the access program database table for this field of the Lab Table.
How should I go about seeing the the .CommandText property is correct or not? The following is the Command.

With objCommand
.Connection = oOleDbConnection
.CommandText =sSQL
.CommandType = CommandType.Text
.ExecuteNonQuery()
End With

 

[RiverGuy]

Set a break point on .ExecuteNonQuery(), and hover your mouse over .CommandText when the break point is hit.

 

[rwn]

Enum CommandType As Integer is what is shown.

 

[RiverGuy]

With objCommand
.Connection = oOleDbConnection
[b].CommandText[/b] =sSQL
.CommandType = CommandType.Text
.ExecuteNonQuery()
End With

Hover it over the part in bold.

 

[rwn]

The breakpoint at .ExecuteNonQuery() has a ? with a message of: The breakpoint will not currently be hit. No symbols have been loaded for this document.

When running the program the CommandType.Text is never hilighted in BOLD.

Sorry about be a newbie, I do appreciatte the quidence.

 

[rwn]

I do see the message: Override NotOVerridable Function ExecuteNonQuery() As Interger: is hilighted in yellow.

 

[RiverGuy]

There are a few different ways in which you can get the current value of a variable while debugging including looking at it when it hits a break point, stepping through the code, using debug.write, using the watch window, etc.

What you are looking for is the value of the .CommandText property before the query is executed. It can be assured that it is being set to your sSQL string. Whether or not the sSQL string and therefore the .CommandText property contain your actual literal string is the first place to start. Use the IDE to help you investigate what the value is.

When I highlighted the text in bold, I didn't mean that it will become bold--I meant that this is the part you can hover your mouse cursor over to see the current value.

 

[earthandfire]

What's wrong with a simple MessageBox?

either:

With objCommand
.Connection = oOleDbConnection
.CommandText =sSQL
MessageBox.Show(.CommandText) 
.CommandType = CommandType.Text
.ExecuteNonQuery()
End With

or:

MessageBox.Show(sSQL) 
With objCommand
.Connection = oOleDbConnection
.CommandText =sSQL
.CommandType = CommandType.Text
.ExecuteNonQuery()
End With

or even both!!

 

[RiverGuy]

MessageBox works too...provided it a WinForms app...or Response.Write if an ASP.Net app.

 

[earthandfire]

RiverGuy, good point.

I never do any Web development - I keep well clear of it!! As such, I would never have thought of suggesting Response.Write.

 

[rwn]

Thanks. It is a vb.net app and I will try both ideas. Thanks. I will let you now what occurs.