Database

Re: Recordset Open method error IDispatch error #3121

1-Dec-05 7:20

Do FASD and POMS reside on the same machine? If yes, then, are database files on the same volume (or HDD or partition)?

Farhan Noor Qureshi

^{if (this == this) thow this;}

flyflyfly1-Dec-05 7:49

flyflyfly

1-Dec-05 7:49

Yes FASD and POMS reside on the same machine, same volume (D:\ drive)
Do you know how to look at those database's logic file name?
I notice that when I do the restore, I need changed the physical data file name, and physical log file name to the new physical data and log file name.
For example I need to change D:\MSSQLDATA\MSSQL\Data\FASD.mdf to D:\MSSQLDATA\MSSQL\Data\POMS.mdf and D:\MSSQLDATA\MSSQL\Data\FASD_log.ldf
to D:\MSSQLDATA\MSSQL\Data\POMS_log.ldf

however I am not be able to change to logic file name.
The restore utility read those infomation from the backup file.

Re: Recordset Open method error IDispatch error #3121

Farhan Noor Qureshi1-Dec-05 8:55

Failed to compare Time portion of DateTime when read ACCESS database

1-Dec-05 8:55

flyflyfly wrote:
Do you know how to look at those database's logic file name?

Run select * from sysfiles

IMHO, having same logical file names is not an issue here. Check for the differences (FASD and POMS) in 'Recovery Model', triggers, Locks (Under Management->Current Activity->Locks/Object in EM).

Farhan Noor Qureshi

^{if (this == this) thow this;}

Roman Muntyanu29-Nov-05 8:35

Roman Muntyanu

29-Nov-05 8:35

Hi all,

I have datetime field in database. When I am getting records older than
specific date that I passed in parameter I can not get records that
differs only in Time part of the datetime value.
Looks like comparison of Time portion in query failed.

I am using ACCESS database and ADO OleDbCommand. When in debug in Visual Studio QuickWatch clearly can see that the record that am going to get is older than datetime in TimestampParam by 10 sec (for example). If I substract 1 day from the
database field value I am getting the record. It is definetly time
portion comparison failed.

These are some lines from my code to show how I am reading database
OleDbDataAdapter adapter = new OleDbDataAdapter();
myCommand.CommantText = "SELECT [NetLog].* FROM [NetLog] WHERE
Timestamp < TimestampParam"
adapter.SelectCommand = myCommand;
System.Data.DataSet dataSet = new System.Data.DataSet();
int iNumRows = adapter.Fill( dataSet );

I would appreciate any advice

Thanks,
Roman

MSDE and SQL SErver Express 2005

inphone29-Nov-05 4:52

inphone

29-Nov-05 4:52

Is possible to keep MSDE on my machine, and install MS SQL EXPRESS 2005?
I want to keep both engines.

Re: MSDE and SQL SErver Express 2005

Paul Conrad29-Nov-05 5:07

Inserting a record into a database with a front web form

29-Nov-05 5:07

Yes it is possible. I am doing so right now on my machine Smile | :)

. Along with MySQL and Firebird.

bhumber29-Nov-05 1:45

bhumber

29-Nov-05 1:45

The code below should insert this record into the database, I don't the error. I drops through and run the line that enables the btnAdd.Enable = true. How do I fix it?

private void SaveRecord()
{
string strSQL;

if(btnSave.CommandArgument == "Add")
{
strSQL =
"INSERT INTO CoinCollection " +
" (ID, TypeOfCoin, YearOfCoin, StateOfCoin, DateReceive)" +
"VALUES " +
" (@ID, @TypeOfCoin, @YearOfCoin, @StateOfCoin, @DateReceive)";
}
else
{
// The user is updating an existing item
strSQL =
"UPDATE CoinCollection " +
" SET TypeOfCoin = @TypeOfCoin, " +
" YearOfCoin = @YearOfCoin, " +
" StateOfCoin = @StateOfCoin, " +
" DateReceive = @DateReceive " +
"WHERE ID = @ID";
}

SqlConnection conn = new SqlConnection(ConfigurationSettings.AppSettings["connString"]);
SqlCommand cmSQL = new SqlCommand(strSQL, conn);

// Add all the requuired SQL Parmeters.
cmSQL.Parameters.Add(new SqlParameter("@ID", SqlDbType.Int)).Value = Convert.ToInt32(tbID.Text);
cmSQL.Parameters.Add(new SqlParameter("@TypeOfCoin", SqlDbType.VarChar, 50)).Value = tbToc.Text;
cmSQL.Parameters.Add(new SqlParameter("@YearOfCoin", SqlDbType.VarChar, 50)).Value = tbYoc.Text;
cmSQL.Parameters.Add(new SqlParameter("@StateOfCoin", SqlDbType.VarChar, 50)).Value = tbSoc.Text;
cmSQL.Parameters.Add(new SqlParameter("@DateReceive", SqlDbType.VarChar, 50)).Value = tbDr.Text;

try
{
conn.Open();
cmSQL.ExecuteNonQuery();
strMsg = "Coin successfully saved to the database.";
}
catch(Exception exp)
{
strErrorMsg = "Database error! Coins not saved to database. Error Message: " + exp.Message;
}
finally
{
conn.Close();
btnAdd.Enabled = true;
}
}

