Failed to convert parameter value from a String to a Int32

Hello,

I have an update query and I'm receiving the error "Failed to convert parameter value from a String to a Int32."

I'm assuming this is because of a format issue... I'm using Text and Date formats. I've tried a few things but I'm not sure exactly what is causing this error.....

There are no Integers except for "PendingID" and other than that, everything is just text and date/time formats in the access backend.
Private Sub cmdSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSave.Click
        Dim sSQL As String
        Dim conn As New System.Data.OleDb.OleDbConnection(sConnectionString)
        Dim cmd1 As New System.Data.OleDb.OleDbCommand(sSQL, conn)
        Dim sql As String = "UPDATE Pending SET [Last] = ?, [First] = ?, [PendingClaimNum] = ?, [IntCallDate] = ?, [Director] = ?, [Custodian] = ?, [ICD9] = ?, [DX] = ?, [PendingStatus] = ?, [DateClosed] = ?, [ClosedReason] = ? WHERE [PendingID] = ?"
        Using cmd As New OleDbCommand(sql, conn)
            Dim pID, last, first, claimnum, intcall, director, custodian, icd9, dx, status, dateclosed, closereason As OleDbParameter
            last = New OleDbParameter("@Last", OleDbType.VarChar, 50)
            first = New OleDbParameter("@First", OleDbType.VarChar, 50)
            claimnum = New OleDbParameter("@PendingClaimNum", OleDbType.VarChar, 50)
            intcall = New OleDbParameter("@IntCallDate", OleDbType.Date)
            director = New OleDbParameter("@Director", OleDbType.VarChar, 50)
            custodian = New OleDbParameter("@Custodian", OleDbType.VarChar, 50)
            icd9 = New OleDbParameter("@ICD9", OleDbType.VarChar, 50)
            dx = New OleDbParameter("@DX", OleDbType.VarChar, 50)
            status = New OleDbParameter("@PendingStatus", OleDbType.VarChar, 50)
            dateclosed = New OleDbParameter("@DateClosed", OleDbType.Date)
            closereason = New OleDbParameter("@ClosedReason", OleDbType.VarChar, 50)
            last.Value = txtLast.Text
            first.Value = txtFirst.Text
            claimnum.Value = txtClaimNum.Text
            intcall.Value = txtIntCallDate.Text
            director.Value = cboDirector.SelectedText
            custodian.Value = txtCustodian.Text
            icd9.Value = txtICD9.Text
            dx.Value = txtDX.Text
            status.Value = cboStatus.SelectedText
            dateclosed.Value = txtClosedDate
            closereason.Value = cboClosedReason.SelectedText
            cmd.Parameters.Add(last)
            cmd.Parameters.Add(first)
            cmd.Parameters.Add(claimnum)
            cmd.Parameters.Add(intcall)
            cmd.Parameters.Add(director)
            cmd.Parameters.Add(custodian)
            cmd.Parameters.Add(icd9)
            cmd.Parameters.Add(dx)
            cmd.Parameters.Add(status)
            cmd.Parameters.Add(dateclosed)
            cmd.Parameters.Add(closereason)
            'PendingID parameter
            pID = New OleDbParameter("@PendingID", OleDbType.Integer)
            pID.Value = txtPendingID.Text
            cmd.Parameters.Add(pID)
            conn.Open()
            cmd.ExecuteNonQuery()
            conn.Close()
        End Using
 
 
        
 
    End Sub

Open in new window

c9k9hAsked:
Who is Participating?
 
abelConnect With a Mentor Commented:
yes, that works the same. You can Parse if you are sure of the correctness of the string, you can use TryParse if you don't want an error to be raised if the content is not conforming:

DateTime dttm = DateTime.Now
DateTime.TryParse(yourtextvarhere, dttm)
or (may raise exception):

DateTime dttm = DateTime.Parse(yourtexthere)
0
 
abelCommented:
This line:

pID.Value = txtPendingID.Text
is probably causing your error. Try:

pID.Value = CInt(txtPendingID.Text)
or:

pID.Value = TryCast(txtPendingID.Text, Integer)
0
 
abelCommented:
TryCast will make it zero if the conversion does not work (i.e., when the text is empty).
0
Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

 
c9k9hAuthor Commented:
Okay Now i'm getting "Conversion from string "" to type 'Integer' is not valid"

