Database Discussion Boards

new_phoenix12-Oct-07 6:59

12-Oct-07 6:59

Is there a way to make a database connection more dynamic, so that the database could be moved to a different directory and still function without requiring the need to change the path in the database code itself? The code I have so far is static and requires changing each time the MS Access file moves or is moved to a different directory.

Dim strDBPath As String
strDBPath = "J:\GELCO DATABASE\Headcount Database\Headcount Database.mdb"
Set dbsHeadcount = OpenDatabase(strDBPath)

Re: Dynamic database pathways...

Hesham Amin12-Oct-07 8:46

Hesham Amin

12-Oct-07 8:46

usually, connection strings, paths, etc are stored in configuration files so changing them does not require changing code.
You should adopt this practice in general.

Hesham A. Amin
My blog

Re: Dynamic database pathways...

new_phoenix15-Oct-07 10:41

new_phoenix

15-Oct-07 10:41

Could you explain where the configuration file is in Microsoft Access? I understand in a web application, there is a configuration file, but what about in a networked version of MS Access?

SQL statement on website, global password

Cory Kimble12-Oct-07 6:57

Cory Kimble

12-Oct-07 6:57

I inherited a job including updating website. the website has a login page. When a client logs in the page will show all there orders. Our staff has to also go in and view there orders. The way it is set up know is the staff member has to look up each clients user id and password in order to view orders. I want a quick and dirty way to allow the staff member to use a global password to get into any account. How would I do this. The page offers a SQL statement that grabs the info. Found here

"SELECT * FROM WSUSER WHERE U_ID='"+ REQUEST.QueryString("U_ID") +"' AND U_PASSWORD ='"+ REQUEST.QueryString("U_PASSWORD")  +"'"

Is there a way to change this to also grab the record if U_PASSWORD is a Global password like PASSWORD?

Re: SQL statement on website, global password

andyharman12-Oct-07 8:18

andyharman

12-Oct-07 8:18

Hi Cory

Try entering

' or '' = '

as the password .... then read Angus' article about SQL Injection attacks[^]. If you still want a global password then tack the following to the end of your SQL.

OR U_PASSWORD = 'MyGlobalPassword'

Regards
Andy

Re: SQL statement on website, global password

Cory Kimble15-Oct-07 4:21

Cory Kimble

15-Oct-07 4:21

When I put OR U_PASSWORD = 'MyGlobalPassword' at the end of the SQL statement it allows all passwords to work even blank ones. In the following where do I place this at?

SQLS="SELECT * FROM WSUSER WHERE U_ID='"+ REQUEST.QueryString("U_ID") +"' AND U_PASSWORD ='"+ REQUEST.QueryString("U_PASSWORD")  +"'"

Re: SQL statement on website, global password

andyharman15-Oct-07 8:29

andyharman

15-Oct-07 8:29

Hi Cory

Try:

SQLS = "SELECT * FROM WSUSER " & _
   "WHERE U_ID = @U_ID " & _
   "AND @U_PASSWORD IN (U_PASSWORD, 'MyGlobalPassword')

then setup a SqlCommand with SqlParameters for @U_ID and @U_PASSWORD. That will remove your SQL injection problem.

You will probably find that posting to the page using Request.Form will be more secure that Request.QueryString too.

Regards
Andy

If you want to thank me for my help, please vote my message by clicking one of numbers beside "Rate this message".

a problem about stackoverflow

nibabug12-Oct-07 3:27

nibabug

12-Oct-07 3:27

void CSerialRecieveDlg::OnBtnAutosend()
{
// TODO: Add your control notification handler code here
if(!m_ctrlMSCommCOM1.GetPortOpen())
AfxMessageBox("please open the port!");
else
{
CCreateName Dlg;
CString TB_Name;
CString mstr;
if(Dlg.DoModal()==IDOK)
{
TB_Name=Dlg.m_TableName;
}

m_pRecordset->Close();

//// create a new table in SQL

mstr.Format("IF NOT EXISTS (SELECT * FROM sysobjects WHERE name = '");
mstr+=TB_Name;
mstr+="') CREATE TABLE ";
mstr+=TB_Name;
mstr+=" (ID INT,FangWei TEXT,FuYang TEXT,QingXie TEXT)";

try
{
m_pRecordset->Open(mstr.AllocSysString theApp.m_pConnection.GetInterfacePtr(),
adOpenDynamic,
adLockOptimistic,
adCmdText);

}

catch(_com_error *e)
{
AfxMessageBox(e->ErrorMessage());

}

SetTimer(1,500,NULL);

}
}

