[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

Failed to convert parameter value from a String to a Int32

Posted on 2009-04-22
17
Medium Priority
?
1,554 Views
Last Modified: 2012-05-06
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

0
Comment
Question by:c9k9h
  • 9
  • 8
17 Comments
 
LVL 39

Expert Comment

by:abel
ID: 24206474
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
 
LVL 39

Expert Comment

by:abel
ID: 24206479
TryCast will make it zero if the conversion does not work (i.e., when the text is empty).
0
 

Author Comment

by:c9k9h
ID: 24206567
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
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!

 
LVL 39

Expert Comment

by:abel
ID: 24206635
what did you try? Can you show me?
0
 
LVL 39

Expert Comment

by:abel
ID: 24206675
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
 

Author Comment

by:c9k9h
ID: 24206867
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
 
LVL 39

Expert Comment

by:abel
ID: 24207147
Then you accidentally deleted this line:

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

0
 

Author Comment

by:c9k9h
ID: 24207851
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
 
LVL 39

Accepted Solution

by:
abel earned 2000 total points
ID: 24207949
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
 

Author Comment

by:c9k9h
ID: 24208253
 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
 
LVL 39

Expert Comment

by:abel
ID: 24208306
>>  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
 

Author Comment

by:c9k9h
ID: 24208507
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
 

Author Comment

by:c9k9h
ID: 24208585
Let me try entering dates in all the fields being updated so that they are not nulls...
0
 

Author Comment

by:c9k9h
ID: 24208652
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
 
LVL 39

Expert Comment

by:abel
ID: 24208736
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
 

Author Comment

by:c9k9h
ID: 24208910
Okay... Sorry... Get a little carried away.

Thank you for all of your help!
0
 
LVL 39

Expert Comment

by:abel
ID: 24209008
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

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

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.

Question has a verified solution.

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

Creating an analog clock UserControl seems fairly straight forward.  It is, after all, essentially just a circle with several lines in it!  Two common approaches for rendering an analog clock typically involve either manually calculating points with…
If you need to start windows update installation remotely or as a scheduled task you will find this very helpful.
Integration Management Part 2
As many of you are aware about Scanpst.exe utility which is owned by Microsoft itself to repair inaccessible or damaged PST files, but the question is do you really think Scanpst.exe is capable to repair all sorts of PST related corruption issues?
Suggested Courses

830 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