Database Discussion Boards

Re: Packaging a SQL Server database for installation as part of a custom application

Dinuj Nath7-Oct-05 22:50

7-Oct-05 22:50

Create a sql script for your database and run it on the client side. I think this is the best way. You can also script any basic data that you may need populated at the start.

Connect to SQL on same machine! help

darXstar30-Sep-05 2:48

darXstar

30-Sep-05 2:48

Hi. I've this question:

I have IIS (and dot net environment) and MS SQL msde on same machine (MS Windows 2000 server).

For security reason I've switched off all network protocols of Sql.
(sql is listening on shared memory)
Is it still possible to connect to sql (not using odbc) ??
I nedd to modify my ADO.NET connection string?
Could someone showme an exemple?

thanks to anyone.

Re: Connect to SQL on same machine! help

Colin Angus Mackay30-Sep-05 12:13

Colin Angus Mackay

30-Sep-05 12:13

darXstar wrote:
Is it still possible to connect to sql (not using odbc) ??

I've never tried that configuration, but it should be possible.

darXstar wrote:
I nedd to modify my ADO.NET connection string?

darXstar wrote:
I nedd to modify my ADO.NET connection string?

I wouldn't think so, why not just try it? I've got loads of apps lying around that were just to try out something where the docs didn't give a complete explanation or I just wanted to see how something worked in isolation.

darXstar wrote:
Could someone showme an exemple?

Of what? Connecting to a SQL Server database using ADO.NET.

string connectionString = "server=(local);database=myData;uid=a;pwd=b";
SqlConnection conn = new SqlConnection(connectionString);
SqlCommand cmd = new SqlCommand();
cmd.CommandText = "myStoredProcedure";
cmd.CommandType = CommandType.StoredProcedure;
cmd.Connection = conn;
SqlReader reader = cmd.ExecuteReader();
while(reader.Read())
{
    // Do stuff to process the information being returned from
    // the database
}

Does this help?

My: Blog | Photos

"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious

Re: Connect to SQL on same machine! help

darXstar3-Oct-05 5:39

darXstar

3-Oct-05 5:39

Hi, and thanks for reply. You have right. Why don't try?
The truth is that I was almost sure this configuration was working on my test server... but today doesn't work anymore...
While I keep switched off TCP protocol of sql server, I can not access it from asp.net web applications. the result is "Server doesn't exist or access denied". I am little bit confused... I can't tell if this configuration should work or not...
About connection string I was asking someone if there are a specific one using "Shared Memory" access mode.

anyone is experienced?
thanks again

SELECTING COLLATION WHEN INSTALLING MSDE.

Bigfootguy29-Sep-05 20:28

Bigfootguy

29-Sep-05 20:28

When you install MSDE, the installation is unattended (or silent), rather than interactive.
I need to specify collation, but it does not allow me to do it, installing instead the default which is no good for me.
How can I select (or change) a collation in MSDE?
Thanks.

Best

Bigfootguy

Re: SELECTING COLLATION WHEN INSTALLING MSDE.

darXstar30-Sep-05 2:37

darXstar

30-Sep-05 2:37

set COLLATION parameter when you run msde Setup :

setup COLLATION="Latin1_General_CI_AI" DATADIR="C:\" TARGETDIR="C:\Programmi\Microsoft SQL Server\" /L*v C:\msdelog.txt SAPWD="[your passw]"

Latin1_General_CI_AI means Case_Insensitive Accent_Insensitive

you can specify also CI_AI_KI for Katana an so on..
option not specified means default value.

darx

To Bitmask or Not

xsoftdev229-Sep-05 19:35

xsoftdev2

29-Sep-05 19:35

Hello folks,

I am confused at to whether to use bitmask to store values in my database. I am working on a web application with MSSQL 2K and C# asp.net. My requirement is to store the options chosen from a CheckBoxList control into the database and be able to search if a user has selected a particular choice.

An analogy would a Question, what Model cars have you driven.
Answer Choices: Honda, Toyota, Peugeot, Lancia, Fiat

A user can choose one or many.

I could store the Answer chosen in a separate table and do a join to search but I read somewhere that I could store all the values in one field in the Users table hence eliminating the need to create a new table.

Does anyone have any ideas, links to tutorials and sample code on how to do this? I very much welcome all suggestions.

Thanks
Mike

Re: To Bitmask or Not

Russell Jones3-Oct-05 5:59

Russell Jones

3-Oct-05 5:59

Let's say you've just got 2 types of car: Honda, Toyota

if i've driven a honda i have the value 10 (2)
if i've driven a toyota i have the value 01 (1)
if i've driven both then i have the value 11 (3)

use the same system for the cars your checking for.

Example 1

Uservalue = 11(3) (driven both)
Carvalue = 01(1) (just a toyota)

If we use a bitwise AND ( check each digit in turn and iff they are both 1 place a 1 in the corresponding digit of the result) we get the value 01. Comparing this to the carvalue we find that the user has indeed driven a toyota.

IF uservalue & Carvalue = Carvalue
BEGIN
PRINT 'Has Driven a toyota'
END

