Link to home
Start Free TrialLog in
Avatar of tobin46
tobin46

asked on

Add Record to DB - VB.Net

Hi:

I'm using SQL Server on the backend.  I keep getting an error when trying to insert record:

ERROR: InvalidCastException was unhandled - conversion from string "Object reference no set to an i" tp type 'Integer' is not valid.

Here is the code:
Private Sub Save_Slip_Header_Info()
        Try

            Dim sqlConnection_IEC As New SqlConnection(My.Settings.IEC_DB_Connection)
            Dim DA_Slip_Header As SqlDataAdapter
            DA_Slip_Header = New SqlDataAdapter("Select * from Slip_Info", sqlConnection_IEC)
            Dim DS_Slip_Header As DataSet = New DataSet
            Dim Row_Slip_Header As DataRow = DS_Slip_Header.Tables("Slip_Info").NewRow

            DA_Slip_Header.Fill(DS_Slip_Header)

            Row_Slip_Header("Slip_Number") = Me.txt_Slip_Number.Text
            Row_Slip_Header("Job_Number") = Me.txt_Job_Number.Text
            Row_Slip_Header("SWO_ID") = Me.txt_SWO_ID.Text
            Row_Slip_Header("Engineer_Last_Name") = Me.txt_Engineer.Text
            Row_Slip_Header("Contractor_Name") = Me.txt_contractor.Text
            Row_Slip_Header("Decription") = Me.txt_Description.Text
            Row_Slip_Header("Slip_Date") = Me.DatePick_Slip_Date.Value.ToString
            Row_Slip_Header("Date_Added") = Date.Today.ToString
            Row_Slip_Header("Date_Changed") = Date.Today.ToString
            Row_Slip_Header("CreatedBy") = "tgeorge"
            Row_Slip_Header("Location") = Me.txt_Location.Text

            DS_Slip_Header.Tables("Slip_Info").Rows.Add(Row_Slip_Header)

            DA_Slip_Header.Update(DS_Slip_Header, "Slip_Info")

            MsgBox("Row Added Sucessfully")

            'Clear all the textboxes
            Me.Clear_Textboxes()

        Catch ex As Exception

            Err.Raise(ex.Message)

        End Try


    End Sub

HERE is the table in designer:
Row_ID      numeric(18, 0)      Unchecked   <----Auto number field
Slip_Number      varchar(50)                           Unchecked
Job_Number      varchar(50)                           Unchecked
SWO_ID      varchar(50)                           Unchecked
Engineer_Last_Name      text      Checked
Contractor_Name      text      Checked
Description      text                          Checked
Slip_Date      varchar(50)                          Checked
Date_Added      varchar(50)                          Checked
CreatedBy      varchar(50)                            Checked
Date_Changed      varchar(50)      Checked
Location      varchar(200)      Checked
            Unchecked
Avatar of Tom Beck
Tom Beck
Flag of United States of America image
One or more of your database table columns is looking for an integer and you are providing a string. You can try casting the string as integer where necessary.

Row_Slip_Header("Slip_Number") = Convert.ToInt32(Me.txt_Slip_Number.Text)
Row_Slip_Header("Job_Number") = Convert.ToInt32(Me.txt_Job_Number.Text)
Avatar of Carlos Villegas
Carlos Villegas
Flag of United States of America image
The problem must be here:
Private Sub Save_Slip_Header_Info()
        Try

            Dim sqlConnection_IEC As New SqlConnection(My.Settings.IEC_DB_Connection)
            Dim DA_Slip_Header As SqlDataAdapter
            DA_Slip_Header = New SqlDataAdapter("Select * from Slip_Info", sqlConnection_IEC)
            Dim DS_Slip_Header As DataSet = New DataSet
            ' first you need to create this datatable instance into  DS_Slip_Header
            Dim dta as new DataTable("Slip_Info")
            DS_Slip_Header.Tables.Add(dta)
            Dim Row_Slip_Header As DataRow = dta.NewRow

            DA_Slip_Header.Fill(DS_Slip_Header)

            Row_Slip_Header("Slip_Number") = Me.txt_Slip_Number.Text
            Row_Slip_Header("Job_Number") = Me.txt_Job_Number.Text
            Row_Slip_Header("SWO_ID") = Me.txt_SWO_ID.Text
            Row_Slip_Header("Engineer_Last_Name") = Me.txt_Engineer.Text
            Row_Slip_Header("Contractor_Name") = Me.txt_contractor.Text
            Row_Slip_Header("Decription") = Me.txt_Description.Text
            Row_Slip_Header("Slip_Date") = Me.DatePick_Slip_Date.Value.ToString
            Row_Slip_Header("Date_Added") = Date.Today.ToString
            Row_Slip_Header("Date_Changed") = Date.Today.ToString
            Row_Slip_Header("CreatedBy") = "tgeorge"
            Row_Slip_Header("Location") = Me.txt_Location.Text

            DS_Slip_Header.Tables("Slip_Info").Rows.Add(Row_Slip_Header)

            DA_Slip_Header.Update(DS_Slip_Header, "Slip_Info")

            MsgBox("Row Added Sucessfully")

            'Clear all the textboxes
            Me.Clear_Textboxes()

        Catch ex As Exception

            Err.Raise(ex.Message)

        End Try


    End Sub

