Actioning Insert Sql with parameters using .net

[blounty]

Hi guys i have been working on this for a few hours and my brain is now a block of concrete. i am using the following code:

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click

        Try

       
            Dim sqlstring As String

            Dim Connection As New OleDb.OleDbConnection
            Connection.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=|DataDirectory|\Devices.mdb;Persist Security Info=True;Jet OLEDB:Database Password=crapbag"

            'Combo_Model.Items.Clear()


            sqlstring = "INSERT INTO NewDevs (EntryNo, Building, Floor,Dept, DevRef, Serial, Asset, MonoUsage, ColUsage, CompanyRef, Owned, Leased, PurchDate, PurchPrice, LeaseStartDate, LeaseTerm, LeaseRental, MonCpp, ColCpp, SMonCpp, SColCpp, ServChg) VALUES (@Entry, @Building, @Floor, @Dept, @DevRef, @Serial, @Asset, @MonoUsage, @ColUsage, @CompanyRef, @Owned, @Leased, @PurchDate, @PurchPrice, @LeaseStartDate, @LeaseTerm, @LeaseRental, @MonCpp, @ColCpp, @SMonCpp, @SColCpp, @ServChg)"

            Dim cmd As New OleDb.OleDbCommand(sqlstring, Connection)

            cmd.Parameters.AddWithValue("@Entry", SqlDbType.Int).Value = Me.Entry_txt.Text
            cmd.Parameters.AddWithValue("@Building", SqlDbType.NVarChar).Value = Me.Combo_Build.SelectedItem
            cmd.Parameters.AddWithValue("@Floor", SqlDbType.NVarChar).Value = Me.Combo_Floor.SelectedItem
            cmd.Parameters.AddWithValue("@Dept", SqlDbType.NVarChar).Value = Me.Combo_Dept.SelectedItem
            cmd.Parameters.AddWithValue("@DevRef", SqlDbType.Int).Value = Me.DevRef_txt.Text
            cmd.Parameters.AddWithValue("@Serial", SqlDbType.NVarChar).Value = Me.Serial_txt.Text
            cmd.Parameters.AddWithValue("@Asset", SqlDbType.NVarChar).Value = Me.Asset_txt.Text
            cmd.Parameters.AddWithValue("@MonoUsage", SqlDbType.Int).Value = Me.MonCalc_txt.Text
            cmd.Parameters.AddWithValue("@ColUsage", SqlDbType.Int).Value = Me.ColCalc_txt.Text
            cmd.Parameters.AddWithValue("@CompanyRef", SqlDbType.Int).Value = Me.Comref_txt.Text
            cmd.Parameters.AddWithValue("@Owned", SqlDbType.NVarChar).Value = Me.RadioButton_Owned.Checked.ToString
            cmd.Parameters.AddWithValue("@Leased", SqlDbType.NVarChar).Value = Me.RadioButton_Owned.Checked.ToString
            cmd.Parameters.AddWithValue("@PurchDate", SqlDbType.DateTime).Value = Me.Mid_txt.Text
            cmd.Parameters.AddWithValue("@PurchPrice", SqlDbType.Int).Value = Me.Purch_txt.Text
            cmd.Parameters.AddWithValue("@LeaseStartDate", SqlDbType.DateTime).Value = Me.Start_txt.Text
            cmd.Parameters.AddWithValue("@LeaseTerm", SqlDbType.Int).Value = Me.LeaseTerm_txt.Text
            cmd.Parameters.AddWithValue("@LeaseRental", SqlDbType.Int).Value = Me.LeaseRental_txt.Text
            cmd.Parameters.AddWithValue("@MonCpp", SqlDbType.Int).Value = Me.MonCpp_txt.Text
            cmd.Parameters.AddWithValue("@ColCpp", SqlDbType.Int).Value = Me.ColCpp_txt.Text
            cmd.Parameters.AddWithValue("@SMonCpp", SqlDbType.Int).Value = Me.ServMono_txt.Text
            cmd.Parameters.AddWithValue("@SColCpp", SqlDbType.Int).Value = Me.ServCol_txt.Text
            cmd.Parameters.AddWithValue("@ServChg", SqlDbType.Int).Value = Me.ServChg_txt.Text

            cmd.Connection.Open()
            cmd.ExecuteNonQuery()
            cmd.Connection.Close()

            Me.Entry_txt.Clear()
        Catch ex As Exception
            MsgBox(ex.ToString)
        End Try


    End Sub

my catch is getting the a data type mismatch exception at line 207 which is...

cmd.ExecuteNonQuery()

any ideas guys i am at your mercy!!!

Thanks All

A

 

[pmegan]

It sounds like one of your parameters is trying to pass a data type that the db isn't expecting. I'm always suspicious of internal string-to-date conversions. Try converting the Purchase Date and Lease Date textbox values to dates before assigning them to the parameters.

Also, you're using an OleDb connection and SqlDbType parameter definitions. Try using OleDbType instead.

Pat