Re: SQL query required - Database Discussion Boards

Brian Ngo15-Sep-03 4:11

15-Sep-03 4:11

hey guys, sort of a general-ish implementation question here:

in this project i'm working on, i have a MFC/ADO program working with MS Access databases for an art firm. being an art firm, the database works with images, and i somehow need to embed those images into the database. so far i have a field with an "OLE Object" type, because that was how the firm has done it in the past (insert the image as an ole object). to me it sounds ideal because then i can insert and view the thumbnail associated with the entry a lot easier than if it was stored externally in a file, and it'd be much easier to move the database around. however, i cant seem to figure out how to interact with OLE Object types.

can anyone point me in the general direction of how i can insert images and extract and view them if they're stored as an OLE Object in an access db? or is the idea bogus and there's an easier implementation that my feeble mind hasn't thought of Poke tongue | ;-P

thanks for any help guys!

Re: handling images in MS Access db

Stefan Pedersen18-Sep-03 14:15

Stefan Pedersen

18-Sep-03 14:15

Quick tip:
Store the images in the filesystem instead. A shared folder on the workstation with the database itself will do fine. You will kill the database otherwise (not to mention that OLEObjects in Access is a real pain in the XXX to work with...).

rHow do I create new Datatable based on columns of datatable in datase

14-Sep-03 22:55

How do I create a datatable based on few columns from a datatable which contains n number of columns

For eg.,

I have a Datatable inside a dataset which contains following
columns say 'A', 'B', 'C', 'D' (the order of column may vary from difft source)

I need to send a datatable to function which requires only 'B' and 'D'. Based on this column, I use SqlDataadapter for update to the database.

This adapter requires ordering of the columns. That is the reason I am creating required table and sending it to the function.

For eg.,

l_sqlString = "SELECT SEGMENT_ID,MEMBER_ID,EMAIL_GRP_ID FROM EM_PROMO_GRP_MEM WHERE EMAIL_GRP_ID = 7";
SqlConnection l_objSqlConn = new SqlConnection(DAOConfigDataUtil.GetConnectionString(DatabaseType.SQL));
sAdapter = new SqlDataAdapter(l_sqlString,l_objSqlConn);

If I am right DataView can be used only to view particular columns from a datatable.

Please help me out.

Query Works in Access But Not in App

Matt Gates13-Sep-03 18:57

Matt Gates

13-Sep-03 18:57

I am new to DB programming in VC.

Any Idea why this SQL statement will not return any results in my program:

SELECT *
FROM Table
WHERE Field1 LIKE "*text*" AND Field2 LIKE "*txt*";

The query runs fine in Access 2002 but will not return any results in my VC app.

"Microsoft.Jet.OLEDB.4.0" is my provider when connecting to the Acesss DB. I tried to use "Microsoft Access Driver" but that throws an exception because it can not be found(???).

Thanks for any suggestions.

Matt (Padawan Learner)

Re: Query Works in Access But Not in App

Matt Gates13-Sep-03 19:24

Matt Gates

13-Sep-03 19:24

Wow, solved my own problem. For those interested, these articles helped me out:

ACC: Running Stored Access Queries with Wildcards via ODBC May Return Incorrect Results

OLE DB Provider for Microsoft Jet

Matt (Padawan Learner)

ADO SQL SERVER - invalid char value for cast specification error

haritadala13-Sep-03 1:40

haritadala

13-Sep-03 1:40

Hello all

I'm connecting to sqlserver using ADO and trying to execute a stored procedure. I'm getting an error like "invalid char value for cast specification error". I've the following code to execute the stored procedure.

setproc(int devid,char *cfgid,char *lmsgid)
{

try{

m_Command.CreateInstance(__uuidof(Command));

//using already created connection object
m_Command->ActiveConnection = m_Connection;
m_Command->CommandType = adCmdStoredProc;
m_Command->CommandText = "usp_IsIDExist";

_ParameterPtr pParam1, pParam2,pParam3;

pParam1=m_Command->CreateParameter(bstr_t ("DevID"), adTinyInt,adParamInput,sizeof (BYTE),_variant_t (long(devid)));

m_Command->Parameters->Append(pParam1);

pParam2=m_Command->CreateParameter(bstr_t ("CfgID"), adVarChar,adParamInput,1,(_bstr_t)cfgid);

m_Command->Parameters->Append(pParam2);

pParam3=m_Command->CreateParameter(bstr_t ("LastMsgID"), adVarChar,adParamInput,strlen(lmsgid),(_bstr_t)lmsgid);

m_Command->Parameters->Append(pParam3);

m_Recordset = m_Command->Execute( &vNull, &vNull, adCmdStoredProc);

}catch(com_error &e){}

}

I've the following strored procedure .....

CREATE PROCEDURE usp_IsIDExist
@DevID integer,
@CfgID char(1),
@MsgID varchar(255)
AS
BEGIN
IF (SELECT COUNT(*) FROM dbo.MsgLog WHERE DevID = @DevID AND CfgID = @CfgID AND MsgID = @MsgID) <> 0
SELECT Result = 1
ELSE
SELECT Result = 0
END

in msglog table devid -- int,cfgid -- char(1) and msgid -- varchar(255).

Is there anything wrong this code. I tried it on two machines, on one machine it worked where on another machine if failed giving me the above mentioned error. Both machines have the same database setup except the fact that one is win2000 server and another one is win2000 prof.

Can anyone help me in this?

Many thanks for your help.
Hari.

SQL query required

Shah Shehpori12-Sep-03 19:14

Shah Shehpori

12-Sep-03 19:14

Dear Friends,

Consider the table:

id country
=== =======
1 Australia
2 Burma
3 Canada
4 USA

I've an input string:

set @input = 'I live in Australia'

Now i need to find out that is there any substring of my input string present in the 'country' column of above table.

example, in my sample string: "I live in Australia", the substring 'Australia' is present in the table above. Thats what i like to know. So how can i make such SQL query ??

Re: SQL query required

Ian Darling13-Sep-03 6:26

Ian Darling

13-Sep-03 6:26

I would use a function that takes your string, and tokenises it into a table (which is returned), and then join onto your country table from there.

See http://www.algonet.se/~sommar/arrays-in-sql.html[^] for some useful info on how to do this.

--
Ian Darling
"The moral of the story is that with a contrived example, you can prove anything." - Joel Spolsky

Re: SQL query required

Nick Parker14-Sep-03 3:37

Nick Parker

14-Sep-03 3:37

I would assume you are using a stored procedure? The key elements in a query like this are the LIKE keyword and a % sign on both sides of your search variable. Something like the following should do it for you:

SELECT id, country FROM [YourTable] WHERE country LIKE '%' + input + '%'

HTH

-Nick Parker

SQL Server Setup

fume12-Sep-03 15:21

fume

12-Sep-03 15:21

... Is SQL Server ideal for client-server application using a peer-to-peer connection? What's the difference if there's a hub that would connect 2 computers rather than having a peer-to-peer connection?

Re: SQL Server Setup

Mike Epprecht13-Sep-03 5:39

Mike Epprecht

13-Sep-03 5:39

fume wrote:
... Is SQL Server ideal for client-server application using a peer-to-peer connection? What's the difference if there's a hub that would connect 2 computers rather than having a peer-to-peer connection?

The network makes no difference. SQL just uses the stack that the OS supplies. It does not care if you are connected via Satellite, 10mbit or cross-over.

Cheers

Mike
Johannesburg, South Africa

Re: SQL Server Setup

Rob Graham15-Sep-03 10:10

Rob Graham

15-Sep-03 10:10

Not entirely true...
SQL uses a choice of protocols, TCP-IP being one, Named Pipes another. If the OS does not provide one of the supported protocols, SQL would not be accessible outside the local node.
I doubt that it would work over a direct serial connect unless it were carring one of the suppoerted connection protocols.

Genius may have its limitations, but stupidity is not thus handicapped. - Elbert Hubbard

Trouble removing deleted rows...

james-cxx12-Sep-03 12:59

james-cxx

12-Sep-03 12:59

I have a DataSet that is filled by a SqlDataAdapter. If a row is deleted within the app, this is properly reflected in both the DataSet and central database when Update() is called on the SqlDataAdapter.

However, if records are deleted from the database by some other process (outside the application), and I subsequently call the Fill(dataset.table) method of the SqlDataAdapter, the rows that were deleted from the database still seem to exist in the DataSet.

Any ideas how I can ensure that if records have been deleted from the database, that these deletions are fully reflected in the DataSet?

-- James --

Re: Trouble removing deleted rows...

Rob Graham15-Sep-03 10:41

Rob Graham

15-Sep-03 10:41

