Input string was not in a correct format.

[seep]

hi

i m finding following error on the code that i wants to use to get all record from table via store procedure with paging. the error is :
Input string was not in a correct format.

after a hectic struggle still i dont know
1--who can i solve it and
2--where should i have to place the function GETDATA . who is it possible to keep all the functions in a separate file and to call that file in required page.

here is the code of whole page where i m getting error::

[code=vbnet]
Imports system.data
Imports system.data.sql client
Partial Class Department_deta ilDept
Inherits System.Web.UI.P age
Private Sub GetData()

' Declare local variables.
Dim totalClients As Integer
Dim totalPages As Integer
Dim connClients As New SqlConnection()
Dim cmdClients As New SqlCommand()
Dim parmClients As SqlParameter
Dim drClients As SqlDataReader
Dim dtClients As New DataTable()
Dim drClient As DataRow



' Define the DataTable.
dtClients.Colum ns.Add("depart_ id", System.Type.[GetType]("System.Int32" ))
dtClients.Colum ns.Add("Departm ent_name", System.Type.[GetType]("System.String "))

' Create a connection to the database.
Try

' Connect using the connection string in the web.config.
connClients.Con nectionString = System.Configur ation.Configura tionManager.Con nectionStrings( "DBcon").Connec tionString
connClients.Ope n()

' Configure the command object.
cmdClients.Comm andText = "phealthMISDepa rtDetail"
cmdClients.Comm andType = CommandType.Sto redProcedure
cmdClients.Conn ection = connClients

' Create the command object parameters.
' Creat the current page parameter.
parmClients = New SqlParameter("@ CurrentPage", SqlDbType.TinyI nt)
parmClients.Dir ection = ParameterDirect ion.Input
parmClients.Val ue = dgClients.Curre ntPageIndex
cmdClients.Para meters.Add(parm Clients)

' Create the page size parameter.
parmClients = New SqlParameter("@ PageSize", SqlDbType.TinyI nt)
parmClients.Dir ection = ParameterDirect ion.Input
parmClients.Val ue = 5
cmdClients.Para meters.Add(parm Clients)

' Create the total rows parameter.
parmClients = New SqlParameter("@ TotalRecords", SqlDbType.Int)
parmClients.Dir ection = ParameterDirect ion.Output
cmdClients.Para meters.Add(parm Clients)

' Process the command.
' It should return to us the client rows into a DataReader.
' There will also be an output parameter of the total number of clients in the db.
' We need to close the DataReader prior to retrieving the output parameter.
drClients = cmdClients.Exec uteReader()

' Iterate through the DataReader.
While drClients.Read( )

' Create a new DataRow to populate.
drClient = dtClients.NewRo w()

' Populate the DataRow.
drClient("depar t_id") = drClients("depa rt_id")
drClient("Depar tment_name") = drClients("Depa rtment_name")


' Add the DataRow.
dtClients.Rows. Add(drClient)
End While

' Close the DataReader.
drClients.Close ()

' Retrieve the output parameter.
totalClients = CInt(cmdClients .Parameters("@T otalRecords").V alue)

' Bind the DataGrid.
dgClients.DataS ource = dtClients
dgClients.DataB ind()

' Configure the footer.
lblCurrentPage. Text = dgClients.Curre ntPageIndex.ToS tring()
If (totalClients Mod 5) = 0 Then
totalPages = totalClients / 5
Else

totalPages = (totalClients / 5) + 1
End If
lblTotalPages.T ext = totalPages.ToSt ring()
Catch ex As Exception

' Nothing here for now.

Finally


' Close and dispose of the database connection.
cmdClients.Disp ose()
connClients.Clo se()
connClients.Dis pose()
End Try

End Sub

Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArg s) Handles Me.Load

' Is this an initial page load?
If Not Page.IsPostBack Then
'Dim totalPages As Integer


' Store a default value to the current page index on the initial load of the page.
dgClients.Curre ntPageIndex = 1

' Initially disable the previous link.
lbtnPrevious.En abled = False
End If

' Get the data for the DataGrid.
GetData()

' Get the total pages.

Dim totalPages As Integer = Integer.Parse(l blTotalPages.Te xt.ToString())

' Determine if there is more than 1 page to display.
If totalPages > 1 Then

lbtnNext.Enable d = True
Else

lbtnNext.Enable d = False
End If
End Sub

Protected Sub PrePage(ByVal sender As Object, ByVal e As System.EventArg s) Handles lbtnPrevious.Cl ick

' Declare local data members.
Dim currentPage As Integer = Integer.Parse(l blCurrentPage.T ext.ToString())
Dim totalPages As Integer = Integer.Parse(l blTotalPages.Te xt.ToString())

' Decrement the current page index.
If currentPage > 1 Then

dgClients.Curre ntPageIndex -= 1

' Get the data for the DataGrid.
GetData()

' Should we disable the previous link?
If dgClients.Curre ntPageIndex = 1 Then

lbtnPrevious.En abled = False
End If

