select and insert

[ven]

Hello

i have two tables in my MS SQL named table1 and table2. In each table i have
a column PRICE with my own datatype decimal 14;4 allows nulls....
now i wanna select from one table to second table and i can do it
here is my code :

select * from table1

dim variable = datareader("PRICE")

insert into [table2] ([price]) values ('" + variable "')

i have following error :


Server Error in '/sklep' Application.
----------------------------------------------------------------------------
----

Input string was not in a correct format.
Description: An unhandled exception occurred during the execution of the
current web request. Please review the stack trace for more information
about the error and where it originated in the code.

Exception Details: System.FormatException: Input string was not in a correct
format.

Source Error:

Line 189: Dim dbConnection1 As System.Data.IDbConnection = New
System.Data.SqlClient.SqlConnection(connectionString)
Line 190: dim czas as datetime =datetime.now
Line 191: Dim queryString1 As String = "INSERT INTO
[zamowienie_intak] ([numer_zamowienia], [data_zamowienia], [id_towar],
[ilosc_towar],[cena_netto],[cena_brutto], [vat], [jednostka]) VALUES ('" +
numer_zam + "','" + today + "', '" + com + "', 1 ,'" + cena_netto_zam +
"','" + cena_brutto_zam +"','" + vat_zam +"','" + jednostka_zam +"')"
Line 192: Dim dbCommand1 As System.Data.IDbCommand = New
System.Data.SqlClient.SqlCommand
Line 193: dbCommand1.CommandText = queryString1

Source File: C:\Inetpub\wwwroot\sklep\search.aspx Line: 191

Stack Trace:

[FormatException: Input string was not in a correct format.]
System.Number.StringToNumber(String str, NumberStyles options,
NumberBuffer& number, NumberFormatInfo info, Boolean parseDecimal) +2179309
System.Number.ParseDouble(String value, NumberStyles options,
NumberFormatInfo numfmt) +97
System.Double.Parse(String s, NumberStyles style, NumberFormatInfo info)
+189
Microsoft.VisualBasic.CompilerServices.Conversions.ParseDouble(String
Value, NumberFormatInfo NumberFormat) +213
Microsoft.VisualBasic.CompilerServices.Conversions.ToDouble(String Value,
NumberFormatInfo NumberFormat) +78

[InvalidCastException: Conversion from string "INSERT INTO
[zamowienie_intak] (" to type 'Double' is not valid.]
Microsoft.VisualBasic.CompilerServices.Conversions.ToDouble(String Value,
NumberFormatInfo NumberFormat) +224
Microsoft.VisualBasic.CompilerServices.Operators.AddObject(Object Left,
Object Right) +2736
ASP.search_aspx.AddToCart(Object Src, CommandEventArgs Args) in
C:\Inetpub\wwwroot\sklep\search.aspx:191
System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +111
System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument)
+288

System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePo
stBackEvent(String eventArgument) +5
System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler
sourceControl, String eventArgument) +11
System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +31
System.Web.UI.Page.ProcessRequestMain(Boolean
includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5157

 

[Lucas Tam]

insert into [table2] ([price]) values ('" + variable "')

What format is price in?

Also, what you don't you do a

INSERT INTO table2
select * from table1

No need for a datareader

Or you can use a SELECT INTO as well.

 

[ven]

insert into [table2] ([price]) values ('" + variable "')

What format is price in?

Also, what you don't you do a

INSERT INTO table2
select * from table1

No need for a datareader

Or you can use a SELECT INTO as well.

table1 is in database1 and table2 is in other database
price have decimal format 14;4 in MS SQL Server

 

[Lucas Tam]

table1 is in database1 and table2 is in other database
price have decimal format 14;4 in MS SQL Server

Did you try removing the single quotes from your query string? It is a
decimal and not string right?


Since the data is in 2 different database, you may want to use a Bulk
Insert. Bulk Inserts are much faster than looping each record and
inserting.