bhumber

Re: Inserting a record into a database with a front web form

dwatkins@dirq.net29-Nov-05 11:06

dwatkins@dirq.net

29-Nov-05 11:06

the btnAdd.Enabled = true; line is in the finally block so it will always run. You might want to put that in the try block.

Also if you do this:

int intAffectedRecords = cmSQL.ExecuteNonQuery();

you can get the count of affected records so you could then say if there were records affected the button would be enabled:

btnAdd.Enabled = (intAffectedRecords > 1) ? true : false;

You can also just use the Exception exp's ToString method to see the entire error message:

Response.Write(exp.ToString());

Dirk Watkins

Working with only Time in Sql?

majidbhutta29-Nov-05 1:00

majidbhutta

29-Nov-05 1:00

i have some confusion. Scenario is that for an Employee to
start and end job the following fields are available;
Field DataType
===== ========
Emp_Id Foreign Key}
DateTime PrimaryKey }----> Composite Primary key

CheckInTime DateTime
CheckOutTime DateTime
InOutStatus bit
HoursWorked ?--------> What shoulud be DataType?

When an Employee Checks in All fields are set and
InOutStatus is set to 1 Except CheckOutTime and
HoursWorked.When The Employee checks out InOutStatus is set
to 0,CheckOutTime is set and then i have to calculate the
HoursWorked (TimeDuaration). Constarins are CheckOutTime is
always greater than CheckInTime.(There may be different
dates for them as employee gets check in ,Nov 29,2005 on
time 17:00 and checksout , Nov 30,2005 on time 03:30).Now
according to calculation he worked for 10 hours and 30
minutes .How to calculate this hours worked(only time
duration and to which datatype it should be saved in sql)
as well as the Stored Procedure for this whole
process.Whenever Employee comes his Emp_Id is only known.
Thnx in Advance.

Re: Working with only Time in Sql?

Farhan Noor Qureshi29-Nov-05 12:01

SELECT @@IDENTITY doesn't work

29-Nov-05 12:01

If minute level precision is fine then save the number of minutes and use int as datatype.

Declare @dt1 datetime, <br />
	@dt2 datetime<br />
<br />
Set @dt1 = GetDate()<br />
Set @dt2 = DateAdd(yyyy, 1, @dt1)<br />
<br />
select DATEDIFF(mi, @dt1, @dt2)

Farhan Noor Qureshi

^{if (this == this) thow this;}

AndySummer28-Nov-05 22:27

AndySummer

28-Nov-05 22:27

Hello developer, i don't become the primary key back.
Whats wrong? The returnvalue is always sero.

C#-Code:
class vers
{
OleDbConnection connection = new OleDbConnection(ConnectionString);

....

public void Eintragen(sql)
{
OleDbCommand myOleDbCommand = new OleDbCommand(sql, connection); myOleDbCommand.ExecuteNonQuery();

myOleDbCommand = new OleDbCommand("SELECT @@IDENTITY", connection);
return Convert.ToInt32(myOleDbCommand.ExecuteNonQuery());
}
}

Re: SELECT @@IDENTITY doesn't work

Frank Kerrigan28-Nov-05 22:42

Frank Kerrigan

28-Nov-05 22:42

This will only work once a new identity has been created or changed should only be used within a stored procedure.

Remarks
After an INSERT, SELECT INTO, or bulk copy statement completes, @@IDENTITY contains the last identity value generated by the statement. If the statement did not affect any tables with identity columns, @@IDENTITY returns NULL. If multiple rows are inserted, generating multiple identity values, @@IDENTITY returns the last identity value generated. If the statement fires one or more triggers that perform inserts that generate identity values, calling @@IDENTITY immediately after the statement returns the last identity value generated by the triggers. The @@IDENTITY value does not revert to a previous setting if the INSERT or SELECT INTO statement or bulk copy fails, or if the transaction is rolled back.

INSERT INTO jobs (job_desc,min_lvl,max_lvl)
VALUES ('Accountant',12,125)
SELECT @@IDENTITY AS 'Identity'

Is this Possible in sql?

majidbhutta28-Nov-05 5:50

majidbhutta

28-Nov-05 5:50

