SQL Adding a User account: update

Showing posts with label update. Show all posts

Tuesday, March 27, 2012

advice normalization

I have a web app which is used to do normal insert/update of employee info. Connected to each employee that is entered is some data that is imported from an outside source for each employee. The question I have is currently my database is very normalized and importing data from this outside source will be quite a pain because of this. Is it bad practice to denormalize a specific table if no user will every insert/update it beside DTS?

Why do you feel that normalization will make data importation more difficult?
Normalization should make the process somewhat easier -- it helpsguarantee data integrity, so you won't have to put excessive amounts ofvalidation code into your importation routines. I would notrecommend denormalizing to make this process "easier". Dataintegrity is the most important factor, and is the reason we usedatabases instead of flat file systems!
Can you post some more information about your schema and the data you're trying to import?

|||No Denormalization is used in OLPT(online transaction processing) to improve performance. Run a search for Composite index in SQL Server BOL(books online). A Composite index can take 16 Columns with a size limit of 900 bytes. Hope this helps.|||No. OLTP databases should NEVER be denormalized when dataintegrity is important! Use of denormalization for performanceshould be limited to OLAP and reporting scenarios in which the data canbe re-built from a trusted source. I would never riskdenormalizing a transactional database for the sake ofperformance. Even the best performance will be utterly useless iftransactions are lost due to data integrity issues.
I'm unsure of what a composite index has to do with this topic, but thanks for throwing it out there.

|||OLTP(online transaction processing) is the general term for none analytical use of the database. Denormalization in SQL Server 2000 and below can be achieved through a Composite Index. A Composite index is a way of life in SQL Server 7.0 and below without built in DRI(declarative referential integrity) And the table in question is being used by a process not people so the access can be scheduled.|||I'm not sure how you've arrived at the conclusion that creating acomposite index is akin to denormalizing a table. Given thatindexes are physical, not logical parts of the schema, they are notconsidered in discussions of normalization. I'd recommend thatyou readthis, and other articles on the Database Debunkings website to dispell that notion.
But even if that were the case, it would not apply to thissituation. The OP is concerned that data importation is going tobe difficult due to the normalization of the database. But as Iassume you're aware, DML operations cannot be performed onindexes. Only on tables (and indirectly on tables via views andUDFs). Furthermore, large composite indexes will slow downimportation -- not speed it up. Therefore, creating large indexeswill serve to hinder -- not help -- the OP's importation efforts.

|||

AdamMachanic wrote:
I'm not sure how you've arrived at the conclusion that creating a composite index is akin to denormalizing a table. Given that indexes are physical, not logical parts of the schema, they are not considered in discussions of normalization.

Indexes are not considered because indexes are from vendors and Normalization is ANSI SQL but there are so many vendor implementations that makes the database better. In SQL Server 2005 it is taken to the next level with index column include. Try the link below for index covering used by Microsoft consulting and is considered good practice by the industry. While you are at it check the appendix of the book below to see why E.F.Code called it normalization. If it is not in the PPT online it became Normalization because Nixon have normalized relationship with China.

http://www.sql-server-performance.com/covering_indexes.asp

http://wings.buffalo.edu/mgmt/courses/mgtsand/data.html

|||It doesn't matter if something is a vendor extension or defined by theANSI Standard -- SQL and the ANSI Standard have absolutely nothing todo with normalization. As a matter of fact, I just searched thecomplete text of the SQL-99 standard and a form of the word 'normalize'appears only once -- in reference to normalization of datetimedatatypes!
What the Relational Model is concerned with is the distinction betweenthe logical and the physical. Tables are logical -- and thereforesubject to the rules of logical normalization. Indexes arephysical -- they have no bearing upon the logical schema.

|||I have ANSI 2003 and the person will take what meets their existing needs.|||

Caddre wrote:
I have ANSI 2003 and the person will take what meets their existing needs.

Where is normalization defined in the SQL 2003 standard?

|||We will not be having this conversion if you have used SQL Server 6.5 and below when SQL Server and Normalization are not in one sentence. What is Normalization when queries are taking hours to complete.|||What difficulties did you have with normalization in SQL Server 6.5?

Sunday, March 25, 2012

advice

hey all,
does sql batch files allow you to have transactions? i'm trying to update 3
tables in a sql batch file and was wondering if this was a good idea or
should i just use stored procedure?
thanks,
ariari wrote:
> hey all,
> does sql batch files allow you to have transactions? i'm trying to
> update 3 tables in a sql batch file and was wondering if this was a
> good idea or should i just use stored procedure?
> thanks,
> ari
Sure you can use transactions. Just add BEGIN TRAN / COMMIT TRAN /
ROLLBACK to the batch where needed.
David Gugick
Quest Software
www.imceda.com
www.quest.com|||this helped, thank you.
"David Gugick" wrote:

> ari wrote:
> Sure you can use transactions. Just add BEGIN TRAN / COMMIT TRAN /
> ROLLBACK to the batch where needed.
> --
> David Gugick
> Quest Software
> www.imceda.com
> www.quest.com
>

Monday, March 19, 2012

Advanced SQL generations options

Advanced SQL generations options:

generate INSERT, UPDATE, and DELETE statements is all greyed out?

in my sql data source control.?

I have made a brand new instance with sql server management express...have I missed something?

Does the table have a Primary key defined?|||

Sorry about being slow to get back

NO,

|||

Thankyou very much Mr Wellens.....

I just put in a primary key into table and all working fine.

Does that mean you cant insert into any table without it having a primary key.

many many thanks

|||

>>Does that mean you cant insert into any table without it having a primary key.

Well YOU can, but ASP.Net wants a primary key.

If there was no primary key, the ASP.Net code could get very confused about what row needs to be updated or deleted.

Sunday, March 11, 2012

Advanced Index Statistics Query

I have noticed that running UPDATE STATISTICS without specifying any sampling
options, thus accepting the default selected by SQL Server, can reduce the
level of detail contained within the index distribution statistics.
As an example I ran DBCC SHOW_STATISTICS following the completion of a DBCC
DBREINDEX maintenance job, where statistics are rebuilt effectively WITH
FULLSCAN. The histogram has the maximum 200 steps. When a subsequent job
runs to UPDATE STATISTICS, the number of histogram steps falls to 110, hence
lessening the usefulness of the statistics. The values for EQ_ROWS are also
much less accurate, which I suppose you'd expect with a smaller sample size.
However, I do not understand what the optimizer gains from reducing the
number of steps during an UPDATE STATISTICS execution.
Kind Regards
Andrew Pike
--
SQL Server DBA
UBS IBDBREINDEX will build the statistics based on all rows, i.e., no sampling. UPDATE STATISTICS without
specifying sampling options *will* sample:
http://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andrew Pike" <AndrewPike@.discussions.microsoft.com> wrote in message
news:91ABAF37-7833-49F2-995C-2996B63A60B6@.microsoft.com...
>I have noticed that running UPDATE STATISTICS without specifying any sampling
> options, thus accepting the default selected by SQL Server, can reduce the
> level of detail contained within the index distribution statistics.
> As an example I ran DBCC SHOW_STATISTICS following the completion of a DBCC
> DBREINDEX maintenance job, where statistics are rebuilt effectively WITH
> FULLSCAN. The histogram has the maximum 200 steps. When a subsequent job
> runs to UPDATE STATISTICS, the number of histogram steps falls to 110, hence
> lessening the usefulness of the statistics. The values for EQ_ROWS are also
> much less accurate, which I suppose you'd expect with a smaller sample size.
> However, I do not understand what the optimizer gains from reducing the
> number of steps during an UPDATE STATISTICS execution.
> Kind Regards
> Andrew Pike
> --
> SQL Server DBA
> UBS IB
>|||Hi Tibor,
So the default sampling selected by SQL Server does not 'inherit' the
previous histogram created WITH FULLSCAN, updating statistics as necessary
based upon the previous values of RANGE_HI_KEY, but instead recreates the
histogram from scratch, knowingly reducing the number of steps.
Kind Regards
Andrew Pike
--
SQL Server DBA
UBS IB
"Tibor Karaszi" wrote:
> DBREINDEX will build the statistics based on all rows, i.e., no sampling. UPDATE STATISTICS without
> specifying sampling options *will* sample:
> http://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "Andrew Pike" <AndrewPike@.discussions.microsoft.com> wrote in message
> news:91ABAF37-7833-49F2-995C-2996B63A60B6@.microsoft.com...
> >I have noticed that running UPDATE STATISTICS without specifying any sampling
> > options, thus accepting the default selected by SQL Server, can reduce the
> > level of detail contained within the index distribution statistics.
> >
> > As an example I ran DBCC SHOW_STATISTICS following the completion of a DBCC
> > DBREINDEX maintenance job, where statistics are rebuilt effectively WITH
> > FULLSCAN. The histogram has the maximum 200 steps. When a subsequent job
> > runs to UPDATE STATISTICS, the number of histogram steps falls to 110, hence
> > lessening the usefulness of the statistics. The values for EQ_ROWS are also
> > much less accurate, which I suppose you'd expect with a smaller sample size.
> > However, I do not understand what the optimizer gains from reducing the
> > number of steps during an UPDATE STATISTICS execution.
> >
> > Kind Regards
> >
> > Andrew Pike
> > --
> > SQL Server DBA
> > UBS IB
> >
>|||Correct. See the URL I posted and also Books Online. Especially the RESAMPLE option.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Andrew Pike" <AndrewPike@.discussions.microsoft.com> wrote in message
news:FBE806D2-D148-44DD-9716-9C98DF0EE209@.microsoft.com...
> Hi Tibor,
> So the default sampling selected by SQL Server does not 'inherit' the
> previous histogram created WITH FULLSCAN, updating statistics as necessary
> based upon the previous values of RANGE_HI_KEY, but instead recreates the
> histogram from scratch, knowingly reducing the number of steps.
> Kind Regards
> Andrew Pike
> --
> SQL Server DBA
> UBS IB
>
> "Tibor Karaszi" wrote:
>> DBREINDEX will build the statistics based on all rows, i.e., no sampling. UPDATE STATISTICS
>> without
>> specifying sampling options *will* sample:
>> http://www.microsoft.com/technet/prodtechnol/sql/2005/qrystats.mspx
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "Andrew Pike" <AndrewPike@.discussions.microsoft.com> wrote in message
>> news:91ABAF37-7833-49F2-995C-2996B63A60B6@.microsoft.com...
>> >I have noticed that running UPDATE STATISTICS without specifying any sampling
>> > options, thus accepting the default selected by SQL Server, can reduce the
>> > level of detail contained within the index distribution statistics.
>> >
>> > As an example I ran DBCC SHOW_STATISTICS following the completion of a DBCC
>> > DBREINDEX maintenance job, where statistics are rebuilt effectively WITH
>> > FULLSCAN. The histogram has the maximum 200 steps. When a subsequent job
>> > runs to UPDATE STATISTICS, the number of histogram steps falls to 110, hence
>> > lessening the usefulness of the statistics. The values for EQ_ROWS are also
>> > much less accurate, which I suppose you'd expect with a smaller sample size.
>> > However, I do not understand what the optimizer gains from reducing the
>> > number of steps during an UPDATE STATISTICS execution.
>> >
>> > Kind Regards
>> >
>> > Andrew Pike
>> > --
>> > SQL Server DBA
>> > UBS IB
>> >
>>

