Validating The Insertion Of A Record Into Database Against Existing Records.

Jan 31, 2008

Hello. I currently have a website that allows appointments to be booked up for doctors. i currently have an insert record page built using ASP components. I would like to introduce some validation so that if a user tries to book an appointment date and time that is already occupied, an error message is returned saying that appointment slot is already booked. I'm quite confused how to do this. My current code looks as follows.

<%@ Page Language="C#" MasterPageFile="~/AdministratorMasterPage.master" AutoEventWireup="true" CodeFile="AddAppointment.aspx.cs" Inherits="AddAppointment" Title="Add Appointments - Bournemouth and Poole NHS Primary Care Trust" %>

<asp:Content ID="Content1" ContentPlaceHolderID="ContentPlaceHolder1" Runat="Server">

<table style="position: relative">

<tr>

<td style="width: 45px">

<br />

</td>

<td style="width: 136px">

</td>

<td style="width: 47px">

</td>

<td style="width: 100px">

<asp:SqlDataSource ID="AppointmentsSqlDataSource" runat="server" ConnectionString="<%$ ConnectionStrings:ProgConnectionString %>"

DeleteCommand="DELETE FROM [Appointment] WHERE [PatientNo] = @PatientNo" InsertCommand="INSERT INTO [Appointment] ([PatientNo], [PatientSurname], [PatientForename], [ConsultantName], [HospitalName], [Time], [Date], [AppointmentStatus]) VALUES (@PatientNo, @PatientSurname, @PatientForename, @ConsultantName, @HospitalName, @Time, @Date, @AppointmentStatus)"

SelectCommand="SELECT * FROM [Appointment]" UpdateCommand="UPDATE [Appointment] SET [ConsultantName] = @ConsultantName, [HospitalName] = @HospitalName, [Time] = @Time, [Date] = @Date, [AppointmentStatus] = @AppointmentStatus WHERE [PatientNo] = @PatientNo">

<DeleteParameters>

<asp:Parameter Name="PatientNo" Type="Int32" />

</DeleteParameters>

<UpdateParameters>

<asp:Parameter Name="ConsultantName" Type="String" />

<asp:Parameter Name="HospitalName" Type="String" />

<asp:Parameter Name="Time" Type="DateTime" />

<asp:Parameter Name="Date" Type="DateTime" />

<asp:Parameter Name="PatientNo" Type="Int32" />

<asp:Parameter Name="AppointmentStatus" Type="String" />

</UpdateParameters>

<InsertParameters>

<asp:ControlParameter ControlID="Textbox1" Name="PatientNo" PropertyName="Text" Type="String" />

<asp:ControlParameter ControlID="Textbox3" Name="PatientSurname" PropertyName="Text" Type="String" />

<asp:ControlParameter ControlID="Textbox4" Name="PatientForename" PropertyName="Text" Type="String" />

<asp:ControlParameter ControlID="DropDownList1" Name="ConsultantName" PropertyName="Text" Type="String" />

<asp:ControlParameter ControlID="DropDownList2" Name="HospitalName" PropertyName="Text" Type="String" />

<asp:ControlParameter ControlID="DropDownList3" Name="Time" PropertyName="Text" Type="DateTime" />

<asp:ControlParameter ControlID="Textbox2" Name="Date" PropertyName="Text" Type="DateTime" />

<asp:ControlParameter ControlID="DropDownList4" Name="AppointmentStatus" PropertyName="Text" Type="String" />

</InsertParameters>

</asp:SqlDataSource>

</td>

<td style="width: 100px">

</td>

<td style="width: 100px">

</td>

</tr>

<tr>

<td style="width: 45px">

</td>

<td align="left" style="width: 136px">

<asp:Label ID="Label1" runat="server" Style="position: relative" Text="Patient No"></asp:Label></td>

<td style="width: 47px">

</td>

<td style="width: 100px">

<asp:TextBox ID="TextBox1" runat="server" Style="position: relative"></asp:TextBox></td>

<td style="width: 100px">

<asp:RequiredFieldValidator ID="RequiredFieldValidator1" runat="server" ControlToValidate="TextBox1"

Display="Dynamic" ErrorMessage="RequiredFieldValidator" Style="position: relative"

Width="148px">Enter a Patient No</asp:RequiredFieldValidator></td>

<td style="width: 100px">

</td>

</tr>

<tr>

<td style="width: 45px">

</td>

<td align="left" style="width: 136px">

</td>

<td style="width: 47px">

</td>

<td style="width: 100px">

<br />

</td>

<td style="width: 100px">

</td>

<td style="width: 100px">

</td>

</tr>

<tr>

<td style="width: 45px">

</td>

<td align="left" style="width: 136px">

<asp:Label ID="Label6" runat="server" Style="position: relative" Text="Patient Surname"

Width="116px"></asp:Label></td>

<td style="width: 47px">

</td>

<td style="width: 100px">

<asp:TextBox ID="TextBox3" runat="server" Style="position: relative"></asp:TextBox></td>

<td style="width: 100px">

<asp:RequiredFieldValidator ID="RequiredFieldValidator6" runat="server" ControlToValidate="TextBox3"

ErrorMessage="Enter a Surname" Style="position: relative" Width="140px"></asp:RequiredFieldValidator></td>

<td style="width: 100px">

</td>

</tr>

<tr>

<td style="width: 45px">

</td>

<td align="left" style="width: 136px">

<br />

</td>

<td style="width: 47px">

</td>

<td style="width: 100px">

</td>

<td style="width: 100px">

</td>

<td style="width: 100px">

</td>

</tr>

<tr>

<td style="width: 45px">

</td>

<td align="left" style="width: 136px">

<asp:Label ID="Label7" runat="server" Style="position: relative" Text="Patient Forename"

Width="128px"></asp:Label></td>

<td style="width: 47px">

</td>

<td style="width: 100px">

<asp:TextBox ID="TextBox4" runat="server" Style="position: relative"></asp:TextBox></td>

<td style="width: 100px">

<asp:RequiredFieldValidator ID="RequiredFieldValidator7" runat="server" ControlToValidate="TextBox4"

ErrorMessage="Enter a Forename" Style="position: relative"></asp:RequiredFieldValidator></td>

<td style="width: 100px">

</td>

</tr>

<tr>

<td style="width: 45px">

</td>

<td align="left" style="width: 136px">

<br />

</td>

<td style="width: 47px">

</td>

<td style="width: 100px">

</td>

<td style="width: 100px">

</td>

<td style="width: 100px">

</td>

</tr>

<tr>

<td style="width: 45px">

</td>

<td style="width: 136px">

<asp:Label ID="Label2" runat="server" Style="position: relative" Text="Consultant Name"></asp:Label></td>

<td style="width: 47px">

</td>

<td style="width: 100px">

<asp:DropDownList ID="DropDownList1" runat="server" DataSourceID="SqlDataSource1"

DataTextField="UserName" DataValueField="UserName" Style="position: relative">

<asp:ListItem Selected="True">Select...</asp:ListItem>

</asp:DropDownList>

<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:ASPNETDBConnectionString %>"

SelectCommand="SELECT aspnet_Users.UserName&#13;&#10;FROM aspnet_UsersInRoles INNER JOIN&#13;&#10; aspnet_Users ON aspnet_UsersInRoles.UserId = aspnet_Users.UserId INNER JOIN&#13;&#10; aspnet_Roles ON aspnet_UsersInRoles.RoleId = aspnet_Roles.RoleId&#13;&#10;WHERE (aspnet_Roles.RoleName = 'Consultant')"></asp:SqlDataSource>

</td>

<td style="width: 100px">

<asp:RequiredFieldValidator ID="RequiredFieldValidator2" runat="server" ControlToValidate="DropDownList1"

