Results 1 to 17 of 17

Thread: not able to save data getting error

  1. #1
    master_2013 is offline VB.NET Forum Enthusiast
    .NET Framework
    .NET 3.5
    Join Date
    Jul 2013
    Posts
    41
    Reputation
    48

    not able to save data getting error

    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,@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

  2. #2
    master_2013 is offline VB.NET Forum Enthusiast
    .NET Framework
    .NET 3.5
    Join Date
    Jul 2013
    Posts
    41
    Reputation
    48
    help me please its urgent ????

  3. #3
    jmcilhinney's Avatar
    jmcilhinney is offline VB.NET Forum Moderator
    .NET Framework
    .NET 4.0
    Join Date
    Aug 2004
    Location
    Sydney, Australia
    Posts
    13,802
    Reputation
    1705
    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.

  4. #4
    master_2013 is offline VB.NET Forum Enthusiast
    .NET Framework
    .NET 3.5
    Join Date
    Jul 2013
    Posts
    41
    Reputation
    48
    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 ??

  5. #5
    jmcilhinney's Avatar
    jmcilhinney is offline VB.NET Forum Moderator
    .NET Framework
    .NET 4.0
    Join Date
    Aug 2004
    Location
    Sydney, Australia
    Posts
    13,802
    Reputation
    1705
    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.

  6. #6
    jacky.goy's Avatar
    jacky.goy is offline VB.NET Forum Enthusiast
    .NET Framework
    .NET 4.0
    Join Date
    Sep 2006
    Posts
    38
    Reputation
    131
    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.
    -------------------------------------------------
    Best Regards,
    Jacky Goy

  7. #7
    master_2013 is offline VB.NET Forum Enthusiast
    .NET Framework
    .NET 3.5
    Join Date
    Jul 2013
    Posts
    41
    Reputation
    48
    After changing this above code you have provided i am getting error "TYPE NAME IS INVALID"

  8. #8
    jacky.goy's Avatar
    jacky.goy is offline VB.NET Forum Enthusiast
    .NET Framework
    .NET 4.0
    Join Date
    Sep 2006
    Posts
    38
    Reputation
    131
    Did you change the OleDbType accordingly ?
    -------------------------------------------------
    Best Regards,
    Jacky Goy

  9. #9
    master_2013 is offline VB.NET Forum Enthusiast
    .NET Framework
    .NET 3.5
    Join Date
    Jul 2013
    Posts
    41
    Reputation
    48
    what to change bro i have the first one numeric and others are in short text in access database

  10. #10
    jmcilhinney's Avatar
    jmcilhinney is offline VB.NET Forum Moderator
    .NET Framework
    .NET 4.0
    Join Date
    Aug 2004
    Location
    Sydney, Australia
    Posts
    13,802
    Reputation
    1705
    Quote Originally Posted by master_2013 View Post
    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.

  11. #11
    master_2013 is offline VB.NET Forum Enthusiast
    .NET Framework
    .NET 3.5
    Join Date
    Jul 2013
    Posts
    41
    Reputation
    48
    I changed all data types date to date/time,numeric,text but getting the same error "no value given for one or more parameters"

  12. #12
    jmcilhinney's Avatar
    jmcilhinney is offline VB.NET Forum Moderator
    .NET Framework
    .NET 4.0
    Join Date
    Aug 2004
    Location
    Sydney, Australia
    Posts
    13,802
    Reputation
    1705
    Quote Originally Posted by master_2013 View Post
    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.

  13. #13
    master_2013 is offline VB.NET Forum Enthusiast
    .NET Framework
    .NET 3.5
    Join Date
    Jul 2013
    Posts
    41
    Reputation
    48
    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,@Vehiclebook edfor,@Vehiclebookedby,@VisitPlace,@JourneyDate,@J ourneyTime,@StartingReadingKM,@ReturnDate,@ReturnT ime,@EndingReadingKM,@ACORNON-AC,@TotalKM,@Useddays,@NightHalt,@Hours,@AdvByClie ntsForCash,@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.OLED B.12.0;Data Source=" & Application.StartupPath & "\STMS.accdb;Persist Security Info=False;")
    End Module
    Attached Images Attached Images

  14. #14
    jmcilhinney's Avatar
    jmcilhinney is offline VB.NET Forum Moderator
    .NET Framework
    .NET 4.0
    Join Date
    Aug 2004
    Location
    Sydney, Australia
    Posts
    13,802
    Reputation
    1705
    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?

  15. #15
    master_2013 is offline VB.NET Forum Enthusiast
    .NET Framework
    .NET 3.5
    Join Date
    Jul 2013
    Posts
    41
    Reputation
    48
    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 !!!!!!!!!!
    !!!!!

  16. #16
    master_2013 is offline VB.NET Forum Enthusiast
    .NET Framework
    .NET 3.5
    Join Date
    Jul 2013
    Posts
    41
    Reputation
    48
    how to show the save date/time value added previously in edit date/time field?????????

  17. #17
    jmcilhinney's Avatar
    jmcilhinney is offline VB.NET Forum Moderator
    .NET Framework
    .NET 4.0
    Join Date
    Aug 2004
    Location
    Sydney, Australia
    Posts
    13,802
    Reputation
    1705
    If the question you asked about here has been answered then please mark the thread Answered. If you have a new question then please start a new thread for that question.

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •