Let say I pass values from first page to second page using a form, how can I
retain the value in the corresponding area (text, radio button, texarea, dro
p
down menu)in the first page when i press the back button on the second page.
--
regards seowhuaYou've posted to the wrong newsgroup - this is for SQL Server, not ASP
programming. I'd try posting your question to one of the
microsoft.public.dotnet.framework.aspnet.* newsgroups. (Ditto for your
"ASP Calendar..." post too.)
*mike hodgson*
blog: http://sqlnerd.blogspot.com
seowhua wrote:
>Let say I pass values from first page to second page using a form, how can
I
>retain the value in the corresponding area (text, radio button, texarea, dr
op
>down menu)in the first page when i press the back button on the second page
.
>sql
Showing posts with label form. Show all posts
Showing posts with label form. Show all posts
Sunday, March 25, 2012
ASP, SQL, long text data problems...
I have an ASP page that will take form info that a user has entered,
then save it into SQL server, and retrive and display the info on
another page. My problem is with long text data (10,000 bytes or
more). It appears to save the long text data, as in it gives no
errors... but it does not save it. In the SQL table, the field is
defined as ntext... So why won't it save?
then save it into SQL server, and retrive and display the info on
another page. My problem is with long text data (10,000 bytes or
more). It appears to save the long text data, as in it gives no
errors... but it does not save it. In the SQL table, the field is
defined as ntext... So why won't it save?
Thanks in advance,
adamAdam (ootena@.imsg.com) writes:
> I have an ASP page that will take form info that a user has entered,
> then save it into SQL server, and retrive and display the info on
> another page. My problem is with long text data (10,000 bytes or
> more). It appears to save the long text data, as in it gives no
> errors... but it does not save it. In the SQL table, the field is
> defined as ntext... So why won't it save?
I guess you need to post your code, so that people here can have a
reasonble chance to spot your error. You are using the AppendChunk on
the Field object?
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Adam (ootena@.imsg.com) writes:
> I guess I need an example of the GetChunk, AppendChunk... I havent
> been able to find one that makes sense to me.
In the MDAC Books Online there are examples of using AppendChunk. This
should also be on MSDN.
Did you try searching for AppendChunk on Google? I seem to get quite some
hits.
--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||Cool, thanks for the books online... that helped a lot later man
Thursday, March 22, 2012
ASP Page to run a Report
I am trying to create a report (form) to run from an ASP page with one
single prompt. The ASP pagre would then populate the parameter below
as it passes the data to Report Manager. I am tring to skip the step
of opening IE and go straight to the PDF.
Example
Enter Shipment# [2022222]
[Sumbit] [Quit]
shipnum=variable above
http://rpt-serv/ReportServer?/sales%2fC+of+C&shipnum=2022222&rs:Format=PDF
Does anyone have a sample ASP page they would be willing to share?
Thanks
BobThis may be way more than what you need, but search this newsgroup for my
post on 12/11/07 with the terms "Classic ASP" or Google ""Reporting Service
in Classic ASP" to do exactly what I think you want. Once the PDF is
rendered you can open it in IE, save it, or save it directly as a file on a
server (by taking the stream and writing it).
"Bob" <bob@.ebearings.com> wrote in message
news:fa89138c-aa86-41c8-bdc2-c0fe620972d9@.e4g2000hsg.googlegroups.com...
>I am trying to create a report (form) to run from an ASP page with one
> single prompt. The ASP pagre would then populate the parameter below
> as it passes the data to Report Manager. I am tring to skip the step
> of opening IE and go straight to the PDF.
> Example
> Enter Shipment# [2022222]
> [Sumbit] [Quit]
> shipnum=variable above
> http://rpt-serv/ReportServer?/sales%2fC+of+C&shipnum=2022222&rs:Format=PDF
> Does anyone have a sample ASP page they would be willing to share?
> Thanks
> Bob
single prompt. The ASP pagre would then populate the parameter below
as it passes the data to Report Manager. I am tring to skip the step
of opening IE and go straight to the PDF.
Example
Enter Shipment# [2022222]
[Sumbit] [Quit]
shipnum=variable above
http://rpt-serv/ReportServer?/sales%2fC+of+C&shipnum=2022222&rs:Format=PDF
Does anyone have a sample ASP page they would be willing to share?
Thanks
BobThis may be way more than what you need, but search this newsgroup for my
post on 12/11/07 with the terms "Classic ASP" or Google ""Reporting Service
in Classic ASP" to do exactly what I think you want. Once the PDF is
rendered you can open it in IE, save it, or save it directly as a file on a
server (by taking the stream and writing it).
"Bob" <bob@.ebearings.com> wrote in message
news:fa89138c-aa86-41c8-bdc2-c0fe620972d9@.e4g2000hsg.googlegroups.com...
>I am trying to create a report (form) to run from an ASP page with one
> single prompt. The ASP pagre would then populate the parameter below
> as it passes the data to Report Manager. I am tring to skip the step
> of opening IE and go straight to the PDF.
> Example
> Enter Shipment# [2022222]
> [Sumbit] [Quit]
> shipnum=variable above
> http://rpt-serv/ReportServer?/sales%2fC+of+C&shipnum=2022222&rs:Format=PDF
> Does anyone have a sample ASP page they would be willing to share?
> Thanks
> Bob
ASP and SQL2000 drop-down menu
Okay, I am new to this so be gental.
I have a form with 2 drop down menus and a text field that I am concerned
with. Size, Packaging, and Cost. Size and Packaging pull their info from a
Size and a Packaging table respectively. I need the third field, Cost, to
take the values selected by the user in the Size and Packaging fields, and
use those values to query a third table "Cost." The Cost Table has three
fields: Size, Packaging, and Cost (The cost field is always different
depending on the Size and Packaging chosen).
I have created a server side procedure:
Create Procedure getmycost
@.size int,
@.packaging varchar(50),
@.cost money OUT
AS
Select @.cost=Cost
From Cost
Where
Size = @.size AND
Packaging = @.packaging
I have placed the this code on the client side within Cost the field:
declare @.size int, @.packaging varchar(50), @.cost money
set @.size=Size
set @.packaging=Packaging
Execute getmynumber @.size,@.packaging,@.cost OUT
select @.cost
I am sure I am doing this all wrong;
I am geting an error:
Invalid column name 'Size'
Is this not the way to reference a field on the web form (Size)?
And will this even work?Hi
Did you check out the links in your previous post?
John
"HollyylloH" wrote:
> Okay, I am new to this so be gental.
> I have a form with 2 drop down menus and a text field that I am concerned
> with. Size, Packaging, and Cost. Size and Packaging pull their info from a
> Size and a Packaging table respectively. I need the third field, Cost, to
> take the values selected by the user in the Size and Packaging fields, and
> use those values to query a third table "Cost." The Cost Table has three
> fields: Size, Packaging, and Cost (The cost field is always different
> depending on the Size and Packaging chosen).
> I have created a server side procedure:
> Create Procedure getmycost
> @.size int,
> @.packaging varchar(50),
> @.cost money OUT
> AS
> Select @.cost=Cost
> From Cost
> Where
> Size = @.size AND
> Packaging = @.packaging
> I have placed the this code on the client side within Cost the field:
> declare @.size int, @.packaging varchar(50), @.cost money
> set @.size=Size
> set @.packaging=Packaging
> Execute getmynumber @.size,@.packaging,@.cost OUT
> select @.cost
> I am sure I am doing this all wrong;
> I am geting an error:
> Invalid column name 'Size'
> Is this not the way to reference a field on the web form (Size)?
> And will this even work?
>|||Yes I did check them out. No I can't answer my question from them.
"John Bell" wrote:
> Hi
> Did you check out the links in your previous post?
> John
> "HollyylloH" wrote:
>
I have a form with 2 drop down menus and a text field that I am concerned
with. Size, Packaging, and Cost. Size and Packaging pull their info from a
Size and a Packaging table respectively. I need the third field, Cost, to
take the values selected by the user in the Size and Packaging fields, and
use those values to query a third table "Cost." The Cost Table has three
fields: Size, Packaging, and Cost (The cost field is always different
depending on the Size and Packaging chosen).
I have created a server side procedure:
Create Procedure getmycost
@.size int,
@.packaging varchar(50),
@.cost money OUT
AS
Select @.cost=Cost
From Cost
Where
Size = @.size AND
Packaging = @.packaging
I have placed the this code on the client side within Cost the field:
declare @.size int, @.packaging varchar(50), @.cost money
set @.size=Size
set @.packaging=Packaging
Execute getmynumber @.size,@.packaging,@.cost OUT
select @.cost
I am sure I am doing this all wrong;
I am geting an error:
Invalid column name 'Size'
Is this not the way to reference a field on the web form (Size)?
And will this even work?Hi
Did you check out the links in your previous post?
John
"HollyylloH" wrote:
> Okay, I am new to this so be gental.
> I have a form with 2 drop down menus and a text field that I am concerned
> with. Size, Packaging, and Cost. Size and Packaging pull their info from a
> Size and a Packaging table respectively. I need the third field, Cost, to
> take the values selected by the user in the Size and Packaging fields, and
> use those values to query a third table "Cost." The Cost Table has three
> fields: Size, Packaging, and Cost (The cost field is always different
> depending on the Size and Packaging chosen).
> I have created a server side procedure:
> Create Procedure getmycost
> @.size int,
> @.packaging varchar(50),
> @.cost money OUT
> AS
> Select @.cost=Cost
> From Cost
> Where
> Size = @.size AND
> Packaging = @.packaging
> I have placed the this code on the client side within Cost the field:
> declare @.size int, @.packaging varchar(50), @.cost money
> set @.size=Size
> set @.packaging=Packaging
> Execute getmynumber @.size,@.packaging,@.cost OUT
> select @.cost
> I am sure I am doing this all wrong;
> I am geting an error:
> Invalid column name 'Size'
> Is this not the way to reference a field on the web form (Size)?
> And will this even work?
>|||Yes I did check them out. No I can't answer my question from them.
"John Bell" wrote:
> Hi
> Did you check out the links in your previous post?
> John
> "HollyylloH" wrote:
>
ASP AND SQL SERVER 2005
i cannot connect to my local server.my code is
<%
ad=request.form("ad")
soyad=request.form("soyad")
email=request.form("email")
dim conn
set conn=CreateObject("ADODB.Connection")
conn.open "Server=./SQLEXPRESS;Database=NEWSWORLD;Trusted_Connection=True;"
sqlifadesi="insert into user_info(ad,soyad,email) values('"&ad&"','"&soyad&"','"&email&"')"
conn.execute(sqlifadesi)
conn.close
set conn=nothing
response.write "bilgileriniz kayit edildi,Ilginiz i?in tesekkür ederiz"
%>
it shows error page in
conn.open "Server=./SQLEXPRESS;Database=NEWSWORLD;Trusted_Connection=True;"
i dont use native client because i dont use .net i want to make a connection to sql server 2005 with classic ASP.
Thanks for replies
Hi Ibrahim,
which error are you getting ?
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de|||
thnx for reply Jens Suessmeyer
when i use this code i get this error .... Microsoft OLE DB Service Components (0x80040E21) Work isnt done
<%
ad=request.form("ad")
soyad=request.form("soyad")
email=request.form("email")
dim conn
set conn=CreateObject("ADODB.Connection")
conn.open "data source=.\SQLEXPRESS;Integrated Security=SSPI;AttachDBFilename=|DataDirectory|NEWSWORLD.mdf;User Instance=true"
sqlifadesi="insert into user_info(ad,soyad,email) values('"&ad&"','"&soyad&"','"&email&"')"
conn.execute(sqlifadesi)
conn.close
set conn=nothing
response.write "bilgileriniz kayit edildi,Ilginiz i?in tesekkür ederiz"
%>
when i use this code i get this error...Microsoft OLE DB Provider for ODBC Drivers (0x80004005)
<%
ad=request.form("ad")
soyad=request.form("soyad")
email=request.form("email")
dim conn
set conn=CreateObject("ADODB.Connection")
conn.open "Server=.\SQLEXPRESS;Database=NEWSWORLD;Trusted_Connection=True;"
sqlifadesi="insert into user_info(ad,soyad,email) values('"&ad&"','"&soyad&"','"&email&"')"
conn.execute(sqlifadesi)
conn.close
set conn=nothing
response.write "bilgileriniz kayit edildi,Ilginiz i?in tesekkür ederiz"
%>
i have been working on it cant find a solution.when i use vb.net i can connect to sql server 2005 by using this "Server=.\SQLEXPRESS;Database=NEWSWORLD;Trusted_Connection=True;" but in classic asp i cant connect
Thank u
|||
i have solved my problem thank u i have done like this
<%
ad=request.form("ad")
soyad=request.form("soyad")
email=request.form("email")
dim conn
set conn=CreateObject("ADODB.Connection")
conn.open "Provider=SQLNCLI;Server=.\SQLEXPRESS;Database=NEWSWORLD;Tru sted_Connection=yes;"
sqlifadesi="insert into user_info(ad,soyad,email) values('"&ad&"','"&soyad&"','"&email& ; ;"')"
conn.execute(sqlifadesi)
conn.close
set conn=nothing
response.write "bilgileriniz kayit edildi,Ilginiz i?in tesekkür ederiz"
%>
i have done it with SQL Native Client with no username and password.
Thanks for replies
sqlASP 3.0 Record is getting saved twice.
Hello All,
I am facing this unusual error. In My ASP Page I am submitting the Page and after submitting the form I am calling another ASP page, which contains sql command to insert the record.
My Problem is when I am submitting the Form. Record is getting inserted twice with same values.
What is the cause of this problem as I am facing this problem in every form.
With Regards
Sunil Dutt
First off, you (almost certainly) need to include a UNIQUE constraint to avoid allowing a user interface inserting duplicate data. You probably have a identity value for a primary key.
Second, the fact that I had to say "probably" in the previous statement means we are going to need more information, perhaps code. The post might need to be moved to the ASP forums, but first step, code :)
|||Setting the Primary key concept I had used but that solved the problem but the asp page shows the error. And for a User it seems as there was error and his record is not saved. I am pasting my Code here :
LoggerDataEntryForm.asp
<%@. Language=VBScript %>
<HTML>
<head>
<title>Inerface Services</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link href="CSS/New%20Folder.css" rel="stylesheet" type="text/css">
<link href="CSS/IntStyle.css" rel="stylesheet" type="text/css">
<script language="JavaScript" type="text/JavaScript">
<!--
function formSave()
{
alert('Saving Record');
var frm = document.getElementById("frmUser_Master");
frm.action = "User_MasterAction.asp";
document.getElementById("frmUser_Master").submit();
}
//-->
</script>
</head>
<body leftmargin="0" topmargin="0" marginwidth="0" marginheight="0">
<form name="frmUser_Master" id="frmUser_Master" method="Post">
<table width="100%" height="100%" border="0" cellpadding="0" cellspacing="0">
<tr>
<td align="center" valign="top"><table width="650" border="0" cellspacing="0" cellpadding="0">
<tr>
<td align="left" valign="top" class="PageBkBorder"><table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td class="LogoBk"><table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="100" align="center"><img src="images/interface-logo.gif" width="65" height="86"></td>
<td align="left" class="HeadingName"><br>
Web Tracking</td>
</tr>
</table></td>
</tr>
<tr>
<td height="7" class="GreyLine"><img src="images/Spacer.gif" width="1" height="7"></td>
</tr>
<tr>
<td height="20" class="BlueLine"> <table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr align="center">
<td class="Toplinks"><a href="LogIssue.htm" class="Toplinks">Log
Issue</a></td>
<td width="7"><img src="images/Divider.gif" width="7" height="19"></td>
<td class="Toplinks"><a href="ListDetails.htm" class="Toplinks">View Issue</a></td>
<td width="7"><img src="images/Divider.gif" width="7" height="19"></td>
<td class="Toplinks"><a href="Summary.htm" class="Toplinks">Summary</a></td>
<td width="7"><img src="images/Divider.gif" width="7" height="19"></td>
<td class="Toplinks"><a href="OpenLastSavedIssue.htm" class="Toplinks">Open
Last Saved issue</a></td>
<td width="7"><img src="images/Divider.gif" width="7" height="19"></td>
<td class="Toplinks"><a href="LoggerDataEntryForm.htm" class="Toplinks">Register
Now! </a></td>
</tr>
</table></td>
</tr>
<tr>
<td height="300" align="center" valign="top">
<table width="100%" border="0" cellpadding="0" cellspacing="3">
<tr>
<td align="center"><table width="400" border="0" cellspacing="0" cellpadding="0">
<tr>
<td align="left" class="PageTitle">Logger Entry Form</td>
</tr>
<tr>
<td align="center" class="FormBk"><table border="0" cellpadding="0" cellspacing="3">
<tr>
<td class="LabelText"> </td>
<td> </td>
</tr>
<tr>
<td width="100" class="LabelText">Name</td>
<td> <input id="txtName" name="txtName" type="text" class="FormText">
</td>
</tr>
<tr>
<td class="LabelText">Designation</td>
<td><input id="txtDesig" name="txtDesig" type="text" class="FormText"></td>
</tr>
<tr>
<td class="LabelText">ID No</td>
<td><input id="txtID" name="txtID" type="text" class="FormText"></td>
</tr>
<tr>
<td class="LabelText">Contact No</td>
<td><input id="txtPhoneNo" name="txtPhoneNo" type="text" class="FormText"></td>
</tr>
<tr>
<td height="21" class="LabelText">Mobile</td>
<td><input id="txtMblPhoneNo" name="txtMblPhoneNo" type="text" class="FormText"></td>
</tr>
<tr>
<td class="LabelText">Username <br>
(Email ID)</td>
<td><input id="txtEmailID" name="txtEmailID" type="text" class="FormText"></td>
</tr>
<tr>
<td class="LabelText">Password</td>
<td><input id="txtPwd" name="txtPwd" type="Password" class="FormText"></td>
</tr>
<tr>
<td class="LabelText">Conform Password</td>
<td><input id="txtConPwd" name="txtConPwd" type="Password" class="FormText"></td>
</tr>
<tr>
<td class="LabelText">Branch Details</td>
<td><table border="0" cellspacing="0" cellpadding="0">
<tr>
<td class="SmallText">State</td>
</tr>
<tr>
<td><select name="cmbState">
<option selected>Select All</option>
<option>Andhra Pradesh </option>
<option>Arunachal Pradesh</option>
<option>Assam</option>
<option>Bihar</option>
<option>Chhattisgarh</option>
<option>Goa</option>
<option>Gujarat</option>
<option>Haryana</option>
<option>Himachal Pradesh</option>
<option>Jammu and Kashmir</option>
<option>Jharkhand</option>
<option>Karnataka</option>
<option>Kerala</option>
<option>Madhya Pradesh</option>
<option>Maharashtra</option>
<option>Manipur</option>
<option>Meghalaya</option>
<option>Mizoram</option>
<option>Nagaland</option>
<option>Orissa</option>
<option>Punjab</option>
<option>Rajasthan</option>
<option>Tamil Nadu</option>
<option>Tripura</option>
<option>Uttarakhand</option>
<option>Uttar Pradesh</option>
<option>West Bengal</option>
</select></td>
</tr>
</table></td>
</tr>
<tr>
<td class="LabelText"> </td>
<td><table border="0" cellspacing="0" cellpadding="0">
<tr>
<td class="SmallText">Area</td>
</tr>
<tr>
<td><select name="cmbArea">
<option selected>Select All</option>
<option value="klm1">klm1</option>
<option value="klm2">klm2</option>
<option value="klm3">klm3</option>
</select></td>
</tr>
</table></td>
</tr>
<tr>
<td class="LabelText"> </td>
<td><table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td class="SmallText">City</td>
</tr>
<tr>
<td><select name="cmbCity">
<option selected value="Select">Select</option>
<option selected value="Pune">Pune</option>
</select></td>
</tr>
</table></td>
</tr>
<tr>
<td class="LabelText"> </td>
<td><table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td class="SmallText">Branch</td>
</tr>
<tr>
<td><select name="cmbBranch">
<option selected>Select</option>
<option value="mhlm1">mhlm1</option>
<option value="mhlm2">mhlm2</option>
<option value="mhlm3">mhlm3</option>
<option value="mhlm4">mhlm4</option>
<option value="mhlm5">mhlm5</option>
</select></td>
</tr>
</table></td>
</tr>
<tr>
<td class="LabelText"> </td>
<td> </td>
</tr>
<tr>
<td class="LabelText"> </td>
<td><input type="submit" name="Submit" value="Submit" onclick=formSave();"></td>
</tr>
<tr>
<td class="LabelText"> </td>
<td> </td>
</tr>
</table></td>
</tr>
</table>
</td></tr>
</table></td>
</tr>
<tr>
<td height="11" align="center" valign="top" class="LightBlue"><img src="images/Spacer.gif" width="1" height="10"></td>
</tr>
<tr>
<td align="center" valign="top" class="BlueLine"> </td>
</tr>
</table></td>
</tr>
</table></td>
</tr>
</table>
</form>
</body>
</HTML>
User_MasterAction.asp
<%@. Language=VBScript %>
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<!--#include file="dbcon.asp"-->
</HEAD>
<BODY>
<%
dim user_name,user_designation,User_ID,User_PhoneNo,User_MobileNo,User_email,user_password,user_state,user_branch,user_area,user_city
Response.Write "Test"
user_name = Request.Form("txtName") 'Name
user_designation = Request.Form("txtDesig") 'Designation
User_PhoneNo = Request.Form("txtPhoneNo") 'Phone Number
User_MobileNo = Request.Form("txtMblPhoneNo") 'Mobile Number
User_email = Request.Form("txtEmailID") 'Email ID
user_password = Request.Form("txtPwd") 'Password
user_state = Request.Form("cmbState") 'State
user_branch = Request.Form("cmbBranch") 'Branch
user_area = Request.Form("cmbArea") 'Area
user_city = Request.Form("cmbCity") 'City
dim strSql
strSql = "EXEC SP_UserMaster '" & user_name &"'," & user_designation & ",-1,'" & User_PhoneNo &"','" & User_MobileNo & "','"
strSql = strSql & User_email & "','" & user_password &"','" & user_state & "','" & user_branch & "','"
strSql = strSql & user_area & "','" & user_city &"',1"
Set TBRs = Server.CreateObject("ADODB.RecordSet")
Dim NxtUserID
TBRs.Open strSql,objcon ',adOpenStatic,adLockReadOnly
'NxtUserID = TBRs("NxtUserID").Value
%>
User Registration is Done. <a href="LoggerDataEntryForm.asp">Go To User Registration</a>
</BODY>
</HTML>
Stored Procedure SP_UserMaster
CREATE Procedure SP_UserMaster
@.user_name varchar(100),
@.user_designation int,
@.User_ID int,
@.User_PhoneNo varchar(15),
@.User_MobileNo varchar(20),
@.User_email varchar(50),
@.user_password varchar(15),
@.user_state varchar(50),
@.user_branch varchar(50),
@.user_area varchar(50),
@.user_city varchar(50),
@.mode int
As
--@.mode = 0 Edit
--@.mode = 1 Add
--@.mode = 3 Delete
if @.mode = 1
begin
declare @.nextUserID int
select @.nextUserID=ISNull(max([User_ID]) ,0) + 1 from User_Master
Insert into User_Master ([user_name],user_designation,[User_ID],phone_no,mobile_no,email_id,password,state,branch,area,city)
Values
(@.user_name,@.user_designation,@.nextUserID,@.User_PhoneNo,@.User_MobileNo,@.User_email,@.user_password,@.user_state,@.user_branch,@.user_area,@.user_city)
select @.nextUserID as NxtUserID
end
if @.mode = 0
begin
update User_Master set
[user_name] = @.user_name,
user_designation = @.user_designation,
Phone_No = @.User_PhoneNo,
Mobile_No = @.User_MobileNo,
email_id = @.User_email,
[password] = @.user_password,
state = @.user_state,
branch = @.user_branch,
area = @.user_area,
city = @.user_city where [User_ID] = @.User_ID
end
if @.mode = 3
begin
update User_Master set [User_Status] = 3 where [User_ID] = @.User_ID
end
GO
*** edited to add ***
Figured it out. I had the exact same thing in my code, an input type of "submit" as well as an onclick event that also submitted the form. Once I changed the "submit" to a "button", I stopped getting the duplicates.|||
yes, the problem was because the Javascript function formSave is called from the submit button and in submitform the form is submitted again. So the form was getting submitted twice just comment the submit line of the javascript or call the Java Script function FormSave from the link instead of Submit Button.
Regards
Sunil Dutt
sqlASP 3.0 Record is getting saved twice.
Hello All,
I am facing this unusual error. In My ASP Page I am submitting the Page and after submitting the form I am calling another ASP page, which contains sql command to insert the record.
My Problem is when I am submitting the Form. Record is getting inserted twice with same values.
What is the cause of this problem as I am facing this problem in every form.
With Regards
Sunil Dutt
First off, you (almost certainly) need to include a UNIQUE constraint to avoid allowing a user interface inserting duplicate data. You probably have a identity value for a primary key.
Second, the fact that I had to say "probably" in the previous statement means we are going to need more information, perhaps code. The post might need to be moved to the ASP forums, but first step, code :)
|||Setting the Primary key concept I had used but that solved the problem but the asp page shows the error. And for a User it seems as there was error and his record is not saved. I am pasting my Code here :
LoggerDataEntryForm.asp
<%@. Language=VBScript %>
<HTML>
<head>
<title>Inerface Services</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link href="CSS/New%20Folder.css" rel="stylesheet" type="text/css">
<link href="CSS/IntStyle.css" rel="stylesheet" type="text/css">
<script language="JavaScript" type="text/JavaScript">
<!--
function formSave()
{
alert('Saving Record');
var frm = document.getElementById("frmUser_Master");
frm.action = "User_MasterAction.asp";
document.getElementById("frmUser_Master").submit();
}
//-->
</script>
</head>
<body leftmargin="0" topmargin="0" marginwidth="0" marginheight="0">
<form name="frmUser_Master" id="frmUser_Master" method="Post">
<table width="100%" height="100%" border="0" cellpadding="0" cellspacing="0">
<tr>
<td align="center" valign="top"><table width="650" border="0" cellspacing="0" cellpadding="0">
<tr>
<td align="left" valign="top" class="PageBkBorder"><table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td class="LogoBk"><table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="100" align="center"><img src="images/interface-logo.gif" width="65" height="86"></td>
<td align="left" class="HeadingName"><br>
Web Tracking</td>
</tr>
</table></td>
</tr>
<tr>
<td height="7" class="GreyLine"><img src="images/Spacer.gif" width="1" height="7"></td>
</tr>
<tr>
<td height="20" class="BlueLine"> <table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr align="center">
<td class="Toplinks"><a href="LogIssue.htm" class="Toplinks">Log
Issue</a></td>
<td width="7"><img src="images/Divider.gif" width="7" height="19"></td>
<td class="Toplinks"><a href="ListDetails.htm" class="Toplinks">View Issue</a></td>
<td width="7"><img src="images/Divider.gif" width="7" height="19"></td>
<td class="Toplinks"><a href="Summary.htm" class="Toplinks">Summary</a></td>
<td width="7"><img src="images/Divider.gif" width="7" height="19"></td>
<td class="Toplinks"><a href="OpenLastSavedIssue.htm" class="Toplinks">Open
Last Saved issue</a></td>
<td width="7"><img src="images/Divider.gif" width="7" height="19"></td>
<td class="Toplinks"><a href="LoggerDataEntryForm.htm" class="Toplinks">Register
Now! </a></td>
</tr>
</table></td>
</tr>
<tr>
<td height="300" align="center" valign="top">
<table width="100%" border="0" cellpadding="0" cellspacing="3">
<tr>
<td align="center"><table width="400" border="0" cellspacing="0" cellpadding="0">
<tr>
<td align="left" class="PageTitle">Logger Entry Form</td>
</tr>
<tr>
<td align="center" class="FormBk"><table border="0" cellpadding="0" cellspacing="3">
<tr>
<td class="LabelText"> </td>
<td> </td>
</tr>
<tr>
<td width="100" class="LabelText">Name</td>
<td> <input id="txtName" name="txtName" type="text" class="FormText">
</td>
</tr>
<tr>
<td class="LabelText">Designation</td>
<td><input id="txtDesig" name="txtDesig" type="text" class="FormText"></td>
</tr>
<tr>
<td class="LabelText">ID No</td>
<td><input id="txtID" name="txtID" type="text" class="FormText"></td>
</tr>
<tr>
<td class="LabelText">Contact No</td>
<td><input id="txtPhoneNo" name="txtPhoneNo" type="text" class="FormText"></td>
</tr>
<tr>
<td height="21" class="LabelText">Mobile</td>
<td><input id="txtMblPhoneNo" name="txtMblPhoneNo" type="text" class="FormText"></td>
</tr>
<tr>
<td class="LabelText">Username <br>
(Email ID)</td>
<td><input id="txtEmailID" name="txtEmailID" type="text" class="FormText"></td>
</tr>
<tr>
<td class="LabelText">Password</td>
<td><input id="txtPwd" name="txtPwd" type="Password" class="FormText"></td>
</tr>
<tr>
<td class="LabelText">Conform Password</td>
<td><input id="txtConPwd" name="txtConPwd" type="Password" class="FormText"></td>
</tr>
<tr>
<td class="LabelText">Branch Details</td>
<td><table border="0" cellspacing="0" cellpadding="0">
<tr>
<td class="SmallText">State</td>
</tr>
<tr>
<td><select name="cmbState">
<option selected>Select All</option>
<option>Andhra Pradesh </option>
<option>Arunachal Pradesh</option>
<option>Assam</option>
<option>Bihar</option>
<option>Chhattisgarh</option>
<option>Goa</option>
<option>Gujarat</option>
<option>Haryana</option>
<option>Himachal Pradesh</option>
<option>Jammu and Kashmir</option>
<option>Jharkhand</option>
<option>Karnataka</option>
<option>Kerala</option>
<option>Madhya Pradesh</option>
<option>Maharashtra</option>
<option>Manipur</option>
<option>Meghalaya</option>
<option>Mizoram</option>
<option>Nagaland</option>
<option>Orissa</option>
<option>Punjab</option>
<option>Rajasthan</option>
<option>Tamil Nadu</option>
<option>Tripura</option>
<option>Uttarakhand</option>
<option>Uttar Pradesh</option>
<option>West Bengal</option>
</select></td>
</tr>
</table></td>
</tr>
<tr>
<td class="LabelText"> </td>
<td><table border="0" cellspacing="0" cellpadding="0">
<tr>
<td class="SmallText">Area</td>
</tr>
<tr>
<td><select name="cmbArea">
<option selected>Select All</option>
<option value="klm1">klm1</option>
<option value="klm2">klm2</option>
<option value="klm3">klm3</option>
</select></td>
</tr>
</table></td>
</tr>
<tr>
<td class="LabelText"> </td>
<td><table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td class="SmallText">City</td>
</tr>
<tr>
<td><select name="cmbCity">
<option selected value="Select">Select</option>
<option selected value="Pune">Pune</option>
</select></td>
</tr>
</table></td>
</tr>
<tr>
<td class="LabelText"> </td>
<td><table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td class="SmallText">Branch</td>
</tr>
<tr>
<td><select name="cmbBranch">
<option selected>Select</option>
<option value="mhlm1">mhlm1</option>
<option value="mhlm2">mhlm2</option>
<option value="mhlm3">mhlm3</option>
<option value="mhlm4">mhlm4</option>
<option value="mhlm5">mhlm5</option>
</select></td>
</tr>
</table></td>
</tr>
<tr>
<td class="LabelText"> </td>
<td> </td>
</tr>
<tr>
<td class="LabelText"> </td>
<td><input type="submit" name="Submit" value="Submit" onclick=formSave();"></td>
</tr>
<tr>
<td class="LabelText"> </td>
<td> </td>
</tr>
</table></td>
</tr>
</table>
</td></tr>
</table></td>
</tr>
<tr>
<td height="11" align="center" valign="top" class="LightBlue"><img src="images/Spacer.gif" width="1" height="10"></td>
</tr>
<tr>
<td align="center" valign="top" class="BlueLine"> </td>
</tr>
</table></td>
</tr>
</table></td>
</tr>
</table>
</form>
</body>
</HTML>
User_MasterAction.asp
<%@. Language=VBScript %>
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<!--#include file="dbcon.asp"-->
</HEAD>
<BODY>
<%
dim user_name,user_designation,User_ID,User_PhoneNo,User_MobileNo,User_email,user_password,user_state,user_branch,user_area,user_city
Response.Write "Test"
user_name = Request.Form("txtName") 'Name
user_designation = Request.Form("txtDesig") 'Designation
User_PhoneNo = Request.Form("txtPhoneNo") 'Phone Number
User_MobileNo = Request.Form("txtMblPhoneNo") 'Mobile Number
User_email = Request.Form("txtEmailID") 'Email ID
user_password = Request.Form("txtPwd") 'Password
user_state = Request.Form("cmbState") 'State
user_branch = Request.Form("cmbBranch") 'Branch
user_area = Request.Form("cmbArea") 'Area
user_city = Request.Form("cmbCity") 'City
dim strSql
strSql = "EXEC SP_UserMaster '" & user_name &"'," & user_designation & ",-1,'" & User_PhoneNo &"','" & User_MobileNo & "','"
strSql = strSql & User_email & "','" & user_password &"','" & user_state & "','" & user_branch & "','"
strSql = strSql & user_area & "','" & user_city &"',1"
Set TBRs = Server.CreateObject("ADODB.RecordSet")
Dim NxtUserID
TBRs.Open strSql,objcon ',adOpenStatic,adLockReadOnly
'NxtUserID = TBRs("NxtUserID").Value
%>
User Registration is Done. <a href="LoggerDataEntryForm.asp">Go To User Registration</a>
</BODY>
</HTML>
Stored Procedure SP_UserMaster
CREATE Procedure SP_UserMaster
@.user_name varchar(100),
@.user_designation int,
@.User_ID int,
@.User_PhoneNo varchar(15),
@.User_MobileNo varchar(20),
@.User_email varchar(50),
@.user_password varchar(15),
@.user_state varchar(50),
@.user_branch varchar(50),
@.user_area varchar(50),
@.user_city varchar(50),
@.mode int
As
--@.mode = 0 Edit
--@.mode = 1 Add
--@.mode = 3 Delete
if @.mode = 1
begin
declare @.nextUserID int
select @.nextUserID=ISNull(max([User_ID]) ,0) + 1 from User_Master
Insert into User_Master ([user_name],user_designation,[User_ID],phone_no,mobile_no,email_id,password,state,branch,area,city)
Values
(@.user_name,@.user_designation,@.nextUserID,@.User_PhoneNo,@.User_MobileNo,@.User_email,@.user_password,@.user_state,@.user_branch,@.user_area,@.user_city)
select @.nextUserID as NxtUserID
end
if @.mode = 0
begin
update User_Master set
[user_name] = @.user_name,
user_designation = @.user_designation,
Phone_No = @.User_PhoneNo,
Mobile_No = @.User_MobileNo,
email_id = @.User_email,
[password] = @.user_password,
state = @.user_state,
branch = @.user_branch,
area = @.user_area,
city = @.user_city where [User_ID] = @.User_ID
end
if @.mode = 3
begin
update User_Master set [User_Status] = 3 where [User_ID] = @.User_ID
end
GO
*** edited to add ***
Figured it out. I had the exact same thing in my code, an input type of "submit" as well as an onclick event that also submitted the form. Once I changed the "submit" to a "button", I stopped getting the duplicates.|||
yes, the problem was because the Javascript function formSave is called from the submit button and in submitform the form is submitted again. So the form was getting submitted twice just comment the submit line of the javascript or call the Java Script function FormSave from the link instead of Submit Button.
Regards
Sunil Dutt
ASP 3.0 Record is getting saved twice.
Hello All,
I am facing this unusual error. In My ASP Page I am submitting the Page and after submitting the form I am calling another ASP page, which contains sql command to insert the record.
My Problem is when I am submitting the Form. Record is getting inserted twice with same values.
What is the cause of this problem as I am facing this problem in every form.
With Regards
Sunil Dutt
First off, you (almost certainly) need to include a UNIQUE constraint to avoid allowing a user interface inserting duplicate data. You probably have a identity value for a primary key.
Second, the fact that I had to say "probably" in the previous statement means we are going to need more information, perhaps code. The post might need to be moved to the ASP forums, but first step, code :)
|||Setting the Primary key concept I had used but that solved the problem but the asp page shows the error. And for a User it seems as there was error and his record is not saved. I am pasting my Code here :
LoggerDataEntryForm.asp
<%@. Language=VBScript %>
<HTML>
<head>
<title>Inerface Services</title>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<link href="CSS/New%20Folder.css" rel="stylesheet" type="text/css">
<link href="CSS/IntStyle.css" rel="stylesheet" type="text/css">
<script language="JavaScript" type="text/JavaScript">
<!--
function formSave()
{
alert('Saving Record');
var frm = document.getElementById("frmUser_Master");
frm.action = "User_MasterAction.asp";
document.getElementById("frmUser_Master").submit();
}
//-->
</script>
</head>
<body leftmargin="0" topmargin="0" marginwidth="0" marginheight="0">
<form name="frmUser_Master" id="frmUser_Master" method="Post">
<table width="100%" height="100%" border="0" cellpadding="0" cellspacing="0">
<tr>
<td align="center" valign="top"><table width="650" border="0" cellspacing="0" cellpadding="0">
<tr>
<td align="left" valign="top" class="PageBkBorder"><table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td class="LogoBk"><table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td width="100" align="center"><img src="images/interface-logo.gif" width="65" height="86"></td>
<td align="left" class="HeadingName"><br>
Web Tracking</td>
</tr>
</table></td>
</tr>
<tr>
<td height="7" class="GreyLine"><img src="images/Spacer.gif" width="1" height="7"></td>
</tr>
<tr>
<td height="20" class="BlueLine"> <table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr align="center">
<td class="Toplinks"><a href="LogIssue.htm" class="Toplinks">Log
Issue</a></td>
<td width="7"><img src="images/Divider.gif" width="7" height="19"></td>
<td class="Toplinks"><a href="ListDetails.htm" class="Toplinks">View Issue</a></td>
<td width="7"><img src="images/Divider.gif" width="7" height="19"></td>
<td class="Toplinks"><a href="Summary.htm" class="Toplinks">Summary</a></td>
<td width="7"><img src="images/Divider.gif" width="7" height="19"></td>
<td class="Toplinks"><a href="OpenLastSavedIssue.htm" class="Toplinks">Open
Last Saved issue</a></td>
<td width="7"><img src="images/Divider.gif" width="7" height="19"></td>
<td class="Toplinks"><a href="LoggerDataEntryForm.htm" class="Toplinks">Register
Now! </a></td>
</tr>
</table></td>
</tr>
<tr>
<td height="300" align="center" valign="top">
<table width="100%" border="0" cellpadding="0" cellspacing="3">
<tr>
<td align="center"><table width="400" border="0" cellspacing="0" cellpadding="0">
<tr>
<td align="left" class="PageTitle">Logger Entry Form</td>
</tr>
<tr>
<td align="center" class="FormBk"><table border="0" cellpadding="0" cellspacing="3">
<tr>
<td class="LabelText"> </td>
<td> </td>
</tr>
<tr>
<td width="100" class="LabelText">Name</td>
<td> <input id="txtName" name="txtName" type="text" class="FormText">
</td>
</tr>
<tr>
<td class="LabelText">Designation</td>
<td><input id="txtDesig" name="txtDesig" type="text" class="FormText"></td>
</tr>
<tr>
<td class="LabelText">ID No</td>
<td><input id="txtID" name="txtID" type="text" class="FormText"></td>
</tr>
<tr>
<td class="LabelText">Contact No</td>
<td><input id="txtPhoneNo" name="txtPhoneNo" type="text" class="FormText"></td>
</tr>
<tr>
<td height="21" class="LabelText">Mobile</td>
<td><input id="txtMblPhoneNo" name="txtMblPhoneNo" type="text" class="FormText"></td>
</tr>
<tr>
<td class="LabelText">Username <br>
(Email ID)</td>
<td><input id="txtEmailID" name="txtEmailID" type="text" class="FormText"></td>
</tr>
<tr>
<td class="LabelText">Password</td>
<td><input id="txtPwd" name="txtPwd" type="Password" class="FormText"></td>
</tr>
<tr>
<td class="LabelText">Conform Password</td>
<td><input id="txtConPwd" name="txtConPwd" type="Password" class="FormText"></td>
</tr>
<tr>
<td class="LabelText">Branch Details</td>
<td><table border="0" cellspacing="0" cellpadding="0">
<tr>
<td class="SmallText">State</td>
</tr>
<tr>
<td><select name="cmbState">
<option selected>Select All</option>
<option>Andhra Pradesh </option>
<option>Arunachal Pradesh</option>
<option>Assam</option>
<option>Bihar</option>
<option>Chhattisgarh</option>
<option>Goa</option>
<option>Gujarat</option>
<option>Haryana</option>
<option>Himachal Pradesh</option>
<option>Jammu and Kashmir</option>
<option>Jharkhand</option>
<option>Karnataka</option>
<option>Kerala</option>
<option>Madhya Pradesh</option>
<option>Maharashtra</option>
<option>Manipur</option>
<option>Meghalaya</option>
<option>Mizoram</option>
<option>Nagaland</option>
<option>Orissa</option>
<option>Punjab</option>
<option>Rajasthan</option>
<option>Tamil Nadu</option>
<option>Tripura</option>
<option>Uttarakhand</option>
<option>Uttar Pradesh</option>
<option>West Bengal</option>
</select></td>
</tr>
</table></td>
</tr>
<tr>
<td class="LabelText"> </td>
<td><table border="0" cellspacing="0" cellpadding="0">
<tr>
<td class="SmallText">Area</td>
</tr>
<tr>
<td><select name="cmbArea">
<option selected>Select All</option>
<option value="klm1">klm1</option>
<option value="klm2">klm2</option>
<option value="klm3">klm3</option>
</select></td>
</tr>
</table></td>
</tr>
<tr>
<td class="LabelText"> </td>
<td><table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td class="SmallText">City</td>
</tr>
<tr>
<td><select name="cmbCity">
<option selected value="Select">Select</option>
<option selected value="Pune">Pune</option>
</select></td>
</tr>
</table></td>
</tr>
<tr>
<td class="LabelText"> </td>
<td><table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td class="SmallText">Branch</td>
</tr>
<tr>
<td><select name="cmbBranch">
<option selected>Select</option>
<option value="mhlm1">mhlm1</option>
<option value="mhlm2">mhlm2</option>
<option value="mhlm3">mhlm3</option>
<option value="mhlm4">mhlm4</option>
<option value="mhlm5">mhlm5</option>
</select></td>
</tr>
</table></td>
</tr>
<tr>
<td class="LabelText"> </td>
<td> </td>
</tr>
<tr>
<td class="LabelText"> </td>
<td><input type="submit" name="Submit" value="Submit" onclick=formSave();"></td>
</tr>
<tr>
<td class="LabelText"> </td>
<td> </td>
</tr>
</table></td>
</tr>
</table>
</td></tr>
</table></td>
</tr>
<tr>
<td height="11" align="center" valign="top" class="LightBlue"><img src="images/Spacer.gif" width="1" height="10"></td>
</tr>
<tr>
<td align="center" valign="top" class="BlueLine"> </td>
</tr>
</table></td>
</tr>
</table></td>
</tr>
</table>
</form>
</body>
</HTML>
User_MasterAction.asp
<%@. Language=VBScript %>
<HTML>
<HEAD>
<META NAME="GENERATOR" Content="Microsoft Visual Studio 6.0">
<!--#include file="dbcon.asp"-->
</HEAD>
<BODY>
<%
dim user_name,user_designation,User_ID,User_PhoneNo,User_MobileNo,User_email,user_password,user_state,user_branch,user_area,user_city
Response.Write "Test"
user_name = Request.Form("txtName") 'Name
user_designation = Request.Form("txtDesig") 'Designation
User_PhoneNo = Request.Form("txtPhoneNo") 'Phone Number
User_MobileNo = Request.Form("txtMblPhoneNo") 'Mobile Number
User_email = Request.Form("txtEmailID") 'Email ID
user_password = Request.Form("txtPwd") 'Password
user_state = Request.Form("cmbState") 'State
user_branch = Request.Form("cmbBranch") 'Branch
user_area = Request.Form("cmbArea") 'Area
user_city = Request.Form("cmbCity") 'City
dim strSql
strSql = "EXEC SP_UserMaster '" & user_name &"'," & user_designation & ",-1,'" & User_PhoneNo &"','" & User_MobileNo & "','"
strSql = strSql & User_email & "','" & user_password &"','" & user_state & "','" & user_branch & "','"
strSql = strSql & user_area & "','" & user_city &"',1"
Set TBRs = Server.CreateObject("ADODB.RecordSet")
Dim NxtUserID
TBRs.Open strSql,objcon ',adOpenStatic,adLockReadOnly
'NxtUserID = TBRs("NxtUserID").Value
%>
User Registration is Done. <a href="LoggerDataEntryForm.asp">Go To User Registration</a>
</BODY>
</HTML>
Stored Procedure SP_UserMaster
CREATE Procedure SP_UserMaster
@.user_name varchar(100),
@.user_designation int,
@.User_ID int,
@.User_PhoneNo varchar(15),
@.User_MobileNo varchar(20),
@.User_email varchar(50),
@.user_password varchar(15),
@.user_state varchar(50),
@.user_branch varchar(50),
@.user_area varchar(50),
@.user_city varchar(50),
@.mode int
As
--@.mode = 0 Edit
--@.mode = 1 Add
--@.mode = 3 Delete
if @.mode = 1
begin
declare @.nextUserID int
select @.nextUserID=ISNull(max([User_ID]) ,0) + 1 from User_Master
Insert into User_Master ([user_name],user_designation,[User_ID],phone_no,mobile_no,email_id,password,state,branch,area,city)
Values
(@.user_name,@.user_designation,@.nextUserID,@.User_PhoneNo,@.User_MobileNo,@.User_email,@.user_password,@.user_state,@.user_branch,@.user_area,@.user_city)
select @.nextUserID as NxtUserID
end
if @.mode = 0
begin
update User_Master set
[user_name] = @.user_name,
user_designation = @.user_designation,
Phone_No = @.User_PhoneNo,
Mobile_No = @.User_MobileNo,
email_id = @.User_email,
[password] = @.user_password,
state = @.user_state,
branch = @.user_branch,
area = @.user_area,
city = @.user_city where [User_ID] = @.User_ID
end
if @.mode = 3
begin
update User_Master set [User_Status] = 3 where [User_ID] = @.User_ID
end
GO
*** edited to add ***
Figured it out. I had the exact same thing in my code, an input type of "submit" as well as an onclick event that also submitted the form. Once I changed the "submit" to a "button", I stopped getting the duplicates.
|||
yes, the problem was because the Javascript function formSave is called from the submit button and in submitform the form is submitted again. So the form was getting submitted twice just comment the submit line of the javascript or call the Java Script function FormSave from the link instead of Submit Button.
Regards
Sunil Dutt
Thursday, February 16, 2012
Arithmetic overflow error converting expression to data type datetime
I have a sql server 2000 database...using vb.net 2005... I have a form which allow user to find outstanding transaction by month selected in dropdownlist... If it matches the month it will populate out in datagrid, this date is store as dd/MM/yyyy... below is the following code:
Protected Sub Page_Load(ByVal senderAs Object,ByVal eAs System.EventArgs)Handles Me.LoadIf ddl_payment.SelectedItem.Text ="Outstanding"Then ddl_months.Visible =True datagrid1.Visible =False If ddl_months.Visible =True Then config.dataReader2("SELECT DISTINCT coName FROM custTransaction WHEREDATEPART(mm,dateOfPurchase)='" + ddl_months.SelectedValue + "'AND balance > 0") bindData() datagrid1.Visible = False datagrid.Visible = True End If ElseIf ddl_payment.SelectedItem.Text = "Fully Paid" Then config.dataReader2("SELECT DISTINCT coName FROM custTransaction WHERE balance = 0") bindData1() datagrid.Visible =False datagrid1.Visible =True ddl_months.Visible =False Else datagrid.Visible =False datagrid1.Visible =False ddl_months.Visible =False End If End SubBelow is the error:
Arithmetic overflow error converting expression to data type datetime.
Description:An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.
Exception Details:System.Data.SqlClient.SqlException: Arithmetic overflow error converting expression to data type datetime.
Source Error:
Line 76: cmd.CommandText = sqlStatementLine 77: cmd.Connection = connLine 78: cmd.ExecuteNonQuery()Line 79: Line 80: reader = cmd.ExecuteReader
Source File:c:\inetpub\wwwroot\TAKA\App_Code\config.vb Line:78
Stack Trace:
[SqlException (0x80131904): Arithmetic overflow error converting expression to data type datetime.] System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection) +862234 System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection) +739110 System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj) +188 System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj) +1956 System.Data.SqlClient.SqlCommand.RunExecuteNonQueryTds(String methodName, Boolean async) +192 System.Data.SqlClient.SqlCommand.InternalExecuteNonQuery(DbAsyncResult result, String methodName, Boolean sendToPipe) +380 System.Data.SqlClient.SqlCommand.ExecuteNonQuery() +135 TAKA.config.dataReader2(String sqlStatement) in c:\inetpub\wwwroot\TAKA\App_Code\config.vb:78 TAKA.PaymentStatus.Page_Load(Object sender, EventArgs e) in c:\inetpub\wwwroot\TAKA\PaymentStatus.aspx.vb:65 System.Web.UI.Control.OnLoad(EventArgs e) +99 System.Web.UI.Control.LoadRecursive() +47 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +1061
I have another table which is staff table and it datefield is store in MM/dd/yyyy format
DATEPART returns an integer so wrappingddl_months.SelectedValue in quotes is probably pointless.
Seeing as it's a sql exception I would say it's extremely likely that the field "dateOfPurchase" is of the wrong type. Perhaps ur storing it as a string or an int...?
Also sql doesn't store dates in a locale specific format, the fact that u've said "I have another table whihc is staff table and it datefield is store in MM/dd/yyyy format" suggests to me ur storing ur dates as strings which will not work.
Furthermore executing unparameterised queries against sql is slap-worthy offence. Use parameters... or get hacked.
I waited so long for it approved zzz...ya...worldspawn thx for reply... 4get to said that my date is in nvarchar format... erm so any idea how to get the month in the database in this format? Or do I need change it to smalldatetime format? I need smth like For example my database has this date(23/10/2007, 21/09/2007,22/10/2007)
How can i retrieve by month, If ddl_months i selected '10' den that 2 records with month 10 will shown in datagrid...
Thank in adv
|||If u want to keep it as a string u'll need to store it in a local generic format such as yyyy-mm-dd. You should then be able to CAST/CONVERT to smalldatetime and then run DATEPART on the converted value.
But i would highly recommend storing it as smalldatetime; unless you have some need to store it as text...?
If you just change it to smalldatetime your code will start working (except remove the quotes from ur sql string. 10 not '10')
That's interesting about the approval. I used to get that but stopped seeing it. I assumed they'd just made everything unmoderated but it's probably just for user's that havent reached 'participant' status.
|||Would it be safe if i jus change my database dateOfPurchase to smalldatetime format? Cos i received prompt when trying to save... It say data may be lost when trying to convert nvarchar to smalldatetime... below is a few line example of my code... I select a date from calendar and display it in a textbox then save all the data enter in database... will there be any serious problem if i change?
PrivateSub Page_Load(ByVal senderAs System.Object,ByVal eAs System.EventArgs)HandlesMyBase.Load
todayD.Text = Calendar1.TodaysDate().ToString("dd/MM/yyyy")
End Sub
PrivateSub Calendar1_SelectionChanged(ByVal senderAs System.Object,ByVal eAs System.EventArgs)Handles Calendar1.SelectionChanged'display selected date from calendar
tb_dop.Text = Calendar1.SelectedDate().ToString("dd/MM/yyyy")EndSub
PublicSub AddTransaction1(ByVal dateOfPurchaseAsString,ByVal totalAmountAsString,ByVal paymentReceivedAsString,ByVal bAsString,ByVal receiptInvoiceAsString,ByVal remarksAsString,ByVal csngInvoiceAsString,ByVal transDetailsAsString,ByVal coNameAsString)'declare the INSERT method for INSERT transaction
Dim strSQLAsString ="INSERT INTO custTransaction (dateOfPurchase, totalAmount, paymentReceived, balance, receiptInvoice, remarks, csngInvoice, transDetails, coName) VALUES ('" + dateOfPurchase +"'," + totalAmount +"," + paymentReceived +"," + b +",'" + receiptInvoice +"','" + remarks +"', '" + csngInvoice +"', '" + transDetails +"', '" + coName +"')"
con1.Open()
Dim cmdAsNew SqlCommand(strSQL, con1)cmd.ExecuteNonQuery()
con1.Close()
EndSub
PrivateSub btn_add2_Click(ByVal senderAs System.Object,ByVal eAs System.EventArgs)Handles btn_add2.ClickdateOfPurchase = tb_dop.Text
If tb_csngInvoice.Text ="None"Then
'check for duplication
If tb_receiptInvoice.Text = config.dataReader2("SELECT receiptInvoice FROM custTransaction WHERE receiptInvoice = '" + tb_receiptInvoice.Text +"'")Then
emsg.Text ="The InvoiceNo. already exist OR cannot be None"
'ElseIf tb_csngInvoice.Text = config.dataReader2("SELECT csngInvoice FROM custTransaction WHERE csngInvoice = '" + tb_csngInvoice.Text + "'") Then
' emsg.Text = " The csngInvoiceNo. already exist"
Else
emsg.Text =""
AddTransaction1(dateOfPurchase, tb_totalAmount.Text, tb_payment.Text, tb_bal.Text, tb_receiptInvoice.Text, tb_remarks.Text, tb_csngInvoice.Text, tb_transD.Text, lbl_coName3.Text)
'redirect to add transaction successful page
Response.Redirect("AddTransactionSuccessfully.aspx")EndIf
ElseIf tb_receiptInvoice.Text ="None"Then
'check for duplication
If tb_csngInvoice.Text = config.dataReader2("SELECT csngInvoice FROM custTransaction WHERE csngInvoice = '" + tb_csngInvoice.Text +"'")Then
emsg.Text ="The InvoiceNo. already exist OR cannot be None"
'ElseIf tb_csngInvoice.Text = config.dataReader2("SELECT csngInvoice FROM custTransaction WHERE csngInvoice = '" + tb_csngInvoice.Text + "'") Then
' emsg.Text = " The csngInvoiceNo. already exist"
Else
emsg.Text =""
AddTransaction1(dateOfPurchase, tb_totalAmount.Text, tb_payment.Text, tb_bal.Text, tb_receiptInvoice.Text, tb_remarks.Text, tb_csngInvoice.Text, tb_transD.Text, lbl_coName3.Text)
'redirect to add transaction successful page
Response.Redirect("AddTransactionSuccessfully.aspx")EndIf
EndIf
EndSub
Thank you for ur time
|||Is this production database? Honestly i have no idea what would happen exactly but i think it's safe to say ur existing date fields will become incorrect and it may encounter and error trying to convert them and not even work... make sure sql server's date format is dmy and it should work without any problems, if it's still set to mdy u'll have big problems.
Also if you want to display a date in the form dd/MM/yyyy use the inbuilt ToShortDateString method which will convert the date to that format (as long as ur server is configured to use that dateformat by default).
Please read a tutorial on how to property construct a sql command using parameters and not string concatenation. String concatentation leaves u open to sql injection attacks... plus it's just crappy.
|||Hi, worldspawn can i know how u convert the sql server to display date in dd/MM/yyyy... although i have set the my own server to UK time but it still display as MM/dd/yyyy... Thanks
|||I'm not a sql guru but I believe u type:
SET DATEFORMAT dmy
So just connect to the sql server with query analyzer or whatever and execute that. It's 6pm, i'm off home, best of luck :)
Hi,
I think the here type of the field as being smalldatetime or datetime is not the issue.
Smalldatetime can handle dates between 1/1/1900 and 6/6/2079 with the accuracy of 1 minute
Datetime can handle dates between 1/1/1753 and 31/12/9999 with the accuracy of 3.33 miliseconds.
So you do not need to change the type of the field. Do it if you have billions of rows to decrease the database size because datetime consumes 8 bytes and smalldatetime consumes 4 bytes. But as mentioned above the accuracy should not be important for that filed.
I think shifting from datetime to smalldatetime will increase the performance.
If you want to know if some data would be truncated.
select dateOfPurchase from custTransaction where dateOfPurchase < cast('01/01/1900 00:00:00' as datetime) and dateOfPurchase > cast('06/06/2079 00:00:00' as datetime)run this if you have any result than dont convert to smalldatetime.
About the main problem, I am not sure you should analyze the script with profiler but maybe this helps;
First be sure ddl_months.SelectedValue is integer
then try the code as
config.dataReader2("SELECT DISTINCT coName FROM custTransaction WHEREDATEPART(m,dateOfPurchase) = cast('"+ ddl_months.SelectedValue + "', as integer)AND balance > 0")
Hope this helps.
|||Hi yvzman,
A nice infomation to note... Thank for replying... But i think is use CONVERT instead of CAST... dunno y CAST is not longer supported by my script
Monday, February 13, 2012
argue why sql2005
well, I need to justify to my client, why to switch to sql2005 form 2000. I
know a lot of 'why it's better', but my client don't really care most of
those aspects.
I need to argue, my client's business gains on that.
Quite frankly your client is right. Why do you think they should
switch? Oh, right, you don't know. If you don't know then there
couldn't possibly be a compelling reason to do so. Have you had
problems with 2000? Is there some feature in an application that your
client wants that you can only implement with 2005? If you can't
answer those questions then there's really no reason to upgrade -- or,
from your client's perspective, spend the money to upgrade.|||fireball wrote:
If they are happy with their existing system then there perhaps isn't
much reason to switch right now.
Since we don't know your client's business needs, we can't help you.
Maybe some of the security improvements? Like built-in encryption of data.
this sounds quite reasonable, I quess client is gonna like idea he can have
some data hidden from the world. I seems to be not easy to find anything
what client will be even able to understand :_)
Yes, performance is one (you can't use an index on an encrypted
column).
Yes, if you forget to backup the certificates.
Correction: you can't use an index for ordering and range searches. You
can use it for exact searches.
ugh I dodn't know it
in practice this means: no more patches after 04.08, - right?
6 to be precise. :-)
To be perfectly honest, I don't think is a very good argument for
upgrading to SQL 2005. It rather makes the argument "we can be on all
this update trains, so we wait for the next one". And Microsoft has
indicated that from now, it's a two-year release cycle for SQL Server.
>
6 to be precise. :-)
>
>
To be perfectly honest, I don't think is a very good argument for
upgrading to SQL 2005. It rather makes the argument "we can be on all
this update trains, so we wait for the next one". And Microsoft has
indicated that from now, it's a two-year release cycle for SQL Server.
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
know a lot of 'why it's better', but my client don't really care most of
those aspects.
I need to argue, my client's business gains on that.
Thnaks in advance.fireball wrote:
Quote:
Originally Posted by
well, I need to justify to my client, why to switch to sql2005 form 2000. I
know a lot of 'why it's better', but my client don't really care most of
those aspects.
I need to argue, my client's business gains on that.
>
Thnaks in advance.
Quite frankly your client is right. Why do you think they should
switch? Oh, right, you don't know. If you don't know then there
couldn't possibly be a compelling reason to do so. Have you had
problems with 2000? Is there some feature in an application that your
client wants that you can only implement with 2005? If you can't
answer those questions then there's really no reason to upgrade -- or,
from your client's perspective, spend the money to upgrade.|||fireball wrote:
Quote:
Originally Posted by
well, I need to justify to my client, why to switch to sql2005 form 2000. I
know a lot of 'why it's better', but my client don't really care most of
those aspects.
I need to argue, my client's business gains on that.
>
Thnaks in advance.
If they are happy with their existing system then there perhaps isn't
much reason to switch right now.
Consider upgrading if they want to do new development or add a new
application or if they can take advantage of some new features with
their existing system (mirroring for example).
--
David Portas, SQL Server MVP
Whenever possible please post enough code to reproduce your problem.
Including CREATE TABLE and INSERT statements usually helps.
State what version of SQL Server you are using and specify the content
of any error messages.
SQL Server Books Online:
http://msdn2.microsoft.com/library/...US,SQL.90).aspx
--|||fireball (fireball@.onet.kropka.eu) writes:
Quote:
Originally Posted by
well, I need to justify to my client, why to switch to sql2005 form
2000. I know a lot of 'why it's better', but my client don't really care
most of those aspects.
I need to argue, my client's business gains on that.
Since we don't know your client's business needs, we can't help you.
As David and ZeldorBlat said, if the client is happy with the system,
there is little reason to change.
But if your client is asking you to add new feature, and you can
confidently say that it would be so much easier to do with SQL 2005,
then you have a selling point. The difficult part is of course that
in many cases the difference is minor. While we as a programmer don't
want to be without row_number() or improved error handling once we've
used it, it's not really sufficient to convince the client to move on.
But here are two-three possible areas where the difference is high
enough:
1) General auditing. This can be easily be done in a generic fashion
with FOR XML and the new xml data type.
2) Any sort of queues - go Service Broker!
3) A heavy use of blobs (text/ntext/image). The new data types makes
it so much easier for you as a programmer, and thus cuts development
costs.
I could probably add a few more, but it gives you an idea what to look
for.
Of course, there will be a day when SQL 2000 reaches end-of-life support,
but it is not going to happen soon, and there might a be yet a new
SQL Server version out by then.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||fireball wrote:
Quote:
Originally Posted by
well, I need to justify to my client, why to switch to sql2005 form
2000. I know a lot of 'why it's better', but my client don't really
care most of those aspects.
I need to argue, my client's business gains on that.
Maybe some of the security improvements? Like built-in encryption of data.
That would (probably... if it works...) have cut down considerably on my
latest development project.
/jim|||Uytkownik "Jim Andersen" <jba020@.politiSLET.dk.invalidnapisa w
wiadomoci
Quote:
Originally Posted by
Maybe some of the security improvements? Like built-in encryption of data.
this sounds quite reasonable, I quess client is gonna like idea he can have
some data hidden from the world. I seems to be not easy to find anything
what client will be even able to understand :_)
Are there any disadvanatages of using data encryption? Is there any risk I
will loose ability to decrypt data from my database(/backup)?|||fireball wrote:
Quote:
Originally Posted by
Are there any disadvanatages of using data encryption?
Yes, performance is one (you can't use an index on an encrypted
column).
Quote:
Originally Posted by
Is there any risk I will loose ability to decrypt data from my database(/backup)?
Yes, if you forget to backup the certificates.
Razvan|||Razvan Socol wrote:
Quote:
Originally Posted by
[...] you can't use an index on an encrypted column
Correction: you can't use an index for ordering and range searches. You
can use it for exact searches.
Razvan|||Hi Fireball
The main reason i'd suggest a client to upgrade to SQL 2005 is the
Microsoft Support Lifecycle for SQL server 2000.
http://support.microsoft.com/lifecycle/?p1=2852
Like it or not SQL Server 2000 is a 7 year old product RDBMS now.
"If its not broken doesnt fix it" doesn't apply to most IT platforms
these days, b'coz of the cost of running legacy/obsolete systems. Plus
the cost of employing IT personnel
So like it or no Microsoft is going to discontinue support for SQL
Server 2000. Therefore your clients got ~ 1 1/2 years to upgrade to SQL
Server 2005. (Unless with Extended Support)
As a DBA I really would wont to take the risk of supporting a platform
thats not supported by the vendor.
Regards
Nuwan
SQL / ORACLE - DBA
*** Sent via Developersdex http://www.developersdex.com ***|||Uzytkownik "Nuwan K" <ckid@.hotmail.comnapisal w wiadomosci
news:459da872$0$497
Quote:
Originally Posted by
Microsoft Support Lifecycle for SQL server 2000.
ugh I dodn't know it
in practice this means: no more patches after 04.08, - right?
So if hackers find some bug and they use it on 2008-04-09, then it's
deadline indeed, but otherwise - it's 1 1/2 and a while, right?|||Nuwan K (ckid@.hotmail.com) writes:
Quote:
Originally Posted by
Like it or not SQL Server 2000 is a 7 year old product RDBMS now.
6 to be precise. :-)
Quote:
Originally Posted by
"If its not broken doesnt fix it" doesn't apply to most IT platforms
these days, b'coz of the cost of running legacy/obsolete systems. Plus
the cost of employing IT personnel
>
So like it or no Microsoft is going to discontinue support for SQL
Server 2000. Therefore your clients got ~ 1 1/2 years to upgrade to SQL
Server 2005. (Unless with Extended Support)
To be perfectly honest, I don't think is a very good argument for
upgrading to SQL 2005. It rather makes the argument "we can be on all
this update trains, so we wait for the next one". And Microsoft has
indicated that from now, it's a two-year release cycle for SQL Server.
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Erland makes a good point. The essence of any argument about
upgrading is that eventually they will have to upgrade, either to SQL
2005 or some other version. The question is: do they want to be
proactive, and take time to plan it, or do they want to be reactive,
and have their schedule for upgrading dictated by market forces.
None of our vendors that supply SQL Server products require SQL 2005,
but I'm betting that new releases this year will begin requiring SS2005
databases. In other words, our current apps don't require 2005, but
future versions may. I'd rather start planning for that contingency
now than be forced into it by a necessary patch for a vendor software.
I'm not saying that upgrades are the only option; could always buy a
new server for applications that require SS2005.
Stu
Erland Sommarskog wrote:
Quote:
Originally Posted by
Nuwan K (ckid@.hotmail.com) writes:
Quote:
Originally Posted by
Like it or not SQL Server 2000 is a 7 year old product RDBMS now.
>
6 to be precise. :-)
>
Quote:
Originally Posted by
"If its not broken doesn't fix it" doesn't apply to most IT platforms
these days, b'coz of the cost of running legacy/obsolete systems. Plus
the cost of employing IT personnel
So like it or no Microsoft is going to discontinue support for SQL
Server 2000. Therefore your client's got ~ 1 1/2 years to upgrade to SQL
Server 2005. (Unless with Extended Support)
>
To be perfectly honest, I don't think is a very good argument for
upgrading to SQL 2005. It rather makes the argument "we can be on all
this update trains, so we wait for the next one". And Microsoft has
indicated that from now, it's a two-year release cycle for SQL Server.
>
>
--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
>
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Subscribe to:
Posts (Atom)