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.
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.
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.
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.
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:
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?
Hi, 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 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.
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)
[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'.
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
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
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?
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.
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 <=.
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.
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.
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
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
