Re: How to get @@IDENTITY when working offline with Dataset - Database Discussion Boards

Re: The Math.Round() in VS.NET and Round() in SQL Server 2000

Rob Graham16-Jul-05 7:48

16-Jul-05 7:48

SQL and .Net use different rounding algorithms (as you have observed), ao the answer is that you can't. Net's version is the more 'proper' algorithm: it returns the nearest even number when the value is half way between (.25 will round to .2, .35 will round to .4) This yeilds more accurate averages and totals for the rounded data, and so is prefered. It is sometimes called "Banker's rounding". SQL always rounds to the next larger number for the mid value, which leads to totals and averages that are biased to the high side.

I would recommend choosing the rounding behavior that you prefer, and always doing the rounding that way (i.e. either have ONLY SQL do the rounding, or ONLY .Net). I suspect that returning unrounded values from SQL and doing the rounding in .Net would be marginally more performant.

Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
The opposite of the religious fanatic is not the fanatical atheist but the gentle cynic who cares not whether there is a god or not.
Eric Hoffer

sqlce connection problems help

Member 190267915-Jul-05 21:58

Member 1902679

15-Jul-05 21:58

I need help i am trying to creat a program in VB.net for a pda.
i already have the database created using the quary analizer.
but i can not get the connection to work.
Public Sub conn()
Dim conn As New SqlCeConnection
conn.ConnectionString = "Provider=Microsoft.SQLSERVER.OLEDB.CE.2.0; Data Source=\My Documents\abook.sdf;"
end sub
but i keep getting exceptions
i have searched threw several examples but they all start out by creating the database or using XML.
all i want is to retrieve data and manipulate it.
please some one explane what i am doing wrong, and a good example of what i need to do.

Re: sqlce connection problems help

Rob Graham16-Jul-05 7:21

Rob Graham

16-Jul-05 7:21

what is the error being reported in the exception?

And don't you need some kind of authentication in the connection string?

Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
The opposite of the religious fanatic is not the fanatical atheist but the gentle cynic who cares not whether there is a god or not.
Eric Hoffer

ADO.net

boutie15-Jul-05 20:30

boutie

15-Jul-05 20:30

I've made a table in access with a columnfield "searchdate"
Now I want to filter rows between two dates in the columnfield "searchdate" using two datetimepickers. I'm using the (configure)oldedbadapter with the querybuilder with the following SQL-expression :SELECT...FROM...WHERE ([searchdate] >= ?) AND ([searchdate] <= ?)
My code : oledbdataAdapter.selectCommand.parameters("searchdate").value = datetimepicker1.value.date
oledbdataAdapter.selectCommand.parameters("searchdate").value = datetimepicker2.value.date
dataset.clear
oledbdataadapter.fill(dataset)
result: it doesn't work!!!
if I'm using the expression select....from...where ([searchdate] >= ?)and ([searchdate]<= #01/12/2005#)he finds the records beween a date chosen with the datetimpicker1 and the date 01/12/2005.
Can somebody help me how to find rows beween two dates using two datetimepickers bound two one columnfield "searchdate"

Thank you very much for answering to my question.

Boutie

Re: ADO.net

Rob Graham16-Jul-05 7:33

Rob Graham

16-Jul-05 7:33

You are giving both dates the same parameter name, so the result is that you are asking for all the records between the same dates: dateTimePicker2 is 5-9-2004 then the query becomes: select...from...where [searchdate] >= #05/09/2005# and [searchdate] <= #05/09/2005#

