Good morning members.

I am now in a small project on request of a friend for his school.

This a WPF with VB.Net 2010 project with .accdb database.
when I am trying to insert a blank data to the database only entering Student Name and Roll No and others are blank, it shows an error message "Parameter ?_14 has no default value".
Parameter ?_14 stands for the parameter @YrPassing.
But when I am trying to insert a fully filed up data to the database(No TextBox remains blank) it shows an error "Data Type mismatch in criteria expression".
I do not understand or point out why this type of behaviour showing by the table and I am unable to resolve this problem. Every fields are Text Type except dates and numerics are as double type.
The general module codes are

Imports System
Imports System.Data.OleDb
Imports System.Reflection

Module ModuleMain

    Public ConnString As String = "Provider=Microsoft.ACE.OLEDB.12.0;Data Source=|DataDirectory|\ByteInfotech.accdb;Persist Security Info=False;"
    Public appPath As String = System.IO.Path.GetDirectoryName(System.Diagnostics.Process.GetCurrentProcess().MainModule.FileName)
    Public picPath As String = IIf(Right(appPath, 1) <> "\", appPath & "\ImageFiles\", appPath & "ImageFiles\")

    Public Structure StudInfo
        Dim Studnm As String
        Dim StudEnroll As String
        Dim pic As String
        Dim sttus As String
    End Structure

    Public Structure StudAttnd
        Dim Studnm As String
        Dim StudEnroll As String
        Dim pic As String
        Dim sttus As String
        Dim crsnm As String
        Dim dtsdul As String
        Dim tmsdul As String
        Dim atntm As Date
        Dim dpttm As Date
    End Structure

    Public Function SearchComboIndex(obj As ComboBox, key As String) As Integer
        Dim Result As Integer = -1

        For i As Integer = 0 To obj.Items.Count - 1
            If obj.Items(i) = key Then
                result = i
                Exit For
            End If

        Return Result
    End Function
End Module

and the save button codes are

    Private Sub SaveNew()
        'checking validation only for student name and rollno
        If Me.ValidateValues = False Then
            Exit Sub
        End If

        Dim Conn As System.Data.OleDb.OleDbConnection = New System.Data.OleDb.OleDbConnection(ConnString)
        Dim Cmd As New System.Data.OleDb.OleDbCommand


            If Not System.IO.Directory.Exists(picPath) Then
            End If

            If studPicPath = "N/A" Then
                svpath = studPicPath
                svpath = safePicName(0) & ".png"

                Dim bmp As BitmapImage = imgStudent.Source

                Using fs As New FileStream(picPath & safePicName(0) & ".png", FileMode.Create)
                    Dim enc As New PngBitmapEncoder
                End Using
            End If

            Dim c As Cursor = Me.Cursor
            Me.Cursor = Cursors.Wait


            Cmd.CommandType = System.Data.CommandType.Text
            Cmd.CommandText = "Insert Into Student (RollNo, AdmDate, StudentName, DOB, Address, Contactno1, Contactno2, " & _
                              "GurdianName, Relation, OffAdd, GrdMobNo, LastExam, BoardName, YrPassing, MrksObtnd, " & _
                              "KidsSchName, KidsClass, KidsAge, CourseName, Duration, DayNos, DayNames, TimeSchedule, " & _
                              "CurrentStatus, CourseAmount, FstRcptNo, FstPayAmt, picture) " & _
                              "Values(?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?)"

            Cmd.Parameters.AddWithValue("@RollNo", Me.txtStuRollNo.Text)
            Cmd.Parameters.AddWithValue("@AdmDate", FormatDateTime(Me.admdtBox.SelectedDate, DateFormat.ShortDate))
            Cmd.Parameters.AddWithValue("@StudentName", Me.txtStuName.Text)
            Cmd.Parameters.AddWithValue("@DOB", FormatDateTime(Me.dobdtBox.SelectedDate, DateFormat.ShortDate))

            IIf(String.IsNullOrWhiteSpace(Me.txtAdd.Text) = False, Cmd.Parameters.AddWithValue("@Address", "N/A"), Cmd.Parameters.AddWithValue("@Address", Me.txtAdd.Text))

            IIf(String.IsNullOrWhiteSpace(Me.txtcontNo1.Text) = False, Cmd.Parameters.AddWithValue("@Contactno1", "N/A"), Cmd.Parameters.AddWithValue("@Contactno1", Me.txtcontNo1.Text))

            IIf(String.IsNullOrWhiteSpace(Me.txtcontNo2.Text) = False, Cmd.Parameters.AddWithValue("@Contactno2", "N/A"), Cmd.Parameters.AddWithValue("@Contactno2", Me.txtcontNo2.Text))

            IIf(String.IsNullOrWhiteSpace(Me.txtGudName.Text) = False, Cmd.Parameters.AddWithValue("@GurdianName", "N/A"), Cmd.Parameters.AddWithValue("@GurdianName", Me.txtGudName.Text))

            IIf(Me.cmbRelation.SelectedItem = Nothing, Cmd.Parameters.AddWithValue("@Relation", "N/A"), Cmd.Parameters.AddWithValue("@Relation", Me.cmbRelation.SelectedItem))

            IIf(Me.cmbOccup.SelectedItem = Nothing, Cmd.Parameters.AddWithValue("@occup", "N/A"), Cmd.Parameters.AddWithValue("@Occup", Me.cmbOccup.SelectedItem))

            IIf(String.IsNullOrWhiteSpace(Me.txtOffAdd.Text) = False, Cmd.Parameters.AddWithValue("@OffAdd", "N/A"), Cmd.Parameters.AddWithValue("@OffAdd", Me.txtOffAdd.Text))

            IIf(String.IsNullOrWhiteSpace(Me.txtGrdMobNo.Text) = False, Cmd.Parameters.AddWithValue("@GrdMobNo", "N/A"), Cmd.Parameters.AddWithValue("@GrdMobNo", Me.txtGrdMobNo.Text))

            IIf(String.IsNullOrWhiteSpace(Me.txtLastExam.Text) = False, Cmd.Parameters.AddWithValue("@LastExam", "N/A"), Cmd.Parameters.AddWithValue("@LastExam", Me.txtLastExam.Text))

            IIf(String.IsNullOrWhiteSpace(Me.txtBrdUnv.Text) = False, Cmd.Parameters.AddWithValue("@BoardName", "N/A"), Cmd.Parameters.AddWithValue("@BoardName", Me.txtBrdUnv.Text))

            IIf(String.IsNullOrWhiteSpace(Me.txtYrPass.Text) = False, Cmd.Parameters.AddWithValue("@YrPassing", "N/A"), Cmd.Parameters.AddWithValue("@YrPassing", Me.txtYrPass.Text))

            IIf(String.IsNullOrWhiteSpace(Me.txtMrksObtnd.Text) = False, Cmd.Parameters.AddWithValue("@MrksObtnd", "N/A"), Cmd.Parameters.AddWithValue("@MrksObtnd", Me.txtMrksObtnd.Text))

            IIf(String.IsNullOrWhiteSpace(Me.txtKidSchName.Text) = False, Cmd.Parameters.AddWithValue("@KidsSchName", "N/A"), Cmd.Parameters.AddWithValue("@KidsSchName", Me.txtKidSchName.Text))

            IIf(String.IsNullOrWhiteSpace(Me.txtKidClass.Text) = False, Cmd.Parameters.AddWithValue("@KidsClass", "N/A"), Cmd.Parameters.AddWithValue("@KidsClass", Me.txtKidClass.Text))

            IIf(String.IsNullOrWhiteSpace(Me.txtKidsAge.Text) = False, Cmd.Parameters.AddWithValue("@KidsAge", "N/A"), Cmd.Parameters.AddWithValue("@KidsAge", Me.txtKidsAge.Text))

            IIf(Me.cmbCrName.SelectedItem = Nothing, Cmd.Parameters.AddWithValue("@CourseName", "N/A"), Cmd.Parameters.AddWithValue("@CourseName", Me.cmbCrName.SelectedItem))

            IIf(Me.cmbDuration.SelectedItem = Nothing, Cmd.Parameters.AddWithValue("@Duration", "N/A"), Cmd.Parameters.AddWithValue("@Duration", Me.cmbDuration.SelectedItem))

            IIf(Me.cmbwkdays.SelectedItem = Nothing, Cmd.Parameters.AddWithValue("@DayNos", "N/A"), Cmd.Parameters.AddWithValue("@DayNos", Me.cmbwkdays.SelectedItem))

            IIf(Me.cmbdaynm.SelectedItem = Nothing, Cmd.Parameters.AddWithValue("@DayNames", "N/A"), Cmd.Parameters.AddWithValue("@DayNames", Me.cmbdaynm.SelectedItem))

            IIf(Me.cmbStEndTime.SelectedItem = Nothing, Cmd.Parameters.AddWithValue("@TimeSchedule", "N/A"), Cmd.Parameters.AddWithValue("@TimeSchedule", Me.cmbStEndTime.SelectedItem))

            IIf(Me.cmbStStstus.SelectedItem = Nothing, Cmd.Parameters.AddWithValue("@CurrentStatus", "N/A"), Cmd.Parameters.AddWithValue("@CurrentStatus", Me.cmbStStstus.SelectedItem))

            IIf(Val(Me.txtcrFees.Text) = 0, Cmd.Parameters.AddWithValue("@CourseAmount", 0.0), Cmd.Parameters.AddWithValue("@CourseAmount", Val(Me.txtcrFees.Text)))

            IIf(String.IsNullOrWhiteSpace(Me.txtRcptNo.Text) = False, Cmd.Parameters.AddWithValue("@FstRcptNo", "N/A"), Cmd.Parameters.AddWithValue("@FstRcptNo", Me.txtRcptNo.Text))

            IIf(Val(Me.txtPayAmt.Text) = 0, Cmd.Parameters.AddWithValue("@FstPayAmt", 0.0), Cmd.Parameters.AddWithValue("@FstPayAmt", Val(Me.txtPayAmt.Text)))

            Cmd.Parameters.AddWithValue("@picture", svpath)

            Cmd.Connection = Conn


            Me.Cursor = c

        Catch ex As Exception
        End Try

        Call Me.ClearData()
    End Sub

Any help should be appreciable to resolve my problem .

Attachments Untitled.png 18.28 KB Untitled2.png 16.92 KB Untitled1.png 89.01 KB
1 Year
Discussion Span
Last Post by Shark_1
Featured Replies
  • You are not using IIF correctly. You can not execute statements within an IIF. For example Dim a As Integer = 1 Dim b As Integer = 2 Dim c As Integer = 3 IIf(a = 1, b = 7, b = 8) b will still have the value 2, … Read More


You are not using IIF correctly. You can not execute statements within an IIF. For example

Dim a As Integer = 1
Dim b As Integer = 2
Dim c As Integer = 3

IIf(a = 1, b = 7, b = 8)

b will still have the value 2, however, if you do

b = IIf(a = 1, 7, 8)

then b will have the value 7. You could rewrite your statements as

If Me.cmbwkdays.SelectedItem = Nothing) Then
    Cmd.Parameters.AddWithValue("@DayNos", "N/A")
    Cmd.Parameters.AddWithValue("@DayNos", Me.cmbwkdays.SelectedItem)
End If

or you could do

Dim temp As String = IIf(Me.cmbwkdays.SelectedItem = Nothing, "N/A", Me.cmbwkdays.SelectedItem))
Cmd.Parameters.AddWithValue("@DayNos", temp)

Also, I counted twice but it looks like you have 29 field names and 29 AddWithValue statements but only 28 "?".

Edited by Reverend Jim

Votes + Comments
I'm grateful to you for all your help.

Thanks @Jim, for your most valuable suggestion. Repeated mistake in 29 lines should be most disgraceful work. I forgot that IIF statement always returns a value.Please Pardon me for my greate mistakes.
Repeatedly, I'm grateful to you for all your help.

Oh! sorry. I totally forgot.
Presently how are you after returning from your painful situation.
Enjoy a good life.

This question has already been answered. Start a new discussion instead.
Have something to contribute to this discussion? Please be thoughtful, detailed and courteous, and be sure to adhere to our posting rules.