address management software

Does anyone know of any address management software that is compliant with sql server 2000, i.e. for record duplication and address cleansing etc...
Thanks!any suggestions appreciated!sql

AddNew then getting Unique ID

I have an auto-incremental field in my sql database table. After I add a
new record I need to get that ID. My below code adds the record with no
problems but the ID field I request always comes back empty. If I look in
the table the new record is there with the auto ID field.
hr = pConnection->Open(strCnn,"","",adConnectUnspecified);
hr= pRstPubInfo.CreateInstance(__uuidof(Recordset));
hr = pRstPubInfo->Open("messages",
_variant_t((IDispatch*)pConnection,true)
,
adOpenKeyset,adLockOptimistic,adCmdTable
);
pRstPubInfo->AddNew();
hr = pRstPubInfo->Fields->GetItem("message")->AppendChunk(varChunk);
hr = pRstPubInfo->Update();
_variant_t DBID = pRstPubInfo->Fields->Item["id"]->GetValue();
//DBID = EMPTY.Use a stored procedure, not add new. Optimistic recordsets and ad hoc SQL
are not optimal for performing inserts!
Anyway, then you could do this in one transaction and retrieve the output
variable:
CREATE PROCEDURE dbo.AddRow
@.value VARCHAR(32),
@.idOut INT OUTPUT
AS
BEGIN
SET NOCOUNT ON;
INSERT table(column) SELECT @.value;
SELECT @.idOut = SCOPE_IDENTITY();
END
GO
(Alternatively, you could use a scalar/resultset or, since it is an INT, you
could buck standard practice and use the return value.)
A
"Bob" <msgdev@.hotmail.com> wrote in message
news:%237C%23PnI4FHA.3000@.TK2MSFTNGP12.phx.gbl...
>I have an auto-incremental field in my sql database table. After I add a
>new record I need to get that ID. My below code adds the record with no
>problems but the ID field I request always comes back empty. If I look in
>the table the new record is there with the auto ID field.
>
> hr = pConnection->Open(strCnn,"","",adConnectUnspecified);
> hr= pRstPubInfo.CreateInstance(__uuidof(Recordset));
> hr = pRstPubInfo->Open("messages",
> _variant_t((IDispatch*)pConnection,true)
,
> adOpenKeyset,adLockOptimistic,adCmdTable
);
> pRstPubInfo->AddNew();
> hr = pRstPubInfo->Fields->GetItem("message")->AppendChunk(varChunk);
> hr = pRstPubInfo->Update();
> _variant_t DBID = pRstPubInfo->Fields->Item["id"]->GetValue();
> //DBID = EMPTY.
>|||I am adding a binary object to the database. It could be very large so I
thought using AddChunk would be better. Is there a way to add binary data
using stored procedures? Is there a way to add chunks? May be I am doing
this all wrong. Any help would be appreciated.
"Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in message
news:OwRUM4I4FHA.636@.TK2MSFTNGP10.phx.gbl...
> Use a stored procedure, not add new. Optimistic recordsets and ad hoc SQL
> are not optimal for performing inserts!
> Anyway, then you could do this in one transaction and retrieve the output
> variable:
> CREATE PROCEDURE dbo.AddRow
> @.value VARCHAR(32),
> @.idOut INT OUTPUT
> AS
> BEGIN
> SET NOCOUNT ON;
> INSERT table(column) SELECT @.value;
> SELECT @.idOut = SCOPE_IDENTITY();
> END
> GO
> (Alternatively, you could use a scalar/resultset or, since it is an INT,
> you could buck standard practice and use the return value.)
> A
>
> "Bob" <msgdev@.hotmail.com> wrote in message
> news:%237C%23PnI4FHA.3000@.TK2MSFTNGP12.phx.gbl...
>|||AppendChunk can be used for Parameter objects as well as Field objects.
Bob wrote:
> I am adding a binary object to the database. It could be very large
> so I thought using AddChunk would be better. Is there a way to add
> binary data using stored procedures? Is there a way to add chunks?
> May be I am doing this all wrong. Any help would be appreciated.
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in
> message news:OwRUM4I4FHA.636@.TK2MSFTNGP10.phx.gbl...
Microsoft MVP -- ASP/ASP.NET
Please reply to the newsgroup. The email account listed in my From
header is my spam trap, so I don't check it very often. You will get a
quicker response by posting to the newsgroup.|||CREATE PROCEDURE sp_InsertBLOB
@.ID int = NULL OUT,
@.BLOB image = NULL
AS
SET NOCOUNT ON
INSERT INTO BLOBTable ( BLOB ) VALUES( @.BLOB )
SELECT @.ID = SCOPE_IDENTITY()
END
GO
The strange thing to me is that you are worried about the efficiency of
sending a "large binary object" to the server, but you are willing to pull
down an entire table full of them just to perform an insert?
John
"Bob" wrote:

> I am adding a binary object to the database. It could be very large so I
> thought using AddChunk would be better. Is there a way to add binary data
> using stored procedures? Is there a way to add chunks? May be I am doing
> this all wrong. Any help would be appreciated.
>
> "Aaron Bertrand [SQL Server MVP]" <ten.xoc@.dnartreb.noraa> wrote in messag
e
> news:OwRUM4I4FHA.636@.TK2MSFTNGP10.phx.gbl...
>
>

adding unique keys

Would anyone please instruct how to prevent the duplicate record by
setting the unique keys on the ms sql server? i've been checking the
duplicate record as front-end and i found out if there is an internet
delay or some other reasons, it has a chance to store the duplicated
data into the database. so i realized it has to be done on the back-end
side.
for example, if i have three columns (office code, office id, office
section) as a unique key, how can i setup this? thanks in advance.create a primary key or a unique constraint

ALTER TABLE [dbo].[YourTable] WITH NOCHECK ADD
CONSTRAINT [YourTable_PK] PRIMARY KEY CLUSTERED
(
[office code],
[office id],
[office section]
) WITH FILLFACTOR = 90 ON [PRIMARY]
GO

or

ALTER TABLE dbo.YourTable ADD CONSTRAINT
IX_YourTable UNIQUE NONCLUSTERED
(
[office code],
[office id],
[office section]

) ON [PRIMARY]

Denis the SQL Menace
http://sqlservercode.blogspot.com/
HandersonVA wrote:

Quote:

Originally Posted by

Would anyone please instruct how to prevent the duplicate record by
setting the unique keys on the ms sql server? i've been checking the
duplicate record as front-end and i found out if there is an internet
delay or some other reasons, it has a chance to store the duplicated
data into the database. so i realized it has to be done on the back-end
side.
for example, if i have three columns (office code, office id, office
section) as a unique key, how can i setup this? thanks in advance.

Adding Records to SQL DB

Here's my dilema. I'm using VB.Net 2003 and SQL DB

I'm having a problen adding a record to a table in my SQL DB

My previous experience has been working with Access databases.

When I try to create an SQLDataAdapter using the wizard from the toolbox the wizard will only create the Select statement. it won't create the UPDATE, DELETE or INSERT statements. I'm not sure why, I even tried using the form wizard and it still wouldn't work...

My tables all have primary keys defined.

So I decided that I would try to create everything programatically.

What I need to know is how to create the INSERT statement and which statements to use to add the record to the table.

I know that when using the OLEDataAdapter with the Acces DB I would do something like the following code.

MyNewRow = MyDatSet.Tables("MyTable").NewRow

MyDataSet.Tables("MyTable").Rows.Add(MyNewRow)
MyDataAdapter.Update(MyDataSet, "MyTable")

I'm pretty sure that for an SQL DB it is different but I don't know how to do it.

Can anyone please help ?

I should also mention that all my textbox fields on my form are databound..

thanks

tattoo

If you define no command on the dataadapter, then no command will be executed. Have a look at the commandbuilder. It will create commands for the apropiate DELETE / INSERT / UPDATE statements based on the underlying schema. If you don′t want to use the (auto)generated command, you can also assign these commands manully. But if you do not give the dataadater any information on how to proceed on the DML action, no action will be done during the udate.

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Adding New Measure to OLAP Cube

