Update DateTime field in MS Access with ASP.NET

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

Other Forums

MS Access

Update DateTime field in MS Access with ASP.NET

Author

Topic

AskSQLTeam
Ask SQLTeam Question

0 Posts

Posted - 2005-04-21 : 08:08:10

Jeff writes "I'm using edit in place with ASP.NET (VB) and I'm having trouble updating a DateTime field in MS Access. I'm using the following code:

Sub dgProducts_Update(sender As Object, e As DataGridCommandEventArgs)

Dim iProductID as Integer = e.Item.Cells(1).Text
Dim dblPrice as Double = CType(e.Item.Cells(2).Controls(1), TextBox).Text
Dim strName as String = CType(e.Item.Cells(3).Controls(1), TextBox).Text
Dim strDesc as String = CType(e.Item.Cells(4).Controls(0), TextBox).Text
Dim saleDate as DateTime = Convert.ToDateTime(CType(e.Item.Cells(5).Controls(1), TextBox).Text)

trace.warn("saleDate: " & saleDate)

Dim strSQL as String = "UPDATE [Products] SET [ProductName] = @ProdName, [UnitPrice] = @UnitPrice, [ProductDescription] = @ProdDesc, [saleDate] = @saleDate WHERE [ProductID] = @ProductID"

Const strConnString as String = "Provider=Microsoft.Jet.OLEDB.4.0;" & _
"Data Source=C:\Documents and Settings\My Documents\websites\salesweb\test\testsample\GrocerToGo1.mdb"
Dim objConn as New OleDbConnection(strConnString)
objConn.Open()

Dim myCommand as OleDbCommand = new OleDbCommand(strSQL, objConn)
myCommand.CommandType = CommandType.Text

' Add Parameters to the SQL query
Dim parameterProdName as OleDbParameter = new OleDbParameter("@ProdName", OleDbType.VarWChar, 75)
parameterProdName.Value = strName
myCommand.Parameters.Add(parameterProdName)

Dim parameterUnitPrice as OleDbParameter = new OleDbParameter("@ProdName", OleDbType.Currency)
parameterUnitPrice.Value = dblPrice
myCommand.Parameters.Add(parameterUnitPrice)

Dim parameterProdDesc as OleDbParameter = new OleDbParameter("@ProdDesc", OleDbType.VarWChar)
parameterProdDesc.Value = strDesc
myCommand.Parameters.Add(parameterProdDesc)

Dim parameterProdID as OleDbParameter = new OleDbParameter("@ProductID", DbType.Int32)
parameterProdID.Value = iProductID
myCommand.Parameters.Add(parameterProdID)

Dim dbParam_saleDate As System.Data.IDataParameter = New System.Data.OleDb.OleDbParameter
dbParam_saleDate.ParameterName = "@saleDate"
dbParam_saleDate.Value = saleDate
dbParam_saleDate.DbType = System.Data.DbType.DateTime
myCommand.Parameters.Add(dbParam_saleDate)

myCommand.ExecuteNonQuery()

objConn.Close()

dgProducts.EditItemIndex = -1
BindData()
End Sub

DataGrid Code:
<asp:DataGrid id="dgProducts" runat="server" OnCancelCommand="dgProducts_Cancel" OnUpdateCommand="dgProducts_Update" OnEditCommand="dgProducts_Edit" EditItemStyle-BackColor="#eeeeee" HeaderStyle-Font-Bold="True" HeaderStyle-HorizontalAlign="Center" HeaderStyle-ForeColor="White" HeaderStyle-BackColor="Black" CellPadding="4" AutoGenerateColumns="False">
<Columns>
<asp:EditCommandColumn EditText="Edit Info" ButtonType="PushButton" UpdateText="Update" CancelText="Cancel" />
<asp:BoundColumn HeaderText="Product ID" DataField="ProductID" ReadOnly="True" />
<asp:TemplateColumn HeaderText="UnitPrice">
<ItemTemplate>
<asp:Label text='# (DataBinder.Eval(Container.DataItem, "UnitPrice")) ' runat="server" id="UnitPrice" width="50" />
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox Runat="server" ID="UnitPrice" Text= '# (DataBinder.Eval(Container.DataItem, "UnitPrice", "{0:$#,###.##}")) ' Width="50" />

bferriter
Starting Member

34 Posts

Posted - 2005-04-21 : 13:53:18

what is the error? (if you included it I cant find it)

what is your column constraints for [saledate]? i have had to use format() on datetime fields to specify the datetime format for it to update properly.

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources