Click here to Skip to main content
13,732,461 members
Rate this:
 
Please Sign up or sign in to vote.
I am creating a LMS in BV.Net and SQL Server. The book adding form contains text boxes combo boxes and picture box. When I click the save button It says the following error message

    Conversion failed when converting the varchar value 'Saman' to data type int. 

On the form load the combo boxes populate the data from the entire tables.

[Tables and relationship][1] <br/>
[Data to be inserted][2] <br/>
[Tables with data type][3] <br/>
[Error message on saving][4] <br/>

  [1]: https://i.stack.imgur.com/bVEHH.png
  [2]: https://i.stack.imgur.com/NVEGX.png
  [3]: https://i.stack.imgur.com/uArlL.png
  [4]: https://i.stack.imgur.com/7jUm1.png


What I have tried:

I tried with the following coding

Form load coding

    Private Sub frmAddBook_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load

        'To load Author details
        Using com As New SqlCommand("SELECT * FROM tblBookAuthor", con)

            Dim dt As New DataTable()
            dt.Load(com.ExecuteReader)
            cmbAuthor.DataSource = dt
            cmbAuthor.DisplayMember = "authorFirstName"
            cmbAuthor.ValueMember = "authorFirstName"
            ' con.Close()
        End Using
        
		'To load Publisher details
        Using com2 As New SqlCommand("SELECT * FROM tblPublisher", con)

            Dim dt As New DataTable()
            dt.Load(com2.ExecuteReader)
            cmbPub.DataSource = dt
            cmbPub.DisplayMember = "publisherName"
            cmbPub.ValueMember = "publisherName"

            ' con.Close()
        End Using
        
        'To load Class details
        Using com3 As New SqlCommand("SELECT * FROM tblBookClass", con)

            Dim dt As New DataTable()
            dt.Load(com3.ExecuteReader)
            cmbClass.DataSource = dt
            cmbClass.DisplayMember = "classDetail"
            cmbClass.ValueMember = "classDetail"
            ' con.Close()
        End Using
        
		'To load bookshelf details
        Using com4 As New SqlCommand("SELECT * FROM tblBookShelfDetail", con)

            Dim dt As New DataTable()
            dt.Load(com4.ExecuteReader)
            cmbBookShelf.DataSource = dt
            cmbBookShelf.DisplayMember = "shelfDetail"
            cmbBookShelf.ValueMember = "shelfDetail"
            con.Close()
        End Using
        
    End Sub


Save button coding

    Private Sub btnSave_Click(sender As Object, e As EventArgs) Handles btnSave.Click

        sql = "INSERT INTO tblBookDetail (accessionNo, authorId, title, pages, price, ISBN, noOfCopies, pubId, classId, source, bookShelfNo,bookEdition, bookCoverImg) VALUES (@accNo, @auID, @title, @pages, @price, @ISBN, @noOfCopies, @pubID, @classID, @src, @shlfNo, @edition, @img)"

        Dim ms As New MemoryStream()
        picPhoto.Image.Save(ms, picPhoto.Image.RawFormat)

        con.Open()
        cmd = New SqlCommand(sql, con)

        cmd.Parameters.Add("@accNo", SqlDbType.Int).Value = txtBookID.Text
        cmd.Parameters.Add("@auID", SqlDbType.VarChar).Value = cmbAuthor.Text
        cmd.Parameters.Add("@title", SqlDbType.VarChar).Value = txtEdition.Text
        cmd.Parameters.Add("@pages", SqlDbType.Int).Value = txtPageNo.Text
        cmd.Parameters.Add("@price", SqlDbType.Decimal).Value = txtPrice.Text
        cmd.Parameters.Add("@ISBN", SqlDbType.Int).Value = txtISBN.Text
        cmd.Parameters.Add("@noOfCopies", SqlDbType.Int).Value = txtNoOfCopies.Text
        cmd.Parameters.Add("@pubID", SqlDbType.VarChar).Value = cmbPub.Text
        cmd.Parameters.Add("@classID", SqlDbType.VarChar).Value = cmbClass.Text
        cmd.Parameters.Add("@src", SqlDbType.VarChar).Value = cmbSrc.Text
        cmd.Parameters.Add("@shlfNo", SqlDbType.VarChar).Value = cmbBookShelf.Text
        cmd.Parameters.Add("@edition", SqlDbType.Int).Value = txtEdition.Text

        cmd.Parameters.Add("@img", SqlDbType.Image).Value = ms.ToArray

        cmd.ExecuteNonQuery()

        MsgBox("SUCCESS")
        con.Close()

    End Sub
Posted 10-Nov-17 17:40pm
Updated 11-Nov-17 0:43am

1 solution

Rate this: bad
 
good
Please Sign up or sign in to vote.

Solution 1

You are assigning the .Text property of the combobox (i.e. the DisplayMember) to the @auID parameter that goes to tblBookDetail.authorId;
cmd.Parameters.Add("@auID", SqlDbType.VarChar).Value = cmbAuthor.Text

what you want to assign is the numeric Id of the author that is in the ValueMember. So you need to access the ValueMember of the selected item, try
cmd.Parameters.Add("@auID", SqlDbType.VarChar).Value = cmbAuthor.SelectedValue 
  Permalink  
Comments
Karthik Bangalore 12-Nov-17 20:54pm
   
5

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

  Print Answers RSS
Top Experts
Last 24hrsThis month


Advertise | Privacy | Cookies | Terms of Service
Web05-2016 | 2.8.180920.1 | Last Updated 11 Nov 2017
Copyright © CodeProject, 1999-2018
All Rights Reserved.
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100