Beginner at Database Programming - Database Discussion Boards

Jeff J13-Mar-03 13:29

13-Mar-03 13:29

Usually it's untyped datasets that are considered more limited than typed ones, unless you are accessing data you know little about. However, it really depends on what you want to do.

Typed datasets essentially just means the types are known and understood, whereas untyped ones are generic, and hence less is known (and needs to be known). Typed sets are much more efficient, since more work is done at compile time than at runtime. Also, typed sets can do more error checking to avoid mistakes. Commonly, the goal is to use typed sets whenever possible.

Untyped sets can be a little simpler at first, since less needs to be specified, column names do not need to be known, etc. If you do need to work with untyped sets at some point, it's useful if you can have a schema automatically generated, so you can move to typed sets. That will depend on the situation, though.

If you do not consider needing to know what types to deal with to be a problem, then I cannot think of any serious limitations with typed datasets.

Cheers

Parsing a Querystring using TSQL.

Alex Polajenko11-Mar-03 6:50

Alex Polajenko

11-Mar-03 6:50

I need each param parsed out of this string. I am having trouble parsing if the vars vary in length.

Data: ec=TTT&oc=CRM&tot=501&UID=88997

<br />
(SELECT<br />
	   ID, IP, TID, TheUserDetails.UserID as UserID, <br />
	   --Parsing<br />
	   Querystring,<br />
	   SUBSTRING(Querystring, CHARINDEX('ec=',Querystring) + 3, CHARINDEX('&', Querystring) - 4) as EC,<br />
	   SUBSTRING(Querystring, CHARINDEX('oc=',Querystring) + 3, CHARINDEX('&', Querystring) - 4) as OC,<br />
	   SUBSTRING(Querystring, CHARINDEX('tot=',Querystring) + 4, CHARINDEX('&', Querystring) - 5) as TOT,<br />
	   SUBSTRING(Querystring, CHARINDEX('uid=',Querystring) + 4, 50) as UID<br />
	  FROM WebLog l LEFT JOIN<br />
                     dbo.TheUserDetails ON <br />
		     l.TID = TheUserDetails.TrackID)<br />

Alex Polajenko

Re: Parsing a Querystring using TSQL.

andyharman11-Mar-03 7:47

andyharman

11-Mar-03 7:47

Alex

If the parameters are always in the same order then couldn't you use something like:

SUBSTRING(Querystring,<br />
CHARINDEX('ec=',Querystring) + 3,<br />
CHARINDEX('&oc=', Querystring) - (CHARINDEX('ec=',Querystring) + 3)<br />
) as EC

The third argument for SubString calculates the size by finding the difference between the starting position of "oc=" and "ec=". Note that I haven't tried this because I'm at a client's site today.

Good luck
Andy Harman

Change Current Cell or Row programatically

DionChen11-Mar-03 5:35

DionChen

11-Mar-03 5:35

Hi,

I have a datagrid bound to a dadaset table. I programatically added a new row. I wonder what is the easiest way to make the current cell or row to the newly added row in the datagrid?

Thanks.

Dion

Re: Change Current Cell or Row programatically

Mazdak15-Mar-03 4:17

Mazdak

15-Mar-03 4:17

DataGrid::CurrentRowIndex and myDataGrid::CurrentCell

Mazy

"And the carpet needs a haircut, and the spotlight looks like a prison break
And the telephone's out of cigarettes, and the balcony is on the make
And the piano has been drinking, the piano has been drinking...not me...not me-Tom Waits

SQL server BLOB and ADO Stream

mirano10-Mar-03 23:12

mirano

10-Mar-03 23:12

Hi guys.

This is the problem I encountered. If anybody has any ideas about this, please get back to me. Thanks in advance.

So, I have like 12.000 records in access, and since it is growing fadtla, I upsized it to SQL server. Now, Access provides a native support for OLE bound object, which is not the casew with SQL server. I have Excel and Word files embedded in Access fields, now when I upsized it to SQL server it converts it to Image data type. I know, there is also na option to save a path to the file, and keep file on a disk, but there are security issues related to that so it nees to stay in the dbs.
The problem is when I open ADO stream object, read a binary content from the filed, save it to the disk and opne it, in Excel for instance, it gives me a lot of corrupted data, when some of it is actually displayed correctly. Now it seems as it gets corrupted when upsizing from Access for Access probably does not keep data as it is, i.e. a binary image of the file, but also puts some wrapper around it to support different OLE features and office automation (when you click the OLE bound field in the access, it automatically opens the Word or Excel app and file in it).
So what would be the way for working around this? I can manually open each file in Access and save it to the disk and then upload it from there to SQL server, which will work (I tried it) but there is just to much of it. Is there any way of getting a clear binary stream out of the access or sql server?

Anyways, your ideas are greatly appreciated.

Thanks,

Mirano

.

Re: SQL server BLOB and ADO Stream

Yuri Kreinin11-Mar-03 21:28

Yuri Kreinin

11-Mar-03 21:28

