Statements with Predicates - Database Discussion Boards

Mattb795-Mar-05 12:27

5-Mar-05 12:27

what i want to do is a very basic database. the program will have some check boxes and depending on whats checked a list box will display certain items. i thought a database would work good for this. i tryed useing a txt file and stringreader and a array to sort the data but was having problems if someone knows a good place to learn how to do thise type of things please tell me Smile | :)

also i tryed a database but it needs you to register the database file with windows.. I dont what to do this becouse i will be usen this program on differnt computers. so is there a simple db that can be moble

Re: New to databases need help

Mekong River5-Mar-05 14:58

Mekong River

5-Mar-05 14:58

Try to check this sample training course[^]. It teach you about using visual basic and access database.

A thousand mile of journey, begin with the first step.

APO-CEDC
Save Children Norway-Cambodia Office

Re: New to databases need help

numbrel7-Mar-05 10:34

numbrel

7-Mar-05 10:34

What information are you trying to store/retrieve? From your description, it sounds like you may be able to handle what you want in code.

Re: New to databases need help

Mattb797-Mar-05 11:33

Mattb79

7-Mar-05 11:33

its a tree id program.. they will have options thu check boxes ( usless theres a better way) like needles or board leaf. shape, location ect.. i was thinking database becouse there are a ton of differnt types of trees. and to code them all in would be a pain later on if i wanted to edit them.

Edit: I had a simple version of what i want to do in c++. i was trying to remake it and add to it useing c# im new to c# and databases thu so its a learning experiance.

Re: New to databases need help

numbrel8-Mar-05 3:51

numbrel

8-Mar-05 3:51

So you will be keeping the trees in the database and based on which characteristics are checked, the matching tree will show in the listbox?

And you are using a listbox for the trees because you also want the user to be able to choose a tree and see the characteristics in the checkboxes?

You will need to establish the database first. You can use Access to set up your database. Once it's setup you just need to copy the database file to wherever you want it to go, you won't need Access after it's setup, except to make changes to the database. Your program will be able to run on computers without Access. If you can't use Access, you can try to set it up using another database program and copy the database file to where you want to work with it. I don't see why that wouldn't work.

Re: New to databases need help

Mattb798-Mar-05 6:04

Mattb79

8-Mar-05 6:04

Right it will only show what trees match what is checked and display those. And if you click a tree it would show what it has. yes i have access. im in college right now ( not a very good one) thats why im here asking questions.. but anyways in school when we did databases we had to go into the ODBC and setup the path of the database. i asked my teacher about c# she said she heard of c# but knew nothening more ( im changing schools ) so thats where im at im not sure what to use to have my program to read from the database without all the extra setup each time I put this program on a differnt computer. I wont lie im really new to database stuff and im willing to buy books or what ever if you know of any good ones.

Re: New to databases need help

numbrel9-Mar-05 8:06

numbrel

9-Mar-05 8:06

I am new to writing programs that access database files myself. I have found the books from Murach publishing to be very useful, although I am learning VB.NET. They do have a C# book that covers databases ADO.NET. Their examples usually use SQL, just replace sql with oledb and it should work.

I have also just begun to learn how to deploy an applications, so I don't know all the ins and outs, but I have my database in its own folder on the C drive and I install this folder on the C drive on the computers I want to run the program, so the reference in the connection remains the same. There is probably a way of doing this automatically through the setup program, but I haven't learned that yet.

Statements with Predicates

Melvin_MCW5-Mar-05 6:11

Melvin_MCW

5-Mar-05 6:11

I am thinking of implementing two SQL statements with either one that can be used by a user. For one, I want the run the query where the very first character of the data of the "Name" field is equivalent to the alphabet selected. E.G: I pick 'B' and it searches for data in "Name" field where name starts with B.

The other one is where it searches for a name that has one of the words entered by the user. Say that if I enter Jiminez and the database returns 2 records where it has Jiminez as either the middle or the surname.

