COM ADO and SQLXML v3

Hi,

I'm in the process of updating an HTA hosted application to SQL Server 2005 Express, using javascript with ADO for database access. I have one function to which I pass an SQLXML query template or updategram for all database queries and updates:

function doSql(sXml) {
var cmd = new ActiveXObject('ADODB.Command');
var conn = new ActiveXObject('ADODB.Connection');
var strmIn = new ActiveXObject('ADODB.Stream');
var strmOut = new ActiveXObject('ADODB.Stream');
var xml = new ActiveXObject(sDOM);
xml.async = false;

try {
conn.Provider = "SQLOLEDB";
conn.Open("Provider=SQLOLEDB.1;Persist Security Info=True;"+
"Initial Catalog=CGIS;Server=(local)\\ocean;Integrated Security=SSPI;");
conn.Properties("SQLXML Version") = "SQLXML.3.0";
cmd.ActiveConnection = conn;
cmd.Dialect = "{5d531cb2-e6ed-11d2-b252-00c04f681b71}";
strmIn.Open();
strmIn.WriteText(sXml);
strmIn.Position = 0;
cmd.CommandStream = strmIn;
strmOut.Open();
cmd.Properties("Output Stream").Value = strmOut;
cmd.Properties("Output Encoding").Value = "UTF-16";
var iCount;
cmd.Execute(iCount, null, 0x400);
conn.Close();
xml.load(strmOut);
return xml;
} catch(e) { alert('A database error occured: \n'+e.description+'\n\nQuery:\n'+sXml); }

I understand that SQLServer 2005 has SQLXML built in, which can be accessed using ADO.Net. Can it also be accessed using COM ADO? When I changed the connection string to this:

conn.Open("Provider=SQLNCLI.1;Integrated Security=SSPI;Persist Security Info=False;Data Source=\\.\pipe\SQLLocal\SQLEXPRESS");

I get this error:

'A database error occured:
SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].'

Sounds like a connection string error however this was the connection string I pulled out of a .udl file that connected successfully.

Any help/suggestions greatly appreciated!

Andrew

Try changing the provider to see if that's it. My bet is that it is something to do with the network connection settings, so look in Books Online in the Database Engine section to learn more about the settings and various issues they have. You can also check out this article:

http://support.microsoft.com/default.aspx/kb/914277

Buck Woody

COM ADO and SQLXML v3

Hi,

I'm in the process of updating an HTA hosted application to SQL Server 2005 Express, using javascript with ADO for database access. I have one function to which I pass an SQLXML query template or updategram for all database queries and updates:

function doSql(sXml) {
var cmd = new ActiveXObject('ADODB.Command');
var conn = new ActiveXObject('ADODB.Connection');
var strmIn = new ActiveXObject('ADODB.Stream');
var strmOut = new ActiveXObject('ADODB.Stream');
var xml = new ActiveXObject(sDOM);
xml.async = false;

try {
conn.Provider = "SQLOLEDB";
conn.Open("Provider=SQLOLEDB.1;Persist Security Info=True;"+
"Initial Catalog=CGIS;Server=(local)\\ocean;Integrated Security=SSPI;");
conn.Properties("SQLXML Version") = "SQLXML.3.0";
cmd.ActiveConnection = conn;
cmd.Dialect = "{5d531cb2-e6ed-11d2-b252-00c04f681b71}";
strmIn.Open();
strmIn.WriteText(sXml);
strmIn.Position = 0;
cmd.CommandStream = strmIn;
strmOut.Open();
cmd.Properties("Output Stream").Value = strmOut;
cmd.Properties("Output Encoding").Value = "UTF-16";
var iCount;
cmd.Execute(iCount, null, 0x400);
conn.Close();
xml.load(strmOut);
return xml;
} catch(e) { alert('A database error occured: \n'+e.description+'\n\nQuery:\n'+sXml); }

I understand that SQLServer 2005 has SQLXML built in, which can be accessed using ADO.Net. Can it also be accessed using COM ADO? When I changed the connection string to this:

conn.Open("Provider=SQLNCLI.1;Integrated Security=SSPI;Persist Security Info=False;Data Source=\\.\pipe\SQLLocal\SQLEXPRESS");

I get this error:

'A database error occured:
SQL Network Interfaces: Error Locating Server/Instance Specified [xFFFFFFFF].'

Sounds like a connection string error however this was the connection string I pulled out of a .udl file that connected successfully.

Any help/suggestions greatly appreciated!

Andrew

Try changing the provider to see if that's it. My bet is that it is something to do with the network connection settings, so look in Books Online in the Database Engine section to learn more about the settings and various issues they have. You can also check out this article:

http://support.microsoft.com/default.aspx/kb/914277

Buck Woody

Columns not updating from Stored procedure

Ok, so I've got the following stored procedure:

ALTER PROCEDURE dbo.tbUserPreferences_UpdateOrInsert

(
@.username varchar(50),
@.preferences varchar(300),
@.view_name varchar(300),
@.default_view varchar(10) = 'Y'
)

AS
UPDATE tbUserPreferences SET @.default_view='N' WHERE username=@.username

-- IF NOT EXISTS (
-- SELECT *
-- FROM tbUserPreferences
-- WHERE username=@.username
-- AND view_name=@.view_name
-- )
-- INSERT INTO tbUserPreferences (username, preferences,view_name,default_view) VALUES (@.username,@.preferences,@.view_name,@.default_view)
RETURN

The commented out section works fine, but the UPDATE line does not. I know there are columns that have "username=@.username", but this call is not updating their default_view column.

Please, if anybody knows why, let me in on the secret. Thanks!Try to execute and check result:

UPDATE tbUserPreferences SET @.default_view='N' WHERE username='your sp param'

select @.@.rowcount|||Wow, I'm so silly. And it took me looking at your reply to get it.

The code I ment to try was:

UPDATE tbUserPreferences SET default_view='N' WHERE username=@.username

"default_view" not "@.default_view". Thank you for the reply. Even though I didn't need to test your suggestion, it made me realize my problem. Thanks!|||It's still a good example of why you should error check your code...

column chechking while updating

If amc.amc2>0 then
UPDATE amc set AMC2= AMC2 + & val(txtamt.text) & where am1=1
else if amc.amc2<0 then
UPDATE amc set AMC2= & val(txtamt.text) & where am1=1
end if

Here I check the value of column with if condition statement. I need to check the column with out if condition statement.

What is my doubt is that how I can check value of amc2 column while updating.

eg: update amc set case when amc2>0 then AMC2= AMC2 + & val(txtamt.text)
like this

Is it possible with case-when -end?

If it is possible I can solve a big problem in my project.
Can you give me an example with this query?update amc
set amc2 = & val(txtamt.text) & + (CASE WHEN amc2 >= 0 THEN amc2 ELSE 0 END)
where am1=1;