You are right. Access uses OleSaveToStream and OleLoadFromStream COM API calls to save/load objects. The CLSID of the stored object is stored prior to object's binary data.
The best solution I may recommend. Don't modify the data transferred from Access. If you are programming in C++ just pass the stream object to OleLoadFromStream function and it will do all job for you. If you are developing under VB, save stream to disk and use GetObject("pathname"), the object will be loaded automatically.

Beginner at Database Programming

K. Shaffer10-Mar-03 11:26

K. Shaffer

10-Mar-03 11:26

I am new to database programming and am relatively ineperienced in C++ programming (have done most of my programming in Java). I have searched and searched for tutorials on how to do what I want to do and have had little luck. All I need to do is to read from a .dbf file that contains information about airports. This dbf file is stored locally on this PC and the PC runs Windows 2000. I need to develop a program that I can run on a number of different PC's running Windows 2000, each having it's own locally stored .dbf file. When I research database resources, I see ADO, DAO, ODBC, OLE DB etc... etc... and I cant seem to make any sense of any of it. I was wondering if some of you could help.

1) How should I get started? What class should I use?
2) Do you have any weblinks of some very simple to the point online tutorials that can help me along.

Any help would be appreciated

thanks

Kevin Shaffer
Student of Computer Science
University of Kansas
kshaff03@msn.com

Re: Beginner at Database Programming

Mark Sanders11-Mar-03 9:36

Mark Sanders

11-Mar-03 9:36

Do a search for the ADO Connection and Command objects.

Mark Sanders
sanderssolutions.com

SQL Server 2000 "length"

Steve McLenithan10-Mar-03 7:42

Steve McLenithan

10-Mar-03 7:42

What exactly is the meaning of the integers in the length column when designing a table. I notice that int is 4 and can't be changed. nvarchar is default to 50 and can be changed. What is the meaning of the numbers? Number of characters?, bits?

Confused | :confused:

Any website or explanation would be great.

$TeVe McLeNiThAn

Re: SQL Server 2000 "length"

Bruce Duncan10-Mar-03 7:47

Bruce Duncan

10-Mar-03 7:47

I think it is the maximum byte size of the field.

Bruce Duncan, CP#9088, CPUA 0xA1EE, Sonork 100.10030
Blackadder: Baldrick, have you no idea what irony is?
Baldrick: Yeah, it's like goldy and bronzy only it's made of iron.

Re: SQL Server 2000 "length"

Steve McLenithan10-Mar-03 7:50

Steve McLenithan

10-Mar-03 7:50

Bruce Duncan wrote:
maximum byte size

Ok cool. I thought that's what it might be.

How would I determine the maximum amount of characters that can be stored in an nvarchar field, say of length 64?

Thanks Smile | :)

$TeVe McLeNiThAn

Re: SQL Server 2000 "length"

Bruce Duncan10-Mar-03 8:00

Bruce Duncan

10-Mar-03 8:00

A thought crossed my mind just as I posted my previous answer...

In Enterprise Manager, when you have an nvarchar type, with a length of 50, it means it can store 50 unicode characters, and therefore take up a maximum of 100 bytes. Hence, my previous answer is not entirely correct.

To answer your new question, an nvarchar field of length 64 can hold up to 64 unicode character, with a byte size of up to 128 (+ minimal overhead I guess)

Bruce Duncan, CP#9088, CPUA 0xA1EE, Sonork 100.10030
Blackadder: Baldrick, have you no idea what irony is?
Baldrick: Yeah, it's like goldy and bronzy only it's made of iron.

Re: SQL Server 2000 "length"

Steve McLenithan10-Mar-03 8:02

Steve McLenithan

10-Mar-03 8:02

Thanks. That helps.... I didn't want to make fields too short nor too big;)

$TeVe McLeNiThAn

Re: SQL Server 2000 "length"

Bruce Duncan10-Mar-03 8:12

Bruce Duncan

10-Mar-03 8:12

Cheers, glad to help Smile | :)

Bruce Duncan, CP#9088, CPUA 0xA1EE, Sonork 100.10030
Blackadder: Baldrick, have you no idea what irony is?
Baldrick: Yeah, it's like goldy and bronzy only it's made of iron.

Build dynamic query within a stored procedure?

Duncan Edwards Jones10-Mar-03 6:45

Duncan Edwards Jones

10-Mar-03 6:45

I have a situation whereby I need to modify a base query with an unknown number of additional restraints which are themselves held in a reference table. How would I go about this?

For example the reference table might hold:

"user_name <> 'default'"
"policy_no > 20000"

etc.

and my stored proc needs to add these conditions to its select operation. Ideas?

'--8<------------------------
Ex. Datis:
Duncan Jones
http://www.merrioncomputing.com

Re: Build dynamic query within a stored procedure?

Bruce Duncan10-Mar-03 7:43

Bruce Duncan

10-Mar-03 7:43

I'm assuming you're using SQL Server.

Look up the sp_executesql system stored proc or the EXECUTE ( ) statement, both will let you execute a statement contained within a varchar variable.

Bruce Duncan, CP#9088, CPUA 0xA1EE, Sonork 100.10030
Blackadder: Baldrick, have you no idea what irony is?
Baldrick: Yeah, it's like goldy and bronzy only it's made of iron.

