ok i will post my code , i dont know how to use this forumPlease don't post just a picture of code or of error messages. Both are text so please post them as text, formatted appropriately. If you feel that a screenshot adds some value then, by all means, provide that as well but it should NEVER be all you post. If we want to test your code for ourselves then we can't copy and paste it from a picture. Likewise, if we want to search the web for your error message. Also, images are harder to deal with on small devices and several people who answer questions on this site regularly view it using a phone.
NullReferenceException
to be thrown, i.e. accessing a member viaThat's fine, as long as you learn from your mistakes.ok i will post my code , i dont know how to use this forum
Please don't post just a picture of code or of error messages. Both are text so please post them as text, formatted appropriately. If you feel that a screenshot adds some value then, by all means, provide that as well but it should NEVER be all you post. If we want to test your code for ourselves then we can't copy and paste it from a picture. Likewise, if we want to search the web for your error message. Also, images are harder to deal with on small devices and several people who answer questions on this site regularly view it using a phone. Even if you do post the code and error message correctly, that still doesn't mean that you don't have to provide anything else. You need to ALWAYS provide a FULL and CLEAR explanation of the issue, which includes exactly what you're trying to achieve, how you're trying to achieve it, what happens when you try and how that differs from your expectations. Sometimes we won't need it but usually we will, so just provide it every time. Do ALL you can to help us help you.
Private Sub Button1_Click(sender As Object, e As EventArgs) Handles Button1.Click
lblBilltype.Text = "CASH BILL"
Me.BackColor = Color.LightGreen
Try
myconn.Open()
For i As Integer = 0 To DataGridView1.Rows.Count - 1
cmd = New MySqlCommand("insert into bills(lotno)values(@lotno)", myconn)
cmd.Parameters.AddWithValue("lotno", DataGridView1.Rows(i).Cells("LotNo").Value.ToString)
cmd.ExecuteNonQuery()
Next
MsgBox("Cash Bill Save", vbInformation)
myconn.Close()
Catch ex As Exception
myconn.Close()
MsgBox(ex.Message, vbCritical)
End Try
End Sub
NullReferenceException
is and what causes it to be thrown. If you'd done that then you'd know that it is trying to access a method of an object that doesn't exist. The error message is telling you that the Value
property returns Nothing
so how can you call ToString
on it? If your data might be Nothing
then you have to check that first before accessing a member and then act accordingly. How to act depends on the situation.DataGridView1.Rows(i).Cells("LotNo").Value.ToString
DataGridView1.Rows(i).Cells("LotNo").Value?.ToString
?.
instead of just .
the expression will be evaluated as normal if there is an object and stop there and return Nothing
if there isn't. That would alleviate the immediate issue but would simply kick the can down the road and a different exception would be thrown.DataGridView1.Rows(i).Cells("LotNo").Value?.ToStringAs for the issue, the error message is telling you exactly what the issue is. The first thing you should have done was research what aNullReferenceException
is and what causes it to be thrown. If you'd done that then you'd know that it is trying to access a method of an object that doesn't exist. The error message is telling you that theValue
property returnsNothing
so how can you callToString
on it? If your data might beNothing
then you have to check that first before accessing a member and then act accordingly. How to act depends on the situation.
One option in some situations is to use the null propagation operator. In this case, instead of this part:
you could use this:VB.NET:DataGridView1.Rows(i).Cells("LotNo").Value.ToString
DataGridView1.Rows(i).Cells("LotNo").Value?.ToString
When you use?.
instead of just.
the expression will be evaluated as normal if there is an object and stop there and returnNothing
if there isn't. That would alleviate the immediate issue but would simply kick the can down the road and a different exception would be thrown.
AddWithValue
at all. It will work as expected most of the time but some of the time it won't. Do you know when those times are? Just don't use it at all and you don't have to. Always use Add
and specify the data type and it will always work as expected:cmd.Parameters.Add("@lotno", MySqlDbType.VarChar, 50).Value = DataGridView1.Rows(i).Cells("LotNo").Value?.ToString()
Value
afterwards.Nothing
as a value for an ADO.NET parameter. Maybe MySQL's Connector/Net does support it but, as far as I'm aware, other ADO.NET providers do not. What you should be doing is explicitly using a NULL value, which you do with DBNull.Value
. This is another reason not to use AddWithValue
, because a data type cannot be inferred without a value. You need to check for no value explicitly and then act accordingly. You can do that the verbose way:With cmd.Parameters.Add("@lotno", MySqlDbType.VarChar, 50)
If DataGridView1.Rows(i).Cells("LotNo").Value Is Nothing Then
.Value = DBNull.Value
Else
.Value = DataGridView1.Rows(i).Cells("LotNo").Value
End If
End With
cmd.Parameters.Add("@lotno", MySqlDbType.VarChar, 50).Value = If(DataGridView1.Rows(i).Cells("LotNo").Value, CObj(DBNull.Value))
Value
on each iteration. Doing that, you have no choice but to use Add
:cmd = New MySqlCommand("INSERT INTO bills (lotno) VALUES (@lotno)", myconn)
Dim param = cmd.Parameters.Add("@lotno", MySqlDbType.VarChar, 50)
For i = 0 To DataGridView1.Rows.Count - 1
param.Value = If(DataGridView1.Rows(i).Cells("LotNo").Value, CObj(DBNull.Value))
cmd.ExecuteNonQuery()
Next
DataTable
with the appropriate schema and binding that to your grid. Any changes made via the grid will be automatically propagated to that DataTable
. Also, any "empty" fields will be automatically set to DBNull.Value
. You can then create a MySqlDataAdapter
with its InsertCommand
configured appropriately and then save the lot with a single call to Update
. I'm going to leave it to you to research how to do that.And here we go with questions about information not provided. This is why you need to provide a FULL and CLEAR explanation. Please do that now. Stop to think what information you would need if you had no prior exposure to this project. Don't just assume that we know what you're thinking.DataGridView1.Rows(i).Cells("LotNo").Value?.ToString
if i use this code , null values also inserted , how to omit my null values to my database table
Thanks for reply and i trouble shooted my error by your conservationBasically, you're doing your data access wrong. Let's improve it in steps.
Firstly, it's best not useAddWithValue
at all. It will work as expected most of the time but some of the time it won't. Do you know when those times are? Just don't use it at all and you don't have to. Always useAdd
and specify the data type and it will always work as expected:
That adds a parameter with the appropriate name, data type and, if necessary, size and then sets itsVB.NET:cmd.Parameters.Add("@lotno", MySqlDbType.VarChar, 50).Value = DataGridView1.Rows(i).Cells("LotNo").Value?.ToString()
Value
afterwards.
Secondly, using null propagation here doesn't work because you can't useNothing
as a value for an ADO.NET parameter. Maybe MySQL's Connector/Net does support it but, as far as I'm aware, other ADO.NET providers do not. What you should be doing is explicitly using a NULL value, which you do withDBNull.Value
. This is another reason not to useAddWithValue
, because a data type cannot be inferred without a value. You need to check for no value explicitly and then act accordingly. You can do that the verbose way:
or the succinct way:VB.NET:With cmd.Parameters.Add("@lotno", MySqlDbType.VarChar, 50) If DataGridView1.Rows(i).Cells("LotNo").Value Is Nothing Then .Value = DBNull.Value Else .Value = DataGridView1.Rows(i).Cells("LotNo").Value End If End With
Thirdly, if you're going to use a loop like that then you should not be creating a new command and parameter on every iteration. You should create one command and add one parameter and then simply set theVB.NET:cmd.Parameters.Add("@lotno", MySqlDbType.VarChar, 50).Value = If(DataGridView1.Rows(i).Cells("LotNo").Value, CObj(DBNull.Value))
Value
on each iteration. Doing that, you have no choice but to useAdd
:
Finally, you shouldn't be using a loop at all. What you should be doing is creating aVB.NET:cmd = New MySqlCommand("INSERT INTO bills (lotno) VALUES (@lotno)", myconn) Dim param = cmd.Parameters.Add("@lotno", MySqlDbType.VarChar, 50) For i = 0 To DataGridView1.Rows.Count - 1 param.Value = If(DataGridView1.Rows(i).Cells("LotNo").Value, CObj(DBNull.Value)) cmd.ExecuteNonQuery() Next
DataTable
with the appropriate schema and binding that to your grid. Any changes made via the grid will be automatically propagated to thatDataTable
. Also, any "empty" fields will be automatically set toDBNull.Value
. You can then create aMySqlDataAdapter
with itsInsertCommand
configured appropriately and then save the lot with a single call toUpdate
. I'm going to leave it to you to research how to do that.