Re: ADODB Recordset Question - Database Discussion Boards

Jeff Martin21-Aug-03 11:05

21-Aug-03 11:05

I just write all my SPs in Query Analyzer and save them as .sql files. I check these into VSS. You could do the same in VS, but it's easier in Query Analyzer.

Re: Add Stored Procedures to VSS

Jason Weibel21-Aug-03 11:09

Jason Weibel

21-Aug-03 11:09

Good idea, I’ll do it that way. Thanks for the help.

Jason W.

SQL Server 2000 Installation Problem

-- NA --21-Aug-03 0:47

-- NA --

21-Aug-03 0:47

Hi all - I'm using Windows Server 2003 Enterprise Edition, and I'm trying to install SQL Server 2000 Developer Edition. At the beginning of the installation I get a warning from Windows saying that there are compatibility issues with SQL lower than SP2. That's fine, because I have SP3 on CD. I install SQL without any problems, then when I try to install the SP3, at the end where it is running .sql scripts, on one it gives an error. Simply "could not run script" and that's the end of setup, it just exits. When I try to use SQL before or after the attempted install of SP3, nothing happens. SQL Agent can't start the service, and Enterprise Manager can't connect. Any ideas? Thanks Big Grin | :-D

Paul

Garfield.Bark();<br />
---Configuration: garfield - Win32 Deworm---<br />
C:\garfield.cpp(9) : error C2039: 'Bark' : is not a member of 'CCat'<br />

modified 18-Jul-18 11:59am.

SQL Administration Through Internet Explorer

nileshmankar20-Aug-03 23:25

nileshmankar

20-Aug-03 23:25

Is there any Tool where i can administer SQL Server through Browser??
whatever i can do through SQL EnterPrise Manager i should be able do the same using browser

Re: SQL Administration Through Internet Explorer

[ Jûroehn ]6-Sep-03 6:12

[ Jûroehn ]

6-Sep-03 6:12

Hey, a bit late, but;

