Please be kind, I am a novice and may ask several questions....
I am trying to read time(7) cell from an SQL Server 2008 Databse using a datareader and putting it into a dropdownlist. I am doing this in Page_Load. When trying to populate the dropdownlist I get a conversion error that I do not know how to fix. Any
help would be appreciated.
Here is the Entire code behind to give you an idea
Imports System.Data
Imports System.Data.SqlClient
Partial Class EditUser
Inherits System.Web.UI.Page
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
If (Not Page.IsPostBack) Then
'Bind the hours to the drop down lists
' In time - TG 11/27/12
BindHours(ddlMonIn)
BindHours(ddlMonOut)
BindHours(ddlTueIn)
BindHours(ddlTueOut)
BindHours(ddlWedIn)
BindHours(ddlWedOut)
BindHours(ddlThuIn)
BindHours(ddlThuOut)
BindHours(ddlFriIn)
BindHours(ddlFriOut)
BindHours(ddlSatIn)
BindHours(ddlSatOut)
BindHours(ddlSunIn)
BindHours(ddlSunOut)
Dim cnGetUser As New SqlConnection
cnGetUser.ConnectionString = "data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\aspnetdb.mdf;User Instance=true"
cnGetUser.Open()
Dim cm As New SqlCommand
cm.CommandText = "SELECT User_Profile.FirstName, User_Profile.LastName, aspnet_Users.UserName, aspnet_Roles.RoleName, User_Profile.EmployeeNum, aspnet_Membership.Password, User_Job_Title.JobTitle, User_Profile.DemoTag, User_Profile.StartDate, User_Department.Department, aspnet_Membership.Email, User_Profile.CellPhone, User_Carriers.Carrier, User_Profile.Ext, User_Profile.MonIn, User_Profile.MonOut, User_Profile.TueIn, User_Profile.TueOut, User_Profile.WedIn, User_Profile.WedOut, User_Profile.ThuIn, User_Profile.ThuOut, User_Profile.FriIn, User_Profile.FriOut, User_Profile.SatIn, User_Profile.SatOut, User_Profile.SunIn, User_Profile.SunOut FROM aspnet_Users INNER JOIN aspnet_UsersInRoles ON aspnet_Users.UserId = aspnet_UsersInRoles.UserId INNER JOIN aspnet_Roles ON aspnet_UsersInRoles.RoleId = aspnet_Roles.RoleId INNER JOIN User_Profile ON aspnet_Users.UserId = User_Profile.UserId INNER JOIN User_Department ON User_Profile.DepartmentId = User_Department.DepartmentId INNER JOIN User_Job_Title ON User_Profile.JobTitleId = User_Job_Title.JobTitleId INNER JOIN aspnet_Membership ON aspnet_Users.UserId = aspnet_Membership.UserId INNER JOIN User_Carriers ON User_Profile.CarrierId = User_Carriers.CarrierID WHERE (User_Profile.UserId = '" & Request.QueryString("UserId") & "')"
cm.Connection = cnGetUser
Dim dr As SqlDataReader
dr = cm.ExecuteReader
If dr.HasRows Then
dr.Read()
txtFirstName.Text = dr.Item("FirstName")
txtLastName.Text = dr.Item("LastName")
txtUsername.Text = dr.Item("UserName")
ddlPosition.Text = dr.Item("RoleName")
txtEmployeeNum.Text = dr.Item("EmployeeNum")
txtPassword.Text = dr.Item("Password")
ddlJobTitle.Text = dr.Item("JobTitle")
txtPlate.Text = dr.Item("DemoTag")
' txtStartDate
ddlDepartment.Text = dr.Item("Department")
' set up ddlTeam
txtEmail.Text = dr.Item("Email")
txtCellPhone.Text = dr.Item("CellPhone")
ddlCarrier.Text = dr.Item("Carrier")
txtWorkExtension.Text = dr.Item("Ext")
ddlMonIn.Text = dr.Item("MonIn") 'I cannot get the data to load from this point on, everything else above works splendidly
ddlMonOut.Text = dr.Item("MonOut")
ddlTueIn.Text = dr.Item("TueIn")
ddlTueOut.Text = dr.Item("TueOut")
ddlWedIn.Text = dr.Item("WedIn")
ddlWedOut.Text = dr.Item("WedOut")
ddlThuIn.Text = dr.Item("ThuIn")
ddlThuOut.Text = dr.Item("ThuOut")
ddlFriIn.Text = dr.Item("FriIn")
ddlFriOut.Text = dr.Item("FriOut")
ddlSatIn.Text = dr.Item("SatIn")
ddlSatOut.Text = dr.Item("SatOut")
ddlSunIn.Text = dr.Item("SunIn")
ddlSunOut.Text = dr.Item("SunOut")
dr.Close()
End If
cnGetUser.Close()
End If
End Sub
Private Sub BindHours(ByVal list As DropDownList)
'Create an array with all the proper times
Dim times As String() = {"OFF", "6:00 AM", "6:30 AM", "7:00 AM", "7:30 AM", "8:00 AM", "8:30 AM", "9:00 AM", "9:30 AM",
"10:00 AM", "10:30 AM", "11:00 AM", "11:30 AM", "12:00 PM", "12:30 PM", "1:00 PM",
"1:30 PM", "2:00 PM", "2:30 PM", "3:00 PM", "3:30 PM", "4:00 PM", "4:30 PM", "5:00 PM",
"5:30 PM", "6:00 PM", "6:30 PM", "7:00 PM", "7:30 PM", "8:30 PM", "9:00 PM", "9:30 PM",
"10:00 PM"}
list.DataSource = times
list.DataBind()
End Sub
End Class
Here is the error message:
System.InvalidCastException was unhandled by user code
Message=Conversion from type 'TimeSpan' to type 'String' is not valid.
Source=Microsoft.VisualBasic
StackTrace:
at Microsoft.VisualBasic.CompilerServices.Conversions.ToString(Object Value)
at EditUser.Page_Load(Object sender, EventArgs e) in C:\Users\Terry Grunwald\Documents\Visual Studio 2010\WebSites\LearnASPTest\EditUser.aspx.vb:line 54
at System.Web.UI.Control.OnLoad(EventArgs e)
at System.Web.UI.Control.LoadRecursive()
at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint)
InnerException:
grunwaldtx
Member
1 Points
8 Posts
Conversion from type 'TimeSpan' to type 'String' is not valid. Help please!
Nov 30, 2012 06:58 PM|LINK
Please be kind, I am a novice and may ask several questions....
I am trying to read time(7) cell from an SQL Server 2008 Databse using a datareader and putting it into a dropdownlist. I am doing this in Page_Load. When trying to populate the dropdownlist I get a conversion error that I do not know how to fix. Any help would be appreciated.
Here is the Entire code behind to give you an idea
Imports System.Data Imports System.Data.SqlClient Partial Class EditUser Inherits System.Web.UI.Page Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load If (Not Page.IsPostBack) Then 'Bind the hours to the drop down lists ' In time - TG 11/27/12 BindHours(ddlMonIn) BindHours(ddlMonOut) BindHours(ddlTueIn) BindHours(ddlTueOut) BindHours(ddlWedIn) BindHours(ddlWedOut) BindHours(ddlThuIn) BindHours(ddlThuOut) BindHours(ddlFriIn) BindHours(ddlFriOut) BindHours(ddlSatIn) BindHours(ddlSatOut) BindHours(ddlSunIn) BindHours(ddlSunOut) Dim cnGetUser As New SqlConnection cnGetUser.ConnectionString = "data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|\aspnetdb.mdf;User Instance=true" cnGetUser.Open() Dim cm As New SqlCommand cm.CommandText = "SELECT User_Profile.FirstName, User_Profile.LastName, aspnet_Users.UserName, aspnet_Roles.RoleName, User_Profile.EmployeeNum, aspnet_Membership.Password, User_Job_Title.JobTitle, User_Profile.DemoTag, User_Profile.StartDate, User_Department.Department, aspnet_Membership.Email, User_Profile.CellPhone, User_Carriers.Carrier, User_Profile.Ext, User_Profile.MonIn, User_Profile.MonOut, User_Profile.TueIn, User_Profile.TueOut, User_Profile.WedIn, User_Profile.WedOut, User_Profile.ThuIn, User_Profile.ThuOut, User_Profile.FriIn, User_Profile.FriOut, User_Profile.SatIn, User_Profile.SatOut, User_Profile.SunIn, User_Profile.SunOut FROM aspnet_Users INNER JOIN aspnet_UsersInRoles ON aspnet_Users.UserId = aspnet_UsersInRoles.UserId INNER JOIN aspnet_Roles ON aspnet_UsersInRoles.RoleId = aspnet_Roles.RoleId INNER JOIN User_Profile ON aspnet_Users.UserId = User_Profile.UserId INNER JOIN User_Department ON User_Profile.DepartmentId = User_Department.DepartmentId INNER JOIN User_Job_Title ON User_Profile.JobTitleId = User_Job_Title.JobTitleId INNER JOIN aspnet_Membership ON aspnet_Users.UserId = aspnet_Membership.UserId INNER JOIN User_Carriers ON User_Profile.CarrierId = User_Carriers.CarrierID WHERE (User_Profile.UserId = '" & Request.QueryString("UserId") & "')" cm.Connection = cnGetUser Dim dr As SqlDataReader dr = cm.ExecuteReader If dr.HasRows Then dr.Read() txtFirstName.Text = dr.Item("FirstName") txtLastName.Text = dr.Item("LastName") txtUsername.Text = dr.Item("UserName") ddlPosition.Text = dr.Item("RoleName") txtEmployeeNum.Text = dr.Item("EmployeeNum") txtPassword.Text = dr.Item("Password") ddlJobTitle.Text = dr.Item("JobTitle") txtPlate.Text = dr.Item("DemoTag") ' txtStartDate ddlDepartment.Text = dr.Item("Department") ' set up ddlTeam txtEmail.Text = dr.Item("Email") txtCellPhone.Text = dr.Item("CellPhone") ddlCarrier.Text = dr.Item("Carrier") txtWorkExtension.Text = dr.Item("Ext") ddlMonIn.Text = dr.Item("MonIn") 'I cannot get the data to load from this point on, everything else above works splendidly ddlMonOut.Text = dr.Item("MonOut") ddlTueIn.Text = dr.Item("TueIn") ddlTueOut.Text = dr.Item("TueOut") ddlWedIn.Text = dr.Item("WedIn") ddlWedOut.Text = dr.Item("WedOut") ddlThuIn.Text = dr.Item("ThuIn") ddlThuOut.Text = dr.Item("ThuOut") ddlFriIn.Text = dr.Item("FriIn") ddlFriOut.Text = dr.Item("FriOut") ddlSatIn.Text = dr.Item("SatIn") ddlSatOut.Text = dr.Item("SatOut") ddlSunIn.Text = dr.Item("SunIn") ddlSunOut.Text = dr.Item("SunOut") dr.Close() End If cnGetUser.Close() End If End Sub Private Sub BindHours(ByVal list As DropDownList) 'Create an array with all the proper times Dim times As String() = {"OFF", "6:00 AM", "6:30 AM", "7:00 AM", "7:30 AM", "8:00 AM", "8:30 AM", "9:00 AM", "9:30 AM", "10:00 AM", "10:30 AM", "11:00 AM", "11:30 AM", "12:00 PM", "12:30 PM", "1:00 PM", "1:30 PM", "2:00 PM", "2:30 PM", "3:00 PM", "3:30 PM", "4:00 PM", "4:30 PM", "5:00 PM", "5:30 PM", "6:00 PM", "6:30 PM", "7:00 PM", "7:30 PM", "8:30 PM", "9:00 PM", "9:30 PM", "10:00 PM"} list.DataSource = times list.DataBind() End Sub End ClassHere is the error message:
System.InvalidCastException was unhandled by user code Message=Conversion from type 'TimeSpan' to type 'String' is not valid. Source=Microsoft.VisualBasic StackTrace: at Microsoft.VisualBasic.CompilerServices.Conversions.ToString(Object Value) at EditUser.Page_Load(Object sender, EventArgs e) in C:\Users\Terry Grunwald\Documents\Visual Studio 2010\WebSites\LearnASPTest\EditUser.aspx.vb:line 54 at System.Web.UI.Control.OnLoad(EventArgs e) at System.Web.UI.Control.LoadRecursive() at System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) InnerException:limno
All-Star
117326 Points
8003 Posts
Moderator
MVP
Re: Conversion from type 'TimeSpan' to type 'String' is not valid. Help please!
Nov 30, 2012 09:05 PM|LINK
Try this:ddlMonIn.Text = dr.Item("MonIn").ToString()
Also, please use parameterized query to avoid SQL Injection threat for your code.
Format your SQL query with instant sql formatter:
http://www.dpriver.com/pp/sqlformat.htm
grunwaldtx
Member
1 Points
8 Posts
Re: Conversion from type 'TimeSpan' to type 'String' is not valid. Help please!
Nov 30, 2012 09:28 PM|LINK
Thank you limno for the prompt reply.
I tried doing this before I posted my question
ddlMonIn.Text = dr.Item("MonIn").ToString()It does not seem to put the time value in the drop down list.
The dropdown list only has the derfault values that are loaded in by BindHours("MonIn")
I have verified with a GridView that the data is there...
What do you mean by using parameterized query?
shivv
Participant
1566 Points
283 Posts
Re: Conversion from type 'TimeSpan' to type 'String' is not valid. Help please!
Dec 03, 2012 04:12 AM|LINK
Can you post the data how stored in User_Profile.MonIn, User_Profile.MonOut, User_Profile.TueIn...etc in these column...??
Chen Yu - MS...
All-Star
21596 Points
2493 Posts
Microsoft
Re: Conversion from type 'TimeSpan' to type 'String' is not valid. Help please!
Dec 03, 2012 09:18 AM|LINK
Hi,
Do you means that you want to add the data from tables to dropdownlist? If so, you could add the following property to your dropdownlist:
AppendDataBoundItems ="true". It will help you.
Reference on: http://forums.asp.net/t/1375726.aspx/1
Thanks.
Feedback to us
Develop and promote your apps in Windows Store