String To Smallmoney - Sql2000

Jul 18, 2003

using SQL2000, table has a field defined as 'smallmoney'. This is being populated from a text file that has two digits after the decimal -> 56.50

I was trying to use cast and convert going in but no luck, as near as I can determine they only work on output.

So the question is : how can I get string data into smallmoney. Otherwise I have to redefined the field as a varchar or decimal

Comments?

Thanks
RK

View 14 Replies


ADVERTISEMENT

Page 2 - String To Smallmoney - Sql2000

Jul 21, 2003

the placeholder approach works nicely with an insert statement and I've tried it with an update statement but keep getting an error. This is the current code:

Var1 = strArray(1)
Var2 = strArray(2)
Var3 = (strArray(3)


Cmd.CommandText = "UPDATE WBS.dbo.pludec38 SET PLU = ?, SET Dscrpt1 = ?, SET Price = ?"

Cmd.Parameters.Refresh
Cmd.Parameters(0) = Var1
Cmd.Parameters(1) = Var2
Cmd.Parameters(2) = Var3

Cmd.Execute

The error occurs at or near the refresh stmt. What am I doing that is causing this? And what is the difference between this and the insert. The insert uses the parameters block without choking.
Thanks
RK

View 11 Replies View Related

How Do You Test For Partial String In SQL2000?

Apr 23, 2007

Is it possible using Sql2000 to compare the first four characters of a parameter?
WHERE (tblLinkInfo.CG_ID = @BCG_ID) AND ( 'first 4 characters' of Location_Zip.Zip = 'first 4 characters' of @ZipCodeIn)

View 2 Replies View Related

SQL2000-SQL2005 Connection String

Jun 4, 2008

Hi all,
I'm new to SQL and I am trying to move from SQL 2000 to SQL 2005 on another server. When I change my connection string from the old IP to the new one(with SQL 2005) my web app crashes. Here is the command string I need to change:

<add key="ConnStr" value="data source=myoldIP;initial catalog=gt-test;uid=myuserid;pwd=mypassword;" />

I have copied the old database exactly the way it was to the new one. I need to change myoldIP to my new one but when I do it crashes... Any ideas?
Thanks in advance for your help
-Will

View 20 Replies View Related

Decimal/smallmoney

Apr 27, 2007

Hello all,

I have two values (1.0 and 0.50 that need to be saved into SQL Server 2005 as numeric values. My issue is that when I try to save 0.5 to the table it is rounded up to 1.0. I checked the Stored Procedures and it looks good there. I tried the datatypes decimal (2, 1) and small money but both cause the value to round. Can anyone help me save the value as 0.5 to the table?

Thanks in advance!

View 3 Replies View Related

Smallmoney With 2 Digits

Jan 2, 2008

I have a table for products and the price field is smallmoney data type. When I view the data stored it shows it with millionths. How do I get it to store only hundredths?

View 2 Replies View Related

Diff Between Money And Smallmoney

Mar 15, 2008

What is the difference between money and smallmoney, and is there a way to format these?

View 3 Replies View Related

Help! SMALLMONEY:Too Many Decimal Places

Aug 12, 2000

Hello all...

I am new to SQL 7, and am having trouble with the money and smallmoney data-types. When I pull data from these columns, it has four decimal places, and I need only two (four is screwing the perl code up). What do I do now?

All help much appreciated!

View 2 Replies View Related

When To Use Money, SmallMoney, Decimal

Aug 27, 2004

Could someone explain to me what the best practices are for using these three data types? (i.e. when to use them). I thought this would be a simple answer to find, but after looking through books on line, and this forum, I have still not found the answer. Can anyone help?

View 1 Replies View Related

Smallmoney Data Type

Mar 25, 2007

whenever i add values in my price column, it automatically put 4 decimal places. how will i limit to 2 decimal places of this smallmoney datatype?

View 4 Replies View Related

Why Does The Sum Of A Smallmoney Have Money Datatype?

Jul 3, 2007

Using the following script...

create table a

( policy_id int not null,

amount smallmoney not null);



insert a values (1, 100.0);



select policy_id, sum(amount) sum_amount

into b

from a

group by policy_id;



Why is the datatype, of column sum_amount in table b, money rather than smallmoney?

View 1 Replies View Related

Store Procedure Problem W/smallmoney

Feb 15, 2004

Hello,
I am having a problem with a stored procedure and inserting money value into a smallmoney field. I have tried a few different lines but still get errors. This is a sample of the code below and the problem is with the MenuCost and converting a string to smallmoney. I am not having any luck with. Thanks inadvance for any advice.

Gary


Dim MenuName As String = txtName.Text
Dim MenuDescription As String = txtDescription.Text
Dim MenuCatId As String = DropDownList2.DataValueField
Dim MenuTypeId As String = DropDownList1.DataValueField
Dim MenuCost As String = txtCost.Text

Dim ConnectionString As String = fdfadsfdsfasdfsdfsdf
Dim CommandText3 As String = "ins_MenuItem"

Dim myConnection3 As New SqlConnection(ConnectionString)
Dim myCommand3 As New SqlCommand(CommandText3, myConnection3)
Dim workParam As New SqlParameter()

myCommand3.CommandType = CommandType.StoredProcedure

With myCommand3.Parameters
.Add("@MenuName", SqlDbType.Varchar, 150, MenuName)

.Add("@MenuDescription", SqlDbType.Text, MenuDescription)

.Add("@MenuCatId", SqlDbType.Int, MenuCatId)

.Add("@MenuTypeId", SqlDbType.Int, MenuTypeId)

'.Add("@MenuCost", SqlDbType.SmallMoney,4,System.Data.SqlTypes.SqlMoney.Parse(MenuCost))
'.Add("@MenuCost", SqlDbType.SmallMoney,4, Convert(MenuCost AS Varchar))

'.Add("@Amount", SqlDbType.SmallMoney,4,System.Data.SqlTypes.SqlMoney.Parse(Amount))
'.Add("@MenuCost", SqlDbType.SmallMoney,4,System.Data.SqlTypes.SqlMoney.Parse(MenuCost))


End With

myConnection3.Open()

View 2 Replies View Related

Problems Converting Varchar To Smallmoney

Oct 11, 2006

Hi


My ticket engine stores values in varchar. The sql db-field that
corresponds was created as smallmoney.


The below statement works for conversion of "leavedays" if the given
value is entered without any decimal places (E.G. 4)


As soon as a user enters a value that includes decimal places (E.G.
4.5) the conversion will not work. In this case the value 4.5 is
rounded to 5.


What do i have to do to convert the value as it is entered by the user?


Thanks in advance


t.


Statement:


INSERT INTO leavereq (mitarbeiter, startdate, enddate, leavedays,
remainingdays, approvedby, approvedon) SELECT {0} , convert(datetime,
{1}) , convert(datetime, {2}), convert(numeric, {3}), convert(numeric,
{4}),{5}, getdate()


DDL for concerned database:


CREATE TABLE [dbo].[leavereq] (
[mitarbeiter] char(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[startdate] datetime NULL,
[enddate] datetime NULL,
[leavedays] smallmoney NULL,
[remainingdays] smallmoney NULL,
[approvedon] datetime NULL,
[approvedby] char(50) COLLATE SQL_Latin1_General_CP1_CI_AS NULL
)
ON [PRIMARY]
GO

View 3 Replies View Related

Inserting Data Into Database Field SmallMoney

Jun 5, 2007

 what i understand if if the data field is integer or money, not string, then i need to do a convert(datatype, value) in the insert but how come its still not working INSERT INTO [Product] ([Title], [Description], [Processor], [Motherboard], [Chipset], [RAM], [HDD], [OpticalDrive], [Graphics], [Sound], [Speakers], [LCD], [Keyboard], [Mouse], [Chassis], [PSU], [Price]) VALUES (@Title, @Description, @Processor, @Motherboard, @Chipset, @RAM, @HDD, @OpticalDrive, @Graphics, @Sound, @Speakers, @LCD, @Keyboard, @Mouse, @Chassis, @PSU, convert(smallmoney, @Price))  

View 1 Replies View Related

Sql2000 && Sql2005, Want Localhost To Use Sql2000

Sep 17, 2006

 i have sql2000 & sql2005 on the same machine. I am unable to register my localhost in sql2000, get an access denied error. How can I make my localhost use sql2000 database?

View 1 Replies View Related

Migrate SQL2000 To SQL2000

May 13, 2008



i am in the process of Migrating SQL 2000 to my new SQL2000 server i want to know the what would the best way for me to migrate one SQL server to another SQL server on the same network and rename the new server to the old server and bring it up for use in our ecommerce website.

View 10 Replies View Related

SQL Server 2008 :: Search Each And Every String In Comma Delimited String Input (AND Condition)

Mar 10, 2015

I have a scenario where in I need to use a comma delimited string as input. And search the tables with each and every string in the comma delimited string.

Example:
DECLARE @StrInput NVARCHAR(2000) = '.NET,Java, Python'

SELECT * FROM TABLE WHERE titleName = '.NET' AND titleName='java' AND titleName = 'Python'

As shown in the example above I need to take the comma delimited string as input and search each individual string like in the select statement.

View 3 Replies View Related

SQL Server 2012 :: Finding Longest String Within A String Field

Mar 20, 2014

We have some URLs within a bulk block of text some of which are very long. I need to identify rows where such urls exceed say 100 characters in length in amongst other text.So the rule would be return a record if within the string there is a string (without spaces) longer than 100 characters.

View 9 Replies View Related

SQL Server 2014 :: Find String With Spaces And Replace Them With Other String

Sep 8, 2015

I have following query which return me SP/Views and Functions script using:

select DEFINITION FROM .SYS.SQL_MODULESNow, the result looks like
Create proc
create procedure
create proc
create view
create function

I need its result as:

Alter Procedure
Alter Procedure
Alter Procedure
Alter View
Alter Function

I used following

select replace(replace(replace(DEFINITION,'CREATE PROCEDURE','Alter Procedure'), 'create proc','Alter Procedure'),'create view','Alter View') FROM .SYS.SQL_MODULESto but it is checking fixed space like create<space>proc, how can i check if there are two or more spaces in between create view or create proc or create function, it should replace as i want?

View 5 Replies View Related

String Or Binary Data Would Be Truncated. (only For 1700 Character String?)

Nov 2, 2006

I am trying to insert a row into a table of Microsoft SQL Server 2000.

There are various columns.















[SNO] [numeric](3, 0) NOT NULL ,
[DATT] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[DATTA] [char] (3000) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,
[CODECS] [char] (32) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL
,

The [DATTA] column is causing a problem. Even if I am trying to put only 1700 character string into [DATTA], the java code throws the following exception:-



StaleConnecti A CONM7007I: Mapping the following
SQLException, with ErrorCode 0 and SQLState 08S01, to a
StaleConnectionException: java.sql.SQLException: [Microsoft][SQLServer 2000
Driver for JDBC]Connection reset

      at
com.microsoft.jdbc.base.BaseExceptions.createException(Unknown Source)


Why is it throwing an exception even though the sum-total of this row doesn't exceed 8000 characters?

Can anyone please tell me what's wrong?

View 6 Replies View Related

Help: About Ms Sql Query, How Can I Check If A Part String Exists In A String?

May 22, 2007

Hello to all,
I have a problem with ms sql query. I hope that somebody can help me. 
i have a table "Relationships". There are two Fields (IDMember und RelationshipIDs) in this table. IDMember is the Owner ID (type: integer) und RelationshipIDs saves all partners of this Owner ( type: varchar(1000)).  Example Datas for Table Relationships:                               IDMember     Relationships              .
                                                                                                                3387            (2345, 2388,4567,....)
                                                                                                                4567           (8990, 7865, 3387...)
i wirte a query to check if there is Relationship between two members.
Query: 
Declare @IDM int; Declare @IDO int; Set @IDM = 3387, @IDO = 4567;
select *
from Relationship where (IDMember = @IDM) and ( cast(@ID0 as char(100)) in
(select Relationship .[RelationshipIDs] from Relationship where IDMember = @IDM))
 
But I get nothing by this query.
Can Someone tell me where is the problem? Thanks
 
Best Regards
Pinsha

View 9 Replies View Related

Adding String To Database, But Name Of String Is Added, Not Data

Mar 12, 2008

Hello, I am tring to add a string my database.  Info is added, but it is the name of the string, not the data contained within.  What am I doing wrong?  The text "Company" and "currentUserID" is showing up in my database, but I need the info contained within the string.  All help is appreciated!
 
 
Imports System.Data
Imports System.Data.Common
Imports System.Data.SqlClientPartial Class _DefaultInherits System.Web.UI.Page
 
Protected Sub CreateUserWizard1_CreatedUser(ByVal sender As Object, ByVal e As System.EventArgs) Handles CreateUserWizard1.CreatedUser
'Database ConnectionDim con As New SqlConnection("Data Source = .SQLExpress;integrated security=true;attachdbfilename=|DataDirectory|ASPNETDB.mdf;user instance=true")
'First Command DataDim Company As String = ((CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("Company"), TextBox)).Text)
Dim insertSQL1 As StringDim currentUserID As String = ((CType(CreateUserWizard1.CreateUserStep.ContentTemplateContainer.FindControl("UserName"), TextBox)).Text)
insertSQL1 = "INSERT INTO Company (CompanyName, UserID) VALUES ('Company', 'currentUserID')"Dim cmd1 As New SqlCommand(insertSQL1, con)
'2nd Command Data
Dim selectSQL As String
selectSQL = "SELECT companyKey FROM Company WHERE UserID = 'currentUserID'"Dim cmd2 As New SqlCommand(selectSQL, con)
Dim reader As SqlDataReader
'3rd Command Data
Dim insertSQL2 As String
insertSQL2 = "INSERT INTO Company_Membership (CompanyKey, UserID) VALUES ('CompanyKey', 'currentUserID')"Dim cmd3 As New SqlCommand(insertSQL2, con)
'First CommandDim added As Integer = 0
Try
con.Open()
added = cmd1.ExecuteNonQuery()
lblResults.Text = added.ToString() & " records inserted."Catch err As Exception
lblResults.Text = "Error inserting record."
lblResults.Text &= err.Message
Finally
con.Close()
End Try
'2nd Command
Try
con.Open()
reader = cmd2.ExecuteReader()Do While reader.Read()
Dim CompanyKey = reader("CompanyKey").ToString()
Loop
reader.Close()Catch err As Exception
lbl1Results.Text = "Error selecting record."
lbl1Results.Text &= err.Message
Finally
con.Close()
End Try
'3rd Command
Try
con.Open()
added = cmd3.ExecuteNonQuery()
lbl2Results.Text = added.ToString() & " records inserted."Catch err As Exception
lbl2Results.Text = "Error inserting record."
lbl2Results.Text &= err.Message
Finally
con.Close()End Try
 
 
 End Sub
End Class

View 3 Replies View Related

SQL 2012 :: Eliminate Characters From A String Based On Another String?

Dec 17, 2014

I have a string 'ACDIPFJZ'

In my table one of the column has data like

PFAG
ABCDEFHJMPUYZ
KML
JC
RPF

My requirement is that if the string in the column has any of the characters from 'ACDIPFJZ' , those characters have to be retained and the rest of the characters have to be removed.

My output should be:

PFAG -- PFA (G Eliminated)
ABCDEFHJMPUYZ -- ACDPFJZ (B,E,H,M,U,Y Eliminated)
KML -- No data
JC -- JC
RPF -- PF (R Eliminated)

View 2 Replies View Related

SQL 2012 :: Picking Number String Out Of Text String

Jul 14, 2015

I have a text field which has entries of variable length of the form:

"house:app.apx&resultid=1234,clientip"
or
"tost:app.apx&resultid=123,clientip"
or
"airplane:app.apx&resultid=123489,clientip"

I'm trying to pick out the numbers between resultid='...',clientip no matter what the rest of the string looks like. So in this example it would be the numbers:

1234
123
12389

the part of the string of the form resultid='...',clientip always stays the same except the length of the number can vary.

View 5 Replies View Related

Need Help With String Manipulation - Splitting 1 String Into Multiple Columns

Sep 11, 2006

Hello All,

I'm a non-programmer and an SQL newbie. I'm trying to create a printer usage report using LogParser and SQL database. I managed to export data from the print server's event log into a table in an SQL2005 database.

There are 3 main columns in the table (PrintJob) - Server (the print server name), TimeWritten (timestamp of each print job), String (eventlog message containing all the info I need). My problem is I need to split the String column which is a varchar(255) delimited by | (pipe). Example:

2|Microsoft Word - ราย�ารรับ.doc|Sukanlaya|HMb1_SD_LJ2420|IP_192.10.1.53|82720|1

The first value is the job number, which I don't need. The second value is the printed document name. The third value is the owner of the printed document. The fourth value is the printer name. The fifth value is the printer port, which I don't need. The sixth value is the size in bytes of the printed document, which I don't need. The seventh value is the number of page(s) printed.

How I can copy data in this table (PrintJob) into another table (PrinterUsage) and split the String column into 4 columns (Document, Owner, Printer, Pages) along with the Server and TimeWritten columns in the destination table?

In Excel, I would use combination of FIND(text_to_be_found, within_text, start_num) and MID(text, start_num, num_char). But CHARINDEX() in T-SQL only starts from the beginning of the string, right? I've been looking at some of the user-defind-function's and I can't find anything like Excel's FIND().

Or if anyone can think of a better "native" way to do this in T-SQL, I've be very grateful for the help or suggestion.

Thanks a bunch in advance,

Chutikorn

View 2 Replies View Related

Transact SQL :: Search And Return String After Searched String

Sep 1, 2015

Is there way to search for the particular string and return the string after that searched string

SalesID
Rejection reason
21812

[code]....

The timeout period elapsed hence disqualified

View 3 Replies View Related

ASP.NET 2.0 And SQL2000

Sep 5, 2006

Hi,I have made a web application using SQL Server 2005
Express with a few admin pages that require login. I used the SQL
Server Managment Studio Express to setup users and groups and it works
fine. But the web server has SQL Server 2000 and all I get is a
user id and password to access the database. It seems I cannot use the
Login control I put on my "login.aspx" page becuase it uses the
integrated authentication which chokes on 2000. I am afraid I wonlt be
able to use some of the 2.0 features such as profiles, ... Is
this the way 2.0 works with SQL Server 2000? Should I ditch the ISP if
they only give me a single access to database with no provisions to
create users. (I was told I had to create a "Users" table and manually
assign ids and passwords and that I can only use the user id and
password they supplied to setup the connection string in the script.)I appreciate your help. 

View 2 Replies View Related

SP1 On SQL2000

Mar 8, 2001

Does anyone know when SP1 will be available for SQL2000??

Thanks,
Steve Bajada

View 1 Replies View Related

SQL2000 On NT4

Oct 23, 2000

Are there any advantages/disadvantages of running SQL2K on NT4 as opposed to running on WIN2K?

View 1 Replies View Related

Sql2000

Jan 7, 2008

pls tell me abt log shipping in sql2000

View 1 Replies View Related

Find In String And Return Part Of String

Mar 21, 2007

I am trying to find a way to find a certian character in a string and then select everything after that character.

for example i would look for the position of the underscore and then need to return everthing after it so in this case

yes_no

i would return no

View 7 Replies View Related

Input String -&> Table -&> Output String?

Jul 13, 2006

I have a nasty situation in SQL Server 7.0. I have a table, in whichone column contains a string-delimited list of IDs pointing to anothertable, called "Ratings" (Ratings is small, containing less than tenvalues, but is subject to change.) For example:[ratingID/descr]1/Bronze2/Silver3/Gold4/PlatinumWhen I record rows in my table, they look something like this:[uniqueid/ratingIDs/etc...]1/2, 4/...2/null/...3/1, 2, 3/...My dilemma is that I can't efficiently read rows in my table, match thestring of ratingIDs with the values in the Ratings table, and returnthat in a reasonable fashion to my jsp. My current stored proceduredoes the following:1) Query my table with the specified criteria, returning ratingIDs as acolumn2) Split the tokens in ratingIDs into a table3) Join this small table with the Ratings table4) Use a CURSOR to iterate through the rows and append it to a string5) Return the string.My query then returns...1/"Silver, Platinum"2/""3/"Bronze, Silver, Gold"And is easy to output.This is super SLOW! Queries on ~100 rows that took <1 sec now take 12secs. Should I:a) Create a junction table to store the IDs initially (I didn't thinkthis would be necessary because the Ratings table has so few values)b) Create a stored procedure that does a "SELECT * FROM Ratings," putthe ratings in a hashtable/map, and match the values up in Java, sinceJava is better for string manipulation?c) Search for alternate SQL syntax, although I don't believe there isanything useful for this problem pre-SQL Server 2005.Thanks!Adam

View 2 Replies View Related

Transact SQL :: How To Get String Array In String Variable

Jul 28, 2015

I have a string variable and following data.

Declare @ServiceID varchar(200)
set @ServiceID='change chock','change starter','wiring for lights','servicing'

when I assign values in @ServiceID  in the above manner then it shows error. How to get string array in @ServiceID variable so that i can go ahead.

View 8 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved