Access data update problem

I have developed an Access 2002 fornt end for an SQL
database. When an form is run, it opens a limited set of
data that is updatable - the sql table is updated directly
from the access table, query or form. This works fine on
our intranet. When accessed from the internet, however, I
am only able to to update data in the table; when I run
the query or the form, the keyboard beeps when I try to
update any value. I have run the analyzer on the query,
and it shows that the dataupdatable value is false (inside
our intranet, the value shows true), but I cannot figure
out how to change this.
I have simplified the query to only draw information from
one table, but cannot update the data in the query
datasheet view or on a resulting form, only on the
original data table.Access is not designed for or intended to be used as the front end for
an Internet application. I'd look at using ASP.NET or some other more
suitable technology for the Internet.
-- Mary
MCW Technologies
http://www.mcwtech.com
On Fri, 6 Feb 2004 14:40:42 -0800, "Don" <don@.ameritest.net> wrote:

>I have developed an Access 2002 fornt end for an SQL
>database. When an form is run, it opens a limited set of
>data that is updatable - the sql table is updated directly
>from the access table, query or form. This works fine on
>our intranet. When accessed from the internet, however, I
>am only able to to update data in the table; when I run
>the query or the form, the keyboard beeps when I try to
>update any value. I have run the analyzer on the query,
>and it shows that the dataupdatable value is false (inside
>our intranet, the value shows true), but I cannot figure
>out how to change this.
>I have simplified the query to only draw information from
>one table, but cannot update the data in the query
>datasheet view or on a resulting form, only on the
>original data table.|||Hi Don,
Thank you for using the newsgroup and it is my pleasure to help you with
your issue.
From the information you provided, you have an Access 2002 front end and a
SQL Server backend. I assume it is a ADP. You found every thing is fine on
intranet but when you run it in a internet environment, you found you could
only able to update data in the table but cannot update from query or form,
right?
Well, could you please provide some informaiton, which is helpful for our
analysis:
As you mentioned that you cannot change the value from the query and the
form, beside the keyboard beep, is there any message you got? You mentioned
that you could update the the record in table, you could please provide the
information of how you modified the record and could you confirm that the
modification is made on the SQL Server side? As a ADP, could provide the
information of the connection of it? You could get the information by
choose from the menu, 'File'->'Connection', then get the information of
Server Name, the authentication mode and related logins you use and the the
database you connect to? This information is much helpful to us.
I am standing by to you reply and thanks.
Best regards
Baisong Wei
Microsoft Online Support
----
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only. Thanks.

Access crashes when updating a stored procedure

Hello,

I am having a problem when using access xp as a frontend for sql server
2000.
I have been trying to update a number of stored procedures (Just simple
adding fields etc) which results in access crashing with event ID 1000 and
1001.
Does anyone have any ideas as to what could be the problem?

Thanks in advance..On Mon, 11 Oct 2004 17:17:54 +0100, "8leggeddj" <me@.home.com> wrote:

>Hello,
>I am having a problem when using access xp as a frontend for sql server
>2000.
>I have been trying to update a number of stored procedures (Just simple
>adding fields etc) which results in access crashing with event ID 1000 and
>1001.
>Does anyone have any ideas as to what could be the problem?
>Thanks in advance..

Access could have corrupted metadata stored in the extended attributes of the
stored procedure. Try copying the SQL from the stored procedure, deleting the
procedure, and creating it again. Paste the SQL back in to recover the old
procedure definition (whic will not include the old extended attributes).|||Thanks for the advice. I have done this in the past when things go wrong,
unfortunately this time Access crashes again when I try to save the new
stored procedure.

"Steve Jorgensen" <nospam@.nospam.nospam> wrote in message
news:sgdlm05d9otcv31ms4lbdenfvnsdvog2m4@.4ax.com...
> On Mon, 11 Oct 2004 17:17:54 +0100, "8leggeddj" <me@.home.com> wrote:
>>Hello,
>>
>>I am having a problem when using access xp as a frontend for sql server
>>2000.
>>I have been trying to update a number of stored procedures (Just simple
>>adding fields etc) which results in access crashing with event ID 1000 and
>>1001.
>>Does anyone have any ideas as to what could be the problem?
>>
>>Thanks in advance..
>>
> Access could have corrupted metadata stored in the extended attributes of
> the
> stored procedure. Try copying the SQL from the stored procedure, deleting
> the
> procedure, and creating it again. Paste the SQL back in to recover the
> old
> procedure definition (whic will not include the old extended attributes).|||Thanks for your help, I have tried this in the past when things have gone
astray. However on trying this Access crashes when I try to save the new
stored procedure.

