Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

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

Posted on 2014-11-26
8
Medium Priority
?
372 Views
Last Modified: 2014-11-26
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
0
Comment
Question by:powerztom
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 24

Expert Comment

by:Phillip Burton
ID: 40467153
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
 
LVL 44

Assisted Solution

by:AndyAinscow
AndyAinscow earned 800 total points
ID: 40467155
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
 
LVL 27

Accepted Solution

by:
Shaun Kline earned 1200 total points
ID: 40467170
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
Simplifying Server Workload Migrations

This use case outlines the migration challenges that organizations face and how the Acronis AnyData Engine supports physical-to-physical (P2P), physical-to-virtual (P2V), virtual to physical (V2P), and cross-virtual (V2V) migration scenarios to address these challenges.

 

Author Comment

by:powerztom
ID: 40467232
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
 
LVL 27

Assisted Solution

by:Shaun Kline
Shaun Kline earned 1200 total points
ID: 40467354
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
 
LVL 44

Assisted Solution

by:AndyAinscow
AndyAinscow earned 800 total points
ID: 40467369
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
 

Author Closing Comment

by:powerztom
ID: 40467466
Some of these answers helped me write a query  that works. Check it out on problempage I'll post
0
 

Author Comment

by:powerztom
ID: 40467468
 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

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Windocks is an independent port of Docker's open source to Windows.   This article introduces the use of SQL Server in containers, with integrated support of SQL Server database cloning.
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.

715 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question