ErrorMessage="RequiredFieldValidator" Style="position: relative" Width="152px">Select a Consultant</asp:RequiredFieldValidator></td>

<td style="width: 100px">

</td>

</tr>

<tr>

<td style="width: 45px">

</td>

<td style="width: 136px">

</td>

<td style="width: 47px">

</td>

<td style="width: 100px">

<br />

</td>

<td style="width: 100px">

</td>

<td style="width: 100px">

</td>

</tr>

<tr>

<td style="width: 45px">

</td>

<td style="width: 136px">

<asp:Label ID="Label3" runat="server" Style="position: relative" Text="Hospital Name"></asp:Label></td>

<td style="width: 47px">

</td>

<td style="width: 100px">

<asp:DropDownList ID="DropDownList2" runat="server" DataSourceID="SqlDataSource2"

DataTextField="HospitalName" DataValueField="HospitalName" Style="position: relative">

</asp:DropDownList>

<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ProgConnectionString %>"

SelectCommand="SELECT [HospitalName] FROM [Hospital]"></asp:SqlDataSource>

</td>

<td style="width: 100px">

<asp:RequiredFieldValidator ID="RequiredFieldValidator3" runat="server" ControlToValidate="DropDownList2"

ErrorMessage="RequiredFieldValidator" Style="position: relative" Width="136px">Select a Hospital</asp:RequiredFieldValidator></td>

<td style="width: 100px">

</td>

</tr>

<tr>

<td style="width: 45px">

</td>

<td style="width: 136px">

</td>

<td style="width: 47px">

</td>

<td style="width: 100px">

<br />

</td>

<td style="width: 100px">

</td>

<td style="width: 100px">

</td>

</tr>

<tr>

<td style="width: 45px">

</td>

<td style="width: 136px">

<asp:Label ID="Label4" runat="server" Style="position: relative" Text="Appointment Date"></asp:Label></td>

<td style="width: 47px">

</td>

<td style="width: 100px">

<asp:Calendar ID="Calendar1" runat="server" Font-Size="Smaller" Style="position: relative" OnSelectionChanged="Calendar1_SelectionChanged" OnDayRender="Calendar1_DayRender">

</asp:Calendar>

</td>

<td style="width: 100px">

</td>

<td style="width: 100px">

</td>

</tr>

<tr>

<td style="width: 45px">

</td>

<td style="width: 136px">

</td>

<td style="width: 47px">

</td>

<td style="width: 100px">

<asp:TextBox ID="TextBox2" runat="server" Style="position: relative; left: 0px; top: 8px;" Width="256px"></asp:TextBox><br />

</td>

<td style="width: 100px">

<asp:RequiredFieldValidator ID="RequiredFieldValidator4" runat="server" ControlToValidate="TextBox2"

Display="Dynamic" ErrorMessage="Enter a Date" Style="position: relative"></asp:RequiredFieldValidator></td>

<td style="width: 100px">

</td>

</tr>

<tr>

<td style="width: 45px">

</td>

<td style="width: 136px">

</td>

<td style="width: 47px">

</td>

<td style="width: 100px">

</td>

<td style="width: 100px">

</td>

<td style="width: 100px">

</td>

</tr>

<tr>

<td style="width: 45px; height: 26px">

</td>

<td style="width: 136px; height: 26px">

<asp:Label ID="Label5" runat="server" Style="position: relative" Text="Appointment Time" Width="132px"></asp:Label></td>

<td style="width: 47px; height: 26px">

</td>

<td style="width: 100px; height: 26px">

<asp:DropDownList ID="DropDownList3" runat="server" Style="position: relative; left: 0px; top: 0px;">

<asp:ListItem>Select...</asp:ListItem>

<asp:ListItem Value="09:00">09:00</asp:ListItem>

<asp:ListItem>09:30</asp:ListItem>

<asp:ListItem>10:00</asp:ListItem>

<asp:ListItem>10:30</asp:ListItem>

<asp:ListItem>11:00</asp:ListItem>

<asp:ListItem>11:30</asp:ListItem>

</asp:DropDownList></td>

<td style="width: 100px; height: 26px">

<asp:RequiredFieldValidator ID="RequiredFieldValidator5" runat="server" ControlToValidate="DropDownList3"

Display="Dynamic" ErrorMessage="Select a Time" Style="position: relative"></asp:RequiredFieldValidator></td>

<td style="width: 100px; height: 26px">

</td>

</tr>

<tr>

<td style="width: 45px">

</td>

<td style="width: 136px">

<br />

</td>

<td style="width: 47px">

</td>

<td style="width: 100px">

</td>

<td style="width: 100px">

</td>

<td style="width: 100px">

</td>

</tr>

<tr>

<td style="width: 45px">

</td>

<td style="width: 136px">

<asp:Label ID="Label8" runat="server" Style="position: relative" Text="Appointment Status"

Width="136px"></asp:Label></td>

<td style="width: 47px">

</td>

<td style="width: 100px">

<asp:DropDownList ID="DropDownList4" runat="server" Style="position: relative">

<asp:ListItem>Select...</asp:ListItem>

<asp:ListItem>Booked</asp:ListItem>

<asp:ListItem>Modified</asp:ListItem>

<asp:ListItem>Patient Notified</asp:ListItem>

</asp:DropDownList></td>

<td style="width: 100px">

<asp:RequiredFieldValidator ID="RequiredFieldValidator8" runat="server" ErrorMessage="Select a Status"

Style="position: relative" Width="120px" ControlToValidate="DropDownList4"></asp:RequiredFieldValidator></td>

<td style="width: 100px">

</td>

</tr>

<tr>

<td style="width: 45px">

</td>

<td style="width: 136px">

</td>

<td style="width: 47px">

</td>

<td style="width: 100px">

</td>

<td style="width: 100px">

</td>

<td style="width: 100px">

</td>

</tr>

<tr>

<td style="width: 45px">

</td>

<td style="width: 136px">

</td>

<td style="width: 47px">

</td>

<td align="center" style="width: 100px">

<asp:Button ID="Button1" runat="server" Style="position: relative" Text="Submit" OnClick="Button1_Click1" /></td>

<td style="width: 100px">

</td>

<td style="width: 100px">

</td>

</tr></table>

</asp:Content>

Any help would be very much appreciated.

Thanks,

James.

View 2 Replies


ADVERTISEMENT

Multiple Record Insertion For Each Record From Source

Jan 10, 2008



Hi,

How can we insert multiple records in a OLEDB destination table for each entry from the source table.
To be more clear, for every record from source we need to insert some 'n' number of records into the destination table. this 'n' changes depending on the record.
how is this achieved.

thanks.

View 4 Replies View Related

Record Insertion

Jul 20, 2005

Hi everyone,I tried to upload my database to my web host. Nevertheless as they donot give me enough permission to perform some DTS thus I have togenerate script to do it myself. The problem is MSSQL scriptgeneration operation can only generate the definitions of the objects,no data insertion sql is created.So is there any method to create insertion script for all the tablesin my database (they have automated increment key as well)Hope for your response!Thank you

View 2 Replies View Related

Opinion On Preventing Duplicate Record Insertion

Oct 26, 2007

Hi, i need an opinion on this...to prevent the duplicate record in db,i am using unique constraints for a column or combination of column as the case may be.By reading this  article http://aspalliance.com/687_Preventing_Duplicate_Record_Insertion_on_Page_Refresh.5 , i get the feeling that its not such a good idea..i am wondering,what does it imply?Does it mean that unique constraints are not reliable enough?Does it mean,it may break and let the duplicate record inserted,even though its not suppose to?I am using SQL server 2005
I have read Dino's article on dup recs and i have still not understood it completely.. i am looking for some not so complex ,full proof method,to prevent duplicate record insertion by clicking refresh or multiple (careless)clicking on submit....thanks ..

View 2 Replies View Related

