|
Thank uo Nick.
Mazy
"If I go crazy then will you still
Call me Superman
If I’m alive and well, will you be
There holding my hand
I’ll keep you by my side with
My superhuman might
Kryptonite"Kryptonite-3 Doors Down
|
|
|
|
|
I am trying to update an existing record in a table using the following code in an ASP page. The table (tblProjectCats) has an ID value (integer, autoincrement), and Title and Description fields (both set to varchar). The following code just doesn't work...doesn't give an error message...
'//start code
Dim objConn, objRS
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open strConnect
Set objRS = Server.CreateObject("ADODB.Recordset")
objRS.Open "UPDATE tblProjectCats SET Title ='Title', Description = 'Desc' WHERE ID=1", objConn, adOpenDynamic, adLockReadOnly, adCmdText
objRS.Close
Set objRS = Nothing
objConn.Close
Set objConn = Nothing
'//end code
|
|
|
|
|
Try:
Dim objConn
Set objConn = Server.CreateObject("ADODB.Connection")
objConn.Open strConnect
oConn.Execute "UPDATE tblProjectCats SET Title ='Title', Description = 'Desc' WHERE ID=1", , adExecuteNoRecords Or adCmdText
objConn.Close
Set objConn = Nothing
|
|
|
|
|
I'm looking for sample code..
using ADO not ODBC to access DB....
i want to just a simple code...
1. id and password checking on Dialog before application start
2. and then access to DB
3. member table id char(10) password char(10)
4. Is right or not
=======================================================================
CString tempID, tempPW; //
// int result;
tempID = m_strID; // ·Î±×ÀΠâ¿¡ ÀÔ·ÂµÈ ID ÀúÀå
tempPW = m_strPW; // ·Î±×ÀΠâ¿¡ ÀÔ·ÂµÈ Æнº¿öµå ÀúÀå
// µ¥ÀÌÅͺ£À̽º ¿¬°á ¹®ÀÚ¿°ú SQL ¸í·É ¹®ÀÚ¿À» ¼ÂÆÃÇÑ´Ù.
m_strConnection =_T("Provider=SQLOLEDB; Data Source=192.168.3.212; Initial catalog = jok_db; User ID = sa; Password = kiss;");
CString check_id = "EXEC check_id";
m_strCmdText=_T(check_id + " " + "'" + tempID + "'" + "," + "'" +tempPW + "'");
long result = m_pRs->Fields->Item["certify"]->Value;
==========================================================================
environment
MFC, SQL2000
i want to full source code.....
plz i need your help.
thank you for reading....
Have a nice day!!!
|
|
|
|
|
i have a MSAccess database (soon to be upgraded to SQL Server db)... which has Memo fields and some OLE and maybe even picture fields
in ASP... given a connection object (set cn = server.createobject("adodb.connection") ), how do i update the data in the memo field to that of a variable
e.g. i want to do "UPDATE myTable SET myMemoField=<here i="" need="" to="" put="" like="" 50k="" of="" data=""> WHERE ... " but of course i can't fit that all into the string
and even if it did fit in the string, it screws up when i have SQL commands in the text data itself
thanks in advance for your help
- Roman -
|
|
|
|
|
I think you have to do it manually - write code to open recordset, call method Edit(), set value for your memo field and call Update().
When you will move to SQL server you might want to write stored procedures to update your database - you will get it very robust and probably faster.
Martin
--------------------------------------------
C'mon we all know computers are experimental devices and should only be used for playing games.
Using them for alternative stuff like business, is clearly not using them for what they are intended.
Colin Davies
|
|
|
|
|
thanks, i will try that method
- Roman -
|
|
|
|
|
With which SQL stored procedure I can get/set table or databse collation?
Mazy
"If I go crazy then will you still
Call me Superman
If I’m alive and well, will you be
There holding my hand
I’ll keep you by my side with
My superhuman might
Kryptonite"Kryptonite-3 Doors Down
|
|
|
|
|
From SQL BOL:
Changing Collations
You can change the collation of a column by using the ALTER TABLE statement:
CREATE TABLE MyTable (
PrimaryKey int PRIMARY KEY,
CharCol varchar(10) COLLATE French_CI_AS NOT NULL
)
GO
ALTER TABLE MyTable
ALTER COLUMN CharCol varchar(10)
COLLATE Latin1_General_CI_AS NOT NULL
GO
You cannot alter the collation of a column that is currently referenced by:
- A computed column.
- An index.
- Distribution statistics, either generated automatically or by the CREATE STATISTICS statement.
- A CHECK constraint.
- A FOREIGN KEY constraint.
You can also use the COLLATE clause on an ALTER DATABASE to change the default collation of the database:
ALTER DATABASE MyDatabase COLLATE French_CI_AS
Altering the default collation of a database does not change the collations of the columns in any existing user-defined tables. These can be changed with ALTER TABLE. The COLLATE CLAUSE on an ALTER DATABASE statement changes:
- The default collation for the database. This new default collation is applied to all columns, user-defined data types, variables, and parameters subsequently created in the database. It is also used when resolving the object identifiers specified in SQL statements against the objects defined in the database.
- Any char, varchar, text, nchar, nvarchar, or ntext columns in system tables to the new collation.
- All existing char, varchar, text, nchar, nvarchar, or ntext parameters and scalar return values for stored procedures and user-defined functions to the new collation.
- The char, varchar, text, nchar, nvarchar, or ntext system data types, and all user-defined data types based on these system data types, to the new default collation.
After a collation has been assigned to any object other than a column or database, you cannot change the collation except by dropping and re-creating the object. This can be a complex operation. To change the default collation for an instance of Microsoft® SQL Server™ 2000 you must:
- Make sure you have all of the information or scripts needed to re-create your user databases and all of the objects in them.
- Export all of your data using a tool such as bulk copy.
- Drop all of the user databases.
- Rebuild the master database specifying the new collation.
- Create all of the databases and all of the objects in them.
- Import all of your data.
Note Instead of changing the default collation of an instance of SQL Server 2000, you can specify a default collation for each new database you create.
|
|
|
|
|
This example returns the name of the default collation for the server.
SELECT SERVERPROPERTY('Collation')
This example returns the name of the default collation for the Northwind database.
SELECT DATABASEPROPERTYEX('Northwind', 'Collation')
Getting the collation for a column is not as easy. The only way I have found is to use the SQL_VARIANT_PROPERTY function. It's only supposed to work on sql_variant types, but it seems to work on other types as well.
SELECT TOP 1 SQL_VARIANT_PROPERTY(Column, 'Collation') FROM Table
|
|
|
|
|
Thank you.
Mazy
"If I go crazy then will you still
Call me Superman
If I’m alive and well, will you be
There holding my hand
I’ll keep you by my side with
My superhuman might
Kryptonite"Kryptonite-3 Doors Down
|
|
|
|
|
I want to write some unicode(farsi) into my SQLServer database from my c# application.When I write them in my app they are ok but after they are written in SQL ? apear instead of them.
Any idea?
Mazy
"If I go crazy then will you still
Call me Superman
If I’m alive and well, will you be
There holding my hand
I’ll keep you by my side with
My superhuman might
Kryptonite"Kryptonite-3 Doors Down
|
|
|
|
|
Solved.I have to change my collation in SQL.
Mazy
"If I go crazy then will you still
Call me Superman
If I’m alive and well, will you be
There holding my hand
I’ll keep you by my side with
My superhuman might
Kryptonite"Kryptonite-3 Doors Down
|
|
|
|
|
Hey,
I've just started trying to program with mySQL (first time programming for a database) and I'm encountering some problems. I'm using Visual C++ v6 and mySQL++ v1.7.1 and when I try to compile any program the compiler is coming up with hundreds of errors all of which reside in the mySQL++ source files. I find it hard to believe that there are actually errors in the source files and I can't seem to relsove this problem, if anyone had any ideas or solutions it would be much appreciated.
Thanks in advance.
|
|
|
|
|
How can I access a database (in access2000) over internet connection.
I want to have an app that would upload a picture with a description to that database remotely.
Thanks
_ra
|
|
|
|
|
I am doing:
if(pAdoDb.Open((LPCTSTR)strConnection))
{
CString strCommandText;
CString strQry = _T("");
int numRecords;
strQry.Format(_T("SELECT DISTINCT (Color) From tblTest"));
CADOCommand pCommand(&pAdoDb, strCommandText = _T(""),CADOCommand::typeCmdText);
pCommand.SetText(strQry);
CADORecordset pRs(&pAdoDb);
if(!pRs.Execute(&pCommand))
AfxMessageBox("failed");
This snippet worked with "select * from tblTest".
With the distinct command above I am getting a runtime error:
CADORecordSet error
code = 80040e10
code meaning = IDispatch error #3088
Source = Microsoft JET database engine
Description = No value given for one or more required parameters
What am I doing wrong? Please help!
Thanks,
ns
|
|
|
|
|
How can I get the db2 database object size on the AS400?
Jassim Rahma
|
|
|
|
|
Does anyone have a good piece of SQL that will randomly select 1 row from a database?
I was thinking of doing a sqequence something like this
Count the number of rows
Generate a random number (rn) between 1 and the result of the count
Select Top (1) From (Select Top (rn) Order by ID DESC)
Is there a better way?
Thanks
Stephen.
|
|
|
|
|
Here are some articles about "Randomness" in SQL, however this article looks the closest to what you're trying to achieve.
Andy Gaskell, MCSD MCDBA
|
|
|
|
|
newbie here so please be kind
why won't this work?
SqlString = "SELECT * FROM Accounts ";
SqlString += "WHERE (Nick IS"; // or with the "=" sign
SqlString += tNick;
SqlString += ")";
recset.Open(CRecordset::forwardOnly,SqlString,CRecordset::readOnly);
i am trying to find the "id" so i may search another record
shotgun
shotgun
|
|
|
|
|
SqlString = "SELECT * FROM Accounts ";
SqlString += "WHERE (Nick = '";
SqlString += tNick;
SqlString += "')";
If you're looking for a string use "'"
cheers,
Chris Maunder
|
|
|
|
|
Chris's answer is correct. However you need not use the += operator, it is inefficient in this case as it does extra memory allocation. You can write
SqlString = "SELECT * FROM Accounts WHERE (Nick = 'tNick');
Best regards,
Alexandru Savescu
|
|
|
|
|
Alexpro wrote:
SqlString = "SELECT * FROM Accounts WHERE (Nick = 'tNick');
If you are only interested in the "id", its much better to use
select id from accounts where Nick='tNick'
Dave
|
|
|
|
|
I have been looking at a variety of options for doing bulk loads from the .net framework - not of which are quite what I want. There is no 'native' class that allows access to the irowsetfastload interface in oledb.
Here is a picture of what I want to do:
1. Load and parse a 1.2 million line file into a structured dataset in memory
2. Perform some 'massage' work on data in memory
3. Shove the data into SQL Server 2000 as quickly as possible
The dataset classes in ADO.net are wonderful and work perfectly for what I am trying to do. The problem is that when I issue the database inserts, they are too slow to do in the timeframe that I need. I can dump the data to a file from the dataset, and use bcp or some such method, but I don't want that additional overhead. Actually what I have implemented so far has an FTP object that allows me to load the FTP dataconnection stream right into a memory stream, and I can process the data as it's coming off the wire, put into a dataset, and them cram into database - all without any file IO (except for database of course).
The Irowsetfastload interface is seems to be exactly what I need, but I cannot access it very well from the framework, and I'm no OLEDB expert. There are very few articles about this on the internet, certainly not enough to get started. Have recently tried the xmlbulkload object through interop, but it requires a file name, so I may as well use bulk insert TSQL - it doesnt buy me anything.
A few questions:
Would it be possible to write a Managed c++, c# or VB.net class that encapsulates this interface? How would one go about doing so. If it was done in managed c++, I assume the library could easily be used by c# or VB. To give an idea of how I would like to do this - I really want a method on the dataset itself like ds.fastload with a few parameters about the load itself. Extra points if someone can figure out how to do an 'UPSERT' - pass in a primary key, and have it figure out which records to fastload, and which ones need a good old update.
Does anyone know if MSFT is planning on adding functionality to the sql client classes to enable this functionality? Any pointers or ideas much appreciated.
James;)
|
|
|
|
|
Are you using the SQL Client provider to access the database, or OleDb provider ?.
The SQL Client provider is faster than OleDb, if you are using OleDb, give it a shot to SQLClient, the interfaces are almost the same so it shouldn't be hard.
Andres Manggini.
Buenos Aires - Argentina.
|
|
|
|