Error: Parameter @Abstract has no default value

  • Thread starter Thread starter jeff
  • Start date Start date
J

jeff

when i use parameters to update a record (in access 2000),
and i replace a text field's value with an empty string
(by erasing the asp:textbox content and firing the
DataGrid's update command), my TRY block catches the
exception (Parameter @Abstract has no default value).

when i rewrote my code to refrain from using parameters i
received no error, and the update works flawlessly.

standard procedure would be to use parameters. how do i
get mine to work?

rem *****************************************************

MY ORIGINAL CODE (catches exception):

'Initialize variables:
Dim tb As TextBox
Dim sql As String = "UPDATE tblEvents " & _
"SET eveDate=@Date, eveTitle=@Title, " & _
"eveAbstract=@Abstract WHERE eveID=@intID;"
Dim cmd As New OleDBCommand( sql, con )

'Assign values to query parameters:
tb = e.Item.FindControl("txtDate")
cmd.Parameters.Add( "@Date", CDate(tb.Text) )
tb = e.Item.FindControl("txtTitle")
cmd.Parameters.Add( "@Title", Replace( tb.Text, _
"'", "''" ) )
tb = e.Item.FindControl("txtAbstract")
cmd.Parameters.Add( "@Abstract", Replace( tb.Text, _
"'", "''" ) )
cmd.Parameters.Add( "@intID", EventGrid.DataKeys( _
CInt(e.Item.ItemIndex)) )

'Update event:
Try
cmd.Connection.Open
cmd.ExecuteNonQuery
Catch
lblFeedBack.Text = "(UPDATE EVENT)Error: #" & _
Err.Number & "< br>Description: " & _
Err.Description & "< br>" & sql & "< br>"
lblFeedBack.Visible = True
Exit Sub
Finally
cmd.Connection.Close
End Try

rem *****************************************************

MY NEW CODE (no exceptions):

'Initialize variables:
Dim tb As TextBox, sql, strDate, strTitle As String
Dim strAbstract As String
Dim intID As Integer

'Assign field values to variables:
tb = e.Item.FindControl("txtDate")
strDate = tb.Text
tb = e.Item.FindControl("txtTitle")
strTitle = Replace( tb.Text, "'", "''" )
tb = e.Item.FindControl("txtAbstract")
strAbstract = Replace( tb.Text, "'", "''" )
intID = EventGrid.DataKeys(CInt(e.Item.ItemIndex))

sql = "UPDATE tblEvents SET eveDate=#" & strDate & _
"#, eveTitle='" & strTitle & _
"', eveAbstract='" & strAbstract & _
"' WHERE eveID=" & intID & ";"

Dim cmd As New OleDBCommand( sql, con )
cmd.CommandType = CommandType.Text

.....
 
Hi, this is probably useless, since your post is from december '03 so
you probably already fixed it, but anyway, I had the same problem
today and found a solution, and maybe someone will have the same
problem and so here's my 2cents on it:

The problem you mention is when inserting/updating records in MS
Access, using a command and using parameters.

Like you said, if you refrain from using parameters it works. Also if
you execute the exactly same command in a SQL Server it works.

It seems that the problem is with the null parameters. My Access table
has about 6 columns, all of them accept nulls except one (the ID
column), and then when inserting/updating and using a null variable
for the parameter value, it just doesn't like it and tries to use a
'default value' that in my case doesn't exist.

The solution I found is to use the DBNull class for those null
parameters.

So if your variable is null, don't use it for the parameter value, use
DBNull.Value instead.

For example, when you do something like this:

dbCommand.Parameters.Add("Name", OleDbType.VarChar);
dbCommand.Parameters["Name"].Direction = ParameterDirection.Input;
dbCommand.Parameters["Name"].Value = sName;

you should set the value with something like this:

dbCommand.Parameters["Name"].Value = ((sName != null) ? sName :
DBNull.Value);


crusher.-
 
Hi, this is probably useless, since your post is from december '03 so
you probably already fixed it, but anyway, I had the same problem
today and found a solution, and maybe someone will have the same
problem and so here's my 2cents on it:

The problem you mention is when inserting/updating records in MS
Access, using a command and using parameters.

Like you said, if you refrain from using parameters it works. Also if
you execute the exactly same command in a SQL Server it works.

It seems that the problem is with the null parameters. My Access table
has about 6 columns, all of them accept nulls except one (the ID
column), and then when inserting/updating and using a null variable
for the parameter value, it just doesn't like it and tries to use a
'default value' that in my case doesn't exist.

The solution I found is to use the DBNull class for those null
parameters.

So if your variable is null, don't use it for the parameter value, use
DBNull.Value instead.

For example, when you do something like this:

dbCommand.Parameters.Add("Name", OleDbType.VarChar);
dbCommand.Parameters["Name"].Direction = ParameterDirection.Input;
dbCommand.Parameters["Name"].Value = sName;

you should set the value with something like this:

dbCommand.Parameters["Name"].Value = ((sName != null) ? sName :
DBNull.Value);


crusher.-
 
Back
Top