Database

Re: ConnectionString Problem (Urgent HELP required)

1-Mar-08 20:08

hello 2 all

the problem that I am facing these days is that I am using oleDbConnection in my application. the database that I am using in my application resides in the solution folder.

everytime I take this application to other computer, I have to change the connection string which I dont want to

how can I overcome this problem. Cry | :((

I will appreciate if someone help me in resolving the issue.

Thanks in advance Big Grin | :-D

Sadaf

pmarfleet1-Mar-08 23:00

Re: ConnectionString Problem (Urgent HELP required)

1-Mar-08 23:00

First of all, don't mark your posts as URGENT. You're not paying anyone for their help, so be prepared to wait.

You haven't stated what type of database you are using. Presumably it is file-based, Access maybe?

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

Sadaf Naeem1-Mar-08 23:59

Re: ConnectionString Problem (Urgent HELP required)

1-Mar-08 23:59

I am really sorry if I sound rude but that was not my purpose to mark my post URGENT . the only purpose to do that was I have to show it to my supervisor and I just want the people to know that I want their help instantly but I AM REALLY VERY SORRY Frown | :(

I will make sure it never happens again

I am using MS ACCESS 2003...

thanks 4 ur kind help

Sadaf

pmarfleet2-Mar-08 4:08

Re: ConnectionString Problem (Urgent HELP required)

2-Mar-08 4:08

Connection strings for Access require an absolute path to the database file. You will need to construct connection string with the path to the database file yourself in code. You can get the path that your application is executing from by calling Environment.CurrentDirectory. Then append the name of your Access database file.

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

Sadaf Naeem2-Mar-08 16:48

Re: ConnectionString Problem (Urgent HELP required)

2-Mar-08 16:48

Thanks a lot I will try it today Big Grin | :-D

Sadaf

Sadaf Naeem2-Mar-08 18:02

Re: ConnectionString Problem (Urgent HELP required)

2-Mar-08 18:02

thanks 4 ur kind help

I tried it but not there is a 2nd problem which is as follows:

my code is:

string currentDirectory = System.Environment.CurrentDirectory;
OleDbConnection con = new OleDbConnection();

con.ConnectionString = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source='" + currentDirectory + "\\dbLetters.mdb';Persist Security Info=False";

this.oleDbInsertCommand1.Connection = con;

//other parameters

this.oleDbInsertCommand1.Parameters["Subject"].Value = this.txtSubject.Text;
this.oleDbInsertCommand1.Parameters["RefNumber"].Value = this.txtRefNumber.Text;
this.oleDbInsertCommand1.Parameters["RefDate"].Value = this.dateTimePicker1.Value;
this.oleDbInsertCommand1.Parameters["OutwardNumber"].Value = this.txtOutwardNumber.Text;
this.oleDbInsertCommand1.Parameters["OutwardDate"].Value = this.dateTimePicker2.Value;
this.oleDbInsertCommand1.Parameters["FileNumber"].Value = this.txtFileNumber.Text;
this.oleDbInsertCommand1.Parameters["Remarks"].Value = this.txtRemarks.Text;

con.Open();
this.oleDbInsertCommand1.ExecuteNonQuery();
con.Close();

-------------------------------

Upon execution , it is not creating a new record but always updating the last record and I have no clue to why is this happening. Frown | :(

Plz guide me . I shall be really thankful 2 u

Sadaf

Sadaf Naeem2-Mar-08 18:04

Re: ConnectionString Problem (Urgent HELP required)

2-Mar-08 18:04

INSERT INTO tblOutgoingLetters
([To], [From], Subject, RefNumber, RefDate, OutwardNumber, OutwardDate, FileNumber, Remarks)
VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?)

this is the command that I have in the CommandText property of Command object..but instead of inserting a new record into the table , it is changing the content i.e. updating the last record

Do u have any idea about why is this problem occurring?

Sadaf

Sadaf Naeem2-Mar-08 20:30

Re: ConnectionString Problem (Urgent HELP required)

2-Mar-08 20:30

EVERY TIME I OPEN THE SOLUTION FILE TO MAKE SOME CHANGES IN MY PROJECT , THE RECORDS ADDED AFTER USING ENVIRONMENT.CURRENTDIRECTORY ARE DELETED . HOWEVER THE RECORDS THAT WERE INSERTED INTO THE DATABASE BEFORE I USED THIS REMAIN AS IT IS.

WHAT COULD BE THE CAUSE? Confused | :confused:

Sadaf

pmarfleet2-Mar-08 22:04

Re: ConnectionString Problem (Urgent HELP required)

2-Mar-08 22:04

Please don't shout. Pay attention to forum rule no. 6

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

Khawar Abbas13-Mar-08 2:28

Khawar Abbas1

3-Mar-08 2:28

This is actually a problem with you program logic. Insert statement can never update another record. So check your logic may be on insertion the program calls the update function.

Do good and have good.

how to store a *.jpg file in oracle?

sejal_tank1-Mar-08 8:02

sejal_tank

1-Mar-08 8:02

i want to store a jpg file in oracle and want to access from vb through ado connection.

Re: how to store a *.jpg file in oracle?

followait1-Mar-08 8:18

Re: how to store a *.jpg file in oracle?

1-Mar-08 8:18

Hope it helps.

BOOL CMyDoc::ImportPic(LPCTSTR filePath)
{
	CFile file;
	file.Open(filePath,CFile::modeRead);
	DWORD len=file.GetLength();
	BYTE *pBuf=new BYTE[len+1];
	file.ReadHuge(pBuf,len);
	
	VARIANT			varBLOB;
	SAFEARRAY		*psa;
	SAFEARRAYBOUND	rgsabound;
	
	_RecordsetPtr pRs;
	pRs.CreateInstance(__uuidof(Recordset));
	pRs->Open(_T("picdata"),m_pConn.GetInterfacePtr(),adOpenStatic,adLockOptimistic,adCmdTable);
	pRs->AddNew();
	pRs->PutCollect(_T("name"),_T("a"));
	rgsabound.lLbound=0;
	rgsabound.cElements=len;
	psa=SafeArrayCreate(VT_UI1,1,&rgsabound);
	for (LONG i=0;i<(LONG)len;++i)
		SafeArrayPutElement(psa,&i,pBuf++);
	varBLOB.vt=VT_ARRAY|VT_UI1;
	varBLOB.parray=psa;
	pRs->GetFields()->GetItem("pic0")->AppendChunk(varBLOB);
	pRs->Update();
	pRs->Close();
	pRs.Release();
	return TRUE;
}

Paul Conrad1-Mar-08 12:32

Paul Conrad

1-Mar-08 12:32

Why did you do it in C++ when the OP is using VB?

Re: how to store a *.jpg file in oracle?

followait1-Mar-08 16:48

How to optimize the SQL command ...

1-Mar-08 16:48

Yes, only the ado part may help.

followait1-Mar-08 7:53

1-Mar-08 7:53

IF EXIST(SELECT 1 FROM t where name='abc' ADN age=20 AND job='worker')
UPDATE t set num=num+1 WHERE name='abc' ADN age=20 AND job='worker'
ELSE INSERT INTO t(id,name,age,job,num) VALUES(NEWID(),'abc',20,'worker',1)

Paul Conrad1-Mar-08 12:32

Paul Conrad

1-Mar-08 12:32

What is your question?

"I guess it's what separates the professionals from the drag and drop, girly wirly, namby pamby, wishy washy, can't code for crap types." - Pete O'Hanlon

followait1-Mar-08 16:48

1-Mar-08 16:48

See the title.

Paul Conrad2-Mar-08 5:17

Paul Conrad

2-Mar-08 5:17

Not sure where you can optimize it. Looks pretty tight. Why do you think it needs optimizing? Is it taking a long time to run?

"I guess it's what separates the professionals from the drag and drop, girly wirly, namby pamby, wishy washy, can't code for crap types." - Pete O'Hanlon

followait2-Mar-08 5:44

2-Mar-08 5:44

Yes.
Two conditions duplicates.

pmarfleet2-Mar-08 6:08

2-Mar-08 6:08

I don't think you can improve this. You can't escape duplicating the WHERE clause in the EXISTS and UPDATE queries. It's not really duplication anyway - the UPDATE query is only fired under certain conditions.

Paul Marfleet

"No, his mind is not for rent
To any God or government"
Tom Sawyer - Rush

andyharman2-Mar-08 23:36

andyharman

2-Mar-08 23:36

The following will remove the need for the "if exist" statement:

UPDATE t SET num=num+1
  WHERE name='abc'
  AND age=20
  AND job='worker'
IF (@@ROWCOUNT = 0) BEGIN
  INSERT INTO t(id,name,age,job,num)
     VALUES(NEWID(),'abc',20,'worker',1)
END

Additionally you should look at your indexes. I would expect something like:

CREATE UNIQUE INDEX t_idx1 ON t (name, age, job)

I would recommend looking at the other SQL that use this table, then change the order of the columns in the above index to best-suit their requirements.

Regards
Andy

followait3-Mar-08 4:19