This worked when only updating a column or two (text columns)..
0
 
abelCommented:
what did you try? Can you show me?
0
 
abelCommented:
Oh, apologies, my TryCast should have been TryParse (you cannot cast a string to an integer, of course):

intPid As Integer = 0
Integer.TryParse(txtPendingID.Text, intPid)
pID.Value = intPid
-- Abel --
0
 
c9k9hAuthor Commented:
Okay I've done this:

'PendingID parameter

            Dim intPid As Integer = 0
            Integer.TryParse(txtPendingID.Text, intPid)
            pID.Value = intPid


            cmd.Parameters.Add(pID)
            conn.Open()
            cmd.ExecuteNonQuery()
            conn.Close()


And i'm getting "Object reference not set to an instance of an object"

highlighting line:

pID.Value = intPid
0
 
abelCommented:
Then you accidentally deleted this line:

pID = New OleDbParameter("@PendingID", OleDbType.Integer)

0
 
c9k9hAuthor Commented:
Okay making progress!!!!

The error now is "Failed to convert parameter value from a String to a DateTime"

Is there a method to casting txt as datetime?
0
 
c9k9hAuthor Commented:
 So i would

Dim DateTime as dttm = DateTime.Now  <--- does this pull current date?

Dim pID, last, first, claimnum, intcall, director, custodian, icd9, dx, status, dateclosed, closereason As OleDbParameter
            last = New OleDbParameter("@Last", OleDbType.VarChar, 50)
            first = New OleDbParameter("@First", OleDbType.VarChar, 50)
            claimnum = New OleDbParameter("@PendingClaimNum", OleDbType.VarChar, 50)
            intcall = New OleDbParameter("@IntCallDate", OleDbType.Date)
            director = New OleDbParameter("@Director", OleDbType.VarChar, 50)
            custodian = New OleDbParameter("@Custodian", OleDbType.VarChar, 50)
            icd9 = New OleDbParameter("@ICD9", OleDbType.VarChar, 50)
            dx = New OleDbParameter("@DX", OleDbType.VarChar, 50)
            status = New OleDbParameter("@PendingStatus", OleDbType.VarChar, 50)
            dateclosed = New OleDbParameter("@DateClosed", OleDbType.Date)
            closereason = New OleDbParameter("@ClosedReason", OleDbType.VarChar, 50)
            last.Value = txtLast.Text
            first.Value = txtFirst.Text
            claimnum.Value = txtClaimNum.Text
            intcall.Value = txtIntCallDate.Text
            director.Value = cboDirector.SelectedText
            custodian.Value = txtCustodian.Text
            icd9.Value = txtICD9.Text
            dx.Value = txtDX.Text
            status.Value = cboStatus.SelectedText
            DateTime.TryParse(txtClosedDate, dttm)  <------------Add this here for every instance of date time?            
            dateclosed.Value = txtClosedDate
            closereason.Value = cboClosedReason.SelectedText
            cmd.Parameters.Add(last)
            cmd.Parameters.Add(first)
            cmd.Parameters.Add(claimnum)
            cmd.Parameters.Add(intcall)
            cmd.Parameters.Add(director)
            cmd.Parameters.Add(custodian)
            cmd.Parameters.Add(icd9)
            cmd.Parameters.Add(dx)
            cmd.Parameters.Add(status)
            cmd.Parameters.Add(dateclosed)
            cmd.Parameters.Add(closereason)

0
 
abelCommented:
>>  does this pull current date?
yes. You can do anything or any date, or DateTime.MinValue, but the point is, that a DateTime cannot be empty and cannot be Nothing either.

>> Add this here for every instance of date time?            
Yes. But, since you are programming, you have all the freedom of creating a small utility function that does this for you and returns a datetime. Now you know why people are using ORM products (which takes all this mapping back and forth away from the programmer). But that's a complex and different subject.
0
 
c9k9hAuthor Commented:
Okay that is really handy to know. In my cause i'm simply updating a Date Field (textbox) on my form... could the formatting be an issue? or am Not specifying the correct datatype here: dateclosed = New OleDbParameter("@DateClosed", OleDbType.Date)
0
 
c9k9hAuthor Commented:
Let me try entering dates in all the fields being updated so that they are not nulls...
0
 
