Conversion from type SqlInt32 to type Integer is not valid]

Hi all,

I am developing ASP.NET 1.1 application using VB.NET & SQL Server, on my machine I am using SQL Server 2000, and everything is working just fine.

The problem appears when I uploaded the site to the Host, they are using SQL Server 2005, is there any reason for this, I am using casting in the code, and I am sure there is something wrong with the hosting settings.

Any suggestions.

Best Regards

Wafi Mohtaseb

You can perform an explicit conversion between those two types

Dim xAs System.Data.SqlTypes.SqlInt32 = 5Dim yAs Integer y =CType(x,Integer)

|||

This is what I am doing in the code, and its working on my local server, the problem appears when I uploaded the site to the hosting company server.

Best regards,

|||

please post the code you are having trouble with.

|||

1Public Overrides Function Insert()As Boolean2 Dim cmdToExecuteAs SqlCommand =New SqlCommand3 cmdToExecute.CommandText ="dbo.[UserFiles_Insert]"4 cmdToExecute.CommandType = CommandType.StoredProcedure56' // Use base class' connection object7 cmdToExecute.Connection = _mainConnection89Try10 cmdToExecute.Parameters.Add(New SqlParameter("@.UserID", SqlDbType.Int, 4, ParameterDirection.Input,False, 10, 0,"", DataRowVersion.Proposed, _userID))11 cmdToExecute.Parameters.Add(New SqlParameter("@.FileName", SqlDbType.VarChar, 50, ParameterDirection.Input,False, 0, 0,"", DataRowVersion.Proposed, _fileName))12 cmdToExecute.Parameters.Add(New SqlParameter("@.FileType", SqlDbType.VarChar, 50, ParameterDirection.Input,False, 0, 0,"", DataRowVersion.Proposed, _fileType))13 cmdToExecute.Parameters.Add(New SqlParameter("@.FileSize", SqlDbType.Decimal, 9, ParameterDirection.Input,False, 18, 0,"", DataRowVersion.Proposed, _fileSize))14Dim lengthAs Integer = 015If Not _fileContent.IsNullThen16 length = _fileContent.Length17End If18 cmdToExecute.Parameters.Add(New SqlParameter("@.FileContent", SqlDbType.Image, length, ParameterDirection.Input,False, 0, 0,"", DataRowVersion.Proposed, _fileContent))19 cmdToExecute.Parameters.Add(New SqlParameter("@.FileID", SqlDbType.Int, 4, ParameterDirection.Output,False, 10, 0,"", DataRowVersion.Proposed, _fileID))2021' // Open connection.22 _mainConnection.Open()2324' // Execute query.25 cmdToExecute.ExecuteNonQuery()26Dim _fileIDAs SqlInt32

//Error ocures here27_fileID =New SqlInt32(CType(cmdToExecute.Parameters.Item("@.FileID").Value,Integer))28Return True29 Catch exAs Exception30' // some error occured. Bubble it to caller and encapsulate Exception object31Throw New Exception("UserFiles::Insert::Error occured.", ex)32Finally33' // Close connection.34 _mainConnection.Close()35 cmdToExecute.Dispose()36End Try37 End Function

Constructing a datatable

Hi,

I am experimenting to make a datatable in C# code in a page. This table should be a disconected table with Only valid for the present session.

I try the following code:

publicpartialclassDefault2 : System.Web.UI.Page

{

DataTable DT =newDataTable("TEST");

protectedvoid Page_Load(object sender,EventArgs e)

{

if (!IsPostBack)

{

DataColumn Col1 =newDataColumn("Col1");

Col1.DataType =typeof(Int32);

Col1.AllowDBNull =false;

DT.Columns.Add(Col1);

DataColumn Col2 =newDataColumn("Col2");

Col2.DataType =typeof(string);

Col2.AllowDBNull =true;

DT.Columns.Add(Col2);

DataColumn Col3 =newDataColumn("Col3");

Col3.DataType =typeof(DateTime);

Col3.AllowDBNull =true;

DT.Columns.Add(Col3);

GridView1.DataSource = DT;

}

}

protectedvoid Button1_Click(object sender,EventArgs e)

{

for (int i = 1; i < 20; i++)

{

DataRow MyRow = DT.NewRow();

MyRow["Col1"] = i;

DT.Rows.Add(MyRow);

}

}

}

For one reason or the other. if I click the button I get the message that Col1 dus not make part of the table TEST. It turns out that there are no columns added to the table. altroug the code in the page load part has been run. I suppose I have to do something with the session state to make my DataTable persistent, but I have no idea what. Can somebody help me out?

Thanks!

Rob

You've got the right idea. If you want to save your DataTable within the Session, just change your 'DT' reference to something like this:

private DataTable DT{get{if (this.Session["DT"] ==null){this.Session["DT"] =new DataTable("Test");}return this.Session["DT"]as DataTable;}set {this.Session["DT"] =value; }}

|||

Great! It works. Only one more question. The Datagrid is not updating the records. Do you maybe have also a solution for that?

many thanks

rob

|||

What do you mean by not updating the records?

|||I have put a gridview on the form, and thougt that with the statement

GridView1.DataSource = DT;

I would automaticly see the rows from the data table in de gridview.

But ASP always works the unexpected ways. . .

|||

Add this to the bottom of your Button1_Click event:

GridView1.DataSource =this.DT;GridView1.DataBind();

