-
Oct 31st, 2014, 03:52 PM
#1
Thread Starter
Member
[RESOLVED] I need an urgent help
Hello,
i have a problem, when i want to insert data in a SQL database, it gives me that "object reference not set to an instance of an object"
Here is the code:
cmd.Connection = con
con.Open()
cmd.CommandText = "INSERT INTO TPVoucher (VNo, VDate, SellingCompany, CustomerCompany, Transporter, MaterialName_Description, DischargeLocation, LoadingLocation, VehicleNo, Unit, QTY, UnitPrice, ReceivedBy, EditStatVoucher, Billed, IssueBill, InvoiceNoSub) VALUES('" & txt_VoucherNumber.Text & "','" & cmb_SellingCompany.SelectedItem.ToString & "', '" & dtp_VoucherDate.Value & "', '" &
cmb_CustomerCompany.SelectedItem.ToString & "', '" & txt_Transporter.Text & "', '" & cmb_MaterialName.SelectedItem.ToString & "', '" & cmb_DischargeLocation.SelectedItem.ToString & "', '" &
cmb_LoadingLocation.SelectedItem.ToString & "', '" & cmb_VehicleNo.SelectedItem.ToString & "', '" & cmb_Unit.SelectedItem.ToString & "', '" & txt_QTY.Text & "', '" & txt_UnitPrice.Text & "', '" & txt_ReceivedBy.Text & "' , '" & txt_EditStatVoucher.Text & "', '" & Billed & "', '" & txt_IssueBill.Text & "', '" & txt_InvoiceNoSub.Text & "')"
cmd.ExecuteNonQuery()
con.Close()
MessageBox.Show("The record has been successfully added.", "Success", MessageBoxButtons.OK, MessageBoxIcon.None)
Thank you
-
Oct 31st, 2014, 04:38 PM
#2
Re: I need an urgent help
The solution to that problem always has the same initial steps: In the line that is causing the exception (which you didn't specify), one of the objects is Nothing. The first step is to find that object. Frankly, I believe the problem comes before the code you have posted, because you didn't show how either cmd or con was created.
If the exception is actually in the line that you made red, then the most likely answer is that one of the .SelectedItem statements is Nothing, in which case .ToString would fail. Still, the first step is to find which one is Nothing. In a way, it would be better if it was one of the .SelectedItems, because the best solution then would be to use parameterized queries, which you should be doing anyways, as the code, as written, leaves you open to SQL injection exploits.
My usual boring signature: Nothing
-
Nov 1st, 2014, 07:16 AM
#3
Thread Starter
Member
Re: I need an urgent help
Thank you Shaggy for your reply.
below you can find the full code, and the issue is in the highlighted in red.
Imports System.Data
Imports System.Data.SqlClient
Public Class Voucher
Dim Billed As String
Dim con As New SqlConnection("Data Source=.;Initial Catalog=TP;Integrated Security=True")
Dim cmd As New SqlCommand
Dim rd As SqlDataReader
Private Sub ComboBox3_SelectedIndexChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmb_LoadingLocation.SelectedIndexChanged
End Sub
Private Sub tsp_AddVoucher_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles tsp_AddVoucher.Click
cmd.Connection = con
con.Open()
cmd.CommandText = "INSERT INTO TPVoucher (VNo, VDate, SellingCompany, CustomerCompany, Transporter, MaterialName_Description, DischargeLocation, LoadingLocation, VehicleNo, Unit, QTY, UnitPrice, ReceivedBy, EditStatVoucher, Billed, IssueBill, InvoiceNoSub) VALUES('" & txt_VoucherNumber.Text & "','" & cmb_SellingCompany.SelectedItem.ToString & "', '" & dtp_VoucherDate.Value & "', '" &
cmb_CustomerCompany.SelectedItem.ToString & "', '" & txt_Transporter.Text & "', '" & cmb_MaterialName.SelectedItem.ToString & "', '" & cmb_DischargeLocation.SelectedItem.ToString & "', '" &
cmb_LoadingLocation.SelectedItem.ToString & "', '" & cmb_VehicleNo.SelectedItem.ToString & "', '" & cmb_Unit.SelectedItem.ToString & "', '" & txt_QTY.Text & "', '" & txt_UnitPrice.Text & "', '" & txt_ReceivedBy.Text & "' , '" & txt_EditStatVoucher.Text & "', '" & Billed & "', '" & txt_IssueBill.Text & "', '" & txt_InvoiceNoSub.Text & "')"
cmd.ExecuteNonQuery()
con.Close()
MessageBox.Show("The record has been successfully added.", "Success", MessageBoxButtons.OK, MessageBoxIcon.None)
End Sub
Private Sub Voucher_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'TODO: This line of code loads data into the 'TPDataSet8.TPSellingCompany' table. You can move, or remove it, as needed.
Me.TPSellingCompanyTableAdapter.Fill(Me.TPDataSet8.TPSellingCompany)
'TODO: This line of code loads data into the 'TPDataSet9.TPCustomerCompany' table. You can move, or remove it, as needed.
Me.TPCustomerCompanyTableAdapter.Fill(Me.TPDataSet9.TPCustomerCompany)
'TODO: This line of code loads data into the 'TPDataSet6.TPLocations' table. You can move, or remove it, as needed.
Me.TPLocationsTableAdapter.Fill(Me.TPDataSet6.TPLocations)
End Sub
Private Sub rd_No_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rd_No.CheckedChanged
Billed = "No"
End Sub
Private Sub rd_Yes_CheckedChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles rd_Yes.CheckedChanged
Billed = "Yes"
End Sub
End Class
I tried the .SelectedItem without .ToString but it gives me " the operator '&' is not defined for a string, or do you mean that one of them i should remove the .ToString?
Thank you.
-
Nov 1st, 2014, 09:49 AM
#4
Re: I need an urgent help
Please I beg of you, use string.format to format your strings because right now it looks like a mess.
And two you're meant to do uhm.
SQLCmd = New Sqlcommand(Query,ConString)
something like that before you submit a query.
-
Nov 1st, 2014, 03:29 PM
#5
Re: I need an urgent help
There are many ways to create a command. I wouldn't use either of the two shown, but that doesn't mean that I'm right, or that my way is better than any other. It's more a matter of preference than anything else.
If the error is in the red line, that is unfortunate, because there are LOTS of objects in there. However, the task is still the same: One of them is Nothing, and you have to find out which one. When the exception happens, you can hover over each object in turn (sometimes you have to highlight it and press Shift+F9), and find the one that is nothing. There are loads of objects in that line, but some can be ruled out right away, such as every textbox.Text. The only way one of them could be Nothing is if you had Option Explicit OFF, in which case you have far worse problems than this exception.
The dtp.Value statements may be ignored, as well...I think. Also, cmd can be ignored, because if that was Nothing the exception would have occured earlier in the code. This leaves the .SelectedItem. statements, and possibly 'billed'. Tell us which one is Nothing.
My usual boring signature: Nothing
-
Nov 1st, 2014, 03:56 PM
#6
Member
Re: I need an urgent help
Hi.
I would stop the execution right in that line, then i would hover my mouse on each variable and check its value.
Good Luck.
Heishiro.
-
Nov 1st, 2014, 04:14 PM
#7
Re: I need an urgent help
Originally Posted by heishiro
Hi.
I would stop the execution right in that line, then i would hover my mouse on each variable and check its value.
Good Luck.
Heishiro.
that's how ya do it
Process control doesn't give you good quality, it gives you consistent quality.
Good quality comes from consistently doing the right things.
Vague general questions have vague general answers. A $100 donation is required for me to help you if you PM me asking for help. Instructions for donating to one of our local charities will be provided.
______________________________ Last edited by kebo : Now. Reason: superfluous typo's
-
Nov 1st, 2014, 07:14 PM
#8
Re: I need an urgent help
The first thing you should do is rewrite your code to use parameters. As has been suggested, using string concatenation to insert values into SQL code is just plain bad for a number of reasons. Do it properly and the issue will be easier to track down. Follow the Blog link in my signature below and check out my post on Paramaters In ADO.NET. Once you are using parameters, you'll be using one value per line so you'll immediately be able to see which value is the issue based on the line on which the exception is thrown. If you write good code to start with, debugging it is much easier.
Posting Permissions
- You may not post new threads
- You may not post replies
- You may not post attachments
- You may not edit your posts
-
Forum Rules
|
Click Here to Expand Forum to Full Width
|