when dibug to the end, appear an ERROR about "stackoverflow"

what is the problem??

wuhuaiji

Re: a problem about stackoverflow

Pete O'Hanlon12-Oct-07 4:44

Pete O'Hanlon

12-Oct-07 4:44

The problem would appear to be that you've posted a C++ question in the SQL forum. Please choose a more appropriate one. Just because you've used the word Recordset in the post doesn't actually make this a database issue. It's still C++.

Deja View - the feeling that you've seen this post before.

Re: a problem about stackoverflow

nibabug12-Oct-07 15:26

nibabug

12-Oct-07 15:26

thanks a lot Smile | :)

wuhuaiji

Re: a problem about stackoverflow

nibabug12-Oct-07 15:36

nibabug

12-Oct-07 15:36

excuse me
but i have found that when i do nothing to the database in a WM_TIMER function, there is no error,
but just if i do something, for example:

void CSerialRecieveDlg::OnTimer(UINT nIDEvent){
CString str;
CCreateName Dlg;
CString TB_Name;
TB_Name=Dlg.m_TableName;

str.Format("SELECT * FROM ");
str+=TB_Name;

try
{
//先关闭已经打开的记录集对象
m_pRecordset->Close();

//根据新的Sql查询语句，重新打开记录集对象
m_pRecordset->Open(str.AllocSysString(),
theApp.m_pConnection.GetInterfacePtr(),
adOpenDynamic,
adLockOptimistic,
adCmdText);
}
catch(_com_error *e)
{
AfxMessageBox(e->ErrorMessage());
}

m_pRecordset->MoveNext();

CDialog::OnTimer(nIDEvent);
}

there is a runtime error,

The problem is that i do not know it is a database problem or a C++ problem??

wuhuaiji

Checking Duplicate values in Database

codingrocks12-Oct-07 2:25

codingrocks

12-Oct-07 2:25

I wrote a query for getting some values from database , and and bind it to grid.

Now i want to add new values, but before that i wanna check whether there are entries of the same values...

How can I do this...? a friend suggested to add some conditions to the same stored proc to get this done..

Please help me...?

SAJAN A PILLAI

C#.NET Programmer
TELESOFT INDIA PVT LTD...
BANGALORE

"Winners don't do different things. They do things differently. ...

Re: Checking Duplicate values in Database

Pete O'Hanlon12-Oct-07 2:31

Pete O'Hanlon

12-Oct-07 2:31

Do a select to check if the duplicate criteria is there and then insert if nothing matches. You could do this with an IF NOT EXISTS query. Alternatively, create a unique index on these fields and rely on the database catching it.

Deja View - the feeling that you've seen this post before.

Re: Checking Duplicate values in Database

Skanless12-Oct-07 5:33

Skanless

12-Oct-07 5:33

Are u checking for duplicate values in a Database or a table? If it is a table the you use the following script.

SELECT
count(Column(s))
FROM
table_Name
ORDER BY Column(s)
GROUP BY column(s)
HAVING Count(Column(s)) > 1

Hope this helps.

Skan

If you knew it would not compile why didn't you tell me?!?!?!

How to update a table using a Select statement in the Where Clause

Skanless11-Oct-07 16:47

Skanless

11-Oct-07 16:47

I am trying to update a record in an Employee work hours table to record the employee time out. I need to find the last time the employee clocked in then assosciate the time out with the record. thus having a time in and timeout data.

UPDATE dbo.EmpWorkHours
SET Emp_Timeout = getdate()
where Emp_Timeout in (SELECT TOP 1 Emp_timeout
FROM dbo.EmpWorkHours
WHERE FK_Emp_Login_ID = 'MDoe74'
and Emp_Timeout IS NULL
ORDER BY RecordNum DESC
)

The script looks like it should be doing what I want it to do but it never finds any record, thought the record exist.

Any assistance will be great appreciated.

Skan

If you knew it would not compile why didn't you tell me?!?!?!

Re: How to update a table using a Select statement in the Where Clause [modified]

joemonvarghese11-Oct-07 19:28