Instead of using the field name as a parameter name, use some arbitrary names like p1,p2 so each of the two parameters in your query have different names. add tem to the parameters collection and set their values in the order they will be used in the query (the oledb layer doesn't care about the parameter names, just the order; but the .Net collection object (the parameters collection of the query) wants unique parameter names)

Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
The opposite of the religious fanatic is not the fanatical atheist but the gentle cynic who cares not whether there is a god or not.
Eric Hoffer

How to get @@IDENTITY when working offline with Dataset

fracalifa15-Jul-05 2:17

fracalifa

15-Jul-05 2:17

Hi all,

how can I get the new id after adding a new DatRow when I'm working with an (offline) Dataset. Which sql command is to use for ?
(or is there an other command ?)

I am working with

MissingSchemaAction.AddWithKey

("SELECT @@IDENTITY") is asking the (online) Database and not the Dataset, isn't it ?

thanks

regards

Re: How to get @@IDENTITY when working offline with Dataset

Rob Graham15-Jul-05 4:17

Rob Graham

15-Jul-05 4:17

It's asking the database.

Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
The opposite of the religious fanatic is not the fanatical atheist but the gentle cynic who cares not whether there is a god or not.
Eric Hoffer

Re: How to get @@IDENTITY when working offline with Dataset

fracalifa15-Jul-05 4:24

fracalifa

15-Jul-05 4:24

Thank you for this info,

but what's the way working with offline tables and getting the new id after adding a row?

regards
Frank

Re: How to get @@IDENTITY when working offline with Dataset

Michael Potter15-Jul-05 5:25

Michael Potter

15-Jul-05 5:25

There is no way to get a new identity key when working offline. Only the server knows what its next identity will be.

It is best to rethink your design and use UniqueIdentifiers (GUIDs) in place of your identity keys. They can be developed on the client without clashing with keys on the server.

Re: How to get @@IDENTITY when working offline with Dataset

fracalifa15-Jul-05 8:19

fracalifa

15-Jul-05 8:19

Hi Michael,

that's a good idea, and I will follow that way in the future. But I think I found a way for my problem but I'm not shure if it always works fine.

...
I placed an eventhandler in the data_new routinem like that:

Table.RowChanged += new DataRowChangeEventHandler(Table_RowChanged);

...

private void Table_RowChanged(object sender, DataRowChangeEventArgs e)
{
   if(e.Action.ToString() == "Add")
   {
	Console.WriteLine("index1 = " + e.Row["id"].ToString());    // 
   }
}

// The dataset in the database is locked to prevent changes from other users.

Thank's
Frank

Book recommendations

PJ Arends14-Jul-05 17:41

PJ Arends

14-Jul-05 17:41

I want to learn SQL. So far I have just a basic knowledge and would like to get a book that I can use to extend my knowledge further. I did a quick search and found some titles, but I have no idea if any of them are any good. Has anybody used any of these, or have any other recommendations for an absolute SQL beginner? Thanks Smile | :)

The Programmer's Guide to SQL[^]

Beginning SQL[^]

SQL: A Beginner's Guide Second Edition[^]

Introduction to SQL: Mastering the Structured Query Language[^]

SQL in a Nutshell[^]

SQL Fundamentals[^]

A Visual Introduction to SQL[^]

"You're obviously a superstar." - Christian Graus about me - 12 Feb '03

"Obviously ??? You're definitely a superstar!!!" - mYkel - 21 Jun '04

"There's not enough blatant self-congratulatory backslapping in the world today..." - HumblePie - 21 Jun '05

Within you lies the power for good - Use it!

Re: Book recommendations

Christian Graus14-Jul-05 17:52

Christian Graus

14-Jul-05 17:52

For an absolute beginner, the books online that come with SQL Server are pretty good, actually. However, I have The programmers guide to SQL and found it very helpful. Then I bought the guru's[^] guides, although I'm far from a guru, I at least have a good source of info when I need to pretend to be....

Christian Graus - Microsoft MVP - C++

Re: Book recommendations

PJ Arends14-Jul-05 21:39

PJ Arends

14-Jul-05 21:39

Christian Graus wrote:
the books online that come with SQL Server are pretty good

I do not have SQL Server. I have played with MySQL, MSDE, Access, and SQLite, but I want to get beyond the basic playing and learn how to actually do something. Thanks for the link to the guru's books, looks like they would also make an interesting addition to my book shelf.

As an aside, the link you gave me to amazon.com lists it's prices in US dollars does it not? The price they are quoting for the box set is $98.86. I looked up the same box set on chapters.ca and they are quoting $97.99 CDN. With the exchange what it is I would save 20% by buying the set from chapters instead of from amazon. Not only that, but chapters gives free shipping. Smile | :)

Re: Book recommendations

Rob Graham15-Jul-05 4:21

Rob Graham

15-Jul-05 4:21

MSDE is Sql server. SQL Books On line can be downloaded here.[^]

Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
The opposite of the religious fanatic is not the fanatical atheist but the gentle cynic who cares not whether there is a god or not.
Eric Hoffer

Re: Book recommendations

PJ Arends15-Jul-05 6:20

PJ Arends

15-Jul-05 6:20

Thanks for the link Rob, I am downloading right now Smile | :)

Database redesign

