469,133 Members | 1,015 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,133 developers. It's quick & easy.

Input string was not in a correct format.

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

Expand|Select|Wrap|Line Numbers
  1. Imports system.data
  2. Imports system.data.sqlclient
  3. Partial Class Department_detailDept
  4.     Inherits System.Web.UI.Page
  5.     Private Sub GetData()
  6.  
  7.         ' Declare local variables. 
  8.         Dim totalClients As Integer
  9.         Dim totalPages As Integer
  10.         Dim connClients As New SqlConnection()
  11.         Dim cmdClients As New SqlCommand()
  12.         Dim parmClients As SqlParameter
  13.         Dim drClients As SqlDataReader
  14.         Dim dtClients As New DataTable()
  15.         Dim drClient As DataRow
  16.  
  17.  
  18.  
  19.         ' Define the DataTable. 
  20.         dtClients.Columns.Add("depart_id", System.Type.[GetType]("System.Int32"))
  21.         dtClients.Columns.Add("Department_name", System.Type.[GetType]("System.String"))
  22.  
  23.         ' Create a connection to the database. 
  24.         Try
  25.  
  26.             ' Connect using the connection string in the web.config. 
  27.             connClients.ConnectionString = System.Configuration.ConfigurationManager.ConnectionStrings("DBcon").ConnectionString
  28.             connClients.Open()
  29.  
  30.             ' Configure the command object. 
  31.             cmdClients.CommandText = "phealthMISDepartDetail"
  32.             cmdClients.CommandType = CommandType.StoredProcedure
  33.             cmdClients.Connection = connClients
  34.  
  35.             ' Create the command object parameters. 
  36.             ' Creat the current page parameter. 
  37.             parmClients = New SqlParameter("@CurrentPage", SqlDbType.TinyInt)
  38.             parmClients.Direction = ParameterDirection.Input
  39.             parmClients.Value = dgClients.CurrentPageIndex
  40.             cmdClients.Parameters.Add(parmClients)
  41.  
  42.             ' Create the page size parameter. 
  43.             parmClients = New SqlParameter("@PageSize", SqlDbType.TinyInt)
  44.             parmClients.Direction = ParameterDirection.Input
  45.             parmClients.Value = 5
  46.             cmdClients.Parameters.Add(parmClients)
  47.  
  48.             ' Create the total rows parameter. 
  49.             parmClients = New SqlParameter("@TotalRecords", SqlDbType.Int)
  50.             parmClients.Direction = ParameterDirection.Output
  51.             cmdClients.Parameters.Add(parmClients)
  52.  
  53.             ' Process the command. 
  54.             ' It should return to us the client rows into a DataReader. 
  55.             ' There will also be an output parameter of the total number of clients in the db. 
  56.             ' We need to close the DataReader prior to retrieving the output parameter. 
  57.             drClients = cmdClients.ExecuteReader()
  58.  
  59.             ' Iterate through the DataReader. 
  60.             While drClients.Read()
  61.  
  62.                 ' Create a new DataRow to populate. 
  63.                 drClient = dtClients.NewRow()
  64.  
  65.                 ' Populate the DataRow. 
  66.                 drClient("depart_id") = drClients("depart_id")
  67.                 drClient("Department_name") = drClients("Department_name")
  68.  
  69.  
  70.                 ' Add the DataRow. 
  71.                 dtClients.Rows.Add(drClient)
  72.             End While
  73.  
  74.             ' Close the DataReader. 
  75.             drClients.Close()
  76.  
  77.             ' Retrieve the output parameter. 
  78.             totalClients = CInt(cmdClients.Parameters("@TotalRecords").Value)
  79.  
  80.             ' Bind the DataGrid. 
  81.             dgClients.DataSource = dtClients
  82.             dgClients.DataBind()
  83.  
  84.             ' Configure the footer. 
  85.             lblCurrentPage.Text = dgClients.CurrentPageIndex.ToString()
  86.             If (totalClients Mod 5) = 0 Then
  87.                 totalPages = totalClients / 5
  88.             Else
  89.  
  90.                 totalPages = (totalClients / 5) + 1
  91.             End If
  92.             lblTotalPages.Text = totalPages.ToString()
  93.         Catch ex As Exception
  94.  
  95.             ' Nothing here for now. 
  96.  
  97.         Finally
  98.  
  99.  
  100.             ' Close and dispose of the database connection. 
  101.             cmdClients.Dispose()
  102.             connClients.Close()
  103.             connClients.Dispose()
  104.         End Try
  105.  
  106.     End Sub
  107.  
  108.     Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
  109.  
  110.         ' Is this an initial page load? 
  111.         If Not Page.IsPostBack Then
  112.             'Dim totalPages As Integer
  113.  
  114.  
  115.             ' Store a default value to the current page index on the initial load of the page. 
  116.             dgClients.CurrentPageIndex = 1
  117.  
  118.             ' Initially disable the previous link. 
  119.             lbtnPrevious.Enabled = False
  120.         End If
  121.  
  122.         ' Get the data for the DataGrid. 
  123.         GetData()
  124.  
  125.         ' Get the total pages. 
  126.  
  127.         Dim totalPages As Integer = Integer.Parse(lblTotalPages.Text.ToString())
  128.  
  129.         ' Determine if there is more than 1 page to display. 
  130.         If totalPages > 1 Then
  131.  
  132.             lbtnNext.Enabled = True
  133.         Else
  134.  
  135.             lbtnNext.Enabled = False
  136.         End If
  137.     End Sub
  138.  
  139.     Protected Sub PrePage(ByVal sender As Object, ByVal e As System.EventArgs) Handles lbtnPrevious.Click
  140.  
  141.         ' Declare local data members. 
  142.         Dim currentPage As Integer = Integer.Parse(lblCurrentPage.Text.ToString())
  143.         Dim totalPages As Integer = Integer.Parse(lblTotalPages.Text.ToString())
  144.  
  145.         ' Decrement the current page index. 
  146.         If currentPage > 1 Then
  147.  
  148.             dgClients.CurrentPageIndex -= 1
  149.  
  150.             ' Get the data for the DataGrid. 
  151.             GetData()
  152.  
  153.             ' Should we disable the previous link? 
  154.             If dgClients.CurrentPageIndex = 1 Then
  155.  
  156.                 lbtnPrevious.Enabled = False
  157.             End If
  158.  
  159.             ' Should we enable the next link? 
  160.             If dgClients.CurrentPageIndex < totalPages Then
  161.  
  162.                 lbtnNext.Enabled = True
  163.             End If
  164.         End If
  165.     End Sub
  166.  
  167.     Protected Sub NextPage(ByVal sender As Object, ByVal e As System.EventArgs) Handles lbtnNext.Click
  168.         Dim currentPage As Integer = Integer.Parse(lblCurrentPage.Text.ToString())
  169.         Dim totalPages As Integer = Integer.Parse(lblTotalPages.Text.ToString())
  170.  
  171.         ' Decrement the current page index. 
  172.         If currentPage > 1 Then
  173.  
  174.             dgClients.CurrentPageIndex -= 1
  175.  
  176.             ' Get the data for the DataGrid. 
  177.             GetData()
  178.  
  179.             ' Should we disable the previous link? 
  180.             If dgClients.CurrentPageIndex = 1 Then
  181.  
  182.                 lbtnPrevious.Enabled = False
  183.             End If
  184.  
  185.             ' Should we enable the next link? 
  186.             If dgClients.CurrentPageIndex < totalPages Then
  187.  
  188.                 lbtnNext.Enabled = True
  189.             End If
  190.         End If
  191.     End Sub
  192.  
  193. End Class
  194.  

