Problem With Inserting Or Updating Dates In Mssql 2000
Apr 12, 2006
Hello.I've read many topics about this problem but i couldn't figure it out.
I use form where user must insert 2 dates using texboxes.-One is required and other is optional.
Sql 2000 is inserting either '20061105' or '2006.11.05' on insert update but select query returns 05.11.2006 on my report.
Question 1.How do I insert or update dates from my form where date is entered dd.mm.yyyy to sql 2000 table?
question 2. What to do if user left optional texbox date empty.
I'm using SP and function with arguments (byval texbox1.text as date, byval texbox2.text as date)
and parameters @date1, sqldbtype date =texbox1.text
View 2 Replies
ADVERTISEMENT
Sep 12, 2005
I have been trying to add values to a database and it keeps failing
i have no idea what i am doing wrong please help the code is asp.net
using vb. I have been having serious trouble passing check boxes in
forms from day one both singularly and dynamically from datagrids
if someone could show me some sample code of how to pass these
sort of values into the component and on to the query in this way
i would very much appreciate it.
Fuzzygoth
the error returned is
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:
INSERT statement conflicted with COLUMN FOREIGN KEY constraint
'tblStation_FK00'. The conflict occurred in database 'TealSQL', table
'tblTravelPoint', column 'travelpointID'.
The statement has been terminated.
I have marked the area of the code the error is returned in the colour Violet and with a ##
The code i am using is below
## The aspx page ##
<%@ Page Language="vb" Debug="true" Trace="True"
Inherits="Devotion2Motion.AdminComp"
Src="../CodeBehind/AdminModule.vb" %>
<!-- Binds the ActivityResortInfo.ascx user control to the page -->
<%@ Register TagPrefix="UserContol" TagName="D2MHeader" Src="../UserControls/Header.ascx" %>
<%@ Register TagPrefix="UserContol" TagName="D2MFooter" Src="../UserControls/Footer.ascx" %>
<%@ Register TagPrefix="UserContol" TagName="TravelPointDD" Src="../UserControls/TravelPointDD.ascx" %>
<script language="vb" runat="server">
Sub Page_Load()
If IsPostback = True Then
Dim aInternational As Integer
Dim Station As String = Request.Form("StationFrm")
Dim Type As String = Request.Form("TypeFrm")
Dim Address1 As String = Request.Form("address1Frm")
Dim Address2 As String = Request.Form("address2Frm")
Dim City As String = Request.Form("cityFrm")
Dim International As String = Request.Form("InternationalFrm")
Dim TravelPoint As Integer = Request.Form("_ctl5:dsTravelPointDD")
If IsNothing(International) Then
aInternational = "0"
Else
aInternational = "1"
End If
Dim AdminTravelPoints As New Devotion2Motion.AdminComp()
' Select the country dropdown list
AdminTravelPoints.AddStation(Station, Type, Address1, Address2, City,
aInternational, TravelPoint)
End If
Dim ReadResultTable As New Devotion2Motion.AdminComp()
' Select the country dropdown list
dsResultSet.DataSource = ReadResultTable.GetStationtbl()
dsResultSet.DataBind()
End Sub
</script>
<!-- This UserControl Pulls in the header UserControl and the Div Tag Positions it #css reffrence is TopControl -->
<Div Class="TopControl">
<UserContol:D2MHeader runat="server"/>
</Div>
<form method="post" action="AdminTravelPoint.aspx" runat="server">
<Div Class="CentreControl">
<table border="0">
<tr>
<td>
<asp:Label Text="Station Name: " CssClass="BodyStyle" runat="server"/>
</td>
<td>
<asp:TextBox ID="StationFrm" CssClass="PrimaryGrid" runat="server"/>
</td>
<tr>
<tr>
<td>
<asp:Label Text="Type: " CssClass="BodyStyle" runat="server"/>
</td>
<td>
<asp:TextBox ID="TypeFrm" CssClass="PrimaryGrid" runat="server"/>
</td>
<tr>
<tr>
<td>
<asp:Label Text="Address1: " CssClass="BodyStyle" runat="server"/>
</td>
<td>
<asp:TextBox ID="Address1Frm" CssClass="PrimaryGrid" runat="server"/>
</td>
<tr>
<tr>
<td>
<asp:Label Text="Address2: " CssClass="BodyStyle" runat="server"/>
</td>
<td>
<asp:TextBox ID="Address2Frm" CssClass="PrimaryGrid" runat="server"/>
</td>
<tr>
<tr>
<td>
<asp:Label Text="City: " CssClass="BodyStyle" runat="server"/>
</td>
<td>
<asp:TextBox ID="CityFrm" CssClass="PrimaryGrid" runat="server"/>
</td>
<tr>
<tr>
<td>
<asp:Label Text="International: " CssClass="BodyStyle" runat="server"/>
</td>
<td>
<asp:CheckBox ID="InternationalFrm" CssClass="PrimaryGrid" runat="server"/>
</td>
<tr>
</tr>
<td>
<asp:Label Text="Travel Point: " CssClass="BodyStyle" runat="server"/>
</td>
<td>
<UserContol:TravelPointDD runat="server"/>
</td>
<tr>
</tr>
<td>
</td>
<td>
<input value="Submit" type="submit">
</td>
</tr>
</table>
</Div>
<asp:DataGrid ID="dsResultSet" AutoGenerateColumns="False" CssClass="PrimaryGrid" ShowHeader="true" runat="server">
<Columns>
<asp:BoundColumn DataField="StationID" ReadOnly="true" HeaderText="Station ID"/>
<asp:BoundColumn DataField="Station" ReadOnly="true" HeaderText="Station"/>
<asp:BoundColumn DataField="Type" ReadOnly="true" HeaderText="Type"/>
<asp:BoundColumn DataField="Address1" ReadOnly="true" HeaderText="Address"/>
<asp:BoundColumn DataField="Address2" ReadOnly="true" HeaderText=" "/>
<asp:BoundColumn DataField="City" ReadOnly="true" HeaderText="City"/>
<asp:BoundColumn DataField="International" ReadOnly="true" HeaderText="International"/>
<asp:BoundColumn DataField="TravelPoint" ReadOnly="true" HeaderText="City"/>
</Columns>
</asp:DataGrid>
</form>
<Div Class="CenterControl">
<UserContol:D2MFooter runat="server"/>
</Div>
##the vb componet that passess to the sql query ##
Public Function AddStation(ByVal Station As String, ByVal Type As
String, ByVal Address1 As String, ByVal Address2 As String, ByVal City
As String, ByVal aInternational As Integer, ByVal TravelPoint As
Integer) As SqlDataReader
' Create Instance of Connection and Command Object
Dim
myConnection As New
SqlConnection(ConfigurationSettings.AppSettings("strConn"))
Dim
myCommand As New SqlCommand("sp_call_Station_Insert", myConnection)
' Mark the Command as a SPROC
myCommand.CommandType = CommandType.StoredProcedure
' Add Parameters to SPROC
Dim
parameterStation As New SqlParameter("@Station", SqlDbType.NVarChar, 50)
parameterStation.Value = Station
myCommand.Parameters.Add(parameterStation)
' Add Parameters to SPROC
Dim
parameterType As New SqlParameter("@Type", SqlDbType.NVarChar, 50)
parameterType.Value = Type
myCommand.Parameters.Add(parameterType)
' Add Parameters to SPROC
Dim
parameterAddress1 As New SqlParameter("@Address1", SqlDbType.NVarChar,
50)
parameterAddress1.Value = Address1
myCommand.Parameters.Add(parameterAddress1)
' Add Parameters to SPROC
Dim
parameterAddress2 As New SqlParameter("@Address2", SqlDbType.NVarChar,
50)
parameterAddress2.Value = Address2
myCommand.Parameters.Add(parameterAddress2)
' Add Parameters to SPROC
Dim
parameterCity As New SqlParameter("@City", SqlDbType.NVarChar, 50)
parameterCity.Value = City
myCommand.Parameters.Add(parameterCity)
' Add Parameters to SPROC
Dim
parameteraInternational As New SqlParameter("@aInternational",
SqlDbType.Int, 4)
parameteraInternational.Value = aInternational
myCommand.Parameters.Add(parameteraInternational)
' Add Parameters to SPROC
Dim
parameterTravelPoint As New SqlParameter("@TravelPoint", SqlDbType.Int,
4)
parameterTravelPoint.Value = TravelPoint
myCommand.Parameters.Add(parameterTravelPoint)
' Execute the command
myConnection.Open()
## Dim result As SqlDataReader = myCommand.ExecuteReader(CommandBehavior.CloseConnection)
' Return the datareader
Return result
End Function
## The sql stored procedure ##
CREATE PROCEDURE [dbo].[sp_call_Station_Insert]
(
@Station As nVarChar(50),
@Type As nVarChar(50),
@address1 As nVarChar(50),
@address2 As nVarChar(50),
@City As nVarChar(50),
@aInternational As nVarChar(50),
@TravelPoint As Int
)
AS
DECLARE @ConVale As nVarChar(50)
SET @ConVale = (SELECT Station FROM tblStation WHERE @Station = Station)
If @ConVale = @Station
BEGIN
SELECT * FROM tblStation
END
ELSE
BEGIN
insert into tblStation
(Station, Type, address1, address2, city, International, TravelPoint)
values (@Station, @Type, @address1, @address2, @City, @aInternational, @TravelPoint)
SELECT * FROM tblStation
END
GO
View 1 Replies
View Related
Jan 10, 2006
Hi, Assume I have a table name "myTime". This table is simply only have 1 (one) DATETIME field "MyTestTime" (also serve as a primary number).Table MyTime- MyTestTime : SQLTYPE DATETIMETo insert a new row into this field, I simply wrote :SqlCommand sqlCommand = new SqlCommand("insert into MyTime values('2006-01-09')", sqlConnection);
I got the value of "2006-01-09" from a textbox or other relevan control.I realize when I try to use "SELECT * FROM MyTime" statement, MSSQL server 2000 automatically convert my date value from "2006-01-09" to "01/09/2006" (from YYYY-MM-DD to MM/DD/YYYY). I don't know why this one must be converted to MM/DD/YYYY automatically (I believe this behavior is depend on some "setting option" in my MSSQL server - but I don't know which one).The challenge is :In my country, the actual date format is like German Date format (DD-MM-YYY). Well I know this is only "Customization" problem. But how insert datetime value given from sql query to a datetime variable?// Connect to database, make a query, get the datareader result, and bla bla blaDateTime aDateTime = new DateTime;aDateTime = Convert.ToDateTime(myDataReader["PostDate"].ToString());// close connectionMy question isHow can I make sure that aDateTime's day is 09 not 01. How my program know that 09 is day not month. I can't use string.split() method because it's possible that my database setting will change from "mm-dd-yyyy" to "dd-mm-yyyy"thanks
View 4 Replies
View Related
Sep 21, 2006
I am very new to SQL Server 2005. I have created a package to load data from a flat delimited file to a database table. The initial load has worked. However, in the future, I will have flat files used to update the table. Some of the records will need to be inserted and some will need to update existing rows. I am trying to do this from SSIS. However, I am very lost as to how to do this.
Any suggestions?
View 7 Replies
View Related
Sep 8, 2006
I am building a site which provides an online handbook. I have two fields - date and update_date. The first is when the contents was originally entered and the second is meant to be updated every time the record is changed.
I have no idea how to do this - any help welcome.
View 7 Replies
View Related
Sep 6, 2006
Does enabling/disabling Data Execution Prevention have a performanceimpact on SQL 2000 or SQL 2005?For SQL best performance - how should I configure for:Processor Scheduling:Programs or Background servicesMemory Usage:Programs or System Cache
View 9 Replies
View Related
Mar 24, 2008
Hi,
I am a bit new to the MSSQL server. In our application, we use so many SQL queries. To imporve the performance, we used the Database enigine Tuning tool to create the indexes. The older version of the application supports MSSQL 2000 also. To re-create these new indexes, I have an issue in running these "CREATE INDEX" commands as the statements generated for index creation are done in MSSQL 2005. The statements include "INCLUDES" keyword which is supported in MSSQL 2005 but not in MSSQL 2000.
Ex:-
CREATE INDEX IND_001_PPM_PA ON PPM_PROCESS_ACTIVITY
(ACTIVITY_NAME ASC, PROCESS_NAME ASC, START_TIME ASC, ISMONITORED ASC)
INCLUDE
(INSTANCE_ID, ACTIVITY_TYPE, STATUS, END_TIME, ORGANIZATION);
Any help in creating such indexes in 2000 version is welcome.
Thanks,
Suresh.
View 2 Replies
View Related
May 3, 2008
Hello
We are using SQL 2005 and now we are planning to use SQL 2000. what are the ways to do the process.
We taken the script spcificall for 2000 and run it in SQL 200. But we are getting the error in SCRIPT?
Could you please give me the step to do?
Thanks,
Sankar R
View 6 Replies
View Related
Dec 17, 2007
I'm the assistant to the assistants assistant and have no SQL base at all but have to change dates in our SQL database. I've got the script for how to do an update/replace for non date fields and have been successful at that. Unfortunately when I try to use the script to change date fields it does not work. It goes through the process and acts like it is working (even asks me if I want to update the records) but when I look in the database the dates have not changed. Below is the script that I am using, any help you can give me will help me keep my job (and pay for the beer I'm sure to need after work today) Thanks in advance for your help. I am trying to change the date '30/08/2007' to '28/08/2008'.
My Script:
update Main_table set valuedate=replace(valuedate,'30/08/2007','28/08/2008')
where country='FR'
and valuedate='30/08/2007'
View 4 Replies
View Related
Sep 20, 2006
Hi all,I have a table called Jobs, with fields PK auto increment job_id, job_name, date. I'm using the visual web developer, .net 2.0, sql server 2005.I'm trying to have an option in one of my forms that allows me to add Jobs for a specific time frame. Say I want to add a job called "JobOne" and that I expect this job to last 2 months, so I would like to add this "JobOne" from October 1st 2006 to December 1st 2006. Than in the table "jobs" I would see JobOne in October 1st, 2nd, 3rd... all the way to December 1st. I'm familiar how to insert single values from formviews, using sqldatasources but I have no idea how to insert something like this, so I was wondering if anyone out there could help.Thanks!
View 1 Replies
View Related
Nov 1, 2004
Is it possible to have sql server automatically record date and time (in a designated field)when a record is created in the db? This may seam basic but it has caused me a lot of grief.
View 8 Replies
View Related
Dec 7, 2004
here is what happens..... i create a sql statement with a date parameter and execute the insert via my asp.net page.
strsql = "insert into table(id, lastdate, id2) values ('id'," & date.today & ",'id2')"
this produces the following date in my database which is 'smalldate' datatype = 1/1/1900
View 3 Replies
View Related
Oct 26, 2006
When i try to insert into a datetime field i keep getting this error.
Msg 242, Level 16, State 3, Procedure Sproc_01220_Tour_PendingPayment, Line 27
The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.
The statement has been terminated.
(1 row(s) affected)
i am passing to the date parameters the date in this format dd/mm/yyyy
Any ideas
ALTER PROCEDURE [dbo].[Sproc_01220_Tour_PendingPayment]
@MemberIdint=Null,
@TourStartDateDateTime=Null,
@Initial_TourQtyint=Null,
@PaidUntillDateTime=Null
AS
BEGIN--Overall
SET NOCOUNT ON;
BEGIN--Insert
INSERT INTOTbl_01040_Tour_Join_PendingPayment
(Pend_MemberId,
Pend_TourStartDate,
Pend_Initial_TourQty,
Pend_PaidUntill)
VALUES (@MemberId,
@TourStartDate,
@Initial_TourQty,
@PaidUntill)
END--Insert
View 7 Replies
View Related
May 9, 2008
Im working on a project to convert a C program from OCI(oracle) to ODBC(MsSQL).
One of the select statements uses oracle julian time:
"Select TO_NUMBER(TO_CHAR(TO_DATE('0101'||:styear,'mmddyyyy'),'J')) STYEAR from dual"
Its not a problem converting the statement, except msSQL uses 10/01/1900 as its origin for the julain calendar, where Oracle uses somewhere around 5000BC. my questions is, because this program is made to use oracle julian and need to keep it that way, how i can make the SQL conversion using oracles origin date?
View 13 Replies
View Related
Apr 18, 2008
Hi Guys,I need some help here. I've created a asp.net reception system for internal use. The problem I'm having is this, when I update a record all the dates are saved as 01/01/1900. I've had a look at a couple of threads on various forums and sites but none of them seem to have a solution for my problem... I have an AJAX calenderExtenders on the date text boxes and i have played around with all different date formats, currently on yyyy/MM/dd. I've also tried DateTime.Parse(txtStartDate.text) but doesn't work. I pass all the parameters to a stored procedure on the sql server in the correct order. I've included my code below, I have only included date parameters. SqlConnection conn = null; try { string connString = ConfigurationManager.ConnectionStrings["RCM"].ConnectionString; //Initialise the sqlConnection object conn = new SqlConnection(); conn.ConnectionString = connString; //Getting connection string to the db //Create the command SqlCommand command = new SqlCommand("UpdateClient", conn); command.CommandType = CommandType.StoredProcedure; //Opening connection to the db conn.Open(); //Setting input parameters command.Parameters.AddWithValue("@DOB", txtDOB.Text); command.Parameters.AddWithValue("@StartDate",txtStartDate.Text); //Execute T-SQL command.ExecuteNonQuery(); //Close connection command.Connection.Close(); conn.Close(); } catch (Exception ex) { lblError.Text = "An error has occured: " + ex.Message; }
View 18 Replies
View Related
Jun 15, 2006
Ben writes "I have a sql script that doesn't function very well when it's executed on a SQL 2000 server.
The scrpt looks like this:
---------------------------------------------------------------------------------------------------
USE [master]
GO
IF NOT EXISTS (SELECT * FROM master.dbo.syslogins WHERE loginname = N'SSDBUSERNAME')
EXEC sp_addlogin N'SSDBUSERNAME', N'SSDBPASSWORD'
GO
GRANT ADMINISTER BULK OPERATIONS TO [SSDBUSERNAME]
GO
GRANT AUTHENTICATE SERVER TO [SSDBUSERNAME]
GO
GRANT CONNECT SQL TO [SSDBUSERNAME]
GO
GRANT CONTROL SERVER TO [SSDBUSERNAME]
GO
GRANT CREATE ANY DATABASE TO [SSDBUSERNAME]
GO
USE [master]
GO
If EXISTS (Select * FROM master.dbo.syslogins WHERE loginname = N'SSDBUSERNAME')
ALTER LOGIN [SSDBUSERNAME] WITH PASSWORD=N'SSDBPASSWORD'
GO
GRANT ADMINISTER BULK OPERATIONS TO [SSDBUSERNAME]
GO
GRANT AUTHENTICATE SERVER TO [SSDBUSERNAME]
GO
GRANT CONNECT SQL TO [SSDBUSERNAME]
GO
GRANT CONTROL SERVER TO [SSDBUSERNAME]
GO
GRANT CREATE ANY DATABASE TO [SSDBUSERNAME]
GO
USE [master]
GO
IF EXISTS (select * from dbo.sysdatabases where name = 'ISIZ')
DROP DATABASE [ISIZ]
GO
USE [SurveyData]
GO
exec sp_adduser 'SSDBUSERNAME'
GRANT INSERT, UPDATE, SELECT, DELETE
TO SSDBUSERNAME
GO
USE [SurveyManagement]
GO
exec sp_adduser 'SSDBUSERNAME'
GRANT INSERT, UPDATE, SELECT, DELETE
TO SSDBUSERNAME
---------------------------------------------------------------
I need to be converted to a script that can be executed on both MSSQL 2000 and MSSQL 2005.
I was wondering if somebody there could help me with this problem?!
Thanks,
Ben"
View 1 Replies
View Related
Nov 17, 2007
I've been tasked to move our production databases on MSSQL 2000 to 2005. I've supported MSSQL since version 6.5 and performed migrations to successor versions.
Current Environment is MSSQL 2000 32-bit with current Service Packs.
I've performed mock migrations on Test servers upgrading all Production instances simultaneously from MSSQL 2000 to 2005 32-bit. The Test environment is identical to Production minus server name, IP etc. Also I have a separate server with MSSQL 2005 installed where I use the DETACH / ATTACH and BACKUP / RESTORE method for migration / acceptance testing. There are approximately 30 databases totaling 70 GB. This has gone as expected and fairly successful. Vendors have been coordinated with to update code and staff for acceptance testing.
I'd prefer going directly to MSSQL 2005 64-bit instead if possible due to memory benefits etc. This is where I'd like some feedback prior to borrowing a 64-bit server for testing.
Upgrade options:
1. Is it better to migrate from MSSQL 2000 32-bit to 2005 64-bit via:
a. DETACH / ATTACH
b. BACKUP / RESTORE
c. Is one method more advantageous relating to the end result?
2. Regarding XP clients, have issues been experienced with the default SQL Server driver or is an alternate recommended for XP clients to connect to a MSSQL 64-bit server databases?
3. If you have performed this migration and have relevant experience please pass them along.
View 3 Replies
View Related
Mar 8, 2007
i am using visual web developer 2005 and SQL Express 2005 with VB as the code behindi have a button and in the button click event i have written codes to INSERT to a database table - it has one primary keyso when i click the button, if there is already a row with primary key fields value as 10 and if i try to INSERT with the same value in the primary key field there will occur primary key constraintso , if i try to INSERT with the already existing primary key fields value, instead of INSERTing it should be UPDATEd without generating any errorplease help me
View 1 Replies
View Related
Jul 16, 2002
Does anybody have a sample SQL script that will select table A and compare it to table B. If a row exists in both table A and table B, it will update the columns in table B with the columns in table A. If the row does not exist in table B, it will insert a row in table B using the row in table A. Is this possible?
Thanks Mitch
View 2 Replies
View Related
Dec 12, 2004
Hi all,
I have trying to insert into my database in MSSQL.
However, everytime I run it it connects, but does not update to the database.
Below is the code I am using.
Please help me find out why it isn't inserting the new record into the database
Imports System.Data
Imports System.Data.SqlClient
Public Class AdminPage
Inherits System.Web.UI.Page
Dim StrConn = "Server='localhost';UID='sa'; database='TrackError'"
#Region " Web Form Designer Generated Code "
'This call is required by the Web Form Designer.
<System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
End Sub
Protected WithEvents Label1 As System.Web.UI.WebControls.Label
Protected WithEvents txtMessage As System.Web.UI.WebControls.TextBox
Protected WithEvents txtScreenName As System.Web.UI.WebControls.TextBox
Protected WithEvents txtTime As System.Web.UI.WebControls.TextBox
Protected WithEvents Label2 As System.Web.UI.WebControls.Label
Protected WithEvents Label3 As System.Web.UI.WebControls.Label
Protected WithEvents Label4 As System.Web.UI.WebControls.Label
Protected WithEvents Button1 As System.Web.UI.WebControls.Button
Protected WithEvents Button2 As System.Web.UI.WebControls.Button
Protected WithEvents txtid As System.Web.UI.WebControls.TextBox
Protected WithEvents Label5 As System.Web.UI.WebControls.Label
Protected WithEvents fixed As System.Web.UI.WebControls.TextBox
'NOTE: The following placeholder declaration is required by the Web Form Designer.
'Do not delete or move it.
Private designerPlaceholderDeclaration As System.Object
Private Sub Page_Init(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Init
'CODEGEN: This method call is required by the Web Form Designer
'Do not modify it using the code editor.
InitializeComponent()
End Sub
#End Region
Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
'Put user code to initialize the page here
End Sub
Private Sub TextBox1_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles txtMessage.TextChanged
End Sub
Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
'If (txtScreenName.Text = "" Or txtTime.Text = "" Or txtMessage.Text = "") Then
'Exit Sub
'End If
Dim Insert As String
Dim MSqlConn As New SqlConnection(StrConn)
'Dim added As Integer
Try
MSqlConn.Open()
Insert = "SELECT INTO Defect ("
Insert &= "DefectID, ScreenName, Message, Times, Fixed)"
Insert &= "VALUES ('"
Insert &= txtid.Text & "', '"
Insert &= txtScreenName.Text & "', '"
Insert &= txtMessage.Text & "', '"
Insert &= txtTime.Text & "', '"
Insert &= fixed.Text & "')"
Dim MSqlCmd As New SqlCommand(Insert, MSqlConn)
MSqlCmd.ExecuteNonQuery()
'txtMessage.Text = MSqlConn.ServerVersion
Catch ex As Exception
txtMessage.Text = ex.Message
Finally
' If Not (MSqlConn Is Nothing) Then
MSqlConn.Close()
' txtMessage.Text = "Not connected"
' End If
End Try
Response.Redirect("MainPage.aspx")
End Sub
Private Sub Button2_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button2.Click
Response.Redirect("MainPage.aspx")
End Sub
End Class
View 1 Replies
View Related
Dec 23, 2003
I have an asp.net project that displays a timesheet based on a fortnightly system.
It has a Y and X axis, i.e. Sun-Mon, Sun-Mon across the top and Categories of hour types accross the Y axis, i.e. Holidays, Overtime, Maintenance.
I was using a datagrid to gather the rows, but I need to have the whole grid in edit mode which is something that requires more coding. So I have swapped to a datalist.
It will take me 240 fields to store all the data in their corresponding fields. So I basically need to know if there is an easier way to store two dimensional array type data into an SQL table using a minimum of fields.
View 1 Replies
View Related
Jun 9, 2006
SQLNewbie writes "I have a table 'ImportedListings' that is populated with data external to the database. This table is only used to hold the data until I can move it to the permanent table 'Listings' at which point 'ImportedListings' gets truncated to nothing. Both tables contain almost identical data and structure. There is a listing ID column available for joins.
Basically I need to compare each row in ImportedListing and if it already exists in Listing, UPDATE Listing with the new info. If the row in ImportedListing doesn't exist in Listing, I need to INSERT it.
Physically deleting rows from the listings table is not an option. Do you have any ideas on how I can do this? I initially tried using a temp table to hold the matching listing id's but I could not get figure out the update statement with this scenario.
Thanks for any help! I have been trying to hammer this out all night(I just started programming tsql)"
View 1 Replies
View Related
Mar 26, 2008
i am creating a database driven website and i am using a sql database. I have a database called company with fields in it to do with a company, I have created a company.cs file which sets the variables, properties and methods and so on. These work fine. I have also stored procedures and they are right as far as i know. i also have coding behind the buttons of my page when i try and update or insert to the database. I am having the problem of when i enter data into the text boxes and click update, nothing gets inserted or updated and whats worst of all no error message appears. The table is used for storing profile data about a user, when a user logs on they enter their profile data, if i manually enter this data and input the username of a user into the username field within the database then the data appears fine in the textboxes of the update info page but the data will not insert or update. i have checked all the little things and i am stressing out cos i am running out of time and cannot find the problem...........please could someone help me!!!!! thanks
Heres my coding to some of my pages to help you....
the code behind the button
using System;
using System.Data;
using System.Configuration;
using System.Web;
using System.Web.Security;
using System.Web.UI;
using System.Web.UI.WebControls;
using System.Web.UI.WebControls.WebParts;
using System.Web.UI.HtmlControls;
using FYPtestsite.Classes;
public partial class Employer_employerprofile : System.Web.UI.Page
{protected void Page_Load(object sender, EventArgs e)
{if (!Roles.IsUserInRole(ConfigurationManager.AppSettings["employerrole"]))
{Response.Redirect("~/Error1.aspx");
}Company objCompany = Company.GetCompany(Profile.UserName);if (objCompany != null)
{
txtCompanyname.Text = objCompany.Companyname;
txtAddress1.Text = objCompany.Address1;
txtAddress2.Text = objCompany.Address2;
txtPostcode.Text = objCompany.Postcode;
txtCity.Text = objCompany.City;
txtPhone.Text = objCompany.Phone;
txtFax.Text = objCompany.Fax;
txtEmail.Text = objCompany.Email;
txtURL.Text = objCompany.URL;
txtProfile.Text = objCompany.Profile;
}
}protected void Button2_Click(object sender, EventArgs e)
{Response.Redirect("~/homepage.aspx");
}protected void Button3_Click(object sender, EventArgs e)
{Company objCompany = new Company();
objCompany.UserName = Profile.UserName;
objCompany.Companyname = txtCompanyname.Text;
objCompany.Address1 = txtAddress1.Text;
objCompany.Address2 = txtAddress2.Text;
objCompany.Postcode = txtPostcode.Text;
objCompany.City = txtCity.Text;
objCompany.Phone = txtPhone.Text;
objCompany.Fax = txtFax.Text;
objCompany.Email = txtEmail.Text;
objCompany.URL = txtURL.Text;
objCompany.Profile = txtProfile.Text;
if (Profile.Employer.CompanyID != -1)
{objCompany.CompanyID = (int)Profile.Employer.CompanyID;Company.Update(objCompany);
}
else
{int i = Company.Insert(objCompany);
Profile.Employer.CompanyID = i;
}labelstatus.Text = "Your profile has been successfully updated";
}
}
Any help would be greatly greatly appreciated!!!!!!
View 10 Replies
View Related
Apr 22, 2008
i wanted to ask how to insert values from a single web form into two sql tables, i have been looking and the visual web developer i use doesnt seam to allow me to even atempt it i've tried selecting all the values from two different tables and then adding those two tables to an insert function but it doesnt work likewise the update functioni have values in a table currently a reference number and i want to use this reference number to update the address values in this table so update this field.table1 and thisfield.table2 when ref number = @ refnumber the reference number is present in both tables and is linked PK to FK <asp:SqlDataSource ID="SqlDataSource2" runat="server"
ConnectionString="<%$ ConnectionStrings:Back End DataConnectionString %>"
SelectCommand="SELECT StartDetails.StartDetailsID, StartDetails.ContractIDNo, StartDetails.ContractName, StartDetails.NINO, StartDetails.AnticipatedStartDate, StartDetails.StartDateTime, StartDetails.StartDateLetterSent, StartDetails.StartDate, StartDetails.AnticipatedEndDate, StartDetails.ActualEndDate, StartDetails.ReasonForLeaving, StartDetails.Provider, StartDetails.AdviserReferrer, StartDetails.ProvisionCat, StartDetails.Provision, ClientDetails.NINO AS Expr1, ClientDetails.CentreNo, ClientDetails.FirstName, ClientDetails.SecondName, ClientDetails.AddressLine1, ClientDetails.AddressLine2, ClientDetails.PostCode, ClientDetails.ContactTelephoneNumber, ClientDetails.MobileNo, ClientDetails.Email, ClientDetails.DateOfBirth, ClientDetails.Gender, ClientDetails.PWD, ClientDetails.Ethnicity, ClientDetails.ClientGroup, ClientDetails.RepeatStartDate, ClientDetails.CaseworkerName, ClientDetails.ClientStatus, ClientDetails.PlacementDates, ClientDetails.JobsearchDay, ClientDetails.AchievedILP, ClientDetails.JobDate, ClientDetails.JobDate2, ClientDetails.JobDate3, ClientDetails.EligibleForRolledUpWeeks, ClientDetails.NoOfWeeksClaimed, ClientDetails.MarketingWhere, ClientDetails.Notes, ClientDetails.JobCentre, ClientDetails.JobCentreRep FROM StartDetails INNER JOIN ClientDetails ON StartDetails.NINO = ClientDetails.NINO WHERE (StartDetails.StartDetailsID = @StartDetailsID) AND (StartDetails.NINO = @NINO)"
InsertCommand="INSERT INTO [StartDetails] ([NINO], [StartDate], [AnticipatedEndDate]) VALUES (@NINO, @StartDate, @AnticipatedEndDate)"
UpdateCommand="UPDATE [StartDetails] SET [StartDate] = @StartDate, [AnticipatedEndDate] = @AnticipatedEndDate WHERE [StartDetailsID] = @StartDetailsID,[NINO] = @NINO">
<SelectParameters>
<asp:QueryStringParameter Direction="InputOutput" Name="StartDetailsID"
QueryStringField="StartDetailsID" />
<asp:QueryStringParameter Direction="InputOutput" Name="NINO"
QueryStringField="NINO" />
</SelectParameters>
<UpdateParameters>
<asp:Parameter Name="StartDate" Type="DateTime" />
<asp:Parameter Name="AnticipatedEndDate" Type="DateTime" />
<asp:Parameter Name="StartDetailsID" Type="Int32" />
<asp:Parameter Name="NINO" Type="String" />
</UpdateParameters>
<InsertParameters>
<asp:Parameter Name="NINO" />
<asp:Parameter Name="StartDate" Type="DateTime" />
<asp:Parameter Name="AnticipatedEndDate" Type="DateTime" />
</InsertParameters>
</asp:SqlDataSource> your coinsideration is appreciatedChris
View 4 Replies
View Related
Jul 20, 2005
Hi, I'm trying to store large strings to a database, so am using thetext field type (LongText). I have used this before when storing thehtml of a webpage, and was able to store more than 255 characters byusing just a normal update sql statement. Now I'm trying to store thebody of research papers, and must be doing something different, as Ican only store 255 characters.Can someone explain why SQL Server doesn't like what I am doing -should I be using the WriteText / UpdateText function? If so, pleaseexplain by example how I would do that, and why doing that works.Thanks so much,Iain
View 5 Replies
View Related
Aug 17, 2006
I have created a single Data Flow Task that reads a set of records from a source table and then makes determinations whether to insert, update or delete from a destination table. The data is basically being copied from one database to another with a small amount of data manipulation and lookups. The problem seems to be that when running the task, even a small amount of records read from the source table seem to take a long time for the task to finish. I feed the records into a Script Component (the brains) that sorts the records to three separate outputs. I use OLE DB Commands to perform the DELETE and UPDATE and an OLE DB Destination for INSERT. I thought that by using three separate database connections would help, but it just appears to be locked while trying to perform these commands against the same table.
Is there a way to control or route these three record sets in such a way as to perform them sequentially?
I know it's a bit of a simple question for some of you, but I'm just learning SSIS (but I like it!).
Thanks!
Jeff Tolman
E&M Electric
View 3 Replies
View Related
Sep 18, 2007
I want to insert arabic characters in my sql server db. I need create table and insert syntax to insert arabic characters.
Can anyone help me???
View 3 Replies
View Related
Dec 2, 2015
This question is extension from the topic Updating table Rows with overlapping dates: [URL] .....
I am actually having a table as following:
Table Name: PromotionList
Note that the NULL in endDate means no end date or infinite end date.
ID PromotionID StartDate EndDate Flag
1 1 2015-04-05 2015-05-28 0
2 1 2015-04-23 NULL 0
3 2 2015-03-03 2015-05-04 0
4 1 2015-04-23 2015-05-29 0
5 1 2015-01-01 2015-02-02 0
And I would like to produce the following outcome to the same table (using update statement): As what you all observe, it merge all overlapping dates based on same promotion ID by taking the minimum start date and maximum end date. Only the first row of overlapping date is updated to the desired value and the flag value change to 1. For other overlapping value, it will be set to NULL and the flag becomes 2.
Flag = 1, success merge row. Flag = 2, fail row
ID PromotionID StartDate EndDate Flag
1 1 2015-04-05 NULL 1
2 1 NULL NULL 2
3 2 2015-03-03 2015-05-04 1
4 1 NULL NULL 2
5 1 2015-01-01 2015-02-02 1
The second part that I would like to acheive is based on the first table as well. However, this time I would like to merge the date which results in the minimum start date and End Date of the last overlapping rows. Since the End date of the last overlapping rows of promotion ID 1 is row with ID 4 with End Date 2015-05-29, the table will result as follow after update.
ID PromotionID StartDate EndDate Flag
1 1 2015-04-05 2015-05-29 1
2 1 NULL NULL 2
3 2 2015-03-03 2015-05-04 1
4 1 NULL NULL 2
5 1 2015-01-01 2015-02-02 1
Note that above is just sample Data. Actual data might contain thousands of records and hopefully it can be done in single update statement.
Extending from the above question, now two extra columns has been added to the table, which is ShouldDelete and PromotionCategoryID respectively.
Original table:
ID PromotionID StartDate EndDate Flag ShouldDelete PromotionCategoryID
1 1 2015-04-05 2015-05-28 0 Y 1
2 1 2015-04-23 2015-05-29 0 NULL NULL
3 2 2015-03-03 2015-05-04 0 N NULL
4 1 2015-04-23 NULL 0 Y 1
5 1 2015-01-01 2015-02-02 0 NULL NULL
Should Delete can take Y, N and NULL
PromotionCategoryID can take any integer and NULL
Now it should be partition according with promotionid, shoulddelete and promotioncategoryID (these 3 should be same).
By taking the min date and max date of the same group, the following table should be achieve after the table is updated.
Final outcome:
ID PromotionID StartDate EndDate Flag ShouldDelete PromotionCategoryID
1 1 2015-04-05 NULL 1 Y 1
2 1 2015-04-23 2015-05-29 1 NULL NULL
3 2 2015-03-03 2015-05-04 1 N NULL
4 1 NULL NULL 2 Y 1
5 1 2015-01-01 2015-02-02 1 NULL NULL
View 2 Replies
View Related
Sep 19, 2006
I have created a sample Database for the school project,
After executing the query below, the Date column is supposed to have the dates I have entered before,
However the dates shown are 1900.
Any idea why is this happening?
I appreciate your help.
Thank you.
Query:
Drop table AccountReceivable
GO
--BEGIN TRANSACTION
Create table AccountReceivable
(
AccountRecID int identity (1,1) not null,
PatientID int not null,
PresentCharges int default 0 not null,
PaymentMade money default 0 not null,
PreviousBalance money default 0 not null,
BalanceDue money default 0 not null,
LastPaymentDate datetime not null,
PresentDate datetime default GetDate() not null
)
GO
ALTER TABLE AccountReceivable ADD CONSTRAINT
PK_AccountRecID Primary Key (AccountRecID)
GO
ALTER TABLE AccountReceivable ADD CONSTRAINT
FK_PatientID_PatientID FOREIGN KEY (PatientID) REFERENCES PATIENT (PatientID)
GO
--COMMIT
--query to find delinquent accounts
--DATEDIFF (d, LastPaymentDate, PresentDate)
--Populate the Accounts Table
DELETE AccountReceivable
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate )
VALUES (913235,451.34,50,0,401.34,4/7/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (918035,109,109,0,0,3/6/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (914235,279,89,0,190,5/9/2005,5/9/2005)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (914235,0,90,190,100,5/9/2005,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (912224,67.90,67.90,0,0,2/2/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (900814,678.32,78.32,0,600,4/6/2006,4/6/2006)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (900814,0,500,600,100,4/6/2006,4/16/2006)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (900814,0,100,100,0,4/16/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (913010,203,0,100,303,2/6/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (913010,0,80,303,223,8/3/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (913230,1030.89,1030.89,0,0,4/16/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (918035,78,60,0,18,7/1/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (941235,902,502,0,400,8/15/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (941235,0,200,400,200,8/15/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (952235,134,24,0,110,4/18/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (952235,0,20,110,90,4/18/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (921635,257.87,57.87,0,200,5/27/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (921635,0,20,200,180,6/27/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (915235,1204,200,0,1004,3/15/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (915235,0,100,1004,904,4/27/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (900035,578,178,0,400,7/10/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (900035,0,100,400,300,7/19/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (913241,157,0,0,157,5/12/2006,DEFAULT)
GO
INSERT AccountReceivable (PatientID,PresentCharges,PaymentMade,PreviousBalance,BalanceDue,LastPaymentDate,PresentDate)
VALUES (913241,0,57,157,100,5/16/2006,DEFAULT)
GO
--sample query
select PatientID,PresentCharges,LastPAymentDate,PresentDate from AccountReceivable
GO
--result
PatientID PresentCharges LastPaymentDate PresentDate
----------- -------------- ----------------------- -----------------------
913235 451 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297
918035 109 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297
914235 279 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000
914235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297
912224 67 1900-01-01 00:00:00.000 2006-09-15 12:54:55.297
900814 678 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000
900814 0 1900-01-01 00:00:00.000 1900-01-01 00:00:00.000
900814 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313
913010 203 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313
913010 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313
913230 1030 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313
918035 78 1900-01-01 00:00:00.000 2006-09-15 12:54:55.313
941235 902 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327
941235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327
952235 134 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327
952235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327
921635 257 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327
921635 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327
915235 1204 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327
915235 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.327
900035 578 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343
900035 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343
913241 157 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343
913241 0 1900-01-01 00:00:00.000 2006-09-15 12:54:55.343
(24 row(s) affected)
View 6 Replies
View Related
May 11, 2007
MSSQL noob here. Sorry guys, but I really need some help.
I have a MSSQL (2000) database and a Products Table. Now, there was three columns used for pricing that I was not able to populate on the initial usage. So I exported the table as an Excel document, and got rid of everything except the UID (unique identifier column) the Name, and the Three columns, so that the customer could take their time in filling out the information.
Ok, so These are what are common to both the MSSQL database and the Excel sheet
column uid = unique id integer
column Name = text
column eBay = number
column PriceGrabber = number
column Amazon = number
So, any idea how I can update my Products Table with this?
Please HELP!
View 18 Replies
View Related
Mar 10, 2006
I am using the following query to calculate date differences:select ..........DATEDIFF(d, recruitment_advertising.advertising_date, career_details.RTS_Email AS Datetime) AS Ad_to_RTS_days FROM .....I have stored all my dates as NVARCHAR because of the issues with localization.If the value is an empty String my output is eg: -38700. which is way off and incorrect. Some of the values in my table are NULL and they produce the correct result.Is there a T-SQL statement to replace empy Strings with the NULL value in my tables.I'd like to use it as a trigger when inserting or updating to convert empty strings to NULLbefore the values are inserted.Thanks guys.
View 1 Replies
View Related
Mar 8, 2007
We've installed the Oracle provider for OLE DB on SQL Server 2005, which has the default collation (SQL_Latin1_General_CP1_CI_AS), and we've created a linked server for the Oracle 9.2.0.5 database, which has AL32UTF8 as the database character set. We can successfully insert strings into VARCHAR2 columns on Oracle from SQL Server via EXEC SP_EXECUTESQL('INSERT OPENQUERY(...) VALUES(...)') -- as long as the strings (whether selected from NVARCHAR columns on SQL Server or specified as literals with the N prefix during testing) only contain Windows-1252 characters.
If the SQL statement contains a character above U+00FF, the string on the Oracle side is incorrectly/doubly encoded; there are nearly (but not exactly) 4 bytes per character instead of the 1 or 2 you'd expect from ASCII/Latin-1 characters encoded as UTF-8.
We've tried reconfiguring the linked server: collation compatible = false, use remote collation = true, and collation name = Latin1_General_BIN2. But that had no effect.
What is the correct way to do this?
Thanks!
View 1 Replies
View Related
Mar 18, 2008
Hi Gnite everyone, i once again need help with a T-SQL syntax for Auto Correction for insert and update when client enters the wrong format, i;e, creating a SSN Data type and a User Defined Procedure that auto corrects input format i;e,
user inserts into table authors of the pubs DB 525-477845 column au_id that executes auto correction so user doesn't have to put in dashes for SSN format. Please help me with this syntax .
View 1 Replies
View Related