In sql database , I have two tables named Emp_Info and Emp_Experience
the structure of table is like this;
Emp_Info
========
Emp_Id PrimaryKey(Auto)
Emp_Name
Emp_Age

Emp_Experience
==============
Exp_Id PrimaryKey(Auto)
Emp_Id ForeignKey
Exp_Detail
Exp_Year
. . . . ... and so on

Working on a single form, All these fields have to be enterd and are saved against one button click.The Problem is how can i get the Emp_Id for Emp_Experience table to save Experince and Emp_Info paralelly.The relationship btw Emp_Info and Emp_Experience is one to many. that is, Against one Employee there may be none or many Experiences.
in the scenario, i have to add Experiences first and then save the Employee? What 2 do? any guide line, help .....>?
i need its stored procedure for saving Experiences.
Thnx in Advance

Re: Is this Possible in sql?

Farhan Noor Qureshi28-Nov-05 10:53

Which one is faster and recommended

28-Nov-05 10:53

Use something like,

1. Insert Into Emp_Info............
2. Store newly created auto generated id in a local variable as,
Select @newId = @@IDENTITY
3. Insert Into Emp_Experience....... and use @newId as fk here

Farhan Noor Qureshi

^{if (this == this) thow this;}

dabuskol28-Nov-05 1:12

dabuskol

28-Nov-05 1:12

I need to save a record with 8 fields in my sql server table to be exact.
Which of the method is recommended and faster with this scenario.
1. Insert the record using the normal insert statement.
OR
2. Insert the record using the stored procedure (I will create the insert statement inside the stored procedure) call it in my program and pass the insert parameters.

Regardless of Security.. One record at a time using web application.

Thanks

Re: Which one is faster and recommended

Farhan Noor Qureshi28-Nov-05 11:02

Access database - for website

28-Nov-05 11:02

A combination of command object with stored proc is usually faster than even with very simple dynamic sql.

dabuskol wrote:
(I will create the insert statement inside the stored procedure)

Will your INSERT statement be dynamic for each call? If thats the case then it may not be a good idea to use a stored proc.

Farhan Noor Qureshi

^{if (this == this) thow this;}

Rama Sharma28-Nov-05 1:11

Rama Sharma

28-Nov-05 1:11

I want to use an Access database for my website (with ASP .NET). This database will have about 50,000+ records. Will an Access database be robust to withstand so many records on a web-server and be fast and efficient? For some reasons, I do not want to use SQL Server or MSDE.

Regards

-- modified at 7:12 Monday 28th November, 2005

Farhan Noor Qureshi28-Nov-05 10:56

28-Nov-05 10:56

Rama Sharma wrote:
Will an Access database be robust to withstand so many records on a web-server and be fast and efficient?

MS Access is not designed for such a scenario and IMHO MS Access is an oxymoron to robust-fast-efficient Smile | :)

Farhan Noor Qureshi

^{if (this == this) thow this;}

Paul Conrad28-Nov-05 11:37

28-Nov-05 11:37

Farhan Noor Qureshi wrote:
MS Access is not designed for such a scenario and IMHO MS Access is an oxymoron to robust-fast-efficient

Yes, Access is indeed an oxymoron.

Why is it that you do not want to use MSSQL or MSDE? You may want to look at migrating from Access to either of these, or to MySQL or Firebird...

Paul

Rama Sharma28-Nov-05 16:33

Rama Sharma

28-Nov-05 16:33

Thanks for your reply.

But, will MySQL be good and efficient for such a job?

Paul Conrad28-Nov-05 19:16

28-Nov-05 19:16

Rama Sharma wrote:
will MySQL be good and efficient for such a job?

It could. Mileage can vary with alot of things. I've found MySQL to work just fine under both Linux and Windows. Firebird is something I have found interesting. I am not sure as to what your requirements are for your website/database, but I hope this can help shed some light.

S Douglas28-Nov-05 21:06

S Douglas

28-Nov-05 21:06

Rama Sharma wrote:
Will an Access database be robust to withstand so many records on a web-server

I don’t do any web type stuff, but I do have an app with access as its database with a few hundred thousand records. It works with no problems, the bottle neck with my app isn’t the database it’s the 4 Meg token ring network.

BTW, I would rather use SQL Server, but getting access to one in the corporation I work for is insane. Dead | X|

If data security is of importance then go with SQL Server, Access for all intense purposes has no security.

ZeePain! wrote:
This seems like one of those programs that started small, grew incrementally, building internal pressure, and finally barfed all over its source code sneakers. Or something.

thedailywtf.com[^]

-- modified at 3:09 Tuesday 29th November, 2005

Paul Conrad29-Nov-05 5:10

29-Nov-05 5:10

As a standalone application, it can work just fine, except when you start getting alot of users accessing it at the same time.

Rama is doing something along the lines of web accessed data and I would move away from Access at that point.