DataSource Controls :: Deprecated Types Not Supported As Output Parameters
Jan 21, 2010
i am trying to do: Parameters.Add("@ProfileHtml", SqlDbType.Text, MAX_TEXT).Direction = ParameterDirection.Output but i keep getting this error. MAX_TEXT is integer = 2147483647 Data type 0x23 is a deprecated large object, or LOB, but is marked as output parameter. Deprecated types are not supported as output parameters. Use current large object types instead.
I read on MSDN that Ntext and Image will be removed from future version of SqlServer, but as I check my db the aspnet_Profiles uses ntext and image MSDN says replace them with nvarchar and varbinary
1. I tried to replace these column and got an error 2. When should I replace these datatypes? 3. Will Microsoft put out an update for this particular setup?
I have a stored procedure that works fine for classic asp, but how can I get my output parameters data back from in /MVC 2 / linq/ entities framework model . I have inported the function also, but when i try to code it i dont get results ...i know this is a matter of lack of / knowledge or exmple because i have been scouring the web for it and cannnot find into that accually works.
here is the stored proc.
[Code]....
Here was the one of the few tries I did:
[Code]....
but it executes fine does not give me an error, but also does not return the info i need to the viewmodel
I keep seeing stuff about "ref" on the net but "ref" shows " arbument # should not be passed with the ref keyword.
So i am not sure if there is a problem with the model, or with my understanding this, Now for your info I can do the same step with returning a dataset from a stored procedure fine, but I dont want a data set I just want excatly the data in the output parameters from the stored procedure.
When I want to get the output values its okay but I also want returning a table as a result data.But Datareader has no rows.is it possible if I want a returning query result and multiple output values togather ?I wrote a test above.I can get output values as sqlparameters. But Datareader attached to a Gridview is empty.can you detect whats wrong here and it doesnt return a query result.So stored procedure is not standart or ı am doing something wrong.this doesnt raise any exception.but not returning any data.
ASP.NET 4.0 comes up with a bulk of noticable improvements , including freshment of LINQ to Entities model. Other than this , I still remember another .net built-in ORM framework "LinqToSQL" which provides a lighter and more direct way to interact with SQL-server based db system. However, it seems no term concerns "Linq to sql" in .net 4.0 pulication announcement. So I think it might be deprecated in the furture due to .Net team's plan. I wonder if .NET team decide to just choose entity framework to fully serve as the .NET official ORM implement and no longer develop 'LinqToSQL' . Is LinqToSQL really an attemptive speck and not worth holding in the furture?
There's an issue I'm having in several areas of my ASP.NET site, but am using the following as an example. If I can get it solved, then I'm reasonably sure it has applications elsewhere.Let's say I have a gridview based on the aspnet_membership table. Its LINQ datasource is aspnet_membership, which exists in my datacontext.By itself, it works GREAT in the grid.However, showing UserId in and of itself doesn't have much meaning; I wanted to include UserName.So, in my Public Partial Class, I created a read-only property UserName:
[Code]....
This retrieves and displays the correct data, no problem there.However, I'm not able to sort on that new column. When I attempt to do so, I get the following error: The member 'aspnet_Membership.UserName' has no supported translation to SQL.A workaround is, of course, to use a Sql datasource. But as I'm learning more about LINQ and the partial classes, I want to learn how to do it the correct way.
I have never created my own solution before now. I have always modified existing solutions and know how to do it. For some reason, this new solution isn't connecting to my SQL server correctly.
I am using VWD 2008 Express Edition.
I have MSSQL 2008 Developer Edition.
I have used aspnet_regsql.exe to create the tables in my SQL DB.
I have added the following to my webconfig:
[Code]....
But for some reason, when I try to use the Web Site Administration Tool it thinks it is not setup.
I have a webpage, i have a usercontrol in it , its name ispersonal1, i have tow Radiobutton in this usercontrol, the id of one of them ismale and the other one is female, it is obviously that each time we fill one of these radiobuttons and the other one will be null,in my database the type of these fields are bit , and they have allow null, i defined a property for my usercontrol like this:
I have a webform which inserts data into a sql table successfully (form is bound to SqlDataSource1). The problem I have is that I need to populate a number of the DataFields based on an entered Ref number from a button click. The Ref is passed into a Stored Procedure as the only input parameter, all other params are output params. The procedure seems to run fine, but I can not seem to display any of the output params in my form. Here is my sproc:
PROC AUMS_RETURN_LOOKUP ( @REF @COMPANY @SALUTATION @FORENAME @SURNAME @ADDRESS1 @ADDRESS2 @ADDRESS3 @ADDRESS4 @ADDRESS5 @ADDRESS6 @CITY @COUNTRY @POSTCODE BIGINT, char(10) OUTPUT, char(20) OUTPUT, char(50) OUTPUT, char(50) OUTPUT, char(50) OUTPUT, char(50) OUTPUT, char(50) OUTPUT, char(50) OUTPUT, char(50) OUTPUT, char(50) OUTPUT, char(30) OUTPUT, char(30) OUTPUT, char(20) OUTPUT ) AS SET NOCOUNT ON SELECT @SALUTATION @FORENAME @SURNAME @ADDRESS1 @COMPANY = rtrim([COMPANY]), = RTRIM([SALUTATION]), = RTRIM([FORENAME]), = RTRIM([SURNAME]), = RTRIM([ADDRESS1]), @ADDRESS2 = RTRIM([ADDRESS2]), @ADDRESS3 = RTRIM([ADDRESS3]), @ADDRESS4 = RTRIM([ADDRESS4]), @ADDRESS5 = RTRIM([ADDRESS5]), @ADDRESS6 = RTRIM([ADDRESS6]), @CITY = RTRIM([CITY]), @COUNTRY = RTRIM[COUNTRY]), @POSTCODE = RTRIM([ZIP]) FROM HEADER WHERE REF = REF Here is my code: Protected Sub Button1_Click1(ByVal sender As Object, ByVal e As System.EventArgs) Dim Company As TextBox = DirectCast(FormView1.FindControl("TextBox9"), TextBox) Dim Forename As TextBox = DirectCast(FormView1.FindControl("TextBox10"), TextBox) Dim Surname As TextBox = DirectCast(FormView1.FindControl("TextBox8"), TextBox) Dim TESTREFTextBox As TextBox = DirectCast(FormView1.FindControl("TxtBox_ref"), TextBox) Dim TESTREF = Convert.ToInt64(TESTREFTextBox.Text) Dim CompanyText = Company.Text Dim strCon As String = ConfigurationManager.ConnectionStrings("ConnString").ConnectionString Dim Con As New SqlConnection(strCon) Dim Cmd As SqlCommand Con.Open() Cmd = New SqlCommand Cmd.CommandText = "RETURN_LOOKUP" Cmd.CommandType = CommandType.StoredProcedure Cmd.Parameters.Add("@REF", SqlDbType.Int) Cmd.Parameters("@REF").Value = TESTREF Cmd.Parameters.Add("@COMPANY", SqlDbType.VarChar, 15) Cmd.Parameters("@COMPANY").Direction = ParameterDirection.Output Cmd.Parameters.Add("@SALUTATION", SqlDbType.VarChar, 20) Cmd.Parameters("@SALUTATION").Direction = ParameterDirection.Output Cmd.Parameters.Add("@FORENAME", SqlDbType.VarChar, 50) Cmd.Parameters("@FORENAME").Direction = ParameterDirection.Output Cmd.Parameters.Add("@SURNAME", SqlDbType.VarChar, 50) Cmd.Parameters("@SURNAME").Direction = ParameterDirection.Output Cmd.Parameters.Add("@ADDRESS1", SqlDbType.VarChar, 50) Cmd.Parameters("@ADDRESS1").Direction = ParameterDirection.Output Cmd.Parameters.Add("@ADDRESS2", SqlDbType.VarChar, 50) Cmd.Parameters("@ADDRESS2").Direction = ParameterDirection.Output Cmd.Parameters.Add("@ADDRESS3", SqlDbType.VarChar, 50) Cmd.Parameters("@ADDRESS3").Direction = ParameterDirection.Output Cmd.Parameters.Add("@ADDRESS4", SqlDbType.VarChar, 50) Cmd.Parameters("@ADDRESS4").Direction = ParameterDirection.Output Cmd.Parameters.Add("@ADDRESS5", SqlDbType.VarChar, 50) Cmd.Parameters("@ADDRESS5").Direction = ParameterDirection.Output Cmd.Parameters.Add("@ADDRESS6", SqlDbType.VarChar, 50) Cmd.Parameters("@ADDRESS6").Direction = ParameterDirection.Output Cmd.Parameters.Add("@CITY", SqlDbType.VarChar, 30) Cmd.Parameters("@CITY").Direction = ParameterDirection.Output Cmd.Parameters.Add("@COUNTRY", SqlDbType.VarChar, 30) Cmd.Parameters("@COUNTRY").Direction = ParameterDirection.Output Cmd.Parameters.Add("@POSTCODE", SqlDbType.VarChar, 20) Cmd.Parameters("@POSTCODE").Direction = ParameterDirection.Output Cmd.Connection = Con "@REF", SqlDbType.Int)"@REF").Value = REF"@COMPANY", SqlDbType.VarChar, 15)"@COMPANY").Direction = ParameterDirection.Output"@SALUTATION", SqlDbType.VarChar, 20)"@SALUTATION").Direction = ParameterDirection.Output"@FORENAME", SqlDbType.VarChar, 50)"@FORENAME").Direction = ParameterDirection.Output"@SURNAME", SqlDbType.VarChar, 50)"@SURNAME").Direction = ParameterDirection.Output"@ADDRESS1", SqlDbType.VarChar, 50)"@ADDRESS1").Direction = ParameterDirection.Output"@ADDRESS2", SqlDbType.VarChar, 50)"@ADDRESS2").Direction = ParameterDirection.Output"@ADDRESS3", SqlDbType.VarChar, 50)"@ADDRESS3").Direction = ParameterDirection.Output"@ADDRESS4", SqlDbType.VarChar, 50)"@ADDRESS4").Direction = ParameterDirection.Output"@ADDRESS5", SqlDbType.VarChar, 50)"@ADDRESS5").Direction = ParameterDirection.Output"@ADDRESS6", SqlDbType.VarChar, 50)"@ADDRESS6").Direction = ParameterDirection.Output"@CITY", SqlDbType.VarChar, 30)"@CITY").Direction = ParameterDirection.Output"@COUNTRY", SqlDbType.VarChar, 30)"@COUNTRY").Direction = ParameterDirection.Output"@POSTCODE", SqlDbType.VarChar, 20)"@POSTCODE").Direction = ParameterDirection.OutputDim result As String Cmd.ExecuteNonQuery() result = Cmd.Parameters("@COMPANY").Value CompanyText = result
While the SqlDataReader is being used, the associated SqlConnection is busy serving the SqlDataReader, and no other operations can be performed on the SqlConnection other than closing it. This is the case until the Close method of the SqlDataReader is called. For example, you cannot retrieve output parameters until after you call Close.
If the above claim is true, then why is the following method able to retrieve a value from output parameter before the reader is closed:
public int Something() { using (SqlConnection con = new SqlConnection(this.ConnectionString)) { SqlCommand cmd = new SqlCommand("some_procedure", con); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.Add("@ID", SqlDbType.Int).Direction = ParameterDirection.Output; con.Open(); cmd.ExecuteReader(); return (int)cmd.Parameters["@ID"].Value; } }
You can reset the CommandText property and reuse the SqlCommand object. However, you must close the SqlDataReader before you can execute a new or previous command.
Why must you close sqldatareader before you execute new command?
I am using VS 2008.I am building a datatable of records to be inserted into a SQL Server 2005 table. The program loops through this table and builds a SqlParameter for each DataColumn in the datatable. The SqlParameters are used in the SQL Insert statement.
The field I am having a problem with is of type 'bit' in the database table and has no default value and cannot be nulls. The field is called 'Active' The meaning of this field in the application is Boolean i.e True or False. When inserting the record, I wish to default the field to "False".
When I define the columns in the datatable I am forced to use the following code to build the datatcolumn containing the boolean field i.e. dtcActive.DataType = GetType(Boolean) as there is no GetType(Bit)
Dim dtcActive As New DataColumn("Active") dtcActive.DataType = GetType(Boolean) dtcActive.Unique = False dtcActive.AllowDBNull = False dtcActive.DefaultValue = False dtProviderDayDetails.Columns.Add(dtcActive)
However, when I build the SqlParameter I am forced to use this:
If field.ColumnName = "Active" Then prm.SqlDbType = SqlDbType.Bit prm.Value = "False" End If
This is because there is no SqlDbType.Boolean. The problem I have is there is no value I have been able to give the SqlParameter that is accepted by the Insert statement. It complains that the boolean field cannot be null on Insert.
I have several VB.NET functions which pass and receive values from executed stored procedures using parameters. However, the latest method I am using seems to be a little too specific on the datatype and data length of the parameters for my liking (i.e.
LogActivityCommand.Parameters.Add("@strErrSource", SqlDbType.NVarChar, 300).Value = strErrSource). For example:
[code]....
If I end up changing the datatype and data length properties of the variables in my SQL stored procedures in the future, I am going to have to re-visit my VB code too and alter the parameter settings here as well.
I have a question. How do we define date output parameters for a stored procedure?
before you answer,I should mention that I am not referring to the sql syntax definition ,which is @mydate datetime output.
This is ok,what i am asking is HOW DO I SPECIFY IT IN LINQ WHEN I NEED TO CALL THE STORED PROCEDURE IN THE SERVER SIDE CODE. I am repeating that I do I am not referring to the sql definition .
here is my code for selectiong some records from db table
string strSql = "select * from mtblNBD where SentTo=@SentTo and InternalStatus Is NULL order by DeadLine desc"; SqlCommand com = new SqlCommand(strSql, con); com.Parameters.Add("@SentTo", SqlDbType.NVarChar, 50).Value = (string)Session["uname"];
here I am using parameters for SenTo field but not for NULL so it is ok... or should I use parameters for this field where value is NULL , if yes then how can I use parameter for this