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
        Next

        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

        Try



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

            If studPicPath = "N/A" Then
                svpath = studPicPath
            Else
                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
                    enc.Frames.Add(BitmapFrame.Create(bmp))
                    enc.Save(fs)
                    fs.Close()
                    fs.Dispose()
                End Using
            End If

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

            Conn.Open()

            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

            Cmd.ExecuteNonQuery()
            Cmd.Dispose()

            Me.Cursor = c

        Catch ex As Exception
            MessageBox.Show(ex.Message.ToString())
        Finally
            Conn.Close()
            Conn.Dispose()
        End Try

        'TO CLEAR ALL OBJECTS
        Call Me.ClearData()
    End Sub

Any help should be appreciable to resolve my problem .

Recommended Answers

All 2 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, 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")
Else
    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 "?".

commented: I'm grateful to you for all your help. +5

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.
Thanks.

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

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.