Fetching Data From SQL Server Giving Error

Question

tom_289 0 Light Poster

13 Years Ago

Code Giving Error is Given Below, and Error is

1) Make Sure your method arguments are in right format
2) When converting a string to DateTime, prase the string to take the date before putting each variable into the DateTime Object

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace WindowsFormsApplication6
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        private void button1_Click(object sender, EventArgs e)
        {
            SqlConnection cs = new SqlConnection("Data Source = raaj-pc; Initial Catalog = BookSellerNescom; Integrated Security = True");
          /*  cs.Open();
            MessageBox.Show(cs.State.ToString());
            cs.Close(); */
            SqlDataAdapter da = new SqlDataAdapter();
            da.InsertCommand = new SqlCommand("INSERT INTO tblBooks VALUES(@Book_ID, @Title, @ISBN_No, @Author, @Publisher, @Date_Published, @Available, @Price)", cs);
            da.InsertCommand.Parameters.Add("@Book_ID", SqlDbType.Int).Value = textBox1.Text;
            da.InsertCommand.Parameters.Add("@Title", SqlDbType.VarChar).Value = textBox2.Text;
            da.InsertCommand.Parameters.Add("@ISBN_No", SqlDbType.NChar).Value = textBox3.Text;
            da.InsertCommand.Parameters.Add("@Author", SqlDbType.VarChar).Value = textBox4.Text;
            da.InsertCommand.Parameters.Add("@Publisher", SqlDbType.VarChar).Value = textBox5.Text;
            da.InsertCommand.Parameters.Add("@Date_Published", SqlDbType.Date).Value = textBox6.Text;
            da.InsertCommand.Parameters.Add("@Available", SqlDbType.Bit).Value = textBox7.Text;
            da.InsertCommand.Parameters.Add("@Price", SqlDbType.Money).Value = textBox8.Text;

            cs.Open();
            da.InsertCommand.ExecuteNonQuery();
            cs.Close();



        }

        
    }
}

4 Contributors
15 Replies
217 Views
5 Months Discussion Span
Latest Post 13 Years Ago Latest Post by jignesh kaila

kvprajapati 1,826 Posting Genius

13 Years Ago

May be this will help you.

private void button1_Click(object sender, EventArgs e)
        {
            SqlConnection cs = new SqlConnection("Data Source = raaj-pc; Initial Catalog = BookSellerNescom; Integrated Security = True");
          
            SqlCommand da;
            da = new SqlCommand("INSERT INTO tblBooks VALUES(@Book_ID, @Title, @ISBN_No, @Author, @Publisher, @Date_Published, @Available, @Price)", cs);
            da.Parameters.Add("@Book_ID", SqlDbType.Int).Value = textBox1.Text;
            da.Parameters.Add("@Title", SqlDbType.VarChar).Value = textBox2.Text;
            da.Parameters.Add("@ISBN_No", SqlDbType.NChar).Value = textBox3.Text;
            da.Parameters.Add("@Author", SqlDbType.VarChar).Value = textBox4.Text;
            da.Parameters.Add("@Publisher", SqlDbType.VarChar).Value = textBox5.Text;
            da.Parameters.Add("@Date_Published", SqlDbType.DateTime).Value = DateTime.Parse(textBox6.Text);
            da.Parameters.Add("@Available", SqlDbType.Bit).Value = textBox7.Text;
            da.Parameters.Add("@Price", SqlDbType.Money).Value = textBox8.Text;

            cs.Open();
            da.ExecuteNonQuery();
            cs.Close();
        }

Please point a line number where your got an exception (Error).

Mitja Bonca 557 Nearly a Posting Maven

13 Years Ago

This is the parameter you have issues, right?:

da.Parameters.Add("@Available", SqlDbType.Bit).Value = textBox7.Text;

Bit can only have "true" or "false" values in th database.
What is your value in textBox7?

Its you have 1 or 0 do a conversation to true or false.

