I have a password field which is of varbinary. Since its a varbinary I
cannot see the password in the database I only see hexadecimal values.
Now my question is that how can I convert those hexadecimal values to
string or varchar so I can read the password.
any ideas ??
Hi everyone,
We're trying to migrate a varchar field from Sql2k to varbinary in a sql25k through a dtsx package. We get an error which tell us: "data will be lost".
Does anyone have any idea about that?
Thanks for your time and inputs,
On the ntext, text, and image (Transact-SQL) page at
http://msdn2.microsoft.com/en-us/library/ms187993.aspx
it states
"Important:
ntext, text, and image data types will be removed in a future version of Microsoft SQL Server. Avoid using these data types in new development work, and plan to modify applications that currently use them. Use nvarchar(max), varchar(max), and varbinary(max) instead. For more information, see Using Large-Value Data Types."
Considering this warning, is VARCHAR(MAX), NVARCHAR(MAX) and VARBINARY(MAX) support going to be implemented in SQL Server Compact Edition?
Robert Wishlaw
Hi Gurus,
Here is my problem-:
I have a field in the SQL 2005 database with type varbinary(50). Now when i enter value to this field, I enter a value for example 111111101. The reason to enter such a value in varbinary(50) type is, that this value is meant to grow, as each 1 and 0 in this value is a permission or right my user would have to various features of the web application, I am designing. Now when I open the table to see the data in the database, it shows as <Binary data>. But that's okay, as binary fields are mostly meant to store pictures, files etc. But when I read this value into my ASP.Net code using c#, the value as returned as 069F6BBD. After some research I realized that for decimal value 111111101, the hex value is 069F6BBD.
All I need in my code is the same thing that I entered which is 111111101.
Can someone guide me a lil bit.
Thanks in advance..
VJ
I have a password column that needs to be converted from varchar to varbinary. Can anybody provide me a proper CONVERT statement syntax for it?
View 12 Replies View RelatedI have a test database that contains a varbinary(max) field and a varchar(max) field.
when I do a
Select * from test where id = xx
I get the expected results if my connection string uses
'driver=SQL Server;Server'
but these two fields return no data if I use
'driver=SQL Native Client'
the other fields in the record come back with no problems.
Is there anything special I need to do to retrieve these types of fields?
I just learned that bit masking a varbinary column can increase it's length in bytes unnecessarily. For example, I ran the following...
declare @v1 varbinary(max)
set @v1 = 0x0100AB
select len(@v1)
set @v1 = @v1 | (len(@v1) - 2) * 256
select master.dbo.fn_varbintohexstr(@v1)
select len(@v1)
...and get
3
0x00000000000101ab
8
This messes up a plan I had for varbinary column use.
So I quickly tried the following to look for strategies to deal with this unwanted growth...thought being that while I'm passed a varbinary(max), operating on a varbinary that matches its passed length would avoid the unwanted growth after bit masking....
declare @i int
set @i = 5
declare @v2 varbinary(@i)
...but got errors.
So I suppose I can use a combo of the len, substring etc functions to correct the situation after bit masking but would like to know if the more elegant approach of dynamically sizing a varbinary is possible in t-sql, or if perhaps there is a way to prevent the unwanted growth during bit masking.
I have a very large database running in a production environment. The backup files are getting to be very difficult to manage. They are at 70gb as of now and growing at a rate of 10gb per month. This is due to document images being stored in the database in one table. I have read a little about a new feature in SQL Server 2012 called Filetable. Can we migrate to SQL Server 2012 and convert to the filetable structure, will this decrease the size of the backups? Would backup of the document images be taken care of by our nightly file system backups now?
View 1 Replies View RelatedI'm using SQL Server 2005. How can I convert a hex string to a varbinary or the opposite of that?
View 6 Replies View RelatedI am using rowVersion to detect rows that need to be re-extracted for my ETL. The first step in the ETL is to get MIN_ACTIVE_ROWVERSION() from the database, as well as the rowVersion from the last successful ETL run. Both of these values are saved in variables in SSIS. Since SSIS doesn't have an associated data type, these values are converted to nvarchar(512) before getting sent to SSIS.
I'm now trying to create a stored procedure to grab the data out of the source system. The sproc takes RowVersionMin and RowVersionMax as input parameters. Those parameters are then converted to varbinary(max), and I use them in a query to select the appropriate rows.
When I run the sproc in SSMS, it runs without a problem. However, when I try to use the sproc as a data source in SSIS, I get the following error: "Error converting data type nvarchar to varbinary". Why SSIS has a problem with this, but SSMS does not? The sproc code looks like this:
ALTER PROCEDURE [dbo].[dw_DimComment_DataLoad]
@RunType varchar(3),
@RowVersionMin nvarchar(512),
@RowVersionMax nvarchar(512)
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
[Code] ....
I have an Execute SQL Task that queries for the contents of a file (which is declared as a varbinary type). My SSIS package needs to write these contents into a temporary location, so what I do is that I pass the result into a System.Object variable. I then use that variable in a Script Task.
Problem is whenever I execute my script task and DirectCast or CType the variable to a string (which is what the contents of the file is), I get a "Conversion from type '_ComObject' to type 'String' is not valid."
Any ideas on how to go about this problem?
Hi,
I'm converting a query from Access to SQL Server.
In this query I select from a column that contains numbers, the result I want is a varchar that is always 2 chars wide..
Ie:
7 should be selected as '07'
12 should be selected as '12'
In the Access-query it's rather nicely done with:
Format(Str(mycolumn),"00")
I could not find a way to make CONVERT do the same job... but I found that:
LEFT('00',2-LEN(CAST(mycolumn as varchar)))+CAST(mycolumn as varchar)
will do the job.
But it feels like it could be done nicer.. any suggestions?
Hi,
I am trying ot do a count with a clause like this "amtdue >=10000 and amtdue <=25000" --- I get and error that says "Syntax error converting the varchar value '52.91' to a column of data type int" There are 20 million records in the table - this field is formatted as a varchar - can someone help me find the problem?
Thanks
John
hi all.
in my database i have two fields ..aprsal and datatype is varchar(100)
second field is maysal datatype is varchar(100)
i want to add these values after converting in to the int
how can i do this in sql
I am struggling with converting a certain varchar column into an int.I have a table that has 2 fields - one field holds the loan number andthe other field holds the codes associated with that loan number.Here's some example data:Loan# Codes11111 24-13-122222 133333 2-9I need to check the Codes field for certain code numbers. The Selectstatement I'd like to use is:SELECT Loan#FROM Table1 WHERE Codes IN (2, 13, 1)/*My desired results is that all loans from the above example would beselected because they all have one of these codes*/Of course I cannot use the above statement because the Codes field is avarchar. And if I put single quotes around the numbers in my INstatement I don't get the desired results; the fields with multiplecodes are excluded.But how do I convert this varchar to an int? A simple convert or caststatement doesn't work. I've looked all over the web to find how to dothis, but have not been able to figure it out. Any help would be muchappreciated.
View 5 Replies View RelatedSyntax error converting the varchar value '3.1.7.4.3.9.' to a column of data type int...
how to overcome this problem ..
I wrote a query that takes the field from one record and compares it to a different field in a different record. When I did it in access it worked. But I did a copie and paste into management studio and it is not working.
SELECT A.TRAN_ID, B.REFERENCE_DATA
FROM dbo.BSTRN_HEADER AS B LEFT OUTER JOIN
dbo.BSTRN_HEADER AS A ON B.REFERENCE_DATA LIKE A.TRAN_ID &'RECL'
I get the following error message:
Msg 245, Level 16, State 1, Line 1
Conversion failed when converting the varchar value 'RECL' to data type int.
So then I try:
SELECT A.TRAN_ID, B.REFERENCE_DATA
FROM dbo.BSTRN_HEADER AS B LEFT OUTER JOIN
dbo.BSTRN_HEADER AS A ON B.REFERENCE_DATA LIKE cast(A.TRAN_ID as varchar) & 'RECL'
I get the following error
Msg 402, Level 16, State 1, Line 1
The data types varchar and varchar are incompatible in the boolean AND operator.
Tran_id is an integer and reference data is a varchar and I need to convert tran_id to a Varchar
Is it possible to convert from ntext to varchar or text??
This is the syntax: ALTER TABLE ssu ALTER COLUMN analysis varchar NULL. This is the error message
Server: Msg 4928, Level 16, State 1, Line 1
Cannot alter column 'ANALYSIS' because it is 'ntext'. Thanks in advance!
CREATE FUNCTION dbo.fnTS2VC
(
@ts BINARY(8)
)
RETURNS VARCHAR(16)
AS
BEGIN
RETURNSUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 1, 1) AS TINYINT) / 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 1, 1) AS TINYINT) % 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 2, 1) AS TINYINT) / 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 2, 1) AS TINYINT) % 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 3, 1) AS TINYINT) / 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 3, 1) AS TINYINT) % 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 4, 1) AS TINYINT) / 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 4, 1) AS TINYINT) % 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 5, 1) AS TINYINT) / 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 5, 1) AS TINYINT) % 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 6, 1) AS TINYINT) / 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 6, 1) AS TINYINT) % 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 7, 1) AS TINYINT) / 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 7, 1) AS TINYINT) % 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 8, 1) AS TINYINT) / 16, 1)
+ SUBSTRING('0123456789ABCDEF', 1 + CAST(SUBSTRING(@ts, 8, 1) AS TINYINT) % 16, 1)
ENDCall with
SELECT dbo.fnTS2VC(@@DBTS)
You can also use SELECT CAST(@@DBTS AS BIGINT) if you only need to convert the TIMESTAMP value to INTEGER.
Peter Larsson
Helsingborg, Sweden
Hi
I have a table named prodwin in which i have to update a column name mfgdt which is varchar(10) to 7 months which I am able to do but the problem is that since it is only 10 character it is cutting the end of the year for eg
Before the date was
5/22/2000
UPDATE PRODWIN_MM SET MFGDT = DATEADD(MM,7,MFGDT)
Now it is this
Dec 23 200
Please tell me what to do , should i use cast or convert ?
Thanks in Adv
Hi all,
I'm trying to update a table as follows:
Where in the table i got.
OldCol; a varchar
NewCol; Float
Update tbl set newCol = cast(float,oldcol)
Where isNumeric(oldcol) =1
But it give me the follwong error:
Error converting data type from Varchar to Float.
Any suggestion?
Hi all I would like anyone to help I want 2 convert a datetime to 2008-02-02 21:00 the output must leave the time just 2008-02-02
If (@DateCreated <> 0)
Begin
Set @varSelectSql = @varSelectSql + ' And convert(varchar(10),DateCreated,121) = '+ cast(@DateCreated as varchar(10))
End
Hi,
I wanted to convert the varchar to date time and here is what i am doing
DECLARE @dt VARCHAR(20)
SET @dt = '20070111' -- YYYYMMDD format
select CONVERT(datetime, @dt, 120)
This works perfectly fine and the result would be- 2007-01-11 00:00:00.000
But if i changed my datetime format from YYYYMMDD to YYYYMMDDHHMM then this is failing and throwing
"Conversion failed when converting datetime from character string."
Can any one please let me know how do we achieve this?
~Mohan
Hi,
I'm facing a small issue with date conversions. It would have been great if someone could help me out. I have a field in my database (SQL Server 2000) called SavDateTime of type varchar(50). I store dates in this field in the format "dd/MM/yy hh:mms". A sample date would be "23/10/2007 10:15:30 AM". Now I need to have an order by for this field, say like,
select * from sample order by SavDateTime desc
When I execute this query I get an error saying "The conversion of a char data type to a datetime data type resulted in an out-of-range datetime value.". I tried using the convert method also like CONVERT(DATETIME,SavDateTime,103), but the problem still exists. It would have been helpful if you could provide me with a solution to this problem.
Thanks & Regards,
Frens
why do i have this error? not converting a varchar into int 1 ALTER PROCEDURE dbo.RevertDB
2 (
3 @Log varchar(MAX) = NULL OUTPUT
4 )
5
6 /* Reverts Database to original "Clean" State */
7 AS
8 SET NOCOUNT OFF
9 DECLARE @RowsInDB AS int
10 SET @Log = 'RevertDB Started at ' + CAST(GETDATE() AS varchar(50)) + '<br />'
11
12 /* *** Disable Constraints ***
13 ALTER TABLE Booking NOCHECK CONSTRAINT ALL
14 ALTER TABLE InventoryPC NOCHECK CONSTRAINT ALL
15 ALTER TABLE PC NOCHECK CONSTRAINT ALL
16 ALTER TABLE Platform NOCHECK CONSTRAINT ALL*/
17
18 /* *** Start Deletes *** */
19 DELETE FROM Booking
20 SET @Log = @Log + 'Clear Table Booking - Done' + '<br />'
21 SET @RowsInDB = (SELECT COUNT(BookingID) FROM Booking)
22 SET @Log = @Log + '-- Rows Affected: ' + CAST(@@ROWCOUNT AS varchar(10)) + ', Rows in Table: ' + CAST(@RowsInDB AS varchar(10)) + '<br />'
23
24 DELETE FROM InventoryPC
25 SET @Log = @Log + 'Clear Table InventoryPC - Done' + ''
26 SET @RowsInDB = (SELECT COUNT(InventoryID) FROM InventoryPC)
27 SET @Log = @Log + '-- Rows Affected: ' + CAST(@@ROWCOUNT AS varchar(10)) + ', Rows in Table: ' + CAST(@RowsInDB AS varchar(10)) + '<br />'
28
29 DELETE FROM PC
30 SET @Log = @Log + 'Clear Table PC - Done' + '<br />'
31 SET @RowsInDB = (SELECT COUNT(PCID) FROM PC)
32 SET @Log = @Log + '-- Rows Affected: ' + CAST(@@ROWCOUNT AS varchar(10)) + ', Rows in Table: ' + CAST(@RowsInDB AS varchar(10)) + '<br />'
33
34 DELETE FROM Platform
35 SET @Log = @Log + 'CLear Table Platform - Done' + ''
36 SET @RowsInDB = (SELECT COUNT(PlatformID) FROM Platform)
37 SET @Log = @Log + '-- Rows Affected: ' + CAST(@@ROWCOUNT AS varchar) + ', Rows in Table: ' + CAST(@RowsInDB AS varchar(10)) + '<br />'
38
39 /* *** Enable Constraints ***
40 ALTER TABLE Booking WITH CHECK CHECK CONSTRAINT ALL
41 ALTER TABLE InventoryPC WITH CHECK CHECK CONSTRAINT ALL
42 ALTER TABLE PC WITH CHECK CHECK CONSTRAINT ALL
43 ALTER TABLE Platform WITH CHECK CHECK CONSTRAINT ALL*/
44
45 SET @Log = @Log + '*** End Truncates ***' + '<br />'
46 /* *** End Truncates *** */
47
48 /* *** Start Insert Platform *** */
49 SET @Log = @Log + 'Start Insert Platform' + '<br />'
50
51 EXEC dbo.InsertPlatfrom 'Windows XP SP2 Professional Edition', 'Some description for Windows XP SP2 Professional Edition over here …'
52 EXEC dbo.InsertPlatfrom 'Windows Vista Ultimate', 'See everything you''re working on more clearly with Windows Aero, and quickly switch between windows or tasks using Windows Flip 3D and Live Thumbnails. You can easily find what you need—when you need it―with Instant Search and live icon previews that display the actual contents of your files. And while you''re at it, give your personal productivity a boost with instant access to the information you care about using Windows Sidebar and Gadgets. Put these easy-to-use and customizable mini-applications on your desktop and reveal the information you''re looking for at a glance. Website: http://www.microsoft.com/windows/products/windowsvista/seeit/default.mspx'
53 EXEC dbo.InsertPlatfrom 'Apple Mac OS X Tiger', 'Some description for Apple Mac OS X Tiger over here …'
54 EXEC dbo.InsertPlatfrom 'Apple Mac OS X Leopard', 'Desktop: The new look of Leopard showcases your favorite desktop image and puts new file Stacks at your fingertips for a stunning, clutter-free workspace. Finder: Browse your files like you browse your music with Cover Flow. Time Machine: See how your system looked on any given day and restore files with a click. Website: http://www.apple.com/macosx/leopard/features/'
55 EXEC dbo.InsertPlatfrom 'Red Hat Linux', 'Some description for Red Hat Linux over here …'
56
57 SET @Log = @Log + 'Rows In Platform: ' + CAST((SELECT COUNT(PlatformID) FROM Platform) AS varchar(10)) + '<br />'
58 /* *** Start Insert PC *** */
59 SET @Log = @Log + 'Start Insert PC' + '<br />'
60
61 DECLARE @WinXP int, @WinVista int, @OSXTiger int, @OSXLeopard int, @RedHat int
62 SET @WinXP = (SELECT PlatformID FROM Platform WHERE Title = 'Windows XP SP2 Professional Edition')
63 SET @WinVista = (SELECT PlatformID FROM Platform WHERE Title = 'Windows Vista Ultimate')
64 SET @OSXTiger = (SELECT PlatformID FROM Platform WHERE Title = 'Apple Mac OS X Tiger')
65 SET @OSXLeopard = (SELECT PlatformID FROM Platform WHERE Title = 'Apple Mac OS X Leopard')
66 SET @RedHat = (SELECT PlatformID FROM Platform WHERE Title = 'Red Hat Linux')
67
68 EXEC dbo.InsertPC 'Fusion PC One', 'Description here ...', 'Intel Core2 Duo E6600 2.4 GHz 1066MHz', '1GB Dual Channel DDR2 667 SDRAM', '120GB SATA2 NCQ HDD', 'NVIDIA GeForce 8600 256MB GDDR3', '22" 3000:1 Wide Screen LCD', @WinXP
69 EXEC dbo.InsertPC 'Fusion PC Two', 'Description here ...', 'Intel Core2 Duo E6850 3 GHz 1333MHz', '2GB Dual Channel DDR2 800 SDRAM', '240GB SATA2 NCQ HDD', 'NVIDIA GeForce 8800 Ultra 256MB GDDR3 SLI', '24" 3000:1 Wide Screen LCD', @WinVista
70 EXEC dbo.InsertPC 'Fusion PC Three', 'Description here ...', 'AMD Athlon 64 X2 Dual Core 6000+ 3 GHz', '2GB Dual Channel DDR2 667 SDRAM', '240GB SATA2 NCQ HDD', 'ATI Radeon Cross Fire 2900 256MB GDDR3', '24" 3000:1 Wide Screen LCD', @WinVista
71 EXEC dbo.InsertPC 'Fusion X1', 'Description here ...', 'Intel Core2 Extreme Q6850 3 GHz 1333MHz', '6GB Dual Channel DDR2 800 SDRAM', '500GB SATA2 NCQ HDD', 'NVIDIA GeForce 8800 Ultra 256MB GDDR3 SLI', '30" 3000:1 Wide Screen LCD', @OSXLeopard
72 EXEC dbo.InsertPC 'Fusion X2', 'Description here ...', 'AMD Athlon 64 FX 74 3 GHz', '6GB Dual Channel DDR2 800 SDRAM', '500GB SATA2 NCQ HDD', 'NVIDIA GeForce 8900 Ultra SLI 256MB GDDR3', '30" 3000:1 Wide Screen LCD', @WinVista
73 EXEC dbo.InsertPC 'Fusion Tiger 1', 'Description here ...', 'Intel Core2 Duo E6600 2.4 GHz 1066MHz', '2GB Dual Channel DDR2 800 SDRAM', '120GB SATA2 NCQ HDD', 'NVIDIA GeForce 8600 256MB GDDR3 SLI', '22" 3000:1 Wide Screen LCD', @OSXTiger
74 EXEC dbo.InsertPC 'Fusion Linux 1', 'Description here ...', 'AMD Athlon 64 X2 6000+ 3 GHz', '1GB Dual Channel DDR2 800 SDRAM', '120GB SATA2 NCQ HDD', 'NVIDIA GeForce 8600 256MB GDDR3', '22" 3000:1 Wide Screen LCD', @RedHat
75
76 SET @Log = @Log + 'Rows In PC: ' + CAST((SELECT COUNT(PCID) FROM PC) AS varchar(10)) + '<br />'
77
78 /* *** Start Insert Inventory *** */
79 SET @Log = @Log + 'Start Insert Inventory' + '<br />'
80
81 DECLARE @F1 int, @F2 int, @F3 int, @FX1 int, @FX2 int, @FT1 int, @FR1 int
82 SET @F1 = (SELECT PCID FROM PC WHERE Title = 'Fusion PC One')
83 SET @F2 = (SELECT PCID FROM PC WHERE Title = 'Fusion PC Two')
84 SET @F3 = (SELECT PCID FROM PC WHERE Title = 'Fusion PC Three')
85 SET @FX1 = (SELECT PCID FROM PC WHERE Title = 'Fusion X1')
86 SET @FX2 = (SELECT PCID FROM PC WHERE Title = 'Fusion X2')
87 SET @FT1 = (SELECT PCID FROM PC WHERE Title = 'Fusion Tiger 1')
88 SET @FR1 = (SELECT PCID FROM PC WHERE Title = 'Fusion Linux 1')
89
90 EXEC dbo.InsertInventory 10, @F1, 2.5, 'iCluster Fusion One'
91 EXEC dbo.InsertInventory 10, @F2, 2.5, 'iCluster Fusion Two'
92 EXEC dbo.InsertInventory 10, @F3, 2.5, 'iCluster Fusion Three'
93 EXEC dbo.InsertInventory 6, @FX1, 6, 'iCluster Fusion X1'
94 EXEC dbo.InsertInventory 6, @FX2, 6, 'iCluster Fusion X2'
95 EXEC dbo.InsertInventory 10, @FT1, 3, 'iCluster Fusion Tiger One'
96 EXEC dbo.InsertInventory 30, @FR1, 2, 'iCluster Fusion Linux One'
97
98 SET @Log = @Log + 'Rows In Inventory: ' + CAST((SELECT COUNT(InventoryID) FROM InventoryPC) AS varchar(10))
99
100 RETURN @Log
if i remove the last line then all is well. but i get "An SqlParameter with ParameterName '@Log' is not contained by this
SqlParameterCollection."
i have a huge stored procedure abt 500 lines..and i am calling this sp from an asp.net page...thn i got this error - error converting varchar to numeric - and am trying to debug...is there any way we can find out where the error is coming from...like aproxly which line number..etcor do i have to go through each line manually and see where i am doing the conversion....
thanks
for some odd reason our other programmer used varchar datatype to store dates. he claims it gives him more control. now i am trying to sort it based on date. so i create a procedure:
CREATE PROCEDURE GetAllWeekEnding
AS
Select convert(datetime, we) as we2 FROM tblArchive order by we2
GO
if i use the convert function in the procedure, i'll get an error msg when i run the code. this is the code i am using.
Dim MyConnection As SqlConnection = New SqlConnection(ConfigurationSettings.AppSettings("ConnectionStringSQL"))
Dim MyCommand As SqlCommand
MyCommand = New SqlCommand("GetAllWeekEnding", MyConnection)
MyCommand.CommandType = CommandType.StoredProcedure
MyConnection.Open()
Dim mydr As SqlDataReader = MyCommand.ExecuteReader()
While mydr.Read()
DropDownList1.Items.Add(mydr("we2"))
End While
mydr.Close()
MyConnection.Close()
the error message is: No accessible overloaded 'ListItemCollection.Add' can be called without a narrowing conversion
any ideas?
Hey gurus!
I've got a stored proc I call to obtain user info from a table. I've used the same format (but is slightly different) for another stored proc which works. But this one gives me "Error: System.Data.SqlClient.SqlException: Syntax error converting the varchar value 'SELECT User_UserName AS Username, User_LastLogin AS LastLogin, User_DateCreated AS Created FROM Community_Users WITH(nolock) WHERE (User_CommunityID = '' to a column of data type int. at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream) at System.Data.SqlClient.SqlCommand.ExecuteReader() at ASP.Recent_Logins_ascx.BindGrid() at ASP.Recent_Logins_ascx.Page_Load(Object Src, EventArgs e) :"
Why is this stored proc converting my SQLStatement to type int???
Here's my stored proc:
CREATE PROCEDURE Community_RecentLogins
(
@CommunityID int,
@SortField nvarchar(75)
)
AS
-- Create a variable @SQLStatement
DECLARE @SQLStatement varchar(255)
SELECT @SQLStatement = 'SELECT User_UserName AS Username, User_LastLogin AS LastLogin, User_DateCreated AS Created FROM Community_Users WITH(nolock) WHERE (User_CommunityID = ''' + @CommunityID + ''') ORDER BY ' + @SortField
-- Execute the SQL statement
EXEC(@SQLStatement)
And here's the (relevant) code in the ascx:
Sub BindGrid()
Dim conPortal as SqlConnection = New SqlConnection(CommunityGlobals.ConnectionString)
Dim cmdGet as SqlCommand = new SqlCommand("Community_RecentLogins", conPortal)
cmdGet.CommandType = CommandType.StoredProcedure
cmdGet.Parameters.Add("@CommunityID", CommunityGlobals.CommunityID)
If SortField = String.Empty Then
cmdGet.Parameters.Add("@SortField", "User_LastLogin DESC")
Else
cmdGet.Parameters.Add("@SortField", SortField & " DESC")
End If
conPortal.Open()
Dim dr as SqlDataReader = cmdGet.ExecuteReader()
LoginGrid.DataSource = dr
LoginGrid.DataBind()
conPortal.Close()
End Sub
Property SortField() As String
Get
Dim o As Object = ViewState("SortField")
If o Is Nothing Then
Return String.Empty
End If
Return CStr(o)
End Get
Set(ByVal Value As String)
ViewState("SortField") = Value
End Set
End Property
Any help is appreciated! Thanks in advance...
I am trying to have the aggregate value 1 replaced by 'only one' in my results.
My data looks like this:
Customer_id (int) ; Salesperson_id
1 ; 1
2 ; 1
3 ; 1
4 ; 2
5 ; 2
6 ; 3
...this is the solution I tried:
select
case when count(convert(varchar(15), customer_id)) = 1
Then 'only one customer'
else count(convert(varchar(15), kund_id)) end as no_of_customers,
salesperson_id
from customerdb
group by
salesperson_id
...and this is the result I was hoping for
no_of_customers ; salesperson_id
3 ; 1
2; 2
only one customer;3
...but SQL server only returns: Conversion failed when converting the varchar value ...
How should I convert a binary/hexadecimal value to Varchar
Thanks in Advance,
Jake
Haiii..
I save number in database as varchar, but i want to call it back and
convert as double/int. Can it be done
Hi guys,
My query was working fine until I added the red writing below.
Basically what I'm doing with this is getting the lowest open purchase order number (docnum) and its matching docduedate.
Now when I run it I get
Conversion failed when converting the varchar value 'po_num' to data type int.
Any ideas? Thanks!
SELECT t3.product ,
t7.itemname ,
t2.u_vlgx_plc,
t3.shorted ,
t4.onhand ,
MIN(
CASE
WHEN t8.linestatus = 'O'
THEN t9.docnum
ELSE NULL
END) po_num,
t10.docduedate
FROM
(SELECT t0.product product ,
SUM(
CASE
WHEN t0.qty_topick <> t0.qty_picked
THEN t0.qty_topick - t0.qty_picked
ELSE 0
END) shorted
FROM rbeacon.dbo.shipline2 t0
INNER JOIN rbeacon.dbo.shiphist t1
ON t0.packslip = t1.packslip
WHERE t1.date_upld = CONVERT(VARCHAR(10), GETDATE()-1, 101)
GROUP BY t0.product
) t3
INNER JOIN comparison.dbo.vlgxplc t2
ON t2.itemcode = t3.product COLLATE Latin1_General_CI_AS
LEFT JOIN
(SELECT t0.product AS product,
SUM(t0.quantity) AS onhand
FROM rbeacon.dbo.binlocat t0
GROUP BY t0.product
) t4
ON t3.product = t4.product
INNER JOIN wbau.dbo.oitm t5
ON t3.product = t5.itemcode COLLATE SQL_Latin1_General_CP850_CI_AS
LEFT JOIN wbau.dbo.ocrd t6
ON t5.cardcode = t6.cardcode
INNER JOIN wbau.dbo.oitm t7
ON t3.product = t7.itemcode COLLATE SQL_Latin1_General_CP850_CI_AS
LEFT JOIN wbau.dbo.por1 t8
ON t3.product = t8.itemcode COLLATE SQL_Latin1_General_CP850_CI_AS
LEFT JOIN wbau.dbo.opor t9
ON t8.docentry = t9.docentry
LEFT JOIN comparison.dbo.podatetest('po_num') t10
ON t10.docnum = t9.docnum
WHERE t3.shorted <> 0
GROUP BY t3.product ,
t7.itemname ,
t2.u_vlgx_plc,
t3.shorted ,
t4.onhand ,
t6.cardname,
t10.docduedate
ORDER BY t2.u_vlgx_plc,
t6.cardname ,
t3.product
Hello,
I have table1
ID PATH
1 .166.184.185.186.
Need get data from table2 using PATH data from table1
select * from table2 where id in (
select Substring(replace(path,'.',','),2,Len(path)-2) from table1)
ERROR:
Syntax error converting the varchar value '166,184,185,186' to a column of data type int.
Any solution please.
Regard,
M.Z.