the wrong data type in vb.net to do an a insert into sql server

I am getting An unhandled exception of type 'System.InvalidCastException' occurred in Microsoft.VisualBasic.dll

Additional information: Conversion from string "Regular" to type 'Integer' is not valid.

I tried String then Integer the thing is it's just a combobox with two choices NFC OR AFC. WHY IS THIS HAPPENING HERE IS THE INSERT
 Public Sub Insertdata()
        Dim strplayerid As Integer = CInt(txtPlayerID.Text)
        Dim strTEAMID As String = CStr(txtTeamID.Text)
        Dim strSEASON As String = CStr(txtSeason.Text)
        Dim strPOSTSEASON As Integer = CInt(cmbPassSeason.Text)
        Dim strConference As String = CStr(txtConf.Text)
        Dim strAttempts As String = CStr(txtatt.Text)
        Dim strLong As String = CStr(txtlong.Text)
        Dim strYards As String = CStr(txtYds.Text)
        Dim strTD As String = CStr(txttds.Text)
        Dim strInterception As String = CStr(txtInter.Text)
        Dim strSacks As String = CStr(txtsacks.Text)
        Dim strSacksyds As String = CStr(txtsackyds.Text)
        Dim strRating As Integer = CInt(txtQBRating.Text)
        '  Dim strCollyr As String = CStr(cmbyrsinCol.Text)
        '  Dim query As String = String.Empty


        Using conn As New SqlConnection("Data Source=tsnappdev01;Initial Catalog=TSN2;User ID=sa;Password=sportsrus")

            Using comm As New SqlCommand()
                With comm
                    .Connection = conn
                    .CommandType = CommandType.Text



                    .Parameters.AddWithValue("@PlayerID", strplayerid)
                    .Parameters.AddWithValue("@Team", strTEAMID)
                    .Parameters.AddWithValue("@Season", strTEAMID)
                    .Parameters.AddWithValue("@SeasonType", strPOSTSEASON)
                    .Parameters.AddWithValue("@Comp", strConference)
                    .Parameters.AddWithValue("@Att", strAttempts)
                    .Parameters.AddWithValue("@Yards", strYards)
                    .Parameters.AddWithValue("@Long", strLong)
                    .Parameters.AddWithValue("@TD", strTD)
                    .Parameters.AddWithValue("@Inter", strInterception)
                    .Parameters.AddWithValue("@Sacks", strSacks)
                    .Parameters.AddWithValue("@SKYds", strSacksyds)
                    .Parameters.AddWithValue("@Rating", strRating)
                    Dim query As String = String.Empty

                    query &= "INSERT INTO NFLPlayerspass (PlayerID, Team,"
                    query &= "Season,SeasonType,Comp,Att,Yards,Long,TD,Inter,Sacks,SKYds,Rating)"
                    query &= "VALUES (@PlayerID,@Team,@Season,@SeasonType,@Comp,@Att,@Yards,@Long,@TD,@Inter,@Sacks,@SKYds,@Rating)"

                    .CommandText = query
                End With
                Try
                    conn.Open()
                    comm.ExecuteNonQuery()
                    MessageBox.Show("Record Added!")
                Catch ex As SqlException
                    MessageBox.Show(ex.ToString)
                Finally
                    conn.Close()
                End Try

                txtPlayerID.Visible = True
               

            End Using
        End Using
    End Sub

Open in new window


HEY IS ERROR MESSAGEBOX
INSERT ERROR MESSAGE  Thanks EE as always
 Dim strPOSTSEASON As Integer = CInt(cmbPassSeason.Text) I know it's not a integer but tried string
In the SQL Database the field season type is varchar(10) hopefully a clue for you guys
powerztomAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Phillip BurtonDirector, Practice Manager and Computing ConsultantCommented:
I don't know where the problem is - VB or MS SQL Server.

Assuming the problem is with SQL Server, try one of the following:

CAST(<<my expression>> as smallint)
CONVERT(smallint, <<my expression>>)

If you are using SQL Server 2012 or 2014, then use the following:

TRY_CAST(<<my expression>> as smallint)
TRY_CONVERT(smallint, <<my expression>>)

It will convert it to NULL if the value is not an integer.
0
AndyAinscowFreelance programmer / ConsultantCommented:
It looks like the table field storing your value AFC or NFC is set to store an integer - and AFC/NFC is not an integer.  Check in the table definition in the database.
0
Shaun KlineLead Software EngineerCommented:
Just a wild guess, but since you are storing player stats, should you be storing conference in the Comp field? Isn't that Completions?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
The Ultimate Tool Kit for Technolgy Solution Provi

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy for valuable how-to assets including sample agreements, checklists, flowcharts, and more!

powerztomAuthor Commented:
Comp is completions conference is conference Phillip Burton were at sql 2005
I am vb guy I know sql queries and a little dba CAST(<<my expression>> as smallint)
CONVERT(smallint, <<my expression>>) I have no idea what to do that would I do that in sql how or vb
0
Shaun KlineLead Software EngineerCommented:
In your posted code above, line 32, you are assigning strConference (which I am inferring is NFL/AFL) to @Comp (which you say is completions, an integer value). Is that what you wanted?
0
AndyAinscowFreelance programmer / ConsultantCommented:
To repeat myself:

