DataType Problem

Hi,

I want to pass data to the stored procedure by vb code.One of the input parameters of the SP has Text Data Type.whats the equivalent of this datatype in vb?

Thanks in Advance.

You can use byte array (byte[]) for BLOB data type (text/ntext/image) in SQL Server:)

DataType Issue

Hi

Using SQLserver 2000 I have to enter a huge data (nearly 8000 charcters) into a field, which data type is suitable for that and what is the method to store data.

You might need to use a VARCHAR(8000) or a text datatype; however, there is not enough information.

Is your data divisible into different definite pieces? Do you need to support unicode? Is your data numeric? binary? Is your data XML Is there additional data that needs to be stored with the 8000 characters?

DataType Conversion using WHERE IN ( )

I am getting a "Syntax error converting the varchar value '10,90' to a column of data type int." error when I run the following procedure:

@.myList varchar(200)

SELECT column1
FROM table1
WHERE table1.ID IN (@.myList)

When @.myList is a single value, I get no errors. However, when @.myList is a comma separated list like in the message above, I error out. I am using SQL Server 2000.

How else can I build this list of IDs? Thank you in advance for your comments.

--ColonelYou cannot do what you are trying to do. YOu need to use dynamic SQL, or send in a string and use a function to create a table variable and do the operation based upon a select on that table variable.|||I found that my varchar parameter was being sent in with single quotes around it. I removed these, and now my WHERE clause looks like this:

WHERE table1.ID IN (REPLACE(@.myList,'''',NULL))

and it works just fine.

I did not add those quotes to the list of values. I believe that SQL Server adds them to delimit the text. Thank you for your comments.

Datatype Conversion Problem.

Hi ALL!

I have a table named 'Table1' which contains a column 'Name'.
The data type of column [Name] is varchar(50).

When i try to change its datatype to binary by trying following code

ALTER TABLE Table1 Alter Column [Name] Binary(5000)

It gives following error.

" Creation of table 'bp_MAIN' failed because the row size would be 10021, including internal overhead. This exceeds the maximum allowable table row size, 8060. "

So, how can i change the datatype of this column ?

Regards,
Shabber Abbas.U cannot convert varchar column to binary column explicitly.
One solution is ,create a new table (lets say t1) with binary datatype.
Then convert and insert record into t1 table from ur original table.
Drop original table and rename new table to original table.
set same permission as orginal table.

--eg:
insert into t1(othercolumnnames,name) select othercolumnnames,convert(binary(5000),name) as name from Table1sql

Datatype Conversion Problem.

Hi ALL!

I have a table named 'Table1' which contains a column 'Name'.
The data type of column [Name] is varchar(50).

When i try to change its datatype to binary by trying following code

ALTER TABLE Table1 Alter Column [Name] Binary(5000)

It gives following error.

" Creation of table 'bp_MAIN' failed because the row size would be 10021, including internal overhead. This exceeds the maximum allowable table row size, 8060. "

So, how can i change the datatype of this column ?

Regards,
Shabber Abbas.Not meaning to be thick here, but why are you changing a varchar to a binary? Did you want instead to change it to nvarchar?

Regards,

hmscott|||In this case, you are implying that you want to convert varchar data to binary. I don't think that can be done automatically. The error might be misleading.

If that's the only field, it shouldn't give that error, but a table consisiting of only a binary field seems like it's not very useful. Is a blob out of the question? it only takes up 16bytes of the page. Yould definetely need to export/import then.

You should be able to add a binary column, or export the data, recreate the table with a binary field, and then import the data, with suitable massaging.|||You could use varbinary, but if the amount of data in the row exceeds 8060, you will get errors, instead of warnings.

Datatype

I am trying to convert a NVCHAR datatype to a DATETIME data type is that possible?Only if it's a valid date. Post a simple example of failure along with associated code and data values and let's see what we can do.|||DECLARE @.x TABLE (Col1 nvarchar(25))
INSERT INTO @.x(Col1)
SELECT '12/31/2004 11:59:59' UNION ALL
SELECT 0 UNION ALL
SELECT 'Happy New Year'

SELECT CONVERT(datetime,Col1) FROM @.x WHERE ISDATE(Col1) = 1|||Here is the select statement I am using but when I use it it doesnt just give me the 2004 only it gives me everything in the table
SELECT TM#, LastName, FirstName, [Date]
FROM Revocations_Tbl
WHERE ([Date] BETWEEN '01/01/2004' AND '12/31/2004')

this is what the tables design looks like all the other tables have DATETIME for their datatypes and I have no problems, when I tried to convert the datatype I got alot of error messages|||To be honest...I'd fix the table...

ALSO! Anyone figure out why I'm getting the conversion error in the following...

USE Northwind
GO

SET NOCOUNT ON
CREATE TABLE myRevocations_Tbl99(TM# int, LastName nvarchar(25), FirstName nvarchar(25), [Date]nvarchar(10))
GO

INSERT INTO myRevocations_Tbl99 (TM#, LastName, FirstName, [Date])
SELECT 1, 'Kaiser', 'Brett', '10/24/2004' UNION ALL
SELECT 2, 'Kaiser', 'Brett', '01/01/2005' UNION ALL
SELECT 3, 'Kaiser', 'Brett', '12/31/2003' UNION ALL
SELECT 4, 'Desiree', 'A', '01/01/2004' UNION ALL
SELECT 5, 'Desiree', 'Whos', '10/19/2004' UNION ALL
SELECT 6, 'Desiree', 'Your', '10/19/200a' UNION ALL
SELECT 7, 'Desiree', 'Their', '12/31/2004' UNION ALL
SELECT 8, 'A Row', 'Of Data 1', 'aaaaaaaaaa' UNION ALL
SELECT 9, 'A Row', 'Of Data 2', '0' UNION ALL
SELECT 0, 'A Row', 'Of Data 3', '123456789'
GO

-- Your's

DECLARE @.x varchar(10), @.y varchar(10)
SELECT @.x = '01/01/2004', @.y = '12/31/2004'
SELECT TM#, LastName, FirstName, [Date]
FROM myRevocations_Tbl99
WHERE [Date] BETWEEN @.x AND @.y
GO

-- Mine

DECLARE @.x datetime, @.y datetime
SELECT @.x = '01/01/2004', @.y = '12/31/2004'
SELECT TM#, LastName, FirstName, [Date]
FROM (SELECT TM#, LastName, FirstName, [Date]
FROM myRevocations_Tbl99
WHERE ISDATE([Date])=1) AS XXX
WHERE DATEDIFF(yy,CONVERT(datetime,[Date]),@.x) = 0
AND DATEDIFF(yy,CONVERT(datetime,[Date]),@.y) = 0
GO

SET NOCOUNT OFF
DROP TABLE myRevocations_Tbl99
GO|||ALSO! Anyone figure out why I'm getting the conversion error in the following...

It looks like the optimizer is flattening out your query into a single table scan, eliminating the ISDATE, then choking on the strings that aren't dates. At least that's what the estimated plan indicates.
Seems like an odd thing to do.|||Put the row for [TM#] 8 first, then it will work.

-PatP|||Put the row for [TM#] 8 first, then it will work.

-PatP

Like I'd have any control over the data...|||Well actually Pat, It blows up right away...|||This works...

DECLARE @.xxx table (TM# int, LastName nvarchar(25), FirstName nvarchar(25), [Date]nvarchar(10))

INSERT INTO @.xxx (TM#, LastName, FirstName, [Date])
SELECT TM#, LastName, FirstName, [Date]
FROM myRevocations_Tbl99
WHERE ISDATE([Date])=1

DECLARE @.x datetime, @.y datetime
SELECT @.x = '01/01/2004', @.y = '12/31/2004'
SELECT TM#, LastName, FirstName, [Date]
FROM @.xxx
WHERE DATEDIFF(yy,CONVERT(datetime,[Date]),@.x) = 0
AND DATEDIFF(yy,CONVERT(datetime,[Date]),@.y) = 0
GO

DataSource uploads as text file type

Hi,
I have a new installation of SQLExpress ADV with RS installed on XP Pro.
When I try to upload an rds file (which has already been running
successfully on a w2k3 server), it uploads as a text file and NOT as a
Datasource.
The associated report does of course not work.
Anyone seen this before - or know how to fix it please '.
Thanks
Regards
GrahamHi Oxns,
Thank you for your posting!
From your description, my understanding of this issue is: you want to
upload the report data source file as a shared data source in the report
manager. If I misunderstood your concern, please feel free to let me know.
You cannot upload a report data source (.rds) file to create a shared data
source. An .rds file is used only in Report Designer. It cannot provide the
content for a shared data source item that you define and manage through
Report Manager or Management Studio. As an alternative to uploading, you
can write a script that creates a shared data source based on a .rds file.
Please refer to the sample script PublishSamplReports.rss file in the
Samples. By default, scripting samples are located in C:\Program
Files\Microsoft SQL Server\90\Samples\Reporting Services\Script Samples.
Here are the articles for your reference:
Script Samples (Reporting Services)
http://msdn2.microsoft.com/en-us/library/ms160854(d=ide).aspx
Uploading Files to a Folder
http://msdn2.microsoft.com/en-us/library/ms156300(d=ide).aspx
Hope this will be helpful!
Sincerely,
Wei Lu
Microsoft Online Community Support
==================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
==================================================This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Oxns,
How are you doing on this issue? Have you got any progress or does Wei's
last reply help you some on this? If the problem is not resolved or there
is anything else we can help, please feel free to post here.
Sincerely,
Steven Cheng
Microsoft MSDN Online Support Lead
This posting is provided "AS IS" with no warranties, and confers no rights.

Dataset - Command type question

Hi to all
I need to use a set of stored procedures as source of my report datasets, my
problem is that I have several reports already created some of them using
StoredProcedure as command type, and others using Text and using "EXEC
MyStoreProcedure @.Param1,@.Param2" as the Query String , it seems to work in
the same way, change one of the sets of reports can cost some resources of
my team.
My question is if no exist differences or I can have problems with this
second type of datasets?
ThanksFor "SQL Server" connections they are for all practical purposes identical.
--
Thanks.
Donovan R. Smith
Software Test Lead
This posting is provided "AS IS" with no warranties, and confers no rights.
"Mauricio Cadima" <mcadima@.jalasoft.com> wrote in message
news:#XmzhXedEHA.2544@.TK2MSFTNGP10.phx.gbl...
> Hi to all
> I need to use a set of stored procedures as source of my report datasets, my
> problem is that I have several reports already created some of them using
> StoredProcedure as command type, and others using Text and using "EXEC
> MyStoreProcedure @.Param1,@.Param2" as the Query String , it seems to work in
> the same way, change one of the sets of reports can cost some resources of
> my team.
> My question is if no exist differences or I can have problems with this
> second type of datasets?
> Thanks
>sql

Dataconversion: STRING (yyyymmdd) to DATE

Hi All,

I need a solution for the following:

I have a field with data type string, length 8, in the form yyyymmdd (f.e. 20070604).

Now I need to transfer this field into a field with data type DATE.

I know the function DATESERIAL in MS ACCESS but what is the equivalent function in TSQL?

Any hint for me is very

Thanks in Advance

ulrike

You just need to make sure DATEFORMAT is set to match the incoming data and then perform a conversion.

Code Snippet

setDATEFORMAT ymd

selectconvert(datetime,'20070604')as d1,cast('20070604'asdatetime)as d2

|||

You could use CONVERT function with 112 style:

Code Snippet

declare @.dt varchar(20)

set @.dt ='20070507'

selectconvert(datetime,@.dt,112)

|||

Hello again,

Thanks for your advise.

I tried to run the following code:

selectconvert(datetime,whuser.[tbl_source].EffectiveDate_str,112)as EffectiveDate_dt

into WHUser.[tbl_target]

from WHUser.[tbl_source]

BUT it terminated with error:

“The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value”.

What’s going wrong? Please give me some mor advise.

Thanks in advance,

ulrike

|||

Hi ulrikeG,

SQL Server will convert that value implicitly without any problem. You are using the ISO format and SQL Server will interprete it corrrectly, no matter the language or settings of dateformat being used.

Code Snippet

create table dbo.t1 (

c1 char(8) null,

c2 datetime null

)

go

insert into dbo.t1(c1) values('20070606')

go

select * from dbo.t1

go

update dbo.t1

set c2 = c1

go

select * from dbo.t1

go

drop table dbo.t1

go

AMB

|||

ulrikeG wrote:

“The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value”.

Maybe there's bad data in your table.

Try running a query WHERE IsDate (string) = 0.

|||Change your code to:

Code Snippet

select CASE WHEN ISDATE(whuser.[tbl_source].EffectiveDate_str) = 1 THENconvert(datetime,whuser.[tbl_source].EffectiveDate_str,112) ELSE NULL ENDas EffectiveDate_dt

into WHUser.[tbl_target]

from WHUser.[tbl_source]

Then:
SELECT * FROMWHUser.[tbl_target] WHERE EffectiveDate_dt IS NULL
to find the errors

database type question

Hi everyone, I am trying to create a page on my website that will be unique depending on the logged on user. Basically, I have a aspnet_User table, a Team table, and a Player table. What I am trying to do is make a datagridview that is editable, but only showing the 'players' from the player table that is associated to that member. I have tried to set a relation between the tables in a dataset, but that only got me so far before I got stuck, and I've also tried to do it making a view between the three tables before I got stuck. My tables look like this:

'aspnet_Users' has a
User ID
User Name
along with the other column associated with this table

'Team' has a
Team ID
Team Name
User Name

'Player' has a
Player Name
Team ID
Player Rating
Class

What I would really like to be able to do, is when the webpage is loaded, the datagridview reads the User Name from the aspnet_User table and matches it to the corresponding Team ID in the Team Table and then filters the 'Player' table to only show players with the corresponding Team ID. I have been stuck on this for two weeks now and it is driving me crazy. I seem to get a breakthrough only to get stuck again. Please Help!!! If you need any more info from me, please let me know!

Use a stored procedure as your datasource for your GV. Pass the teamID in as a parameter.

|||

What you need is a gridview that is populated by a parameterised query, like so:

SELECT
PlayerName
FROM
Team INNER JOIN Player ON Team.TeamID = Player.TeamID INNER JOIN
aspnet_users ON Team.UserName = aspnet_users.UserName
WHERE
aspnet_users.UserName @.UserName

Set this at the SQL for the Gridview and then the @.UserName parameter's value is Context.User.Identity.Name. Context.User.Identity.Name is the username of the currently logged in user.

|||

Ok, now, stupid question

By gridview, you mean make a view from the database right?

I then put in the three tables, make my relationship and add in the sql statement you just provided me, correct?

Then where, and/or how do I set the parameter's value?

I really appreciate the help guys, I think this is starting to make sense!

|||

Ok,

I made my gridview with the 3 tables and tried to insert the sql statement you gave me and it tells me this

incorrect syntax near @.UserName

|||

Here's a good thread for you:

http://forums.asp.net/p/991273/1317881.aspx

|||

First off, thank everyone so far for all your help.

I think I have everything set up the way it is supposed to be. When I test the query and type in 'ryan', one of my UserNames it returns the correct team. if I try to type in a wrong name that does not exist (i even put a name in the Team table and not the username table, and it didn't pull, so i think it is pulling from the right table)

Here's the problem. when i go to web page preview, no datagridview shows up on my page, nothing, nada, zip...like it doesn't exist.

what do i have wrong?

|||

Ah. Try a databind for your grid on the GridView_PreRender event.

|||

So are you typing in a username in a textbox and then clicking a button? On the button click event are you calling the DataBind method on your gridview?

All you need to do is call this in your PageLoad event after setting the textbox.text to the name of the currently logged in user. Do you follow?

|||

I've got a login box that i inserted through the ide. in the code it doesn't give a pageload reference. i remember doing a pageload on a button click with visual basic, but i'm not sure how to work in into this.

<asp:LoginID="Login1"runat="server"BackColor="#EFF3FB"BorderColor="#B5C7DE"BorderPadding="4"

BorderStyle="Solid"BorderWidth="1px"Font-Names="Verdana"Font-Size="0.8em"

ForeColor="#333333"DestinationPageUrl="index.aspx">

<TitleTextStyleBackColor="#507CD1"Font-Bold="True"Font-Size="0.9em"ForeColor="White"/>

<InstructionTextStyleFont-Italic="True"ForeColor="Black"/>

<TextBoxStyleFont-Size="0.8em"/>

<LoginButtonStyleBackColor="White"BorderColor="#507CD1"BorderStyle="Solid"BorderWidth="1px"

Font-Names="Verdana"Font-Size="0.8em"ForeColor="#284E98"/>

</asp:Login>

It gives me a destination url, but thats about it. does the code need to go into here somewhere?

btw, thank you for your help and patience

|||

Where's your codebehind?

<%@. Page Language="C#"
MasterPageFile="~/MasterPage.master"
AutoEventWireup="true"
EnableEventValidation="false"
Inherits="blah.blah.blah"
Title="balh : blah"
Codebehind="blah.aspx.cs"
Description="blah."
Theme="blah"
EnableTheming="true"
%>

Then you access your page_load in the aspx.cs file.|||

can you give me an example?

btw, it tells me that codebehind is no longer used

|||

ummm. I'm using it. Is there something someone needs to tell me?

Are you using VS2005? When you start a project in C#, select Web and you'll get a aspx and aspx.cs (codebehind) pages. If you are using VB, you may need to wire up the codebehind manually, I don't know. Does anyone out there know about codebehind being obsoleted? AM I CODEBEHIND THE CURVE!?!?!?!

|||

sorry, i'm using vb not c#, my files are aspx.vb

its telling me that it is still compatible with asp.net 2.0, but now they would 'rather' you use the codefile and the inherits property

ok, i can get into the aspx.vb files and it gives me

PartialClass Teams_westerville

Inherits System.Web.UI.Page

EndClass

i put the page_load into here, right?

I'm at work right now, going to try it when i get home

|||

Must be a VB thing. My ASP.Net 2.0 C# page here has a codebehind and an inherits. I'm inheriting what appears to be the namespace... but it compiles. so it must be right, right?