"Steve Jorgensen" <nospam@.nospam.nospam> wrote in message
news:sgdlm05d9otcv31ms4lbdenfvnsdvog2m4@.4ax.com...
> On Mon, 11 Oct 2004 17:17:54 +0100, "8leggeddj" <me@.home.com> wrote:
>>Hello,
>>
>>I am having a problem when using access xp as a frontend for sql server
>>2000.
>>I have been trying to update a number of stored procedures (Just simple
>>adding fields etc) which results in access crashing with event ID 1000 and
>>1001.
>>Does anyone have any ideas as to what could be the problem?
>>
>>Thanks in advance..
>>
> Access could have corrupted metadata stored in the extended attributes of
> the
> stored procedure. Try copying the SQL from the stored procedure, deleting
> the
> procedure, and creating it again. Paste the SQL back in to recover the
> old
> procedure definition (whic will not include the old extended attributes).sql

Access Control List (structure) invalid - updating Virtual directory information

Hi,

I've just installed SQL server and then IIS and SQLCE tools.
I created a virtual directory and was trying to update the NTFS
permissions from SQLServer Connectivity management when i got the
following error - Access Control List (structure) invalid.

Has anyone come across this and if so, what did u do to fix it.

Thanks
LynThis seems to be an OS error, rather than something specific to MSSQL,
so you might get a better response in a Windows
admin/security/filesystem newsgroup. The error message is documented,
but the explanation is very minimal:

http://msdn.microsoft.com/library/d...w2kmsgs/393.asp

The replies to a previous (old) post about this error suggested using
CHKDSK to look for any NTFS corruption issues, so that might be worth a
go:

http://groups.google.ch/group/micro...43a9a59d?hl=en&

Simon|||Thanks

I'll give it a go

Simon Hayes wrote:
> This seems to be an OS error, rather than something specific to MSSQL,
> so you might get a better response in a Windows
> admin/security/filesystem newsgroup. The error message is documented,
> but the explanation is very minimal:
> http://msdn.microsoft.com/library/d...w2kmsgs/393.asp
> The replies to a previous (old) post about this error suggested using
> CHKDSK to look for any NTFS corruption issues, so that might be worth a
> go:
> http://groups.google.ch/group/micro...43a9a59d?hl=en&
> Simonsql

Access another database from sp

I've looked around on how to do this and haven't had any luck so far. I have a stored procedure that needs to update tables in the current database (db1) and in another database (db2) on the same server. The user executing the stored procedure on db1 does not have permissions to access db2, but does know the username, password, and connection string of db2. Is there any way to create a connection to db2 in the stored procedure using the credentials I have? I've seen sp_addlinkedserver, but I don't think I need to use that because that database is on the same server? I just can't figure out how to 'switch' users to update the other database from the one stored procedure.

If I haven't made myself clear just let me know and I'll do my best to give a better explanation. Any help would be greatly appreciated.

Option 1:

Create a credential which has access on both db1 & db2 database. It will tune your query performance, bcs it is a direct access & you need not to stick with Ad-Hoc Remote quires.

Option 2:

Using Ad-Hoc remote quires using OPENROWSET or OPENDATASOURCE.

|||

As Mani indicates, the user will, in some way, require permission to access db2. However, it may not be necessary to provide the user direct access permissions. You may wish to explore the new 'EXECUTE AS' functionality (SQL 2005).

Refer to Books Online, Topic: "EXECUTE AS'

|||

Arnie,

I am not sure EXECUTE AS will help here.

As per my understanding, EXECUTE AS will affect the entire scope, if we need to change the context then we have to use the Execute AS & revert.

I created 2 users,

user1 only have access on db1

user2 only have access on db2

Without direct access permission on both db, user1 can't able to access the db2 rite? (even with execute as). If it allows then i can create any sp with Execute As target db user name, and i can access all the data.

Am i missing something here?

|||

Mani,

As in the Example , in Books Online, Topic: 'EXECUTE AS (Transact-SQL)', inside the stored procedure, the security context can be changed, the UPDATE statement can be executed in db2, and then the security context can revert -leaving the entirety of the context switching 'hidden' from the application users' view.

And, if necessary, the sproc can be encrypted locally to obfuscate the context switching from developers.

Access 2003 Linked Tables update

Hello,

I am trying to update some SQL-linked tables in my Access database by repoiting the existing linked tables to a new datasource. The problem is, when I go to select the machine data source where the table sits, I get an error message saying the MS Jet Database can't find the object. This is because when Access creates the linked table, it replaces the period in the <schema>.<table_name> with an underscore. So when I go to update the links, it is essentially looking for the new table with the wrong file name.

I have about 80 linked tables to update and I haven't been able to figure a work-around. HELP PLEASE!

Cheers,

Josh

According to http://www.microsoft.com/technet/archive/office/office97/reskit/office97/027.mspx

Renaming Linked Tables

When Access links a remote table, it prefixes the default table owner ID of the SQL Server to each table name. The period separator between the owner ID and the table name is replaced by an underscore because periods in table names are not permitted by Access. Thus, the names of linked tables no longer correspond to the original table names in your MDB file. The simplest way to correct this is to rename your tables to their original names after linking.

Access 2003 and SQL Server Express

Does anybody know if there is and update for access 2003 which could make it compatible with SQLServer Express. Actually XML Field are not supported, Diagram too, and surely much more.

Any help would be appreciate.

No, the special features are actually not supported. Only the SQL Server 2k compatible features are available.

HTH, jens Suessmeyer.

http://www.sqlserver2005.de

Accelerating an update statement

I was wondering if anyone sees a way to make this faster, its not super slow(10-300 seconds) right now but it has to be run up to 40 times in a row with different variables which combined can take up to 60 minutes.

With CorrectTab(SumCol,Loc1,Loc2) as
(
Select Sum(coltobesum1),Loc1,Loc2
from [Dat1].dbo.Table1 where (CCol=@.cCol) and Quarter=@.Quarter and Year=@.Year and area='D'
Group By Loc1,Loc2
),
CombinedCorrectTab(SumCol2,SumCol,SumColPer,Loc1,Loc2) as
(
Select Sum(coltobesum2)*10 as SumCol2,Min(SumCol)
,((Min(SumCol)-Sum(coltobesum2)*10)/(Sum(coltobesum2)*10))+1.0
,Table2.Loc1,Table2.Loc2
from Table2,CorrectTab
where (OpCCol=@.CCol)
and Table2.Loc1=Table1.Loc1
and Table2.Loc2=Table1.Loc2 and newcoltobesum is null
Group by Table2.Loc1,Table2.Loc2
)
Update Table2
Set NewColtobesum=coltobesum2*SumColPer
from CombinedCorrectTab
where (OpCCol=@.CCol) and Table2.Loc1=CombinedCorrectTab.Loc1 and Table2.Loc2=CombinedCorrectTab.Loc2

How many rows are being updated? If it is one row at a time, you might build a table of @.cCol values to join to rather than doing it multiple times... This is especially true if each of these updates does a table scan.

Can you post the plan? Use:

set showplan_text on
go

<exec query>
go

set showplan_text off
go

|||The number of rows updated per run varies from 90 to about 1.5 million. I will post an execution plan shortly.|||is there a way to simplify the two CTe's using join into one then call the update|||

You can also use batch updates: place your update statement in a loop and set rowcount 1000 or 10000 (depending on what's faster)..

P.S. Nice nick

|||

You may also consider using a temporary table to hold the calculation values that does not change between various parameter invocations of the UPDATE statement. With the CTE approach, you are essentially doing the work every time you perform the UPDATE statement. So using temporary table will considerably reduce the time taken to run the UPDATE statement. Apart from this, there are other factors that affect the UPDATE statement performance:

1. Indexes on the table (one being updated). You might consider dropping unnecessary indexes

2. Does the table have triggers? What about the logic in the triggers?

3. If you are running the same UPDATE statement multiple times then you can also consider storing the different parameter values in a temporary table and joining against that as suggested by Louis. This will also help

4. Lastly, if you are updating large number of rows then consider running the UPDATE in batches using the TOP clause

Accelerating an update statement

I was wondering if anyone sees a way to make this faster, its not super slow(10-300 seconds) right now but it has to be run up to 40 times in a row with different variables which combined can take up to 60 minutes.

With CorrectTab(SumCol,Loc1,Loc2) as
(
Select Sum(coltobesum1),Loc1,Loc2
from [Dat1].dbo.Table1 where (CCol=@.cCol) and Quarter=@.Quarter and Year=@.Year and area='D'
Group By Loc1,Loc2
),
CombinedCorrectTab(SumCol2,SumCol,SumColPer,Loc1,Loc2) as
(
Select Sum(coltobesum2)*10 as SumCol2,Min(SumCol)
,((Min(SumCol)-Sum(coltobesum2)*10)/(Sum(coltobesum2)*10))+1.0
,Table2.Loc1,Table2.Loc2
from Table2,CorrectTab
where (OpCCol=@.CCol)
and Table2.Loc1=Table1.Loc1
and Table2.Loc2=Table1.Loc2 and newcoltobesum is null
Group by Table2.Loc1,Table2.Loc2
)
Update Table2
Set NewColtobesum=coltobesum2*SumColPer
from CombinedCorrectTab
where (OpCCol=@.CCol) and Table2.Loc1=CombinedCorrectTab.Loc1 and Table2.Loc2=CombinedCorrectTab.Loc2

How many rows are being updated? If it is one row at a time, you might build a table of @.cCol values to join to rather than doing it multiple times... This is especially true if each of these updates does a table scan.

Can you post the plan? Use:

set showplan_text on
go

<exec query>
go

set showplan_text off
go

|||The number of rows updated per run varies from 90 to about 1.5 million. I will post an execution plan shortly.|||is there a way to simplify the two CTe's using join into one then call the update|||

You can also use batch updates: place your update statement in a loop and set rowcount 1000 or 10000 (depending on what's faster)..

P.S. Nice nick

|||

You may also consider using a temporary table to hold the calculation values that does not change between various parameter invocations of the UPDATE statement. With the CTE approach, you are essentially doing the work every time you perform the UPDATE statement. So using temporary table will considerably reduce the time taken to run the UPDATE statement. Apart from this, there are other factors that affect the UPDATE statement performance:

1. Indexes on the table (one being updated). You might consider dropping unnecessary indexes

2. Does the table have triggers? What about the logic in the triggers?

3. If you are running the same UPDATE statement multiple times then you can also consider storing the different parameter values in a temporary table and joining against that as suggested by Louis. This will also help

4. Lastly, if you are updating large number of rows then consider running the UPDATE in batches using the TOP clause

abt RTRIM/LTRIM ,Null values

Hi
can any one solve my problem; i could not get solution for following
query
I have two tables like tblOrg and tblorgchartreport
so i need to update tblorg for newly inserted rows with
tblorgchartreport, suppose there are firstname, lastname
so i need to update these fields in tblorg by triming two fields like
i need to get firstname + lastname from tblorgchartreport into
firstname of tblorg. so i have few null values in lastname fields .
so can any one tell me how to solve avoid null values while using RTRIM
or LTRIM when inserting values
waiting for reply thank you in advance
*** Sent via Developersdex http://www.codecomments.com ***
Hi
You can use this way:
SELECT LTRIM(RTRIM(ISNULL(FirstName,''))) + LTRIM(RTRIM(ISNULL(LastName,'')))
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
"krishna nellutla" wrote:

> Hi
> can any one solve my problem; i could not get solution for following
> query
> I have two tables like tblOrg and tblorgchartreport
> so i need to update tblorg for newly inserted rows with
> tblorgchartreport, suppose there are firstname, lastname
> so i need to update these fields in tblorg by triming two fields like
> i need to get firstname + lastname from tblorgchartreport into
> firstname of tblorg. so i have few null values in lastname fields .
> so can any one tell me how to solve avoid null values while using RTRIM
> or LTRIM when inserting values
> waiting for reply thank you in advance
>
> *** Sent via Developersdex http://www.codecomments.com ***
>
|||Does this help?
use tempdb
go
create table names (f varchar(30), l varchar(30))
go
insert names (f,l) values ('krishna','nellutla')
insert names (f,l) values ('mark','allison')
insert names (f,l) values ('ben',null)
set concat_null_yields_null off
select rtrim(l) + ', ' + ltrim(f) from names
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
krishna nellutla wrote:
> Hi
> can any one solve my problem; i could not get solution for following
> query
> I have two tables like tblOrg and tblorgchartreport
> so i need to update tblorg for newly inserted rows with
> tblorgchartreport, suppose there are firstname, lastname
> so i need to update these fields in tblorg by triming two fields like
> i need to get firstname + lastname from tblorgchartreport into
> firstname of tblorg. so i have few null values in lastname fields .
> so can any one tell me how to solve avoid null values while using RTRIM
> or LTRIM when inserting values
> waiting for reply thank you in advance
>
> *** Sent via Developersdex http://www.codecomments.com ***

abt RTRIM/LTRIM ,Null values

Hi
can any one solve my problem; i could not get solution for following
query
I have two tables like tblOrg and tblorgchartreport
so i need to update tblorg for newly inserted rows with
tblorgchartreport, suppose there are firstname, lastname
so i need to update these fields in tblorg by triming two fields like
i need to get firstname + lastname from tblorgchartreport into
firstname of tblorg. so i have few null values in lastname fields .
so can any one tell me how to solve avoid null values while using RTRIM
or LTRIM when inserting values
waiting for reply thank you in advance
*** Sent via Developersdex http://www.developersdex.com ***Hi
You can use this way:
SELECT LTRIM(RTRIM(ISNULL(FirstName,''))) + LTRIM(RTRIM(ISNULL(LastName,'')))
--
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"krishna nellutla" wrote:
> Hi
> can any one solve my problem; i could not get solution for following
> query
> I have two tables like tblOrg and tblorgchartreport
> so i need to update tblorg for newly inserted rows with
> tblorgchartreport, suppose there are firstname, lastname
> so i need to update these fields in tblorg by triming two fields like
> i need to get firstname + lastname from tblorgchartreport into
> firstname of tblorg. so i have few null values in lastname fields .
> so can any one tell me how to solve avoid null values while using RTRIM
> or LTRIM when inserting values
> waiting for reply thank you in advance
>
> *** Sent via Developersdex http://www.developersdex.com ***
>|||Does this help?
use tempdb
go
create table names (f varchar(30), l varchar(30))
go
insert names (f,l) values ('krishna','nellutla')
insert names (f,l) values ('mark','allison')
insert names (f,l) values ('ben',null)
set concat_null_yields_null off
select rtrim(l) + ', ' + ltrim(f) from names
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
krishna nellutla wrote:
> Hi
> can any one solve my problem; i could not get solution for following
> query
> I have two tables like tblOrg and tblorgchartreport
> so i need to update tblorg for newly inserted rows with
> tblorgchartreport, suppose there are firstname, lastname
> so i need to update these fields in tblorg by triming two fields like
> i need to get firstname + lastname from tblorgchartreport into
> firstname of tblorg. so i have few null values in lastname fields .
> so can any one tell me how to solve avoid null values while using RTRIM
> or LTRIM when inserting values
> waiting for reply thank you in advance
>
> *** Sent via Developersdex http://www.developersdex.com ***

abt RTRIM/LTRIM ,Null values

Hi
can any one solve my problem; i could not get solution for following
query
I have two tables like tblOrg and tblorgchartreport
so i need to update tblorg for newly inserted rows with
tblorgchartreport, suppose there are firstname, lastname
so i need to update these fields in tblorg by triming two fields like
i need to get firstname + lastname from tblorgchartreport into
firstname of tblorg. so i have few null values in lastname fields .
so can any one tell me how to solve avoid null values while using RTRIM
or LTRIM when inserting values
waiting for reply thank you in advance
*** Sent via Developersdex http://www.codecomments.com ***Hi
You can use this way:
SELECT LTRIM(RTRIM(ISNULL(FirstName,''))) + LTRIM(RTRIM(ISNULL(LastName,''))
)
best Regards,
Chandra
http://chanduas.blogspot.com/
http://groups.msn.com/SQLResource/
---
"krishna nellutla" wrote:

> Hi
> can any one solve my problem; i could not get solution for following
> query
> I have two tables like tblOrg and tblorgchartreport
> so i need to update tblorg for newly inserted rows with
> tblorgchartreport, suppose there are firstname, lastname
> so i need to update these fields in tblorg by triming two fields like
> i need to get firstname + lastname from tblorgchartreport into
> firstname of tblorg. so i have few null values in lastname fields .
> so can any one tell me how to solve avoid null values while using RTRIM
> or LTRIM when inserting values
> waiting for reply thank you in advance
>
> *** Sent via Developersdex http://www.codecomments.com ***
>|||Does this help?
use tempdb
go
create table names (f varchar(30), l varchar(30))
go
insert names (f,l) values ('krishna','nellutla')
insert names (f,l) values ('mark','allison')
insert names (f,l) values ('ben',null)
set concat_null_yields_null off
select rtrim(l) + ', ' + ltrim(f) from names
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602m.html
krishna nellutla wrote:
> Hi
> can any one solve my problem; i could not get solution for following
> query
> I have two tables like tblOrg and tblorgchartreport
> so i need to update tblorg for newly inserted rows with
> tblorgchartreport, suppose there are firstname, lastname
> so i need to update these fields in tblorg by triming two fields like
> i need to get firstname + lastname from tblorgchartreport into
> firstname of tblorg. so i have few null values in lastname fields .
> so can any one tell me how to solve avoid null values while using RTRIM
> or LTRIM when inserting values
> waiting for reply thank you in advance
>
> *** Sent via Developersdex http://www.codecomments.com ***

about SqlTransaction

updatestring = "Update [Flow] set received=1 where flowid=@.flowid"
insertstring = "Insert into [Flow] (barcode, copyid, floworder, recvdate, recvuser, recvdepartment) "
insertstring += "values (@.barcode, @.copyid, @.floworder, @.recvdate, @.recvuser, @.recvdepartment)"

is it can't use sqltransaction in the same table ?

when i not apply the sqltransaction, then the about two queries can work success, but when i apply the sqltransaction, then have an error in following.

error :
Object reference not set to an instance of an object

Source Error:

Line 278: message.Text = "Recebido o documento " & barcodetx.Text & "!!"
Line 279: Catch ex As Exception
<red>Line 280: objtransaction.Rollback()</red>
Line 281: message.Text = "Não se conseguiu registar!!"
Line 282: FinallyHi

You haven't posted any of the real code where stuff is happening.
You are probably best of wrapping the whole thing inside a stored proc and calling that.

about SQL update

Hi i have a problem recently about updating. Let's say i have table

TableName

Name TestBoolean

Ian 1

Shane 1

Ianne 0

Jong 0

How can i update the testboolean where the 1 must be 0 and 0 must be 1... in one statement only

Regards

update tablename set Testboolean = case when Testboolean = 0 then 1 else 0 end

Good luck

about SQL String (security question)

I have a SQL Query String like below..

string SQLUpd = "UPDATE Member SET Member_pwd = '" + pwd + "',Member_nickname = '" + NickName + "',Member_mail = '" + Mail + "',Member_birthday = '" + BDay + "', Member_gende_no = " + Gender + ",Member_mobile = '" + Mobile + "' ,Member_tel_day = '" + DTel + "',Member_tel_night = '" + NTel + "',Member_occupy_no = " + occupy + ",Member_national_no = " + National + ",Member_area_no = " + Area + ",Member_address = '" + Address + "' WHERE (Member_no = " + MemberNo + " )";

in my program it does work perfectly ... but now I just recalled my teacher have told.. it may cause security problem in that way...

maybe I need to change 【UPDATE Member SET Member_pwd = '" + pwd +】 to【UPDATE Member SET Member_pwd = '" + @.pwd +】

is there any difference between pwd and @.pwd?

thank you very much

I think what your teacher suggested is to use Parameterized Queries. There are a few benefits to it - primarily - security, maintenance/ease of coding. Please google for more info.I can type up a couple of lines but you will learn more from the articles already published.|||

Using this dynamic query building with strings is bad. Users can potentially inject malicious code into your queries. I have included a helpful link for you to review:http://www.4guysfromrolla.com/webtech/092601-1.shtml

Good Luck!

|||

thanks for you all (very much)

do you have a example for C#... shame on me I'm not similiar with VB...

I just trying to fix my original code . can you give me some suggestion?

except I have to change pwd to @.pwd.. is something else I've to add or edit?

thank you very much

================start of original code ============================

string strUpd = "data Source=x.x.x.x;user=sa;password=1234 ;initial catalog=English";
string SQLUpd = "UPDATE Member SET Member_pwd = '" + pwd + "',Member_nickname = '" + NickName + "',Member_mail = '" + Mail + "',Member_birthday = '" + BDay + "', Member_gende_no = " + Gender + ",Member_mobile = '" + Mobile + "' ,Member_tel_day = '" + DTel + "',Member_tel_night = '" + NTel + "',Member_occupy_no = " + occupy + ",Member_national_no = " + National + ",Member_area_no = " + Area + ",Member_address = '" + Address + "' WHERE (Member_no = " + MemberNo + " )";
SqlConnection connUPD = new SqlConnection(strUpd);
SqlCommand cmdUpd = new SqlCommand(SQLUpd, connUPD);
cmdUpd.CommandType = CommandType.Text;

connUPD.Open();
cmdUpd.ExecuteNonQuery();

connUPD.Dispose();
connUPD.Close();

================end of original code ============================

================fixed code ===================================

string strUpd = "data Source=x.x.x.x;user=sa;password=1234 ;initial catalog=English";
string SQLUpd = "UPDATE Member SET Member_pwd = '" + @.pwd + "',Member_nickname = '" + NickName + "',Member_mail = '" + Mail + "',Member_birthday = '" + BDay + "', Member_gende_no = " + Gender + ",Member_mobile = '" + Mobile + "' ,Member_tel_day = '" + DTel + "',Member_tel_night = '" + NTel + "',Member_occupy_no = " + occupy + ",Member_national_no = " + National + ",Member_area_no = " + Area + ",Member_address = '" + Address + "' WHERE (Member_no = " + MemberNo + " )";
SqlConnection connUPD = new SqlConnection(strUpd);
SqlCommand cmdUpd = new SqlCommand(SQLUpd, connUPD);
cmdUpd.CommandType = CommandType.Text;

connUPD.Open();
cmdUpd.ExecuteNonQuery();

connUPD.Dispose();
connUPD.Close();

|||The post is marked as answered. So I am assuming you got your solution?|||

no no no... I'm sorry

should I post a new topic for extra question?

I still have many wonder on this filed...

thank you

|||

Hi,

You will need to add the parameters for all the criterias that is used in the query. Not only the pwd.

A typical sample might look like

SqlCommand cmd = new SqlCommand("Select * from Table WHERE [name]=@.name", this.connection);
cmd.Parameters.Add("@.name", SqlDbType.NVarChar, 20);
cmd.Parameters.Value = "Kevin";
SqlDataReader sdr = cmd.ExecuteReader();

About Security between views and tables

Hi,

I've got a table T1 and a view V1 based on T1's rows (but not all rows).

I've granted users to do update and select on my View V1.
But i don't want users directly update my table T1.

then, i log as a standard user in Enterprise mgr, and open my view V1. I modify a value in a field, and when i validate, an error occurs saying i'm not allowed to make Select neither Update on table T1.

Is there a way of doing this ? (sql 2000).

You can't update a view. A view isn't a table. It's just a select statement.|||

Yes i can. I did it many times, as the select is not too complex, you can do it.

For insance, if you got a table T1 with fields (ID, f1,f2,f3,f4) and a view V1 as select id, f1,f2 from t1, you 'll be allowed to make updates.

this feature works fine with many views i wrote.

|||Well, I'll be...

Have you read through this document to see if you have met the criteria for updateable views? http://msdn2.microsoft.com/en-gb/library/ms187956.aspx|||

thanks for the link. yes my view is updateable, and when there's no grant restrictions, it works fine.

My problem is the following : I'm working on an ms access migration to sql 2000.
Then, i use my view as an attached table in access, an users can modify some values.
But i don't wan basic users have a direct access to the table, only to the view.
That's why i tried to restrict select and update on the table, whereas select and updates are allowed to the view.

But i'm afraid i won't be able to do this...

|||If you have the base table restricted, I'm wondering if the view can supercede that security. I'm thinking not.|||

i'm afraid you're right. so i'll have to manage my own security... :-(