EDIT:
and instead of textBox6, in which you have date, you better use dateTimePicker ot monthCalendar. In this case there will never come to any inconveniances in capturing the right date value.

Edited 13 Years Ago by Mitja Bonca because: added some info

Mitja Bonca 557 Nearly a Posting Maven

13 Years Ago

This error might occur becuase you attempted to insert a row containing a specific identity value into a table that contains an identity column.

Now, you must specify the column list of the Product column when u use the IDENTITY_INSERT ON.

Check for the colution here:http://www.sql-server-helper.com/error-messages/msg-8101.aspx

Mitja Bonca 557 Nearly a Posting Maven

13 Years Ago

Look at this example here, it might help you out:
http://social.msdn.microsoft.com/forums/en-US/sqlce/thread/9a536345-c90a-462b-b378-cb4e62533f41/

Reply to this topic

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.

tom_289 0 Light Poster · Answer 1 · 2011-01-05T14:31:25+00:00

Wrong Heading sorry,

basically i am INSERTING data using Windows form to SQL Database

thanks

tom_289 0 Light Poster · Answer 2 · 2011-01-05T18:33:27+00:00

tried it but its giving same Error

its line 17

" da.ExecuteNonQuery();"

tom_289 0 Light Poster · Answer 3 · 2011-01-05T18:42:43+00:00

i checked the detail and it saying this,

"{"Failed to convert parameter value from a String to a Boolean."}"

tom_289 0 Light Poster · Answer 4 · 2011-01-05T19:11:07+00:00

the data type for field date_published in SQL database is just "Date" --> not DateTime...
i used

tom_289 0 Light Poster · Answer 5 · 2011-01-05T21:22:36+00:00

thanks for replies...

i made the changes and got another different error now on same Row,

"An explicit value for the identity column in table 'tblBooks' can only be specified when a column list is used and IDENTITY_INSERT is ON."

tom_289 0 Light Poster · Answer 6 · 2011-01-05T21:35:06+00:00

heres code again with updates

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using System.Data.SqlClient;

namespace WindowsFormsApplication6
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }
        private void button1_Click(object sender, EventArgs e)
        {
            SqlConnection cs = new SqlConnection("Data Source = raaj-pc; Initial Catalog = BookSellerNescom; Integrated Security = True");

            SqlCommand da;
            da = new SqlCommand("INSERT INTO tblBooks VALUES(@Book_ID, @Title, @ISBN_No, @Author, @Publisher, @Date_Published, @Available, @Price)", cs);
            da.Parameters.Add("@Book_ID", SqlDbType.Int).Value = textBox1.Text;
            da.Parameters.Add("@Title", SqlDbType.VarChar).Value = textBox2.Text;
            da.Parameters.Add("@ISBN_No", SqlDbType.NChar).Value = textBox3.Text;
            da.Parameters.Add("@Author", SqlDbType.VarChar).Value = textBox4.Text;
            da.Parameters.Add("@Publisher", SqlDbType.VarChar).Value = textBox5.Text;
            da.Parameters.Add("@Date_Published", SqlDbType.DateTime).Value = dateTimePicker1.Text;
            da.Parameters.Add("@Available", SqlDbType.Bit).Value = textBox7.Text;
            da.Parameters.Add("@Price", SqlDbType.Money).Value = textBox8.Text;

            cs.Open();
            
            //SET IDENTITY_INSERT <tablename> ON
            da.ExecuteNonQuery();
            cs.Close();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            // TODO: This line of code loads data into the 'booksellerNescomDataSet.tblBooks' table. You can move, or remove it, as needed.
            this.tblBooksTableAdapter.Fill(this.booksellerNescomDataSet.tblBooks);

        }        
    }
}

tom_289 0 Light Poster · Answer 7 · 2011-01-05T21:38:33+00:00

i was trying "SET IDENTITY_INSERT <tablename> ON"
but it aint coming i mean not a valid statement which can be used in SQL query...
buh i dunt kno hw to use it in VS 2008 :/