store procedure is as follows:
Expand|Select|Wrap|Line Numbers
  1. ALTER Procedure dbo.phealthMISDepartDetail 
  2.  
  3. -- Declare parameters.
  4. @CurrentPage As tinyint,
  5. @PageSize As tinyint,
  6. @TotalRecords As int OUTPUT
  7.  
  8.  
  9. As
  10.  
  11. -- Turn off count return.
  12. Set NoCount On
  13.  
  14. -- Declare variables.
  15. Declare @FirstRec int
  16. Declare @LastRec int
  17.  
  18. -- Initialize variables.
  19. Set @FirstRec = (@CurrentPage - 1) * @PageSize
  20. Set @LastRec = (@CurrentPage * @PageSize + 1)
  21.  
  22. -- Create a temp table to hold the current page of data
  23. -- Add an ID column to count the records
  24. Create Table #TempTable
  25. (
  26. depart_id int IDENTITY PRIMARY KEY,
  27. Department_name varchar(50)
  28. )
  29.  
  30. --Fill the temp table with the reminders
  31. Insert Into #TempTable 
  32. (
  33. Department_name
  34. )
  35. Select
  36. Department_name As Name
  37. From 
  38. Department
  39.  
  40.  
  41. --Select one page of data based on the record numbers above
  42. Select 
  43. depart_id,
  44. Department_name
  45. From 
  46. #TempTable As Department
  47. Where 
  48. depart_id > @FirstRec 
  49. And depart_id < @LastRec
  50.  
  51. --Return the total number of records available as an output parameter
  52. Select
  53. @TotalRecords = Count(*)
  54. From 
  55. Department
  56.  