It looks like the table field storing your value AFC or NFC is set to store an integer - and AFC/NFC is not an integer.  Check in the table definition in the database.
0
powerztomAuthor Commented:
Some of these answers helped me write a query  that works. Check it out on problempage I'll post
0
powerztomAuthor Commented:
 Public Sub Insertdata()
        Dim strplayerid As Integer = CInt(txtPlayerID.Text)
        Dim strTEAMID As String = CStr(txtTeamID.Text)
        Dim strSEASON As Integer = CInt(txtSeason.Text)
        Dim strPOSTSEASON As String = CStr(cmbPassSeason.Text)
        Dim strConference As String = CStr(txtConf.Text)
        Dim strComp As Integer = CInt(txtComp.Text)
        Dim strAttempts As Integer = CInt(txtatt.Text)
        Dim strLong As Integer = CInt(txtlong.Text)
        Dim strYards As Integer = CInt(txtYds.Text)
        Dim strTD As Integer = CInt(txttds.Text)
        Dim strInterception As Integer = CInt(txtInter.Text)
        Dim strSacks As Integer = CInt(txtsacks.Text)
        Dim strSacksyds As Integer = CInt(txtsackyds.Text)
        Dim strRating As Decimal = CDec(txtQBRating.Text)
        '  Dim strCollyr As String = CStr(cmbyrsinCol.Text)
        '  Dim query As String = String.Empty


        Using conn As New SqlConnection("Data Source=tsnappdev01;Initial Catalog=TSN2;User ID=sa;Password=sportsrus")

            Using comm As New SqlCommand()
                With comm
                    .Connection = conn
                    .CommandType = CommandType.Text

                    .Parameters.AddWithValue("@PlayerID", SqlDbType.Int).Value = strplayerid
                    .Parameters.AddWithValue("@Team", SqlDbType.Char).Value = strTEAMID
                    .Parameters.AddWithValue("@Season", SqlDbType.Char).Value = strSEASON
                    .Parameters.AddWithValue("@SeasonType", SqlDbType.VarChar).Value = strPOSTSEASON
                    .Parameters.AddWithValue("@Conference", SqlDbType.Char).Value = strConference
                    .Parameters.AddWithValue("@Att", SqlDbType.SmallInt).Value = strAttempts
                    .Parameters.AddWithValue("@Comp", SqlDbType.SmallInt).Value = strComp
                    .Parameters.AddWithValue("@Yards", SqlDbType.SmallInt).Value = strYards
                    .Parameters.AddWithValue("@Long", SqlDbType.SmallInt).Value = strLong
                    .Parameters.AddWithValue("@TD", SqlDbType.TinyInt).Value = strTD
                    .Parameters.AddWithValue("@Inter", SqlDbType.TinyInt).Value = strInterception
                    .Parameters.AddWithValue("@Sacks", SqlDbType.TinyInt).Value = strSacks
                    .Parameters.AddWithValue("@SKYds", SqlDbType.SmallInt).Value = strSacksyds
                    .Parameters.AddWithValue("@Rating", SqlDbType.Real).Value = strRating
                    Dim query As String = String.Empty

                    '.Parameters.AddWithValue("@PlayerID", strplayerid)
                    '.Parameters.AddWithValue("@Team", strTEAMID)
                    ' .Parameters.AddWithValue("@Season", strSEASON)
                    ' .Parameters.AddWithValue("@SeasonType", strPOSTSEASON)
                    '.Parameters.AddWithValue("@Conference", strConference)
                    ' .Parameters.AddWithValue("@Att", strAttempts)
                    ' .Parameters.AddWithValue("@Comp", strComp)
                    '.Parameters.AddWithValue("@Yards", strYards)
                    '.Parameters.AddWithValue("@Long", strLong)
                    ' .Parameters.AddWithValue("@TD", strTD)
                    '.Parameters.AddWithValue("@Inter", strInterception)
                    ' .Parameters.AddWithValue("@Sacks", strSacks)
                    ' .Parameters.AddWithValue("@SKYds", strSacksyds)
                    ' .Parameters.AddWithValue("@Rating", strRating)
                    ' Dim query As String = String.Empty

                    query &= "INSERT INTO NFLPlayerspass (PlayerID, Team,"
                    query &= "Season,SeasonType,Conference,Comp,Att,Yards,Long,TD,Inter,Sacks,SKYds,Rating)"
                    query &= "VALUES (@PlayerID,@Team,@Season,@SeasonType,@Conference,@Att,@Comp,@Yards,@Long,@TD,@Inter,@Sacks,@SKYds,@Rating)"

                    .CommandText = query
                End With
                Try
                    conn.Open()
                    comm.ExecuteNonQuery()
                    MessageBox.Show("Record Added!")
                Catch ex As SqlException
                    MessageBox.Show(ex.ToString)
                Finally
                    conn.Close()
                End Try
                GetpassingNfl()
                txtPlayerID.Visible = True
               

            End Using
        End Using
    End Sub

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server

From novice to tech pro — start learning today.