Advance Update Statement Sql Server

I am trying to speed up my update statements by removing inner select
statements.

Example:
update orders set shipname = (select contactName from
customers where customerid = orders.customerID)

I read some articles which said that I should be able to use an inner
join on the update statement like the following:

update orders set shipname = (select contactName from customers where
customerid = orders.customerID)

But every time that I run this statement I get the follwing error:
Server: Msg 156, Level 15, State 1, Line 1
Incorrect syntax near the keyword 'inner'.

Any Help will be greatly appreciated.

Thank you.I think you posted the wrong UPDATE statement. Both those statements
are identical and valid syntax.

However, if performance is your concern then why not take SHIPNAME out
of the Orders table. It looks like it's redundant there.

--
David Portas
SQL Server MVP
--|||See "Changing Data Using the FROM Clause" and also example C under
UPDATE in Books Online (although it would be better to rewrite it with
INNER JOIN).

Simon|||Both statements were the same ?! I think I know what you meant to say
...

As long as the scalar query expression returns zero or one row, you
will be fine. If you use the proprietary FROM syntax, you will get an
unpredictable result from a multi-row result set.

The real cost of an update is in the physical disk access, not the
code.|||HeadScratcher (mayur@.servicemg.com) writes:
> I am trying to speed up my update statements by removing inner select
> statements.
> Example:
> update orders set shipname = (select contactName from
> customers where customerid = orders.customerID)
> I read some articles which said that I should be able to use an inner
> join on the update statement like the following:
> update orders set shipname = (select contactName from customers where
> customerid = orders.customerID)
> But every time that I run this statement I get the follwing error:
> Server: Msg 156, Level 15, State 1, Line 1
> Incorrect syntax near the keyword 'inner'.

Apparently there was some glitch in the editing. Anyway, this is what
you want:

UPDATE Orders
SET ShipName = c.ContactName
FROM Orders o
JOIN Customers c ON c.CustomerID = O.CustomerID

I suspect the problem is that you left out the FROM clause.

I left out INNER here, because this is implied.

I should add your original syntax is in alignment with ANSI standards,
whereas the syntax with FROM JOIN is proprietary to MS SQL Server and
Sybase (and possibly Informix). If you need portability, stick to the
original syntax. As long as you work with SQL Server only, do as you
please. Personally, I find the FROM/JOIN syntax very pleasant, as it
builds on the same paradigm as a regular SELECT statement. It is also
more effecient, if you need to update more than one column.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||David Portas (REMOVE_BEFORE_REPLYING_dportas@.acm.org) writes:
> However, if performance is your concern then why not take SHIPNAME out
> of the Orders table. It looks like it's redundant there.

Nah, I would not recommend people to drop columns from their
Northwind databases. :-)

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||>> I find the FROM/JOIN syntax very pleasant, as it builds on the same paradigm as a regular SELECT statement.<<

Arrrgh! The **consistent meaning in the Standard SQL model** of a
FROM clause is that a temporary working table is constructed, used and
dropped at the end of the statement. You would be updating a temporary
working table, not the base table.

The Sybase, Informix and MS SQL Server syntax might look the same, but
the semantics are all slightly different when you get to a 1:m
relationship. Moving the code is deadly -- it moves over to the next
platform and runs differently. With the ANSI syntax, the vendors have
to follow the same rules. This is a good thing.

Thursday, March 8, 2012

ADP/ADE Permissions Issue

I cannot add, update or delete records in a SQL Server database using ADP/ADE but I can within another database on the same server.

I have two databases. One is the test environment and the other is production. Both database reside on the same server but they have unique logins so as not to allow an admin in the test environment to automatically be an admin in production.

I can do everything and anything necessary through Enterprise Manager to both databases. I can do everything necessary to an Access ADP/ADE application in the test environment (add, update, delete) but I cannot do the same in the production database. I can connect and get a valid connection but the tables do not allow me to update or add rows to tables through the ADP/ADE application.

Given that the only difference to the application is which database it connects to I am left with the conclusion that there is something different to the login in the production environment that is not allowing the ADP application appropriate permissions. Can anyone please help or at least direct me to an answer to correct this problem?

Thanks!Well... I suppose this is what happens when you aren't in charge of the data and it is migrated by someone else.

After much digging I found that the tables in question did not have their primary key migrated with them to the production server. If the table did not have a primary key defined then ADP/ADE would not allow a user to add rows.

This was structural, not security. Hopefully someone else who runs across a similar problem in the future will find this information and more quickly correct their issue.

Saturday, February 25, 2012

ADO.NET Transaction Fails to update Database after .Commit()

SQL Server 2000, C#, ASP.NET and ADO.NET. I have searched for 3 days trying to figure out why my ADO.NET Transaction executes my stored procedures and runs the .COMMIT() on the server, figured this out by using the SQL Profiler, but the data doesn't show up in the database tables and I recieve no error from SQL or ASP.NET. What gives?? Anyone else ever heard of such a thing. Please help, before I loose my sanity.I may need to be more specific about the problem, so here goes. I am running an ADO.NET SQLTransaction that is calling 3 different stored procedures. The first proc Inserts data into a MailMessage Table and returns the @.@.IDENTITY of the Insert. The next set of stored procedures are called in a loop to Insert into a MailboxMessageAddressees table that has a foreign key of the MailMessages table and the ID of the Addressee. This loop can run 1-50+ times to insert all the Addressees. In SQL Profiler, I can trace the connection I made to a database and it starts the transaction, executes all stored procedures, and then COMMITS. I recieve no errors in my transaction and no errors occur on SQLServer. But the data is never inserted into the database. Its almost like the ADO.NET Transaction didn't take place, or SQL Server is rolling back the transaction on its own without throwing any errors that I can see.

Needless to say, I have closed the connection and disposed of the command and connection. Is there something else I need to send to SQL Server other than COMMIT to let it know that I have completed my transaction. This is really perplexing because I can manually execute all the stored procedures in Entrerprise Manager. I get every statement executed from the SQL Profiler when I trace the Transaction and put it in Query Analyzer and it works fine, and all the data shows up in SQL Server. Is there something I am missing?

Here is the actual code, maybe someone can clue me into what I might be missing.

