Showing posts with label enter. Show all posts
Showing posts with label enter. Show all posts

Sunday, March 11, 2012

Data not posting to SQL Database

I have a asp.net page that is supposed to post data to a sqlserver 2000 database. The page opens fine and let's you enter the data and submit it, but the data never appears in the database. I've set up exception handling to catch duplicate records, invalid date/time format and then general sql exceptions and general system exceptions. Either something is going wrong before the try...catch or it's not throwing an exception and I don't get any runtime or designtime errors>>>
'This code adds values to the fields to be submitted
SqlConnection1.Open()
SqlInsertCommand1.Parameters("@.Site").Value() = Server.HtmlEncode(ddlSite.SelectedValue)
SqlInsertCommand1.Parameters("@.RepMonth").Value() = Server.HtmlEncode(ddlMonth.SelectedValue)
SqlInsertCommand1.Parameters("@.SerDate").Value() = Server.HtmlEncode(txtDate.Text)
SqlInsertCommand1.Parameters("@.VolName").Value() = Server.HtmlEncode(txtName.Text)
SqlInsertCommand1.Parameters("@.SSNum").Value() = Server.HtmlEncode(txtSSNum.Text)
SqlInsertCommand1.Parameters("@.ArtCraftHrs").Value = Server.HtmlEncode(Val(txtArt.Text))
SqlInsertCommand1.Parameters("@.TransHrs").Value = Server.HtmlEncode(Val(txtTrans.Text))
SqlInsertCommand1.Parameters("@.EscortHrs").Value = Server.HtmlEncode(Val(txtEscort.Text))
SqlInsertCommand1.Parameters("@.InfoRefHrs").Value = Server.HtmlEncode(Val(txtInfo.Text))
SqlInsertCommand1.Parameters("@.ProgAsstHrs").Value = Server.HtmlEncode(Val(txtProgAsst.Text))
SqlInsertCommand1.Parameters("@.SHopAsstHrs").Value = Server.HtmlEncode(Val(txtShopAsst.Text))
SqlInsertCommand1.Parameters("@.FoodAidHrs").Value = Server.HtmlEncode(Val(txtFoodAid.Text))
SqlInsertCommand1.Parameters("@.HomeDelHrs").Value = Server.HtmlEncode(Val(txtDelAid.Text))
SqlInsertCommand1.Parameters("@.SubmitDate").Value = Server.HtmlEncode(Now().ToShortDateString)

'This code creates a sqlcommand to return data to the datagird

Dim sqlstr As String
sqlstr = "declare @.Date as char(12)set @.Date=(select rtrim(cast(datepart(mm,getdate()) as c" & _
"har(2)))+'/'+cast(datepart(dd,getdate()) as char(2))+'/'+cast(datepart(yy,getdat" & _
"e()) as char(4)))SELECT Site, RepMonth, SerDate, VolName, SSNum, ArtCraftHrs, Tr" & _
"ansHrs, EscortHrs, InfoRefHrs, ProgAsstHrs, ShopAsstHrs, FoodAidHrs, HomeDelHrs," & _
" SubmitDate FROM dbo.VolDetailReport where submitdate = @.Date and Site = '" + ddlSite.SelectedItem.Text + "'"
Me.SqlSelectCommand1.CommandText = sqlstr
Me.SqlSelectCommand1.Connection = Me.SqlConnection1
Try

SqlInsertCommand1.ExecuteNonQuery()
SqlDataAdapter1.Fill(DataSet11, "VolDetailReport")
dgDisplay.DataBind()
SqlConnection1.Close()
Dim volSubmitDate As String
volSubmitDate = DatePart(DateInterval.Month, Now()) & " / " & DatePart(DateInterval.Day, Now()) & " / " & DatePart(DateInterval.Year, Now())
Response.Cookies("VolSubmitted").Value = volSubmitDate
Response.Cookies("VolSubmitted").Expires = DateTime.MaxValue
Catch ex As SqlClient.SqlException When ex.Number = 242
'Checks for improper format of the data fields
lblSqlError.Visible = "True"
lblSqlError.Text = "The Date is not in the form m/d/yyyy"
Catch ex As SqlClient.SqlException When ex.Number = 2627
'Checks for Primary Key violation ie(Already existing record)
lblSqlError.Text = "This Record has already been submitted"
Catch ex As Exception
lblSqlError.Text = ex.Message
End Try

