Database

6-Jan-06 1:24

ma se wrote:
Ideally I would like to look at the eBay database because it comes with a lot of features such as product listings in various countries, shipping, etc. I had a look at the eBay API at http://developer.ebay.com, but can't seem to find the database. I'm not sure if it is available for download.

I would seriously doubt it would be available - That is proprietary information and companies guard that kind of thing jelously because it is the key into their business.

ma se wrote:
If anyone knows of good sample databases I can use for my project, please let me know.

The Advernture Works database that comes with SQL Server 2005 is a good sample database to get you started.

ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

Re: SQL Sample Databases

Brendan Vogt6-Jan-06 1:29

Brendan Vogt

6-Jan-06 1:29

Thanks for the reply.

I have SQL Server 2005 Express. I miss the query analyser in this version as what I had in SQL Server 2000. Everything seems to work through C# Express, or Visual Web Developer Express. Is there such a product available for SQL Server Express edition? I would like to view the Advernture Works database? Where can I download this database if it does not come with the express edition?

Re: SQL Sample Databases

gharry6-Jan-06 5:44

6-Jan-06 5:44

You wil find sample database at these links:

http://www.microsoft.com/downloads/details.aspx?FamilyID=06616212-0356-46a0-8da2-eebc53a68034&DisplayLang=en

to SQL server 2000 sample databases that can be used with the 2005 versions and here

http://www.microsoft.com/downloads/details.aspx?FamilyId=E719ECF7-9F46-4312-AF89-6AD8702E4E6E&displaylang=en

for 2005 specifik database examples. Thoose the correct platform and install.

SQL Doubt.

<color>Aljechin 5-Jan-06 19:32

<color>Aljechin

5-Jan-06 19:32

I have one small program which uses a database (MDB) i can copy both the exe and mdb and run on another machine. But if I use MS SQL then how can I ship the data with the program? Its because SQL is more powerful I want to do this. My program should query what file? Please explain me. I am very new to SQL Server. Just starting. Confused | :confused:

Re: SQL Doubt.

nguyenvhn5-Jan-06 20:48

nguyenvhn

5-Jan-06 20:48

Because MS Access is file database so you can copy it beside application. But MS SQL Server, as it named, is a database server, you have to connect application to such server that running SQL Database engine. If another computer is on a LAN, you could config connection string which point to database server. If another computer do not reside on LAN or in others networks, you have to re-config another database server and point connection string to that server.

Hope that help

Re: SQL Doubt.

Colin Angus Mackay5-Jan-06 20:49

5-Jan-06 20:49