/// <summary>
/// Mail_DAL.AddNewMessage(int Mailbox_ID,string subject,string body,ArrayList recipientTo,ArrayList recipientCc,ArrayList groupTo,ArrayList groupCc,ArrayList files,Wargame_ID)
/// ->Executes a ExecuteNonQuery using the Stored Procedure Mail_DeleteGroupAddressees and an ExecuteNonQuery using stored
/// procedure Mail_AddGroupAddressee to insert all addressees not in the Group.
/// </summary>
/// <param name="Mailbox_ID">Int: The Mailbox associated with the MailGroup.</param>
/// <param name="subject">String: The GroupName of the Group the addressees are associated with.</param>
/// <param name="body">String: The GroupName of the Group the addressees are associated with.</param>
/// <param name="recipientTo">ArrayList: An ArrayList of the To addresses Mailbox_IDs for the group.</param>
/// <param name="recipientCc">ArrayList: An ArrayList of the CC addresses Mailbox_IDs for the group.</param>
/// <param name="groupTo">ArrayList: An ArrayList of the To group addresses Mailbox_IDs for the group.</param>
/// <param name="groupCc">ArrayList: An ArrayList of the CC group addresses Mailbox_IDs for the group.</param>
/// <param name="files">ArrayList: An ArrayList of Files if there are any.</param>
/// <param name="Wargame_ID">Int: The Wargame_ID of any file to be put in Files</param>
public void AddNewMessage(int Mailbox_ID,string subject,string body,ArrayList recipientTo,ArrayList recipientCc,ArrayList groupTo,ArrayList groupCc,ArrayList files,int Wargame_ID)
{
//Get To Addresses from Groups
OpenProcCommand(Conn,"Mail_GetCurrentGroupAddressees");
SqlCommand cmd = ProcCommand;
param = new SqlParameter();
SqlDataReader dr;
Int32 holder = 0;
if(groupTo.Count > 0)
{
foreach(Object item in groupTo)
{
cmd.Parameters.Clear();
param = cmd.Parameters.Add("@.MailGroup_ID",SqlDbType.Int);
param.Value = item;
dr = cmd.ExecuteReader();
//Add any Group Recipients to Recipients ArrayList
while(dr.Read())
{
holder = Convert.ToInt32(dr["Mailbox_ID"].ToString());
if(!recipientTo.Contains(holder))
{
recipientTo.Add(holder);
}
}
dr.Close();
}
}

//Get CC Addresses from Groups
if(groupCc.Count > 0)
{
foreach(Object item in groupCc)
{
cmd.Parameters.Clear();
param = cmd.Parameters.Add("@.MailGroup_ID",SqlDbType.Int);
param.Value = item;
dr = cmd.ExecuteReader();
//Add any Group Recipients to Recipients ArrayList
while(dr.Read())
{
holder = Convert.ToInt32(dr["Mailbox_ID"].ToString());
if(!recipientCc.Contains(holder))
{
recipientCc.Add(holder);
}
}
dr.Close();
}
}

//Get BCC Recipients for this Maillbox
ArrayList recipientAll = new ArrayList();
recipientAll.Add(Convert.ToInt32(Mailbox_ID));
foreach(Object item in recipientTo)
{
recipientAll.Add(item);
}
foreach(Object item in recipientCc)
{
if(!recipientAll.Contains(item))
{
recipientAll.Add(item);
}
}
string allRecipients = "";
foreach(Object item in recipientAll)
{
allRecipients += item + ",";
}
allRecipients = allRecipients.Substring(0,allRecipients.Length-1);
ArrayList recipientBcc = new ArrayList();
cmd.CommandText = "Mail_GetBccAddressees";
cmd.Parameters.Clear();
param = cmd.Parameters.Add("@.Addressees",SqlDbType.VarChar,1000);
param.Value = allRecipients;
dr = cmd.ExecuteReader();
//Add any Bcc Recipients to Bcc Recipients ArrayList
while(dr.Read())
{
recipientBcc.Add(Convert.ToInt32(dr["Addressee"].ToString()));
}
dr.Close();

// Start a local transaction.
SqlTransaction myTrans = Conn.BeginTransaction();
// Enlist the command in the current transaction.
cmd.Transaction = myTrans;

try
{
//Insert Message into Database
cmd.CommandText = "Mail_AddNewMessage";
cmd.Parameters.Clear();
param = cmd.Parameters.Add("@.MsgFrom",SqlDbType.Int);
param.Value = Mailbox_ID;
param = cmd.Parameters.Add("@.MsgSubject",SqlDbType.VarChar,100);
param.Value = subject;
param = cmd.Parameters.Add("@.MsgBody",SqlDbType.Text);
param.Value = body;
int msg_ID = Convert.ToInt32(cmd.ExecuteScalar());

//Add To Recipients
cmd.CommandText = "Mail_AddMessageAddressees";
foreach(Object item in recipientTo)
{
cmd.Parameters.Clear();
param = cmd.Parameters.Add("@.Mailbox_ID",SqlDbType.Int);
param.Value = item;
param = cmd.Parameters.Add("@.Msg_ID",SqlDbType.Int);
param.Value = msg_ID;
param = cmd.Parameters.Add("@.MsgType_ID",SqlDbType.Int);
param.Value = 1;
cmd.ExecuteNonQuery();
}

//Add CC Recipients
foreach(Object item in recipientCc)
{
cmd.Parameters.Clear();
param = cmd.Parameters.Add("@.Mailbox_ID",SqlDbType.Int);
param.Value = item;
param = cmd.Parameters.Add("@.Msg_ID",SqlDbType.Int);
param.Value = msg_ID;
param = cmd.Parameters.Add("@.MsgType_ID",SqlDbType.Int);
param.Value = 2;
cmd.ExecuteNonQuery();
}

//Add BCC Recipients
foreach(Object item in recipientBcc)
{
cmd.Parameters.Clear();
param = cmd.Parameters.Add("@.Mailbox_ID",SqlDbType.Int);
param.Value = item;
param = cmd.Parameters.Add("@.Msg_ID",SqlDbType.Int);
param.Value = msg_ID;
param = cmd.Parameters.Add("@.MsgType_ID",SqlDbType.Int);
param.Value = 3;
cmd.ExecuteNonQuery();
}

//Add message to senders Mailbox in SentItems
cmd.Parameters.Clear();
param = cmd.Parameters.Add("@.Mailbox_ID",SqlDbType.Int);
param.Value = Mailbox_ID;
param = cmd.Parameters.Add("@.Msg_ID",SqlDbType.Int);
param.Value = msg_ID;
param = cmd.Parameters.Add("@.MsgType_ID",SqlDbType.Int);
param.Value = 4;
cmd.ExecuteNonQuery();

//Add files if there are any.
foreach(HttpPostedFile myfile in files)
{
//Get the file name
string FileName = myfile.FileName;
int strLoc = FileName.LastIndexOf("\\");
FileName = FileName.Remove(0,strLoc+1);

// Get size of uploaded file
int nFileLen = myfile.ContentLength;

// Allocate a buffer for reading of the file
byte[] myData = new byte[nFileLen];

// Read uploaded file from the Stream
myfile.InputStream.Read(myData, 0, nFileLen);
File file = new File();
int file_ID = file.UploadFile(cmd,3,FileName,nFileLen,myfile.GetType().ToString(),Wargame_ID,myData);

//Add the file message association
cmd.Parameters.Clear();
cmd.CommandText = "Mail_AddMessageFile";
param = cmd.Parameters.Add("@.Msg_ID",SqlDbType.Int);
param.Value = msg_ID;
param = cmd.Parameters.Add("@.File_ID",SqlDbType.Int);
param.Value = file_ID;
cmd.ExecuteNonQuery();
}
//Commit Transaction
myTrans.Commit();
}
catch (SqlException sqlex)
{
// Specific catch for deadlock
if (sqlex.Number != 1205)
{
myTrans.Rollback();
}
throw(sqlex);
}
catch(InvalidOperationException ex1){
string ex2 = ex1.ToString();
}
catch (Exception ex)
{
myTrans.Rollback();
throw (ex);
}
finally
{
myTrans.Dispose();
Conn.Close();
Conn.Dispose();
}

}|||First try to run a simple transaction with one stored proc and minimal .net code. This will make it easier to pinpoint your problem. Could be memory problem on server or some other potential problem you are not aware of.

This may not be a problem but I have encountered this one before. Are you returning more than one id with the @.@.IDENTITY call (i.e more than one insert command in a single transaction)? If you are you need to reset the sql connection or the same id is returned each time you call it if my memory serves me correctly.

Also, you can check the error status within a stored proc and return it to .net. I have used this before to pinpoint a problem.

Cheers

Mo|||Thanks Mo for the insight, unfortunatly I still have the same problems. Funny thing is I created a single stored procedure to do all the inserts, thinking that would solve the problem, but alas nothing. Getting different results, but same outcome. The database executes the procedure, which inserts data into the database, but for some reason after about 3 min or so the rows are rolled back. Running sp_lock I can see that the process that executed the command has a lock on all the tables, but it never gives them up. After that timeout, they are all rolled back. Is there some reason anyone can think of where this would happen. I can execute the stored procedure on the database using query analyzer and the data goes in fine and stays in there. Another thing to note, after the web application executes the stored procedure I cannot view the data in the tables through Enterprise manager. I can't even view the current activity, it sits there for a while then returns a 1222 Error. This has become a nightmare. Thanks

Chris|||Could be a bug between sql server and .net ?? Are both upto date with service packs?

Have you looked on http://support.microsoft.com

Very good area for problem solving, used it many times.

ADO.NET SP throws ArgumentException after migrating to SQL 2005 Standard - UPDATE 2 - FIXED

I just migrated a database from SQL 2000 Developer Edition on W2k Server to a production machine running SQL 2005 Standard on W2k3 Server Standard. A Web Service was also migrated from .NET 1.1 on the development machine to .NET 2.0 on the production machine.

Since the migration, a stored procedure that worked on SQL 2000/.NET 1.1 now throws a System.ArgumentException: "No mapping exists from object type System.RuntimeType to a known managed proider native type."

This occurs with a date value - the stored procedure is filled from XML data the Web Service receives from the client program by a generic subroutine that recognizes the applicable table names and primary key column names, selecting the appropriate stored procedure to use, but may or may not have data to update for each field. I have tried explicitly setting the SQLDBType for the parameter as the parametercollection is filled, but still get the ArgumentException. I also tried changing the parameter type to varchar(20), and having the stored procedure convert it to datetime, but still got the exception.

I know the ultimate solution will probably be to build the data Web Service within SQL 2005, but what can I do to get this working until I have time for that conversion?

Thanks for any help.

Darrell Escola

UPDATE - I was able to use the stored procedures to manually update the database using data that should have passed through the web service, so the problem is within the web service on .NET 2.0 - I think 2.0 is much stricter on data types than was 1.1. The SPs create dynamic SQL that is then executed - I know this is not the best security, hence my eventual interest in creating an end-point within SQL 2005 to handle the data in a more secure manner.

Darrell

|||