Reset_Page()Can you run SQL Profiler and see what is actually being passed to SQL Server? I have found this to be very helpful.|||I don't have sysadmin rights to create a trace in SQL Profiler. What I did was do a step into debug and setup a break in the button click procedure. It didn't find a problem and when I checked the database the data was there!!!! I don't know what the problem was, but it seems to have fixed itself.

Thanks for the info

Friday, February 24, 2012

Data lost after INSERT command

I have a website that is used to enter dates from a calendar. For example, user A might go to the website and select 20 different days in January. When user A clicks on the Save button the dates are written to the database with an INSERT command. This works fine. However, when user B then goes through the same procedure, say selecting 5 dates that are in the set selected by user A, those dates are properly saved in the database but the rows containing user A's same 5 dates disappear. I suspect this is something simple but I'm new and ignorant. Please help.Are you sure that when User B selects dates that are in the set selected by User A you are not using an Update statement instead of an Insert statement?|||It would be very helpful if you would post the queries involved, as well as the table DDL.|||Thank you for taking the time to respond. I have a bit of code where I do a DELETE prior to doing the INSERT. Your question led me to examine my DELETE statement again, only to discover that I had made it too broad so that it was deleting all records having a particular date, not just the one of the user currently logged in. I will adopt the suggestion to include code in future postings. Thanks again.

Tuesday, February 14, 2012

Data from Excel into SQLserver 2005

Hi Folks,
I'm a novice user of sqlserver. I'm planned to run webqueries in excel. And
finally the fresh data should enter sqlserver als well.
Can one of you advise me how to handle this with some clear and stepwise
directions?
Hi
SQL Server 2005 (Dev)
The file contains two fields (Lname,FName)
CREATE TABLE dbo.Users
(
LName VARCHAR(50) NOT NULL,
FName VARCHAR(50) NOT NULL
)
INSERT INTO dbo.Users
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=c:\TEST.xls;Extended Properties=Excel 8.0')...Sheet1$
SELECT * FROM dbo.Users
"JerrelA" <JerrelA@.discussions.microsoft.com> wrote in message
news:159A8952-2B70-4442-A035-2165EA70F3F4@.microsoft.com...
> Hi Folks,
> I'm a novice user of sqlserver. I'm planned to run webqueries in excel.
> And
> finally the fresh data should enter sqlserver als well.
> Can one of you advise me how to handle this with some clear and stepwise
> directions?

Data from Excel into SQLserver 2005

Hi Folks,
I'm a novice user of sqlserver. I'm planned to run webqueries in excel. And
finally the fresh data should enter sqlserver als well.
Can one of you advise me how to handle this with some clear and stepwise
directions?Hi
SQL Server 2005 (Dev)
The file contains two fields (Lname,FName)
CREATE TABLE dbo.Users
(
LName VARCHAR(50) NOT NULL,
FName VARCHAR(50) NOT NULL
)
INSERT INTO dbo.Users
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=c:\TEST.xls;Extended Properties=Excel 8.0')...Sheet1$
SELECT * FROM dbo.Users
"JerrelA" <JerrelA@.discussions.microsoft.com> wrote in message
news:159A8952-2B70-4442-A035-2165EA70F3F4@.microsoft.com...
> Hi Folks,
> I'm a novice user of sqlserver. I'm planned to run webqueries in excel.
> And
> finally the fresh data should enter sqlserver als well.
> Can one of you advise me how to handle this with some clear and stepwise
> directions?

Data from Excel into SQLserver 2005

Hi Folks,
I'm a novice user of sqlserver. I'm planned to run webqueries in excel. And
finally the fresh data should enter sqlserver als well.
Can one of you advise me how to handle this with some clear and stepwise
directions?Hi
SQL Server 2005 (Dev)
The file contains two fields (Lname,FName)
CREATE TABLE dbo.Users
(
LName VARCHAR(50) NOT NULL,
FName VARCHAR(50) NOT NULL
)
INSERT INTO dbo.Users
SELECT * FROM OPENDATASOURCE('Microsoft.Jet.OLEDB.4.0',
'Data Source=c:\TEST.xls;Extended Properties=Excel 8.0')...Sheet1$
SELECT * FROM dbo.Users
"JerrelA" <JerrelA@.discussions.microsoft.com> wrote in message
news:159A8952-2B70-4442-A035-2165EA70F3F4@.microsoft.com...
> Hi Folks,
> I'm a novice user of sqlserver. I'm planned to run webqueries in excel.
> And
> finally the fresh data should enter sqlserver als well.
> Can one of you advise me how to handle this with some clear and stepwise
> directions?