The problem is that i've tried in doing in access query with some test data by entering the LIKE predicate but no result. Please help a.s.a.p Unsure | :~

Re: Statements with Predicates

NassosReyzidis10-Mar-05 0:47

NassosReyzidis

10-Mar-05 0:47

query 1 :" SELECT * FROM... WHERE CUSTNAME LIKE 'A%'" returns everything strating with A
query 2 :" SELECT * FROM... WHERE CUSTNAME LIKE '%John%'" returns everything that contain john

to get both querys in one dataSet:
SELECT * FROM... WHERE CUSTNAME LIKE 'A%'
UNION
SELECT * FROM... WHERE CUSTNAME LIKE '%John%'
to do that you must ensure that the two queries must have the SAME columns layout AND SAME columns number

GanDad

Date Time shows different

Tom Wright4-Mar-05 5:54

Tom Wright

4-Mar-05 5:54

I have a field set with a type of datetime size 8. When I run an SQL statement like this

SELECT     TOP 1 MDC1_DATA_TEXT, MDC1_REV_DATE<br />
FROM         MDC1<br />
WHERE     (MDC1_I_O = 'O')

thru the qurey analyzer I get this:

2005-02-25 16:12:07.373

When I run the statement thru enterprise manager by right clicking on the table and choosing open table return all rows then clicking on the SQL button in the toolbar and running my statement, I get this:

2/25/2005 4:12:07 PM

This is the same thing that comes across when I run my SQL statement in my program. The problem is that this "2/25/2005 4:12:07 PM" does not contain milliseconds which is part of the primary key and needed to delete the record after reading it in. Does anyon know how to have SQL return back the other date time stamp?

Here is my code that I'm using in my program:

