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::

Imports system.data
Imports system.data.sqlclient
Partial Class Department_detailDept
    Inherits System.Web.UI.Page
    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.Columns.Add("depart_id", System.Type.[GetType]("System.Int32"))
        dtClients.Columns.Add("Department_name", System.Type.[GetType]("System.String"))
 
        ' Create a connection to the database. 
        Try
 
            ' Connect using the connection string in the web.config. 
            connClients.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings("DBcon").ConnectionString
            connClients.Open()
 
            ' Configure the command object. 
            cmdClients.CommandText = "phealthMISDepartDetail"
            cmdClients.CommandType = CommandType.StoredProcedure
            cmdClients.Connection = connClients
 
            ' Create the command object parameters. 
            ' Creat the current page parameter. 
            parmClients = New SqlParameter("@CurrentPage", SqlDbType.TinyInt)
            parmClients.Direction = ParameterDirection.Input
            parmClients.Value = dgClients.CurrentPageIndex
            cmdClients.Parameters.Add(parmClients)
 
            ' Create the page size parameter. 
            parmClients = New SqlParameter("@PageSize", SqlDbType.TinyInt)
            parmClients.Direction = ParameterDirection.Input
            parmClients.Value = 5
            cmdClients.Parameters.Add(parmClients)
 
            ' Create the total rows parameter. 
            parmClients = New SqlParameter("@TotalRecords", SqlDbType.Int)
            parmClients.Direction = ParameterDirection.Output
            cmdClients.Parameters.Add(parmClients)
 
            ' 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.ExecuteReader()
 
            ' Iterate through the DataReader. 
            While drClients.Read()
 
                ' Create a new DataRow to populate. 
                drClient = dtClients.NewRow()
 
                ' Populate the DataRow. 
                drClient("depart_id") = drClients("depart_id")
                drClient("Department_name") = drClients("Department_name")
 
 
                ' Add the DataRow. 
                dtClients.Rows.Add(drClient)
            End While
 
            ' Close the DataReader. 
            drClients.Close()
 
            ' Retrieve the output parameter. 
            totalClients = CInt(cmdClients.Parameters("@TotalRecords").Value)
 
            ' Bind the DataGrid. 
            dgClients.DataSource = dtClients
            dgClients.DataBind()
 
            ' Configure the footer. 
            lblCurrentPage.Text = dgClients.CurrentPageIndex.ToString()
            If (totalClients Mod 5) = 0 Then
                totalPages = totalClients / 5
            Else
 
                totalPages = (totalClients / 5) + 1
            End If
            lblTotalPages.Text = totalPages.ToString()
        Catch ex As Exception
 
            ' Nothing here for now. 
 
        Finally
 
 
            ' Close and dispose of the database connection. 
            cmdClients.Dispose()
            connClients.Close()
            connClients.Dispose()
        End Try
 
    End Sub
 
    Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) 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.CurrentPageIndex = 1
 
            ' Initially disable the previous link. 
            lbtnPrevious.Enabled = False
        End If
 
        ' Get the data for the DataGrid. 
        GetData()
 
        ' Get the total pages. 
 
        Dim totalPages As Integer = Integer.Parse(lblTotalPages.Text.ToString())
 
        ' Determine if there is more than 1 page to display. 
        If totalPages > 1 Then
 
            lbtnNext.Enabled = True
        Else
 
            lbtnNext.Enabled = False
        End If
    End Sub
 
    Protected Sub PrePage(ByVal sender As Object, ByVal e As System.EventArgs) Handles lbtnPrevious.Click
 
        ' Declare local data members. 
        Dim currentPage As Integer = Integer.Parse(lblCurrentPage.Text.ToString())
        Dim totalPages As Integer = Integer.Parse(lblTotalPages.Text.ToString())
 
        ' Decrement the current page index. 
        If currentPage > 1 Then
 
            dgClients.CurrentPageIndex -= 1
 
            ' Get the data for the DataGrid. 
            GetData()
 
            ' Should we disable the previous link? 
            If dgClients.CurrentPageIndex = 1 Then
 
                lbtnPrevious.Enabled = False
            End If
 
            ' Should we enable the next link? 
            If dgClients.CurrentPageIndex < totalPages Then
 
                lbtnNext.Enabled = True
            End If
        End If
    End Sub
 
    Protected Sub NextPage(ByVal sender As Object, ByVal e As System.EventArgs) Handles lbtnNext.Click
        Dim currentPage As Integer = Integer.Parse(lblCurrentPage.Text.ToString())
        Dim totalPages As Integer = Integer.Parse(lblTotalPages.Text.ToString())
 
        ' Decrement the current page index. 
        If currentPage > 1 Then
 
            dgClients.CurrentPageIndex -= 1
 
            ' Get the data for the DataGrid. 
            GetData()
 
            ' Should we disable the previous link? 
            If dgClients.CurrentPageIndex = 1 Then
 
                lbtnPrevious.Enabled = False
            End If
 
            ' Should we enable the next link? 
            If dgClients.CurrentPageIndex < totalPages Then
 
                lbtnNext.Enabled = True
            End If
        End If
    End Sub
 
End Class
 

store procedure is as follows:

ALTER Procedure dbo.phealthMISDepartDetail 
 
-- 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
 

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:

        ' Define the DataTable. 
        dtClients.Columns.Add("depart_id", System.Type.[GetType]("System.Int32"))
        dtClients.Columns.Add("Department_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.ExecuteReader()
 
            ' Iterate through the DataReader. 
            While drClients.Read()
 
                ' Create a new DataRow to populate. 
                drClient = dtClients.NewRow()
 
                ' Populate the DataRow. 
                drClient("depart_id") = drClients("depart_id")
                drClient("Department_name") = drClients("Department_name")
 
 
                ' Add the DataRow. 
                dtClients.Rows.Add(drClient)
            End While
 
            ' Close the DataReader. 
            drClients.Close()
 

Can be turned into:

Dim mya = new SqlDataAdapter(cmdClients);
mya.Fill(dtClients );
 

[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 [code] tags.
For instance if you want to post VB.Net code you'd type:

 '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

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

to

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