-
October 26th, 2016, 05:29 AM
#1
not able to save data getting error in vb.net
when i execute this code to save data i got data type mismatch in criteria expression when i remove the brackets used in like rs = "INSERT INTO [Duty Slip] VALUES(@ID,@DDate,@VehicleType,@RegNo,@Vehiclebook edfor,@Vehiclebookedby,@VisitPlace,@JourneyDate,@J ourneyTime,@StartingReadingKM,@ReturnDate,@ReturnT ime,@EndingReadingKM,@ACORNON-AC,@TotalKM,@Useddays,@NightHalt,@Hours,@AdvByClie ntsForCash,@ForFuel,@AdvByAgencyForCash,@ForFuel1) " then i got this error "NO VALUE GIVEN FOR ONE OR MORE REQUIRED PARAMETER" what to do please help me out ???
here is my code
Private Sub KryptonButton1_Click(sender As System.Object, e As System.EventArgs) Handles KryptonButton1.Click
If con.State = ConnectionState.Open Then
con.Close()
End If
Dim rs As String
Try
rs = "INSERT INTO [Duty Slip] VALUES('@ID','@DDate','@VehicleType','@RegNo','@Ve hiclebookedfor','@Vehiclebookedby','@VisitPlace',' @JourneyDate','@JourneyTime','@StartingReadingKM', '@ReturnDate','@ReturnTime','@EndingReadingKM','@A CORNON-AC','@TotalKM','@Useddays','@NightHalt','@Hours',' @AdvByClientsForCash','@ForFuel','@AdvByAgencyForC ash','@ForFuel1')"
Dim cmd As OleDbCommand = New OleDbCommand(rs, con)
cmd.Parameters.AddWithValue("ID", KryptonTextBox18.Text)
cmd.Parameters.AddWithValue("DDate", KryptonLabel2.Text)
cmd.Parameters.AddWithValue("VehicleType", KryptonComboBox1.Text)
cmd.Parameters.AddWithValue("RegNo", KryptonTextBox16.Text)
cmd.Parameters.AddWithValue("Vehiclebookedfor", KryptonTextBox1.Text)
cmd.Parameters.AddWithValue("VehiclebookedBy", KryptonTextBox17.Text)
cmd.Parameters.AddWithValue("VisitPlace", KryptonTextBox2.Text)
cmd.Parameters.AddWithValue("JourneyDate", KryptonDateTimePicker1.Text)
cmd.Parameters.AddWithValue("JourneyTime", KryptonTextBox3.Text)
cmd.Parameters.AddWithValue("StartingReadingKM", KryptonTextBox4.Text)
cmd.Parameters.AddWithValue("ReturnDate", KryptonDateTimePicker2.Text)
cmd.Parameters.AddWithValue("ReturnTime", KryptonTextBox6.Text)
cmd.Parameters.AddWithValue("EndingReadingKM", KryptonTextBox5.Text)
cmd.Parameters.AddWithValue("ACORNON-AC", KryptonTextBox7.Text)
cmd.Parameters.AddWithValue("TotalKM", KryptonTextBox8.Text)
cmd.Parameters.AddWithValue("Useddays", KryptonTextBox9.Text)
cmd.Parameters.AddWithValue("NightHalt", KryptonTextBox10.Text)
cmd.Parameters.AddWithValue("Hours", KryptonTextBox11.Text)
cmd.Parameters.AddWithValue("AdvByClientsForCash", KryptonTextBox12.Text)
cmd.Parameters.AddWithValue("ForFuel", KryptonTextBox13.Text)
cmd.Parameters.AddWithValue("AdvByAgencyForCash", KryptonTextBox14.Text)
cmd.Parameters.AddWithValue("ForFuel1", KryptonTextBox15.Text)
con.Open()
Dim x As Integer = cmd.ExecuteNonQuery
MessageBox.Show(x.ToString & "Data Saved Successfully....")
KryptonButton5.Enabled = True
con.Close()
clear()
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
-
October 26th, 2016, 06:37 AM
#2
Re: not able to save data getting error in vb.net
Find out what user settings cause the error. Put a breakpoint on the first .AddWithValue call, run the program in debug mode, enter the user settings that cause the issue and press save. When the breakpoint hits, hover over the .Text values to see if you can spot the offending value.
Another approach is to comment out portions of the insert statement to reduce the inserted values until you narrow down the one causing the problem (you can only do this on table columns that accept nulls or have defaults).
For these reasons, I usually will not put .Text values directly into a sql statement. Instead I will convert the values into their correct types first and store then in individual variables or a class instance that holds all the values of the correct type. The individual variables or class instance values would then be used in the insert statement.
Last edited by Arjay; October 26th, 2016 at 06:41 AM.
-
October 26th, 2016, 02:18 PM
#3
Re: not able to save data getting error in vb.net
Can you please explain more on this with a example to understand better ??
-
October 27th, 2016, 12:52 AM
#4
Re: not able to save data getting error in vb.net
Sounds from the error liek you have left out a field that is required or perhaps it is this '@Ve hiclebookedfor'
Always use [code][/code] tags when posting code.
-
October 27th, 2016, 03:54 AM
#5
Re: not able to save data getting error in vb.net
still im getting this error after solving these ??
-
October 27th, 2016, 11:17 AM
#6
Re: not able to save data getting error in vb.net
Originally Posted by master2010
Can you please explain more on this with a example to understand better ??
Have you tried setting a breakpoint and debugging?
-
November 4th, 2016, 01:04 AM
#7
Re: not able to save data getting error in vb.net
@master
your code doenst say what database you are using, but maybe in this case its not that importent.
Years ago I carried about this issus, those lines :
cmd.Parameters.AddWithValue("ID", KryptonTextBox18.Text)
you need to change to:
cmd.Parameters.AddWithValue("@ID", KryptonTextBox18.Text)
There has to be always the parameter.
Futhermore its recomeded to convert your textfields to the type like
cmd.Parameters.AddWithValue("@VisitPlace", Convert.ToString(KryptonTextBox2.Text))
ANd if it comes to types like DateTime, then:
cmd.Parameters.AddWithValue("@DDate", Convert.ToDatetime(KryptonTextBox18.Text))
-
November 4th, 2016, 04:41 AM
#8
Re: not able to save data getting error in vb.net
after changing as per your given data still im getting the same error man i am frustrated with these code ???? please do help me out ??STMS.rar
here s my code
Private Sub KryptonButton1_Click(sender As System.Object, e As System.EventArgs) Handles KryptonButton1.Click
If con.State = ConnectionState.Open Then
con.Close()
End If
Dim rpt As String
Try
rpt = "INSERT INTO DutySlip VALUES(@ID,@DDate,@VehicleType,@RegNo,@Vehiclebookedfor,@Vehiclebookedby,@VisitPlace,@JourneyDate,@JourneyTime,@StartingReadingKM,@ReturnDate,@ReturnTime,@EndingReadingKM,@ACORNON-AC,@TotalKM,@Useddays,@NightHalt,@Hours,@AdvByClientsForCash,@ForFuel,@AdvByAgencyForCash,@ForFuel1)"
Dim cmd As OleDbCommand = New OleDbCommand(rpt, con)
cmd.Parameters.AddWithValue("@ID", Convert.ToString(KryptonTextBox18.Text))
cmd.Parameters.AddWithValue("@DDate", Convert.ToDateTime(KryptonLabel2.Text))
cmd.Parameters.AddWithValue("@VehicleType", Convert.ToString(KryptonComboBox1.Text))
cmd.Parameters.AddWithValue("@RegNo", Convert.ToString(KryptonTextBox16.Text))
cmd.Parameters.AddWithValue("@Vehiclebookedfor", Convert.ToString(KryptonTextBox1.Text))
cmd.Parameters.AddWithValue("@Vehiclebookedby", Convert.ToString(KryptonTextBox17.Text))
cmd.Parameters.AddWithValue("@VisitPlace", Convert.ToString(KryptonTextBox2.Text))
cmd.Parameters.AddWithValue("@JourneyDate", Convert.ToDateTime(KryptonDateTimePicker1.Text))
cmd.Parameters.AddWithValue("@JourneyTime", Convert.ToString(KryptonTextBox3.Text))
cmd.Parameters.AddWithValue("@StartingReadingKM", Convert.ToString(KryptonTextBox4.Text))
cmd.Parameters.AddWithValue("@ReturnDate", Convert.ToDateTime(KryptonDateTimePicker2.Text))
cmd.Parameters.AddWithValue("@ReturnTime", Convert.ToString(KryptonTextBox6.Text))
cmd.Parameters.AddWithValue("@EndingReadingKM", Convert.ToString(KryptonTextBox5.Text))
cmd.Parameters.AddWithValue("@ACORNON-AC", Convert.ToString(KryptonTextBox7.Text))
cmd.Parameters.AddWithValue("@TotalKM", Convert.ToString(KryptonTextBox8.Text))
cmd.Parameters.AddWithValue("@Useddays", Convert.ToString(KryptonTextBox9.Text))
cmd.Parameters.AddWithValue("@NightHalt", Convert.ToString(KryptonTextBox10.Text))
cmd.Parameters.AddWithValue("@Hours", Convert.ToString(KryptonTextBox11.Text))
cmd.Parameters.AddWithValue("@AdvByClientsForCash", Convert.ToString(KryptonTextBox12.Text))
cmd.Parameters.AddWithValue("@ForFuel", Convert.ToString(KryptonTextBox13.Text))
cmd.Parameters.AddWithValue("@AdvByAgencyForCash", Convert.ToString(KryptonTextBox14.Text))
cmd.Parameters.AddWithValue("@ForFuel1", Convert.ToString(KryptonTextBox15.Text))
con.Open()
Dim x As Integer = cmd.ExecuteNonQuery
MessageBox.Show(x.ToString & "Data Saved Successfully....")
KryptonButton5.Enabled = True
con.Close()
clear()
Catch ex As Exception
MsgBox(ex.Message)
End Try
End Sub
module1 code
Imports System.Data.OleDb
Imports System.IO
Module Module1
Public rpt, dts As String
Public con As New OleDb.OleDbConnection("Provider=Microsoft.ACE.OLEDB.12.0;Data Source=" & Application.StartupPath & "\STMS.accdb;Persist Security Info=False;")
End Module
-
November 4th, 2016, 08:24 AM
#9
Re: not able to save data getting error in vb.net
What error are you getting? Your original post shows more than one error. You say you are still getting the same error but since you mentioned more than one there is no way to tell what error you are talking about.
Always use [code][/code] tags when posting code.
-
November 4th, 2016, 12:25 PM
#10
Re: not able to save data getting error in vb.net
got this error "NO VALUE GIVEN FOR ONE OR MORE REQUIRED PARAMETER"
-
November 4th, 2016, 01:21 PM
#11
Re: not able to save data getting error in vb.net
So do you understand what that error means?
Either you are missing a field or one of your values in blank
Your insert statement does not specify field names and you have not shown us the table layout so no way for us to know what field may be the issue.
Always use [code][/code] tags when posting code.
-
November 4th, 2016, 01:29 PM
#12
Re: not able to save data getting error in vb.net
I'm not a VB person, so can't really help with the VB code. But the way I would tackle something like this in another language would be to reduce the code to the absolute bare minimum - say just have @ID to start with and none of the others. Then get that code to work. Then gradually expand it so that it does more and more of what it's supposed to do until either the code is complete and it works (yuppee!) or an error then occurs. If an error occurs then it must be in the code added since the last time it worked. You now know (and can narrow it down to probably one line or part of a line of code) where is the error.
All advice is offered in good faith only. All my code is tested (unless stated explicitly otherwise) with the latest version of Microsoft Visual Studio (using the supported features of the latest standard) and is offered as examples only - not as production quality. I cannot offer advice regarding any other c/c++ compiler/IDE or incompatibilities with VS. You are ultimately responsible for the effects of your programs and the integrity of the machines they run on. Anything I post, code snippets, advice, etc is licensed as Public Domain https://creativecommons.org/publicdomain/zero/1.0/ and can be used without reference or acknowledgement. Also note that I only provide advice and guidance via the forums - and not via private messages!
C++23 Compiler: Microsoft VS2022 (17.6.5)
-
November 4th, 2016, 02:02 PM
#13
Re: not able to save data getting error in vb.net
I've not worked with an ACCDB myself and am not sure what the proper syntax is for the parameters. I know the code looks fine for SQL Server which is what I usually work with but in at least some cases I have saw ? used for this in access databases I am just not sure if that applies to both ACE and Jet or if only to one of the two.
Always use [code][/code] tags when posting code.
-
November 4th, 2016, 03:39 PM
#14
Re: not able to save data getting error in vb.net
Originally Posted by master2010
got this error "NO VALUE GIVEN FOR ONE OR MORE REQUIRED PARAMETER"
You are trying to insert the ID field from a text box. This is a bad idea. Instead, let access generate the number for you.
In access, change the data type of ID column in the DutySlip table from "Number" to "AutoNumber", then modify your code to not insert the ID column (Notice I've removed the @ID references):
Code:
rpt = "INSERT INTO DutySlip VALUES(@DDate,@VehicleType,@RegNo,@Vehiclebookedfor,@Vehiclebookedby,@VisitPlace,@JourneyDate,@JourneyTime,@StartingReadingKM,@ReturnDate,@ReturnTime,@EndingReadingKM,@ACORNON-AC,@TotalKM,@Useddays,@NightHalt,@Hours,@AdvByClientsForCash,@ForFuel,@AdvByAgencyForCash,@ForFuel1)"
Dim cmd As OleDbCommand = New OleDbCommand(rpt, con)
cmd.Parameters.AddWithValue("@DDate", Convert.ToDateTime(KryptonLabel2.Text))
cmd.Parameters.AddWithValue("@VehicleType", Convert.ToString(KryptonComboBox1.Text))
For comparison, here is the original code with the bolded items remove in the modified code above.
Code:
rpt = "INSERT INTO DutySlip VALUES(@ID,@DDate,@VehicleType,@RegNo,@Vehiclebookedfor,@Vehiclebookedby,@VisitPlace,@JourneyDate,@JourneyTime,@StartingReadingKM,@ReturnDate,@ReturnTime,@EndingReadingKM,@ACORNON-AC,@TotalKM,@Useddays,@NightHalt,@Hours,@AdvByClientsForCash,@ForFuel,@AdvByAgencyForCash,@ForFuel1)"
Dim cmd As OleDbCommand = New OleDbCommand(rpt, con)
cmd.Parameters.AddWithValue("@ID", Convert.ToString(KryptonTextBox18.Text))
cmd.Parameters.AddWithValue("@DDate", Convert.ToDateTime(KryptonLabel2.Text))
Last edited by Arjay; November 4th, 2016 at 03:42 PM.
-
November 4th, 2016, 05:14 PM
#15
Re: not able to save data getting error in vb.net
Whats this by the way:
rpt = "INSERT INTO DutySlip
is it "INSERT INTO TblName(and here should be the ClmNames)"
And even if it is, cut it, make it shorter, error messages are not always that clear, but after all, thats whats left, - >any fieldname is wrong or left.
Hereis a code code on stackoverflow, its oledb even with this sign '?' instead of parameters (this '?' were for oledb an obligation till around 2007, should work with ACE.12, if niot just place the parameters where '?' is.)
https://stackoverflow.com/questions/...s-using-vb-net
Last edited by pschulz; November 4th, 2016 at 05:15 PM.
Reason: correction better to read
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
|