user3580480 user3580480 - 2 months ago 16
SQL Question

Passing values from NumericUpDown control to SQL database

I have some field in my table formatted with four decimal places (i.e. 0.0125).

I am using a NumericUpDown object in Visual Basic to collect numbers which I then want to pass to my database.

Unfortunately when I run my code I get the following error:


Conversion from string " (CallType,ChargeCode,Destinatio" to type 'Double' is not valid.


Here is a copy of the code I am using to push the values to the database.

Using cmdc = New SqlCommand("INSERT INTO " & TextBox2.Text
& " (CallType,
ChargeCode,
Destination,
TariffUsed,
(case when chargecode in ('" + Combobox2.text + "') then '" + NumericUpDown1.value + "'
else Peak*2 end) as Peak,
(case when chargecode in ('" + Combobox2.text + "') then '" + NumericUpDown2.value + "'
else OffPeak*2 end) as OffPeak,
(case when chargecode in ('" + Combobox2.text + "') then '" + NumericUpDown3.value + "'
else Weekend*2 end) as Weekend,
(case when chargecode in ('" + Combobox2.text + "') then '" + NumericUpDown4.value + "'
else Setup*2 end) as Setup,
(case when chargecode in ('" + Combobox2.text + "') then '" + NumericUpDown5.value + "'
else MinimumCharge*2 end) as MinimumCharge,
ChargeCap,
InitialUnits,
InitialCharge,
InitialPeak,
InitialOffPeak,
InitialWeekend,
BillingUnit,
MinimumUnits,
RateType
FROM " & ComboBox1.Text & "", con)

con.Open()
cmdc.ExecuteNonQuery()
con.Close()
End Using
End Using


As DOUBLE doesn't exist in SQL, I have the destination fields configured as FLOAT.

Is it possible to pass values from the NumericUpDown into my database, and if so what is the correct format?

Answer

Big thanks to @Paqogomez:

The ToString() fixed the issue.

Here is the working code:

Using cmdc = New SqlCommand("Insert into " & TextBox2.Text & " Select CallType,ChargeCode,Destination,TariffUsed,(case when chargecode in ('" + ComboBox2.Text + "') then " + NumericUpDown1.Value.ToString() + " else Peak*2 end) as Peak,(case when chargecode in ('" + ComboBox2.Text + "') then " + NumericUpDown2.Value.ToString() + " else OffPeak*2 end) as OffPeak,(case when chargecode in ('" + ComboBox2.Text + "') then " + NumericUpDown3.Value.ToString() + " else Weekend*2 end) as Weekend,(case when chargecode in ('" + ComboBox2.Text + "') then " + NumericUpDown4.Value.ToString() + " else Setup*2 end) as Setup,(case when chargecode in ('" + ComboBox2.Text + "') then " + NumericUpDown5.Value.ToString() + " else MinimumCharge*2 end) as MinimumCharge,ChargeCap,InitialUnits,InitialCharge,InitialPeak,InitialOffPeak,InitialWeekend,BillingUnit,MinimumUnits,RateType from Daisy_March2014", con)
con.Open()
cmdc.Parameters.AddWithValue("@tarrif2", TextBox2.Text)
cmdc.Parameters.AddWithValue("@DateTime", DateTime.Now())
cmdc.ExecuteNonQuery()
con.Close()
End Using
End Using

*Use at your own risk, should really be parametized!