|
Have you thought about using the openrowset functionality of SQL Server? and letting SQL check to see if the values in the spreadsheet are in the database? rather than Excel doing the work!
MSDN: Openrowset[^]
Lobster Thermidor aux crevettes with a Mornay sauce, served in a Provençale manner with shallots and aubergines, garnished with truffle pate, brandy and a fried egg on top and Spam - Monty Python Spam Sketch
|
|
|
|
|
Hi I don't have choice. My clients or users uploads their file in excel and from that excel file, i have to read the data and insert it into the sql server.
suchita
|
|
|
|
|
Hey Guys,
Saying I'm a rookie at databases would be giving me too much credit. That said, I'm working on a project where a manager can create a job for his employees. Each employee is stored in a table tblUsers and each job is stored in a table tblJobs. Now I know I have a few problems with the way my DB is set up. tblUsers has a primary key of UserName and tblJobs has a primary key of Job Descriptions. Each entry in tblJobs has a linked key, UserName from tblUsers is linked to Project lead from tblJobs.
I have two ArrayLists, jobs and copyOfJobs. When a manager signs in, copyOfJobs is passed to what I call ManagerForm. In ManagerForm, the manager can make alterations to copyOfJobs, and once he exits, I want to compare jobs with copyOfJobs. The contents of jobs is pulled from an Access Database, then copied to copyOfJobs. Once ManagerForm is closed, I want to compare jobs to copyOfJobs. Durring the comparison I want to check if things such as project name, project lead, duedate for a particular assignment has changed. If changed, I want it removed from the database and reinserted with the new values. I'm completely stuck! Any advice/comments would be greatly appreciated.
Here is some code to prove I've actually messed around with this before coming to you guys.
public void GetUsers(ArrayList users)
{
object[] userData = new object[4];
OleDbCommand dbCommand = new OleDbCommand("SELECT * FROM tblUsers", this._dbConnection);
try
{
OleDbDataReader dbReader = dbCommand.ExecuteReader();
while (dbReader.Read())
{
User currentUser = new User();
dbReader.GetValues(userData);
currentUser.Name = userData[0].ToString();
currentUser.Password = userData[1].ToString();
string temp = userData[2].ToString();
int rights = Convert.ToInt16(temp);
currentUser.Rights = (User.EN_RIGHTS)rights;
users.Add(currentUser);
}
dbReader.Close();
}
catch (Exception e)
{
String temp = String.Format("DBManager::GetUsers: {0}", e.Message);
MessageBox.Show(temp);
}
}
pretty simple, probably not the best way.
and here is the method stub that I'm working on so far
public void UpdateJobs(ArrayList copyOfJobs, ArrayList originalJobs)
{
ArrayList copy = new ArrayList();
ArrayList original = new ArrayList();
copy = copyOfJobs;
original = originalJobs;
string temp = "";
if (!copy.Equals(original))
{
temp = string.Format("");
for (int i = 0; i < copyOfJobs.Count; i++)
{
}
}
}
[Insert Witty Sig Here]
|
|
|
|
|
Okay, so that's just a lot of text, and the question seems to be drowning in it. That's why nobody is answering I guess.
So I will answer what I am guessing is the core question here:
The basic idea is to get the list with sql, compare stuff and change values with C# and finally update these values with sql.
So first of all you'll want to use an UPDATE query instead of removing and reinserting each row.
OleDbCommand dbCommand = new OleDbCommand("SELECT * FROM tblUsers", this._dbConnection);
OleDbCommand dbUpdateCommand = new OleDbCommand("UPDATE tblUsers " _
"SET FieldOne = @ParameterOne " _
", FieldTwo = @ParameterTwo " _
"WHERE KeyField = @ParameterThree ", this._dbConnection);
dbUpdateCommand.Parameters.Add("@ParameterOne", _
SqlDbType.NVarChar).Value = SomeVariableHoldingTheValueInQuestion;
dbUpdateCommand.ExecuteNonQuery;
N.B. You may have to use a second connection for the update (I don't remember for sure)...
N.B. I used the syntax for MS SQL. You may have to adapt it to MS Access syntax, as it often uses its own slightly different version. You can check this by building a simple update query with the MS Access Query wizard / query builder.
A coding tip: try to avoid using index numbers for database fields in your code:
currentUser.Name = userData["UserName"].ToString();
is better than
currentUser.Name = userData[0].ToString();
because otherwise when you make a change in your database table or in the query, that will change the order of the fields, and then your code will run amok. Or you will be forced to edit your code all the time, and figure out which field is which index number over and over.
My advice is free, and you may get what you paid for.
|
|
|
|
|
Dear all,
My hosting server is using SQL server 2008 and it is using replication. I have copied the databases from hosting to restore in my laptop then i see all tables are attached with "rowguid" column, i want to remove it from all tables but it did not allow me to remove at all. How to resolve this problem???
please see the error message below:
========================================================
TITLE: Microsoft SQL Server Management Studio
------------------------------
Drop failed for Column 'rowguid'. (Microsoft.SqlServer.Smo)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600.1+((KJ_RTM).100402-1540+)&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Drop+Column&LinkId=20476
------------------------------
ADDITIONAL INFORMATION:
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
------------------------------
It is invalid to drop the default constraint on the rowguid column that is used by merge replication.
The schema change failed during execution of an internal replication procedure. For corrective action, see the other error messages that accompany this error message.
The transaction ended in the trigger. The batch has been aborted. (Microsoft SQL Server, Error: 25005)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=10.50.1600&EvtSrc=MSSQLServer&EvtID=25005&LinkId=20476
------------------------------
BUTTONS:
OK
------------------------------
=================================================
I am looking forward to hear from you.
regards,
Han
|
|
|
|
|
I just found a solution it helped me a lot now. To remove all ""rowguid" column from all table in SQL server which used replication you just run this script "exec sp_removedbreplication" in sql query and then all rowguid column will be deleted.
thanks,
Han
|
|
|
|
|
Hi All,
I am using Table value parameter for bulk insert in my database. So it will be rolling back all the changes even if atleast one row fails. Is there any way that i can find out which row is failed? i hope u are able to understand my question. Can any one help me on this?
Thanks
Lijo
|
|
|
|
|
Hi,
I'm trying to setup a SSIS Package to migrate data between two databases.
There are many tables to migrate, and there are some tables that I cannot/do not want to migrate (i.e. I create the records by hand in the destination database - there are not a lot of records in these particular tables).
An example of the tables I do not want to migrate is the 'Users' table. The primary key of this table is of type GUID. It is used in almost every other table, that I have to migrate, as foreign key. So I have to setup a translation of this GUID.
Of course, for one particular table/data flow I can do the translation with a script component ; but then I have to rewrite the same long code for every other table/data flow.
So I wondered if there could be a way of defining a global transformation method that I could call in each of my script component ; something that would look as below :
public static Guid GetUserGuid(Guid sourceGuid)
{
if (sourceGuid == new Guid("589CD136-4AED-4E3E-A391-DDAE8A790D1B"))
return new Guid("4D0947C1-5746-4DD6-AAFE-5ECB5BACBB9F");
else if (sourceGuid == new Guid("286BCF1A-8CDD-DE11-9EA4-000C29FC4D89"))
return new Guid("05F0CAD9-4095-E011-8ED0-00155D785E00");
}
But I did not find the way to achieve this. I've been searching for three hours without finding some relevant help.
I also thought of setting up a specific database that could provide the matching ; for example :
CREATE TABLE [Users](
[SourceId] [uniqueidentifier] ROWGUIDCOL NOT NULL,
[DestinationId] [uniqueidentifier] NOT NULL
)
But then again I did not find a way to use this matching-table in my transformation process.
Does anyone have some clues about my problem and where I should search for some hints about it ?
|
|
|
|
|
if I use the Seek method with multiple criteria, does Seek use any of the fields to match or all fields must match?
I have an ADODB Recordset. My syntax follows:
Dim cnn As ADODB.Connection
Dim rst As ADODB.RecordSet
'set connection (assume correct)
Set cnn = CurrentProject.Connection
Set rst = New ADODB.Recordset
rst.Open "MyTable", cnn, adOpenKeyset, adLockOptimistic, adCmdTableDirect
'rst.index = "PrimaryKey"
'check if recordset valid - if rst.RecordCount >0
rst.Seek fld_cmpr1 & fld_cmpr2 & fld_cmpr3, adSeekFirstEQ
Does this mean ALL 3 fields must match otherwise Seek result in rst.EOF??
(likewise, if this were DAO and i used rstDAO.Seek "=", fld_cmpr1..... do ALL fields must match?)
Thanks!
JJM
|
|
|
|
|
I think it means that it should concatenate the fields, and search for that.
If you need to specify a value for more than one field, use the VBA Array function to pass those values to the KeyValues argument of the Seek method. If you only need to specify one value, it is not necessary to use the Array function.
Bastard Programmer from Hell
|
|
|
|
|
how to moving column in sql ?
by code ?
|
|
|
|
|
Which database?
MS SQL server from 2005 on - can't be done anymore. They took the ability out.
Other databases...don't know...but a Google for ALTER COLUMN ORDER might bring some up.
|
|
|
|
|
What do you mean "move"? Do you mean move it from one table to another? Or change the order in which the column appears in a query result set? Or change the order in which the column is defined in the table?
|
|
|
|
|
Hi
Thank you for the answer
In your table, not the other table
I mean, is the priority fields
|
|
|
|
|
|
What this command do?
EXEC sp_rename @objname = ‘test.col3_new’, @newname = ‘col3′, @objtype = ‘COLUMN’
Whether the priorities are changed?
This will only change the name?
But I want to change priority
Thanks!
|
|
|
|
|
What do you mean by change priority? Do you mean you want to affect how the result is ordered (in other words, how it is sorted)?
|
|
|
|
|
I have the following tables (I left out fields that don't apply to this question).
Employees, Stores, and Groups. Each employee can belong to one or more Stores, and each Store is in one Group
Employees
EmployeeId Number
Certified DateTime
EmployeeStores
EmployeeStoreId Number
EmployeeId Number
StoreId Number
Stores
StoreId Number
GroupId Number
I need to produce a query that displays the percentage of the Certified field completed in the Employees table by Group. So, assuming Group A, Group B, and Group C, the result set should have:
Group A 13
Group B 42
Group C 21
Other 24
Since I'm very new to Access, I could use some help forming this query.
Thank you
Everything makes sense in someone's mind
|
|
|
|
|
Anyone have any ideas why this won't work? I just want to check whether some fields are not null before I set the value of @UserName
IF EXISTS (SELECT * FROM dbo.sysobjects WHERE id = Object_ID('[dbo].[__Reporting_GetUserName]')
AND type in (N'FN', N'IF', N'TF', N'FS', N'FT'))
BEGIN
DROP FUNCTION [dbo].[__Reporting_GetUserName]
END
BEGIN
EXEC dbo.sp_executesql N'
CREATE FUNCTION [dbo].[__Reporting_GetUserName] (@userId int)
RETURNS VARCHAR(MAX)
AS
BEGIN
DECLARE @UserName VARCHAR(MAX)
SET @UserName =
(
IF [FirstName] is not null
BEGIN
SELECT [FirstName] + '' '' + [LastName]
FROM __User
WHERE Id = @userId
END
)
RETURN @UserName
END
'
END
|
|
|
|
|
Why not use a CASE?
SELECT CASE WHEN [FirstName] is not null THEN [FirstName] + '' '' + [LastName] ELSE null END FROM __User WHERE Id = @userId
(Or something.)
|
|
|
|
|
Etienne_123 wrote: Anyone have any ideas why this won't work?
What would it fill the variable with if FirstName does equall null? You'd better assign it directly without the IF statement, and use the <a href="http://msdn.microsoft.com/en-us/library/ms184325.aspx">ISNULL</a>[<a href="http://msdn.microsoft.com/en-us/library/ms184325.aspx" target="_blank" title="New Window">^</a>] function.
Bastard Programmer from Hell
|
|
|
|
|
I am working on a simple game where you take creatures into battle and they fight against each other. Everything is working fine minus figuring out the best way to store the results.
I want something similar to this data structure.
int ID (identity)
int AttackerID
int DefenderID
int AttackerCreatureID1
int AttackerCreatureID2
int AttackerCreatureID3
int DefenderCreatureID1
int DefenderCreatureID2
int DefenderCreatureID3
and then more for the creatures that died, ect
The problem is, I want the characters to be able to buy more battle slots and bring more into battle at a time. So, say they know can bring 5 creatures in, I don't want to have to make a bunch of new columns. Using XML this exercise would be pretty easy since you can just add a couple extra tags here and there whenever you want.
Like:
<AttackerCreatures>
<Creature>
<ID>12</ID>
</Creature>
<Creature>
<ID>12</ID>
</Creature>
<Creature>
<ID>12</ID>
</Creature>
</AttackerCreatures>
I've done lots of work with SQL before but never come across doing something like this with variable amounts of data column wise...
Any help would be awesome! Thanks ahead of time.
The best way to accelerate a Macintosh is at 9.8m/sec² - Marcus Dolengo
|
|
|
|
|
You could try this:
Procedure with following params
int ID (identity)
int AttackerID
int DefenderID
NVARCHAR AttackerCreatureIDs (this would be comma separated ids)
NVARCHAR DefenderCreatureIDs (this would be comma separated ids)
then in your proc, split the Ids string
the below function could be used
CREATE FUNCTION [dbo].[Split]
(
@List nvarchar(2000),
@SplitOn nvarchar(5)
)
RETURNS @RtnValue table
(
Id int identity(1,1),
Value nvarchar(100)
)
AS
BEGIN
While (Charindex(@SplitOn,@List)>0)
Begin
Insert Into @RtnValue (value)
Select
Value = ltrim(rtrim(Substring(@List,1,Charindex(@SplitOn,@List)-1)))
Set @List = Substring(@List,Charindex(@SplitOn,@List)+len(@SplitOn),len(@List))
End
Insert Into @RtnValue (Value)
Select Value = ltrim(rtrim(@List))
Return
END
GO
|
|
|
|
|
I thought about doing that but it feels dirty, doesn't feel very relational databaseish...
The best way to accelerate a Macintosh is at 9.8m/sec² - Marcus Dolengo
|
|
|
|
|
It looks to me as if you've got a many to many relationship there. A battle joins many attackers to many defenders. So you would have a table Battles which has a unique BattleId, plus AttackerId and DefenderId plus whatever other information you want to hold against a battle (date, location, whatever).
Then you have a number of options. One option would be to have an Attackers table which holds BattleId and CreatureId for all the attackers, and another table Defenders which holds BattleId and CreatureId for all the defenders. Or another option would be to have just one table BattleCreatures which holds BattleId, ArmyId and CreatureId where ArmyId is either the AttackerId or the DefenderId depending which side the creature is on.
Whether you separate out Attackers from Defenders into two separate tables or whether you have them all together in one table is a bit of a judgement call. There are arguments for and against both options.
|
|
|
|