Simple Insert Statement - Database Discussion Boards

Santanu Lahiri26-Apr-04 12:06

26-Apr-04 12:06

I need to create a series of linked tables in an Access database using ADOX.
I have the ADOX files from C. Antollini's article.
I tried the following code, everything seems to work okay until the actual append call.

// cat is a catalog defined as per ADOX.h
// ConnectString is "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=%s;Persist Security Info=False"

CString sTbl = dlgLst.m_sTable;

CString sConnectSrc, sConnectTrg;
sConnectSrc.Format (ID_ADO_CONNECTSTR, m_sDBFile);
sConnectTrg.Format (ID_ADO_CONNECTSTR, sDBFile);
LPCTSTR lpTblName = sTbl;
LPCTSTR lpConnectSrc = sConnectSrc;
LPCTSTR lpConnectTrg = sConnectTrg;

CADOXCatalog cat;
cat.Open (sConnectSrc); // this is the Database in which the table definision is to be created

_TablePtr tbl;
tbl.CreateInstance(__uuidof(Table)); // Create the instance of a table
tbl->PutName (lpTblName); // give it a local name
tbl->put_ParentCatalog(cat.m_pCatalog); // define the parent catalog

// some debugging info, could also use TRACE
AfxMessageBox (sConnectTrg);
AfxMessageBox (sConnectSrc);

tbl->Properties->GetItem(_T("Jet OLEDB:Create Link"))->Value = true; // make it a linked table
tbl->Properties->GetItem(_T("Jet OLEDB:Link Datasource"))->Value = lpConnectTrg;
tbl->Properties->GetItem(_T("Jet OLEDB:Remote Table Name"))->Value = lpTblName;

// Upto this point everything executes. At least, I do not get any complaints

try
{
cat.m_pCatalog->Tables->Append (_variant_t((IDispatch *)tbl));
cat.m_pCatalog->Tables->Refresh();
}
catch(_com_error &e)
{
TCHAR szBuf[300];
// Notify the user of errors if any.
_bstr_t bstrSource(e.Source());
_bstr_t bstrDescription(e.Description());
sprintf(szBuf, "\n\tSource : %s \n\tDescription : %s \n ", (LPCSTR)bstrSource,(LPCSTR)bstrDescription);
CString sErr = CString (szBuf);
AfxMessageBox (sErr);
}

This is causing an error saying "Not a Valid File Name" from the JET engine. The two file names are correct, and the files are both valid databases. Also, the table to be linked exists in the database being linked to.

I am using VC6 and MDAC 2.8.

Any pointers would be most welcome.

Followup: How to create linked tables using ADOX?

Santanu Lahiri29-Apr-04 10:13

Santanu Lahiri

29-Apr-04 10:13

Okay, I goofed up...

The correct value for the Jet OLEDB:Link Datasource is just the name of the database, not the connection string.

Oh yes, this will work only for linking to Access Databases. If you are trying to link to a non-Access data source, you need to fill in the "Jet OLEDB:Link Provider String" instead of the Data source. I found this little tidbit at this location:

MSDN Page on creating linked tables via ADOX

Restore database

tmcafee26-Apr-04 8:40

tmcafee

26-Apr-04 8:40

I have an application that allows people to import data and make changes to a database. I have been told by my boss that I need to add the ability to undo a data import/change from my application. What I was thinking of doing was backing up or copying the database when the application starts and then allowing the user to restore this backup/copy. Is this the best way to do it? I know sql server creates a backup everytime you make a change to the database. I don't know where to start or even how to write the code to do this (should it be a stored procedure...?). Any help would be great. I am using C#. Or is there a way to just do an undo from the transaction log?

Re: Restore database

Mazdak26-Apr-04 20:52

Mazdak

26-Apr-04 20:52

I don't think you need to backup when your application start. Just set proper recovery option for your database. See online help for SQLServer to see which one suit you , then just run RESTORE command from your appliaction whith SqlCommand class. There are examples of using this class in this site/MSDN/googling. This way you could recover to different time and many optionn you can use. Another way which I think is better is using SqlTransaction class . For sample and how to use it see SqlConnection.BeginTransaction() in MSDN. You can set different point and restore to them in your application.

Mazy

