MS Access db: Text [@ParameterName1] has no default value

[RyanBurns]

I'm using ASP.NET with a Microsoft Access 2003 database. In trying to pass values to a parameterized query, I get the following error:

System.Data.Oledb.OledbException: Parameter [@ParameterName1] has no default value.

The underlying query looks something like this:

UPDATE TableName
SET field1=[@ParameterName1],
SET field2=[@ParameterName2]
WHERE id=[@ID]

Field1 is a text field. In this particular instance, I'm passing "" as the value for @ParameterName1. I've tried String.Empty, and "''" but it actually inserts two single quotes, not an empty string. Ideas? I'd prefer not inserting a space, or some other type of "rig.

 

[vbkris]

>>but it actually inserts two single quotes, not an empty string.

thats how an empty value is represented an access database.

can i have the code (from building the command object to execution)...

Known is handfull, Unknown is worldfull

 

[RyanBurns]

To be more precise, using '' inserts or updates to an empty value, but the '' itself does not reside in the database. It's just a zero length string. Which is fine in a regular SQL statement, such as:

UPDATE table
SET stringfield1 = ''
WHERE id=1

However, the difficulty is when passing values to parameters. Here is the function which does the work:

Public Sub Update(ByVal SomeVal As String)

Dim conn As OleDbConnection = New OleDbConnection(connString)
Dim cmd As OleDbCommand = New OleDbCommand("usp_Table_Update", conn)

With cmd
.CommandType = CommandType.StoredProcedure
With .Parameters
.Add(New OleDbParameter("@FieldName1", SomeVal))
End With
End With

conn.Open()
cmd.ExecuteNonQuery
End Sub

 

[vbkris]

did you try passing null parameters???

>>To be more precise, using '' inserts or updates to an empty value, but the '' itself does not reside in the database

confused...

Known is handfull, Unknown is worldfull

 

[RyanBurns]

Got it to work. I passed my string parameters through this function:

Public Function DBStringInput(ByVal Value As String) As String

Dim strValue As String
strValue = Replace(Value, "'", "''")

If strValue = "" Then
Return System.DBNull.Value.ToString
Else
Return strValue
End If

End Function

 

[vbkris]

gr8

Known is handfull, Unknown is worldfull