not able to save data getting error

master_2013

Active member
Joined
Jul 8, 2013
Messages
41
Programming Experience
Beginner
Untitled3.jpgUntitled4.jpgwhen 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,@Vehiclebookedfor,@Vehiclebookedby,@VisitPlace,@JourneyDate,@JourneyTime,@StartingReadingKM,@ReturnDate,@ReturnTime,@EndingReadingKM,@ACORNON-AC,@TotalKM,@Useddays,@NightHalt,@Hours,@AdvByClientsForCash,@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','@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(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
 
That error message means that you are trying to insert a value into a column that is not of a compatible data type. I can see two issues with your code.

Firstly, and most obviously, you're not actually using parameters. You're trying to but failing. In SQL code, when you wrap something in single quotes you are saying that it is literal text. It's the same as when you use double quotes in VB. In VB code, what would you expect this to display?
Dim var1 As String = "Hello World"
Dim var2 As String = var1

MessageBox.Show()
If you said that it would display "Hello World" (without quotes) then you get a gold star. Now, what would you expect this to display?
Dim var1 As String = "Hello World"
Dim var2 As String = "var1"

MessageBox.Show()
According to your SQL code, you would expect it to display "Hello World", but it would actually display "var1". In your SQL code, take all the single quotes away from around the parameter names. That way, you'll actually be using the parameter values instead of the parameter names.

Secondly, you're using AddWithValue with Strings to add all the parameters. The point of AddWithValue is to add a parameter with a value and have the data type inferred from that value. You're using the Text property of a control for every one so every parameter will be inferred to be text. Are all your database columns a text data type? I certainly hope not. I can see several columns that should be a date data type so if you want to be using AddWithValue for them then you should be passing a Date as the value, not a String.

On an unrelated note, I also notice that you have not renamed any of your controls from their default. That is absolutely terrible. How is anyone supposed to look at your app and know what KryptonTextBox2 is for and what KryptonTextBox3 is for without weasting their time digging around? That includes you when you go back to this code after not having looked at it for several months. When you add a control to a form, the very first thing you should do is to change its name to something meaningful. EVERY type, meber and variable you use should have a meaningful name.
 
Can you please explain properly after removing the single quote im getting error i mentioned above please do check and help me on my code ??
 
I already have explained properly. It's like you didn't even read what I posted. Did you read this at all?
Secondly, you're using AddWithValue with Strings to add all the parameters. The point of AddWithValue is to add a parameter with a value and have the data type inferred from that value. You're using the Text property of a control for every one so every parameter will be inferred to be text. Are all your database columns a text data type? I certainly hope not. I can see several columns that should be a date data type so if you want to be using AddWithValue for them then you should be passing a Date as the value, not a String.
The error message is teling you and I have told you that you are using data of the wrong type. The solution is to use data of the correct type. I don't know exactly what type your data should be because, unlike you, I didn't specify the type of the data in the first place. You know what type the data is supposed to be so use data of that type. I gave the example of columns that are supposed to contains dates. Likewise, if columns are supposed to contain numbers then you need to provide numbers, not text.
 
try changing your code as below


Try
rs = "INSERT INTO [Duty Slip] 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(rs, con)


cmd.Parameters.Add("@ID", OleDbType.VarChar)
cmd.Parameters.Add("@DDate", OleDbType.VarChar)
cmd.Parameters.Add("@VehicleType", OleDbType.VarChar)
cmd.Parameters.Add("@RegNo", OleDbType.VarChar)
cmd.Parameters.Add("@Vehiclebookedfor", OleDbType.VarChar)
cmd.Parameters.Add("@Vehiclebookedby", OleDbType.VarChar)
cmd.Parameters.Add("@VisitPlace", OleDbType.VarChar)
cmd.Parameters.Add("@JourneyDate", OleDbType.VarChar)
cmd.Parameters.Add("@JourneyTime", OleDbType.VarChar)
cmd.Parameters.Add("@StartingReadingKM", OleDbType.VarChar)
cmd.Parameters.Add("@ReturnDate", OleDbType.VarChar)
cmd.Parameters.Add("@ReturnTime", OleDbType.VarChar)
cmd.Parameters.Add("@EndingReadingKM", OleDbType.VarChar)
cmd.Parameters.Add("@ACORNON-AC", OleDbType.VarChar)
cmd.Parameters.Add("@TotalKM", OleDbType.VarChar)
cmd.Parameters.Add("@Useddays", OleDbType.VarChar)
cmd.Parameters.Add("@NightHalt", OleDbType.VarChar)
cmd.Parameters.Add("@Hours", OleDbType.VarChar)
cmd.Parameters.Add("@AdvByClientsForCash", OleDbType.VarChar)
cmd.Parameters.Add("@ForFuel", OleDbType.VarChar)
cmd.Parameters.Add("@AdvByAgencyForCash", OleDbType.VarChar)
cmd.Parameters.Add("@ForFuel1", OleDbType.VarChar)

cmd.Parameters(0).Value = KryptonTextBox18.Text
cmd.Parameters(1).Value = KryptonLabel2.Text
cmd.Parameters(2).Value = KryptonComboBox1.Text
cmd.Parameters(3).Value = KryptonTextBox16.Text
cmd.Parameters(4).Value = KryptonTextBox1.Text
cmd.Parameters(5).Value = KryptonTextBox17.Text
cmd.Parameters(6).Value = KryptonTextBox2.Text
cmd.Parameters(7).Value = KryptonDateTimePicker1.Text
cmd.Parameters(8).Value = KryptonTextBox3.Text
cmd.Parameters(9).Value = KryptonTextBox4.Text
cmd.Parameters(10).Value = KryptonDateTimePicker2.Text
cmd.Parameters(11).Value = KryptonTextBox6.Text
cmd.Parameters(12).Value = KryptonTextBox5.Text
cmd.Parameters(13).Value = KryptonTextBox7.Text
cmd.Parameters(14).Value = KryptonTextBox8.Text
cmd.Parameters(15).Value = KryptonTextBox9.Text
cmd.Parameters(16).Value = KryptonTextBox10.Text
cmd.Parameters(17).Value = KryptonTextBox11.Text
cmd.Parameters(18).Value = KryptonTextBox12.Text
cmd.Parameters(19).Value = KryptonTextBox13.Text
cmd.Parameters(20).Value = KryptonTextBox14.Text
cmd.Parameters(21).Value = 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


**Remark**
You may need to change the OleDbType to suit your field type.
 
Did you change the OleDbType accordingly ?
 
what to change bro i have the first one numeric and others are in short text in access database

Why have you not yet used the Help menu to find the documentation for the OleDbType enumeration to see what options are available and what they mean? You don't have to sit on your hands and wait to be fed all the information. You can use what you already have to find the information you need.

Also, your database is poorly designed if everything is being stored as text. You have two columns there that are supposed to be storing dates and Access has a Date/Time data type sop why are you not using that? You should never simply default to text for everything. Always use the data type that is most appropriate for the data. As an example of why, you'll never be able to sort by column that contains dates as text unless you use a specific format and you'll never be able to filter other than using an equality comparison. If you have a hammer available, you'd never use a screwdriver to bang in a nail, so why would you use a text data type to store dates when you have a data type specifically for dates? You're just making things harder for yourself for no good reason.
 
I changed all data types date to date/time,numeric,text but getting the same error "no value given for one or more parameters"

That would suggest that you haven't provided a value for one or more of your parameters. Have you checked to make sure that each parameter is given a value? If you'd like us to help you with an issue with your current code then you should show us your current code.
 
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
 

Attachments

  • Untitled41.png
    Untitled41.png
    64.4 KB · Views: 52
I very much doubt that you can put a dash in a parameter name. Get rid of that dash in that parameter name and I suspect that it will work as you expect.

Also, what's the point of calling Convert.ToString and passing a String? Exactly what conversion do you expect that to make?

Finally, I see that, despite storing the dates in Date/Time columns, you're still storing the times in text columns. Why so determined to do the wrong thing? Why even split up the date and the time in the database when you could just store both the date and the time in a single Date/Time column?
 
thanks it works like a charm thanks a lot i used "ACORNON-AC" thats why its not working thanks for your sincere help thanxxx a lot !!!!!!!!!!
!!!!!
 
Back
Top