SQL Insert query for database

Ok this is long explanation so thank you for your time. Ok I have 2 forms One asks for an ISBN number the number is searched from a "bookCache database" and if found populated which is in the first portion of code below. This works fine, What I would like to do to more on this is to search a website online for the isbn and retrieve the book info from a database and then send that back to the book Cache database which then populates the form. The extra portion which I have supplied code for is the new book form which receives the populated book - title, isbn, description etc..  My problem is when i fill in the extra boxes (condition, price) into the text boxes
and press the button to insert the data and I get an "object reference not set to an instance of an object" system.Null reference Exception.  I have provided my database code as well which has a method that returns an BookID which I'm not sure I want I just want the inserting of data. I also have a memberID which will be associated to each member when entered into the database. This is an association from my member class and I'm not sure if its coded properly in the constructor. That code is here as well... Any tips.. help.. would be great primarily on the insert statement after my form is populated from the bookCache. Thanks for your time !


This is the first form the scan book isbn add method

Open in new window

private void btnAdd_Click(object sender, EventArgs e)
        {            
            frmNewItem frm = new frmNewItem(LookupBook(txtEnterISBN.Text));
                frm.ShowDialog();
           
        }
        private MemberBook LookupBook(string ISBNNumber)
        {
            MemberBook mb = new MemberBook();
            mb.ISBNNumber = ISBNNumber;
            //Query for your book info
            try
            {              
                SqlConnection con = new SqlConnection("Data Source=localhost\\SqlExpress;Initial Catalog=MMABooks;" +
                   "Integrated Security=True");
                {
                    con.Open();
                    DataTable book = new DataTable();
                    var adapter = new SqlDataAdapter(String.Format("SELECT Title, Author, Description FROM Books WHERE ISBNNumber = '{0}'", mb.ISBNNumber), con);
                    adapter.Fill(book);

                    if (book.Rows.Count > 0)
                    {
                        mb.Title = book.Rows[0]["Title"].ToString();
                        mb.Author = book.Rows[0]["Author"].ToString();
                        mb.Description = book.Rows[0]["Description"].ToString();
                       
                    }
                    con.Close();
                }
            }
            catch (Exception e)
            {

               
            }
            return mb;
        }
    }

Open in new window



this is the second form the add book form

Open in new window

public partial class frmNewItem : Form
    {
        public MemberBook mb = new MemberBook();


        public frmNewItem(MemberBook mb)
        {
            InitializeComponent();
            //this.MemberBook = mb;
            this.Text = String.Format("Book Info for ISBN {0}", mb.ISBNNumber);
            txtISBNNumber.Text = mb.ISBNNumber;
            txtAuthor.Text = mb.Author;
            txtTitle.Text = mb.Title;
            txtDescription.Text = mb.Description;
        }

        private bool IsValidData()
        {
            return Validator.IsPresent(txtTitle) &&
              Validator.IsPresent(txtAuthor) &&
              Validator.IsPresent(txtISBNNumber) &&
              Validator.IsPresent(txtDescription) &&
              Validator.IsPresent(txtCondition) &&
              Validator.IsPresent(txtPrice) &&
              Validator.IsDecimal(txtPrice);
        }
        private void PutMemberBookData(MemberBook memberBook)
        {
            //mb.Member.MemberID = Convert.ToInt32(txtMemberID.Text);
            mb.Title = txtTitle.Text;
            mb.Author = txtAuthor.Text;
            mb.ISBNNumber = txtISBNNumber.Text;
            mb.Condition = txtCondition.Text;
            mb.Description = txtISBNNumber.Text;
            mb.Price = Convert.ToDecimal(txtPrice.Text);
        }


        private void btnCancel_Click(object sender, EventArgs e)
        {
            this.Close();

        }

        private void btnSave_Click(object sender, EventArgs e)
        {
            if (IsValidData())
            {
                {
                    mb = new MemberBook();
                    this.PutMemberBookData(mb);
                    try
                    {
                        mb.Member.MemberID = MemberBookInventoryDB.AddMemberBook(mb);
                        this.DialogResult = DialogResult.OK;
                    }
                    catch (Exception ex)
                    {
                        MessageBox.Show(ex.Message, ex.GetType().ToString());
                    }
                }
            }
        }
    }
}

Open in new window