c9k9hAuthor Commented:
Alright... So I have parsed all of the datefields and it does not throw an error when executing as long as there is a date entered... this is fine for now... However, it is not updating anything.... like the query is not actually running.

Private Sub cmdSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdSave.Click
        Dim sSQL As String
        Dim conn As New System.Data.OleDb.OleDbConnection(sConnectionString)
        Dim cmd1 As New System.Data.OleDb.OleDbCommand(sSQL, conn)
        Dim sql As String = "UPDATE Pending SET [Last] = ?, [First] = ?, [PendingClaimNum] = ?, [IntCallDate] = ?, [Director] = ?, [Custodian] = ?, [ICD9] = ?, [DX] = ?, [PendingStatus] = ?, [DateClosed] = ?, [ClosedReason] = ? WHERE [PendingID] = ?"
        Using cmd As New OleDbCommand(sql, conn)
            Dim pID, last, first, claimnum, intcall, director, custodian, icd9, dx, status, dateclosed, closereason As OleDbParameter
            last = New OleDbParameter("@Last", OleDbType.VarChar, 50)
            first = New OleDbParameter("@First", OleDbType.VarChar, 50)
            claimnum = New OleDbParameter("@PendingClaimNum", OleDbType.VarChar, 50)
            intcall = New OleDbParameter("@IntCallDate", OleDbType.Date)
            director = New OleDbParameter("@Director", OleDbType.VarChar, 50)
            custodian = New OleDbParameter("@Custodian", OleDbType.VarChar, 50)
            icd9 = New OleDbParameter("@ICD9", OleDbType.VarChar, 50)
            dx = New OleDbParameter("@DX", OleDbType.VarChar, 50)
            status = New OleDbParameter("@PendingStatus", OleDbType.VarChar, 50)
            dateclosed = New OleDbParameter("@DateClosed", OleDbType.Date)
            closereason = New OleDbParameter("@ClosedReason", OleDbType.VarChar, 50)
            last.Value = txtLast.Text
            first.Value = txtFirst.Text
            claimnum.Value = txtClaimNum.Text
            intcall.Value = DateTime.Parse(txtIntCallDate.Text)
            director.Value = cboDirector.Text
            custodian.Value = txtCustodian.Text
            icd9.Value = txtICD9.Text
            dx.Value = txtDX.Text
            status.Value = cboStatus.Text
            dateclosed.Value = DateTime.Parse(txtClosedDate.Text)
            closereason.Value = cboClosedReason.Text
            cmd.Parameters.Add(last)
            cmd.Parameters.Add(first)
            cmd.Parameters.Add(claimnum)
            cmd.Parameters.Add(intcall)
            cmd.Parameters.Add(director)
            cmd.Parameters.Add(custodian)
            cmd.Parameters.Add(icd9)
            cmd.Parameters.Add(dx)
            cmd.Parameters.Add(status)
            cmd.Parameters.Add(dateclosed)
            cmd.Parameters.Add(closereason)
            'PendingID parameter
         
 
 
 
 
            'New CODE
 
            pID = New OleDbParameter("@PendingID", OleDbType.Integer)
            Dim intPid As Integer = 0
            Integer.TryParse(txtPendingID.Text, intPid)
            pID.Value = intPid
 
 
            cmd.Parameters.Add(pID)
            conn.Open()
            cmd.ExecuteNonQuery()
            conn.Close()
        End Using
 
 
        
 
    End Sub

Open in new window

0
 
abelCommented:
You are continuing adding new questions to this thread. I don't mind helping you, but this way I never know where this is going to end. My advice in the future: when you ask a question, make sure you cover all what you want to be covered. In this case, you could've said "please help me getting rid of this error and all later errors until I get the code working".

And really, then there are many people here that will help you (including me).

Since you do not report any errors anymore, I must assume that the query is executed and that the database is reached. My advice is to print the statement through the immediate window while stepping through your code and then to go to the SQL query analyzer (or the database that you use) and test the update query there to see if something happens. Most of the time people all of a sudden find out that the SQL is not really correct.
0
 
c9k9hAuthor Commented:
Okay... Sorry... Get a little carried away.

Thank you for all of your help!
0
 
abelCommented:
np. Like I said, I don't mind helping, but sometimes it is nice to know where the end of the trees are... ;)

tx for the points!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.