I finally got the web service to update through the stored procedure. I was able to access the stored procedure by specifically casting the data types for each parameter using a subroutine similar to that shown below, but the web service was still throwing an exception on the output value type, which I use to get the actual primary key value for the new record, so the off-line client can properly map its dependent records to the proper key value before inserting those dependent records:

Dim newID As SQLTypes.SQLInt32

I added a subroutine to determine the data type for each column used in the update:

Private Sub AddSPParameter(ByRef cmd as SQLClient.SQLCommand, ByVal paramName as String, ByVal fieldValue as Object)

If paramName = "<stringColName1>" Or paramName = "<stringColName...>" Then

cmd.Parameters.AddWithValue(Ctype(Cstr(fieldValue), SQLTypes.SQLString).DbType = DbType.String

ElseIf paramName = "<otherTypeColName1>" Or paramName.IndexOf("...") > 0 Or paramName.EndsWith("...") Then

cmd.Parameters.AddWithValue(Ctype(Cstr(fieldValue), SQLTypes.SQL<otherType>).DbType = DbType.<otherType>

ElseIf ...

End If

This subroutine determines the column data type by matches on the column name, and then explicitly casting the parameter value to the applicable database data type.

I also had to explicitly cast the Return Value:

cmd.Parameters.AddWithValue("@.ReturnValue", Ctype(0, SQLTypes.SQLInt32)).DbType = SqlDbType.Int

cmd.Parameters("@.ReturnValue").Direction = ParameterDirection.ReturnValue

Lesson learned: Explicitly type all data values used.

Darrell

Friday, February 24, 2012

ADO Update Efficiency

Hi All,

I tried my luck in the Access forum and I've search the web and MSDN for an answer with little luck.

Simply, is it better to update a table via an UPDATE query or Recordset manipulation?

I have read that if you were to update 10,000 records an UPDATE query is more efficient (obviously), but does that transend down to say 1 - 10 updates?

i.e. There are six unique updates I want to make to 6 different rows. Should I code the backend VB to execute 6 different queries or seek and update a recordset?

It's a MS Access XP app with ADO 2.8.

My gut feeling on this is that making 6 update queries is more efficient, both with system resources and record-locking issues; I'd just like another opinion on the matter.

I appreciate your help!
Thanks,
WarrenHow about this...

store the keys and values in an access table and join the sql server table to it and perform the update...

UPDATE s
SET Col1 = a.Col1
FROM SQLTable s INNER JOIN AccessTable a
ON s.key = a.key|||Hey Brett,

While that would be efficient, however the data is being populated via a form. To update a "search table" then run the query would include extra transactions: 6 queries/seeks to update the search table then another query to update the main table.

I decided on this:

'con = open connection
Con.Execute "UPDATE ...", , adExecuteNoRecords

I felt this would be more efficient than

'rs open with appropriate connection; seek and index support
rs.Index = "PrimaryKey"
rs.seek "pkval1", "pkval2", adSeekFirstEQ
rs!val1 = newval1
rs!val2 = newval2
rs.update

I really can't find any documentation benchmarking the efficiency of ADO updates; it just doesn't seem to be out there.

-Warren

Sunday, February 19, 2012

ADO Recorset / Set object = Nothing Issue?

I am having an issue of update statements not being
commited (lost). I am using ado 2.7 with vb6.
Here's an example that I am concerned about:
adoRS.Open "SELECT TOP 1 Field1 FROM MyTable"
adoRS.AddNew
adoRS.field("field1").value = "abc"
adoRS.update
set adoRS = Nothing
Since I am seting the ADORS object to nothing, is it
possible it's not finished executing and rolls the
transaction back?
Jason Roozeethis is technically the wrong group for this post, but here goes...
You need to make sure your cursor type supports the updates.
If I were you, I would not use rs.Update to post changes back to server.
You should consider using Command objects.
if you want more specifics, post back.
Greg Jackson
PDX, Oregon|||Your life will be a lot easier and your code will run faster if you
use SQL to update/insert/delete data. You can execute it in ADO from
either a Connection or a Command object. You also eliminate that extra
and unnecessary round trip to the database to create the recordset.
cmd.Execute "INSERT INTO MyTable (Field1) " & _
"VALUES ('" & stringvariable & "')"
-- Mary
MCW Technologies
http://www.mcwtech.com
On Fri, 6 Feb 2004 14:31:42 -0800, "Jason Roozee" <jason@.camcoinc.net>
wrote:
>I am having an issue of update statements not being
>commited (lost). I am using ado 2.7 with vb6.
>Here's an example that I am concerned about:
>adoRS.Open "SELECT TOP 1 Field1 FROM MyTable"
>adoRS.AddNew
>adoRS.field("field1").value = "abc"
>adoRS.update
>set adoRS = Nothing
>Since I am seting the ADORS object to nothing, is it
>possible it's not finished executing and rolls the
>transaction back?
>Jason Roozee|||Don't use a recordset to affect data. Recordsets are for *retrieving* data.
http://www.aspfaq.com/2191
--
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Jason Roozee" <jason@.camcoinc.net> wrote in message
news:b4bb01c3ed00$fe683150$a101280a@.phx.gbl...
> I am having an issue of update statements not being
> commited (lost). I am using ado 2.7 with vb6.
> Here's an example that I am concerned about:
> adoRS.Open "SELECT TOP 1 Field1 FROM MyTable"
> adoRS.AddNew
> adoRS.field("field1").value = "abc"
> adoRS.update
> set adoRS = Nothing
> Since I am seting the ADORS object to nothing, is it
> possible it's not finished executing and rolls the
> transaction back?
> Jason Roozee

ADO Recorset / Set object = Nothing Issue?

I am having an issue of update statements not being
commited (lost). I am using ado 2.7 with vb6.
Here's an example that I am concerned about:
adoRS.Open "SELECT TOP 1 Field1 FROM MyTable"
adoRS.AddNew
adoRS.field("field1").value = "abc"
adoRS.update
set adoRS = Nothing
Since I am seting the ADORS object to nothing, is it
possible it's not finished executing and rolls the
transaction back?
Jason Roozeethis is technically the wrong group for this post, but here goes...
You need to make sure your cursor type supports the updates.
If I were you, I would not use rs.Update to post changes back to server.
You should consider using Command objects.
if you want more specifics, post back.
Greg Jackson
PDX, Oregon|||Your life will be a lot easier and your code will run faster if you
use SQL to update/insert/delete data. You can execute it in ADO from
either a Connection or a Command object. You also eliminate that extra
and unnecessary round trip to the database to create the recordset.
cmd.Execute "INSERT INTO MyTable (Field1) " & _
"VALUES ('" & stringvariable & "')"
-- Mary
MCW Technologies
http://www.mcwtech.com
On Fri, 6 Feb 2004 14:31:42 -0800, "Jason Roozee" <jason@.camcoinc.net>
wrote:

>I am having an issue of update statements not being
>commited (lost). I am using ado 2.7 with vb6.
>Here's an example that I am concerned about :
>adoRS.Open "SELECT TOP 1 Field1 FROM MyTable"
>adoRS.AddNew
>adoRS.field("field1").value = "abc"
>adoRS.update
>set adoRS = Nothing
>Since I am seting the ADORS object to nothing, is it
>possible it's not finished executing and rolls the
>transaction back?
>Jason Roozee|||Don't use a recordset to affect data. Recordsets are for *retrieving* data.
http://www.aspfaq.com/2191
Aaron Bertrand
SQL Server MVP
http://www.aspfaq.com/
"Jason Roozee" <jason@.camcoinc.net> wrote in message
news:b4bb01c3ed00$fe683150$a101280a@.phx.gbl...
> I am having an issue of update statements not being
> commited (lost). I am using ado 2.7 with vb6.
> Here's an example that I am concerned about :
> adoRS.Open "SELECT TOP 1 Field1 FROM MyTable"
> adoRS.AddNew
> adoRS.field("field1").value = "abc"
> adoRS.update
> set adoRS = Nothing
> Since I am seting the ADORS object to nothing, is it
> possible it's not finished executing and rolls the
> transaction back?
> Jason Roozee

ADO query restricting to SELECT

Hi,
Can I restrict INSERT, DELETE, UPDATE, INTO queries in ADO? I get the
query as input from user. Run it using ADO connection. Display the result in
grid. However I want to allow only SELECT queries. Is there a property on
ADO which allows this? Otherwise I will have to do the parsing myself.
Thanks in advance.
AjeyHi
You will need to restrict the access with permissions at table level. If you
allow them to write their own queries instead of using a controlled query
builder or stored procedures then you will need to do your own parsing.
You should also read up on SQL Injection such as
http://www.sqlsecurity.com/DesktopDefault.aspx?tabid=23
John
"Ajey" wrote:
> Hi,
> Can I restrict INSERT, DELETE, UPDATE, INTO queries in ADO? I get the
> query as input from user. Run it using ADO connection. Display the result in
> grid. However I want to allow only SELECT queries. Is there a property on
> ADO which allows this? Otherwise I will have to do the parsing myself.
> Thanks in advance.
> Ajey
>
>|||Hi,
By the post I understand that, u have a layee between the database and the
user.
The user need to send a select query and the result is displayed in the Grid.
The users are presently having a flexibility to send the any kind of query.
If they send INSERT, UPDATE or DELETE, your data will be currupted, and you
wante to restrict that.
If my prediction was correct, what I suggest you is, to use a Stored
Procedure for this purpose or open the ADO with read only permissions.
I believe this answered your question. please revert back if u have any issues
thanks and regards
Chandra
"Ajey" wrote:
> Hi,
> Can I restrict INSERT, DELETE, UPDATE, INTO queries in ADO? I get the
> query as input from user. Run it using ADO connection. Display the result in
> grid. However I want to allow only SELECT queries. Is there a property on
> ADO which allows this? Otherwise I will have to do the parsing myself.
> Thanks in advance.
> Ajey
>
>|||Thanks.
Yes, that's what i want to do.
And also I want to prevent sql-injection. Thanks John.
I want to restrict the query to SELECT. I even if the user has permissions
to modify the table I don't want him to alter it through the query.
How can I open ADO with read only permissons?
Thanks in advance.
Ajey
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:CF97CE16-EFCF-4212-811C-A8C95E28D03E@.microsoft.com...
> Hi,
> By the post I understand that, u have a layee between the database and the
> user.
> The user need to send a select query and the result is displayed in the
Grid.
> The users are presently having a flexibility to send the any kind of
query.
> If they send INSERT, UPDATE or DELETE, your data will be currupted, and
you
> wante to restrict that.
> If my prediction was correct, what I suggest you is, to use a Stored
> Procedure for this purpose or open the ADO with read only permissions.
> I believe this answered your question. please revert back if u have any
issues
> thanks and regards
> Chandra
>
> "Ajey" wrote:
> > Hi,
> > Can I restrict INSERT, DELETE, UPDATE, INTO queries in ADO? I get
the
> > query as input from user. Run it using ADO connection. Display the
result in
> > grid. However I want to allow only SELECT queries. Is there a property
on
> > ADO which allows this? Otherwise I will have to do the parsing myself.
> >
> > Thanks in advance.
> > Ajey
> >
> >
> >
> >|||"Ajey" <ajey5@.hotmail.com> wrote in message
news:OfmIdX8SFHA.2560@.TK2MSFTNGP09.phx.gbl...
> Thanks.
> Yes, that's what i want to do.
> And also I want to prevent sql-injection. Thanks John.
> I want to restrict the query to SELECT. I even if the user has permissions
> to modify the table I don't want him to alter it through the query.
> How can I open ADO with read only permissons?
1. Set the Mode property of the underlying connection to adModeRead, and
2. Set the recordset.LockType property to adLockReadOnly
-Mark
> Thanks in advance.
> Ajey
> "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> news:CF97CE16-EFCF-4212-811C-A8C95E28D03E@.microsoft.com...
>> Hi,
>> By the post I understand that, u have a layee between the database and
>> the
>> user.
>> The user need to send a select query and the result is displayed in the
> Grid.
>> The users are presently having a flexibility to send the any kind of
> query.
>> If they send INSERT, UPDATE or DELETE, your data will be currupted, and
> you
>> wante to restrict that.
>> If my prediction was correct, what I suggest you is, to use a Stored
>> Procedure for this purpose or open the ADO with read only permissions.
>> I believe this answered your question. please revert back if u have any
> issues
>> thanks and regards
>> Chandra
>>
>> "Ajey" wrote:
>> > Hi,
>> > Can I restrict INSERT, DELETE, UPDATE, INTO queries in ADO? I get
> the
>> > query as input from user. Run it using ADO connection. Display the
> result in
>> > grid. However I want to allow only SELECT queries. Is there a property
> on
>> > ADO which allows this? Otherwise I will have to do the parsing myself.
>> >
>> > Thanks in advance.
>> > Ajey
>> >
>> >
>> >
>> >
>|||You can do this while you are opening the connection itself.
For More info refer to:
http://www.codeguru.com/vb/gen/vb_database/adonet/article.php/c5153/
"Ajey" wrote:
> Thanks.
> Yes, that's what i want to do.
> And also I want to prevent sql-injection. Thanks John.
> I want to restrict the query to SELECT. I even if the user has permissions
> to modify the table I don't want him to alter it through the query.
> How can I open ADO with read only permissons?
> Thanks in advance.
> Ajey
> "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> news:CF97CE16-EFCF-4212-811C-A8C95E28D03E@.microsoft.com...
> > Hi,
> > By the post I understand that, u have a layee between the database and the
> > user.
> > The user need to send a select query and the result is displayed in the
> Grid.
> >
> > The users are presently having a flexibility to send the any kind of
> query.
> > If they send INSERT, UPDATE or DELETE, your data will be currupted, and
> you
> > wante to restrict that.
> >
> > If my prediction was correct, what I suggest you is, to use a Stored
> > Procedure for this purpose or open the ADO with read only permissions.
> >
> > I believe this answered your question. please revert back if u have any
> issues
> >
> > thanks and regards
> > Chandra
> >
> >
> >
> > "Ajey" wrote:
> >
> > > Hi,
> > > Can I restrict INSERT, DELETE, UPDATE, INTO queries in ADO? I get
> the
> > > query as input from user. Run it using ADO connection. Display the
> result in
> > > grid. However I want to allow only SELECT queries. Is there a property
> on
> > > ADO which allows this? Otherwise I will have to do the parsing myself.
> > >
> > > Thanks in advance.
> > > Ajey
> > >
> > >
> > >
> > >
>
>|||It's not working:
Following is the sample code:
----
--
g_objConn.Mode = adModeRead
g_objConn.Open strConn, , , 0
Debug.Print g_objConn.Mode
strQuery = "select * from sysobjects select * from sysindexes insert
into testtable values ('xyz')"
Set objRS = New Recordset
'Set objRS = g_objConn.Execute(strQuery)
objRS.Open strQuery, g_objConn, adOpenForwardOnly, adLockReadOnly,
adCmdText
----
--
After the Open on recordset the insert is always successful.
Thanks.
Ajey
"Mark J. McGinty" <mmcginty@.spamfromyou.com> wrote in message
news:OWodsc8SFHA.3980@.TK2MSFTNGP12.phx.gbl...
> "Ajey" <ajey5@.hotmail.com> wrote in message
> news:OfmIdX8SFHA.2560@.TK2MSFTNGP09.phx.gbl...
> > Thanks.
> > Yes, that's what i want to do.
> > And also I want to prevent sql-injection. Thanks John.
> > I want to restrict the query to SELECT. I even if the user has
permissions
> > to modify the table I don't want him to alter it through the query.
> >
> > How can I open ADO with read only permissons?
> 1. Set the Mode property of the underlying connection to adModeRead, and
> 2. Set the recordset.LockType property to adLockReadOnly
>
> -Mark
>
>
> > Thanks in advance.
> > Ajey
> >
> > "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> > news:CF97CE16-EFCF-4212-811C-A8C95E28D03E@.microsoft.com...
> >> Hi,
> >> By the post I understand that, u have a layee between the database and
> >> the
> >> user.
> >> The user need to send a select query and the result is displayed in the
> > Grid.
> >>
> >> The users are presently having a flexibility to send the any kind of
> > query.
> >> If they send INSERT, UPDATE or DELETE, your data will be currupted, and
> > you
> >> wante to restrict that.
> >>
> >> If my prediction was correct, what I suggest you is, to use a Stored
> >> Procedure for this purpose or open the ADO with read only permissions.
> >>
> >> I believe this answered your question. please revert back if u have any
> > issues
> >>
> >> thanks and regards
> >> Chandra
> >>
> >>
> >>
> >> "Ajey" wrote:
> >>
> >> > Hi,
> >> > Can I restrict INSERT, DELETE, UPDATE, INTO queries in ADO? I
get
> > the
> >> > query as input from user. Run it using ADO connection. Display the
> > result in
> >> > grid. However I want to allow only SELECT queries. Is there a
property
> > on
> >> > ADO which allows this? Otherwise I will have to do the parsing
myself.
> >> >
> >> > Thanks in advance.
> >> > Ajey
> >> >
> >> >
> >> >
> >> >
> >
> >
>|||I am not using ADO.NET but it's a simple ADO application.
Thanks.
Ajey
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:F419CECC-2DF9-4C52-953F-CEA0334B0336@.microsoft.com...
> You can do this while you are opening the connection itself.
> For More info refer to:
> http://www.codeguru.com/vb/gen/vb_database/adonet/article.php/c5153/
>
>
> "Ajey" wrote:
> > Thanks.
> > Yes, that's what i want to do.
> > And also I want to prevent sql-injection. Thanks John.
> > I want to restrict the query to SELECT. I even if the user has
permissions
> > to modify the table I don't want him to alter it through the query.
> >
> > How can I open ADO with read only permissons?
> >
> > Thanks in advance.
> > Ajey
> >
> > "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> > news:CF97CE16-EFCF-4212-811C-A8C95E28D03E@.microsoft.com...
> > > Hi,
> > > By the post I understand that, u have a layee between the database and
the
> > > user.
> > > The user need to send a select query and the result is displayed in
the
> > Grid.
> > >
> > > The users are presently having a flexibility to send the any kind of
> > query.
> > > If they send INSERT, UPDATE or DELETE, your data will be currupted,
and
> > you
> > > wante to restrict that.
> > >
> > > If my prediction was correct, what I suggest you is, to use a Stored
> > > Procedure for this purpose or open the ADO with read only permissions.
> > >
> > > I believe this answered your question. please revert back if u have
any
> > issues
> > >
> > > thanks and regards
> > > Chandra
> > >
> > >
> > >
> > > "Ajey" wrote:
> > >
> > > > Hi,
> > > > Can I restrict INSERT, DELETE, UPDATE, INTO queries in ADO? I
get
> > the
> > > > query as input from user. Run it using ADO connection. Display the
> > result in
> > > > grid. However I want to allow only SELECT queries. Is there a
property
> > on
> > > > ADO which allows this? Otherwise I will have to do the parsing
myself.
> > > >
> > > > Thanks in advance.
> > > > Ajey
> > > >
> > > >
> > > >
> > > >
> >
> >
> >|||Hi Ajey
Will this be of any help:
===========
Set conn = New ADODB.Connection
conn.Open "dns=<>"
Dim rs As ADODB.Recordset
' Open the table.
Set rs = New ADODB.Recordset
rs.Open Query, conn, adOpenDynamic, adLockReadOnly
===========
thanks and regards
Chandar
"Ajey" wrote:
> I am not using ADO.NET but it's a simple ADO application.
> Thanks.
> Ajey
> "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> news:F419CECC-2DF9-4C52-953F-CEA0334B0336@.microsoft.com...
> > You can do this while you are opening the connection itself.
> >
> > For More info refer to:
> > http://www.codeguru.com/vb/gen/vb_database/adonet/article.php/c5153/
> >
> >
> >
> >
> > "Ajey" wrote:
> >
> > > Thanks.
> > > Yes, that's what i want to do.
> > > And also I want to prevent sql-injection. Thanks John.
> > > I want to restrict the query to SELECT. I even if the user has
> permissions
> > > to modify the table I don't want him to alter it through the query.
> > >
> > > How can I open ADO with read only permissons?
> > >
> > > Thanks in advance.
> > > Ajey
> > >
> > > "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> > > news:CF97CE16-EFCF-4212-811C-A8C95E28D03E@.microsoft.com...
> > > > Hi,
> > > > By the post I understand that, u have a layee between the database and
> the
> > > > user.
> > > > The user need to send a select query and the result is displayed in
> the
> > > Grid.
> > > >
> > > > The users are presently having a flexibility to send the any kind of
> > > query.
> > > > If they send INSERT, UPDATE or DELETE, your data will be currupted,
> and
> > > you
> > > > wante to restrict that.
> > > >
> > > > If my prediction was correct, what I suggest you is, to use a Stored
> > > > Procedure for this purpose or open the ADO with read only permissions.
> > > >
> > > > I believe this answered your question. please revert back if u have
> any
> > > issues
> > > >
> > > > thanks and regards
> > > > Chandra
> > > >
> > > >
> > > >
> > > > "Ajey" wrote:
> > > >
> > > > > Hi,
> > > > > Can I restrict INSERT, DELETE, UPDATE, INTO queries in ADO? I
> get
> > > the
> > > > > query as input from user. Run it using ADO connection. Display the
> > > result in
> > > > > grid. However I want to allow only SELECT queries. Is there a
> property
> > > on
> > > > > ADO which allows this? Otherwise I will have to do the parsing
> myself.
> > > > >
> > > > > Thanks in advance.
> > > > > Ajey
> > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>|||This seems to be doing the trick. I get following error when I try to
execute multiple queries:
sp_cursoropen/sp_cursorprepare: The statement parameter can only be a single
select or a single stored procedure. : Microsoft OLE DB Provider for SQL
Server
But I don't want to use adOpenDynamic but adOpenForwardOnly cursor type.
At least this now blocks the user from executing multiple statement and so i
can check only the first token to be SELECT.
But can I achive same using adOpenForwardOnly cursor type.
Thanks.
Ajey
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:34579EEE-DFDA-4768-9CD8-B5AAFEBE66B3@.microsoft.com...
> Hi Ajey
> Will this be of any help:
> ===========> Set conn = New ADODB.Connection
> conn.Open "dns=<>"
>
> Dim rs As ADODB.Recordset
> ' Open the table.
> Set rs = New ADODB.Recordset
> rs.Open Query, conn, adOpenDynamic, adLockReadOnly
> ===========> thanks and regards
> Chandar
>
> "Ajey" wrote:
> > I am not using ADO.NET but it's a simple ADO application.
> >
> > Thanks.
> > Ajey
> >
> > "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> > news:F419CECC-2DF9-4C52-953F-CEA0334B0336@.microsoft.com...
> > > You can do this while you are opening the connection itself.
> > >
> > > For More info refer to:
> > > http://www.codeguru.com/vb/gen/vb_database/adonet/article.php/c5153/
> > >
> > >
> > >
> > >
> > > "Ajey" wrote:
> > >
> > > > Thanks.
> > > > Yes, that's what i want to do.
> > > > And also I want to prevent sql-injection. Thanks John.
> > > > I want to restrict the query to SELECT. I even if the user has
> > permissions
> > > > to modify the table I don't want him to alter it through the query.
> > > >
> > > > How can I open ADO with read only permissons?
> > > >
> > > > Thanks in advance.
> > > > Ajey
> > > >
> > > > "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> > > > news:CF97CE16-EFCF-4212-811C-A8C95E28D03E@.microsoft.com...
> > > > > Hi,
> > > > > By the post I understand that, u have a layee between the database
and
> > the
> > > > > user.
> > > > > The user need to send a select query and the result is displayed
in
> > the
> > > > Grid.
> > > > >
> > > > > The users are presently having a flexibility to send the any kind
of
> > > > query.
> > > > > If they send INSERT, UPDATE or DELETE, your data will be
currupted,
> > and
> > > > you
> > > > > wante to restrict that.
> > > > >
> > > > > If my prediction was correct, what I suggest you is, to use a
Stored
> > > > > Procedure for this purpose or open the ADO with read only
permissions.
> > > > >
> > > > > I believe this answered your question. please revert back if u
have
> > any
> > > > issues
> > > > >
> > > > > thanks and regards
> > > > > Chandra
> > > > >
> > > > >
> > > > >
> > > > > "Ajey" wrote:
> > > > >
> > > > > > Hi,
> > > > > > Can I restrict INSERT, DELETE, UPDATE, INTO queries in ADO?
I
> > get
> > > > the
> > > > > > query as input from user. Run it using ADO connection. Display
the
> > > > result in
> > > > > > grid. However I want to allow only SELECT queries. Is there a
> > property
> > > > on
> > > > > > ADO which allows this? Otherwise I will have to do the parsing
> > myself.
> > > > > >
> > > > > > Thanks in advance.
> > > > > > Ajey
> > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >|||Hi Ajey
Good to know that ur problem is getting solved. adLockReadOnly will prevent
the users from using INSERT, DELETE and UPDATE. The cursor type is optional.
You can ignore it and procede further.
thanks and regards
Chandra
"Ajey" wrote:
> This seems to be doing the trick. I get following error when I try to
> execute multiple queries:
> sp_cursoropen/sp_cursorprepare: The statement parameter can only be a single
> select or a single stored procedure. : Microsoft OLE DB Provider for SQL
> Server
> But I don't want to use adOpenDynamic but adOpenForwardOnly cursor type.
> At least this now blocks the user from executing multiple statement and so i
> can check only the first token to be SELECT.
> But can I achive same using adOpenForwardOnly cursor type.
> Thanks.
> Ajey
> "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> news:34579EEE-DFDA-4768-9CD8-B5AAFEBE66B3@.microsoft.com...
> > Hi Ajey
> >
> > Will this be of any help:
> >
> > ===========> >
> > Set conn = New ADODB.Connection
> > conn.Open "dns=<>"
> >
> >
> >
> > Dim rs As ADODB.Recordset
> >
> > ' Open the table.
> > Set rs = New ADODB.Recordset
> > rs.Open Query, conn, adOpenDynamic, adLockReadOnly
> >
> > ===========> >
> > thanks and regards
> > Chandar
> >
> >
> > "Ajey" wrote:
> >
> > > I am not using ADO.NET but it's a simple ADO application.
> > >
> > > Thanks.
> > > Ajey
> > >
> > > "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> > > news:F419CECC-2DF9-4C52-953F-CEA0334B0336@.microsoft.com...
> > > > You can do this while you are opening the connection itself.
> > > >
> > > > For More info refer to:
> > > > http://www.codeguru.com/vb/gen/vb_database/adonet/article.php/c5153/
> > > >
> > > >
> > > >
> > > >
> > > > "Ajey" wrote:
> > > >
> > > > > Thanks.
> > > > > Yes, that's what i want to do.
> > > > > And also I want to prevent sql-injection. Thanks John.
> > > > > I want to restrict the query to SELECT. I even if the user has
> > > permissions
> > > > > to modify the table I don't want him to alter it through the query.
> > > > >
> > > > > How can I open ADO with read only permissons?
> > > > >
> > > > > Thanks in advance.
> > > > > Ajey
> > > > >
> > > > > "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> > > > > news:CF97CE16-EFCF-4212-811C-A8C95E28D03E@.microsoft.com...
> > > > > > Hi,
> > > > > > By the post I understand that, u have a layee between the database
> and
> > > the
> > > > > > user.
> > > > > > The user need to send a select query and the result is displayed
> in
> > > the
> > > > > Grid.
> > > > > >
> > > > > > The users are presently having a flexibility to send the any kind
> of
> > > > > query.
> > > > > > If they send INSERT, UPDATE or DELETE, your data will be
> currupted,
> > > and
> > > > > you
> > > > > > wante to restrict that.
> > > > > >
> > > > > > If my prediction was correct, what I suggest you is, to use a
> Stored
> > > > > > Procedure for this purpose or open the ADO with read only
> permissions.
> > > > > >
> > > > > > I believe this answered your question. please revert back if u
> have
> > > any
> > > > > issues
> > > > > >
> > > > > > thanks and regards
> > > > > > Chandra
> > > > > >
> > > > > >
> > > > > >
> > > > > > "Ajey" wrote:
> > > > > >
> > > > > > > Hi,
> > > > > > > Can I restrict INSERT, DELETE, UPDATE, INTO queries in ADO?
> I
> > > get
> > > > > the
> > > > > > > query as input from user. Run it using ADO connection. Display
> the
> > > > > result in
> > > > > > > grid. However I want to allow only SELECT queries. Is there a
> > > property
> > > > > on
> > > > > > > ADO which allows this? Otherwise I will have to do the parsing
> > > myself.
> > > > > > >
> > > > > > > Thanks in advance.
> > > > > > > Ajey
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > >
> > > > >
> > > > >
> > > > >
> > >
> > >
> > >
>
>|||The error comes only when I specify the cusrosor type as adOpenDynamic and
not when adOpenForwardOnly
"Chandra" <Chandra@.discussions.microsoft.com> wrote in message
news:7F7945AF-AACD-4545-B5F2-1ACE9D3FD1D7@.microsoft.com...
> Hi Ajey
> Good to know that ur problem is getting solved. adLockReadOnly will
prevent
> the users from using INSERT, DELETE and UPDATE. The cursor type is
optional.
> You can ignore it and procede further.
> thanks and regards
> Chandra
>
>
> "Ajey" wrote:
> > This seems to be doing the trick. I get following error when I try to
> > execute multiple queries:
> > sp_cursoropen/sp_cursorprepare: The statement parameter can only be a
single
> > select or a single stored procedure. : Microsoft OLE DB Provider for SQL
> > Server
> >
> > But I don't want to use adOpenDynamic but adOpenForwardOnly cursor type.
> >
> > At least this now blocks the user from executing multiple statement and
so i
> > can check only the first token to be SELECT.
> > But can I achive same using adOpenForwardOnly cursor type.
> >
> > Thanks.
> > Ajey
> >
> > "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> > news:34579EEE-DFDA-4768-9CD8-B5AAFEBE66B3@.microsoft.com...
> > > Hi Ajey
> > >
> > > Will this be of any help:
> > >
> > > ===========> > >
> > > Set conn = New ADODB.Connection
> > > conn.Open "dns=<>"
> > >
> > >
> > >
> > > Dim rs As ADODB.Recordset
> > >
> > > ' Open the table.
> > > Set rs = New ADODB.Recordset
> > > rs.Open Query, conn, adOpenDynamic, adLockReadOnly
> > >
> > > ===========> > >
> > > thanks and regards
> > > Chandar
> > >
> > >
> > > "Ajey" wrote:
> > >
> > > > I am not using ADO.NET but it's a simple ADO application.
> > > >
> > > > Thanks.
> > > > Ajey
> > > >
> > > > "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> > > > news:F419CECC-2DF9-4C52-953F-CEA0334B0336@.microsoft.com...
> > > > > You can do this while you are opening the connection itself.
> > > > >
> > > > > For More info refer to:
> > > > >
http://www.codeguru.com/vb/gen/vb_database/adonet/article.php/c5153/
> > > > >
> > > > >
> > > > >
> > > > >
> > > > > "Ajey" wrote:
> > > > >
> > > > > > Thanks.
> > > > > > Yes, that's what i want to do.
> > > > > > And also I want to prevent sql-injection. Thanks John.
> > > > > > I want to restrict the query to SELECT. I even if the user has
> > > > permissions
> > > > > > to modify the table I don't want him to alter it through the
query.
> > > > > >
> > > > > > How can I open ADO with read only permissons?
> > > > > >
> > > > > > Thanks in advance.
> > > > > > Ajey
> > > > > >
> > > > > > "Chandra" <Chandra@.discussions.microsoft.com> wrote in message
> > > > > > news:CF97CE16-EFCF-4212-811C-A8C95E28D03E@.microsoft.com...
> > > > > > > Hi,
> > > > > > > By the post I understand that, u have a layee between the
database
> > and
> > > > the
> > > > > > > user.
> > > > > > > The user need to send a select query and the result is
displayed
> > in
> > > > the
> > > > > > Grid.
> > > > > > >
> > > > > > > The users are presently having a flexibility to send the any
kind
> > of
> > > > > > query.
> > > > > > > If they send INSERT, UPDATE or DELETE, your data will be
> > currupted,
> > > > and
> > > > > > you
> > > > > > > wante to restrict that.
> > > > > > >
> > > > > > > If my prediction was correct, what I suggest you is, to use a
> > Stored
> > > > > > > Procedure for this purpose or open the ADO with read only
> > permissions.
> > > > > > >
> > > > > > > I believe this answered your question. please revert back if u
> > have
> > > > any
> > > > > > issues
> > > > > > >
> > > > > > > thanks and regards
> > > > > > > Chandra
> > > > > > >
> > > > > > >
> > > > > > >
> > > > > > > "Ajey" wrote:
> > > > > > >
> > > > > > > > Hi,
> > > > > > > > Can I restrict INSERT, DELETE, UPDATE, INTO queries in
ADO?
> > I
> > > > get
> > > > > > the
> > > > > > > > query as input from user. Run it using ADO connection.
Display
> > the
> > > > > > result in
> > > > > > > > grid. However I want to allow only SELECT queries. Is there
a
> > > > property
> > > > > > on
> > > > > > > > ADO which allows this? Otherwise I will have to do the
parsing
> > > > myself.
> > > > > > > >
> > > > > > > > Thanks in advance.
> > > > > > > > Ajey
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > > > >
> > > > > >
> > > > > >
> > > > > >
> > > >
> > > >
> > > >
> >
> >
> >|||Also, I created a stored procedure sp_my_sp2 which raises an error on
execution.
When I try to run multiple queries as:
sp_my_sp2 select * from sysobjects
I can verify that the stored proc as well as the SELECT query gets executed.
But for the following:
select * from sysobjects sp_my_sp2
only the SELECT query gets executed.
What's the behavior for multiple queries in SQL Server?
Thanks in advance.
Ajey
"Ajey" <ajey5@.hotmail.com> wrote in message
news:e8bxOD8SFHA.612@.TK2MSFTNGP12.phx.gbl...
> Hi,
> Can I restrict INSERT, DELETE, UPDATE, INTO queries in ADO? I get the
> query as input from user. Run it using ADO connection. Display the result
in
> grid. However I want to allow only SELECT queries. Is there a property on
> ADO which allows this? Otherwise I will have to do the parsing myself.
> Thanks in advance.
> Ajey
>
>|||"Ajey" <ajey5@.hotmail.com> wrote in message
news:%23Dd%23xs$SFHA.2520@.TK2MSFTNGP09.phx.gbl...
> Also, I created a stored procedure sp_my_sp2 which raises an error on
> execution.
> When I try to run multiple queries as:
> sp_my_sp2 select * from sysobjects
> I can verify that the stored proc as well as the SELECT query gets
> executed.
> But for the following:
> select * from sysobjects sp_my_sp2
select * from sysobjects; exec sp_my_sp2
-Mark
> only the SELECT query gets executed.
> What's the behavior for multiple queries in SQL Server?
> Thanks in advance.
> Ajey
>
> "Ajey" <ajey5@.hotmail.com> wrote in message
> news:e8bxOD8SFHA.612@.TK2MSFTNGP12.phx.gbl...
>> Hi,
>> Can I restrict INSERT, DELETE, UPDATE, INTO queries in ADO? I get
>> the
>> query as input from user. Run it using ADO connection. Display the result
> in
>> grid. However I want to allow only SELECT queries. Is there a property on
>> ADO which allows this? Otherwise I will have to do the parsing myself.
>> Thanks in advance.
>> Ajey
>>
>|||That helps to execute the stored procedure as well
I wanted to make sure that user does not enter any other statement other
than SELECT. I can put restriction on having keywords INSERT, UPDATE,
DELETE, INTO, EXEC, EXECUTE in the query string but what about stored
procedure given as mentioned below. With the execution i verified that in
this case the stored procedure does not get executed. But is this behavior
documented?
"Mark J. McGinty" <mmcginty@.spamfromyou.com> wrote in message
news:2j6ce.1$ZN.0@.fed1read07...
> "Ajey" <ajey5@.hotmail.com> wrote in message
> news:%23Dd%23xs$SFHA.2520@.TK2MSFTNGP09.phx.gbl...
> > Also, I created a stored procedure sp_my_sp2 which raises an error on
> > execution.
> > When I try to run multiple queries as:
> > sp_my_sp2 select * from sysobjects
> > I can verify that the stored proc as well as the SELECT query gets
> > executed.
> >
> > But for the following:
> > select * from sysobjects sp_my_sp2
>
> select * from sysobjects; exec sp_my_sp2
>
> -Mark
>
>
> > only the SELECT query gets executed.
> >
> > What's the behavior for multiple queries in SQL Server?
> >
> > Thanks in advance.
> > Ajey
> >
> >
> > "Ajey" <ajey5@.hotmail.com> wrote in message
> > news:e8bxOD8SFHA.612@.TK2MSFTNGP12.phx.gbl...
> >> Hi,
> >> Can I restrict INSERT, DELETE, UPDATE, INTO queries in ADO? I get
> >> the
> >> query as input from user. Run it using ADO connection. Display the
result
> > in
> >> grid. However I want to allow only SELECT queries. Is there a property
on
> >> ADO which allows this? Otherwise I will have to do the parsing myself.
> >>
> >> Thanks in advance.
> >> Ajey
> >>
> >>
> >>
> >
> >
>|||"Ajey" <ajey5@.hotmail.com> wrote in message
news:uAiO1EATFHA.2996@.TK2MSFTNGP15.phx.gbl...
> That helps to execute the stored procedure as well
> I wanted to make sure that user does not enter any other statement other
> than SELECT. I can put restriction on having keywords INSERT, UPDATE,
> DELETE, INTO, EXEC, EXECUTE in the query string but what about stored
> procedure given as mentioned below. With the execution i verified that in
> this case the stored procedure does not get executed. But is this behavior
> documented?
Not sure if it's documented, but it has been that way forever (given that
SQL 6.5 marked the beginning of time.)
Just the stored procedure name (with args if any) alone will work for a
single statement, but you must use exec for each statement in a batch.
-Mark
> "Mark J. McGinty" <mmcginty@.spamfromyou.com> wrote in message
> news:2j6ce.1$ZN.0@.fed1read07...
>> "Ajey" <ajey5@.hotmail.com> wrote in message
>> news:%23Dd%23xs$SFHA.2520@.TK2MSFTNGP09.phx.gbl...
>> > Also, I created a stored procedure sp_my_sp2 which raises an error on
>> > execution.
>> > When I try to run multiple queries as:
>> > sp_my_sp2 select * from sysobjects
>> > I can verify that the stored proc as well as the SELECT query gets
>> > executed.
>> >
>> > But for the following:
>> > select * from sysobjects sp_my_sp2
>>
>> select * from sysobjects; exec sp_my_sp2
>>
>> -Mark
>>
>>
>> > only the SELECT query gets executed.
>> >
>> > What's the behavior for multiple queries in SQL Server?
>> >
>> > Thanks in advance.
>> > Ajey
>> >
>> >
>> > "Ajey" <ajey5@.hotmail.com> wrote in message
>> > news:e8bxOD8SFHA.612@.TK2MSFTNGP12.phx.gbl...
>> >> Hi,
>> >> Can I restrict INSERT, DELETE, UPDATE, INTO queries in ADO? I get
>> >> the
>> >> query as input from user. Run it using ADO connection. Display the
> result
>> > in
>> >> grid. However I want to allow only SELECT queries. Is there a property
> on
>> >> ADO which allows this? Otherwise I will have to do the parsing myself.
>> >>
>> >> Thanks in advance.
>> >> Ajey
>> >>
>> >>
>> >>
>> >
>> >
>>
>|||Thank Mark.
I even verified with the query execution plan to make sure that the stored
proc does not get executed. But then why no error is shown for the stored
proc name.
"Mark J. McGinty" <mmcginty@.spamfromyou.com> wrote in message
news:2g8ce.17$ZN.14@.fed1read07...
> "Ajey" <ajey5@.hotmail.com> wrote in message
> news:uAiO1EATFHA.2996@.TK2MSFTNGP15.phx.gbl...
> > That helps to execute the stored procedure as well
> > I wanted to make sure that user does not enter any other statement other
> > than SELECT. I can put restriction on having keywords INSERT, UPDATE,
> > DELETE, INTO, EXEC, EXECUTE in the query string but what about stored
> > procedure given as mentioned below. With the execution i verified that
in
> > this case the stored procedure does not get executed. But is this
behavior
> > documented?
> Not sure if it's documented, but it has been that way forever (given that
> SQL 6.5 marked the beginning of time.)
> Just the stored procedure name (with args if any) alone will work for a
> single statement, but you must use exec for each statement in a batch.
> -Mark
>
>
> > "Mark J. McGinty" <mmcginty@.spamfromyou.com> wrote in message
> > news:2j6ce.1$ZN.0@.fed1read07...
> >>
> >> "Ajey" <ajey5@.hotmail.com> wrote in message
> >> news:%23Dd%23xs$SFHA.2520@.TK2MSFTNGP09.phx.gbl...
> >> > Also, I created a stored procedure sp_my_sp2 which raises an error on
> >> > execution.
> >> > When I try to run multiple queries as:
> >> > sp_my_sp2 select * from sysobjects
> >> > I can verify that the stored proc as well as the SELECT query gets
> >> > executed.
> >> >
> >> > But for the following:
> >> > select * from sysobjects sp_my_sp2
> >>
> >>
> >> select * from sysobjects; exec sp_my_sp2
> >>
> >>
> >> -Mark
> >>
> >>
> >>
> >>
> >>
> >> > only the SELECT query gets executed.
> >> >
> >> > What's the behavior for multiple queries in SQL Server?
> >> >
> >> > Thanks in advance.
> >> > Ajey
> >> >
> >> >
> >> > "Ajey" <ajey5@.hotmail.com> wrote in message
> >> > news:e8bxOD8SFHA.612@.TK2MSFTNGP12.phx.gbl...
> >> >> Hi,
> >> >> Can I restrict INSERT, DELETE, UPDATE, INTO queries in ADO? I
get
> >> >> the
> >> >> query as input from user. Run it using ADO connection. Display the
> > result
> >> > in
> >> >> grid. However I want to allow only SELECT queries. Is there a
property
> > on
> >> >> ADO which allows this? Otherwise I will have to do the parsing
myself.
> >> >>
> >> >> Thanks in advance.
> >> >> Ajey
> >> >>
> >> >>
> >> >>
> >> >
> >> >
> >>
> >>
> >
> >
>

