Null value error
-
Hi, I am getting this error when I try to add null to the value. Operator is not valid for type DBNull and type Date. Not sure how to fix this issue. I tried everything I could, but with no luck. Any help will be appreciated. Thanks This is the code I have so far, which is causing the error.
comm.CommandText = "Update testbackup set datein = (null) & timein = (null) where barcodeid = '" & DgDisplay.Item(DgDisplay.CurrentRowIndex, 0).ToString & "'" comm.ExecuteNonQuery()I'm not sure there's a way to specify DBNull in a string, perhaps you need to write a stored proc ? Christian Graus - Microsoft MVP - C++ Metal Musings - Rex and my new metal blog
-
I'm not sure there's a way to specify DBNull in a string, perhaps you need to write a stored proc ? Christian Graus - Microsoft MVP - C++ Metal Musings - Rex and my new metal blog
-
What excatly would I write. Would you be able to help me with that like get me started on the right track. Appreciate it. Thanks
Assuming you're using SQL Server, something like: CREATE PROC ClearBackup @id nvarchar(20) as Update testbackup set datein = null & timein = null where barcodeid = @id go will create the proc. Change nvarchar(20) to whatever type your ID is ( why is it not a number ? ) Then you can execute it from code, and pass in the ID. Christian Graus - Microsoft MVP - C++ Metal Musings - Rex and my new metal blog
-
Hi, I am getting this error when I try to add null to the value. Operator is not valid for type DBNull and type Date. Not sure how to fix this issue. I tried everything I could, but with no luck. Any help will be appreciated. Thanks This is the code I have so far, which is causing the error.
comm.CommandText = "Update testbackup set datein = (null) & timein = (null) where barcodeid = '" & DgDisplay.Item(DgDisplay.CurrentRowIndex, 0).ToString & "'" comm.ExecuteNonQuery()You should be using parameters, if for no other reason than it protects you from a SQL Injection Attack. You shouldn't be injecting text into a SQL string, expecially one directly from a control. Also, you seem to have an error in your SQL string itself. Maybe something like this would work:
comm.CommandText = "Update testbackup set datein = null, timein = null where barcodeid = @barcodeId"
If DgDisplay.CurrentRowIndex < 0 Then
comm.Parameters.Add("@barcodeId", DBNull.Value)
Else
comm.Parameters.Add("@barcodeId", DgDisplay.Item(DgDisplay.CurrentRowIndex, 0).ToString)
End If
Scottish Developers events: * .NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy * Developer Day Scotland: are you interested in speaking or attending? My: Website | Blog
-
Assuming you're using SQL Server, something like: CREATE PROC ClearBackup @id nvarchar(20) as Update testbackup set datein = null & timein = null where barcodeid = @id go will create the proc. Change nvarchar(20) to whatever type your ID is ( why is it not a number ? ) Then you can execute it from code, and pass in the ID. Christian Graus - Microsoft MVP - C++ Metal Musings - Rex and my new metal blog
-
You should be using parameters, if for no other reason than it protects you from a SQL Injection Attack. You shouldn't be injecting text into a SQL string, expecially one directly from a control. Also, you seem to have an error in your SQL string itself. Maybe something like this would work:
comm.CommandText = "Update testbackup set datein = null, timein = null where barcodeid = @barcodeId"
If DgDisplay.CurrentRowIndex < 0 Then
comm.Parameters.Add("@barcodeId", DBNull.Value)
Else
comm.Parameters.Add("@barcodeId", DgDisplay.Item(DgDisplay.CurrentRowIndex, 0).ToString)
End If
Scottish Developers events: * .NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy * Developer Day Scotland: are you interested in speaking or attending? My: Website | Blog
-
I am using access as my back end for database. I am not using SQL server. Sorry for not letting you know before. Does that change now and how
Access allows stored procs nowadays, I believe, but I don't know the exact way to create them. Christian Graus - Microsoft MVP - C++ Metal Musings - Rex and my new metal blog
-
Access allows stored procs nowadays, I believe, but I don't know the exact way to create them. Christian Graus - Microsoft MVP - C++ Metal Musings - Rex and my new metal blog
-
Sorry about not writing in my first post that I am using access as my database. I am not using SQL server. How will the code change and also I am using vb.net 2003 version. Thanks
Maybe something like this would work: Then this:
comm.CommandText = "Update testbackup set datein = null, timein = null where barcodeid = ?"
If DgDisplay.CurrentRowIndex < 0 Then
comm.Parameters.Add("", DBNull.Value)
Else
' NOTE: If barcodeid is not a string in the database then this
' should be changed to the correct type
comm.Parameters.Add("", DgDisplay.Item(DgDisplay.CurrentRowIndex, 0).ToString)
End If
Scottish Developers events: * .NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy * Developer Day Scotland: are you interested in speaking or attending? My: Website | Blog
-
Maybe something like this would work: Then this:
comm.CommandText = "Update testbackup set datein = null, timein = null where barcodeid = ?"
If DgDisplay.CurrentRowIndex < 0 Then
comm.Parameters.Add("", DBNull.Value)
Else
' NOTE: If barcodeid is not a string in the database then this
' should be changed to the correct type
comm.Parameters.Add("", DgDisplay.Item(DgDisplay.CurrentRowIndex, 0).ToString)
End If
Scottish Developers events: * .NET debugging, tracing and instrumentation by Duncan Edwards Jones and Code Coverage in .NET by Craig Murphy * Developer Day Scotland: are you interested in speaking or attending? My: Website | Blog
I tried your suggestion and it still did not work. I have a feeling where I have put the code is in the wrong place. In the database barcodeid is a text field. In access database it is a text field. I will paste the code that I have for that button. Thanks for your help again.
Private Sub btnUpdateOSSF_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdateOSSF.Click Dim changes As Integer Dim commandBuilder As New OleDb.OleDbCommandBuilder(dataadapter) Dim i As String Dim str As String Dim comm As New OleDbCommand OleDbConnection1.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;" & _ "Data Source=" & Application.StartupPath & "\backup.mdb;" comm.Connection = OleDbConnection1 Me.BindingContext(dt).EndCurrentEdit() Dim strWrk As String = dt.Rows(0).Item("DateIN") = DateTimePicker1.Value Dim strWrk1 As String = dt.Rows(0).Item("TimeIN") = DateTimePicker2.Value changes = dataadapter.Update(dt) If changes > 0 Then MsgBox(changes & " changed rows were stored in the database.") Else MsgBox("No changes made.") End If Try OleDbConnection1.Open() 'Copy existing record from original table to history table. comm.CommandText = "INSERT INTO History (barcodeid, Dateout, timeout, location, comments) SELECT barcodeid, Dateout, timeout, location, comments FROM testbackup WHERE barcodeid='" & DgDisplay.Item(DgDisplay.CurrentRowIndex, 0).ToString & "'" comm.ExecuteNonQuery() 'Delete old history record. comm.CommandText = "DELETE FROM history WHERE (barcodeid = '" & DgDisplay.Item(DgDisplay.CurrentRowIndex, 0).ToString & "') AND (ID NOT IN (SELECT TOP 5 ID FROM history where barcodeid = '" & DgDisplay.Item(DgDisplay.CurrentRowIndex, 0).ToString & "' ORDER BY ID Desc))" comm.ExecuteNonQuery() comm.CommandText = "Update testbackup set datein = null, timein = null where barcodeid = ?" If DgDisplay.CurrentRowIndex < 0 Then comm.Parameters.Add("", DBNull.Value) Else comm.Parameters.Add("", DgDisplay.Item(DgDisplay.CurrentRowIndex, 0).ToString) End If comm.ExecuteNonQuery() Catch ex As Exception MsgBox(ex.Message) Err.Clear() Finally O