To add a record count measure to the olap cube. Create the cube as
usual then run the following VB code with command line parameters
Step1: Build your cube as usual
Step2: Convert the blow vb code to exe prog
Step3: Run the exe with the <Ananlysis server name> <cube name>
parameters (e.g) OLAPcount.exe <Analysis Server> <Cube Name>
Public Sub main()
Dim dsoServer As New DSO.Server
Dim dsoDB As DSO.MDStore
Dim dsoCube As DSO.MDStore
Dim dsoMea As DSO.Measure
Dim dsoAssFactCube As DSO.Cube
Dim dsoPortAnalyzerCube As DSO.Cube
'for storing initial command line arguments as entered by user
Dim strArgs() As String
'for storing the parsed command line arguments
Dim ParsedArgs As String
'for storing the final array of command line arguments
Dim finalArgs() As String
'Splitting the command line arguments based on a space
strArgs = Split(Command$, " ")
'Parsing the command line arguments to generate the parsed string
For i = 0 To UBound(strArgs)
If Len(Trim(strArgs(i))) > 0 Then
ParsedArgs = ParsedArgs & Trim(strArgs(i)) & " "
End If
Next
'Splitting the parsed string into final array of arguments
finalArgs = Split(ParsedArgs, " ")
'Check for correct number of arguments
If UBound(finalArgs) < 2 Then
MsgBox ("Wrong Syntax..." & "or wrong number of
arguments....Correcet Syntax : OLAPcount.exe <Analysis Server> <Cube
Name> (e.g)OLAPcount.exe livdwqprj03 AIGTMSReport1")
Else
'connect to the server (Analysis Server name)
dsoServer.Connect (finalArgs(0))
'Examine whether all necessary components are present (Cube
name)
If dsoServer.MDStores.Find(finalArgs(1)) = False Then
GoTo err_no_database
End If
'Connect with the data base (CUBE) (Cube name)
Set dsoDB = dsoServer.MDStores(finalArgs(1))
If dsoDB.DataSources.Count = 0 Then
GoTo err_no_datasource
ElseIf dsoDB.Dimensions.Count = 0 Then
GoTo err_no_dimensions
ElseIf dsoDB.MDStores.Find("MSP_ASSN_FACT") = False Then
GoTo err_no_fact_cube
ElseIf dsoDB.MDStores.Find("MSP_PORTFOLIO_ANALYZER") = False
Then
GoTo err_no_analyzer
End If
'Set the cube table to use
Set dsoAssFactCube = dsoDB.MDStores("MSP_ASSN_FACT")
Set dsoPortAnalyzerCube =
dsoDB.MDStores("MSP_PORTFOLIO_ANALYZER")
'Specify the name of the new measure
Set dsoMea = dsoAssFactCube.Measures.AddNew("Total
Assignments")
'Specify the source column based on which the operation need to
be performed
'dsoMea.SourceColumn =
"""MSP_CUBE_ASSN_FACT"".""ENT_ASSIGNMENT_CODE6"""
dsoMea.SourceColumn = """MSP_CUBE_ASSN_FACT"".""PROJ_UID"""
'The datatype for the column
dsoMea.SourceColumnType = ADODB.DataTypeEnum.adDecimal
'The method for the column aggSum or aggCount aggregates the
column by summation or counts.
dsoMea.AggregateFunction = aggCount
'update the cube
dsoAssFactCube.Update
dsoAssFactCube.Process
'dsoAnalyzerCube represents a virtual Cube. the measure of a
virtual Cubes has
'the characteristics of the measure of the material cubes
Set dsoMea = dsoPortAnalyzerCube.Measures.AddNew("Total
Assignments")
'The column is indicated in "more normal" form, since the
measure belongs to the virtual Cube!
'dsoMea.SourceColumn = "MSP_ASSN_FACT.FIXED COST"
dsoMea.SourceColumn = "MSP_ASSN_FACT.Total Assignments"
dsoPortAnalyzerCube.Update
dsoPortAnalyzerCube.Process
dsoDB.Process
leave_now:
UserOLAPUpdate = 0
' Exit Function
err_no_database:
l_errnum = 1
s_errdesc = "Datenbank konnte nicht gefunden werden!"
UserOLAPUpdate = vbObjectError + 1
' Exit Function
err_no_datasource:
l_errnum = 1
s_errdesc = "Datenquelle konnte nicht gefunden werden!"
UserOLAPUpdate = vbObjectError + 2
' Exit Function
err_no_dimensions:
l_errnum = 1
s_errdesc = "Dimensionen konnten nicht gefunden werden!"
UserOLAPUpdate = vbObjectError + 3
' Exit Function
err_no_fact_cube:
l_errnum = 1
s_errdesc = "Cube MSP_ASSN_FACT konnte nicht gefunden werden!"
UserOLAPUpdate = vbObjectError + 4
' Exit Function
err_no_analyzer:
l_errnum = 1
s_errdesc = "Cube MSP_PORTFOLIO_ANALYZER konnte nicht gefunden
werden! "
UserOLAPUpdate = vbObjectError + 5
' Exit Function
error_handler:
l_errnum = Err.Number
s_errdesc = Err.Description
UserOLAPUpdate = 1 ' although it could be any non-zero value
' to indicate an error
End If
End SubPerhaps you should head for the ng
http://www.microsoft.com/communitie...sqlserver.olap
HTH, Jens Suessmeyer.
http://www.sqlserver2005.de
--
"wilsonjust@.gmail.com" wrote:

> To add a record count measure to the olap cube. Create the cube as
> usual then run the following VB code with command line parameters
> Step1: Build your cube as usual
> Step2: Convert the blow vb code to exe prog
> Step3: Run the exe with the <Ananlysis server name> <cube name>
> parameters (e.g) OLAPcount.exe <Analysis Server> <Cube Name>
> Public Sub main()
> Dim dsoServer As New DSO.Server
> Dim dsoDB As DSO.MDStore
> Dim dsoCube As DSO.MDStore
> Dim dsoMea As DSO.Measure
> Dim dsoAssFactCube As DSO.Cube
> Dim dsoPortAnalyzerCube As DSO.Cube
> 'for storing initial command line arguments as entered by user
> Dim strArgs() As String
> 'for storing the parsed command line arguments
> Dim ParsedArgs As String
> 'for storing the final array of command line arguments
> Dim finalArgs() As String
> 'Splitting the command line arguments based on a space
> strArgs = Split(Command$, " ")
> 'Parsing the command line arguments to generate the parsed string
> For i = 0 To UBound(strArgs)
> If Len(Trim(strArgs(i))) > 0 Then
> ParsedArgs = ParsedArgs & Trim(strArgs(i)) & " "
> End If
> Next
> 'Splitting the parsed string into final array of arguments
> finalArgs = Split(ParsedArgs, " ")
> 'Check for correct number of arguments
> If UBound(finalArgs) < 2 Then
> MsgBox ("Wrong Syntax..." & "or wrong number of
> arguments....Correcet Syntax : OLAPcount.exe <Analysis Server> <Cube
> Name> (e.g)OLAPcount.exe livdwqprj03 AIGTMSReport1")
> Else
> 'connect to the server (Analysis Server name)
> dsoServer.Connect (finalArgs(0))
> 'Examine whether all necessary components are present (Cube
> name)
> If dsoServer.MDStores.Find(finalArgs(1)) = False Then
> GoTo err_no_database
> End If
> 'Connect with the data base (CUBE) (Cube name)
> Set dsoDB = dsoServer.MDStores(finalArgs(1))
> If dsoDB.DataSources.Count = 0 Then
> GoTo err_no_datasource
> ElseIf dsoDB.Dimensions.Count = 0 Then
> GoTo err_no_dimensions
> ElseIf dsoDB.MDStores.Find("MSP_ASSN_FACT") = False Then
> GoTo err_no_fact_cube
> ElseIf dsoDB.MDStores.Find("MSP_PORTFOLIO_ANALYZER") = False
> Then
> GoTo err_no_analyzer
> End If
> 'Set the cube table to use
> Set dsoAssFactCube = dsoDB.MDStores("MSP_ASSN_FACT")
> Set dsoPortAnalyzerCube =
> dsoDB.MDStores("MSP_PORTFOLIO_ANALYZER")
> 'Specify the name of the new measure
> Set dsoMea = dsoAssFactCube.Measures.AddNew("Total
> Assignments")
> 'Specify the source column based on which the operation need to
> be performed
> 'dsoMea.SourceColumn =
> """MSP_CUBE_ASSN_FACT"".""ENT_ASSIGNMENT_CODE6"""
> dsoMea.SourceColumn = """MSP_CUBE_ASSN_FACT"".""PROJ_UID"""
> 'The datatype for the column
> dsoMea.SourceColumnType = ADODB.DataTypeEnum.adDecimal
> 'The method for the column aggSum or aggCount aggregates the
> column by summation or counts.
> dsoMea.AggregateFunction = aggCount
> 'update the cube
> dsoAssFactCube.Update
> dsoAssFactCube.Process
> 'dsoAnalyzerCube represents a virtual Cube. the measure of a
> virtual Cubes has
> 'the characteristics of the measure of the material cubes
> Set dsoMea = dsoPortAnalyzerCube.Measures.AddNew("Total
> Assignments")
> 'The column is indicated in "more normal" form, since the
> measure belongs to the virtual Cube!
> 'dsoMea.SourceColumn = "MSP_ASSN_FACT.FIXED COST"
> dsoMea.SourceColumn = "MSP_ASSN_FACT.Total Assignments"
> dsoPortAnalyzerCube.Update
> dsoPortAnalyzerCube.Process
> dsoDB.Process
> leave_now:
> UserOLAPUpdate = 0
> ' Exit Function
> err_no_database:
> l_errnum = 1
> s_errdesc = "Datenbank konnte nicht gefunden werden!"
> UserOLAPUpdate = vbObjectError + 1
> ' Exit Function
> err_no_datasource:
> l_errnum = 1
> s_errdesc = "Datenquelle konnte nicht gefunden werden!"
> UserOLAPUpdate = vbObjectError + 2
> ' Exit Function
> err_no_dimensions:
> l_errnum = 1
> s_errdesc = "Dimensionen konnten nicht gefunden werden!"
> UserOLAPUpdate = vbObjectError + 3
> ' Exit Function
> err_no_fact_cube:
> l_errnum = 1
> s_errdesc = "Cube MSP_ASSN_FACT konnte nicht gefunden werden!"
> UserOLAPUpdate = vbObjectError + 4
> ' Exit Function
> err_no_analyzer:
> l_errnum = 1
> s_errdesc = "Cube MSP_PORTFOLIO_ANALYZER konnte nicht gefunden
> werden! "
> UserOLAPUpdate = vbObjectError + 5
> ' Exit Function
> error_handler:
> l_errnum = Err.Number
> s_errdesc = Err.Description
> UserOLAPUpdate = 1 ' although it could be any non-zero value
> ' to indicate an error
> End If
> End Sub
>

Adding in a query

i want to add vales in a query

this is my scenario (how can i add the price column values and display as one record) ex. 222 17

table = table1

tep1 price (column)

222 5

222 2

222 10

SELECT SUM(price) FROM table1 WHERE tep1=222

Or

SELECT SUM(price) FROM table1 GROUP by tep1

The first will sum for just the 222 record, the second will sum for each unique tep1.

|||

SELECT tep1, SUM(price) AS "Total" FROM table1 GROUP BY tep1

Adding datetime to database?

I have DateCreated with datetime datatype in my SQL Express 2005. I'd like to add the record to my Task table so I have a form in my ASPX and create a button event in my ASPX.CS here is the code

protected void Button_AddTask_Click(object sender, EventArgs e)
{
SqlDataSource newTask = new SqlDataSource();
newTask.ConnectionString = ConfigurationManager.ConnectionStrings["ConnectionString"].ToString();
newTask.InsertCommand = "INSERT INTO [Task] ([MemberAccountName], [Title], [Place], [TaskDetail],[DateCreated]) VALUES (@.MemberAccountName, @.Title, @.Place, @.TaskDetail,@.DateCreated)";
newTask.InsertParameters.Add("MemberAccountName", User.Identity.Name);
newTask.InsertParameters.Add("Title", TextBox_Title.Text);
newTask.InsertParameters.Add("Place", TextBox_Place.Text);
newTask.InsertParameters.Add("TaskDetail", TextBox_Detail.Text);
newTask.InsertParameters.Add("DateCreated",DateTime.Now.ToString());
newTask.Insert();
Response.Redirect("Default.aspx");
}

but I got this error

Arithmetic overflow error converting expression to data type datetime.
The statement has been terminated.

Any idea?
Sorry your guy, I think I found the problem...

In my SQL Express 2005 use the datetime format in DD/MM/YYYY

butDateTime.Now.ToString() giveMM/DD/YYYY

I found it by hardcoding "22/12/2005 00:00:00" and it works!!

So now how can I fix this??
|||

Check the other ways to use the add, there should be one that allows you to specify the parameter type. Tell it that it is a datetime, and pass in datetime.now (NOT datetime.now.tostring).

|||You can avoid date problems by always presenting your date in YYYYMMDD format to SQL Server.

In your original code, try this instead (add a format to the ToString method):

newTask.InsertParameters.Add("DateCreated",DateTime.Now.ToString("yyyyMMdd"));

|||Thank you everyone, now I can make it work

However there is a little problem with my Thai Buddhist year !!

There is 543 year different between Christ year and Buddhist year.

I can get the Datetime.Now show correctly in the page but when inserting into the database the system will add 543 automatically to the year !!! So when I retrieve the data back from the database it get the year 3091 !!

So what I have to do is
.
DateTime dt = DateTime.Now;
.
.
newTask.InsertParameters.Add("DateCreated", dt.AddYears(-543).ToString("yyyyMMdd HH:mm:ss"));
.

It's kind of weird, right??|||See my post 3 posts up.