|
You can use ADOX to retrieve tables/views list. If you want, I can give you some code fragment. 
Kyle Chen
|
|
|
|
|
Hi all,
I have data arriving via a text file that needs to be synchronized with the data in the DBase, the problem is that the text file contains all the data that is in the DB table + changes, so i need to figure out the best way to filter only the modified data and update the table in the db.
i am currently parsing the text file into a dataset and pulling the data from the DB into a different dataset, is there a way to update the data source only with the data witch was modified ?
|
|
|
|
|
Hi,
I have two database files, an access database(jet) and a mssql database(mssqloledb).
I'm tryng to execute the query "DELETE * FROM table1", so I've created a _CommandPtr variable. When I execute the command with the access database it works fine, but when I execute the command with the mssql it throws an exception 'DB_E_ERRORSINCOMMAND'. It's a sintax error in the sql.
Is there any difference in the sintax of the sql statement between this two providers ?
ps: I can't use "SELECT * FROM table1" with the mssql provider either.
thanks.
|
|
|
|
|
There are differences in syntax, but in this case it may be that your connection is trying to use the Access (Jet) provider instead of the MSSQL provider.
"Welcome to Arizona!
Drive Nice - We're Armed..."
- Proposed Sign at CA/AZ Border
|
|
|
|
|
try to use fully qualified names :
databasename.dbowner.tablename
|
|
|
|
|
Maybe you should use "DELETE FROM table1" for MSSQL. Just have a try.;)
Kyle Chen
|
|
|
|
|
Anyone know that what is the limitation on the number of records affected when using the UpdateBatch function on a recordset pointer ? Any other limitation ?
|
|
|
|
|
It depends on the provider.
I think (the docs aren't very clear on this) that you can find it out by looking at the BatchSize dynamic property (accessed via the Properties property of the recordset).
|
|
|
|
|
Using VS.Net I am trying to figure out an easy way to add my stored procedures to visual source safe. Preferably, I would like to be able to just check in and out the stored procedures like they were normal documents. Can this be done?
Thanks,
Jason W.
|
|
|
|
|
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.
|
|
|
|
|
Good idea, I’ll do it that way. Thanks for the help.
Jason W.
|
|
|
|
|
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 
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.
|
|
|
|
|
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
|
|
|
|
|
Hey, a bit late, but;
Try PhpMyAdmin (http://www.phpmyadmin.net).
phpMyAdmin is a web-based utility to manage MySQL databases...
MySQLFront Rocks. 
Jeroen.
|
|
|
|
|
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.
|
|
|
|
|
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
|
|
|
|
|
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..
|
|
|
|
|
Told you I'd never worked with ASP. All my DB stuff is usually OLE DB.;)
Steve S
|
|
|
|
|
|
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();
|
|
|
|
|
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;
|
|
|
|
|
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
|
|
|
|
|
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!
|
|
|
|
|
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 
Steve S
|
|
|
|
|
How about @@IDENTITY or SCOPE_IDENTITY()?
|
|
|
|
General 
News 
Suggestion 
Question 
Bug 
Answer 
Joke 
Praise 
Rant 
Admin 
Submit an article or tip