Try PhpMyAdmin (http://www.phpmyadmin.net).
phpMyAdmin is a web-based utility to manage MySQL databases...

MySQLFront Rocks. Cool | :cool:

Jeroen.

ADODB Recordset Question

Abin20-Aug-03 16:38

Abin

20-Aug-03 16:38

I'm using asp(not asp.net) and sql server 2000. The recordset object worked fine except that "RecordCount" is always -1, also "PageSize" and "PageCount" could not be used because "PageCount" is always -1 too regardless what "PageSize" is. My code:

<br />
Dim objConn<br />
Set objConn = Server.CreateObject("ADODB.Connection")<br />
objConn.ConnectionString = "Driver={SQL Server};Server=localhost;Database=mydb;Uid=sa; Pwd=sa;"<br />
objConn.Open<br />
Dim objRS<br />
Set objRS = Server.CreateObject("ADODB.Recordset")<br />
<br />
objRS.Open "select * from mytable", objConn 'this table contains like 2000 records<br />
Response.Write objRS.RecordCount 'always -1<br />
objRS.PageSize = 20<br />
Response.Write objRS.PageCount 'always -1 too<br />
<br />
objRS.Close<br />
'......<br />

Any help are very much appreciated.

Re: ADODB Recordset Question

Steve S20-Aug-03 22:54

Steve S

20-Aug-03 22:54

Hmm. You might want to do
ObjRS.MoveLast
ObjRS.MoveFirst
before your first Response.Write, as I seem to remember that the recordcount isn't filled in until you've seen the last record.

This might explain why it's not accurate, and since

PageCount = (RecordCount+PageSize-1)/PageSize

can only be calculated if RecordCount is valid....

Steve S
Me, I know nothing about ASP; never ever used it

Re: ADODB Recordset Question

Hesham Amin21-Aug-03 5:26

Hesham Amin

21-Aug-03 5:26

Steve S wrote:
ObjRS.MoveLast
ObjRS.MoveFirst

the default cursor type is forward only so this code will cause errors..
try this : (before openning the recordset
ObjRS.CursorLocation = adUseClient
please tell us if this solution works..

Re: ADODB Recordset Question

Steve S21-Aug-03 6:37

Steve S

21-Aug-03 6:37

Told you I'd never worked with ASP. All my DB stuff is usually OLE DB.;)

Steve S

Re: ADODB Recordset Question

Abin21-Aug-03 15:48

Abin

21-Aug-03 15:48

Worked!

Thanks a lot.

Inserting DateTime value

Francis B.20-Aug-03 5:32

Francis B.

20-Aug-03 5:32

Hi! I have a problem when I try to insert DateTime value in an access database.
I receive this exception: "Syntax error in INSERT INTO statement."

I try two different ways to add Datetime and no one works!

<br />
newDataRow["Timestamp"] = DateTime.Now.ToString();<br />

and

<br />
newDataRow["Timestamp"] = DateTime.Now;<br />

But if I remove all DateTime stuff in my database and my code, I am able to insert data whitout any problem.

Here my code:

DataSet dataSet = new DataSet();
string dbGenConn = "Provider=Microsoft.Jet.OLEDB.4.0; Data Source=";
string dbConn = dbGenConn + "fileName.mdb" + "; Persist Security Info=False;";
OleDbConnection conn = new OleDbConnection(dbConn);
conn.Open(); 

OleDbDataAdapter adapter = new OleDbDataAdapter(new OleDbCommand("SELECT * FROM Data", conn));
OleDbCommandBuilder dataCmdBuilder = new OleDbCommandBuilder(adapter);
adapter.Fill(dataSet, "Data");

for(int i = 0; i < items.Length; i++)
{	
	DataRow newDataRow = dataSet.Tables["Data"].NewRow();
	newDataRow["DataID"] = i;
	newDataRow["Timestamp"] = DateTime.Now.ToString();
	newDataRow["Quality"] = 192
	dataSet.Tables["Data"].Rows.Add(newDataRow);	
}

adapter.Update(dataSet, "Data");
conn.Close();

Re: Inserting DateTime value

Marcel Härry25-Aug-03 11:34

Marcel Härry

25-Aug-03 11:34

i think this is the problem:

newDataRow["Timestamp"] = DateTime.Now.ToString();

if your database field is a text field use

newDataRow["Timestamp"] = Convert.ToString(DateTime.Now);

and if it's a datetime (much better) use it this way

newDataRow["Timestamp"] = DateTime.Now;

get the newly create record ID

Jerome Conus19-Aug-03 23:20

Jerome Conus

19-Aug-03 23:20

Hi !

When I create a new record in my table, an autoincremented field (which is the record's ID) is filled by the database. Often I need, as soon as I created the record, to know it's ID to use it.

How can I get this new record ID if the database is used in a multiuser environnement ? I guess creating the record and then getting the highest ID could be a problem if two records are simultaneously created.

Thank you for your help !
Jerome

Re: get the newly create record ID

Alexandru Savescu20-Aug-03 0:34

Alexandru Savescu

20-Aug-03 0:34

I ran into the same situation. You have this solutions:

1. If your application uses only one server and all users connect to that server to write data in the database then you can use a critical section at the application level to lock the application when doing this:
a. Insert new value
b. Select MAx (ID)

2. Get rid of autoincrement values. Use a new table (say ID_Generator) that holds a table name and the max reached value. Then, to insert a unique id you would do this:
a. UPDATE ID_GENERATOR SET IDValue = IDValue + 1 WHERE TableName = 'MYTable'. This ensures that the page is locked during the update process
b. SELECT IDValue FROM ID_GENERATOR WHERE TableName = 'MyTable';

I prefer the second solution since it gives me complete control over IDs.

Best regards,
Alexandru Savescu

P.S. Interested in art? Visit this!

Re: get the newly create record ID

Steve S20-Aug-03 0:50

Steve S

20-Aug-03 0:50

We use (2) and stored procedures to add data to tables which have an ID element, passing it back as an output parameter. That way it works with SQLServer and Oracle9, which we support - don't know about access, as that's not a supported product in this context.

Admittedly, we're writing COM+ components in C++, which makes this stuff easier Blush | :O

Steve S

Re: get the newly create record ID

Dr_Sh0ck24-Aug-03 23:52

Dr_Sh0ck

24-Aug-03 23:52

How about @@IDENTITY or SCOPE_IDENTITY()?

Re: get the newly create record ID

Tom Archer25-Aug-03 15:41

Tom Archer

25-Aug-03 15:41

What database api are you using? ADO? ADO.NET? ...

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: get the newly create record ID

Jerome Conus25-Aug-03 19:02

Jerome Conus

25-Aug-03 19:02

Hi !

Tom Archer wrote:
What database api are you using? ADO? ADO.NET? ...

I want to use only standard SQL with the ODBC driver, to be able to use several database types.

Jerome

Re: get the newly create record ID

Tom Archer26-Aug-03 2:00

Tom Archer

26-Aug-03 2:00

APIs such as ADO.NET provide means of making this easier, but with straight ODBC, I believe you'll have to make a following read to get the value.

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

OleDB or Sql?

Alexandru Savescu19-Aug-03 22:28

Alexandru Savescu

19-Aug-03 22:28

I am the datalayer of an enterprise application with .NET.
The database is now on SQL Server but we might port it for other systems like Oracle.
To make it more portable we are using OleDB. I would like to know how much the performance would imporve should I use Sql classes when dealing with SQL server.

Thanks.

Best regards,
Alexandru Savescu

P.S. Interested in art? Visit this!

Re: OleDB or Sql?

Brad Sokol22-Aug-03 2:58

Brad Sokol

22-Aug-03 2:58

Everything I've read says the same thing, but I've never seen it quantified.

A solution that gives you some flexibility might be to use the ADO.NET interfaces rather than the concrete classes. Use a factory class to create your connections, data readers, data adapters, etc. and then in your data layer use the interface types, rather than the concrete types to work with the objects.

To change to one of the other 'families' of classes, you only need to change the factory. You can have a factory for SQL Server and one for Oracle (with .NET 1.1). You could even instantiate the factory dynamically at runtime though you'll need a common base class or interface for your factories.

Here is some psuedo-code:

<br />
class SQLServerDataObjectFactory : DataObjectFactory<br />
{<br />
    public IDbConnection CreateConnection(...)<br />
    {<br />
        return new SqlConnection(...);<br />
    }<br />
}<br />
<br />
class OracleDataObjectFactory : DataObjectFactory<br />
{<br />
    public IDbConnection CreateConnection(...)<br />
    {<br />
        // The class name may be wrong - I don't have the .NET 1.1 framework installed<br />
        return new OracleConnection(...);<br />
    }<br />
}<br />
<br />
class MyDataLayerObject<br />
{<br />
    public void MyDataLayerMethod()<br />
    {<br />
        // Do stuff<br />
<br />
        // The data object factory was instantiated somewhere in the <br />
        // program's initialisation code.<br />
        IDbConnection connection = myDataObjectFactory.CreateConnection(...);<br />
<br />
        // Do stuff with connection. At this point, I don't care what<br />
        // concrete type it is.<br />
    }<br />
} <br />
<br />
Brad

NO_DATA - no error information available

bpchia19-Aug-03 20:10

bpchia

19-Aug-03 20:10

I get this error message when I read data from MySQL database. It is Microsoft.Data.Odbc.OdbcException.

Anyone know what is this problem and how to solve it? TQ

Re: NO_DATA - no error information available

bpchia22-Aug-03 1:43

bpchia

22-Aug-03 1:43

OK, I was got the solution

http://support.microsoft.com/default.aspx?scid=kb;EN-US;q319243

stumped

Shotgun19-Aug-03 18:34

Shotgun

19-Aug-03 18:34

what is wrong here?
I'm using this function to add a new record, all is well, close the application, I get
The instruction at "0x044e2cc2" referenced memory at "0x00000020", memory could not be read.
If I add a second record right away will also error.

CDaoDatabase data;
CString SqlString;
CString sDriver = "MICROSOFT ACCESS DRIVER (*.mdb)";
CString sDsn;
CString File; // = pApp->m_strDB;

File = "D:\\Apps\\MVS\\VC98\\MyProjects\\Homeplay\\Release\\ruthere.mdb";

char pszBuf[MAX_PATH];

GetDateFormat(LOCALE_USER_DEFAULT, NULL, NULL, "MMMM'.' dd',' yyyy", pszBuf, MAX_PATH);

// Build ODBC connection string
sDsn.Format("ODBC;DRIVER={%s};DSN='';DBQ=%s",sDriver,File);
TRY
{
// Open the database
data.Open(File);

// Allocate the recordset
CDaoRecordset recset( &data );

SqlString = "SELECT * "
"FROM Accounts"; // search all database

recset.Open(CRecordset::forwardOnly,SqlString, dbSeeChanges);

// make sure we can make changes and update
if (!recset.CanAppend() || !recset.CanUpdate( ))
{
AfxMessageBox("can't append to recordset");
return;
}

recset.AddNew();

recset.SetFieldValue("Nick", lpszUser);
recset.SetFieldValue("Pass", lpszPass);
recset.SetFieldValue("IP", lpszAddress);
recset.SetFieldValue("Authorize", "0");
recset.SetFieldValue("RegDate", pszBuf);

recset.Update();

recset.Close();

data.Close();
}

CATCH(CDBException, e)
{

// If a database exception occured, show error msg
AfxMessageBox("Database error: "+e->m_strError);
return;
}
END_CATCH;

any helps really appreciated.

shotgun

Re: stumped

Rocky Moore19-Aug-03 22:58

Rocky Moore

19-Aug-03 22:58

May sound silly, but you might try setting your recset and data to null before you exit. Everything looks fine to me.

Rocky Moore <><

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