CodeGuru Home VC++ / MFC / C++ .NET / C# Visual Basic VB Forums Developer.com
Results 1 to 15 of 15
  1. #1
    Join Date
    Oct 2016
    Posts
    5

    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
    Attached Images Attached Images   

  2. #2
    Arjay's Avatar
    Arjay is offline Moderator / EX MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    13,490

    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.

  3. #3
    Join Date
    Oct 2016
    Posts
    5

    Re: not able to save data getting error in vb.net

    Can you please explain more on this with a example to understand better ??

  4. #4
    Join Date
    Jul 2008
    Location
    WV
    Posts
    5,362

    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.

  5. #5
    Join Date
    Oct 2016
    Posts
    5

    Re: not able to save data getting error in vb.net

    still im getting this error after solving these ??

  6. #6
    Arjay's Avatar
    Arjay is offline Moderator / EX MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    13,490

    Re: not able to save data getting error in vb.net

    Quote Originally Posted by master2010 View Post
    Can you please explain more on this with a example to understand better ??
    Have you tried setting a breakpoint and debugging?

  7. #7
    Join Date
    Aug 2015
    Posts
    59

    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))

  8. #8
    Join Date
    Oct 2016
    Posts
    5

    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

  9. #9
    Join Date
    Jul 2008
    Location
    WV
    Posts
    5,362

    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.

  10. #10
    Join Date
    Oct 2016
    Posts
    5

    Re: not able to save data getting error in vb.net

    got this error "NO VALUE GIVEN FOR ONE OR MORE REQUIRED PARAMETER"

  11. #11
    Join Date
    Jul 2008
    Location
    WV
    Posts
    5,362

    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.

  12. #12
    2kaud's Avatar
    2kaud is online now Super Moderator Power Poster
    Join Date
    Dec 2012
    Location
    England
    Posts
    7,822

    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)

  13. #13
    Join Date
    Jul 2008
    Location
    WV
    Posts
    5,362

    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.

  14. #14
    Arjay's Avatar
    Arjay is offline Moderator / EX MS MVP Power Poster
    Join Date
    Aug 2004
    Posts
    13,490

    Re: not able to save data getting error in vb.net

    Quote Originally Posted by master2010 View Post
    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.

  15. #15
    Join Date
    Aug 2015
    Posts
    59

    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
  •  





Click Here to Expand Forum to Full Width

Featured