"One who dives deep gets the pearls,the burning desire for realization brings the goal nearer." - Babuji

Import Oracle *.DMP into MS-SQL (without Oracle DB Server)

johncogan26-Apr-04 3:45

johncogan

26-Apr-04 3:45

Hi
I am having no luck in finding some way (or tool) that will allow me to import an Oracle DMP file into SQL DB or convert the file into some form that will allow me to recreate the Oracle DB on the SQL DB (Only the schema is needed, so not working with data)

Any help in this regard is greatly appreciated.
Ta
John

Re: Import Oracle *.DMP into MS-SQL (without Oracle DB Server)

RichardGrimmer26-Apr-04 3:56

RichardGrimmer

26-Apr-04 3:56

Best method to get the schema (and the data for that matter) would be to use a DTS task....Simply set Oracle as the source, SQL as the destination, and elect to import ALL objects....

"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox

Re: Import Oracle *.DMP into MS-SQL (without Oracle DB Server)

Steve S28-Apr-04 1:31

Steve S

28-Apr-04 1:31

Except that DTS will talk to ORACLE, not a DMP file, so if you don't have ORACLE client installed, this won't work.

Rich's suggestion is a good one, though.^*

Steve S
^*I can't believe I just wrote that

Simple Insert Statement

dabuskol25-Apr-04 23:09

dabuskol

25-Apr-04 23:09

Guys,

Pls, Kindly try this insert statement and tell me what's the problem. Everytime I insert this data it gave me an error message Server: Msg 511, Level 16, State 1, Line 1 Cannot create a row of size 9113 which is greater than the allowable maximum of 8060.The statement has been terminated.

code:
create table tmplog
(
strlog varchar(8000)
)
;
insert into tmlog
values ('START OF CU-0 RECOVERYPRM_0 E000 0300 0000 0000 79 E8 00PRM_0 E141 DD00 5600 0BD0 79 E8 00PRM_0 E241 DD00 5600 0BD0 79 E8 00PRM_0 E341 DD00 5600 0BD0 79 E8 00PRM_0 E441 DD00 5600 0BD0 79 E8 00PRM_0 E541 DD00 5600 0BD0 79 E8 00PRM_0 E541 DD00 5600 0BD0 79 E8 00PRM_0 E641 DD00 5600 0BD0 79 E8 00PRM_0 E941 DD00 5600 0BD0 79 E8 00PRM_0 E941 DD00 5600 0BD0 79 E8 00PRM_0 E841 DD00 5600 0BD0 79 E8 00PRM_0 E941 DD00 5600 0BD0 79 E8 00PRM_0 E941 DD00 5600 0BD0 79 E8 00PRM_0 EA41 DD00 5600 0BD0 79 E8 00PRM_0 EA41 6400 0000 0000 79 E8 00PRM_0 E841 0001 1303 474C 79 E8 00PRM_0 E841 0001 2503 474C 79 E8 00PRM_0 EE41 DD00 5600 0BD0 79 E8 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EE41 5400 0000 0000 79 E8 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EE41 5001 6200 0000 79 E8 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EE41 4000 7400 0001 79 E8 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EE41 EE13 0014 0001 79 E8 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EE41 6074 0420 0000 79 E8 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EE41 6074 0410 0000 7A E8 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EC41 DD00 5600 0BD0 79 E8 00CU RECOVERY COMPLETEPRM_0 EB41 5000 0000 001E 7A 68 00PRM_0 EE41 6074 0430 0000 79 68 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 E841 0001 0403 474C 79 68 00PRM_0 EE41 0600 0000 0000 79 68 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EE41 02E7 401C FFFF 7A 68 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EE41 6074 0410 0100 7A 68 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EE41 6074 0430 0100 79 68 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EE41 6074 0430 0200 79 68 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EE41 0211 040C FFFF 79 68 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EE41 0240 A410 0000 7A 68 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EE41 6074 0460 0000 79 68 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EE41 0240 6430 0000 79 68 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EE41 6074 0460 0100 79 68 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EE41 6074 0460 0500 79 68 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EE41 6074 0455 0000 79 68 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EE41 0240 7440 0000 79 68 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EE41 6074 07E9 0000 79 68 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EE41 0222 64AC FFFF 79 68 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EE41 6074 0410 0200 79 68 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EE41 6074 0430 0300 79 68 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EE41 0215 0000 000A 79 68 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EE41 0225 0400 000A 79 68 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EE41 6074 0410 0300 79 68 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 E841 0001 0D03 474C 79 68 00PRM_0 E841 0001 1C04 6414 79 68 00PRM_0 EE41 6074 0450 0000 79 68 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EE41 6074 0450 0100 79 68 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 E841 0001 2804 6414 79 68 00PRM_0 E841 0001 2D03 474C 79 68 00PRM_0 EE41 6074 0430 0400 79 68 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EE41 0217 0000 0000 79 68 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EE41 0227 0000 0060 7A 68 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EE41 6074 0410 0400 79 68 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EE41 6074 0430 0500 79 68 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EE41 6074 0410 0500 79 68 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 E841 0001 1504 6414 79 68 00PRM_0 E841 0001 2703 474C 7A 68 00PRM_0 E841 0001 1803 474C 7A 68 00PRM_0 E841 0001 1003 474C 7A 68 00PRM_0 E841 0001 2804 6414 79 68 00PRM_0 EE41 0800 0000 0000 79 68 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EE41 0100 0000 0000 79 68 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 E841 0001 2F04 6414 79 68 00PRM_0 E841 0001 0703 474C 79 68 00PRM_0 E841 0001 2403 474C 79 68 00PRM_0 E841 0001 2C04 6414 79 68 00PRM_0 EE41 0700 0000 0000 79 68 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EE41 C000 0000 0E42 79 68 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 E841 0001 2403 474C 79 68 00PRM_0 EE41 0100 0000 0000 79 68 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EE41 0100 0000 0000 79 68 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EE41 0100 0000 0000 79 68 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EE00 0100 0000 0000 79 68 00 APPLICATION PRM -- CDOC PRM DESCRIPTIONPRM_0 EB00 60C6 0000 0202 79 68 00 ')