Monday, February 13, 2012

ADO and the RowID

I am trying to update a SQL db record with ADO commands from an asp page thru a stored proc using the RowID and it is not working. RowID is the Identity seed record.

Here is the stored proc:

CREATE PROCEDURE [sp_Update_tblECMTimeTrackingMain]
@.RowID int,
@.StartTime datetime,
@.EndTime datetime,
@.TransxStatus varchar(50)
AS
Begin
UPDATE [tblECMTimeTrackingMain]
SET FStartTime = @.StartTime,
FEndTime = @.EndTime,
TransxStatus = @.TransxStatus
where RowID = @.RowID
End
GO

Here is the asp/ado code:

set cmdINSERT = Server.CreateObject("ADODB.command") cmdINSERT.ActiveConnection = strCONN_DATA cmdINSERT.CommandText = "[sp_Update_tblECMTimeTrackingMain]" cmdINSERT.CommandType = 4

set param = cmdINSERT.CreateParameter("@.RowID",3,1,4) cmdINSERT.Parameters.Append param

set param = cmdINSERT.CreateParameter("@.FStartTime",135,1,8) cmdINSERT.Parameters.Append param

set param = cmdINSERT.CreateParameter("@.FEndTime",135,1,8) cmdINSERT.Parameters.Append param

set param = cmdINSERT.CreateParameter("@.TransxStatus",129,1,50) cmdINSERT.Parameters.Append param

