ASP.net Insert query error

Question

rambok4 0 Newbie Poster

14 Years Ago

try
Dim da As String = "+100acs+"

            Dim con As SqlConnection = New SqlConnection("Data Source=RAHUL-034890AF0\SERVER2005;Initial Catalog=user_accounts; Integrated Security =True")

            Dim command As Data.SqlClient.SqlCommand = New Data.SqlClient.SqlCommand("Insert into sales_details (bill_number,product_id,quantity,rate,amount) VALUES ('" + da + " ','" + Ddpproducts.SelectedItem.ToString + " ','" + Textquant.Text + "','" + Ddpproducts.SelectedValue + "','" + 2306 + "')", con)

 

            command.BeginExecuteNonQuery()

 

            con.Close()

 

        Catch [x] As Exception

 

            Txterror.text = x

 

 

 

       

End Try

I get the following error in my error textbox:

System.InvalidCastException: Conversion from string "Insert into sales_details (bill_" to type 'Double' is not valid. ---> System.FormatException: Input string was not in a correct format. at Microsoft.VisualBasic.CompilerServices.Conversions.ParseDouble(String Value, NumberFormatInfo NumberFormat) at Microsoft.VisualBasic.CompilerServices.Conversions.ToDouble(String Value, NumberFormatInfo NumberFormat) --- End of inner exception stack trace --- at Microsoft.VisualBasic.CompilerServices.Conversions.ToDouble(String Value, NumberFormatInfo NumberFormat) at Microsoft.VisualBasic.CompilerServices.Conversions.ToDouble(String Value) at _Default.btnadd_Click(Object sender, EventArgs e) in D:\sms_new\salesbill.aspx.vb:line 71

My bill number field is nvarchar(10) in sql2005 and even if I replace da with numbers or alphabets (eg– 123, abc)I still get and error.

asp.net sql

3 Contributors
8 Replies
341 Views
3 Days Discussion Span
Latest Post 14 Years Ago Latest Post by rohand

rohand 1 Posting Whiz in Training

14 Years Ago

try
Dim da As String = "+100acs+"

            Dim con As SqlConnection = New SqlConnection("Data Source=RAHUL-034890AF0\SERVER2005;Initial Catalog=user_accounts; Integrated Security =True")

            Dim command As Data.SqlClient.SqlCommand = New Data.SqlClient.SqlCommand("Insert into sales_details (bill_number,product_id,quantity,rate,amount) VALUES ('" + da + " ','" + Ddpproducts.SelectedItem.ToString + " ','" + Textquant.Text + "','" + Ddpproducts.SelectedValue + "','" + 2306 + "')", con)

 

            command.BeginExecuteNonQuery()

 

            con.Close()

 

        Catch [x] As Exception

 

            Txterror.text = x

 

 

 

       

End Try
I get the following error in my error textbox:
System.InvalidCastException: Conversion from string "Insert into sales_details (bill_" to type 'Double' is not valid. ---> System.FormatException: Input string was not in a correct format. at Microsoft.VisualBasic.CompilerServices.Conversions.ParseDouble(String Value, NumberFormatInfo NumberFormat) at Microsoft.VisualBasic.CompilerServices.Conversions.ToDouble(String Value, NumberFormatInfo NumberFormat) --- End of inner exception stack trace --- at Microsoft.VisualBasic.CompilerServices.Conversions.ToDouble(String Value, NumberFormatInfo NumberFormat) at Microsoft.VisualBasic.CompilerServices.Conversions.ToDouble(String Value) at _Default.btnadd_Click(Object sender, EventArgs e) in D:\sms_new\salesbill.aspx.vb:line 71
My bill number field is nvarchar(10) in sql2005 and even if I replace da with numbers or alphabets (eg– 123, abc)I still get and error.

I think the error is not because of da or your bill_number column. I am not sure but it may be because of rate or amount you are inserting. what "2306" in your insert statement ? what the data type of quantity, rate and amount in your table ?

KushMishra 38 Senior Technical Lead

14 Years Ago

If I'm not wrong then I think You've missed the con.open();

Hope this helps........

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.

rambok4 0 Newbie Poster · Answer 1 · 2010-05-08T20:19:02+00:00

As per the stack trace it says problem with bill number...
I am using money for rate, amount & integer for quantity..
Besides i have put validators on the page to ensure only integer values are excepted...
2306 is a random value i was trying to insert in the table to test my connection

rohand 1 Posting Whiz in Training · Answer 2 · 2010-05-10T18:20:57+00:00

As per the stack trace it says problem with bill number...
I am using money for rate, amount & integer for quantity..
Besides i have put validators on the page to ensure only integer values are excepted...
2306 is a random value i was trying to insert in the table to test my connection

no dude- the problem is not with "da", i have checked in sample application. what the data type of Product_id in sql table ?

rambok4 0 Newbie Poster · Answer 3 · 2010-05-10T20:48:01+00:00