please,please,please
I word count the entire data en it only gives me 4535 characters with spaces any my variable is varchar(8000). I know I overlooked somthing but I can't find.

ASAP;
/Dabsukol

Re: Simple Insert Statement

Peet Schultz26-Apr-04 3:19

Peet Schultz

26-Apr-04 3:19

1) Check your spelling. You are creating table tmplog and inserting into tmlog.
2) What version of SQL are you using? With the table name correct this works fine on SQL server 2000 sp3

Regards

YASP

Re: Simple Insert Statement

Janya26-Apr-04 14:34

Janya

26-Apr-04 14:34

This may not help much, but any chance there is a double or single quote in your string that SQL is seeing as a statement terminator, but that is getting stripped before we see your nice post?

Janya

Encrypted Tables From ASP.NET & Access

MKlucher24-Apr-04 14:42

MKlucher

24-Apr-04 14:42

I've built an ASP.NET application in C# that takes information and encrypts this information and puts it into the database. One of the people who works with the system would like to be able to use Access to run querys on the database and return the information, While I don't think tying into an MSSQL database with access is hard, I don't know if there is any clever way to decrypt the data. Can my C# module somehow be used in Access to do this or is there even a way at all? I don't know a ton about enchanced SQL stuff but I know there are stored procedures that can do encryption or can be written for them or something like that, but I would like to try to avoid doing a re-write of all my stuff if it's possible...

Thanks!

Data Grid / update when going to next record

xstoneheartx23-Apr-04 13:24

xstoneheartx

23-Apr-04 13:24

Hi I want to update my DataBase (using a data grid) when going to next record...
how can i do it?
tanx

Datagrid Multi-table update

Anonymous23-Apr-04 7:03

Anonymous

23-Apr-04 7:03

Hi! Is it possible to do multi-table update in one datagrid?

Minimum of 2 values

Colin Angus Mackay22-Apr-04 12:05

Colin Angus Mackay

22-Apr-04 12:05

Hello,

Is there anything in SQL Server 2000 that I can use to perform a calculation returning the minimum of 2 values. For example:
SET @value = min(@a, @b)

I realise that there is a min() aggregate function, but I don't want that as I am wanting to use this in a computed column. (I have a value calculated from other fields, but must be capped at some limit)

Any ideas?

"You can have everything in life you want if you will just help enough other people get what they want." --Zig Ziglar

