Database

Re: SQL database batch files

7-May-06 15:56

No problem. It's easy to overlook simple things in one's own code (I do it more often than I'd care to admit). That is one of the great values that comes from having others review your code (particularly when having a problem) - they will often see things you are temporarily blind to.

We need to graduate from the ridiculous notion that greed is some kind of elixir for capitalism - it's the downfall of capitalism. Self-interest, maybe, but self-interest run amok does not serve anyone. The core value of conscious capitalism is enlightened self-interest.
Patricia Aburdene

SQL database batch files

abdelhameed816-May-06 9:43

abdelhameed81

6-May-06 9:43

hi all i would like to know how to create a batch file that carries the schema and the data of database and run it on any pc to add the database with all the data in it in the SQL sever
i have seen such files but the idea of how to create one
thank u all

abdelhameed81

Rob Graham7-May-06 5:36

Deleting from an SQL database

7-May-06 5:36

You can use SQL Enterprise Manager to script the database: select the tree node for the database you want to script, right click. Select 'All Tasks' then 'Generate SQL Scripts'
This will callup a dialog which will allow you to select which schema objects you want in the script, and what format you want for the script file (warning: the default is Unicode Text, change it if you want pain text).

To script the data, you must either write your own tool to "export" the data as text (INSERT Statements) or use the bulk copy tool (bcp) to export and import the data.

A simple and faster method of creating a copy of yur database (complete with data) is simply detach the database for the development server, copy the log and data files to the new server, and attach to the new server, specifing new locactions for the files if necessary. (you can include the data and log files in your installer, and run a script to attach them, if need be.)

We need to graduate from the ridiculous notion that greed is some kind of elixir for capitalism - it's the downfall of capitalism. Self-interest, maybe, but self-interest run amok does not serve anyone. The core value of conscious capitalism is enlightened self-interest.
Patricia Aburdene

alee15.10.886-May-06 1:52

alee15.10.88

6-May-06 1:52

Hi all,

I am doing a program with c# in which I am connecting it to an SQL database.
I have a button (called btnDelete) in which in it I have the following code:-

string query = ("DELETE FROM tblUsers WHERE User_name = ("+ txtDelete.Text +")");

SqlCommand myCommand = new SqlCommand();
myCommand.CommandText = query;
myCommand.Connection = myConnection;
myCommand.ExecuteNonQuery();

//Calling a method to update the database
UpdateDataIntoDatabase();

MessageBox.Show( "Deleted." );

When I run the program and click the delete button the following error occurs pointing in the myCommand.ExecuteNonQuery() part:-

An unhandled exception of type 'System.Data.SqlClient.SqlException' occurred in system.data.dll
Additional information: System error.

Can some one please tell me what do I have because I can't see anything wrong!

Thanks a lot guys

Re: Deleting from an SQL database

DotNetDominator7-May-06 2:02

DotNetDominator

7-May-06 2:02

Hi,

replace your String Query to one given here.

string query = "DELETE FROM tblUsers WHERE User_name = '" + txtDelete.Text + "'";

I this should work now. Also, Just check your connection is open before you assign it to myCommand.

Thank you.

Re: Deleting from an SQL database

alee15.10.887-May-06 20:12

alee15.10.88

7-May-06 20:12

Ohh thanks... I arranged the quotes and it worked out

Thanks a lot!!

Re: Deleting from an SQL database

Rob Graham7-May-06 4:06

SQL2005 Express reporting services remote user

7-May-06 4:06

I case you can't see what's different in DotNetD's answer, he replaced the double quotes surrounding the textbox variable with single quotes.

I strongly recommend that you check out this[^] article by Colin Angus Mackay before someone types ';truncate table tblusers; in your textbox and destroys your database.

If you use parameters rather than direct text concatenation, you will avoid both the problem you are having now, and the vulnerability to sql injection attacks in your app.

We need to graduate from the ridiculous notion that greed is some kind of elixir for capitalism - it's the downfall of capitalism. Self-interest, maybe, but self-interest run amok does not serve anyone. The core value of conscious capitalism is enlightened self-interest.
Patricia Aburdene

-- modified at 10:07 Sunday 7th May, 2006

juszuf5-May-06 20:37

juszuf

5-May-06 20:37

Hi,
I coldn't find the way how to enable remote users to run my reports from LAN an internet.I can run my reports in BI preview or on localhost, but from other pc it asks for username and password. Where can manage my users rols?
Can anybody send me a link to learn it?
Thanks

Using ROUND

myNameIsRon5-May-06 19:03

5-May-06 19:03

Hi,
I'm using ROUND in my SQL statement but I'm not getting the end result that I want. Using MS Access db with C#

Here is the statement
SELECT Price - ROUND(Price * Percent ,3)
Price = 28.50
Percent = 0.25

The end result I would like is: 28.50 - 7.13 = 21.37
What I'm getting is 21.38, it's like the ROUND is taking the whole statement and rounding the final result 21.375. Shouldn't it just ROUND what is in the ()?

Thanks for any help you can give,
Ron

Rob Graham7-May-06 5:52

7-May-06 5:52

SQL Round function rounds to the nearest even value.

select 28.50 - ROUND(28.50 * 0.25, 3)  returns 21.375 (which will round up to 21.38)
select Round(28.50 - ROUND(28.50 * 0.25, 3),2) returns 21.38 (rounds up to 21.38)
If the precision in the rounding is chaged to 2 for the 'price*percent' term:
select 28.50 - ROUND(28.50 * 0.25, 2) returns 21.37
select Round(28.50 - ROUND(28.50 * 0.25, 2),2) returns 21.37

We need to graduate from the ridiculous notion that greed is some kind of elixir for capitalism - it's the downfall of capitalism. Self-interest, maybe, but self-interest run amok does not serve anyone. The core value of conscious capitalism is enlightened self-interest.
Patricia Aburdene

myNameIsRon7-May-06 11:16

7-May-06 11:16

Thanks Rob, I really appreciate the info!

It looks like I shouldn't use ROUND, in my SQL because I'm wanting cash register type of rounding (always round up). It's too bad they don't have parameter options to select the type of rounding you want.

I have one other issue if you can help. I notice that if I use SUM(), it truncates to the decimal place the db columns are set to. Sometimes the SQL statement will calculate a value over the two decimal places. I need the SUM to give me at least 3 decimal places, so I can round up the value calculated. I can set one of the columns in the db (to 3 decimals) to achieve this, but I was wondering if there was a way to do this at the SQL statement?

Thanks!
Ron

Rob Graham7-May-06 12:30

7-May-06 12:30

myNameIsRon wrote:
It looks like I shouldn't use ROUND, in my SQL because I'm wanting cash register type of rounding (always round up). It's too bad they don't have parameter options to select the type of rounding you want.

Actually, SQL servers Round( a,b,c) funtion is parameterized to select between "round up" (c==0) and truncate (C!=0)

select Round(1.5,0,0)  -> 2.0 round up .5+
select round(2.5,0,0)  -> 3.0 round up 2.5 and greater
select round(2.4,0,0)  -> 2.0 round down 2.0 to 2.5 exclusive
select Round(1.5,0,1)  -> 1.0
select round(2.5,0,1)  -> 2.0

I think the problem with your example was just the wrong choice for the b ( # decimals)
parameter - you used 3, when what you wanted was 2 (assuming you wanted to round .005
and larger to .01 and .004 and smaller to 0.00. If what you realy wanted was to round
.11+ TO .12 , then use Ceiling(x *100)/100 - round any fraction to next highest integer

a good discussion of the varous rounding schemes can be found here.[^]

I think your observation on SUM() is correct, I know of no other solution.

We need to graduate from the ridiculous notion that greed is some kind of elixir for capitalism - it's the downfall of capitalism. Self-interest, maybe, but self-interest run amok does not serve anyone. The core value of conscious capitalism is enlightened self-interest.
Patricia Aburdene

-- modified at 18:31 Sunday 7th May, 2006

myNameIsRon7-May-06 19:19

7-May-06 19:19

Thanks again for the great info Rob!

Yes, I should have been using 2 for the decimal place to round.
It apprears that with MS Access, I can't use the "c" parameter (I get an error that I'm using too many parameters).

I really don't want Bankers Rounding (even # to round up):
Round(0.005,2) = 0.00
Round(7.125,2) = 7.12

I want old school rounding Blush | :O

)
Round(0.005,2) = 0.01
Round(7.125,2) = 7.13

Do you think I'll have to round the SQL result using something like this:
Floor(x * 100 + 0.5m)/100
to get what want?

thanks,
Ron

Rob Graham8-May-06 8:49

8-May-06 8:49

myNameIsRon wrote:
really don't want Bankers Rounding (even # to round up):

SQL server uses "round up" not Banker's rounding (I was wrong about that in the first post). I think if you just change the 3 to a 2 you'll get what you want from SQL. Access may be a diffewrent matter, check the link in the previous post: it details rounding for all microsoft products (they are very inconsistent).

We need to graduate from the ridiculous notion that greed is some kind of elixir for capitalism - it's the downfall of capitalism. Self-interest, maybe, but self-interest run amok does not serve anyone. The core value of conscious capitalism is enlightened self-interest.
Patricia Aburdene

myNameIsRon8-May-06 12:28

8-May-06 12:28

Hi Rob,
I did a bunch of tests and unfortunately Access does use the Banker's rounding when using ROUND. I was able to solve both my issues (SUM truncating and ROUND up only on even) though by using FORMAT on the calculations that could cause values to go past the second decimal place. I didn't think I could SUM on a FORMAT because I thought FORMAT converts the value to a string... but it works ok.

SUM ( FORMAT( price * percent, 'Standard' ) )

I really appreciate all your help, I'm new to programming and find almost everything a challenge… but a lot of fun too Blush | :O

).