Dim command As Data.SqlClient.SqlCommand = New Data.SqlClient.SqlCommand("Insert into sales_details (bill_number,product_id,quantity, rate, amount, customer_name,customer_address) VALUES ('" + Label13.Text + "','" + Ddpproducts.SelectedItem.ToString + "','" + Textquant.Text + "','" + Ddpproducts.SelectedValue + "','" + (Textquant.Text * Ddpproducts.SelectedValue).ToString + "','" + Txtname.Text + "','" + Txtaddr.Text + "')", con)

Just to refresh, consider the above code for a sales bill page.
I have used money datatype for rate & amount, nvarchar for product_id & bill_number and integer for quantity.

If it makes things any better to solve, in another page add customer i used the following queries:

'Dim command As Data.SqlClient.SqlCommand = New Data.SqlClient.SqlCommand("Insert into customers (membership_id,name,membership_date,membership_type,dob,gender,address,mobile,telephone1,telephone2,discountdeals,seasonsales,newarrivals,specialoffers)values('" + Textmemid.Text + "', '" + Textnam.Text + "','" + BasicDatePicker1.SelectedDate + "','" + ddtype.SelectedValue + "','" + BasicDatePicker2.SelectedDate + "','" + RadioButton3.Checked + "','" + Txtadd.Text + "'.'" + Txtmob.Text + "','" + Texttel.Text + "','" + Textoff.Text + "', '" + Checkdd.Checked + "','" + Checkss.Checked + "','" + Checkna.Checked + "','" + Checkso.Checked + "'  ) ", con)
        Dim command As Data.SqlClient.SqlCommand = New Data.SqlClient.SqlCommand("Insert into customers (membership_id,name,membership_type,address)VALUES ('" + Textmemid.Text + "', '" + Textnam.Text + "','" + ddtype.SelectedValue + "','" + Txtadd.Text + "')", con)

While the shorter query works fine, it gives me error in the first one. It gives error wheneva i try to insert details of a checkbox or gender or type.
MY datatypes here are: nvarchar for customerid, employeeid & bit for the checkboxes and gender
& numeric for telephone numbers & datetime for dob, membership_date.

rohand 1 Posting Whiz in Training · Answer 4 · 2010-05-11T12:37:42+00:00

Dim command As Data.SqlClient.SqlCommand = New Data.SqlClient.SqlCommand("Insert into sales_details (bill_number,product_id,quantity, rate, amount, customer_name,customer_address) VALUES ('" + Label13.Text + "','" + Ddpproducts.SelectedItem.ToString + "','" + Textquant.Text + "','" + Ddpproducts.SelectedValue + "','" + (Textquant.Text * Ddpproducts.SelectedValue).ToString + "','" + Txtname.Text + "','" + Txtaddr.Text + "')", con)
Just to refresh, consider the above code for a sales bill page.
I have used money datatype for rate & amount, nvarchar for product_id & bill_number and integer for quantity.
If it makes things any better to solve, in another page add customer i used the following queries:
'Dim command As Data.SqlClient.SqlCommand = New Data.SqlClient.SqlCommand("Insert into customers (membership_id,name,membership_date,membership_type,dob,gender,address,mobile,telephone1,telephone2,discountdeals,seasonsales,newarrivals,specialoffers)values('" + Textmemid.Text + "', '" + Textnam.Text + "','" + BasicDatePicker1.SelectedDate + "','" + ddtype.SelectedValue + "','" + BasicDatePicker2.SelectedDate + "','" + RadioButton3.Checked + "','" + Txtadd.Text + "'.'" + Txtmob.Text + "','" + Texttel.Text + "','" + Textoff.Text + "', '" + Checkdd.Checked + "','" + Checkss.Checked + "','" + Checkna.Checked + "','" + Checkso.Checked + "'  ) ", con)
        Dim command As Data.SqlClient.SqlCommand = New Data.SqlClient.SqlCommand("Insert into customers (membership_id,name,membership_type,address)VALUES ('" + Textmemid.Text + "', '" + Textnam.Text + "','" + ddtype.SelectedValue + "','" + Txtadd.Text + "')", con)
While the shorter query works fine, it gives me error in the first one. It gives error wheneva i try to insert details of a checkbox or gender or type.
MY datatypes here are: nvarchar for customerid, employeeid & bit for the checkboxes and gender
& numeric for telephone numbers & datetime for dob, membership_date.

Okay. try to convert the value of check boxes to 1 or 0 by using Convert.ToInt16. because the reason is bit data type in sql holds 1/0 for true/false.
when you are using say Checkdd.Checked, it holds true/ false not 1/0. so your code looks like
Convert.ToInt16(Checkdd.Checked) and it will work for you.

rambok4 0 Newbie Poster · Answer 5 · 2010-05-11T13:52:21+00:00

thanx! would try and mark it as answer if it works.

It should work.

rohand 1 Posting Whiz in Training · Answer 6 · 2010-05-11T14:47:20+00:00

thanx! would try and mark it as answer if it works.
It should work.

is it work ?