Getting Data To A String From A SQL Server
Sep 8, 2006
Hey guys :)
First, im sorry for my English - I'm really trying! :D
I started using ASP.NET with Visual Web Developer for about 2 weeks ago, so im still very new to it. Before the I started with ASP.NET, I used classic ASP and now I really miss it :D
My problem is that I'm trying to make my own login-system. Creating users with detailsView to my SQLServer is very simple, but generating the login system is a bit harder I think. In the old days I would just make a recordset and the do something like If (rs.BOF or rs.EOF) then bla bla bla and the but the username or userID into a Session, but that seems to not be the way to do it in ASP.NET/Visual Basic?
How do I do?
Really just wanna know where to start :)
Thanks :)
/Hovgaard - Denmark
View 3 Replies
ADVERTISEMENT
Feb 12, 2008
Hello,
I am wondering what conversion rules apply, when a string, which contains a number, is saved to a SQL Server 2005 into a column of type decimal.
This is the code I€™m using (C++):
CString cValue = "0.75"
_variant_t vtFieldValue;
vtFieldValue = _variant_t(cValue)
pRecordSet->Fields->Item["MyColumn"]->Value = vtFieldValue;
"pRecordSet" is an ADO recordset. The database column "MyColumn" is of type "decimal(19,10)".
The most important question for me is, if the regional settings of the database server or the regional settings of the client PC are considered during the conversion from the string to the decimal value. For example in standard French regional settings the "." would not be recognized as decimal separator.
I am also wondering if the language of the database instance, in which this data is saved, is considered during this conversion or any other settings of this database instance.
So my general question is: Does anybody know exactly what rules apply during the above mentioned conversion?
Thank you for your help.
Regards,
Volker
View 2 Replies
View Related
May 21, 2008
String or binary data would be truncated. I get this error when entering data using sql server management studio express.
I am not running a sql to insert or update the table. This is through the EDI.
The data type is varchar(100). I enter one character and it errors on me. So this isn't a string being too long problem.
Any ideas?
View 2 Replies
View Related
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
May 7, 2015
I have system id information in table system_ids and productids and systemidinsformation has lot of data but I am looking two strings in tire data to pull into two separate columns. details below
Database versions :ms sql 2008/2012
tablename:system_id's
column:system id information
sample data from system_id_information column
########################################
<obj xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns="urn:vim25" versionId="5.5" xsi:type="ArrayOfHostSystemIdentificationInfo"><HostSystemIdentificationInfo xsi:type="HostSystemIdentificationInfo"><identifierValue> unknown</identifierValue><identifierType><label>Asset Tag</label><summary>Asset tag of the system</summary><key>AssetTag</key></identifierType>
[Code] .....
I am looking output of two columns, which are bolded
product_id snumber
654081-B21 MXQ43905SW
for serial number this is common
before string :HostSystemIdentificationInfo"><identifierValue>
and after string </identifierValue><identifierType><label>Service tag
and snumber is always between the before and after string and number of characters of snumber varies and entire data for a row also varies
View 9 Replies
View Related
Jan 11, 2008
I am trying to write a query in sql query analyzer that will extract a date that appears after the first comma in the string. An example of the data is below:
DAVE,12/31/03,17:18,PERIODENDP
AS,01/16/06,16:51,CUSTOMERS_VW
EM,11/09/07,15:08,ORDER_ENTRY_
I want to select the 8 characters after the first comma in the field, and then convert it to a date format.
I am a novice, so any help with the correct function and syntax would be greatly appreciated.
thanks,
Dave
View 8 Replies
View Related
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
Oct 16, 2007
I have a sp which works fine from my local database.
I move the procedure to a different database and modify it to access my data via linked server and I get the following:
provider "SQLNCLI" for linked server "Production" returned message "String data, length mismatch".
Msg 7421, Level 16, State 2, Line 27
Cannot fetch the rowset from OLE DB provider "SQLNCLI" for linked server "Production". .
Now I've seen references to this error message for ODBC, but as I said above, the same SP works fine if it goes against local data views. I am working with rather long concatenated strings using nvarchar(max) as the data type.
I've tried cast() on just about everything to no avail.
Anyone have any other ideas?
Thanks!
View 2 Replies
View Related
Feb 9, 2014
I have data like this
"entitlementwrapper" : [ {
"Type" : "Factory Warranty",
"Date_Type" : "Ship date",
"Status" : "Active",
"Start_Date" : "2012-12-21",
"End_Date" : "2014-01-19",
"Days_Left" : "116",
"Term" : "13",
"Description" : "Wty: HP HW Replacement Support",
"IsTrusted" : "Y",
"Transaction_ID" : "4644780453"
}
I want to get only data in double codes in using sql query.
View 9 Replies
View Related
Feb 18, 2004
Hi !
I'm trying to load data in sql server table with Bulk Copy Program (BCP).
I have the following errors.
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
SQLState = 22001, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]String data, right truncation
SQLState = S1000, NativeError = 0
Error = [Microsoft][ODBC SQL Server Driver]Unexpected EOF encountered in BCP data-file
In my File to load i have this :
Hello1¤Hello2¤17/02/2004
TOTO1¤TOTO2¤17/02/2004
TITI1¤TITI2¤17/02/2004
My definition table is :
create table tab1
(
TABLE varchar(20) null ,
PK varchar(50) null ,
DATE datetime null
)
go
I launch bcp with this command :
bcp.exe "BASETEST.dbo.tab1" in "c: ab1.out" -c -CRAW -t¤ -m100 -Smyserver -U -P -o"c: ab1.log"
The file "table1.out" is making with a C program.
Do you have an idea ?
Thanks.
View 1 Replies
View Related
Jun 16, 2015
I am trying to erase some erroneous bad data in my table. The description column has a lot of </div>oqwiroiweuru</a> weird data attached to it, i want to keep the data to the left of where the </div> erroneous data begins
update MyTable
set Description = LEFT(Description(CHARINDEX('<',Description)-1)) where myid = 1
that totally works.
update MyTable
set Description = LEFT(Description(CHARINDEX('<',Description)-1)) where myid >= 2
gives me a Invalid length parameter passed to the LEFT or SUBSTRING function. The statement has been terminated error.
View 2 Replies
View Related
Aug 21, 2015
I'm presented with a problem where I have a database table which must be migrated via a "custom tool", moving the data into a new table which has special character requirements that didn't exist in the source database. My data resides in an SQL Server 2008R2 instance.
I envision a one-time query which will loop through selected records and replace the offending characters with --, however I'm having trouble understanding how this works.
There are roughly 2500 records which meet the criteria of "contains bad characters", frequently containing multiple separate bad chars, and the table contains roughly 100000 rows.
Special Characters are defined as #%&*:<>?/{}|~ and ..
While the field is called "Filename" it isn't always so, it is a parent/child table where foldernames are also stored.
Example data:
Tablename = Items
ItemID Filename ListID
1 Badfile<2015>.docx 15
2 Goodfile.docx 15
3 MoreBad#.docx 15
4 Dog&Cat#17.pdf 15
5 John's "Special" Folder 16
The examples I'm finding are all oriented around SELECT statements, to change the output of what I see returned, however I'd rather just fix the entire column using an UPDATE. Initial testing using REPLACE fails because I don't always have a single character as the bad thing in a string.
In a better solution, I found an example using a User Defined Function to modify the output of a select, but I cannot use that UDF in an UPDATE.
My alternative is to learn enough C# to modify the "migration tool" to do this in-transit, but I know even less about C# than I do of SQL.
I gather I want to use @@ROWCOUNT to loop through the rows but I really can't put it all together in a cohesive way.
View 3 Replies
View Related
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
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
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
Oct 18, 2006
When I enter over 4000 chars in any ntext field in my SQL Server 2005 database (directly in the database and through the application) I get an error saying that the data could not be updated because string or binary data would be truncated.Has anyone ever seen this? I cannot figure out what is causing it, ntext should be able to hold a lot more data that this...
View 7 Replies
View Related
Oct 25, 2007
Hello,
i want to import data from an excel sheet into a database. While reading from the excel sheet OleDb automatically guesses the Datatype of each column. My Problem is the first A Column which contains ~240 Lines. 210 Lines are Numbers, the latter 30 do contain strings. When i use this code:
Code BlockDim sConn As String = "Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & conf_path_current & file_to_import & ";Extended Properties=""Excel 8.0;HDR=NO"""
Dim oConn As New OleDb.OleDbConnection(sConn)
Dim cmd1 As New System.Data.OleDb.OleDbCommand("Select * From [Table$]", oConn)
Dim rdr As OleDb.OleDbDataReader = cmd1.ExecuteReader
Do While rdr.Read()
Console.WriteLine(rdr.Item(0)) 'or rdr(0).ToString
Next
it will continue to read the stuff till the String-Lines are coming.
when using Item(0), it just crashes for trying to convert a DBNull to a String, when using rdr(0).ToString() it just gives me no value.
So my question is how to tell OleDB that i want that column to be completly read as String/Varchar?
Thanks for Reading
- Pierre from Berlin
[seems i got redirected into the wrong forum, please move into the correct one]
View 1 Replies
View Related
Mar 18, 2007
A data reader is using a connection manager to connect to an ODBC System DSN . A query in the SqlCommand property is provided. Data is being truncated in the only string column . The data type in data reader output-->external columns shows as Unicode string [DT_WSTR] Length 7.
The truncated output in a text file is the first 3 characters from left to right . Changing the column order has no effect.
A linked server was created in SQL Server Management Studio to test the ODBC System DSN using the following:
EXEC sp_addlinkedserver
@server = 'server_name',
@srvproduct = '',
@provider = 'MSDASQL',
@datasrc = 'odbc_dsn_name'
Data returned using "OPENQUERY" does not truncate the string column indicating that the ODBC Driver returns data as expected with sql 2005, but not with the Data Reader.
Any assistance would be appreciated.
Thanks,
View 3 Replies
View Related
Oct 4, 2015
I am studying indexes and keys. I have a table that has a fixed width of data to be loaded in the first column which is parsed in a view based on data types within the fixed width specifications.
Example column A:
(name phone house cost of house,zipcodecountystatecountry)
-a view will later split this large varchar string basedÂ
column b: is the source filename of the data load (varchar 256)
....
a. would there be a benefit of adding a clustered or nonclustered index (if so which/point in direction on why)
b. is there benefit of making one of these two columns a primary key (millions of records) or for adding a 3rd new column as a pk?
c. view: this parses the data in column a so it ends up looking more like "name phone house cost of house zipcode county state country" each having their own column.
-any pros/cons of adding indexes (if so which) to the view instead of the tables or both for once the data is parsed?
View 4 Replies
View Related
Feb 21, 2006
When using AquaData or JDBC (inet tds driver), when doing an insert using SqlServer 2005, I get error "String or binary data would be truncated" when the data is actually OK. There are no triggers, etc. that would confuse the situation. It works fine in SqlServer 2000.
The scenario is as follows:
Create table:
create table test3 (
name varchar (18) ,
tbname varchar (18)
)
Create and populate table:
create table maxtable (
tablename varchar (18) not null,
[...]
)
Try to insert into test3:
insert into test3 (name, tbname)
select i.name, o.name
from dbo.sysindexes i, sysobjects o, maxtable m
where i.indid > 0 and i.indid < 255
and i.id = o.id and i.indid = 1
and o.name = lower(m.tablename)
And I get the error "String or binary data would be truncated." The values being selected for i.name and o.name have maximum length of 18. There are other rows in sysindexes and sysobjects with longer values, but they are not being selected.
The error does not occur with SQL Server Management Studio, and does not occur using SqlServer 2000.
View 6 Replies
View Related
Aug 20, 2007
Hi!
Need help with this one:
I have a column with a string composed by several data. After using REPLACE several times, I get something like the data below, which has (in most of cases) a value and a date.
378 9/05
388 9/05
4/05
1/06 606
1/06 646
76 5/05
100 1/05
118 8/05
129 8/05
9/05 342
05/3 123
1/07
4/06 164
The problem is that I need to get each value alone (to separate columns), in example:
Value Date
378 09/2005
388 09/2005
0 04/2005
...
606 01/2006
and so on...
In addittion you can see that sometimes the Value come first or alone, and sometimes the Date come first or alone.
I will appreciate any good ideas,
Thanks in advance,
Aldo.
View 3 Replies
View Related
Jun 17, 2004
I need to get rid of the first char of data in a field f1 (in sql server)
I tried to use:
update myTable
set f1= substring(f1,1,f1.Len -1)
and get error "The column prefix 'f1' does not match with a table name or alias name used in the query.
Could anyone help? Thanks.
View 2 Replies
View Related
Mar 7, 2008
Hi there,
I'm faced with a bit of an issue within SSIS and I was wondering if anyone could help. I have data that looks like the following:
TABLE 1:::
CustomerID StaffID Percentage
A123 1234 1
A124 1245 1
A125 1234 0.7
A125 1443 0.3
B117 6653 1
B154 8899 1
C776 1443 0.2
C776 1337 0.6
C776 1245 0.2
I need to be able to summarize the data above and build up a string that should look like the following:
TABLE 2:::
CustomerID StringID
A123 1234||1
A124 1245||1
A125 1234||0.7||1443||0.3
B117 6653||1
B154 8899||1
C776 1443||0.2||1337||0.6||1245||0.2
As you can see in Table 1, if a CustomerID has only one StaffID associated to it, the "Percentage" Column's value is 1. In the cases where there is more than one StaffID associated to a particular CustomerID, the percentage column get's split up among the StaffIDs, however the sum of this split can never exceed 1 (100%) per CustomerID.
I need to know how to find the cases where there is more than one StaffID associated to a CustomerID, group the CustomerIDs, and build a string made up with the StaffID and Percentages as shown in Table 2.
Any help would be greatly appreciated, as I've struggled with this one for a while and have hit a brick wall.
Ciao,
Colin
View 6 Replies
View Related
Sep 18, 2006
I'm trying to import a student id in one database (nvarchar(7)) to int in another database. No sure whether I need ato use cast or convert. Anyone have some examples?
View 2 Replies
View Related
Sep 16, 2007
<p>
Hi everybody, I was hoping to get some advice something I can't quite get my head around. I have a SQL db which contains a table with ratings using the AJAX rating control.
When someone rates an object, I need to select the current rating and then use those numbers to;
- calculate the new average
- add new score to total score
- increment number of votes by one.
I thought this can be best achieved using the SELECT statement and then parsing the SELECT string. (is the string comma separated?)
using each array, i'd need to convert this into integers and then do the calculation. and re-upload the data to the ratings table (using the UPDATE statement).
Is this the best way of proceeding? I have tried initially to write the code using three sql statements. But that would mean to many requests from the server, right?
Below is the conde I have writting already.int myrating;
myrating = Rating1.CurrentRating;string getscore = "SELECT " +
"RatingScore" +"FROM Rating " +
"WHERE ItemID= '" + _ItemID+ "'";string getcount = "SELECT " +
"RatingCount" +"FROM Rating " +
"WHERE ItemID = '" + _ItemID + "'";string getaverage = "SELECT " +
"RatingAverage " +"FROM Rating " +"WHERE ItemID = '" + _ItemID + "'";
int _ratingscore;int _newscore; _ratingscore = int.Parse(getscore);
_newscore = _ratingscore + myrating; //add new rating score to old scoreint _ratingcount;
int _newcount;_ratingcount = int.Parse(getcount);
_newcount = _ratingcount + 1; //increase count by 1int _ratingaverage;
int _newaverage;_ratingaverage = int.Parse(getaverage);
_newaverage = _newscore / _newcount; //calculate new average rating
otherwise
otherwise would i be best off to do the following?...
string[] dbRatings = SQLstring.Split(',');
??
Any help would be appreciated.
Many thanks in advance.
Phil
</p>
View 2 Replies
View Related
Jan 21, 2001
I am writing a stored procedure that generates a computername for new workstations. Our naming standards, based on terminal ID's, are as follows:
'R' + X + Y + ZZZ, where 'R' is the letter 'R', X is a hex digit indication a locatoin, Y is a hex digit indicating business line, and ZZZ is a hexadecimal sequential number assigned within each location/busines line, for up to 4096. (This isn't a perfect naming standard, but is an improvement over the one that preceded it and is a requirement for our legacy applications).
I can do everything fine, and calculate the sequential number using an int. Once I have the new number, I am trying to convert the integer to a varbinary, then convert that to a string, then use RIGHT to get the rightmost three characters of the hex string.
set @SEQNUM = 4095
set @HEXSEQNUM = CONVERT(varbinary ,@SEQNUM)
PRINT CONVERT(varbinary ,@SEQNUM) -- displays 0x00000FFE etc.
PRINT 'HEXSEQNUM ' + @HEXSEQNUM -- displays 'HEXSEQNUM'
Any ideas? I am very new to TSQL...
View 1 Replies
View Related
Feb 11, 2004
I have a web site that allows user to enter large strings into a database (comments, etc). What is the best way to do that? Right now I have them limited to 25 characters and the data type is varchar. Is there a better way?
Thanks!
View 2 Replies
View Related
Jul 27, 2006
Hi,
I want to read xml from a string and save it in SQL. Can anyone help me plz.
Regards,
View 7 Replies
View Related
Mar 14, 2007
Hi all
I have a column with the following data
Change Order 1887:MODIFIED EXPIRE_DATE TO 2007-10-26 00:00:00.000
Now i need to get only 2007-10-26 00:00:00.000 from the above string data.
Thanks
View 1 Replies
View Related
Sep 13, 2007
Hi,
I'm not a programmer by trade, so please be gentle with me! I am trying to add or insert data to the beginning of an existing string of data. The string is in one field in a table, with various options seperated by [ ].
An example of the current data is:
[Eye Color:TEST][Hair Color:TEST]
and I would like to add the data [# Persons:1] to the beginning of the string so it looks like this:
[# Persons:1][Eye Color:TEST][Hair Color:TEST]
Can anyone help me with the best way to do this?
View 6 Replies
View Related
Dec 6, 2007
I am trying to extract the numbers only from a string field any help would be great.
field is phone number (123)123-2584 and need to display 1231232584
thanks
View 1 Replies
View Related
Oct 17, 2006
Hi,
I am trying to execute one huge select statement from my application using ODBC, but I am getting this error:
[Microsoft][ODBC SQL Server Driver]String data, right truncation.
the select statement is bigger than 2000 characters, but I don't have any long string column, just strings and numbers. How can I work around this?
Is there any max size? Because if I use less than 1000 characters, it works fine.
I am using Centura/SqlWindows, Native Sql Server ODBC and SqlServer 2005. unfortunately, I can not use Ole from my application.
cheers,
Alessandro Camargo
View 2 Replies
View Related
Sep 8, 2006
Hi, Folks,
Can we have a table listing the BEST solution of DB Connection string
and getting data for each version of framework and databses?
use Oledb? SQlConnection? ODBC? what's the BEST solution?
Oracle | MS SQL
==============================================
Framework 1.0 ? | ?
Framework 1.1 ? | ?
Framework 2.0 ? | ?
thanks alot
Please have some code for the ?
View 1 Replies
View Related