Say, I have a different question about pulling data from a database...
Sometimes I want to use \n to give me a new line in a MessageBox.Show().
This works ok as a string to use:
string info = "Name\nAddress\nPhone";
but when I try to use a string that has been pulled from a database, the \n does not give me a new line (just shows up). Is there a way to change the string to work with \n again?

Thanks again,
Ron

Rob Graham8-May-06 16:48

8-May-06 16:48

Yea, "\n" is an "escape sequence" that the compiler understands to replace with the non-printing newline character(s), but embedded in a db string it is just \n...

You might be able to pass the db string through a format function and get the newlines replaced. If you're using C# try String.Format(dbString). C++ CString.Format(dbstring).

I haven't tried this, and I'm just guessing, but that is what I would try first.

We need to graduate from the ridiculous notion that greed is some kind of elixir for capitalism - it's the downfall of capitalism. Self-interest, maybe, but self-interest run amok does not serve anyone. The core value of conscious capitalism is enlightened self-interest.
Patricia Aburdene

myNameIsRon8-May-06 21:34

Export a table to a CSV file

8-May-06 21:34

Hey Rob,
The string.Format didn't seem to work so I googled "\n not working" and found this:

http://www.codeproject.com/cs/miscctrl/MessageBoxEx.asp?df=100&forumid=155440&exp=0&select=1246013#xx1246013xx[\n Not Working^]

Which gave me this to try, and it works.
dbString.Replace(@"\n",Environment.NewLine);
or
dbString.Replace(@"\n","\n");

Cheers!
Ron

Reporting Service 2005

devvvy5-May-06 14:38

devvvy

5-May-06 14:38

Hey, "Formula Fields" in Reporting Service (SQL 2005), is it in VBScript?

Norman Fung

Alvin Ortiz5-May-06 12:46

Alvin Ortiz

5-May-06 12:46

I have a table on Microsoft Access and I need to export it to a CSV file when I press a button on an Access form. I'm using some Transact-SQL code inside VBA, but nothing yet. Any commentaries?

Re: Export a table to a CSV file

Rob Graham7-May-06 6:07