My database to insert the book into the database from the previous form

 public static int AddMemberBook(MemberBook mb)
        {
            SqlConnection connection = MMABooksDB.GetConnection();
            string insertStatement =
                "INSERT MemberBookInventory " +
                "(Title, Author, ISBNNumber, Description, Condition, Price, MemberID, BookID) " +
                "VALUES (@Title, @Author, @ISBNNumber, @Description @Condition, @Price, @MemberID, @BookID)";
            SqlCommand insertCommand =
                new SqlCommand(insertStatement, connection);
            insertCommand.Parameters.AddWithValue(
                "@Title", mb.Title);
            insertCommand.Parameters.AddWithValue(
                "@Author", mb.Author);
            insertCommand.Parameters.AddWithValue(
                "@ISBNNumber", mb.ISBNNumber);
            insertCommand.Parameters.AddWithValue(
                "@Description", mb.Description);
            insertCommand.Parameters.AddWithValue(
                "@Condition", mb.Condition);
            insertCommand.Parameters.AddWithValue(
                "@Price", mb.Price);
            insertCommand.Parameters.AddWithValue(
                "@MemberID", mb.Member.MemberID);
            insertCommand.Parameters.AddWithValue(
                "@BookD", mb.BookID);
            try
            {
                connection.Open();
                insertCommand.ExecuteNonQuery();
                string selectStatement =
                    "SELECT IDENT_CURRENT('MemberBookInventory') FROM MemberBookInventory";
                SqlCommand selectCommand =
                    new SqlCommand(selectStatement, connection);
                int bookID = Convert.ToInt32(selectCommand.ExecuteScalar());
                return bookID;
            }
       
            catch (SqlException ex)
            {
                throw ex;
            }
            finally
            {
                connection.Close();
            }
        }

Open in new window



And finally the member code and the member book form

Open in new window

public class MemberBook : Book

    {
        private int bookID;
        private Member member { get; set; }

        public MemberBook() { }
       
        public MemberBook(String bookId, string title, string author, string iSBNNumber, string condition,string  description, decimal price)
            : base (title, author, iSBNNumber, condition, description, price)
        {
            this.BookID = bookID;
            this.Member = member;
        }

        public int BookID
        {
            get
            {
                return bookID;
            }
            set
            {
                bookID = value;
            }
        }

        public Member Member
        {
            get
            {
                return member;
            }
            set
            {
                member = value;
            }
        }
   
    }

Open in new window


member

Open in new window

   public class Member
    {
        private int memberID;    
        private string firstName;
        private string lastName;
        private string email;
        private int pinNumber;
      //  private Transaction transaction { get; set; }

        public Member(int memberID, String firstName, String lastName, String email, int pinNumber)
        {
            this.MemberID = memberID;        
            this.FirstName = firstName;
            this.LastName = lastName;
            this.Email = email;
            this.PinNumber = pinNumber;
            //Transaction = null;

        }

        public Member() { }

Open in new window



linkcube1Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Carl TawnSystems and Integration DeveloperCommented:
It looks like you never initialise the Member object of your MemberBook for new items, so when you try and assign an ID to it there is nothing there. Try changing the default constructor of MemberBook from:
public MemberBook() { }

Open in new window

To:
public MemberBook() 
{ 
      this.Member = new Member();
}

Open in new window

0
linkcube1Author Commented:
Thanks Carl, It inserts my data into the database But memberID stays at 0  and ISBN is used as description.
Screen-shot-2011-04-01-at-8.54.0.png
0
Carl TawnSystems and Integration DeveloperCommented:
You'll need to run through your code an make sure that the right form fields are populating the right properties of your MemberBook object. The code looks ok, so it is likely to be a problem mapping your fields to your objects properties.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Upgrade your Question Security!

Your question, your audience. Choose who sees your identity—and your question—with question security.

tpaynCommented:
Further to what carl_tawn has said you mapping the description field to txtISBNNumber in the   PutMemberBookData function. Also you have commented out the MemberID assignment.
0
linkcube1Author Commented:
Yeah I fixed the description issue, and when the put memberbook data function is used I get the following error since memberID is 0.. Any help how to get the value from the reference? It should be passing from form to form..
Capture1.PNG
0
tpaynCommented:
Looks like you planned to have MemberID as a Identity Field in SQL if i look at your btnSave_Click -> AddMemberBook functions. If that is the case then remove the MemberId from the Insert Statement and dont assign it in the PutMemberBookData  cause you will be assigning it in the save routine.

If you are going to use the MemberId as an Identity field then it propably will be a better idea to put your insert statement into a stored procedure and return @@IDENTITY from the stored procedure.
0
linkcube1Author Commented:
Actually, I want the user to type in their memberID in the field, I checked SQL and its not an identity field, so I think my put memberbook data is appropiate any other suggestions?
0
linkcube1Author Commented:
Problem solved for some reason a label was txtMemberID.... goodness.. thanks for the help
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
C#

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.