Problem In Update Existing Records Using OLEDB Commmand(ORAcle DataBase) In SSIS Package

Sep 4, 2006

Hi when i am using OLDB Command for Update Existing Records the Follwing ERror Code I am getting . Any one pls help me on this one.



1)[DTS.Pipeline] Error: The ProcessInput method on component "OLE DB Command 1" (9282)
failed with error code 0xC0202009. The identified component returned an error from the ProcessInput method.
The error is specific to the component, but the error is fatal and will cause the Data Flow task to stop running.


2)

[OLE DB Command 1 [9282]] Error: An OLE DB error has occurred. Error code: 0x80040E10.



3)

[OLE DB Command 1 [9282]] Error: An OLE DB error has occurred. Error code: 0x80040E10.

View 1 Replies View Related

Transact SQL :: Insertion Procedure To Insert A Record In More Than One Table

Nov 17, 2015

Consider a 4 tables where 1 of them is considered to be as the parent class and the other 3 are sub-classes and they are disjoint so for every recored i insert in the parent class i want to also insert in one of the subclass according to a condition which checks a certain attribute in the recored that is also entered in the parent class .. how could this be done .

View 8 Replies View Related

Sql Scripts For Insertion Of Records

Jan 24, 2006

i am executing this scripts in sql server2000

SELECT
'INSERT into temp values(data1, data2, data3)' +
'SELECT ' +
CONVERT(VARCHAR(20), temp2.data1) + ', ' + CONVERT(VARCHAR(20), temp2.data2) + ', ' + '''' + CONVERT(VARCHAR(20), temp2.data3) + ''''
FROM temp, temp2
order by temp2.data1


now my table temp and temp2 both has folowing fields and data type

data1 int
data2 int
data3 varchar(20)

befor execution i have 0 records in temp and 10 records in temp2.
this scripts supposed to add all 10 records from temp2 to temp but it does nothing. evevn it is not giving error too.

View 2 Replies View Related

Auto Insertion Of Records

Oct 4, 2007

hi, i'm quite new to sql and would like to ask if there is any way to automatically insert records from a table, for example dailyTransaction, into another table, for example transactionHistory, daily maybe around midnight or something. would also have to delete the records in dailyTransaction after it has been copied over to transactionHistory table i've done a bit of searching around and i think i have to use job to do this but i have no idea how to implement it. looked through the libary in msdn but i'm still not too sure. would really appreciate any help. thanks

View 5 Replies View Related

Check For Duplicate Records Before Insertion

Jul 19, 2006

Hi,
Does anyone have any sugestions as to the best way to achieve the following?
I want to display a list of products in a GridView. Against each product I want to place a button which will add the product to a "wish list" table.
I need to be able to check that the product has not already been inserted into the wish list.
Either suggested methods or links to tutorials will be appreciated.
Thanks in advance.
 

View 2 Replies View Related

Conditional Records Insertion In SQL 7.0 Tables

Aug 5, 2000

Dear All,

I am having a problem in adding new records in SQL 7.0 Database.
I have two databases - one with table name DB1..WTEST and the other with
table name DB2..TEST. I want to update records in DB2..TEST table when
records are already in TEST table (by comparing primary key - with ignore
duplicate option) & secondly, INSERT records in DB2..TEST table from
DB1..WTEST table if the records do not exist in DB2..TEST table. The first
option which is Updation, works fine but with second option I have problem.
It works and records are INSERTED but I don't know the statistics of
inserted records. Instead it gives a warning message - Duplicate records
were Ignored -

Can somebody help me out or suggest some better solution for conditional
record insertion. I used CURSOR option but it is only for Updation &
Deletion.

Here is my Transact-SQL Script.

--------------------------------------------------------------

IF EXISTS (SELECT * FROM DB1..WTEST
INNER JOIN DB2..TEST
ON DB1..WTEST.Name1 = DB2..TEST.Name1)
BEGIN
UPDATE DB1..TEST
SET add1 = DB1..WTEST.Add1
FROM DB1..WTEST
Print 'Updated'
END

WHILE NOT EXISTS(SELECT * FROM DB1..WTEST
INNER JOIN DB2..TEST
ON DB1..WTEST.Name1 = DB2..TEST.Name1)
BEGIN
INSERT DB2..TEST
SELECT DB1..WTEST.Name1,
DB1..WTEST.Add1
FROM DB1..WTEST
CONTINUE
END

------------------ Alternate Script ---------------------
IF EXISTS (SELECT * FROM DB1..WTEST, DB2..TEST
WHERE DB1..WTEST.Name1 = DB2..TEST.Name1)
BEGIN
UPDATE DB2..TEST
SET add1 = DB1..WTEST.Add1
FROM DB1..WTEST
WHERE DB1..WTEST.Name1 = DB2..TEST.Name1
END

INSERT DB2..TEST
SELECT DB1..WTEST.Name1,
DB1..WTEST.Add1
FROM DB1..WTEST
WHERE NOT EXISTS(SELECT * FROM DB2..TEST
WHERE DB1..WTEST.Name1 =
DB2..TEST.Name1)
END


----------------------------------------------------------------------------
------------
Ibrar Ahmed
System Projects Controller
Olayan Saudi Holding Company
Ph:- +966-3-8871000 x 1122
Fax:- +966-3-8872000
E-Mail: i.ahmed@oshco.com

View 2 Replies View Related

Capture Records That Failed Insertion From XML

Jul 23, 2005

Does anyone know an effective way to capture any records that failed toinsert into a table when inserting from an xml document in SQL Svr2000?thanks.-ak

View 3 Replies View Related

Select Records By Order Of Insertion???

Jan 3, 2008



I need to create a temp table with an identity column based on the contents of a physical table (CONTACTS) that has no key or unique way to identify which record was inserted first,

I can query CONTACTS with no ORDER clause and everything is in the order it was inserted. However, when I create the temp table with a newid() as ID or IDENTITY(1,1) as ID, the duplicate rows as they existsed in the physical table are not inserted into the temp table in the same order.

Is it possible to select records from a physical table into a temp table in the same order while creating a unique field?

Here is how they exist in the physical table:

CLIENTID CHANGEDATE NEWVALUE

------------ ----------------------- ----------------------------------------------------------------

C0HTRA200005 2007-11-19 21:11:57.000 NULL

C0HTRA200005 2007-11-19 21:11:57.000 NULL

C0HTRA200005 2007-11-19 21:11:57.000 Acquisition

C0HTRA200005 2007-11-19 21:11:57.000 Acquisition

C0HTRA200005 2007-11-19 21:11:57.000 Acquisition

C0HTRA200005 2007-11-19 21:11:57.000 Acquisition

C0HTRA200005 2007-11-19 21:11:57.000 Acquisition

C0HTRA200005 2007-11-19 21:11:57.000 Acquisition

C0HTRA200005 2007-11-19 21:11:57.000 Acquisition

C0HTRA200005 2007-11-19 21:11:57.000 Acquisition

C0HTRA200005 2007-11-21 06:46:17.000 Acquisition

C0HTRA200005 2007-11-22 07:47:56.000 Acquisition

Here is how they exist after being inserted into the temp table using DISTINCT:

ID CLIENTID CHANGEDATE NEWVALUE

----------- ------------ ----------------------- ----------------------------------------------------------------

15 C0HTRA200005 2007-11-19 21:07:20.000 NULL

16 C0HTRA200005 2007-11-19 21:10:52.000 NULL

17 C0HTRA200005 2007-11-19 21:11:57.000 Acquisition

18 C0HTRA200005 2007-11-19 21:11:57.000 NULL

19 C0HTRA200005 2007-11-21 06:46:17.000 Acquisition

20 C0HTRA200005 2007-11-22 07:47:56.000 Acquisition


