[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 249
  • Last Modified:

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
0
tobin46
Asked:
tobin46
  • 4
  • 4
  • 3
  • +1
2 Solutions
 
Tom BeckCommented:
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)
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
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

0
 
Paul JacksonCommented:
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
0
VIDEO: THE CONCERTO CLOUD FOR HEALTHCARE

Modern healthcare requires a modern cloud. View this brief video to understand how the Concerto Cloud for Healthcare can help your organization.

 
Carlos VillegasFull Stack .NET DeveloperCommented:
I did some fixes:
    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 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

0
 
tobin46Author Commented:
@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?
0
 
Paul JacksonCommented:
I would change: err.raise(ex.message) to : throw ex
err.raise is left over from vb6 and shouldn't really be used IMHO
0
 
tobin46Author Commented:
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.
0
 
tobin46Author Commented:
Jacko - I like the throw ex much better.  Thanks.
0
 
Paul JacksonCommented:
You need to generate an insert command add the following after these lines :

            DA_Slip_Header = New SqlDataAdapter("Select * from Slip_Info", sqlConnection_IEC)
            Dim DS_Slip_Header As DataSet = New DataSet

add this line :

Dim builder as SqlCommandBuilder = New SqlCommandBuilder(DA_Slip_Header)

This should auto-generate the insertcommand.
Above taken from the below link:
http://msdn.microsoft.com/en-us/library/tf579hcz.aspx
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
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

0
 
tobin46Author Commented:
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
0
 
Carlos VillegasFull Stack .NET DeveloperCommented:
Thank you tobin46!
0

Featured Post

Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

  • 4
  • 4
  • 3
  • +1
Tackle projects and never again get stuck behind a technical roadblock.
Join Now