' Should we enable the next link?
If dgClients.Curre ntPageIndex < totalPages Then

lbtnNext.Enable d = True
End If
End If
End Sub

Protected Sub NextPage(ByVal sender As Object, ByVal e As System.EventArg s) Handles lbtnNext.Click
Dim currentPage As Integer = Integer.Parse(l blCurrentPage.T ext.ToString())
Dim totalPages As Integer = Integer.Parse(l blTotalPages.Te xt.ToString())

' Decrement the current page index.
If currentPage > 1 Then

dgClients.Curre ntPageIndex -= 1

' Get the data for the DataGrid.
GetData()

' Should we disable the previous link?
If dgClients.Curre ntPageIndex = 1 Then

lbtnPrevious.En abled = False
End If

' Should we enable the next link?
If dgClients.Curre ntPageIndex < totalPages Then

lbtnNext.Enable d = True
End If
End If
End Sub

End Class
[/code]


store procedure is as follows:
[code=sql]
ALTER Procedure dbo.phealthMISD epartDetail

-- Declare parameters.
@CurrentPage As tinyint,
@PageSize As tinyint,
@TotalRecords As int OUTPUT


As

-- Turn off count return.
Set NoCount On

-- Declare variables.
Declare @FirstRec int
Declare @LastRec int

-- Initialize variables.
Set @FirstRec = (@CurrentPage - 1) * @PageSize
Set @LastRec = (@CurrentPage * @PageSize + 1)

-- Create a temp table to hold the current page of data
-- Add an ID column to count the records
Create Table #TempTable
(
depart_id int IDENTITY PRIMARY KEY,
Department_name varchar(50)
)

--Fill the temp table with the reminders
Insert Into #TempTable
(
Department_name
)
Select
Department_name As Name
From
Department


--Select one page of data based on the record numbers above
Select
depart_id,
Department_name
From
#TempTable As Department
Where
depart_id > @FirstRec
And depart_id < @LastRec

--Return the total number of records available as an output parameter
Select
@TotalRecords = Count(*)
From
Department
[/code]

kindly experts let me out of this problem as soon as possible!!!!

[Plater]

Well I see a number of things wrong.
First off, it appears that you are trying to assign a value to your datareader FROM your datatable (instead of other way around)

Secondly, you're doing a LOT more work then you need to.

This code:
[code=vbnet]
' Define the DataTable.
dtClients.Colum ns.Add("depart_ id", System.Type.[GetType]("System.Int32" ))
dtClients.Colum ns.Add("Departm ent_name", System.Type.[GetType]("System.String "))


' Process the command.
' It should return to us the client rows into a DataReader.
' There will also be an output parameter of the total number of clients in the db.
' We need to close the DataReader prior to retrieving the output parameter.
drClients = cmdClients.Exec uteReader()

' Iterate through the DataReader.
While drClients.Read( )

' Create a new DataRow to populate.
drClient = dtClients.NewRo w()

' Populate the DataRow.
drClient("depar t_id") = drClients("depa rt_id")
drClient("Depar tment_name") = drClients("Depa rtment_name")


' Add the DataRow.
dtClients.Rows. Add(drClient)
End While

' Close the DataReader.
drClients.Close ()
[/code]

Can be turned into:
[code=vbnet]
Dim mya = new SqlDataAdapter( cmdClients);
mya.Fill(dtClie nts );
[/code]

[seep]

what steps i have to take to do it from the scrap, am i going in a wrong way???

[Frinavale]

what steps i have to take to do it from the scrap, am i going in a wrong way???

Hi Seep,

Could you please put your code within

 tags.
For instance if you want to post VB.Net code you'd type:
[code=vbnet]
 'My Vb.Net code goes here

This will make it a lot easier to read your posts.

Thanks a lot!

-Frinny

[seep]

pls help me i m struck in this problem and find no way to out. kindly help m e with in coding. waiting for ur response thanks for all in advance

[krris]

hi

First check ur all Data. u r trying to convert String into Int,Decimal or else.
and Pass String null or blank thats why this error comes.

check Data comes correct.

[seep]

really hoof..
still i may not b able to correct this. is their any one who can provide me code for this??
needs an urgent response!!!

[Frinavale]

really hoof..
still i may not b able to correct this. is their any one who can provide me code for this??
needs an urgent response!!!

You've posted a lot of code.
Could you please post the line of code that the error is occurring on...
Have you checked your connection string?

[seep]

i found error at this:

Dim totalPages As Integer = Integer.Parse(lblTotalPages.Text.ToString

at line 127 of the above posted code. so, pls guide me.

[Frinavale]

i found error at this:

Dim totalPages As Integer = Integer.Parse(lblTotalPages.Text.ToString

at line 127 of the above posted code. so, pls guide me.

Try excluding the ToString.
So change
[code=vbnet]
Dim totalPages As Integer = Integer.Parse(l blTotalPages.Te xt.ToString())[/code]
to
[code=vbnet]
Dim totalPages As Integer = Integer.Parse(l blTotalPages.Te xt.ToString())[/code]