tom_289 0 Light Poster · Answer 8 · 2011-01-05T21:48:05+00:00

i aint a advance programmer... buh i solved it by NOT inserting Primary key book_ID into the table as its automatically being assigned !
but i dunt kno how i will gonna
display and delete data.....
i mean i dunt kno the commands...
any help or syntax wud b appreciated

cheers

Mitja Bonca 557 Nearly a Posting Maven · Answer 9 · 2011-01-05T22:35:11+00:00

What exactly do you mean?
Here is some info about using commands:
http://www.daniweb.com/code/snippet303280.html

tom_289 0 Light Poster · Answer 10 · 2011-01-06T09:44:37+00:00

thanks Alot for HELP guyz :) i solved it
cheers

jignesh kaila 0 Newbie Poster · Answer 11 · 2011-06-09T17:47:24+00:00

cmd = new SqlCommand();
                    da = new SqlDataAdapter();
                    da.InsertCommand = cmd;
                    da.InsertCommand.Parameters.Add("@AdminID", SqlDbType.VarChar).Value = textBox1.Text;
                    da.InsertCommand.Parameters.Add("@FirstName", SqlDbType.VarChar).Value = textBox2.Text;
                    da.InsertCommand.Parameters.Add("@MiddleName", SqlDbType.VarChar).Value = textBox3.Text;
                    da.InsertCommand.Parameters.Add("@LastName", SqlDbType.VarChar).Value = textBox4.Text;
                    da.InsertCommand.Parameters.Add("@Address", SqlDbType.VarChar).Value = textBox6.Text;
                    da.InsertCommand.Parameters.Add("@BirthDate", SqlDbType.DateTime).Value = textBox7.Text;
                    da.InsertCommand.Parameters.Add("@ParentName", SqlDbType.VarChar).Value = textBox8.Text;
                    da.InsertCommand.Parameters.Add("@PhoneNumber", SqlDbType.VarChar).Value = textBox9.Text;
                    da.InsertCommand.Parameters.Add("@ParentProfession", SqlDbType.VarChar).Value = textBox10.Text;
                    da.InsertCommand.Parameters.Add("@ParentDesignation", SqlDbType.VarChar).Value = textBox11.Text;
                    da.InsertCommand.Parameters.Add("@ParentEmailID", SqlDbType.VarChar).Value = textBox12.Text;
                    da.InsertCommand.Parameters.Add("@OfficeAddress", SqlDbType.VarChar).Value = textBox13.Text;
                    da.InsertCommand.Parameters.Add("@OfficePhoneNumber", SqlDbType.VarChar).Value = textBox14.Text;
                    da.InsertCommand.Parameters.Add("@MobileNumber", SqlDbType.VarChar).Value = textBox15.Text;
                    da.InsertCommand.Parameters.Add("@Qualification", SqlDbType.VarChar).Value = textBox16.Text;
                    da.InsertCommand.Parameters.Add("@AdminImage", SqlDbType.VarChar).Value = imagepath;
                    da.InsertCommand.Connection = con;
                    da.InsertCommand.CommandText = "insert into Admin(AdminID,FirstName,MiddleName,LastName,Address,BirthDate,ParentName,PhoneNumber,ParentProfession,ParentDesignation,ParentEmailID,OfficeAddress,OfficePhoneNumber,MobileNumber,Qualification,AdminImage) values(@AdminID,@FirstName,@MiddleName,@LastName,@Address,@BirthDate,@ParentName,@PhoneNumber,@ParentProfession,@ParentDesignation,@ParentEmailID,@OfficeAddress,@OfficePhoneNumber,@MobileNumber,@Qualification,@AdminImage)";
                    if (con.State != ConnectionState.Open)
                    {
                        con.Open();
                    }
                    da.InsertCommand.ExecuteNonQuery();
                    con.Close();
                    cmd.Dispose();
                    MessageBox.Show("You Inserted Admin BioData Entered");