kindly experts let me out of this problem as soon as possible!!!!
Sep 20 '07 #1
9 3414
Plater
7,872 Expert 4TB
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:
Expand|Select|Wrap|Line Numbers
  1.         ' Define the DataTable. 
  2.         dtClients.Columns.Add("depart_id", System.Type.[GetType]("System.Int32"))
  3.         dtClients.Columns.Add("Department_name", System.Type.[GetType]("System.String"))
  4.  
  5.  
  6.             ' Process the command. 
  7.             ' It should return to us the client rows into a DataReader. 
  8.             ' There will also be an output parameter of the total number of clients in the db. 
  9.             ' We need to close the DataReader prior to retrieving the output parameter. 
  10.             drClients = cmdClients.ExecuteReader()
  11.  
  12.             ' Iterate through the DataReader. 
  13.             While drClients.Read()
  14.  
  15.                 ' Create a new DataRow to populate. 
  16.                 drClient = dtClients.NewRow()
  17.  
  18.                 ' Populate the DataRow. 
  19.                 drClient("depart_id") = drClients("depart_id")
  20.                 drClient("Department_name") = drClients("Department_name")
  21.  
  22.  
  23.                 ' Add the DataRow. 
  24.                 dtClients.Rows.Add(drClient)
  25.             End While
  26.  
  27.             ' Close the DataReader. 
  28.             drClients.Close()
  29.  
Can be turned into:
Expand|Select|Wrap|Line Numbers
  1. Dim mya = new SqlDataAdapter(cmdClients);
  2. mya.Fill(dtClients );
  3.  
Sep 20 '07 #2
seep
15
what steps i have to take to do it from the scrap, am i going in a wrong way???
Sep 20 '07 #3
Frinavale
9,735 Expert Mod 8TB
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:
Expand|Select|Wrap|Line Numbers
  1.  'My Vb.Net code goes here
  2.  
This will make it a lot easier to read your posts.

Thanks a lot!

-Frinny
Sep 20 '07 #4
seep
15
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
Sep 23 '07 #5
krris
10
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.
Sep 24 '07 #6
seep
15
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!!!
Sep 25 '07 #7
Frinavale
9,735 Expert Mod 8TB
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?
Sep 25 '07 #8
seep
15
i found error at this:
Expand|Select|Wrap|Line Numbers
  1. Dim totalPages As Integer = Integer.Parse(lblTotalPages.Text.ToString
at line 127 of the above posted code. so, pls guide me.
Sep 26 '07 #9
Frinavale
9,735 Expert Mod 8TB
i found error at this:
Expand|Select|Wrap|Line Numbers
  1. 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
Expand|Select|Wrap|Line Numbers
  1. Dim totalPages As Integer = Integer.Parse(lblTotalPages.Text.ToString())
to
Expand|Select|Wrap|Line Numbers
  1. Dim totalPages As Integer = Integer.Parse(lblTotalPages.Text.ToString())
Sep 26 '07 #10

Post your reply

Sign in to post your reply or Sign up for a free account.

Similar topics

reply views Thread by lianfe_ravago | last post: by
5 posts views Thread by blackg | last post: by
1 post views Thread by amitbadgi | last post: by
1 post views Thread by amitbadgi | last post: by
1 post views Thread by Mortomer39 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.