joemonvarghese

11-Oct-07 19:28

hi

Check whether the field Emp_Timeout is null or ' '(Space) in the table dbo.EmpWorkHours

Regards

Joe

-- modified at 1:50 Friday 12th October, 2007

Re: How to update a table using a Select statement in the Where Clause

Skanless12-Oct-07 21:08

Skanless

12-Oct-07 21:08

Thanks for your response. I figured it out, using the code below.

UPDATE dbo.EmpWorkHours
SET Emp_Timeout = getdate()
where RecordNum = (SELECT TOP 1 RecordNum
FROM dbo.EmpWorkHours
WHERE FK_Emp_Login_ID = 'MDoe74'
and Emp_Timeout IS NULL
ORDER BY RecordNum DESC
)

What was wrong with it you may ask? Well, I was selecting the Emp_timeout field which was null. I was then telling to update a record where getdate() equals the value in the Emp_Timeout field. That would never happen because at the time of the query Emp_timeout field is NULL thus it will never equal getdate().

Hope this helps.

Skan

If you knew it would not compile why didn't you tell me?!?!?!

Please Help to me this is very urgent issue

mohd faiz11-Oct-07 12:52

mohd faiz

11-Oct-07 12:52

Hi,

I m facing problem when I m trying to bulk insert record from back end and front end. it was working before but accidently my server has crashed and i have install every thing but now I m not able to Bulk insert through my store procedure.

Error Message :
Transaction count after EXECUTE indicates that a COMMIT or ROLLBACK TRANSACTION statement is missing. Previous count = 0, current count = 1.

please help why this error comes? what is the reason, is something i didnot install yet or something else?

Thanks in advance

I am software programming engineering student
and i want to develop in this field so Please help me whenever I want ur support.

Thanks & Regards
Mohammad Faiz Siddiqui

Re: Please Help to me this is very urgent issue

Christian Graus11-Oct-07 13:41

Christian Graus

11-Oct-07 13:41

Of course it's urgent, whose question isn't ?

Have you considered checking your stored proc to see if the advice the error gives you is correct ? Did you consider posting the proc here so people have something on which to base their attempts to help you ?

Christian Graus - Microsoft MVP - C++

"I am working on a project that will convert a FORTRAN code to corresponding C++ code.I am not aware of FORTRAN syntax" ( spotted in the C++/CLI forum )

Re: Please Help to me this is very urgent issue

kubben12-Oct-07 2:13

kubben

12-Oct-07 2:13

This usually means you have a "begin tran" and you either don't have a commit or rollback or they are not getting hit when you execute the stored procedure. Leaving an open transaction on the database will cause major problems since the open transaction will keep others from being able to access the table(s) that were in the transaction.

Ben

Re: Please Help to me this is very urgent issue

Skanless12-Oct-07 5:35

Skanless

12-Oct-07 5:35

Have u tried this:

Begin

StoredProc()
IF
@@Error = 0
Commit trans
Else
Rollback Trans

End

Hope this helps.

Skan

If you knew it would not compile why didn't you tell me?!?!?!

connection string

IamAmit11-Oct-07 5:49

IamAmit

11-Oct-07 5:49

Hello friends,
I want to connect to the remote database through asp pages. My db is made in sql server 2005. What could be the database connection string?

Re: connection string

Vasudevan Deepak Kumar11-Oct-07 7:24

Vasudevan Deepak Kumar

11-Oct-07 7:24

Check out this website:

http://www.connectionstrings.com/[^]

Vasudevan Deepak Kumar
Personal Homepage
Tech Gossips

1:1 Relationships

Brendan Vogt11-Oct-07 4:43

Brendan Vogt

11-Oct-07 4:43

Hi,

I'm a little confused with my table relationships. I have a Customer table and Title table. Title is something like Mr., or Miss., etc.

I have a foreign key reference in the Customer table to the Title. How would I read this relation.

...for each customer there is only 1 title, and for each title there is only one customer, or
...for each customer there is only 1 title, and for each title there is one or many customers??

This is a 1:1 relation, or 1:M?

Please help.

Regards

Re: 1:1 Relationships

John Gathogo11-Oct-07 4:47

John Gathogo

11-Oct-07 4:47

This is how you read:

For each customer you can only have one title, and for each title you can have one or more customers.

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