Re: Build dynamic query within a stored procedure?

Duncan Edwards Jones10-Mar-03 8:27

Duncan Edwards Jones

10-Mar-03 8:27

I'm using Sybase XI - but I'm sure there's an equivalent somewhere - but that'd mean I'd have to assemple the entire query as a varchar().

'--8<------------------------
Ex. Datis:
Duncan Jones
http://www.merrioncomputing.com

Re: Build dynamic query within a stored procedure?

Bruce Duncan10-Mar-03 8:42

Bruce Duncan

10-Mar-03 8:42

Sorry, don't know anything about Sybase, but I would imagine it has something similar.

Maybe this is what you're looking for ?

How can I execute dynamic SQL with ASE?[^]

Bruce Duncan, CP#9088, CPUA 0xA1EE, Sonork 100.10030
Blackadder: Baldrick, have you no idea what irony is?
Baldrick: Yeah, it's like goldy and bronzy only it's made of iron.

that dang datagrid...

stephen.hazel8-Mar-03 11:43

stephen.hazel

8-Mar-03 11:43

Okay... c# winforms ado.net

Can i size up columns using the resource editor thing?
Ahead of time before the app starts and forces the user
to resize all the columns each time?

And how do I hide a column?

Thanks.

...Steve

SQL server call back our application

Shah Shehpori7-Mar-03 22:22

Shah Shehpori

7-Mar-03 22:22

Dear friends,

I have an application developed in VC++ and it makes an active connection to SQL server using ADO. My application issues queries and in return SQL server sends me results.

Is it possible that SQL server 'talk to my application' i.e what i want is that whenever any row of a table is changed in SQL server by some other entity, SQL server inform my application of this change. Is it possible??

Re: SQL server call back our application

Hesham Amin8-Mar-03 11:03

Hesham Amin

8-Mar-03 11:03

Hi..
Seems you need to create a trigger in SQL Server that call an Extended Stored Procedure (made in vc++ for example)
this Stored Procedure can store a shared variable that your Application checks for it..

You can create a worker thread that is blocked until this variable changes and unlocked by the Extended Stored Procedure...

Anyway .. I love the idea of making SQL server talks to your Application..

tell us about any progress Smile | :)

this freakin adapter dealy???

stephen.hazel7-Mar-03 7:08

stephen.hazel

7-Mar-03 7:08

I'm new to c#, winforms and ado.net, so bear with me.

I've got a dumb lil checkbook table. I don't WANT to have an identity
column for a primary key, but i do to help out ado.net...

So I get SqlConnection, DataSet, and SqlDataAdapter's Fill/Update.
I haven't really messed much with the DataGrid, but got somethin goin on.
And I need help with the properties of this frassin brassin adapter.

I've been thru the adapter wizard, but hate the mess it leaves.

I know about the commandbuilder a bit, but don't know when it's needed
and when not.

1) c# to hide the dang identity column in my datagrid is?

2) can I lose the identity column? implications are?

3) What's the deal with the SqlDataAdapter properties???
I guess tackin on a CommandBuilder is easiest, but you gotta have
a single primary key, right?
Otherwise, what do I do to fill in the sel/ins/upd/del command props?

Thanks VERY much for ANY help Rose | [Rose]

...Steve

Determining SQL Datatypes

AustinTom7-Mar-03 3:52

AustinTom

7-Mar-03 3:52

Hi folks,

From a dataset (generated by Data.SqlClient rather than Data.OleDb) can I determine the original SQL Datatype? I am trying to determine if a field is Currency vs. a straight decimal. It seems that the "datatype" property of the datacolumn object return decimal for both SQl currency and SQL decimal.

Thanks

Tom K.

Help: Save data back to MS Access DB

DionChen6-Mar-03 10:55

DionChen

6-Mar-03 10:55

Hi, all,

I am very close to finish my first .NET application( C#, Winform Datagrid, and MS Access database). I can modify all the data in my DataSet on my winform datagrid. NowI am trying for the first time to save the data back to my Access DB. When I call daMyDataAdaptor.Update( for a single table), I got this attached error. It says the UPDATE statement has syntax error, but the Statement was created by the wizard when I configured DataAdaptor, how can it be wrong? I know this must be something I missed, Please help.

Due to size limitation of the attachment, I can not attach the small test program. If you can help me, I can sent you the zip test program.

Code on Save button as follows:
------------------------------------

if ( connMain.State != ConnectionState.Open )
{
connMain.Open();
}

System.Data.OleDb.OleDbTransaction trans = connMain.BeginTransaction();

daTaskList.UpdateCommand.Transaction = trans;

try
{
daTaskList.Update(dsMain1, "LMTaskList");

//System.Exception MyErr = new System.Exception("Error Occurred");
//throw(MyErr);

trans.Commit();
}

catch(System.Exception err)
{
trans.Rollback();
MessageBox.Show("Failed to save Available Tasks!\n" + "Details: " + err.ToString() );
}

finally
{
connMain.Close();
}
-----------------------------------------------------

I wonder what is wrong with the code I have.

Thanks again.

Dion

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