Update DateTime into sql db throws

Question

I am trying to update a DateTime object into a datetime field of a sql (SQL Server 2000) database.

I have the following function

Public Sub Update(ByVal lastlogin as DateTime)

Using slqupdate as SqlCommand = _connection.CreateCommand()
  sqlupdate.CommandType = CommandType.Text
  sqlupdate.CommandText = "UPDATE myTable SET LastLogin = @lastlogin WHERE ID = 2"

  updatelastlogin = sqlupdate.CreateParameter()
  updatelastlogin.ParameterName = "@lastlogin"
  updatelastlogin.DbType = SqlDbType.DateTime
  updatelastlogin.Value = lastlogin
  slqlupdate.Parameters.Add(updatelastlogin)

  sqlupdate.ExecuteNonQuery()    
End Using
End Sub

Attempting to call it as follows Update(DateTime.Now) produces the following Exception:

Failed to convert parameter value from a DateTime to a Decimal.

I am not sure what I've done wrong, does anybody know?

Michael J Swart · Accepted Answer · 2011-05-31 14:18:26Z

2

Try SQLDBType instead of DBType

answered May 31, 2011 at 14:18

Michael J Swart

3,2093 gold badges32 silver badges47 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

Michael J Swart Over a year ago

No problem. I found the problem quickly when I ported your sample to c#.

Tony The Lion · Accepted Answer · 2011-05-31 14:18:47Z

I found this on the Microsoft MSDN:

Private Sub UpdateDemographics(ByVal customerID As Integer, _
    ByVal demoXml As String, _
    ByVal connectionString As String)

    ' Update the demographics for a store, which is stored 
    ' in an xml column.
    Dim commandText As String = _
     "UPDATE Sales.Store SET Demographics = @demographics " _
     & "WHERE CustomerID = @ID;"

    Using connection As New SqlConnection(connectionString)
        Dim command As New SqlCommand(commandText, connection)

        ' Add CustomerID parameter for WHERE clause.
        command.Parameters.Add("@ID", SqlDbType.Int)
        command.Parameters("@ID").Value = customerID

        ' Use AddWithValue to assign Demographics.
        ' SQL Server will implicitly convert strings into XML.
        command.Parameters.AddWithValue("@demographics", demoXml)

        Try
            connection.Open()
            Dim rowsAffected As Integer = command.ExecuteNonQuery()
            Console.WriteLine("RowsAffected: {0}", rowsAffected)

        Catch ex As Exception
            Console.WriteLine(ex.Message)
        End Try
    End Using
End Sub

The "command.Parameters.Add("@ID", SqlDbType.Int)" part should be an option, stating that it is a datetime parameter?

Collectives™ on Stack Overflow

Update DateTime into sql db throws

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related