|||

I thank you very mutch. It works great.

regards Rob

Constraint expression not working

Hi!
I have a table with users in which I would like to have a constraint making
sure that for active users (IsActive=1) a valid user name always exists
(OSUserName <> '' AND OSUserName IS NOT NULL).
I enter the following constraint expression in the table designer:
(([IsActive] = 1 and [OSUserName] is not null) or ([IsActive] = 0 and
[OSUserName] is null))
But when I look at what gets saved, the paranthesis separating the two AND
clauses are gone:
([IsActive] = 1 and [OSUserName] is not null or [IsActive] = 0 and
[OSUserName] is null)
And this also make my constraint fail and allowing IsActive = 0 and
OSUserName = 'XYZ'. How can I write the expression to get the desired
effect?
Brgds
JonasYou can write the same expression in the following way:
SIGN(LEN(ISNULL([OSUserName], ''))) = [IsActive]
But I don't understand your logic. Do you really mean that if you change a
user from being active to non-active that you have set the OSUserName to
NULL as well? That is what you constraint enforces now. If you don't need
that, you can simplify your check to LEN(ISNULL([OSUserName], '')) > 0 OR
[IsActive] = 0. I.e. you either have to provide a username or the user must
be inactive.
Jacco Schalkwijk
SQL Server MVP
"Jonas" <jonas@.no.spam.pl> wrote in message
news:uMmM97iKFHA.732@.TK2MSFTNGP12.phx.gbl...
> Hi!
> I have a table with users in which I would like to have a constraint
> making sure that for active users (IsActive=1) a valid user name always
> exists (OSUserName <> '' AND OSUserName IS NOT NULL).
> I enter the following constraint expression in the table designer:
> (([IsActive] = 1 and [OSUserName] is not null) or ([IsActive] = 0 and
> [OSUserName] is null))
> But when I look at what gets saved, the paranthesis separating the two AND
> clauses are gone:
> ([IsActive] = 1 and [OSUserName] is not null or [IsActive] = 0 and
> [OSUserName] is null)
> And this also make my constraint fail and allowing IsActive = 0 and
> OSUserName = 'XYZ'. How can I write the expression to get the desired
> effect?
> Brgds
> Jonas
>
>|||Instead of using query designer, do your work from Query Analyzer...
ALTER TABLE YourTable
ADD CONSTRAINT CK_IsActive_UserName CHECK
((([IsActive] = 1 and [OSUserName] is not null) or ([IsActive] = 0 and
[OSUserName] is null))
Adam Machanic
SQL Server MVP
http://www.sqljunkies.com/weblog/amachanic
--
"Jonas" <jonas@.no.spam.pl> wrote in message
news:uMmM97iKFHA.732@.TK2MSFTNGP12.phx.gbl...
> Hi!
> I have a table with users in which I would like to have a constraint
making
> sure that for active users (IsActive=1) a valid user name always exists
> (OSUserName <> '' AND OSUserName IS NOT NULL).
> I enter the following constraint expression in the table designer:
> (([IsActive] = 1 and [OSUserName] is not null) or ([IsActive] = 0 and
> [OSUserName] is null))
> But when I look at what gets saved, the paranthesis separating the two AND
> clauses are gone:
> ([IsActive] = 1 and [OSUserName] is not null or [IsActive] = 0 and
> [OSUserName] is null)
> And this also make my constraint fail and allowing IsActive = 0 and
> OSUserName = 'XYZ'. How can I write the expression to get the desired
> effect?
> Brgds
> Jonas
>
>|||Jonas,
Create the constraint from QA.
Example:
use northwind
go
create table t (
IsActive smallint not null check (IsActive = 0 or IsActive = 1) default (0),
OSUserName varchar(50) null,
)
go
alter table t
add constraint chk_OSUserName check (([IsActive] = 1 and
isnull([OSUserName], '') > '') or ([IsActive] = 0))
go
insert into t default values
insert into t values(1, 'MSSQLSERVER')
go
insert into t values(1, null)
go
update t
set IsActive = 1
where OSUserName is null
go
select * from t
go
drop table t
go
AMB
"Jonas" wrote:

> Hi!
> I have a table with users in which I would like to have a constraint makin
g
> sure that for active users (IsActive=1) a valid user name always exists
> (OSUserName <> '' AND OSUserName IS NOT NULL).
> I enter the following constraint expression in the table designer:
> (([IsActive] = 1 and [OSUserName] is not null) or ([IsActive] = 0 and
> [OSUserName] is null))
> But when I look at what gets saved, the paranthesis separating the two AND
> clauses are gone:
> ([IsActive] = 1 and [OSUserName] is not null or [IsActive] = 0 and
> [OSUserName] is null)
> And this also make my constraint fail and allowing IsActive = 0 and
> OSUserName = 'XYZ'. How can I write the expression to get the desired
> effect?
> Brgds
> Jonas
>
>|||No you are not crazy: SQL Server does re-write the check constraint
source.
If a check is defined as "Column in ('a', 'b' )" it is re-written to
"[Column] = 'a' or [Column] = 'b'"
For complex nested conditions, the re-write includes awareness of the
precedence of NOT, AND and OR as defined in Books On Line.
Carl Federl
Please post DDL (create table) with datatypes, primary and foreign keys.
*** Sent via Developersdex http://www.examnotes.net ***
Don't just participate in USENET...get rewarded for it!