Normally what I do is ship a backup file and have the installer restore the backup onto the target SQL Server. I then write scripts to reconnect all the security back (because things like the logins are handled in the master database, and you can't restore over your user's master database because they'd lose all their other server level settings)

ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

DAO Notification?

lynchspawn5-Jan-06 9:51

lynchspawn

5-Jan-06 9:51

I have an .mdb file (MS Access) that each employee will be using to tell who is in or out of the office. The .mbd file will be located on one of our servers. A windows program currently polls at a certain interval to refresh the status.

Is there some built-in way to have notifications sent from the DAO objects instead of polling?

I was also considering writing a server app and have each users program register with the server and handle it that way.

If there is a better way, please let me know.

Re: DAO Notification?

Dave Kreskowiak6-Jan-06 5:05

Dave Kreskowiak

6-Jan-06 5:05

No. Access doesn't support triggers or notification. SQL Server 2000 and above does. SQL Server 2000 needs a Notification Services extension installed. Search MS's site. SQL 2005 comes with it standard.

A workaround method would be to implement something like a service, or .NET Remoting singleton, that manages access to the database, updating required tables and whatnot, but also exposing events that the clients subscribe to. When one client requests a change to the database, it does so through this service or object, which then fires your Changed event so the other clients get notified.

RageInTheMachine9532
"...a pungent, ghastly, stinky piece of cheese!" -- The Roaming Gnome

-- modified at 11:06 Friday 6th January, 2006

Partial name

gharry5-Jan-06 9:47

5-Jan-06 9:47

If i want to search for a name in a table i use Select * from Tabel where Column = name

But how do i proceed if there are names like na, nam, name, and names.
The reason is that i would like to search for partial names if the full name is not found or more than one starting with nam

I hope the explanation is clear

Re: Partial name

Colin Angus Mackay5-Jan-06 11:01

5-Jan-06 11:01

This will search for anything containing "name"

SELECT * FROM MyTable WHERE column LIKE '%name%'

The % is a wildcard meaning zero or more characters.

However, overuse of wildcards screws indexing and reduces the performance of the SQL Server.

ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

Re: Partial name

gharry5-Jan-06 22:46

5-Jan-06 22:46

Thanks thats where i was looking for.
Is there a simple way to determ how many records are returend so i can either pace them in a list ore in a label

Re: Partial name

Colin Angus Mackay6-Jan-06 1:45

Reading CSV file into DataGrid

6-Jan-06 1:45

gharry wrote:
Is there a simple way to determ how many records are returend so i can either pace them in a list ore in a label

That depends on how you are extracting the data from the database.

If you are using a Data Reader then you just have to keep reading until you have all the rows. If you use a Data Adapter then fill the DataSet and find the Rows.Count from the DataTable.

ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

gharry5-Jan-06 5:32

5-Jan-06 5:32

I use this code to read a CSV file into a DataGrid:

Dim objConnString As String = "Provider=Microsoft.Jet.OleDb.4.0;Data Source=" & Server.MapPath("Sql\") & ";Extended Properties=""Text;HDR=Yes;FMT=Delimited"""
Dim objString As String = "SELECT * FROM C_Registered_horses.csv"

Dim objConn As New OleDbConnection(objConnString)
Dim objConnCommand As New OleDbCommand(objString, objConn)

Dim objConnReader As OleDbDataReader

objConn.Open()
objConnReader = objConnCommand.ExecuteReader()

dataGridConn.DataSource = objConnReader
dataGridConn.DataBind()

But it only shows the first column from the CSV file.
This methode works fine when connecting to a SQL database but why not here

Problem populating table

TheJudeDude5-Jan-06 5:06

TheJudeDude

5-Jan-06 5:06

I am trying to write a program which parses a text file, stores certain values, then inserts these values into a table. The table has one primary auto key, with 8 columns. The data is getting put into the table ( somewhat ). Several columns that hold (char) values are only insert the first character into the column. I ran the program through the debugger, and the parameter items of the data adapter hold the correct values. Here is the method that handles the parse and insert command:
private void btnStart_Click(object sender, System.EventArgs e)
{
string strFilename;
int intStoreNumber;
StreamReader objRead;
string strRecord;
int intStore;
string strNU;
string strManf;
string strModel;
string strSerial;
decimal decCost;
decimal decNet;
DateTime dtDate;
int intCount = 0;

for (intStoreNumber = 1; intStoreNumber <= 20; intStoreNumber++)
{
if (intStoreNumber < 10)
{
strFilename = intStoreNumber.ToString();
strFilename = strFilename.Insert(0,"0");
}
else
{
strFilename = intStoreNumber.ToString();
}
strFilename = "invd" + strFilename + ".prn";
objRead = new StreamReader(strFilename);

while (objRead.Peek() > -1)
{
strRecord = objRead.ReadLine();
intCount++;
if (strRecord != "" && strRecord != "\\f" )
{
if (strRecord.Substring(14,1) == "N" | strRecord.Substring(14,1) == "U")
{
if(strRecord.Substring(14,3) != "NU-")
{
intStore = intStoreNumber;
strNU = strRecord.Substring(14,1);
strManf = strRecord.Substring(26,8);
strModel = strRecord.Substring(36,10);
strSerial = strRecord.Substring(47,12);
decCost = Decimal.Parse(strRecord.Substring(60,8));
decNet = Decimal.Parse(strRecord.Substring(70,8));
dtDate = DateTime.Parse(strRecord.Substring(79,10));

SqlCommand cmdNew = new SqlCommand("ProcessInventory", this.sqlConnInv);
SqlDataAdapter sdaNew = new SqlDataAdapter(cmdNew);
DataSet dsInventory = new DataSet();
cmdNew.Parameters.Add("@Store_ID", SqlDbType.Int).Value = intStore;

cmdNew.Parameters.Add("@NU", SqlDbType.Char).Value = strNU;

cmdNew.Parameters.Add("@MANUF", SqlDbType.Char).Value = strManf;

cmdNew.Parameters.Add("@MODELNO", SqlDbType.Char).Value = strModel;

cmdNew.Parameters.Add("@SERIALNO", SqlDbType.Char).Value = strSerial;

cmdNew.Parameters.Add("@ORIGCOST", SqlDbType.SmallMoney).Value = decCost;

cmdNew.Parameters.Add("@NETVAL", SqlDbType.SmallMoney).Value = decNet;

cmdNew.Parameters.Add("@DATEAQ", SqlDbType.SmallDateTime).Value = dtDate;

sdaNew.Fill(dsInventory1,"Inventory");
}
}
}
}

}

}

and here is the stored procedure:
ALTER PROCEDURE dbo.ProcessInventory
/*
(
@parameter1 datatype = default value,
@parameter2 datatype OUTPUT
)
*/
@Store_ID int,
@NU char,
@MANUF char,
@MODELNO char,
@SERIALNO char,
@ORIGCOST smallmoney,
@NETVAL smallmoney,
@DATEAQ smalldatetime
AS
/* SET NOCOUNT ON */
INSERT Inventory(Store_ID, NewUsed,Manf,Model,Serial,Cost,Net,DateAcc)
VALUES (@Store_ID, @NU, @MANUF, @MODELNO, @SERIALNO, @ORIGCOST, @NETVAL, @DATEAQ)

RETURN

The Manf,Model, and Serial columns are the ones not being updated correctly.

Here is the definition for the table:
Inv_Reco(P-Key) int 4
Store_ID int 4
NewUsed char 1 (allow nulls)
Manf char 20 (allow nulls)
Model char 20 (allow nulls)
Serial char 20 (allow nulls)
Cost smallmoney 4 (allow nulls)
Net smallmoney 4 (allow nulls)
DateAcc smaldatetime 4 (allow nulls)

I wrote another board and was told to use the Command.ExecuteNonQuery, but that did not work.
I am new to SQL databases w/ C#, so any input would be greatly appreciated

Colin Angus Mackay5-Jan-06 6:45

5-Jan-06 6:45

I notice your code does not specify in the parameter the length of the char - I'm guessing that it might be defaulting to char(1).

TheJudeDude wrote:
I wrote another board and was told to use the Command.ExecuteNonQuery

They were right. Since your command is only calling a stored procedure that performs an INSERT the use of the DataAdapter is overkill. You are using an expensive object, and an expensive method (Fill) for nothing as there will be no data returned to your application with which to "fill" anything.

ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

TheJudeDude5-Jan-06 14:38

TheJudeDude

5-Jan-06 14:38

OK - sorry, the char variables are actually varchar(20) except for the first (NU).
I killed that data adapter, and tried the Command.ExecuteNonQuery and I get the following error :
Additional information: ExecuteReader requires an open and available Connection. The connection's current state is Closed.
But isn't the connection open fron the form?? I tried sqlConnInv.Open(), but got an error that the connection was already open...
Thanx in advance!

Colin Angus Mackay5-Jan-06 20:45

5-Jan-06 20:45

TheJudeDude wrote:
But isn't the connection open fron the form??

I don't know - Did you open it before calling this method? As a rule it is better not to keep a connection hanging around, it is better to create a new connection, open it, perform the data operations then close it again and let .NET's connection pooling handle how long the physical connection hangs around for. I recommend creating a method whose purpose is to create the connection, then call that method to get your connection.

In fact, and I know this is getting further from your original problem, I would recommend that you refactor all your data access in to its own class (at least) or assembly. This keeps all the data access code together and you can benefit from being able to easily reuse common methods (like opening a connection).

ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

TheJudeDude7-Jan-06 3:30

TheJudeDude

7-Jan-06 3:30

Well, I got it done yesterday. I ended up making the connection programmaticly, and modifying the stored procedure and it worked. One little quirk is on the form. I have two blank labels and a progress bar. The progress bar works fine. I pass two variables to the labels inside a for loop and while loop. The problem is that the text is not showing in the labels....odd. Any insight?

Colin Angus Mackay7-Jan-06 3:42

7-Jan-06 3:42

TheJudeDude wrote:
I ended up making the connection programmaticly

How else would you make the connection?

TheJudeDude wrote:
The progress bar works fine. I pass two variables to the labels inside a for loop and while loop. The problem is that the text is not showing in the labels....odd. Any insight?

Probably because of its nature the progress bar updates the display immediately when its value is changed without waiting for paint event to fire, while the label control won't update the display until it gets a paint event, while a long task is in progress the message pump isn't firing events. You can force the label, or any control, to Refresh()[^] or you can call Application.DoEvents()[^] in order to process any other events (like button clicks, moving the scroll bar, etc.)

ColinMackay.net
"Man who stand on hill with mouth open will wait long time for roast duck to drop in." -- Confucius
"If a man empties his purse into his head, no man can take it away from him, for an investment in knowledge pays the best interest." -- Joseph E. O'Donnell

SQL query

Deian5-Jan-06 4:33

Deian

5-Jan-06 4:33

Hello,

I'm a newbie at SQL, I can't figure out how to make the following query:

I have table 1 (2 columns)

code|otherstuff

And table 2 (2 columns)

code|some other stuff.

I'm getting the DISTINCT code, and in the same query I want to get the count(*) of records in table1 for each code, and count(*) in table2 where the code equals the code in table1. Tried already all kinds of joins but to no avail.

Any suggestions? Confused | :confused:

Re: SQL query

Colin Angus Mackay5-Jan-06 6:39