cmdINSERT.Parameters(0) = CInt(mACTREFNUM) ...when I change this to "568" it actually does the update. Yes there is something in mACTREFNUM.

cmdINSERT.Parameters(1) = meStartTime

cmdINSERT.Parameters(2) = meEndTime

cmdINSERT.Parameters(3) = meStatus

cmdINSERT.Execute lngRECS,,128

Any clues?

Is there any indication as to what, if any, error is happening? What value is stored in mACTREFNUM and can you verify that the result of CInt(mACTREFNUM) is, in fact, an integer value?

Thanks,

Jason

ADO and the RowID

I am trying to update a SQL db record with ADO commands from an asp page thru a stored proc using the RowID and it is not working. RowID is the Identity seed record.

Here is the stored proc:

Here is the asp/ado code:

set cmdINSERT = Server.CreateObject("ADODB.command") cmdINSERT.ActiveConnection = strCONN_DATA cmdINSERT.CommandText = "[sp_Update_tblECMTimeTrackingMain]" cmdINSERT.CommandType = 4

set param = cmdINSERT.CreateParameter("@.RowID",3,1,4) cmdINSERT.Parameters.Append param

set param = cmdINSERT.CreateParameter("@.FStartTime",135,1,8) cmdINSERT.Parameters.Append param