I've tried this many different ways using variations of ORDER BY/ no ORDER BY.
I've tried creating the temp table first with the IDENTITY field, then insert into it.
I've also tried SELECT INTO syntax while creating the IDENTITY field with the select. I've done this with newid() as well.

Any ideas would be huge.

Thanks much.

View 5 Replies View Related

Extracting The Specified Record No Of Records From Database Table

Jun 11, 2006



hi all,

I need to select the no of records on the basis of specified range of records.

In oracle i found rownum, i could not find it in sqlserver. how the data are extracted from the huge records..

I have used temporary table,map the table primary key to the next table with identity

but i dont find it good. I need to ignore the insert the data in next table or craeting new table having the rowid ...

Is there some other best way to extract the specified data

here is the type of query.

select * from customers where rownum between 1000 and 10000

this is in oracle

i am in need to do this in the sql server

waiting for the response...............................

View 5 Replies View Related

Insertion And Deletion Of Records Inside A Single Stored Procedure

Mar 25, 2008

Hi,
I have two tables A and B. In table A i have three columns called empid, empname and empsalary where empid is an identity column. Table A has some records filled in it. Table B has the same schema except the fact that the empid is not an identity column in table B. Table B does not contain any rows initially. All other aspects remain the same as that of table A. Now i am going to delete some records in table A based on the empid. When i delete the records in table A based on empid the deleted records should be inserted into table B with the same empid. I need to accomplish these two tasks in a single stored procedure. How to do it? I need the entire code for the stored procedure. Please help me. I am trying for the past 4 days.
 Thanx in Advance

View 4 Replies View Related

ADO - Connection::Execute() Returning Zero Records Affected After Successful Insertion Of Data In SQL Server 2005

Apr 11, 2008

Hello,

For the following ADO Connection::Execute() function the "recAffected" is zero, after a successful insertion of data, in SQL Server 2005. In SQL Server Express I am getting the the number of records affected value correctly.

_variant_t recAffected;
connectionObject->Execute(SQL, &recAffected, adCmdText );

The SQL string is

BEGIN
DELETE FROM MyTable WHERE Column_1 IN ('abc', 'def' )
END
BEGIN
INSERT INTO MyTable (Column_1, Column_2, Cloumn_3 )
SELECT 'abc', 'data11', 'data12'
UNION ALL
SELECT 'def', 'data21', 'data22'
END

But see this, for SQL Server 2005 "recAffected" has the correct value of 2 when I have just the insert statement.

INSERT INTO MyTable (Column_1, Column_2, Cloumn_3 )
SELECT 'abc', 'data11', 'data12'
UNION ALL
SELECT 'def', 'data21', 'data22'

For SQL Server 2005 in both cases the table got successfully inserted two rows and the HRESULT of Execute() function returns S_OK.

Does the Execute function has any problem with a statement like the first one (with delete and insert) on a SQL Server 2005 DB?

Why the "recAffected" has a value zero for the first SQL (with delete and insert) on a SQL Server 2005 DB? Do I need to pass any options to Execute() function for a SQL Server 2005 DB?

When connecting to SQL Server Express the "recAffected" has the correct values for any type of SQL statements.

Thank you for your time. Any help greatly appreciated.

Thanks,
Kannan

View 1 Replies View Related

ADO - Connection::Execute() Returning Zero Records Affected After Successful Insertion Of Data In SQL Server 2005

Apr 5, 2008

Hello,

For the following ADO Connection::Execute() function the "recAffected" is zero, after a successful insertion of data, in SQL Server 2005. In SQL Server Express I am getting the the number of records affected value correctly.

_variant_t recAffected;
connectionObject->Execute(SQL, &recAffected, adCmdText );

The SQL string is

BEGIN
DELETE FROM MyTable WHERE Column_1 IN ('abc', 'def' )
END
BEGIN
INSERT INTO MyTable (Column_1, Column_2, Cloumn_3 )
SELECT 'abc', 'data11', 'data12'
UNION ALL
SELECT 'def', 'data21', 'data22'
END

But see this, for SQL Server 2005 "recAffected" has the correct value of 2 when I have just the insert statement.

INSERT INTO MyTable (Column_1, Column_2, Cloumn_3 )
SELECT 'abc', 'data11', 'data12'
UNION ALL
SELECT 'def', 'data21', 'data22'

For SQL Server 2005 in both cases the table got successfully inserted two rows and the HRESULT of Execute() function returns S_OK.

Does the Execute function has any problem with a statement like the first one (with delete and insert) on a SQL Server 2005 DB?

Why the "recAffected" has a value zero for the first SQL (with delete and insert) on a SQL Server 2005 DB? Do I need to pass any options to Execute() function for a SQL Server 2005 DB?

When connecting to SQL Server Express the "recAffected" has the correct values for any type of SQL statements.

Thank you for your time. Any help greatly appreciated.

Thanks,
Kannan

View 5 Replies View Related

Force To Close Existing Connections When Restoring Existing Database

Aug 13, 2007

Hi All,

I would like to restore database using RESTORE DATABASE ... REPLACE command.
If database exists already and has any open connections this command will fail.
I would like to close all existing connections to specific database before running RESTORE DATABASE ... REPLACE command.
I can do closing from Management Studio using checkbox "Close Existing Connection" when deleting database. Actually I need to do the same but from script.

Please advice me how to do it.

Thanks in advance,
Roman

View 3 Replies View Related

Transact SQL :: Return One Of Two Existing Values For A Record With Same ID

Apr 28, 2015

I have been researching BOL and other online resources but cannot seem to get a definitive answer.

Current Output:
[MemberID][Category][Type]
12345ABCtest
12345XYZtest
12777ABCtest
12888FGDtest

Desired Output:
[MemberID][Category][Type]
12345ABCtest
12777ABCtest
12888FGDtest

Query:
SELECT m.MemberID,
vw.Category,
vw.Type,
FROM dbo.TestVW vw JOIN
dbo.TestMember m ON vw.MemberKey = m.MemberKey
WHERE vw.Type = 'test'
GROUP BY m.MemberID,

[Code] ...

but cannot seem to be able to return one record with its corresponding value criteria.

View 21 Replies View Related

How To Do An Update On Existing Records?

Jul 20, 2005

I have one table of new records (tableA) that may already exist intableB. I want to insert these records into tableB with insert if theydon't already exist, or update any existing ones with new data if theydo already exist. A column (Action) in tableA already tells me whetherthis is an INSERT, UPDATE, or DELETE. I'm able to derive that I can doan insert withselect * into tableB from tableA where Action = 'INSERT'....and I think I can handle the delete.But I'm stuck on the update. How do I do the update? An ordinaryUPDATE statement just won't do unless I use a cursor to cycle throughthe recordset. I want to avoid a cursor.

View 1 Replies View Related

Updating Of Existing Records

Sep 4, 2007

I have a following problem: I€™m importing records from an Access table into a SQL Server Table. I€™m using lookup to determine if a record already exists in the SQL Server table and in that case I should update the record if it was modified.

I thought of using OLE DB Destination for new records (done, works fine) and OLE DB Command Transformation to update the modified records. It all works but the thing that bothers me is that my table has approx. 40 columns so my OLE DB Command is very long (update table set col1 = ?, col2 = ?, col3 = ? €¦€¦). The other problem is that I€™m always updating all the columns even if only one column was modified.

Is there a better way to update the existing records?

Any help is appreciated.

Thanx
Sara

View 4 Replies View Related

SQL Server 2005 Enterprise Edition Installation Stalls On Validating Database Password

Oct 18, 2007

All:

I am attempting to install SQL Server 2005 Enterprise Edition. The install stalls permanently, the SQL Server's memeory usage and CPU usage plummets to nothing and the SQL Server 2005 Setup Dialogue's status stops at "Validating Database Password." This occurs on the SQL Server Database Services portion of the install. The last line in the SQL Server Setup.log says "<Func Name = 'SAPaswwordPolicyCheck'>

