Help me out to clear error

Deva

Member
Joined
Feb 10, 2021
Messages
17
Programming Experience
1-3
1624253082087.png
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,553
Location
Sydney, Australia
Programming Experience
10+
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.
 

Deva

Member
Joined
Feb 10, 2021
Messages
17
Programming Experience
1-3
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.
ok i will post my code , i dont know how to use this forum
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,553
Location
Sydney, Australia
Programming Experience
10+
As for the issue, the error message is telling you exactly what the issue is. If you had done your own research first, which you should have done, then you'd know what the general circumstances are that cause a NullReferenceException to be thrown, i.e. accessing a member via
ok i will post my code , i dont know how to use this forum
That's fine, as long as you learn from your mistakes. :)
 

Deva

Member
Joined
Feb 10, 2021
Messages
17
Programming Experience
1-3
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.
VB.NET:
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



this is my piece of code ,

i m trying to insert data from datagridview to my mysql database , when i click my button , object reference not set to an instance of an object this error occured, but my data is inserted to my database.
 
Last edited by a moderator:

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,553
Location
Sydney, Australia
Programming Experience
10+
As for the issue, the error message is telling you exactly what the issue is. The first thing you should have done was research what a 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.

One option in some situations is to use the null propagation operator. In this case, instead of this part:
VB.NET:
DataGridView1.Rows(i).Cells("LotNo").Value.ToString
you could use this:
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 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.
 

Deva

Member
Joined
Feb 10, 2021
Messages
17
Programming Experience
1-3
As for the issue, the error message is telling you exactly what the issue is. The first thing you should have done was research what a 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.

One option in some situations is to use the null propagation operator. In this case, instead of this part:
VB.NET:
DataGridView1.Rows(i).Cells("LotNo").Value.ToString
you could use this:
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 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?.ToString

if i use this code , null values also inserted , how to omit my null values to my database table
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,553
Location
Sydney, Australia
Programming Experience
10+
Basically, you're doing your data access wrong. Let's improve it in steps.

Firstly, it's best not use 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:
VB.NET:
cmd.Parameters.Add("@lotno", MySqlDbType.VarChar, 50).Value = DataGridView1.Rows(i).Cells("LotNo").Value?.ToString()
That adds a parameter with the appropriate name, data type and, if necessary, size and then sets its Value afterwards.

Secondly, using null propagation here doesn't work because you can't use 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:
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
or the succinct way:
VB.NET:
cmd.Parameters.Add("@lotno", MySqlDbType.VarChar, 50).Value = If(DataGridView1.Rows(i).Cells("LotNo").Value, CObj(DBNull.Value))
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 the Value on each iteration. Doing that, you have no choice but to use Add:
VB.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
Finally, you shouldn't be using a loop at all. What you should be doing is creating a 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.
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,553
Location
Sydney, Australia
Programming Experience
10+
In post #2, I specifically said that you should post your code FORMATTED APPROPRIATELY. In post #5 you posted unformatted code. I have now taken the time to format it for you, so that it is appropriately readable. Please format ALL code snippets for readability.
 

jmcilhinney

VB.NET Forum Moderator
Staff member
Joined
Aug 17, 2004
Messages
14,553
Location
Sydney, Australia
Programming Experience
10+
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
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.
 

Deva

Member
Joined
Feb 10, 2021
Messages
17
Programming Experience
1-3
Basically, you're doing your data access wrong. Let's improve it in steps.

Firstly, it's best not use 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:
VB.NET:
cmd.Parameters.Add("@lotno", MySqlDbType.VarChar, 50).Value = DataGridView1.Rows(i).Cells("LotNo").Value?.ToString()
That adds a parameter with the appropriate name, data type and, if necessary, size and then sets its Value afterwards.

Secondly, using null propagation here doesn't work because you can't use 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:
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
or the succinct way:
VB.NET:
cmd.Parameters.Add("@lotno", MySqlDbType.VarChar, 50).Value = If(DataGridView1.Rows(i).Cells("LotNo").Value, CObj(DBNull.Value))
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 the Value on each iteration. Doing that, you have no choice but to use Add:
VB.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
Finally, you shouldn't be using a loop at all. What you should be doing is creating a 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.
Thanks for reply and i trouble shooted my error by your conservation
 
Top Bottom