Open in new window

Avatar of Paul Jackson
Paul Jackson
Flag of United Kingdom of Great Britain and Northern Ireland image
You are passing a string into the err.raise call which expects an integer.

http://msdn.microsoft.com/en-us/library/ka13cy19(v=vs.80).aspx
SOLUTION
Avatar of Carlos Villegas
Carlos Villegas
Flag of United States of America image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of tobin46
tobin46

ASKER

@jacko72 - What integer do I pass?  
@yv989c - I modified code and that doesn't work....
@tommyBoy - none of my columns are looking for integers.

Could this have something to do with the field "Row_ID" which is a sql server autonumber field?
Avatar of Paul Jackson
Paul Jackson
Flag of United Kingdom of Great Britain and Northern Ireland image
I would change: err.raise(ex.message) to : throw ex
err.raise is left over from vb6 and shouldn't really be used IMHO
Avatar of tobin46
tobin46

ASKER

Thanks Jacko...I will experiment.  I changed to err.raise(6000, ex.message) so I can still gather details about the error.  I've updated code to yv989c's latest code and I get the error:

Update requires a valid InsertCommand when passed DataRow collection with new rows.

Thanks.
Avatar of tobin46
tobin46

ASKER

Jacko - I like the throw ex much better.  Thanks.
ASKER CERTIFIED SOLUTION
Avatar of Paul Jackson
Paul Jackson
Flag of United Kingdom of Great Britain and Northern Ireland image
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Carlos Villegas
Carlos Villegas
Flag of United States of America image
Can you try this?
    Private Sub Save_Slip_Header_Info()
        Try
            Dim sqlConnection_IEC As New SqlConnection(My.Settings.IEC_DB_Connection)
            Dim DA_Slip_Header As SqlDataAdapter
            DA_Slip_Header = New SqlDataAdapter("Select * from Slip_Info", sqlConnection_IEC)

            Dim InsertCommand As New SqlCommand
            InsertCommand.CommandText = "INSERT INTO Slip_Info " &
                "(Slip_Number, Job_Number, SWO_ID, Engineer_Last_Name, Contractor_Name, Description, Slip_Date, Date_Added, CreatedBy, Date_Changed, Location) " &
                "VALUES " &
                "(@Slip_Number, @Job_Number, @SWO_ID, @Engineer_Last_Name, @Contractor_Name, @Decription, @Slip_Date, @Date_Added, @CreatedBy, @Date_Changed, @Location)"

            InsertCommand.Parameters.Add("@Slip_Number", System.Data.SqlDbType.VarChar, 50)
            InsertCommand.Parameters.Add("@Job_Number", System.Data.SqlDbType.VarChar, 50)
            InsertCommand.Parameters.Add("@SWO_ID", System.Data.SqlDbType.VarChar, 50)
            InsertCommand.Parameters.Add("@Engineer_Last_Name", System.Data.SqlDbType.VarChar, 50)
            InsertCommand.Parameters.Add("@Contractor_Name", System.Data.SqlDbType.VarChar, 50)
            InsertCommand.Parameters.Add("@Decription", System.Data.SqlDbType.VarChar, 50)
            InsertCommand.Parameters.Add("@Slip_Date", System.Data.SqlDbType.VarChar, 50)
            InsertCommand.Parameters.Add("@Date_Added", System.Data.SqlDbType.VarChar, 50)
            InsertCommand.Parameters.Add("@CreatedBy", System.Data.SqlDbType.VarChar, 50)
            InsertCommand.Parameters.Add("@Date_Changed", System.Data.SqlDbType.VarChar, 50)
            InsertCommand.Parameters.Add("@Location", System.Data.SqlDbType.VarChar, 200)

            DA_Slip_Header.InsertCommand = InsertCommand

            Dim DS_Slip_Header As DataSet = New DataSet

            Dim dtaSlip_Info As New DataTable("Slip_Info")
            DS_Slip_Header.Tables.Add(dtaSlip_Info)

            DA_Slip_Header.TableMappings.Add("Table", "Slip_Info")
            DA_Slip_Header.Fill(DS_Slip_Header)

            Dim Row_Slip_Header As DataRow = dtaSlip_Info.NewRow
            Row_Slip_Header("Slip_Number") = Me.txt_Slip_Number.Text
            Row_Slip_Header("Job_Number") = Me.txt_Job_Number.Text
            Row_Slip_Header("SWO_ID") = Me.txt_SWO_ID.Text
            Row_Slip_Header("Engineer_Last_Name") = Me.txt_Engineer.Text
            Row_Slip_Header("Contractor_Name") = Me.txt_contractor.Text
            Row_Slip_Header("Decription") = Me.txt_Description.Text
            Row_Slip_Header("Slip_Date") = Me.DatePick_Slip_Date.Value.ToString
            Row_Slip_Header("Date_Added") = Date.Today.ToString
            Row_Slip_Header("Date_Changed") = Date.Today.ToString
            Row_Slip_Header("CreatedBy") = "tgeorge"
            Row_Slip_Header("Location") = Me.txt_Location.Text

            dtaSlip_Info.Rows.Add(Row_Slip_Header)

            DA_Slip_Header.Update(DS_Slip_Header, "Slip_Info")

            MsgBox("Row Added Sucessfully")

            'Clear all the textboxes
            Me.Clear_Textboxes()

        Catch ex As Exception
            Err.Raise(ex.Message)
        End Try
    End Sub

