how can I add InsertCommand in SqlDataSource?

I need to add an 'InsertCommand' to my query via sqldatasource, but i cannot see this option, i only have the 'order', 'where' and 'advanced' option, could you please advice?

Hi,

VB is great :) you can add and you insertcommand in code behind for example:

Private Sub Page_Load(ByVal sender As Object, ByVal e As eventArgs) Handles Page.Load

SqlDataSource1.InsertCommand.Add("Cell_Name", System.CodeType.String, TextBox.Text)

End Sub

Thats it

Hope it helps

------------------------------

Please do not forget to mark as "Answered" the answer which helped you. Thanks

|||

It seems that you want to add an InsertCommand to your SQLDatasource dynamically. Below is the code you can use to create an insert command for an existing sql datasource.

<your sql datasource id>.InsertCommand = "<either your insert query or stored procedure name>"
<your sql datasource id>.InsertCommandType = SqlDataSourceCommandType.StoredProcedure or SqlDataSourceCommandType.Text

You can add the command parameters using <your sql datasource id>.InsertParameters.Add method.

To call the insert method you can to use <your sql datasource id>.Insert().

Hope this will help.

|||

i have tried using this code but the 'SqlDataSource1' and 'System.CodeType' are not recognised; says its not declared.

could you please advice?

|||

Hi,

did you added SqlDataSource control in your aspx page ?

Regards

|||

Yes, and i used the same ID name.

How can a user change password in SQL2005 via TSQL?

I have an application that controls user logins, passwords, etc. at the front end for a SQL database. I am in the stage of migrating to SQL2005 and cannot get the TSQL code to allow a user to change their own password. Here's the background;

The ADMIN of the app is a Sysadmin on the SQL server and can create logins, set roles, etc. Assume the Admin creates a user TOM with a password of xxxx. This works fine using the create login statement from a Connect.Execute statement from my app like so;

"Create Login 'TOM' With Password 'xxxx', Default_Database = 'myDB', Check_Policy = OFF"

TOM will be setup with db roles as well

When TOM logins into my app, he will have to change his password at some point. The TSQL code I am using (which fails) is executed by TOM who has a connection to the SQL db because he is logged into the app.

"Alter Login TOM With Password = 'xxxx' Old_Password = 'xxxxx', Check_Policy = OFF"

At this point I get an error:

RunTime error -2147217900 (80040e14)

ODBC SQL Server Driver][SQL Server] Cannot alter

the login 'TOM', becuase it does not exist or you do

not have permission.

Obviously it exists since TOM is currently logged into the SQL Server. So if it's permissions related, what permissions does a user need to change his/her password? Or is there another way to do it?

Thanks in advance for your help.

CH

A user does not need any permissions to change his password, but he cannot change his password policy setting - that option is only settable by someone that has ALTER ANY LOGIN permission. See http://msdn2.microsoft.com/en-us/library/ms189828.aspx.

Thanks

Laurentiu

|||

If I have the users change password statement read; it works.

"Alter Login TOM With Password = 'xxxx' Old_Password = 'xxxxx' "

However, I am unable to test this on a Win2003 Server right now, so I wonder if the Check Policy will be enforced for user TOM.

Under no circumstances do I want to have Policy Checked since the front end of my app provides the password security.

Any ideas if this is possible?

CH

|||If you are using ADO.NET 2.0 you can also use the

SqlConnection.ChangePassword(ConnectionString, "MyNewSecretpassword");

For changing the password.

Jens K. Suessmeyer.

http://www.sqlserver2005.de
|||

Thanks for the reply.

The problem we have run into is the Windows security, which is set across the domain, maybe set differently than our app. So if the user of our app chooses a different policy that that of the domain (which is fine), the user cannot alter his login since he does not have Alter Any login rights. And we wouldn't want to give him any.

Do you see a way around this?

All I can think of now is to change the password via the Admin login through a separate connection to the database. Something like this;

Dim gConnTmp As New ADODB.Connection
Dim sConnect As String

With gConnTmp
sConnect = "DSN=" & SQLDataSource & ";"
sConnect = sConnect & "UID= 'ADMIN' ;"
sConnect = sConnect & "PWD=" & gsPassword & ";"
.Open sConnect
End With

'change the password here... ALTER LOGIN etc.

'kill connection

gConnTmp.Close
Set gConnTmp = Nothing

Do you see any issues with doing this? I think it looks ok.

CH

|||

If you want to enforce a custom password policy, then you should just create the login with CHECK_POLICY set to off; otherwise, the Windows password policy will be enforced for a password change (on Windows 2003).

Thanks

Laurentiu

how 2 insert the value from a SP into a tmp table

can any one advice me on how to insert the results of a SP into a temp
table
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!INSERT INTO #tmp (col1, col2, ...)
EXEC procname
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Emil Henrico" <emil@.interres.co.za> wrote in message
news:u%23myVz9HFHA.3332@.TK2MSFTNGP14.phx.gbl...
>
> can any one advice me on how to insert the results of a SP into a temp
> table
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!|||Something like
[script]
create table #MyTable(column1,column2,...,columnX)
go
insert into #MyTable (column1,column2,...,columnX) exec MyProcedure
[/script]
Cristian Lefter, SQL Server MVP
"Emil Henrico" <emil@.interres.co.za> wrote in message
news:u%23myVz9HFHA.3332@.TK2MSFTNGP14.phx.gbl...
>
> can any one advice me on how to insert the results of a SP into a temp
> table
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!|||Emil
INSERT INTO #Temp EXEC sp
"Emil Henrico" <emil@.interres.co.za> wrote in message
news:u%23myVz9HFHA.3332@.TK2MSFTNGP14.phx.gbl...
>
> can any one advice me on how to insert the results of a SP into a temp
> table
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!|||
evry ting
execp the SP returns 10 vals and i only need to use 2 of them...
how do i do that
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Delete the others from the table after the INSERT. Or, a nasty workaround, i
s to call back to the
SQL Server as a linked server using either OPENQUERY or OPENROWSET and do SE
LECT TOP 2 from that
table valued function.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Emil Henrico" <emil@.interres.co.za> wrote in message
news:u46$uU%23HFHA.1476@.TK2MSFTNGP09.phx.gbl...
>
> evry ting
> execp the SP returns 10 vals and i only need to use 2 of them...
> how do i do that
>
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!|||let my try and explain better...itonly returns one row..with 10
Columns..i onle need 2 of those ..not all 10...
thisis my question ...
create table #test
( mktcode int, rttotal float,
)
insert into #test (mktcode, rttotal)
exec sp...but the reslut gives me culmun a,b,c,d,e,f,g,
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!|||Well, with INSERT EXEC you get all. How about modifying the stored procedure
, or extracting the
relevant part of the procedure to make another suitable procedure. Or re-wri
te the procedure into a
table valued user defined function?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Emil Henrico" <emil@.interres.co.za> wrote in message
news:exVfFo%23HFHA.4076@.TK2MSFTNGP10.phx.gbl...
> let my try and explain better...itonly returns one row..with 10
> Columns..i onle need 2 of those ..not all 10...
> thisis my question ...
> create table #test
> ( mktcode int, rttotal float,
> )
> insert into #test (mktcode, rttotal)
> exec sp...but the reslut gives me culmun a,b,c,d,e,f,g,
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!|||Create a temporary Table Variable, say @.Tmp,
Declare @.Tmp Table (
Col1 Varchar(20),
Col2 Varchar(20),
Col3 Varchar(20),
..
Col10 Varchar(20))
Only make the column definitions match the output of the stored proc.
Then Insert @.Tmp Exec SP -- This inserts all ten values into @.Tmp
Then Insert from @.tmp into your real table.
Insert #test (mktcode, rttotal)
Select Col3, Col 7 From @.tmp -- WHichever 2 columns you want
"Emil Henrico" wrote:

> let my try and explain better...itonly returns one row..with 10
> Columns..i onle need 2 of those ..not all 10...
> thisis my question ...
> create table #test
> ( mktcode int, rttotal float,
> )
> insert into #test (mktcode, rttotal)
> exec sp...but the reslut gives me culmun a,b,c,d,e,f,g,
> *** Sent via Developersdex http://www.examnotes.net ***
> Don't just participate in USENET...get rewarded for it!
>