Page 2 of 2 FirstFirst 12
Results 41 to 48 of 48

Thread: [RESOLVED] Data Type Mismatch in criteria expression

  1. #41
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    Re: Data Type Mismatch in criteria expression

    I have nothing even close to that. I do have code that does inserts into databases, and what you are doing is not all that far off. In fact, if I put the snippets together, it doesn't look all that bad. I'd change from Parameters.Add to Parameters.AddWithValue, as I showed in post #30, but at this point, I don't think that would make any difference. What it would have done initially is to have avoided those issues with using the wrong datatype for the parameters, but you've fixed all that.

    The thing that I would focus on now is the '1' that FD was mentioning. I believe he is correct in saying that you put the breakpoint on the wrong line and are getting the error in the Immediate window because you stopped before the parameters had been supplied. Getting the current SQL would probably be informative, because the formatting would be correct. However, '1' is simply not a valid date, so if that has now become #1#, then that is certainly a problem. Whether or not it is the only problem is hard to say, but the rest looks pretty good by this point. Getting that SQL output would be key, though.
    My usual boring signature: Nothing

  2. #42

    Thread Starter
    Addicted Member ashveen's Avatar
    Join Date
    Sep 2013
    Location
    Sri Lanka
    Posts
    141

    Re: Data Type Mismatch in criteria expression

    Quote Originally Posted by Shaggy Hiker View Post
    I have nothing even close to that. I do have code that does inserts into databases, and what you are doing is not all that far off. In fact, if I put the snippets together, it doesn't look all that bad. I'd change from Parameters.Add to Parameters.AddWithValue, as I showed in post #30, but at this point, I don't think that would make any difference. What it would have done initially is to have avoided those issues with using the wrong datatype for the parameters, but you've fixed all that.

    The thing that I would focus on now is the '1' that FD was mentioning. I believe he is correct in saying that you put the breakpoint on the wrong line and are getting the error in the Immediate window because you stopped before the parameters had been supplied. Getting the current SQL would probably be informative, because the formatting would be correct. However, '1' is simply not a valid date, so if that has now become #1#, then that is certainly a problem. Whether or not it is the only problem is hard to say, but the rest looks pretty good by this point. Getting that SQL output would be key, though.
    I'm sorry. I do not know how to find the SQL of that. Can you please tell me that in simple steps so that I could understand.

  3. #43
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    Re: Data Type Mismatch in criteria expression

    What I meant about getting the SQL is exactly what you did for post #23. Repeat those steps to get the current SQL. It should be a fair amount different from what you posted in #23, because fields will have changed to reflect the different data types you are using.
    My usual boring signature: Nothing

  4. #44

    Thread Starter
    Addicted Member ashveen's Avatar
    Join Date
    Sep 2013
    Location
    Sri Lanka
    Posts
    141

    Re: Data Type Mismatch in criteria expression

    Quote Originally Posted by Shaggy Hiker View Post
    What I meant about getting the SQL is exactly what you did for post #23. Repeat those steps to get the current SQL. It should be a fair amount different from what you posted in #23, because fields will have changed to reflect the different data types you are using.
    I got rid of that error and now I'm getting another one
    " Number of query values and destination fields are not the same "

    this is the current coding i'm using

    Code:
    Private Sub SaveButton1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SaveButton1.Click
            Dim promptQuestion As DialogResult = MessageBox.Show("Are you sure you want to save this record?", "Save Record", MessageBoxButtons.YesNo, MessageBoxIcon.Question)
            If promptQuestion = Windows.Forms.DialogResult.No Then
                Exit Sub
            End If
            If Len(Trim(Employee_NameTextBox.Text)) = 0 Then
                MessageBox.Show("Please enter employee name", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                Employee_NameTextBox.Focus()
            End If
            If Len(Trim(Last_NameTextBox.Text)) = 0 Then
                MessageBox.Show("Please enter last name", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                Last_NameTextBox.Focus()
            End If
            If Len(Trim(GenderComboBox.Text)) = 0 Then
                MessageBox.Show("Please enter Gender", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                GenderComboBox.Focus()
            End If
            If Len(Trim(BirthDatePicker.Value)) = 0 Then
                MessageBox.Show("Please enter Birth Date", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                BirthDatePicker.Focus()
            End If
            If Len(Trim(Contact_NumberTextBox.Text)) = 0 Then
                MessageBox.Show("Please enter Contact Number", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                Contact_NumberTextBox.Focus()
            End If
            If Len(Trim(AddressTextBox.Text)) = 0 Then
                MessageBox.Show("Please enter Address", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                AddressTextBox.Focus()
            End If
            If Len(Trim(EmailTextBox.Text)) = 0 Then
                MessageBox.Show("Please enter Email", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                EmailTextBox.Focus()
            End If
            If Len(Trim(DesignationComboBox.Text)) = 0 Then
                MessageBox.Show("Please enter Designation", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                DesignationComboBox.Focus()
            End If
            If Len(Trim(DateEmployedPicker.Value)) = 0 Then
                MessageBox.Show("Please enter Date Employed", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                DateEmployedPicker.Focus()
            End If
            If Len(Trim(Basic_PayTextBox.Text)) = 0 Then
                MessageBox.Show("Please enter Basic Salary", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                Basic_PayTextBox.Focus()
            End If
            If Len(Trim(StatusComboBox.Text)) = 0 Then
                MessageBox.Show("Please enter Status", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                StatusComboBox.Focus()
            End If
    
            AutoGenerate()
    
            con = New OleDbConnection(cs)
            con.Open()
    
            Dim cb As String = "Insert INTO Staff_Details(DepartmentCode,EmployeeCode,EmployeeName,LastName,Gender,BirthDate,ContactNumber,Address,Email,Designation,DateEmployed,BasicSalary,Status) VALUES (@d1,@d2,@d3,@d4,@d5,@d6,@d7,@d8,@d9,@d10,@d11)"
    
            cmd = New OleDbCommand(cb)
    
            cmd.Connection = con
    
            cmd.Parameters.AddWithValue("@d1", "DepartmentCode")
            cmd.Parameters.AddWithValue("@d2", "EmployeeCode")
            cmd.Parameters.AddWithValue("@d3", "EmployeeName")
            cmd.Parameters.AddWithValue("@d4", "LastName")
            cmd.Parameters.AddWithValue("@d5", "Gender")
            cmd.Parameters.AddWithValue("@d6", "BirthDate")
            cmd.Parameters.AddWithValue("@d7", "ContactNumber")
            cmd.Parameters.AddWithValue("@d8", "Address")
            cmd.Parameters.AddWithValue("@d9", "Email")
            cmd.Parameters.AddWithValue("@d10", "Designation")
            cmd.Parameters.AddWithValue("@d11", "DateEmployed")
            cmd.Parameters.AddWithValue("@d12", "BasicSalary")
            cmd.Parameters.AddWithValue("@d13", "Status")
    
    
            cmd.Parameters("@d1").Value = DepartmentCodeTextBox2.Text.Trim
            cmd.Parameters("@d2").Value = EmployeeCodeTextBox.Text.Trim
            cmd.Parameters("@d3").Value = Employee_NameTextBox.Text.Trim
            cmd.Parameters("@d4").Value = Last_NameTextBox.Text.Trim
            cmd.Parameters("@d5").Value = GenderComboBox.Text.Trim
            cmd.Parameters("@d6").Value = CDate(BirthDatePicker.Text.Trim)
            cmd.Parameters("@d7").Value = Contact_NumberTextBox.Text.Trim
            cmd.Parameters("@d8").Value = AddressTextBox.Text.Trim
            cmd.Parameters("@d9").Value = EmailTextBox.Text.Trim
            cmd.Parameters("@d10").Value = DesignationComboBox.Text.Trim
            cmd.Parameters("@d11").Value = CDate(DateEmployedPicker.Text.Trim)
            cmd.Parameters("@d12").Value = CDec(Basic_PayTextBox.Text.Trim)
            cmd.Parameters("@d13").Value = StatusComboBox.Text.Trim
    
    
            cmd.ExecuteNonQuery()
            MessageBox.Show("Record successfully saved.")
            SaveButton1.Enabled = False
            EmployeeCodeTextBox.Focus()
            con.Close()
        End Sub

  5. #45
    Super Moderator Shaggy Hiker's Avatar
    Join Date
    Aug 2002
    Location
    Idaho
    Posts
    38,988

    Re: Data Type Mismatch in criteria expression

    You don't need any of this:

    cmd.Parameters("@d1").Value = DepartmentCodeTextBox2.Text.Trim
    cmd.Parameters("@d2").Value = EmployeeCodeTextBox.Text.Trim
    cmd.Parameters("@d3").Value = Employee_NameTextBox.Text.Trim
    cmd.Parameters("@d4").Value = Last_NameTextBox.Text.Trim
    cmd.Parameters("@d5").Value = GenderComboBox.Text.Trim
    cmd.Parameters("@d6").Value = CDate(BirthDatePicker.Text.Trim)
    cmd.Parameters("@d7").Value = Contact_NumberTextBox.Text.Trim
    cmd.Parameters("@d8").Value = AddressTextBox.Text.Trim
    cmd.Parameters("@d9").Value = EmailTextBox.Text.Trim
    cmd.Parameters("@d10").Value = DesignationComboBox.Text.Trim
    cmd.Parameters("@d11").Value = CDate(DateEmployedPicker.Text.Trim)
    cmd.Parameters("@d12").Value = CDec(Basic_PayTextBox.Text.Trim)
    cmd.Parameters("@d13").Value = StatusComboBox.Text.Trim

    You already added the value in the AddWithValue statement. Adding a second time may cause trouble, though it shouldn't. It's unnecessary, though. The whole point of AddWithValue is that it ads the parameter and the value in a single line rather than two lines as you were doing it.

    However, that's not the cause of the error. The cause is right here:

    VALUES (@d1,@d2,@d3,@d4,@d5,@d6,@d7,@d8,@d9,@d10,@d11)

    Do you see it? How many parameters are you supplying? How many fields do you have?
    My usual boring signature: Nothing

  6. #46

    Thread Starter
    Addicted Member ashveen's Avatar
    Join Date
    Sep 2013
    Location
    Sri Lanka
    Posts
    141

    Re: Data Type Mismatch in criteria expression

    [QUOTE=Shaggy Hiker;4624783]You don't need any of this:

    Code:
        Private Sub SaveButton1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles SaveButton1.Click
            Dim promptQuestion As DialogResult = MessageBox.Show("Are you sure you want to save this record?", "Save Record", MessageBoxButtons.YesNo, MessageBoxIcon.Question)
            If promptQuestion = Windows.Forms.DialogResult.No Then
                Exit Sub
            End If
            If Len(Trim(Employee_NameTextBox.Text)) = 0 Then
                MessageBox.Show("Please enter employee name", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                Employee_NameTextBox.Focus()
            End If
            If Len(Trim(Last_NameTextBox.Text)) = 0 Then
                MessageBox.Show("Please enter last name", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                Last_NameTextBox.Focus()
            End If
            If Len(Trim(GenderComboBox.Text)) = 0 Then
                MessageBox.Show("Please enter Gender", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                GenderComboBox.Focus()
            End If
            If Len(Trim(BirthDatePicker.Value)) = 0 Then
                MessageBox.Show("Please enter Birth Date", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                BirthDatePicker.Focus()
            End If
            If Len(Trim(Contact_NumberTextBox.Text)) = 0 Then
                MessageBox.Show("Please enter Contact Number", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                Contact_NumberTextBox.Focus()
            End If
            If Len(Trim(AddressTextBox.Text)) = 0 Then
                MessageBox.Show("Please enter Address", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                AddressTextBox.Focus()
            End If
            If Len(Trim(EmailTextBox.Text)) = 0 Then
                MessageBox.Show("Please enter Email", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                EmailTextBox.Focus()
            End If
            If Len(Trim(DesignationComboBox.Text)) = 0 Then
                MessageBox.Show("Please enter Designation", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                DesignationComboBox.Focus()
            End If
            If Len(Trim(DateEmployedPicker.Value)) = 0 Then
                MessageBox.Show("Please enter Date Employed", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                DateEmployedPicker.Focus()
            End If
            If Len(Trim(Basic_PayTextBox.Text)) = 0 Then
                MessageBox.Show("Please enter Basic Salary", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                Basic_PayTextBox.Focus()
            End If
            If Len(Trim(StatusComboBox.Text)) = 0 Then
                MessageBox.Show("Please enter Status", "Input Error", MessageBoxButtons.OK, MessageBoxIcon.Error)
                StatusComboBox.Focus()
            End If
    
            AutoGenerate()
    
            con = New OleDbConnection(cs)
            con.Open()
    
            Dim cb As String = "Insert INTO Staff_Details(DepartmentCode,EmployeeCode,EmployeeName,LastName,Gender,BirthDate,ContactNumber,Address,Email,Designation,DateEmployed,BasicSalary,Status) VALUES (@d1,@d2,@d3,@d4,@d5,@d6,@d7,@d8,@d9,@d10,@d11)"
    
            cmd = New OleDbCommand(cb)
    
            cmd.Connection = con
    
            cmd.Parameters.Add(New OleDbParameter("@d1", OleDbType.VarChar, 4, "DepartmentCode"))
            cmd.Parameters.Add(New OleDbParameter("@d2", OleDbType.VarChar, 5, "EmployeeCode"))
            cmd.Parameters.Add(New OleDbParameter("@d3", OleDbType.VarChar, 20, "EmployeeName"))
            cmd.Parameters.Add(New OleDbParameter("@d4", OleDbType.VarChar, 20, "LastName"))
            cmd.Parameters.Add(New OleDbParameter("@d5", OleDbType.VarChar, 6, "Gender"))
            cmd.Parameters.Add(New OleDbParameter("@d6", OleDbType.Date, 30, "BirthDate"))
            cmd.Parameters.Add(New OleDbParameter("@d7", OleDbType.VarChar, 11, "ContactNumber"))
            cmd.Parameters.Add(New OleDbParameter("@d8", OleDbType.VarChar, 100, "Address"))
            cmd.Parameters.Add(New OleDbParameter("@d9", OleDbType.VarChar, 50, "Email"))
            cmd.Parameters.Add(New OleDbParameter("@d10", OleDbType.VarChar, 15, "Designation"))
            cmd.Parameters.Add(New OleDbParameter("@d11", OleDbType.Date, 30, "DateEmployed"))
            cmd.Parameters.Add(New OleDbParameter("@d12", OleDbType.VarChar, 10, "BasicSalary"))
            cmd.Parameters.Add(New OleDbParameter("@d13", OleDbType.VarChar, 15, "Status"))
    
            cmd.Parameters("@d1").Value = DepartmentCodeTextBox2.Text
            cmd.Parameters("@d2").Value = EmployeeCodeTextBox.Text
            cmd.Parameters("@d3").Value = Employee_NameTextBox.Text
            cmd.Parameters("@d4").Value = Last_NameTextBox.Text
            cmd.Parameters("@d5").Value = GenderComboBox.Text
            cmd.Parameters("@d6").Value = BirthDatePicker.Value
            cmd.Parameters("@d7").Value = Contact_NumberTextBox.Text
            cmd.Parameters("@d8").Value = AddressTextBox.Text
            cmd.Parameters("@d9").Value = EmailTextBox.Text
            cmd.Parameters("@d10").Value = DesignationComboBox.Text
            cmd.Parameters("@d11").Value = DateEmployedPicker.Value
            cmd.Parameters("@d12").Value = Basic_PayTextBox.Text
            cmd.Parameters("@d13").Value = StatusComboBox.Text
    
    
            cmd.ExecuteNonQuery()
            MessageBox.Show("Record successfully saved.")
            SaveButton1.Enabled = False
            EmployeeCodeTextBox.Focus()
            con.Close()
    End Sub
    I used the above code and I still get the same error
    Number of query values and destination fields are not the same

  7. #47
    PowerPoster
    Join Date
    Oct 2008
    Location
    Midwest Region, United States
    Posts
    3,574

    Re: Data Type Mismatch in criteria expression

    As Shaggy pointed out, this line:

    Code:
    Dim cb As String = "Insert INTO Staff_Details(DepartmentCode,EmployeeCode,EmployeeName,LastName,Gender,BirthDate,ContactNumber,Address,Email,Designation,DateEmployed,BasicSalary,Status) VALUES (@d1,@d2,@d3,@d4,@d5,@d6,@d7,@d8,@d9,@d10,@d11)"
    only goes from "@d1" to "@d11" and it appears you're trying to insert 13 values.

  8. #48

    Thread Starter
    Addicted Member ashveen's Avatar
    Join Date
    Sep 2013
    Location
    Sri Lanka
    Posts
    141

    Re: Data Type Mismatch in criteria expression

    FINALLY...it worked!!!!
    I want to thank each and everyone who helped me.
    after hours and hours staring at the computer I finally came to the finale of my project.
    If it weren't for you guys and this forum I wouldn't accomplish my goal.
    Thank You!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!!

Page 2 of 2 FirstFirst 12

Tags for this Thread

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