set param = cmdINSERT.CreateParameter("@.FEndTime",135,1,8) cmdINSERT.Parameters.Append param

set param = cmdINSERT.CreateParameter("@.TransxStatus",129,1,50) cmdINSERT.Parameters.Append param

cmdINSERT.Parameters(0) = CInt(mACTREFNUM) ...when I change this to "568" it actually does the update. Yes there is something in mACTREFNUM.

cmdINSERT.Parameters(1) = meStartTime

cmdINSERT.Parameters(2) = meEndTime

cmdINSERT.Parameters(3) = meStatus

cmdINSERT.Execute lngRECS,,128

Any clues?

Is there any indication as to what, if any, error is happening? What value is stored in mACTREFNUM and can you verify that the result of CInt(mACTREFNUM) is, in fact, an integer value?

Thanks,

Jason

Tuesday, March 27, 2012

advice normalization

Sunday, March 25, 2012

advice

Monday, March 19, 2012

Advanced SQL generations options

Sunday, March 11, 2012

Advanced Index Statistics Query

Advanced Index Statistics Query

Advanced Index Statistics Query

Advance Update Statement Sql Server

Thursday, March 8, 2012

ADP/ADE Permissions Issue

Saturday, February 25, 2012

ADO.NET Transaction Fails to update Database after .Commit()

ADO.NET SP throws ArgumentException after migrating to SQL 2005 Standard - UPDATE 2 - FIXED

Friday, February 24, 2012

ADO Update Efficiency

Sunday, February 19, 2012

ADO Recorset / Set object = Nothing Issue?

ADO Recorset / Set object = Nothing Issue?

ADO query restricting to SELECT

Monday, February 13, 2012

ADO and the RowID

ADO and the RowID

SQL Adding a User account

Blog Archive

About Me