Coming soon: The Second EuroCPian Event

Re: Minimum of 2 values

Mike Ellison23-Apr-04 5:46

Mike Ellison

23-Apr-04 5:46

Hi Colin. How about building your own function?

CREATE FUNCTION MinValue(@val1 decimal, @val2 decimal)
RETURNS decimal
AS
BEGIN   
  declare @retValue decimal;

  If (@val1 < @val2)      
    set @retValue = @val1
  Else      
    set @retValue = @val2

  Return(@retValue);
END

Re: Minimum of 2 values

Colin Angus Mackay23-Apr-04 5:54

Colin Angus Mackay

23-Apr-04 5:54

Yeah! I've done something similar to that - I was just hoping that I'd missed a nice piece of documentation that says there is a built in function for it.

C'est la vie.

"You can have everything in life you want if you will just help enough other people get what they want." --Zig Ziglar

Coming soon: The Second EuroCPian Event

Re: Minimum of 2 values

Mike Ellison23-Apr-04 5:54

Mike Ellison

23-Apr-04 5:54

Well, if you missed it, so did I Smile | :)

Re: Minimum of 2 values

Peet Schultz26-Apr-04 3:41

Peet Schultz

26-Apr-04 3:41

Hi There

Please try this (Not sure if this is what you are looking for:

if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[Table1]') and OBJECTPROPERTY(id, N'IsUserTable') = 1)
drop table [dbo].[Table1]
GO

CREATE TABLE [dbo].[Table1] (
[a] [int] NULL ,
[b] [int] NULL ,
[c] AS (isnull(case when (isnull([a],0) > isnull([b],0)) then (isnull([b],0)) else (isnull([a],0)) end,0))
) ON [PRIMARY]
GO

insert into [table1] values (1,3)
insert into [table1] values (22,3)
insert into [table1] values (1,1)

select [a],[b],[c] from [table1]

Selecting [c] seems to always return the smallest of [a] or [b]

Regards
Peet Schultz

YASP

Rename a table

suhredayan22-Apr-04 0:27

suhredayan

22-Apr-04 0:27

Is there any way to rename a table through t-sql, other than using sp_rename.

It's not a bug, it's an undocumented feature.
suhredayan@omniquad.com

messenger :suhredayan@hotmail.com

Re: Rename a table

suhredayan22-Apr-04 0:29

suhredayan

22-Apr-04 0:29

i want to do it on MS-Sql server.

It's not a bug, it's an undocumented feature.
suhredayan@omniquad.com

messenger :suhredayan@hotmail.com

Re: Rename a table

Mazdak22-Apr-04 0:39

Mazdak

22-Apr-04 0:39

Look for sp_rename in SQLServer help.

Mazy

"One who dives deep gets the pearls,the burning desire for realization brings the goal nearer." - Babuji

Re: Rename a table

22491722-Apr-04 0:55

224917

22-Apr-04 0:55

i think you have not read my question clearly. i was looking for any sql statement not for a stored procedure. cos i want to do it in access2000.

It's not a bug, it's an undocumented feature.
suhredayan@omniquad.com

messenger :suhredayan@hotmail.com

Re: Rename a table

Mazdak23-Apr-04 1:47

Mazdak

23-Apr-04 1:47

Yes , but in your second post you have mentioned you want to do it in MSSQLServer.

Mazy

"One who dives deep gets the pearls,the burning desire for realization brings the goal nearer." - Babuji

Re: Rename a table

RichardGrimmer26-Apr-04 3:58

RichardGrimmer

26-Apr-04 3:58

AKAIK it's not possible in Access without drop / create.....(though SteveS may have a view)

"Now I guess I'll sit back and watch people misinterpret what I just said......"
Christian Graus At The Soapbox

Oracle Dbase By c#

unitecsoft21-Apr-04 22:43

unitecsoft

21-Apr-04 22:43

Hi

I have an application where I have to open oracle database read some data and update the tables using C# and ADO.Net . I will use OLE Oracle connection for that . The problem is that I have no Idea about oracle , never used it . How are oracle files named , what are the parameteres I have to pass in my connection to be able to open the Oracle database , access the tables then update it again .

" We have either to find a way , or make one "
Hanipal

Dean

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