I have verified that the sa password that I am entering when prompted for it in the install setup is valid with respect to SQL Server and Windows Server 2003 SP 1 (13 chars, at least one symbol, at least one upper and at least one lower). What else could be causing this problem? Any ideas or stabs would be greatly appreciated.

Thanks,

NubbyDBA

View 1 Replies View Related

Sql Database Data Insertion Problem.

Apr 25, 2007

Hi I have 5 tables. 1). Student 2).Student Info 3). Academics 4).Parents 5).Address. Each table has its own primary key & student Id is taken as the primary key for student Table and is used as foreign key for other tables. Now whenever i try to insert data into these tables nothing happens. I urgently require help. My project is a student database and I am not able to insert data into these tables. My Code is:-   protected void Button1_Click(object sender, EventArgs e) { SqlConnection conn; SqlCommand comm,comm1,comm2,comm3,comm4; string connectionString = ConfigurationManager.ConnectionStrings["XMG.MDFConnectionString"].ConnectionString; conn = new SqlConnection(connectionString); comm = new SqlCommand( "INSERT INTO Student (form_no,programe,date_admission,name_student,gender,caste,nationality,date_birth,place_birth,student_email,martial_status ) " + "VALUES (@form_no,@programe,@date_admission,@name_student,@gender,@caste,@nationality,@date_birth,@place_birth,@photograph,@student_email,@martial_status)", conn); comm.Parameters.Add("@form_no", System.Data.SqlDbType.Int); comm.Parameters["@form_no"].Value = formnotextbox.Text; comm.Parameters.Add("@programe", System.Data.SqlDbType.VarChar); comm.Parameters["@programe"].Value = BcaDropDownList.SelectedValue; comm.Parameters.Add("@date_admission", System.Data.SqlDbType.DateTime); comm.Parameters["@date_admission"].Value = doatextbox.Text; comm.Parameters.Add("@name_student", System.Data.SqlDbType.VarChar); comm.Parameters["@name_student"].Value = Nametextbox.Text; comm.Parameters.Add("@gender", System.Data.SqlDbType.Char); comm.Parameters["@gender"].Value = GenderDropDownList.SelectedValue; comm.Parameters.Add("@caste", System.Data.SqlDbType.VarChar); comm.Parameters["@caste"].Value = casttextbox.Text; comm.Parameters.Add("@nationality", System.Data.SqlDbType.VarChar); comm.Parameters["@nationality"].Value = nationalityTextBox.Text; comm.Parameters.Add("@date_birth", System.Data.SqlDbType.DateTime); comm.Parameters["@date_birth"].Value = sdobTextBox.Text; comm.Parameters.Add("@place_birth", System.Data.SqlDbType.VarChar); comm.Parameters["@place_birth"].Value = pobTextBox.Text; comm.Parameters.Add("@student_email", System.Data.SqlDbType.VarChar); comm.Parameters["@student_email"].Value = emailTextBox.Text; comm.Parameters.Add("@martial_status", System.Data.SqlDbType.Bit); comm.Parameters["@martial_status"].Value = maritalstatusDropDownList.SelectedValue; comm1 = new SqlCommand("INSERT INTO Student info(spouse_name,spouse_dob,marriage_anniversary,student_emp_status,name_organ,designation,nature_of_job,office_address,date_of_joining,joined_as,salary,parent_marriage_anniversary,Category,Physically_Challenged,Blood_Group,donate_blood,Height,Weight,complextion,Anthropometric_Details,Membership_in_club,club_designation,relative_mats,name_of_relative,relative_programme,relative_designation,relative_institutions,relative_class ) " + "VALUES (@spouse_name,@spouse_dob,@marriage_anniversary,@student_emp_status,@name_organ,@designation,@nature_of_job,@office_address,@date_of_joining,@joined_as,@salary,@parent_marriage_anniversary,@Category,@Physically_Challenged,@Blood_Group,@donate_blood,@Height,@Weight,@complextion,@Anthropometric_Details,@Membership_in_club,@club_designation,@relative_mats,@name_of_relative,@relative_programme,@relative_designation,@relative_institutions,@relative_class)", conn); comm1.Parameters.Add("@spouse_name", System.Data.SqlDbType.VarChar); comm1.Parameters["@spouse_name"].Value = SpousenameTextBox.Text; comm1.Parameters.Add("@spouse_dob", System.Data.SqlDbType.DateTime); comm1.Parameters["@spouse_dob"].Value = dobTextBox.Text; comm1.Parameters.Add("@marriage_anniversary", System.Data.SqlDbType.DateTime); comm1.Parameters["@marriage_anniversary"].Value = anniversaryTextBox.Text; comm1.Parameters.Add("@student_emp_status", System.Data.SqlDbType.Bit); comm1.Parameters["@student_emp_status"].Value = employeestatesTextBox.Text; comm1.Parameters.Add("@name_organ", System.Data.SqlDbType.VarChar); comm1.Parameters["@name_organ"].Value = organisationnameTextBox.Text; comm1.Parameters.Add("@designation", System.Data.SqlDbType.VarChar); comm1.Parameters["@designation"].Value = designationTextBox.Text; comm1.Parameters.Add("@nature_of_job", System.Data.SqlDbType.VarChar); comm1.Parameters["@nature_of_job"].Value = snatureofjobTextBox.Text; comm1.Parameters.Add("@date_of_joining", System.Data.SqlDbType.DateTime); comm1.Parameters["@date_of_joining"].Value = sdateofjoiningTextBox.Text; comm1.Parameters.Add("@office_address", System.Data.SqlDbType.VarChar); comm1.Parameters["@office_address"].Value = sofficeaddressTextBox.Text; comm1.Parameters.Add("@joined_as", System.Data.SqlDbType.VarChar); comm1.Parameters["@joined_as"].Value = sjoinedasTextBox.Text; comm1.Parameters.Add("@salary", System.Data.SqlDbType.Int); comm1.Parameters["@salary"].Value = ssalaryTextBox.Text; comm1.Parameters.Add("@parent_marriage_anniversary", System.Data.SqlDbType.DateTime); comm1.Parameters["@parent_marriage_anniversary"].Value = parentmarriageanniversaryTextBox.Text; comm1.Parameters.Add("@Category", System.Data.SqlDbType.Char); comm1.Parameters["@Category"].Value = categoryTextBox.Text; comm1.Parameters.Add("@Physically_Challenged", System.Data.SqlDbType.Bit); comm1.Parameters["@Physically_Challenged"].Value = physicallychallengedTextBox.Text; comm1.Parameters.Add("@Blood_Group", System.Data.SqlDbType.Char); comm1.Parameters["@Blood_Group"].Value = sbloodgroupTextBox.Text; comm1.Parameters.Add("@donate_blood", System.Data.SqlDbType.Bit); comm1.Parameters["@donate_blood"].Value = donateDropDown.SelectedValue; comm1.Parameters.Add("@Height", System.Data.SqlDbType.Decimal); comm1.Parameters["@Height"].Value = heightTextBox.Text; comm1.Parameters.Add("@Weight", System.Data.SqlDbType.Int); comm1.Parameters["@Weight"].Value = weightTextBox.Text; comm1.Parameters.Add("@complextion", System.Data.SqlDbType.VarChar); comm1.Parameters["@complextion"].Value = complextionTextBox.Text; comm1.Parameters.Add("@Anthropometric_Details", System.Data.SqlDbType.Char); comm1.Parameters["@Anthropometric_Details"].Value = AnthroprometicDetailsTextBox.Text; comm1.Parameters.Add("@Membership_in_club", System.Data.SqlDbType.Bit); comm1.Parameters["@Membership_in_club"].Value = MembersshipinclubTextBox.Text; comm1.Parameters.Add("@club_name", System.Data.SqlDbType.VarChar); comm1.Parameters["@club_name"].Value = ClubNameTextBox.Text; comm1.Parameters.Add("@club_designation", System.Data.SqlDbType.Char); comm1.Parameters["@club_designation"].Value = ClubDesignationTextBox.Text; comm1.Parameters.Add("@relative_mats", System.Data.SqlDbType.Bit); comm1.Parameters["@relative_mats"].Value = RelativeinMatsTextBox.Text; comm1.Parameters.Add("@name_of_relative", System.Data.SqlDbType.VarChar); comm1.Parameters["@name_of_relative"].Value = NameofRelativeTextBox.Text; comm1.Parameters.Add("@relative_programme", System.Data.SqlDbType.Char); comm1.Parameters["@relative_programme"].Value = RelativeProgrammeTextBox.Text; comm1.Parameters.Add("@relative_designation", System.Data.SqlDbType.VarChar); comm1.Parameters["@relative_designation"].Value = RelativeDesignationTextBox.Text; comm1.Parameters.Add("@relative_institutions", System.Data.SqlDbType.VarChar); comm1.Parameters["@relative_institutions"].Value = RelativeInstitutiosTextBox.Text; comm1.Parameters.Add("@relative_class", System.Data.SqlDbType.Char); comm1.Parameters["@relative_class"].Value = RelativeClassTextBox.Text; comm2 = new SqlCommand("INSERT INTO Academics (High_School_Board,High_School_Institute,High_School_Percentage,High_School_Year,Higher_Secon_Board,Higher_Secon_Institute,Higher_Secon_Percentage,Higher_Secon_Year,Graduation_Board,Graduation_Institute,Graduation_Percentage,Graduation_Year,Other_Certificates,Gap,Gap_Certificate,Migration_Certificate,Character_Certificate,Other_fields,Level_of_fields ) " + "VALUES (@High_School_Board,@High_School_Institute,@High_School_Percentage,@High_School_Year,@Higher_Secon_Board,@Higher_Secon_Institute,@Higher_Secon_Percentage,@Higher_Secon_Year,@Graduation_Board,@Graduation_Institute,@Graduation_Percentage,@Graduation_Year,@Other_Certificates,@Gap,@Gap_Certificate,@Migration_Certificate,@Character_Certificate,@Other_fields,@Level_of_fields)", conn); comm2.Parameters.Add("@High_School_Board", System.Data.SqlDbType.VarChar); comm2.Parameters["@High_School_Board"].Value = HighSchoolBoardtextbox.Text; comm2.Parameters.Add("@High_School_Institute", System.Data.SqlDbType.VarChar); comm2.Parameters["@High_School_Institute"].Value = HighSchoolInstitutionTextBox.Text; comm2.Parameters.Add("@High_School_Percentage", System.Data.SqlDbType.Decimal); comm2.Parameters["@High_School_Percentage"].Value = HighSchoolpercentageTextBox.Text; comm2.Parameters.Add("@High_School_Year", System.Data.SqlDbType.Int); comm2.Parameters["@High_School_Year"].Value = HighSchoolYearTextBox.Text; comm2.Parameters.Add("@Higher_Secon_Board", System.Data.SqlDbType.VarChar); comm2.Parameters["@Higher_Secon_Board"].Value = HigherSecondaryBordTextBox.Text; comm2.Parameters.Add("@Higher_Secon_Institute", System.Data.SqlDbType.VarChar); comm2.Parameters["@Higher_Secon_Institute"].Value = HigherSecondaryinstituteTextBox.Text; comm2.Parameters.Add("@Higher_Secon_Percentage", System.Data.SqlDbType.Decimal); comm2.Parameters["@Higher_Secon_Percentage"].Value = HigherSecondarypercentageTextBox.Text; comm2.Parameters.Add("@Higher_Secon_Year", System.Data.SqlDbType.Int); comm2.Parameters["@Higher_Secon_Year"].Value = HigherSecondaryYearTextBox.Text; comm2.Parameters.Add("@Graduation_Board", System.Data.SqlDbType.Text); comm2.Parameters["@Graduation_Board"].Value = GraduationBoardTextBox.Text; comm2.Parameters.Add("@Graduation_Institute", System.Data.SqlDbType.VarChar); comm2.Parameters["@Graduation_Institute"].Value = GraduationInstituteTextBox.Text; comm2.Parameters.Add("@Graduation_Percentage", System.Data.SqlDbType.Decimal); comm2.Parameters["@Graduation_Percentage"].Value = GraduationPercentageTextBox.Text; comm2.Parameters.Add("@Graduation_Year", System.Data.SqlDbType.Int); comm2.Parameters["@Graduation_Year"].Value = GraduationYearTextBox.Text; comm2.Parameters.Add("@Gap", System.Data.SqlDbType.Bit); comm2.Parameters["@Gap"].Value = gapeDropDownList.Text; comm2.Parameters.Add("@Gap_Certificate", System.Data.SqlDbType.Bit); comm2.Parameters["@Gap_Certificate"].Value = GapCertificateTextBox.Text; comm2.Parameters.Add("@Migration_Certificate", System.Data.SqlDbType.Bit); comm2.Parameters["@Migration_Certificate"].Value = MigrationCertificateTextBox.Text; comm2.Parameters.Add("@Character_Certificate", System.Data.SqlDbType.Bit); comm2.Parameters["@Character_Certificate"].Value = CharacterCertificateTextBox.Text; comm2.Parameters.Add("@Other_fields", System.Data.SqlDbType.Text); comm2.Parameters["@Other_fields"].Value = OtherFildTextBox.Text; comm2.Parameters.Add("@Level_of_fields", System.Data.SqlDbType.Text); comm2.Parameters["@Level_of_fields"].Value = LevelOfFildsTextBox.Text; comm2.Parameters.Add("@Other_Certificates", System.Data.SqlDbType.Text); comm2.Parameters["@Other_Certificates"].Value = OthercertificateTextBox.Text; comm3 = new SqlCommand("INSERT INTO Parents (father_name,father_dob,father_photograph,father_blood_group,father_job_status,Fname_organization,Fdesignation,Fnature_of_job,Foff_address,Fdate_joining,Fjoined_as,Fsalary,Fname_firm,Ffirm_established,Fdeals_in,Fannual_turn_over,Fincome,Fbusness_off_address,Fcontact_no,Fmobile_no,F_Email,Mother_Name,Mother_dob,Mother_Photograph,Mother_Blood_Group,Mother_Work_Status,Name_Organization,MDesignation,Moffice_address,Mcontact_no,Mdate_joining,Joined_As,Salary_Drawn,M_annualincome,M_Email,M_Mobileno ) " + "VALUES (@father_name,@father_dob,@father_photograph,@father_blood_group,@father_job_status,@Fname_organization,@Fdesignation,@Fnature_of_job,@Foff_address,@Fdate_joining,@Fjoined_as,@Fsalary,@Fname_firm,@Ffirm_established,@Fdeals_in,@Fannual_turn_over,@Fincome,@Fbusness_off_address,@Fcontact_no,@Fmobile_no,@F_Email,@Mother_Name,@Mother_dob,@Mother_Photograph,@Mother_Blood_Group,@Mother_Work_Status,@Name_Organization,@MDesignation,@Moffice_address,@Mcontact_no,@Mdate_joining,@Joined_As,@Salary_Drawn,@M_annualincome,@M_Email,@M_Mobileno)", conn); comm3.Parameters.Add("@father_name", System.Data.SqlDbType.VarChar); comm3.Parameters["@father_name"].Value = Fnametextbox.Text; comm3.Parameters.Add("@father_dob", System.Data.SqlDbType.DateTime); comm3.Parameters["@father_dob"].Value = fDateOfBirthTextBox.Text; comm3.Parameters.Add("@father_photograph", System.Data.SqlDbType.Image); comm3.Parameters["@father_photograph"].Value = PhotographTextBox.Text; comm3.Parameters.Add("@father_blood_group", System.Data.SqlDbType.Char); comm3.Parameters["@father_blood_group"].Value = BloodGroupTextBox.Text; comm3.Parameters.Add("@father_job_status", System.Data.SqlDbType.VarChar); comm3.Parameters["@father_job_status"].Value = JobStatusTextBox.Text; comm3.Parameters.Add("@Fname_organization", System.Data.SqlDbType.VarChar); comm3.Parameters["@Fname_organization"].Value = NameofOrgationTextBox.Text; comm3.Parameters.Add("@Fdesignation", System.Data.SqlDbType.VarChar); comm3.Parameters["@Fdesignation"].Value = fDesignationTextBox.Text; comm3.Parameters.Add("@Fnature_of_job", System.Data.SqlDbType.VarChar); comm3.Parameters["@Fnature_of_job"].Value = NatureofJobTextBox.Text; comm3.Parameters.Add("@Foff_address", System.Data.SqlDbType.VarChar); comm3.Parameters["@Foff_address"].Value = OfficeAddressTextBox.Text; comm3.Parameters.Add("@Fdate_joining", System.Data.SqlDbType.DateTime); comm3.Parameters["@Fdate_joining"].Value = DateofJoiningTextBox.Text; comm3.Parameters.Add("@Fjoined_as", System.Data.SqlDbType.VarChar); comm3.Parameters["@Fjoined_as"].Value = JoinedasTextBox.Text; comm3.Parameters.Add("@Fsalary", System.Data.SqlDbType.Int); comm3.Parameters["@Fsalary"].Value = SalaryTextBox.Text; comm3.Parameters.Add("@Fname_firm", System.Data.SqlDbType.VarChar); comm3.Parameters["@Fname_firm"].Value = NameofFirmTextBox.Text; comm3.Parameters.Add("@Ffirm_established", System.Data.SqlDbType.Int); comm3.Parameters["@Ffirm_established"].Value = firmEstablishedTextBox.Text; comm3.Parameters.Add("@Fdeals_in", System.Data.SqlDbType.Int); comm3.Parameters["@Fdeals_in"].Value = dealsinTextBox.Text; comm3.Parameters.Add("@Fannual_turn_over", System.Data.SqlDbType.Int); comm3.Parameters["@Fannual_turn_over"].Value = AnnualTurnOverTextBox.Text; comm3.Parameters.Add("@Fincome", System.Data.SqlDbType.Int); comm3.Parameters["@Fincome"].Value = IncomeTextBox.Text; comm3.Parameters.Add("@Fbusness_off_address", System.Data.SqlDbType.VarChar); comm3.Parameters["@Fbusness_off_address"].Value = BoAddressTextBox.Text; comm3.Parameters.Add("@Fcontact_no", System.Data.SqlDbType.BigInt); comm3.Parameters["@Fcontact_no"].Value = ContactNumberTextBox.Text; comm3.Parameters.Add("@Fmobile_no", System.Data.SqlDbType.BigInt); comm3.Parameters["@Fmobile_no"].Value = MobileNumberTextBox.Text; comm3.Parameters.Add("@F_Email", System.Data.SqlDbType.Text); comm3.Parameters["@F_Email"].Value = EmailIdTextBox.Text; comm3.Parameters.Add("@Mother_Name", System.Data.SqlDbType.Text); comm3.Parameters["@Mother_Name"].Value = MnameTextBox.Text; comm3.Parameters.Add("@Mother_dob", System.Data.SqlDbType.DateTime); comm3.Parameters["@Mother_dob"].Value = MdobTextBox.Text; comm3.Parameters.Add("@Mother_Photograph", System.Data.SqlDbType.Image); comm3.Parameters["@Mother_Photograph"].Value = HighSchoolInstitutionTextBox.Text; comm3.Parameters.Add("@Mother_Blood_Group", System.Data.SqlDbType.Text); comm3.Parameters["@Mother_Blood_Group"].Value = MbloodgroupTextBox.Text; comm3.Parameters.Add("@Mother_Work_Status", System.Data.SqlDbType.Char); comm3.Parameters["@Mother_Work_Status"].Value = MworkstatusTextBox.Text; comm3.Parameters.Add("@Name_Organization", System.Data.SqlDbType.VarChar); comm3.Parameters["@Name_Organization"].Value = MnameofOrgationTextBox.Text; comm3.Parameters.Add("@MDesignation", System.Data.SqlDbType.Text); comm3.Parameters["@MDesignation"].Value = MdesignationTextBox.Text; comm3.Parameters.Add("@Moffice_address", System.Data.SqlDbType.VarChar); comm3.Parameters["@Moffice_address"].Value = MofficeAddressTextBox.Text; comm3.Parameters.Add("@Mcontact_no", System.Data.SqlDbType.BigInt); comm3.Parameters["@Mcontact_no"].Value = McontactnoTextBox.Text; comm3.Parameters.Add("@Mdate_joining", System.Data.SqlDbType.DateTime); comm3.Parameters["@Mdate_joining"].Value = MdateofjoiningTextBox.Text; comm3.Parameters.Add("@Joined_As", System.Data.SqlDbType.Char); comm3.Parameters["@Joined_As"].Value = MjoiningasTextBox.Text; comm3.Parameters.Add("@Salary_Drawn", System.Data.SqlDbType.Float); comm3.Parameters["@Salary_Drawn"].Value = MSalarydrwnTextBox.Text; comm3.Parameters.Add("@M_annualincome", System.Data.SqlDbType.Float); comm3.Parameters["@M_annualincome"].Value = MAnnualincomeTextBox.Text; comm3.Parameters.Add("@M_Email", System.Data.SqlDbType.Text); comm3.Parameters["@M_Email"].Value = MemailidTextBox.Text; comm3.Parameters.Add("@M_Mobileno", System.Data.SqlDbType.BigInt); comm3.Parameters["@M_Mobileno"].Value = mmnoTextBox.Text; comm4 = new SqlCommand("INSERT INTO Address (Residential_Address,City,State,Country,Phone_No,Fax_No,Permanent_Address,Permanent_City,Permanent_State,Permanent_Country,Name_L_Guardian,Guardian_Address,Contact_Number,Relation_L_Guardian,Photograph_L_Guardian,Hostel_Details ) " + "VALUES (@Residential_Address,@City,State,@Country,@Phone_No,@Fax_No,@Permanent_Address,@Permanent_City,@Permanent_State,@Permanent_Country,@Name_L_Guardian,@Guardian_Address,@Contact_Number,@Relation_L_Guardian,@Photograph_L_Guardian,@Hostel_Details)", conn); comm4.Parameters.Add("@Residential_Address", System.Data.SqlDbType.VarChar); comm4.Parameters["@Residential_Address"].Value = ResidentialAddressTextBox.Text; comm4.Parameters.Add("@City", System.Data.SqlDbType.VarChar); comm4.Parameters["@City"].Value = cityTextBox.Text; comm4.Parameters.Add("@State", System.Data.SqlDbType.VarChar); comm4.Parameters["@State"].Value = stateTextBox.Text; comm4.Parameters.Add("@Country", System.Data.SqlDbType.VarChar); comm4.Parameters["@Country"].Value = CountryTextBox.Text; comm4.Parameters.Add("@Phone_No", System.Data.SqlDbType.BigInt); comm4.Parameters["@Phone_No"].Value = PhonenumberTextBox.Text; comm4.Parameters.Add("@Fax_No", System.Data.SqlDbType.BigInt); comm4.Parameters["@Fax_No"].Value = FaxnumberTextBox.Text; comm4.Parameters.Add("@Permanent_Address", System.Data.SqlDbType.VarChar); comm4.Parameters["@Permanent_Address"].Value = PermanentAdressTextBox.Text; comm4.Parameters.Add("@Permanent_City", System.Data.SqlDbType.VarChar); comm4.Parameters["@Permanent_City"].Value = PermanentcityTextBox.Text; comm4.Parameters.Add("@Permanent_State", System.Data.SqlDbType.VarChar); comm4.Parameters["@Permanent_State"].Value = PermanentStateTextBox.Text; comm4.Parameters.Add("@Permanent_Country", System.Data.SqlDbType.VarChar); comm4.Parameters["@Permanent_Country"].Value = PermanentCountryTextBox.Text; comm4.Parameters.Add("@Name_L_Guardian", System.Data.SqlDbType.VarChar); comm4.Parameters["@Name_L_Guardian"].Value = NameofLocalGuardianTextBox.Text; comm4.Parameters.Add("@Guardian_Address", System.Data.SqlDbType.VarChar); comm4.Parameters["@Guardian_Address"].Value = GuardianAddressTextBox.Text; comm4.Parameters.Add("@Contact_Number", System.Data.SqlDbType.BigInt); comm4.Parameters["@Contact_Number"].Value = GContactTextBox.Text; comm4.Parameters.Add("@Relation_L_Guardian", System.Data.SqlDbType.VarChar); comm4.Parameters["@Relation_L_Guardian"].Value = relationloTextBox.Text; comm4.Parameters.Add("@Photograph_L_Guardian", System.Data.SqlDbType.Image); comm4.Parameters["@Photograph_L_Guardian"].Value = SpousenameTextBox.Text; comm4.Parameters.Add("@Hostel_Details", System.Data.SqlDbType.VarChar); comm4.Parameters["@Hostel_Details"].Value = HostalDetailTextBox.Text; try { conn.Open(); comm.ExecuteNonQuery(); comm1.ExecuteNonQuery(); comm2.ExecuteNonQuery(); comm3.ExecuteNonQuery(); comm4.ExecuteNonQuery(); Response.Redirect("HelpDesk.aspx"); } catch { } finally { conn.Close(); } }} PLEASE HELP ME. I AM IN TROUBLE.     