Open in new window

Avatar of tobin46
tobin46

ASKER

I did this an worked....
Private Sub Save_Slip_Header_Info()
        Try
            Dim command_builder As SqlCommandBuilder
            Dim sqlConnection_IEC As New SqlConnection(My.Settings.IEC_DB_Connection)
            Dim DA_Slip_Header As SqlDataAdapter
            DA_Slip_Header = New SqlDataAdapter("Select * from Slip_Info", sqlConnection_IEC)

            command_builder = New _
            SqlCommandBuilder(DA_Slip_Header)

            Dim DS_Slip_Header As DataSet = New DataSet

            Dim dtaSlip_Info As New DataTable("Slip_Info")
            DS_Slip_Header.Tables.Add(dtaSlip_Info)

            DA_Slip_Header.TableMappings.Add("Table", "Slip_Info")
            DA_Slip_Header.Fill(DS_Slip_Header)

            Dim Row_Slip_Header As DataRow = dtaSlip_Info.NewRow
            Row_Slip_Header("Slip_Number") = Me.txt_Slip_Number.Text
            Row_Slip_Header("Job_Number") = Me.txt_Job_Number.Text
            Row_Slip_Header("SWO_ID") = Me.txt_SWO_ID.Text
            Row_Slip_Header("Engineer_Last_Name") = Me.txt_Engineer.Text
            Row_Slip_Header("Contractor_Name") = Me.txt_contractor.Text
            Row_Slip_Header("Description") = Me.txt_Description.Text
            Row_Slip_Header("Slip_Date") = Me.DatePick_Slip_Date.Value.ToString
            Row_Slip_Header("Date_Added") = Date.Today.ToString
            Row_Slip_Header("Date_Changed") = Date.Today.ToString
            Row_Slip_Header("CreatedBy") = "tgeorge"
            Row_Slip_Header("Location") = Me.txt_Location.Text

            dtaSlip_Info.Rows.Add(Row_Slip_Header)

            DA_Slip_Header.Update(DS_Slip_Header, "Slip_Info")

            MsgBox("Row Added Sucessfully")

            'Clear all the textboxes
            Me.Clear_Textboxes()

        Catch ex As Exception
            Throw ex
        End Try
    End Sub
Avatar of Carlos Villegas
Carlos Villegas
Flag of United States of America image
Thank you tobin46!