This seems so simple but I can't find any examples to do it..
I have the following in a text file "Lastname, Firstname MI" and I want to select each part separetly.. What would be the SELECT statement to get the Lastname up to the comma, then the Firstname after the comma, then the MI after the last space..
Thanks for the help, and any reference to learn more.
I have a small problem that I can't figure out. Here's my table:
----------------------------------------
Members
----------------------------------------
ID | Name | Image
----------------------------------------
When a user signs up to become a member, they can upload an image, which will be stored under Image. It will also be stored in a folder. For example, if the user with the ID of 14 uploads an image, it will be stored in 'livefiles/Members/14/image.jpg'. If the user does not upload an image, NULL is stored under Image, and the user has a default image, which is stored under 'livefiles/MembersDefault/1/image.jpg'.
What I want to do is return the path of where the image is in the folders. Basically...
If Image is not null
SELECT 'livefiles/Members/' + ID + '/' + Image AS ImagePath
If Image is null
SELECT 'livefiles/MembersDefault/1/image.jpg'
I'd rather not do this with an IF statement, I'm doing a lot of other SELECTS, and I don't want to have to copy all that code. Here's what I tried...
COALESCE (NULLIF('livefiles/Members/' + CONVERT(varchar(20), ID) + '/' + Image, 'livefiles/Members/' + ID + '/'), 'livefiles/MembersDefault/1/image.jpg') AS ImagePath
but that gave me an error saying:
Conversion failed when converting the varchar value 'livefiles/Members/' to data type int.
Hi everyone,
You have helped me resolved my previous problem with the LIKE statement, and now I'm running into this TEXT STRING problem that I desperately need your help and guidance again.
The following is the set of various descriptions in a PRODUCT_DESC field. I need to be able to calculate the Squared Meter of these products. As you can see, I need to be able to extract the part in the middle (like 2x60YD, etc.) for each record and perform some sort of calculation and conversion. The problem is that I can't find a way to do this effectively. Can someone please help me? Thanks.
PG21..181 MASKING TAPE 2X60YD 7.3MIL IPG PREMIUM
CH PG21..181 MASKING TAPE 2X60YD 7.3MIL IPG PREMIUM
PG5...130 MASKING TAPE 2X60YD 6.4MIL IPG PREMIUM
PG21..179 MASKING TAPE 24MMX55 7.3MIL IPG PREMIUM
PG21..179 MASKING TAPE 24MMX55 7.3MIL IPG PREMIUM
PG21..181 MASKING TAPE 2X60YD 7.3MIL IPG PREMIUM
PG5...130 MASKING TAPE 2X60YD 6.4MIL IPG PREMIUM
PG21..179 MASKING TAPE 24MMX55 7.3MIL IPG PREMIUM
PG21..181 MASKING TAPE 2X60YD 7.3MIL IPG PREMIUM
I have a whole bunch of bit fields in an SQL data base, which makes it a little messy to report on.
I thought a nice idea would be to assigne a text string/null value to each bit field and concatenate all of them into a result.
This is the basic logic goes soemthing like this:
select case new_accountant = 1 then 'acct/' end +
case new_advisor = 1 then 'adv/' end +
case new_attorney = 1 then 'atty/' end as String
from new_database
The output would be
Null, acct/, adv/, atty, acct/adv/, acct/atty/... acct/adv/atty/
So far, nothing I have tried has worked.
Any ideas?
I have a table that looks like the example below. I need to return the tindex and the entire description on one row. Any clues? I'm drawing a blank.
thanks for you help
tindex tdline description
1234 1 Talk to Mr. Cartwright about
1234 2 new issues with patent law. Conferece
1234 3 call to discuss payment of past bills.
I need to see
1234,Talk to Mr. Cartwright about new issues with patent law. Conferece call to discuss payment of past bills.
Hi everyone.
Is it possible to put in a string value as one of the results? I'm trying to produce a string in the data table is the value is null so I want to do something like:
iif(somevalue is nothing, 'Other', somevalue)
Thank you in advance.
I am trying to develop a web site. I have a local ms sql database on my machine.
I am trying to connect to a ms Sql database on a goDaddy server from the application.
I am trying to understand the connection string and its total properties.
here is what I think should be in my web.config file
<
add name="Personal" connectionString="Server=whsql-v12.prod.mesa1.secureserver.net;
Database=DB_XX10;
User ID=myID;
Password=myypassword;
Trusted_Connection=False" providerName="System.Data.SqlClient"
/>
<remove name="LocalSqlServer"/>
can someone please tell me where I am going wrong, Thanks for your help.....
I am using query strings to pass data from web form to web form and I have two questions. First if i use a asp:sqldatasouce to fill up a grid view and I have my select command set to a paramater that get whatever is in the query string it will not work because whatever is in the quers string gets a " ' " put in front and in the back of it. So if the query string was 5 whene it does the sql statement it sets my paramater = '5' not just 5 so its wont work. How can I fix this using the asp:sql datasource my aspx code looks like
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ConnectionString="<%$ ConnectionStrings:Rental PropertiesConnectionString %>"
SelectCommand="SELECT * FROM [APARTMENTS] WHERE ([PROPERITY_ID] = @PROPERITY_ID)">
<SelectParameters>
<asp:QueryStringParameter Name="PROPERITY_ID" QueryStringField="key" Type="Int32" />
</SelectParameters>
</asp:SqlDataSource>
Also since i have not been able to get around this so i have been wrting code in vb.net to attact a dataset to a grid view to populate it based on the query string i would do the following in vb.net to get ride of the ' in front and behind the query string
Dim y as string = "'" // " ' "
key = Request.QueryString("key").trim(y.tochararray)
But now i am doing another project in C# and I have re-written the above code in C# it will run but it will not take the " ' " out form infront or behind key. How does this need to be changed up?
string y = "'";
key = Request.QueryString["key"].trim(y.tochararray());
How can i make a sum (concatenation) of strings of one column in a table.
for example i have a table like this
field1 field2
1 abc
1 bcd
2 sdf
2 sdd
I want to get these strings added group by field 1
Thanks
I am submitting a telephone number into a table. I have 3 boxes for the telephone number. Telephone1,Telephone2,Telephone3. I need to insert the values of the 3 text boxes into a column called phone in my table.
so like
InsertCommand="INSERT INTO customer_mod (phone) Values (@Telephone_1)
<asp:formparameter name="Telephone_1" formfield="Telephone1+Telephone2+Telephone3" />
I don't think that is gonna work, so can you please help me make that code work?
I developed an application and deployed it onto a hosted provider. I use SQL 2005 Express for development SQL 2000 for production. Both systems work.
I backed up my production database and wish to use it for my development testing. I restored it as a SQL 2000 database on my development machine and changed my connection strings to point to it. I added it as a new Data Connection in Database Explorer and can see all of the data through it, but I see that all the tables now have the owner name in parenthesis as part of the table name.
My application now fails trying to find any of the tables. It works if I include the owner name as part of the SELECT statement. So much for data separation.
I don't know where to start. Is this an issue between SQL 2005 Express and SQL 2000, or is it an issue between SQL and ASP.NET, or is it a hosting provider issue, or what?????
Hey guys, don't know if this is in the right place but here goes.
I'd like some advice on optimizing the way I write my code. Here's the scenario. I have a select string for 3 different tables. One for the main gridview, and two others that are binded to dropdownlists.
So I created a Sqldatasource1,2,3 and had to create the connection strings for each one.
ex. SqlDataSource1.ConnectionString = "blah", SqlDataSource2.ConnectionString = "blah", SqlDataSource3.ConnectionString = "blah"the thing is, the connection string itself is the same. Is there a way to consolidate that? So that when I use a select/update/insert command for each sqldatasource i dont have to have another connection string? Also, having multiple connectionstrings, does that slow down the performance?
In an ASP.NET app, where is the preferred place (or your preferred place, I should say) to store any SQL Statements (updates, selects, stored procedure calls, etc.) you have? I have been using a key/value pair in AppSettings in my web.config but just feel like that isn't the best way. What say you?
View 4 Replies View RelatedHi,
Im struggling to work out the connection string to my ms sql database.
My website is being hosted with freezone.co.uk and I have a ms sql database called "aspnetdb". I usually log into the database with a username but no password. I don't no what other information is needed to create my connection string?
Thanks, Mark Lawton
I've got a GridView, a datasource, 2 textboxes and a button. If I choose to bind the GridView to the datasource the correct data is shown. But if I modify the datasource and chooses the TextBoxes as Parameter Controls it doesn't work out as I expected.
Can I please have a step-by-step response which will work? And expecially if one if the TextBoxes eguals nothing/null. Because this is very important, too, and that part doesn't work either.
I have a varchar field that contains answers to questions separated by commas. Say there are 4 questions for each user. Here is an example of what the table would look like:
User Answer
1 Good,Fair,Good,Bad
2 Bad,Good,Good,Good
3 Fair,Good,Bad,Fair
I need to write a stored procedure to report off of that separates the Answer field into 4 different columns. How can this be achieved? Any assistance would be greatly appreciated.
In MySQL I can represent a string as the following:
X'6162636465'
However, I can't seem to find an equivalent that is supported MS SQL from the last hour and a half of searching the net. Does anyone know of any way to do this?
I have a column called professional_degrees in my table
and it has the following data
professional_degrees
bs,ms,mba
bs,mba,
bs,ms,bs,ms
mba,mba
bs,mba
In the above data u can see some of the degrees are repeated.So how can i find them and delete them from the table.
Thanks.
I everyone, I have been on the admin side of IT for the past 20 years and recently started to do some scripting (VBscript) and a little SQL.
I have developed a solution to meet the needs of some federal auditors, but not really met my needs yet.
What I have done is this.
I use MS Logparser to go out to 64 servers and copy the event logs into a DB on a SQL 2000 Ent. Server.
On the SQL server I have one StoredProcedure that parses out information from the security event log DB and put that info into a temp DB.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[stp_SecurityAuditReport]
AS
TRUNCATE TABLE SecurityEvents_Tmp
-- Parse Bank Number & UserName
INSERT INTO SecurityEvents_Tmp(DepartmentNumber, UserName, EventLog, RecordNumber, TimeGenerated, TimeWritten, EventID, EventType, EventTypeName, EventCategory, EventCategoryName, SourceName, Strings, ComputerName, SID, Message, Data)
SELECT
DepartmentNumber = '001',
UserName = CASE
WHEN Strings LIKE '[0-9][0-9][0-9]%'
THEN SUBSTRING(Strings,1,charindex('|',Strings,1)-1)
WHEN Strings LIKE '-|[0-9][0-9][0-9]%'
THEN SUBSTRING(Strings,3,charindex('|',Strings,3)-3)
WHEN Strings LIKE '-|[a-z]%'
THEN SUBSTRING(Strings,3,charindex('|',Strings,3)-3)
WHEN Strings LIKE 'Account Unlocked. |%'
THEN SUBSTRING(Strings,21,charindex('|',Strings,21)-21)
ELSE SUBSTRING(Strings,1,charindex('|',Strings,1)-1)
END,
Events.*
FROM Events
JOIN EventsToLog on Events.EventID = EventsToLog.EventID
WHERE SID NOT LIKE 'S-%'
-- Update blank usernames
UPDATE SecurityEvents_Tmp SET UserName = 'NO USERNAME'
WHERE UserName = '' OR UserName = '-'
-- Update DepartmentNumbers with zeros
UPDATE SecurityEvents_Tmp SET
DepartmentNumber = CASE
WHEN UserName LIKE '[0-9][0-9][0-9][a-z]%' OR
UserName LIKE '[0-9][0-9][0-9]#%' OR
UserName LIKE '[0-9][0-9][0-9]$%'
THEN SUBSTRING(UserName,1,3)
ELSE '001' END
As you can see, we use 3 didgit numeric prefixes on all Departmental employee accounts. This is later used to produce departmenntal user audit reports.
I then have this script in a DTS that exports the report to an excel spreadsheet. (All works well for this purpose!)
DECLARE @TimeGenerated datetime
SELECT @TimeGenerated = TimeGenerated FROM SecurityEvents_TimeGenerated
DECLARE @TimeGeneratedEnd datetime
SELECT @TimeGeneratedEnd = TimeGeneratedEnd FROM SecurityEvents_TimeGenerated
SELECT DepartmentName = CASE WHEN b.DepartmentName IS NULL
THEN 'All Department' ELSE b.DepartmentName END,
a.EventID,d.EventDescription,a.UserName, a.TimeGenerated,c.Email1,c.Email2,c.Email3,c.Email4
FROM SecurityEvents_Tmp a
LEFT JOIN DepartmentList b on a.DepartmentNumber = b.DepartmentNumber
LEFT JOIN EmailToList c on b.DepartmentNumber = c.DepartmentNumber
JOIN EventsToLog d on a.EventID = d.EventID
WHERE b.Departmentnumber in (select Departmentnumber from Departmentlist)
AND a.TimeGenerated BETWEEN @TimeGenerated AND @TimeGeneratedEnd
ORDER BY b.DepartmentNumber,a.EventID,a.TimeGenerated
This combination of utils and scripts does very good for producing generic security reports for branch officers.
But now I am getting requests to justify/explain what is in these reports. The problem I have is that the information needed to delve further into the event logs is in a field called Strings. This field not only changes in length and the amount of fields within this string, but the information in this field changes depending on the type of event record it came from.
This is the Strings field from a failed logon (529)
200jenil|DOMAIN|10|User32 |Negotiate|SERVER|SERVER$|DOMAIN|(0x0,0x3E7)|6920|-|10.190.12.10|48397
And this is from Event ID 642 which was an account being created.
-|381$cmiller|DOMAIN|%{S-1-5-21-3554868564-134719009-1577582102-7972}|Jmotta|DOMAIN|(0x0,0x58F635E)|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|-|%%1792|-|-
Now, my script does a good job of getting the first user name out but as in the 642 event the second users name would be useful as well. This is the person that created/modified the user account.
So what I was hoping was that I could use a function (or whatever) to automaticaly split the Strings value into it's individual components and put them into an auto-sizing temp table as something like Field1, Field2, Field3, and so on until the end of string.
I could then use a case to get the information needed.
HELP PLEASE!!
Thank You,
John Fuhrman
http://www.titangs.com
Hello everyone, I have this SP and can't get it to work on my SQL2000 server. I just can't seem to figure out what syntax error I am making.
(This works on my test SQL2005 server)
Truncate Table [SecurityEvents_Temp]
Insert Into SecurityEvents_Temp (
[BankNumber],
[UserName],
[DomainName],
[EventLog],
[RecordNumber],
[TimeGenerated],
[TimeWritten],
[EventID],
[EventType],
[EventTypeName],
[EventCategory],
[EventCategoryName],
[SourceName],
[Strings],
[ComputerName],
[SID],
[Message],
[Data]
)
Select
[BankNumber] = '001',
[UserName] = (Select [Value] From [dbo].[fn_Split]([Strings],'|') where idx = 3)
[DomainName] = (Select [Value] From [dbo].[fn_Split]([Strings],'|') where idx = 4),
[SecurityEvents].*
FROM [SecurityEvents]
JOIN [EventsToLog] on [SecurityEvents].[EventID] = [EventsToLog].[EventID]
WHERE [SID] NOT LIKE 'S-%'
Query Output:
quote:
Server: Msg 170, Level 15, State 1, Line 28
Line 28: Incorrect syntax near 'Strings'.
Server: Msg 170, Level 15, State 1, Line 30
Line 30: Incorrect syntax near 'Strings'.
Thank You,
John Fuhrman
http://www.titangs.com
I need to convert string data so that I can use it in a view.
example
xxx-xx-xx
xxx-xx-xx
xxxxxxx
xxxxxxx
xxxxxxx
xxx
xx
I need to be able to add the dashes in for the data that has 7 characters and disregard the data that is less then 7 characters
Jeff
Guys,
I have following table where I need to concatenate varchar column.
For example
IDCOMMENT
__________________
1JOHN SMITH
1 SURRENDER
1TO COPS
I want to be able to group by ID and concatenate COMMENT field to 'JOHN SMITH SURRENDER TO COPS' for ID 1
Is there any way to accomplish this?
Any suggestions and inputs would help
Thanks
I find myself between rock and hard place.
My requirement is to simply output a flat file from an OLE DB source containing three types of records.
The output records should look something like this:
Type 1 contains field1, field2, field3
Type 2 contains field1, field3, field6
Type3 contains field1, field4, field5
The source table contains all six fields.
If I simply map the input to output columns, I get all fields output in each record, which is not what I want.
So I run the source through a derived column transformation, inspecting the record type field and creating a new column containing the relevant fields concatenated together, e.g field1 + field 2 + field3 for the Type 1 record.
The problem is that the fields are now all scrunched together, i.e all the trailing spaces are being stripped out of each field during concatenation.
Is there any way to string these fields together while maintaining the original field length?
I'd like the output to look like
ABC FGH XYZ instead of
ABCFGHXYZ
Hi,
Whats wrong with the following T-SQL?
DECLARE @SQL NCHAR(4000)
SELECT @SQL = 'xxxx'
SELECT @SQL = @SQL + ' yyyy'
PRINT @SQL
I'm only seeing 'xxxx' in the debug window so it appears that its not adding the 2 strings?
Weird?!?
Hi All
I've got a list of IDs seperated by commas and I want to get each indivdual ID and insert them into a table. Has anybody got any ideas how this can be done?
Thanks
I have created a SSIS package, and got it to perform as I want.
I am having a problem though with connecting to my data source (OLE DB Source) using SQL Server Authentication.
If I use Integrated security, I have no problems. If I use SQL Server Authentication, I get errors.
Here is the connection string that works:
Provider=SQLNCLI.1;Data Source=.SqlExpress;Integrated Security=SSPI;Initial Catalog=MyDataBase
Here is the connection string that fails:
Provider=SQLNCLI.1;Data Source=.SqlExpress;Persist Security Info=True;Password=**********;User ID=myUserid;Initial Catalog=MyDataBase
The strange thing is that when I use the Connection Manager dialog screen and click on "Test Connection", I get the response that the connection succeeded. It is only when I try to run the task that I get an error.
Here is the error I am getting:
[OLE DB Source [1]] Error: The AcquireConnection method call to the connection manager "MyDataBase" failed with error code 0xC0202009.
[DTS.Pipeline] Error: component "MyDataBase" (1) failed validation and returned error code 0xC020801C.
I have eliminated the obvious errors (incorrect Userid or password). Also, I am unable to use Integrated Security as this package needs to be distributed to various systems that may or may not be on our company's primary domain.
It seems I am missing something really basic here, but am not seeing it.
Any thoughts?
Hi,
I have a strange problem I can't solve. I have a log table where a have for example a timecreated column where I store dates in the format 0704212112538707
What I want to to is to search for log posts that are in between two dates.
I have this SQL query: select * from log where timecreated >= '070421' and timecreated <= '070422'. With this question I only get hits from the date 070421. I would like to get both 070421 and 070422. I get no difference if I use < or <=.
How would I solve this
Best /M
Hey everyone,
So I'm querying from a database where one of the fields for each entry is a city. I would like to count the ones in each city. So I'm counting and grouping on city. Unfortunately, the data is not very consistent. Some are listed as Miami, FL and others simply as Miami. I don't need the state so I can just drop it, but I can't figure out how. I was using charindex and substring, but can't get it to work because if there is no ',' then the charindex is zero and substring doesn't work correctly. Any help would be greatly appreciated.
Thanks,
Keith
This is probably a very basic question and I apologise for asking. Can an app developed with VWD using SQLexpress be used with server2000? if so how do I modify the connection string.
Hi,
I wrote a strore procedure (MS SQL DBMS) that concatenates few strings
and then write the resuls into a field of my table. I use a variable
@temp ( varchar(800) ) to add all the string I need.
For example:
@temp = @temp + string1
@temp = @temp + string2...
Nevertheless, even if I have declared @temp as varchar(800), and
the length of the strings that I added is for istance 334 , @temp loses
part fo the last string.
Why?
This is my store procedure:
CREATE PROCEDURE dbo.AddSearchColumn AS
Declare @cElements cursor,
@DocNum integer,
@Fname varchar(30),
@Lname varchar(30),
@Loc varchar(50),
@titleV varchar(200),
@KeyV varchar(250),
@temp varchar(800),
@a integer
begin transaction
Set @cElements = cursor for select DocNumber,Title,
FirstName,LastName,Location,Keywords from dbo.DocTable1
open @cElements
fetch NEXT from @cElements into @DocNum,@titleV,@Fname,
@Lname,@Loc,@KeyV
while (@@FETCH_STATUS = 0)
begin
set @temp=convert(varchar(4),@DocNum)+';'+rtrim(ltrim(@titleV))+';'+
rtrim(ltrim(@Fname))+';'+rtrim(ltrim(@Lname))+';'
if @Loc is not null
set @temp= @temp + rtrim(ltrim(@Loc))+';'
if @KeyV is not null
set @temp= @temp + rtrim(ltrim(@KeyV))+';'
if @DocNum=436
set @a=2
UPDATE dbo.DocTable1
set SearchColumn=@temp where DocNumber=@DocNum
commit
fetch NEXT from @cElements into @DocNum,@titleV,@Fname,
@Lname,@Loc,@KeyV
END
close @cElements
DEALLOCATE @cElements
GO
Has anyone had to do sorting on a column of string datatype that can hold multiple decimals. Just like an IP Address but not necessarily 3 decimal place ?
I could not find an out of box solution with SQL Server and wasn't sure if there was a nice way to use a datatable or some specific function.
Thanks,
Hi,
I am looking to allow a user to select which database they need to connect to, and then for them to be able to use that connection string until they choose a different database. I have a separate database specifically to hold the list of databases with the respective connection strings, and various forms that are currently looking at the web.config for the connection string. Unfortunately, I can't just put all the connection strings into the web.config as the number of databases available to the user will increase on a weekly basis which will be handled by a database administrator, so I want a user to select from a list of databases (for which they have permissions), and for that connection string to remain for that user. Potentially there will be 20 or more users each connected to a different database (all SQL Express).
If anyone can provide any help on this I will be truly thankful as I've hunted on the internet for hours and have been unable to find anything that has helped.
Thanks in advance.
Paul
Dynamic connection strings
I have found lots of people asking for something similar to this but cannot find any solutions - can you help?
I am developing an application (ASP.Net2) in which users are associated with 'Clients' and every Client has their own SQL Server database.
Users are authenticated using standard ASP.Net Authentication via a separate database (common for all users). This includes a table linking them to a Client and each Client record includes the connection string to their database. Currently a default connection string is held in the web.config file.
My problem is that I don't know how to dynamically change the connection string after a user has logged on. I have a large number of databound controls, many of which are declared and some are coded in the VB.Net code behind. I know I can use code behind to change the connection string for a SQLDatatSource:
I thought I had it cracked with the following:
Public objClientConnection As New ConnectionStringSettings
Public Sub Page_Init(ByVal sender As Object, ByVal e As System.EventArgs)If Session("ClientDBConnectionString") <> "" ThenobjClientConnection.ConnectionString = Session("ClientDBConnectionString")objClientConnection.Name = "ClientDBConnection"objClientConnection.ProviderName = "System.Data.SqlClient"ElseobjClientConnection = ConfigurationManager.ConnectionStrings("WebTool1ConnectionString")End IfEnd Sub
The session variable Session("ClientDBConnectionString") holds the client specific connection string and is populated when the user logs on. If this is not populated then the connection string defaults to one collected from web.config. This should ensure there is always a design time connection string available.
The trouble is, when I try and declare the SQL data souce like this:
<asp:SqlDataSource ID="dsTest" runat="server" ConnectionString="<%# objClientConnection.ConnectionString %>" SelectCommand="usp_SEL_DocumentTypes" SelectCommandType="StoredProcedure" ></asp:SqlDataSource>
I get an error: The ConnectionString property has not been initialized.
I have been going around in circles on this for days and would really appreciate some help. Am I on the right lines? What needs to change to make it work? Is there a better way?
Many thanks,
Cliff