Modifying Records In SQL Database Programmatically
Jan 23, 2008
VWD 2005 Express. Visual Basic. SQL Server 2005.
I know how to set up SqlDataSources and their insert, delete, update, and select commands. I also have code for querying a Sql table and populating a dataset and scanning the dataset for values. However, I do not know how to modify records in a Sql table programmatically. Here is what I need to do:
I need to open a Sql table.
I need to process record bny record to check a particular field for a particular value.
If the field has the particular value, I need to change it and write the record back to the table with the modification.
The table is named "SystemUser" and the field is call "SystemUserTypeId."
Can anyone provide me with sample code that would accomplish this? Thanks for the help.
View 5 Replies
ADVERTISEMENT
Mar 24, 2008
I am making some changes to a tracking SP that I'm using in about 100 SSIS packages. I don't want to go into each package and add the additional parameter (A "?") and map a variable to some ordinal position. I'd like to do this programmatically.
I have some expirence in validing variables and manipulating connection objects in packages though a utility that I wrote. However, I cannot find any guidance on How to create/edit Execute SQL Tasks via code in the package. I would like to do something like this..
Code Snippet
private Microsoft.SqlServer.Dts.Runtime.Application app = new Microsoft.SqlServer.Dts.Runtime.Application();
Package p = app.LoadPackage(DTSPackage, null);
p.Executables["My Execute SQL Task"]
However, the Executables returns an Executable object that is a DtsObject. Do I have to add a reference to the task and cast this executable to that type? Any help is appreciated.
Thanks
Scott
View 4 Replies
View Related
Aug 26, 2006
I'm a new user to vwd. If I use a details view control on my page, I have noticed that the "New" link is not visible unless there is at least one record in the table. Is there any way of making it visible where there aren't any records?My web pages are currently hosted at vwdhosting. I have uploaded my database with the record structure onto the web site and I am using a remote connection string to access it. I have had users updating data in another table on the remote database. If I add records to my new table locally and upload the database to the remote site, all the data that my users have been adding will be lost. So, if I can't add my first record using a control on my web page when there are no records in the table, should I be doing it programmatically? If so, how?Thanks,Julie
View 2 Replies
View Related
Nov 26, 2007
Hello,
I have a VB.NET Windows Application with a SQL 2K5 back end.
The application creates a view which produces approximately 5 million records. I am attempting to transfer these records to a table programmatically, but every attempt causes a timeout. The name of the source server and view will change depending on the client's installation.
Using the standard SQL connection/command objects with the "INSERT INTO TABLE (SELECT * FROM VIEW)" doesn't work at all.
What is the best way to transfer these records programmatically?
Thanks,
-Torrwin
View 5 Replies
View Related
Jul 23, 2005
Hi Folks,I should begin by saying I'm fairly new to SQL Server. I'm an applicationdeveloper who has built front ends atop Server, but have never done much inthe way of design / development.My question is in two parts;1). What is the best way to remotely "create" an SQL Server database? I'vebeen told that this can be done with SQL Statements, but thought that theremay be a way using XML.2). Can I use this same method to propogate updates to the remote databasewithout affecting the data?To give you a better idea of what I'm trying to do, I have a client thatlives quite a ways up north. I'm building an application that will beinstalled up there. I'd like a method to be able to make changes to thisdatabase on my end (the development copy), create an XML file of the"database structure" and have the other database or application import thisXML file, and add tables, fields and relationships if they don't alreadyexist.Any help would be appreciated.Thanks!Rick
View 1 Replies
View Related
Nov 8, 2007
I've used ClickOne to package my desktop database application (Developed using VB 2005 Express and SQLServer 2005 Express) then installed it on another PC. Is there a way that I could modify the installed database just like I could if I used MS Access?
View 2 Replies
View Related
Aug 6, 2015
I want to restore a database (from an encrypted .bak file) - but *not* over the live original if you take my meaning. Encryption is the standard AES-256 that comes with Sql Server 2014 btw. I don't want the original touched/altered in any way. I would like to capture a success message if possible.I can extract the physical device name of the database in question using the following code:
SELECT physical_device_name, *
FROM msdb.dbo.backupmediafamily
WHERE media_set_id =(SELECT TOP 1 media_set_id
FROM msdb.dbo.backupset
WHERE database_name='MyDatabase'
AND type='D'
ORDER BY backup_start_date DESC)
I would like if the newly restored database was rename to something different than 'MyDatabase' (as shown above) and has different logs than the original. If possible, and capture a success message when restored.
View 5 Replies
View Related
Aug 30, 2006
I've seen several post asking for that possibility, but all 've read, didn't help me.Some sing SQLDMO, other suggest to use SQLSMO, others only explaining to connect to a server and then do "CREATE DATABASE".I will do this within .Net. Connecting to a SQL Server 2005 and execute "CREATE DATABASE" I could realize.But how doing this with SQLExpress? Trying to do SqlConnection.Open with a non existing DB does not work., says "file not exists".Or do I only have the wrong connection string? Can someone post here an excample connection string, which works with a non existing database?Some hints I've read make me considering to use SQLSMO. But I don't have it on my computer. Where do I get it from? Any links would be nice.
View 6 Replies
View Related
Jan 21, 2005
Howdy,
Is the any easy way to clone a database programatically?
I've searched the web but did not come up with anything.
At the moment I create a new blank database (based on the model database)
and create tables, indexes, etc. via asp. (The databases are always exactly
the same)
It seems to me that it would be a better option to
create one database with tables, indexes, etc and copy
it to a new database (on the same server).
One of the options I'm considering would be to create a database,
fill it with tables and indexes, etc. and then detach it.
Whenever I need a new database all I would have to do is use the
file system object to copy and rename the mdf file and then reattach the
new mdf file.
I realise that I could also make the changes to the model
database so that all new databases have the same structure
but that would be my last resort.
Is there any easier way to do this? A stored procedure perhaps?
You're opinions and advice would be welcomed.
View 5 Replies
View Related
Apr 5, 2006
I recently had to find ways to programmatically backup and restore anSQL Server Express database.For backup, I found a one-liner stored procedure that does the job(BACKUP DATABASE Toy to disk = 'c:ProjectsToy.bak'.My question is, is there something as simple for a restore operation?Thanks in Advance-Kostas
View 4 Replies
View Related
Jan 3, 2008
Hi all
this is my code and i find it in microsoft's site
if i run it with sql server connection it works but if i try to use it with sql express it give me this error:
CREATE FILE encountered operating system error 5(access denied) while attempting to open or create the physical file 'c://mydatabase.mdf'
it seems as a permission error but it isn't. I have to set something in sql express while in sql server it is already setted?
static void WriteDB()
{
String str;
//sql server connection
SqlConnection myConn = new SqlConnection("Server=localhost;Integrated security=SSPI;database=master");
//sql express connection
SqlConnection myConn = new SqlConnection("Server=localhost;Integrated security=SSPI;database=master");
str = "CREATE DATABASE MyDatabase ON PRIMARY " +
"(NAME = MyDatabase_Data, " +
"FILENAME = 'C:\MyDatabaseData.mdf', " +
"SIZE = 2MB, MAXSIZE = 10MB, FILEGROWTH = 10%) " +
"LOG ON (NAME = MyDatabase_Log, " +
"FILENAME = 'C:\MyDatabaseLog.ldf', " +
"SIZE = 1MB, " +
"MAXSIZE = 5MB, " +
"FILEGROWTH = 10%)";
SqlCommand myCommand = new SqlCommand(str, myConn);
try
{
myConn.Open();
myCommand.ExecuteNonQuery();
MessageBox.Show("DataBase is Created Successfully", "MyProgram", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
catch (System.Exception ex)
{
MessageBox.Show(ex.ToString(), "MyProgram", MessageBoxButtons.OK, MessageBoxIcon.Information);
}
finally
{
if (myConn.State == ConnectionState.Open)
{
myConn.Close();
}
}
}
thanks
Marco
View 3 Replies
View Related
May 12, 2008
I'm trying to compact a SQL CE database programmatically. I've copied the code from MSDN, and it's fine as far as it goes, but there's a problem tying to compact a database in a ClickOnce application because you don't know the path to the database. You have to use the "|DataDirectory|" in your connection string to signify the path to the database. But when I try the file operations to replace the old database with the compacted version, I get an error message that the path "|DataDirectory|" is not valid. Here's the code:
SqlCeEngine engine = new SqlCeEngine("Data Source = " + src + "; Password =" + txtPassword.Text);
try
{
engine.Compact("Data Source = " + dest + "; Password =" + txtPassword.Text);
ShowMsg("Compacting...", MsgType.NoError);
}
catch (Exception ex)
{
MessageBox.Show("There was an error completing the operation: " + ex.Message);
}
engine.Dispose();
File.Delete(src);
File.Move(dest, src);
(dest is = src + ".tmp", i.e., a path to a tmp file to hold the database while compacting).
How do I locate the files to copy if I can't use "|DataDirectory|"?
View 3 Replies
View Related
Mar 12, 2008
Hi Guys,
I was wondering if there is a system procedure / T-SQL code to programmatically Failover a Database from Principal to mirror mode?
also,
We noticed that at times few of all the databases that were kept on 'Principal' mode get randomly 'Failover'. Does anyone have any idea?
If so, pls respond.
Thank you.
View 9 Replies
View Related
Jun 28, 2006
When my app starts up I want to ensure that the database compatibility level has been set to 90. I know about sp_dbcmptlevel, but that only seems to work in an interactive session; the documentation says it can't be used in a stored procedure, and that appears to be true. Does anyone know how I could read the database compatibility level in a stored proc?
View 3 Replies
View Related
Jul 10, 2012
I need to be able to create & connect to temporary databases programmatically, and NOT using the command-line, within a C# program. How would one go about doing this?
View 7 Replies
View Related
Dec 16, 2007
Hello friends,
I am new to the SQL Server 2005 development.
From last 1 week or so, i have been facing very strange problem with my sql server 2005s database
which is configured and set on the hosting web server. Right now for managing my sql server 2005 database,
i am using an web based Control Panel developed by my hosting company.
Problem i am facing is that, whenever i try to modify (i.e. add new columns) tables in the database,
it gives me error saying that,
"There is already an object named 'PK_xxx_Temp' in the database. Could not create constraint. See previous errors.
Source: .Net SqlClient Data Provider".
where xxx is the table name.
I have done quite a bit research on the problem and have also searched on the net for solution but still
the problem persist.
Thanks in advance. Any help will be appreciated.
View 5 Replies
View Related
Mar 10, 2015
Recently we had multiple production failures because one database was in single-user mode. I am trying to recreate this scenario to write a script that will kick out that connection that was holding database in single-user.In SSMS window 1 I run the following code:
use master
go
alter database test
set single_user
with rollback immediate
[code]....
Yes, it shows one record, but the database name is master, not test. And indeed, I run it from the context of master. Also tried sp_who, sp_who2, sys.dm_exec_requests - nothing works. For dm_exec_requests it shows nothing because it does not run at the current moment.Any other solutions to catch this SPID with a script?
View 5 Replies
View Related
Apr 6, 2008
Hi,
I already submitted this type of question before and i receive reply. But unfortunately i found out errors when performing on my system.
My problem regarding to this one:
Suppose i have two databases with same tables with different records and I would like to copy the records from one database to another data and vice-versa. So that both the tables contains same number of records inside the tables.
Example:
Database1 (EmployeeTable) contains 6 records.
Database2 (EmployeeTable) contains 10 records.
It should copy only those records which is not present in each other database. No duplicate records.
Before i was recommend to use Primary key, if it is not present use index.
Hope this time i could solve my problem.
Thanks.
Kashif Chotu
View 1 Replies
View Related
Mar 20, 2014
writing the query for the following, I need to collapse the continuity. If the termdate for an ID is one day less than the effdate of the next id (for the same ID) i need to collapse the records. See below example .....how should i write the query which will give me the desired output. i.e., get min(effdate) and max(termdate) if termdate is one day less than the effdate of next record.
ID effdate termdate
556868 1999-01-01 1999-06-30
556868 1999-07-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-01-31
556872 2004-02-01 2004-02-29
output should be ......
ID effdate termdate
556868 1999-01-01 1999-10-31
556869 2002-10-01 2004-01-31
556872 1999-02-01 2000-08-31
556872 2000-11-01 2004-02-29
View 0 Replies
View Related
Jan 10, 2002
I have an existing table called OrderHeader with a column called ID. The ID column is currently set as an INT. I want to run a script to modify that column to be an IDENTIY. I've tried the following:
ALTER TABLE OrderHeader ALTER COLUMN [ID] INT IDENTITY
but this does not work.
Any idea how to accomplish this?
Thanks!
Mike
View 1 Replies
View Related
Apr 28, 2004
I am trying to modify data in a tble using the Stored Procedure below. It doesnt work properly. I seem to only be getting one character. I think it has something to do with me using "nchar" with the variables. The value I am changing is actually a string.
Alter Procedure usp_UpdateJobName
@JobNameOld nchar, @JobNameNew nchar
As
UPDATE
JobName
SET
JobName=@JobNameNew
FROM
tblRMADATA
WHERE
tblRMADATA.JobName=@JobNameOld
View 4 Replies
View Related
Apr 28, 2004
I have a DTS package package1 which imports an excel sheet into my DataBase.ANd its saved and scheduled and running fine.Actually the excel sheet will be located daily @ c:ruceexceldata.But now we wanted to change the location of the file to c:ruce1ewexceldata
So how and where can I change my package.
Thanks.
View 2 Replies
View Related
May 5, 2004
I have a scheduled DTS package which gets the data from a text file and imports into a sql table.The table will be droped and created whenever the DTS package runs.previusly the table has 6 rows and now i wanted to add a column row_id which is having an idenity seed.So how can I modify my package so that I can get done with my requirements.
View 1 Replies
View Related
May 29, 2008
Hi,
I have a query that I am working on that involves 2 tables. The query below is working correctly and bringing back the desired results, except I want to add 1 more column of data, and I'm not exactly sure how to write it.
What I want to add is the following data.
For each row that is brought back we want to have the COUNT(*) of users who joined the website (tbluserdetails) where their tbluserdteails.date is > the tblreferemails.referDate
Effectively we are attempting to track how well the "tell a friend" via email feature works, and converts to other joined members.
Any assistance is much appreciated!!
thanks once again
mike123
SELECT CONVERT(varchar(10),referDate,112) AS referDate,
SUM ( CASE WHEN emailSendCount = 0 THEN 1 ELSE 0 END ) as '0',
SUM ( CASE WHEN emailSendCount = 1 THEN 1 ELSE 0 END ) as '1',
SUM ( CASE WHEN emailSendCount = 2 THEN 1 ELSE 0 END ) as '2',
SUM ( CASE WHEN emailSendCount = 3 THEN 1 ELSE 0 END ) as '3',
SUM ( CASE WHEN emailSendCount > 3 THEN 1 ELSE 0 END ) as '> 3',
SUM ( CASE WHEN emailSendCount > 0 THEN 1 ELSE 0 END ) as 'totalSent',
count(*) as totalRefers,
count(distinct(referUserID)) as totalUsers,
SUM ( CASE WHEN emailAddress like '%hotmail%' THEN 1 ELSE 0 END ) as 'hotmail',
SUM ( CASE WHEN emailAddress like '%hotmail.co.uk%' THEN 1 ELSE 0 END ) as 'hotmail.co.uk',
SUM ( CASE WHEN emailAddress like '%yahoo.ca%' THEN 1 ELSE 0 END ) as 'yahoo.ca',
SUM ( CASE WHEN emailAddress like '%yahoo.co.uk%' THEN 1 ELSE 0 END ) as 'yahoo.co.uk',
SUM ( CASE WHEN emailAddress like '%gmail%' THEN 1 ELSE 0 END ) as 'gmail',
SUM ( CASE WHEN emailAddress like '%aol%' THEN 1 ELSE 0 END ) as 'aol',
SUM ( CASE WHEN emailAddress like '%yahoo%' THEN 1 ELSE 0 END ) as 'yahoo',
SUM ( CASE WHEN referalTypeID = 1 THEN 1 ELSE 0 END ) as 'manual',
SUM ( CASE WHEN referalTypeID = 2 THEN 1 ELSE 0 END ) as 'auto'
FROM tblreferemails R
WHERE DateDiff(dd, referDate, GetDate()) < 5
GROUP BY CONVERT(varchar(10),referDate,112)
ORDER BY referDate DESC
CREATE TABLE [dbo].[tblUserDetails]
(
[UserID] [int] IDENTITY(1,1) NOT NULL,
[NameOnline] [varchar](15) NULL,
[EmailAddress] [varchar](50) NULL,
[Date] [datetime] NULL,
[Active] [tinyint] NULL
)
CREATE TABLE [dbo].[tblReferEmails](
[emailID] [int] IDENTITY(1,1) NOT NULL,
[referUserID] [int] NOT NULL,
[destinationName] [varchar](50) NULL,
[emailaddress] [varchar](50) NOT NULL,
[referDate] [datetime] NOT NULL,
[referalTypeID] [int] NULL,
[deleted] [int] NULL,
[emailSendCount] [int] NULL,
[lastDateSent] [smalldatetime] NULL
) ON [PRIMARY]
GO
View 3 Replies
View Related
Nov 19, 2007
Greetings
I have 3 tables. The tables are populated in the following order: One row for CallDetail, One for Call and one for Request and so on
I have to generate a UniqueNo - Per empid, Per StateNo, Per CityNo, Per CallType grouped by the CallDetailID and ordered by the date created
SCRIPTS
DECLARE @Request TABLE(RequestID INT, CustomerName VARCHAR(30),
StateNo NVARCHAR(5), CityNo INT, CallID INT, UniqueNo INT)
INSERT @Request
SELECT '324234', 'Jack', 'SA023', 12, 111, Null UNION ALL
SELECT '223452', 'Tom', 'SA023', 12, 112, Null UNION ALL
SELECT '456456', 'Bobby', 'SA024', 12, 114, Null UNION ALL
SELECT '22322362', 'Guck', 'SA024', 44, 123, Null UNION ALL
SELECT '22654392', 'Luck', 'SA023', 12, 134, Null UNION ALL
SELECT '225652', 'Jim', 'SA055', 67, 143, Null UNION ALL
SELECT '126756', 'Jasm', 'SA055', 67, 145, Null UNION ALL
SELECT '786234', 'Chuck', 'SA055', 67, 154, Null UNION ALL
SELECT '66234', 'Mutuk', 'SA059', 72, 185, Null UNION ALL
SELECT '2232362', 'Buck', 'SA055', 67, 195, Null
DECLARE @Call TABLE(CallID INT, CallType INT, CallDetailID INT)
INSERT @Call
SELECT 111, 1, 12123 UNION ALL
SELECT 112, 1, 12123 UNION ALL
SELECT 114, 2, 12123 UNION ALL
SELECT 123, 2, 12123 UNION ALL
SELECT 134, 3, 12123 UNION ALL
SELECT 143, 1, 6532 UNION ALL
SELECT 145, 1, 6532 UNION ALL
SELECT 154, 2, 6532 UNION ALL
SELECT 185, 2, 6532 UNION ALL
SELECT 195, 3, 6532
DECLARE @CallDetail TABLE(CallDetailID INT, EmpID INT, EntryDt DateTime)
INSERT @CallDetail
SELECT 12123, 1, '11/5/2007 10:41:34 AM' UNION ALL
SELECT 6532, 1, '11/5/2007 12:12:34 PM'
--
--select * from @Request
Query written to achieve the requirement
UPDATE r
SET r.UniqueNo = p.RecID
FROM @Request AS r
INNER JOIN (
SELECT r.RequestID,
ROW_NUMBER() OVER (PARTITION BY cd.EmpID, r.StateNo, r.CityNo, c.CallDetailID, c.CallType ORDER BY cd.EntryDt) AS RecID
FROM @Request AS r
INNER JOIN @Call AS c ON c.CallID = r.CallID
INNER JOIN @CallDetail AS cd ON cd.CallDetailID = c.CallDetailID
) AS p ON p.RequestID = r.RequestID
WHERE r.UniqueNo IS NULL
select * from @Request
OUTPUT :
324234 Jack SA023 12 111 1
223452 Tom SA023 12 112 2
456456 Bobby SA024 12 114 1
22322362 Guck SA024 44 123 1
22654392 Luck SA023 12 134 1
225652 Jim SA055 67 143 1
126756 Jasm SA055 67 145 2
786234 Chuck SA055 67 154 1
66234 Mutuk SA059 72 185 1
2232362 Buck SA055 67 195 1
EXPECTED OUTPUT: (See the last column for unique nos).
324234 Jack SA023 12 111 1
223452 Tom SA023 12 112 1
456456 Bobby SA024 12 114 1
22322362 Guck SA024 44 123 1
22654392 Luck SA023 12 134 1
225652 Jim SA055 67 143 2
126756 Jasm SA055 67 145 2
786234 Chuck SA055 67 154 2
66234 Mutuk SA059 72 185 2
2232362 Buck SA055 67 195 2
How can I modify the query to achieve the expected output?
Thanks:
View 9 Replies
View Related
Feb 13, 2008
Hi our reports server has some reports on it.
we need to modify some of them. how can i get those reports on to "SQL BUSSINESS INTELLIGECSE DEVELOPMENT STUDIO".
can we get rdl file from reporting server ?
View 3 Replies
View Related
Sep 27, 2007
Can I modify 2000 DTS in 2005.I mean to say add some functionality to the existing DTS.
View 1 Replies
View Related
Oct 16, 2007
I need help with modifying this procedure to join JobTypeGallery, Remodel on JobTypeGallery.TypeID and Remodel.TypeID.
I would like for it the procedure to not allow deleting a record from JobTypeGallery if there are any records in Remodel Table that is associated with JobTypeGallery. Can someone please help me modify this stored procedure?
Create PROCEDURE [dbo].[spDeleteJobTypeGallery] @typeid int AS delete from jobTypeGallery where typeID = @typeid GO
View 4 Replies
View Related
Dec 6, 2004
I probably know that I don't want to do this, but I have an odd case.
I have a database with a bunch of stored procedures and functions. 128 of them reference a different database than the one they're in. For testing puposes, I need to temporarily re-point these functions and procedures to a different database.
I suspect the answer will be a resounding 'No', but I was wondering if it was possible to somehow run a query against syscomments where I could update all of those objects with the temporary database name rather than edit each and every one of them. Conceptually, it would just be an UPDATE using REPLACE. Pretty comfortable with that, but I'm always very reluctant to mess with stuff like that.
I'll probably still just do it through QA, but I was wondering if it's possible to do something like that and thought it might be an interesting topic for discussion.
Thanks,
Pete
View 2 Replies
View Related
Jul 31, 2002
Hello ,
I want to drop a table from a publication , so that i can copy some data from another server . After the copy , i want to add the article or the table back again to the publication without making any changes to the subscribers configuration .
Is it really possible on SQL 7.0 ? Right now it does not allow me to copy the data to the published table or even drop the article (table) and it says that the article is published for replication and cannot be dropped or modified .
The table is configured for transactional replication .
I will have to drop the entire publication and create all the subcribers again .
Any help in this regard please .
Sameer
View 2 Replies
View Related
Feb 1, 2001
I've learned to create DTS packages, saving them in a SQL database. But how can one modify a saved DTS package? And how does one delete a saved DTS package?
thanks,
Boog
View 2 Replies
View Related
Jan 3, 2000
I have a trigger that emails me each time an order is modified by a manager. I can't recall how to find out which user is performing the modification. Is there a global variable such as @@username?
Thanks,
-Darin.
View 2 Replies
View Related
Dec 19, 2002
I have some rather large TSQL scripts I'd like to schedule as jobs. Unfortunately, the SysJobSteps table in the MSDB Database is limited to 3200 characters while I need it to be 5000. Does anyone know if it's possible to increase the size of this to allow for larger TSQL scripts?
Thanks!
View 3 Replies
View Related