<br />
	try<br />
	{<br />
		pCommand.CreateInstance(__uuidof(Command));<br />
		pCommand->ActiveConnection = m_pConnection;<br />
		//pCommand->CommandText = _bstr_t("SELECT TOP 1 MDC1_DATA_TEXT, MDC1_REV_DATE FROM MDC1 WHERE (MDC1_I_O = 'O')");<br />
		pCommand->CommandText = _bstr_t("SELECT TOP 1 * FROM MDC1 WHERE (MDC1_I_O = 'O')");<br />
		pRecordset.CreateInstance(__uuidof(Recordset));<br />
		pRecordset->CursorLocation = adUseClient;<br />
		pRecordset->Open((IDispatch*) pCommand, <br />
			vtMissing,<br />
			adOpenStatic,<br />
			adLockBatchOptimistic,<br />
			adCmdUnknown);<br />
		_variant_t MDC1_DATA_TEXT;<br />
		_variant_t MDC1_REV_DATE;<br />
<br />
		while (!pRecordset->adoEOF)<br />
		{<br />
			CString mySQLData = (char*)(_bstr_t)pRecordset->Fields->GetItem("MDC1_DATA_TEXT")->Value;<br />
			//Get the reverse date time stamp so that we can use it for later.<br />
			//MDC1_REV_DATE = pRecordset->GetCollect("MDC1_REV_DATE");<br />
			CString revDate = (char*) (_bstr_t)pRecordset->Fields->GetItem("MDC1_REV_DATE")->Value;<br />

Should I set my pointer for the rev_date to something else?

Thanks

Tom Wright
tawright915@yahoo.com

Re: Date Time shows different

numbrel5-Mar-05 3:45

numbrel

5-Mar-05 3:45

I don't think your problem is SQL, I think it is with how .NET handles DateTime. You might try reading up on DataTime and see how it handles milliseconds. I did a quick search on www.support.microsoft.com and there seem to be a problem with what you want to do.

Re: Date Time shows different

Rob Graham5-Mar-05 12:08

Rob Graham

5-Mar-05 12:08

You are casting it to a string not converting it with a format specifier, so it is going to use default formatting which rounds to seconds. Retain the value as a variant, so that the presison is not lost, use it in a parameterised query when you need to delete.

Beware of string conversions from date time, they are locale dependant, and unless you specify the formating the default precison of the conversion is likely seconds.
SQL servers Date data type is accurate to 3 ms, but will round up: the next values after .373 will be .377 then .380.

Anger is the most impotent of passions. It effects nothing it goes about, and hurts the one who is possessed by it more than the one against whom it is directed.
Carl Sandburg

oracleDataAdapter & Parameters

Gerder3-Mar-05 0:56

Gerder

3-Mar-05 0:56

Hi to all, sorry me for my Inglish.
I have a Problem:
I want to select rows from my Oracle table which named "Table1" and I want to do it with oracleDataAdapter.
It's OK when I use OleDbDataAdapter:
............
command.CommandText="select name from Table1 where num = ?";
Ole.OleDbDataAdapter ada=new Ole.OleDbDataAdapter();
ada.SelectCommand=com;
command.Parameters.Add("@num",Ole.OleDbType.Numeric,3);
command.Parameters[0].Value=1;
............
Can you give me this code with using the oracleDataAdapter and with parameters of oracleConnection.

I had not find the using of oracle parameters in MSDN.At last can you give me Internet resource with the using of oracle in C#.

Thanks.

Gerder

Re: oracleDataAdapter & Parameters

numbrel7-Mar-05 10:38

numbrel

7-Mar-05 10:38

Have you tried using the SQLDataAdapter and just substitute "SQL" for everywhere you have "OleDb"?

insert record into field names that contain brackets

elephantstar2-Mar-05 8:08

elephantstar

2-Mar-05 8:08

Hi there,

I am having difficuly inserting a record into the database. I came to realize that it may be because of my column name that contains brackets. It inserts just fine with other fields that are the same data type but it stops at the bracketed field name. How can I get around this? I can change the column name to exclude any spaces because it's a pre-defined format. I tried bracketing the field name within my code so it's the same as in the design view but it still doesn't work. Can someone help me please. Here's my code below:
----------------------------------------------------------------------------------------------------

COleSafeArray vaFieldList;
vaFieldList.CreateOneDim(VT_VARIANT, 4);

COleSafeArray vaValueArray;
vaValueArray.CreateOneDim(VT_VARIANT, 4);

long lArrayIndex[1];
lArrayIndex[0] = 0;
vaFieldList.PutElement(lArrayIndex, &(_variant_t("NAME")));
lArrayIndex[0] = 1;
vaFieldList.PutElement(lArrayIndex, &(_variant_t("ADDRESS")));
lArrayIndex[0] = 2;
vaFieldList.PutElement(lArrayIndex, &(_variant_t("EMAIL")));
lArrayIndex[0] = 3;
vaFieldList.PutElement(lArrayIndex, &(_variant_t("[NUM CARS]"))); //will not insert this field

lArrayIndex[0] = 0;
vaValueArray.PutElement(lArrayIndex, &(_variant_t(m_name)));
lArrayIndex[0] = 1;
vaValueArray.PutElement(lArrayIndex, &(_variant_t(m_address)));
lArrayIndex[0] = 2;
vaValueArray.PutElement(lArrayIndex, &(_variant_t(m_email)));
lArrayIndex[0] = 3;
vaValueArray.PutElement(lArrayIndex, &(_variant_t(m_num_cars)));

pRecordset->AddNew(vaFieldList, vaValueArray);
pRecordset->Close();

Thanks!

Re: insert record into field names that contain brackets

Michael Potter4-Mar-05 4:23

Michael Potter

4-Mar-05 4:23

If the field name on the Sql Server is actually [NUM CARS] then you would use:

[[Num Cars]]]

If the field name is NUM CARS and you are using the brackets to deal with the space - I don't know what the problem is.

If this does solve your problem then any time you run into the issue again use QUOTENAME()in query analyzer to find the solution.

SELECT QUOTENAME('[NUM CARS]')

Sql Stored Procedure

Mahesh16791-Mar-05 18:40

Mahesh1679

1-Mar-05 18:40

Hi To ALL!

Can any one tell me the max no of input parameter and also output parameter supported in stored procedure (sql server 2000)

Another problem that i face is that i need to convert the sql server 7 database to sql server 2000 database.

Thanks for your valuable help

Mahesh

Re: Sql Stored Procedure

Anonymous1-Mar-05 19:00

Anonymous

1-Mar-05 19:00

A stored procedure can have a maximum of 2,100 parameters

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_create_4hk5.asp[^]

Re: Sql Stored Procedure

Mahesh16791-Mar-05 22:16

Mahesh1679

1-Mar-05 22:16

Thank You Very Much...

You mean 2100 parameters is a combination of input,output,return.

Re: Sql Stored Procedure

Tom Wright4-Mar-05 9:21

Tom Wright

4-Mar-05 9:21

yes the total number.

Tom Wright
tawright915@yahoo.com

OleDbDataAdapter.Update() should throw error but doesn't

Florent Geffroy1-Mar-05 3:37

Florent Geffroy

1-Mar-05 3:37

Hi !

I have a application which use an Access DataBase to store its data. I use an OleDbDataAdapter to update it, everything works fine.

Now, I want to perform some tests, to see what happens in case of errors. The first thing I've tried is when I have not enough space on disk. As I have a VarBinary field in a table, I put inside it a large amount of data.

This is where the problem appears. I add a single DataRow in the table, and then call OleDbDataAdapter.Update() on my table. And no exception is thrown. I know that I have not enough space, but it tells me that the row was updated (although it was not). If I add two rows in the table, it throws the exception on the second row, and it's still telling me that the first one was updated...

Any ideas ? Thanks !

Re: OleDbDataAdapter.Update() should throw error but doesn't

Rob Graham5-Mar-05 12:20

Rob Graham

5-Mar-05 12:20

Access plays some games with storage allocation for Varbinary fields, It likely has an 'unused' varbinary row left over from some previous update that is already allocated but not in use. Compact the database before you run the test, the result will likely be an exception. Access will try to 'recycle' blob storage, so the size of the database file is not the only determining factor in whether or not it can store the update.

Anger is the most impotent of passions. It effects nothing it goes about, and hurts the one who is possessed by it more than the one against whom it is directed.
Carl Sandburg

Re: OleDbDataAdapter.Update() should throw error but doesn't

Florent Geffroy6-Mar-05 21:49

Florent Geffroy

6-Mar-05 21:49

Thanks for replying !

I'll try to compact my database Smile | :)

multiple db systems in one app

schilled1-Mar-05 2:45

schilled

1-Mar-05 2:45

hi,
i've a little db design problem. we are designing an app which should support multiple data storage technologies(sql server, oracle and xml).
because of xml and sql server replication we decided to use a guid column as pk col. but there are some problems.

if use guid:
@ the old version of this app used auto int as pk, so theres no way
to import old databases.
@ oracle doesnt have a guid datatype so we would need to put it into
a different datatype like char or raw but i think it would result
in bad performance

if not use guid:
@ sql server replication is not that easy anymore
@ how do i get a unique id in a xml document??????

does anybody have some hints??
is there another way to get a unique key in a xml doc except guids??

thanks in advance

Re: multiple db systems in one app

-Dr_X-19-Mar-05 16:49

-Dr_X-

19-Mar-05 16:49

The only way I can think of is to create an empty copy of the old database and append the GUID fields to all of the tables. Import the old database then update all of the GUID fields according per their in pk to keep the referencial integrity. It would take some time to create the script to update all of the GUIDs properly but is doable. Then you could import the GUID'd db into the final target db. As far as GUID in oracle as char columns, the performance would most probably suffer but if the columns are indexed properly it shouldn't be that bad.

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)

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