View 3 Replies View Related

Connecting To Database For Bulk Insertion

Oct 20, 2007

HI friends,
    I am retrieving oracle data(linked server) to my local sql server 2005. Initially I want to have duplicate copy of the oracle data to my local server. I have created linked server and inserting to my local table(exact replica). This will run for every 2 minutes(as per my client requirement) to get newly added records.
But, I want to retrieve specific columns from the replicated table and insert into other local tables. i have written a SP to do this. some columns are storing in other tables to maintain normalization. this should also run for every two minutes.
I have written a class (c#) to retrieve replica table and passing parameters to SP and inserting to my local tables in normalized form.
But this is taking 10 minutes to complete my process to insert 1500 records. but my client insist to reduce the speed to run for every 2 mins.
is it correct way wat i am doing? or any other solution is there?
pls suggest me.
thanks in advance

View 1 Replies View Related

Replacing Existing Records In A Table

Feb 5, 2007

I have a table that stores all the processed data from other tables. How can I replace the same data in this table when I do "reprocessing"? It's kinda like a combination of delete then insert kind of thing. I cannot simply insert as it will become duplicates. any idea?

View 3 Replies View Related

Search No Existing Sets Of Records

Jan 19, 2014

I need to query the contents: Look in Table A of such sets (A, B), which are not present in table B

To illustrate:

Tab A
A....B
aa..kr - this set does not occur in Tab B
bb..gh
vv..kl
cc..er
ss..we

[code]....

View 3 Replies View Related

SQL / Not Existing Records As Blank Cells

Oct 7, 2007

hi

I have data in two tables.

NAMES
IDName
1FIRST
2SECOND


CODES
IDCodeTypeCode
1Axyz
1Babc
1Cgfd
2Axdz
2Bdca



I want to join the two tables to add the Code of CodeType "C" to the records of NAMES

Result Example
IDNameCode
1FIRSTgfd
2SECOND----


I want to have all records from the names with the codetype C, if there is no record with the codetype c for a given ID, the cell should be blank to identify for which ID's the CodeType C is mising.

how should the sql statement look like?

Please help!
thanks in advance!

Mikk

View 1 Replies View Related

Using A Lookup To Update Existing Records

Jul 2, 2007

Hi,



I'm building a package to import data from a flat file into a Customer table, and I have set up a Lookup to check if that customer already exists in this table, and if so, perform an Update command instead of the bulk load. I don't expect many updates, if any, this is why i just used the OLE DB Command instead of using a staging table.



I've a bit of a problem executing this within a transaction and having the lock table option set on the SQL Server Destination. Is there anyway I can get Transaction support for this Data Flow working, as I want to be able to rollback a complete file/import if possible.



Thanks

Trent

View 4 Replies View Related

T-SQL (SS2K8) :: Creating Database Where Each Record Is Required To Have Twin Record In Database

May 12, 2014

,I am creating a database where each record is required to have a twin record in the database.These is a type a value and a type b value and both must be present for the record to be valid.

Customer_ID, Order_Type, Product_Code
54, a, 00345
54, b, 00356

Is this something that would have to be done programmatically, or is it possible to create a constraint of some sort to ensure this?

View 8 Replies View Related

How To Prevent The Insertion Of The Same Entry Of An Unique Value Into The Database

Jun 4, 2008

how to prevent the insertion of the same entry of an unique value into the database?
assume that I have a primary key username " abc"  in the database
and then I insert the "abc" again ,the debuggging error msg pops out saying the primary key cannot be duplicated..
how can I do an if--else statement to check the database against this unintentional inputs of the same unique data " abc"???
 

View 7 Replies View Related

Importing Data (Overwrite Any Existing Records)

Mar 19, 2007

Good Morning,  I need some assistance with SQL Server 2000 Importing Data. 
When I import data from a text on a routine basis, three things must happen:
1.  New records identified by primary key get appended to table.
2.  Exisiting records identified by primary key get overwritten with new/(updated) data.
3.  All other existing records are left alone.
Does anyone know how to Import Records with the following the criteria above?  It cannot insert duplicate primary keys by nature, so it must overwrite those records!
This is being built into a DTS Package, but I need to get over this obsticle!  Thanks for any guidance!

View 2 Replies View Related

Problem Updating Existing Records With Newid()

Apr 23, 2008

I currently have a table called stores.  I've just added a uniqueidentifier column called store_guid to the stores table.  The table currently has about 500 records in it and now i'm trying to set each store_guid = to a newid().  I've tried using  UPDATE stores SET store_guid = newid()  .   however, that doesn't work because i think it's trying to set each record equal to the same guid using that approach.  All i need to do is fill in my new column with new guids.  any ideas?
 
Thanks in advance,
 

View 3 Replies View Related

Question: Changing Case On Existing Records

Jul 23, 2005

Greetings:I have a SQL 2000 database, in which about 1% of the records are inlower case. I need to make them UPPER CASE.Is there a function to determine and change the case of existingrecords, or will I have to re-write the records manually?Thanks,DW

View 6 Replies View Related

Integration Services :: Check Table For Existing Record Before Data Flow Task

Jun 1, 2015

Using SSIS 2012 (within Visual Studio) on Windows 7.

Before allowing my Data Flow task to fire, I'd like to check the target table (OLE DB Destination) for a specific date value in a specific field. I've seen how the Lookup Task is commonly used to check for dupes before inserting, but I'm not able to use that method because the data value I want to search the table for is contained in a Global Variable (let's say "MyVariableDate"). 

Is there any way to check for any records in a target table where Date1 = MyVariableDate (i.e. scanning the entire table for any occurrence of MyVariableDate in the Date1 field)?

View 12 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved