Question With SQLDATASOURCE and User.identity.name

is there a way that I can use the sqldatasource with a form view where my sqldatasource select statement is like this

select * from tblUsers wherevcUserName=@.vcUserName

<selectparameters>

<

asp:ParameterName="user.identity.name"Type="String/>

</selectParameters>

Hi,

The way you are trying will not work.

Here is a link from Peter with a solution to handle this case by using ExpressionBuilder:

http://peterkellner.net/2006/09/18/expressionbuilderidentity/

Another way, I think you can try to assign the user to the selectParameter from your code under SqlDataSource's selecting event programatically. Also, you can assign the login user to a session varable and access to it through SessionParameter

Question Regarding Stored Procedure??OUTPUTS

I have a stored procedure that I just need to return the output to my program.It is a Select All type statement.I will post my vb code that works when I use both inputs and outputs but not for all output procedure...I dont get it.
Here is the Stored Procedure....

CREATE procedure dbo.IDXAppt_Settings_NET
(
@.SQLADD nvarchar(15)Output,
@.SQLDatabase nvarchar(20)Output,
@.SQLLogin nvarchar(20)Output,
@.SQLPass nvarchar(20)Output
)
as
select
@.SQLADD=SQLAddress,
@.SQLDatabase=SQLDatabase,
@.SQLLogin=SQLLogin,
@.SQLPass=SQLPassword

from
Clinic_Settings

GO

Here is the Vb.Net Code......
To retrieve the elements that does not give me an error just gives me no data...

Private Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim consql As New SqlConnection("server=myserver,database=APPOINTMENTS;uid=webtest;pwd=webtest")
Dim cmdsql As New SqlCommand

Dim parmSQLAddress As SqlParameter
Dim parmDatabase As SqlParameter
Dim parmLogin As SqlParameter
Dim parmSqlPass As SqlParameter

Dim strtest As String
Dim db As String
Dim login As String
Dim pass As String

cmdsql = New SqlCommand("Appt_Settings_NET", consql)
cmdsql.CommandType = CommandType.StoredProcedure
parmDatabase = cmdsql.Parameters.Add("@.SQLData", SqlDbType.NVarChar)
parmDatabase.Size = 20
parmDatabase.Direction = ParameterDirection.Output
db = cmdsql.Parameters("@.SQLData").Value

parmLogin = cmdsql.Parameters.Add("@.SQLLogin", SqlDbType.NVarChar)
parmLogin.Size = 20
parmLogin.Direction = ParameterDirection.Output
login = cmdsql.Parameters("@.SQLLogin").Value

parmSqlPass = cmdsql.Parameters.Add("@.SQLPass", SqlDbType.NVarChar)
parmSqlPass.Size = 20
parmSqlPass.Direction = ParameterDirection.Output
pass = cmdsql.Parameters("@.SQLPass").Value

parmSQLAddress = cmdsql.Parameters.Add("@.SQLADD", SqlDbType.NVarChar)
parmSQLAddress.Size = 15
parmSQLAddress.Direction = ParameterDirection.Output
strtest = cmdsql.Parameters("@.SQLADD").Value
consql.Open()
cmdsql.ExecuteNonQuery()

Label1.Text = strtest
End SubYou may get the output value after the stored procedure executed.|||Doesn't look like you want to use OUTPUTs here. You're query will get every row back from the table, so only the last (or is it the first - last I think) row will go into your output params.

Question on parameter.Add for an SQL Insert

I'm trying to update my database table from my dataset.

In the following statement, the ? represents the "size as integer" of the SqlDbType of DateTime and I can't fiqure out what to put there.

command.Parameters.Add("@.ItemDate", SqlDbType.DateTime, ?,"ItemDate")

In the following statement I understand the "50" since it is the size of the field, but I don't understand what to do with the DateTime.

command.Parameters.Add("@.ItemText", SqlDbType.NVarChar, 50, "ItemText")

Thank you for your help

This chart might help:

http://www.carlprothman.net/Technology/DataTypeMapping/tabid/97/Default.aspx

Buck Woody

Question on parameter.Add for an SQL Insert

I'm trying to update my database table from my dataset.

In the following statement, the ? represents the "size as integer" of the SqlDbType of DateTime and I can't fiqure out what to put there.

command.Parameters.Add("@.ItemDate", SqlDbType.DateTime, ?,"ItemDate")

In the following statement I understand the "50" since it is the size of the field, but I don't understand what to do with the DateTime.

command.Parameters.Add("@.ItemText", SqlDbType.NVarChar, 50, "ItemText")

Thank you for your help

This chart might help:

http://www.carlprothman.net/Technology/DataTypeMapping/tabid/97/Default.aspx

Buck Woody

Question on Inserting to a different server

What would be the into statement when I am trying to do an insert into another database from SQL
basically want to do something like this but its not working quite right
insert into DatabaseName.TableName
Any help would be greatly appreciated...Thanksinsert server.db.dbo.table values(...)

server - linked server. See BOL fro details.|||You can use SELECT...INTO to create an identical table definition (different table name) with no data by having a FALSE condition in the WHERE clause.

To transfer data between the databases you can take help of DTS which is easy and manageble.

Question on case

I am trying to run a query with a case statement in it. The column has some null values and I want to replace the null with a character string.
ex:
case my_column when null then 'char_string' else my_column end 'column'

I run it and the nulls are still in my_column. Does case not work on null values this way?
Thanks.Originally posted by exdter
I am trying to run a query with a case statement in it. The column has some null values and I want to replace the null with a character string.
ex:
case my_column when null then 'char_string' else my_column end 'column'

I run it and the nulls are still in my_column. Does case not work on null values this way?
Thanks.

Try this:

case when my_column is null then 'char_string' else my_column end 'column'|||Can I use an isnull here? Doesn't matter. Your solution worked. Thanks alot.|||Use isull(mycolumn,'CharString')|||Thats what I thought too but I used Snails method already.
Thanks.|||I tried it this way too and it works fine and is much neater.
Thanks.|||coalesce(mycolumn,'charstring') is also much neater and it uses standard sql

;)|||Thanks r937!