This sytem extends to as many bytes as the biggest int available to you.

Example 2:

Uservalue = 110110110110110110
CarValue = 100110100010110010
Result = 100110100010110010 (The user has driven all the cars specified in CarValue)

To Create the bitmasks use a logical OR, or just add the numbers together

CarValue = Toyota | Honda is equivalent to Carvalue = Toyota + Honda using bitmasks, but if you use the OR sysntax for complicated expressions you will never be able to add the same car twice which would cause problems.

HTH

Russ

-- modified at 11:59 Monday 3rd October, 2005

Oracle Help Needed - ComboBox data binding

Glenn E. Lanier II29-Sep-05 9:30

Glenn E. Lanier II

29-Sep-05 9:30

I am having some difficulty in getting my .NET application to work as desired using an Oracle database (I am using the Oracle Data Provider for .NET). In particular, my ComboBox no longer contains any items.

I had a SQL table:

CREATE TABLE [dbo].[tblTitles] (<br />
	[TitleID] [int] IDENTITY (1, 1) NOT NULL ,<br />
	[TitleDisplayText] [varchar] (50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL ,<br />
	[TitleHidden] [bit] NULL CONSTRAINT [DF_tblTitles_TitleHidden] DEFAULT (0),<br />
	CONSTRAINT [PK_Titles] PRIMARY KEY  CLUSTERED <br />
	(<br />
		[TitleID]<br />
	)  ON [PRIMARY] <br />
) ON [PRIMARY]

and created a complementary (I think) Oracle table:

CREATE  SEQUENCE  tblTitles_TitleID_SEQ<br />
	START WITH 1<br />
	INCREMENT BY 1<br />
/<br />
<br />
CREATE TABLE tblTitles <br />
(<br />
	TitleID int  NOT NULL , <br />
	TitleDisplayText VARCHAR2 (50)  NULL , <br />
	TitleHidden NUMBER(1)  DEFAULT 0  NULL , <br />
	CONSTRAINT PK_Titles PRIMARY KEY (TitleID) <br />
)

My Code (which worked against SQL Server table, but not against Oracle):

// Create the dataset<br />
DataSet dsTitles = new DataSet("Titles");<br />
OpenConnection(); // creates and opens a new connection or returns existing connection.<br />
OracleCommand tableCommand = null;<br />
tableCommand = dbConnection.CreateCommand();<br />
tableCommand.CommandText = "select TitleID, TitleDisplayText FROM tblTitles WHERE TitleHidden <> 1 ORDER BY TitleDisplayText";<br />
<br />
OracleDataAdapter adapter = new OracleDataAdapter();<br />
adapter.SelectCommand = tableCommand;<br />
adapter.Fill(dsTitles, "Titles"); <br />
<br />
// Bind to ComboBox<br />
comboBoxTitle.BeginUpdate();<br />
comboBoxTitle.DataSource = dsTitles.Tables["Titles"];<br />
comboBoxTitle.DisplayMember = "TitleDisplayText";<br />
comboBoxTitle.ValueMember = "TitleID";<br />
comboBoxTitle.EndUpdate();

If I create an OracleDataReader and execute the query, adding each item to the ComboBox, it displays, but this takes a considerable amount of time.

Any ideas on what I'm doing wrong, or not doing? If this is not the best place to ask, kindly advise a better place.

Thanks,
Glenn

Re: Oracle Help Needed - ComboBox data binding

Guillermo Rivero3-Oct-05 11:40

Guillermo Rivero

3-Oct-05 11:40

1. Instead of creating a DataSet, create a DataTable.
DataTable dtTitles = new DataTable();

2. Fill the DataTable with the adapter
adapter.Fill(dtTitles);

3. Bind the datatable
comboBoxTitle.DataSource = dtTitles;

Hope this helps...

Free your mind...

Re: Oracle Help Needed - ComboBox data binding

Glenn E. Lanier II2-Mar-06 8:33

Glenn E. Lanier II

2-Mar-06 8:33

Thanks. Turns out the problem was solved when I removed the comboBox from the form and created a new comboBox with same settings. Worked on all that were causing me grief.

--G

Where should I store my connection string?

Haim Yulzari28-Sep-05 22:24

Haim Yulzari

28-Sep-05 22:24

I have to write a small application where a GUI accesses a DAL (Data Access Layer) through a web service. The GUI and the DAL are on two different computers. My question: where should I store the connection string so the DAL can get it and connect to the database?

Thanks!

Re: Where should I store my connection string?

Anonymous29-Sep-05 7:41

Anonymous

29-Sep-05 7:41

You should use an xml file to store the configuration of the connection string. The xml file may contain the path of encrypted password. The DAL should be able to build the connection string using these attributes.

Re: Where should I store my connection string?

Luis Alonso Ramos29-Sep-05 17:13

Luis Alonso Ramos

29-Sep-05 17:13

If the DAL is on a web service (ASP.NET application), store it encrypted in web.config.

-- LuisR

Luis Alonso Ramos
Intelectix - Chihuahua, Mexico

Not much here: My CP Blog!

The amount of sleep the average person needs is five more minutes. -- Vikram A Punathambekar, Aug. 11, 2005

Re: Where should I store my connection string?

-Dr_X-2-Oct-05 14:42

-Dr_X-

2-Oct-05 14:42

Per Microsoft about a year and a half ago, you should store you encrypted connection string in the registry of your web box.
Here's the link:
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/dnnetsec/html/SecNetHT11.asp[^]

Michael

I firmly believe that any man's finest hour, the greatest fulfillment of all that he holds dear, is that moment when he has worked his heart out in a good cause and lies exhausted on the field of battle - victorious.
Vince Lombardi (1913-1970)

Retrieve & update data against remote database

nmhuy28-Sep-05 8:15

nmhuy

28-Sep-05 8:15

I am developing an Access 2000 database application. In my application, I need to connect to a remote Access 2000 database to retrieve and update data. I tried to use RDS (Remote Data Service) as mentioned in MSDN but failed (it raised a runtime error 8447: Internet Server Error).

Dim cn as ADODB.Connection
Dim rs as ADODB.Recordset

cn.Open "Provider=MS Remote; Remote Server=http://myServer; Data Source=sample.mdb"
Set rs = cn.Execute(mySelect)

Do I need any configurations on the server to make the above code work? Or are there any way else? Please help me, thanks.

Lan hue sau ai lan hue heo
Lan hue sau doi trong heo ngoai tuoi

Re: Retrieve & update data against remote database

enjoycrack28-Sep-05 20:01

enjoycrack

28-Sep-05 20:01

hi there,

this article might give u a hint on this
Remote DB Access

<< >>

Re: Retrieve & update data against remote database

nmhuy29-Sep-05 7:22

nmhuy

29-Sep-05 7:22

Thanks for you help!

Lan hue sau ai lan hue heo
Lan hue sau doi trong heo ngoai tuoi

Help in SQL Stored proc

satishrg28-Sep-05 7:00

satishrg

28-Sep-05 7:00

hi,

I have a store procedure that needs to set of values if the record is found in the table and if it doesnt find any row in the table I a have to send some columns with some fix values and I dont want to send other columns but as I am filling the same dataset I have to send some value in these feilds so I am sending "null" value.

How do I handle this null value in the C# code.It is like if I get null value then I dont want to override value in that feild.

Can any one help me in doing this.

Thanking you in advance,
Satish.

Re: Help in SQL Stored proc

enjoycrack28-Sep-05 20:08

enjoycrack

28-Sep-05 20:08

hi there,

satishrg wrote:
How do I handle this null value in the C# code

refer to DBNull class in MSDN

<< >>

ADO .net --> Excel

Braulio Díez27-Sep-05 23:02

Braulio Díez

27-Sep-05 23:02

Hi,

I'm trying to read and Excel Workbook using excel, all goes fine but not the dates, depending on regional settings sometimes I got numbers instead of dates when I read using ADO .net

The problem it's on the regional settings I guess, sometimes I just save the Excel workbook (even with the same regional settings), and when I read I get an ugly number, something 38564...

Any ideas about this issue ? Thanks in advance, Greetings
Braulio

Re: ADO .net --> Excel

Braulio Dez27-Sep-05 23:30

Braulio Dez

27-Sep-05 23:30

Well...

It seems that Excel just mess up dates if some user has different regional settings... the good news are that the number that you get has some sense... it's the number of days since a 1900 date, here is a link that explains how that works:

http://www.cpearson.com/excel/datetime.htm[^]

Greetings
Braulio

get update status of each row

manasrahfantom27-Sep-05 22:13

manasrahfantom

27-Sep-05 22:13

Hi everyone,

im going to send an xml file containing about 3000 records to a stored procedure using asp.net, to make a bulk update operation on the DB.
The update will depend on the Date of each row, and the date in the DB, for example, if the Date in the DB was less than the date in row that has the update data, then the update will be done, else the update will fail.

i need to get the update status of each the 3000 row, and i should return it back to asp.net to display it for the user...

so how a thing like this can happen?

i dont want to access the DB from the code 3000 times Smile | :)

for example can i update the xml that i sent to the stored procedure and return it back to the page..

if anyone has any ideas, just pls send it

million thanks

Mahmoud Manasrah

-- modified at 4:19 Wednesday 28th September, 2005

automatically Run an Sql statement

achrafus27-Sep-05 9:18

achrafus

27-Sep-05 9:18

hello everybody,
I have an sql statement that I want to automatially run at the beguining of each month from server side. I'm using sql server 2000 and didn't find anything about dbmsjobs in sql!
Can anybody help me with that please

-- modified at 15:23 Tuesday 27th September, 2005

Re: automatically Run an Sql statement

Colin Angus Mackay27-Sep-05 9:49

Colin Angus Mackay

27-Sep-05 9:49

You can set up a job that the SQL Server Agent will perform for you on the schedule that you want.

Go in to Enterprise Manager, Open the server that you want the job to run on, select the Management folder and then SQL Server Agent and finally Jobs.

You can right-click on Jobs and select New Job... and you'll get a dialog that will allow you to define the job.

Does this help?

My: Blog | Photos

"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucious

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