The deleted rows are likely only visible in the deleted rows view (Datatable retains all your original rows unless you clear it first, or call datatable.acceptchanges after you call Fill to "refresh" the data. Same applies to modifications made by others. Strange behavior perhaps, but that how it's documented.

Genius may have its limitations, but stupidity is not thus handicapped. - Elbert Hubbard

Re: Trouble removing deleted rows...

james-cxx16-Sep-03 8:11

james-cxx

16-Sep-03 8:11

Calling AcceptChanges on the datatable after the Fill did not seem to remove the extra records. However, calling Clear on the table just before calling Fill does the trick. I was a little wary of using Clear for fear of deleting my database, but I see it only clears the table and doesn't mark records for deletion.

Thanks for the help!

-- James --

help, please!

yyf12-Sep-03 8:13

yyf

12-Sep-03 8:13

In Access, there's a field in my table: Count number;

I try to use it in my project like this:

DataRow row;<br />
<br />
		public string Count<br />
		{<br />
			get<br />
			{<br />
				if ( row != null && row["Count"] != null ) <br />
				{<br />
					return row["Count"].ToString();<br />
				}<br />
				return "";<br />
			}<br />
			set<br />
			{<br />
				if ( row != null ) <br />
				{<br />
					row["Count"] = ( value == "" ) ? null : value;				}<br />
			}<br />
		}

but when I set the do this: this.Count = ""; I got an exception. Acctually what I want to do here is same as "update count = null" in sql.

How can I fix that?

Thanks in advance.

VSS

yyf12-Sep-03 8:07

yyf

12-Sep-03 8:07

There is VSS in Visual Studio 6.0. But I can't find it in Visual Studio .Net

Anyone know why?

Thanks in advance.

Re: VSS

Tom Archer12-Sep-03 10:17

Tom Archer

12-Sep-03 10:17

I believe VSS is only in the Enterprise Edition of VS.NET (along with Visio) and needs to be explicitly installed

Cheers,
Tom Archer
Inside C#,
Extending MFC Applications with the .NET Framework
It's better to listen to others than to speak, because I already know what I'm going to say anyway. - friend of Jörgen Sigvardsson

Re: VSS

yyf12-Sep-03 11:03

yyf

12-Sep-03 11:03

But when I installed visual studio .Net, I chose fully installed. Is it isolated from VS.Net now?

Re: VSS

Tom Archer12-Sep-03 11:14

Tom Archer

12-Sep-03 11:14

In my (RTM) VS.NET Enterprise Edition CDs, VSS is on a CD by itself and, if I recall correctly, had to be installed separately.

Cheers,
Tom Archer
Inside C#,
Extending MFC Applications with the .NET Framework
It's better to listen to others than to speak, because I already know what I'm going to say anyway. - friend of Jörgen Sigvardsson

Re: VSS

Michael P Butler15-Sep-03 10:35

Michael P Butler

15-Sep-03 10:35

VS.NET integration with source-safe can be found on the File menu, under the Source-Control option.

I'm not sure if the installation is isolated from VS.NET as I did an upgrade so my Visual Source Safe is still under my Microsoft Visual Studio 6 start menu item.

Michael

'War is at best barbarism...Its glory is all moonshine. It is only those who have neither fired a shot nor heard the shrieks and groans of the wounded who cry aloud for blood, more vengeance, more desolation. War is hell.' - General William Sherman, 1879

Fill Missing Dates in Query for Reporting

Roland Bär12-Sep-03 4:22

Roland Bär

12-Sep-03 4:22

For Reports / Statistics I have a Table with the Log Data. The Problem is that for some days i have several entries and for others i have none. I can get the number of entries for one day with the count() and grouping the datepart()s, but then I don't have entries for the days that havn't any Log Data.
From these Data I have to build a table report (one table per month, with the days listed horizontaly and the type of entry vertically (also grouped). This looks not very nice, if you only have 4 days in a month ...
Even worse I have to build a Chart, where this looks very strange when you have only some days in a month.
The Reports and Charts I build with Cristal Reports for .NET.

Is there a Solution in the SQL Server or maybe in the Cristal Reports?

Thanks

Roland

DatatAdapter Update method and Transactions question

Risbom12-Sep-03 1:57

Risbom

12-Sep-03 1:57

Is it possible to isolate more than 1 DataAdapers Update into single ADO.NET transaction? I want to insert sam rows in aplications log table, but only if previous Update was ok. And if log insert fail, I want to roll back whole transaction. When trying to implement it, I got feeling that DataAdapter Update method itself call Commit. If it is not possible, any idea aboat roundtrip? Thanks,

Risbom

Getting SQL Server to generate code for objects

OMalleyW11-Sep-03 4:51

OMalleyW

11-Sep-03 4:51

The following code will allow you to have SQL Server script objects with out using Enterprise Manager.

I found this code at
http://www.sqlmag.com/Forums/messag...&threadid=18602

Enjoy

Here is what ya do.
in this code I am scripting all the Procedures.
-----------------------------------------------------------------------
declare @cmd2 varchar(255)
declare GetProcedures cursor fast_forward
for
select 'sp_helptext [' + name + ']' from sysobjects where type = 'P'
open GetProcedures
fetch next from GetProcedures into @cmd2

while @@fetch_status = 0
begin
exec (@cmd2)
print 'GO'
fetch next from GetProcedures into @cmd2
end

close GetProcedures
deallocate GetProcedures
-------------------------------------------------------------------

This works Just change the type to match what ever you want to script.
If you are not sure of the Type then run this first

SELECT * FROM SYSOBJECTS

William O'Malley

Shrinkwrap database deployment

Michael P Butler11-Sep-03 2:18

Michael P Butler

11-Sep-03 2:18

All my previous projects have involved me being on site to install my software. That meant I could create a SQL script of my database structure and use query Analyzer to run the script and create the database tables.

However, I know find my self in the position of doing a "shrinkwrap" piece of software where the user will be installing the software.

What is the recommended way of creating an SQL server database? I've thought about writing an executable that runs an SQL script but wonder if there are better ways.

Michael

'War is at best barbarism...Its glory is all moonshine. It is only those who have neither fired a shot nor heard the shrieks and groans of the wounded who cry aloud for blood, more vengeance, more desolation. War is hell.' - General William Sherman, 1879

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