Mincy George14-Jul-05 4:53

Mincy George

14-Jul-05 4:53

I have an sql table with many varchar columns and the row size exceeds 8000 max.
If I redesign the table to make the lengthy columns to text data type, what are the implications on the data?
any other issues?
TIA.

Re: Database redesign

Michael Potter15-Jul-05 5:31

Michael Potter

15-Jul-05 5:31

If the rows that are changed to text are rarely used - you may see a speed increase. If they are heavily used, then you may see a speed decrease. Client code won't need to change that much. There are more restrictions in Stored Procs on the manipulation of Text Fields.

Text fields are stored in a seperate place from rest of the table data. Therefore, it takes a minimum of 2 IOs for the SQL Server build a query result that contains a text field.

Re: Database redesign

Rob Graham16-Jul-05 7:55

Rob Graham

16-Jul-05 7:55

A better approach would be to take a hard look at the table design, and see if you can split the table into several smaller ones.
Text (or NText) columns can't be used in 'Like' queries (you would have to use ful text search) and they will introduce a performance hit, since the data is stored in a binary tree rather than in the table (the table will only contain a pointer to the root of the tree). this results in at least one additional disk IO for each field returned (possibly more if the field was large, and had to span several allocation nodes on the storage tree).

Certainly you have more data per row than can easily be displayed, so I would think multiple tables with a common foreign key would work.

Absolute faith corrupts as absolutely as absolute power
Eric Hoffer
The opposite of the religious fanatic is not the fanatical atheist but the gentle cynic who cares not whether there is a god or not.
Eric Hoffer

Re: Database redesign

Anonymous18-Jul-05 6:20

Anonymous

18-Jul-05 6:20

Thanks all.
I agree to the idea of spliting the table with foreign Key relations
than changing to text fields.
thanks again.

Import data to SQL server

cberam14-Jul-05 1:06

cberam

14-Jul-05 1:06

Hi,

I have a foxpro dbf file and I want to copy the data from mdf to sql server,
how can I ? I have tried through the sql server Import wizard but failed to
do this....Pls help

Thanks
Ram

Re: Import data to SQL server

phokojoe14-Jul-05 2:35

phokojoe

14-Jul-05 2:35

Try the Data Transformation Services import/Export wizard of the sql server and choose the correct data source.

Does it work! Smile | :)

phokojoe

Re: Import data to SQL server

Vasudevan Deepak Kumar15-Jul-05 3:03

Vasudevan Deepak Kumar

15-Jul-05 3:03

Hi Ram:

By 'Import Wizard', did you mean DTSWiz (Data Transformation Services Wizard) with SQLServer.

What is the error that you are encountering along?

Deepak Kumar Vasudevan
Personal Web: http://vdeepakkumar.netfirms.com/
I Blog At: http://deepak.blogdrive.com/

Change SQL Server startup account to SA

Toms Edison14-Jul-05 0:40

Toms Edison

14-Jul-05 0:40

Hi,
Anyone got source code to change SQl Server startup account to SA.
I need this to be run in my installation.

VB script, java script or installscript will be very helpful.

Thanks
Toms

Re: Change SQL Server startup account to SA

Colin Angus Mackay14-Jul-05 2:35

Colin Angus Mackay

14-Jul-05 2:35

Toms Edison wrote:
change SQl Server startup account to SA

This doesn't make sense. When you make a connection to SQL Server you specify the account that you want to use. There is no "startup account".

My: Blog | Photos
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More

<help me>Access

vtalau13-Jul-05 23:43

vtalau

13-Jul-05 23:43

Help me
I have a trouble again with M.Access. I have a combo-box on a form, and this combo-box has the responsibility on showing the Query which creates the 4-column table, including Company,Name,Address and ClientID.

My purpose is that I want to show this Query in the combo-box in the order A-Z, before the blank company items.
My solution:
I create a query like below:
select .........
from nhanvien
where company is null
union
select .........
from nhanvien
where company is not null
However, the result isn't right like my hope. I think that the reason is because of the order of the ClientID which is the primary key...As a result, the destination query automatically sorts on the ClientID order.
Thinking of the solution :
I don't know how to do to archieve that result. I think so much about the SQL sentence of the above query. I believe that there's another SQL sentence helping me to solve this problem. I'm very happy whether you give me a SQL sentence to do that or another solution to obtain the same result ....

Thanks so much <vtalau>

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.