Need Help Building/reading C# SQL Array
I'm stuck and uinder a bit of a time crunch.
I have 5 fields I want to get out of a sql database using a function that I'm writing. I figure it sounds like an array. basically I want to make an array, and fill it up with the results of a sql select, then read the array.
This is what I have so far.....
String TempHRAcctCode, TempJobDescription, TempHourlyRate, TempEmplID;
Array TempArray;
TempJobDescription = DDDept.SelectedItem.Text; (to get KeY Value)
SqlConnection connection = new SqlConnection(ConfigurationManager.ConnectionStrings["MYCONN"].ToString());
connection.Open();
SqlCommand command = new SqlCommand("Select HRAcctCode, HourlyRate , EmplID, ... FROM TimeMyProfile WHERE JobDescription = " + TempJobDescription + " ", connection);
SqlDataReader TempDataReader = command.ExecuteReader;
while TempDataReader.Read
(
... OK I GIVE UP!
Thanks in advance
View Complete Forum Thread with Replies
Related Forum Messages:
How Would I Send A String Array As A Integer Array?
I have a stored procedure that has a paramter that accepts a string of values. At the user interface, I use a StringBuilder to concatenate the values (2,4,34,35,etc.) I would send these value to the stored procedure. The problem is that the stored procedure doesn't allow it to be query with the parameter because the Fieldname, "Officer_UID" is an integer data type, which can't be query against parameter string type. What would I need to do to convert it to an Integer array? @OfficerIDs as varchar(200) Select Officer_UID From Officers Where Officer_UID in (@OfficerIDs) Thanks
View Replies !
Array Of Array - IRR Function
Hi, I am using the IRR function in a report. I have created the following code so it creates an array: Public GroupIRRArray(-1) As Double Public Function addToIRRArray(ByVal BMV As Decimal, ByVal BAB As Decimal, ByVal EMV As Decimal, ByVal EAB As Decimal, ByVal CFB As Decimal) Dim g As Integer g = uBound(GroupIRRArray) + 1 ReDim Preserve GroupIRRArray(g) if g=0 then GroupIRRArray(g) = (CFB+EAB-BAB+BMV)*-1 else if g=1 then GroupIRRArray(g) = (BAB-CFB-EAB+EMV) else GroupIRRArray(g-1)= GroupIRRArray(g-1)-(BMV) GroupIRRArray(g) = (BAB-CFB-EAB+EMV) end if End Function It works fine but now I want to create multiple groups within my report. How can I change the code so it loops on another parameter? What I had in mind was to create an initial array with the parameter value that I want to use for grouping and a dynamic array based on the name of each group. So I would end up with one array containing the group name plus x number of arrays with the raw data. Alternatively, is there a way to use the IRR function without creating a custom code? Like a conversion parameter that would make my floating field a one dimensional array? Thanks, Jam
View Replies !
Building Treeview
userfeatureuserName featureIda 1a 5b 1b 5b 9c 5 c 9 menuid Pid Name1 Administrator2 1 Create User3 1 Delete USer4 1 View log5 WSR6 5 X7 5 X8 5 X9 Manager10 9 Y11 9 Y Using the above table i want to create a treeview ie based on the user login. Please let me know if there is any previous sample to this situation.
View Replies !
Building A Date In SQL
Is there any sql method that takes 3 parameter like, day, month and year . And return me the date. For example function(10,3,2007) and it returns 10-03-2007 Thanks,
View Replies !
Building A Community
Hi! Hello. I have now started to build my own community. And I have some questions on the database.For the users to login I use the login control and all the users information is stored in the ASPNETDB.MDF database.In the web.config file I have created some profiles for saving some information about the users (Name, Birth, Town) and so on.Now. All the users in this community will have their own profile page, Guestbooks ++.So I was wondering if I should create tables for all features like guestbook, profile pages or should I do this by using Profile (ASP.NET).How many users does ASPNETDB support?.
View Replies !
Building A Where Clause
I am not very experienced with stored procs and I'm attempting to write my first one. I am writing a search page via aspx and that page will call my proc and depending on the input parameters, the proc will return the search results. To do this I have built a where clause string but I don't know how to (if it's even possible) make this variable part of my query. Can anyone tell me a way to make the following work (input params left out to conserve space)? BEGIN -- SET NOCOUNT ON added to prevent extra result sets from -- interfering with SELECT statements. SET NOCOUNT ON; SET ANSI_WARNINGS OFF SET @where = '' IF @JobNoStart !='' SET @where =+ ' AND LJOB BETWEEN @JobNoStart AND @JobNoEnd' IF @OrderDateStart !='' SET @where =+ ' AND JOBDATE BETWEEN @OrderDateStart AND @OrderDateEnd' IF @DueDateStart !='' SET @where =+ ' AND DUEDATE BETWEEN @DueDateStart AND @DueDateEnd' IF @ProofDateStart !='' SET @where =+ ' AND PROOFDUE BETWEEN @ProofDateStart AND @ProofDateEnd' IF @CloseDateStart !='' SET @where =+ ' AND CLOSEDATE BETWEEN @CloseDateStart AND @CloseDateEnd' IF @CogsDateStart !='' SET @where =+ ' AND COGSDATE BETWEEN @CogsDateStart AND @CogsDateEnd' IF @ProductName !='' SET @where =+ ' AND PRODUCT = @ProductName' IF @CustomerNumber !='' SET @where =+ ' AND FCUSTNO = @CustomerNumber' IF @SalesPerson !='' SET @where =+ ' AND FSALESPN = @SalesPerson' IF @CSR !='' SET @where =+ ' AND JOBPER = @CSR' IF @Closed = 0 SET @where =+ ' AND CLOSEDATE IS NOT NULL OR CLOSEDATE IS NULL' ELSE IF @Closed = 1 SET @where =+ ' AND CLOSEDATE IS NULL' ELSE IF @Closed = 2 SET @where =+ ' AND CLOSEDATE IS NOT NULL' IF @Canceled = 0 SET @where =+ ' AND CANCDATE IS NOT NULL OR CANCDATE IS NULL' ELSE IF @Canceled = 1 SET @where =+ ' AND CANCDATE IS NOT NULL' ELSE IF @Canceled = 2 SET @where =+ ' AND CANCDATE IS NULL' IF @FinalShip = 0 SET @where =+ ' AND FINALSHIP IS NOT NULL OR FINALSHIP IS NULL' ELSE IF @FinalShip = 1 SET @where =+ ' AND FINALSHIP IS NOT NULL' ELSE IF @FinalShip = 2 SET @where =+ ' AND FINALSHIP IS NULL' SELECT LJOB, DUEDATE, FCOMPANY, ID, QUAN WHERE LJOB IS NOT NULL @where END
View Replies !
Building Search
Hi I've never done this before and I have all kinds of issues conflicting in my head (search rank, noise words, injection attacks ..etc). simply i need to search several columns in a table in the database using one search text (just like the simple search in Google). if multiple words are you used then the search should search for each of them. also manage to ignore noise words and other issues. what is the best way of doing this? I looked at FTS in SQL 2000 but didn't know how to handle all the above mentioned issues. this should be simple, right? but i have been looking all day. I guess i don't know what im looking for because i've never implemented a web search b4. please help p.s. I know t-sql and asp.net/c# well.
View Replies !
Building A Database
I am working with SQL server 2000. The database is installed in my machine. Now I have got the ".ldf" and ".mdf" file pertaining to a database from someother server. Is it possible to build the database present in the above said files in my server. NOTE : I dont have a direct access to the remote server from where the above said files were obtained (otherwise I think the DTS utility would have come in handy).
View Replies !
Help With Building Query
Hello, This will probably be trivial and basic for most, but I'm having a hard time trying to figure out the best way to do a SELECT statement. First, let me explain what I have: Two tables: Table 1: Orders Some of the fields: ID PropID WorkOrderNum OrderDesc DateCompleted Table 2: OrderDetail ID OrderID TenantName As you probably have realized, the OrderID in my 'OrderDetail' table corresponds to the ID field in my 'Orders' table. The 'Orders' table contains the order header information, while the OrderDetail contains line items for that order - 1 line item per record. Here is my SQL statement to retrieve an order when searching by the 'Order Description' (Orders.OrderDesc): SELECT PropertyLocations.PropertyLocation, Orders.ID, Orders.PropID, Orders.WorkOrderNum, Orders.OrderDesc, Orders.DateCompleted FROM PropertyLocations, ORDERS WHERE PropertyLocations.ID = Orders.PropID AND OrderDesc LIKE '%lds%' Ok, so now for the 'big' question/problem: I also need to be able to search the 'Tenant Name' field from the 'OrderDetail' table. So what is the best/most efficient way of doing that? The other stipulation about that is that there can be (and usually is) several records/line items (in the OrderDetail table, of course) that contains the same (or similar) data, but I don't want duplicates. And when I say duplicates, all I care about is retrieving a few fields (as you can see from my SQL statement) from the 'Orders' table. Another way to describe what I want is that I want all unique orders that have a 'TenantName' in the 'OrderDetail' table that matches the search criteria. My brain just isn't wanting to figure this out right now, so I was hoping someone could help me out. thanks.
View Replies !
SQL Newbie Needs Help Building SP
Help Please - JMail - SQL SPs - Confirmation Email I'm at the last stage of my current project and an totally stuck. I'm trying to build the body of a order processing request email. For security reasons I wish to use a SP to build and send the email. (therefore no sensitive data gets passed to the client) I have the JMail Object running properly on the server. It collects and sends the email in the normal course of the transaction from the client. Problem is that I cannot figure out how to properly build the body of the email in the SP. I'm looking to do add the following to a single SQL SP variable to stuff in the Jmail Object to be sent. The content of the variable should look something linke the following: ----------------------------------------- "Order placed:" = orders.date + '<cr>?' customers.name<cr> orders.shippingaddress<cr> orders.billingaddress<cr> /*loop each record where /*customerID and orderID match passed arguments /***loopstart*** OrderDetails.SKU <tab> Product.Productname <tab><tab> Orderdetails.Qty <tab> OrderDetails.Price<cr> /***loopend*** Ordertotal.GrandTotal -------------------------------------- Any help greatly appreciated. THX, Charles
View Replies !
Building An NT Box To Host SQL 7
I'm in the midst of planning how to build an NT box to host SQL 7.0 and was wondering if there is any advantage to segregating the RAID 5 Array (5 x 18GB drives) into numerous *logical* partitions to separate database and log files (I can't see what advantage there would be if the disks are all on the same array, but..) If anyone has any pointers or links to recommended NT configurations for hosting SQL, I'd appreciate hearing them. TIA, RM
View Replies !
Building Sql Statements
Hi, I am having some problems trying to build an sql statement from more than one statement. Here is the statement select 'Insert App_Column (Table_ID, Column_Type_Transformation, Column_Name, ) Values (@table_ID,' ,'NULL,', name from payatwork..syscolumns where id in ( select id from payatwork..sysobjects where name like 'Employee_Profile') order by colorder, ')' What I am finding is that the bracket at the end of the statement is not appearing - how do I append statements to the end of this sql statement (i've tried various combinations of the + sign and the comma without success. thanks, Jim
View Replies !
DTS Building And Distribuiding
Hi Which files does anybody know I need to distribute with DTS?, because DTS usually I run in the SQL SERVER or in my machine with VB, but when I distribute my application through Package & Deployment Wizard , give message: Activex Component cannot create Object Thank you in advance
View Replies !
Building A Parameter
I have a parameter on the report with two options 1."Less than Jan 1 2007" 2. "Greater than Jan 1 2007". According to the selected value, I need to change the query(Oracle database) of report as: select * from table_name where open_date < Jan 1 2007 (or) select * from table_name where open_date > Jan 1 2007 Please let me know how to acheive this..
View Replies !
Building A Database
Hi I've been asked to design a samll database in access with 5 tables and 3 querys also a form, and reports. I'am not to sure on what to charge the person would any1 have a idea on what a small to medium database would be cost to build. Thanks
View Replies !
Help In Chart Building
Hi, i need to build a chart in reporting services, i need some help cos it's the first time i do it. i have a table with columns : date, service, comment_card_id and restaurant_name. the service values can be:"Superior", Excellent", "Good" or "Not Satisfactory" .I created three parameters :restaurant, from date and to date. I want to be able to show on my chart the percentage of Superior, Excellet, Good, Not Satisfactory service from the count of the all comment cards after having chosen the date range and the reataurant name. So i thought that my category should be the service and my data the percentage with filters on restaurant name and date range. Should the date be in the category or it can be in the filter? Can anyone give suggestions please?
View Replies !
Recommendations For Building An App.
I have a SQL 2005 database containing the location of graphics files. I want to start learning how to write a C# application that will get a path from the DB and display the file. Any recommendations on sites where I can start learning how to do this? Thanks
View Replies !
Problems Building Sql Statement
Hello, I'm having problems building an sql stament that joins a few tables. I can seem to get my head around the structure! I have to try and link up four different tables to try and get my result. Here are the 4 table structures... Web_Users----------------User_IDName Tags_Table-----------------Tags_IDUser_IDGroup_IDTitle Created_Groups-----------------------Group_IDGroup_Name Tags_To_Groups------------------------Group_Link_IDGroup_IDTag_ID Basically, this database, has four tables; One table (Web_Users) that contains a users name, and assigns a unique ID (User_ID), another table that stores a users tags they have created, and also links it to a group_ID. The created_groups table, contains group names and assigns a unique id also. And the last table, Tags_To_Groups, links tags to groups. So this is what I'm trying to do... I'm trying to get the Group_name field from Created_Groups table, of a tag , that belongs to a certain user. If sounds easy when I say it like that, but I've been inner joining tables all night and failing every time. Does this make sense? Can anyone help? Thank you
View Replies !
Building A Table From SQL Query
I am hoping someone can point me in the right direction with this.I have query that returns all the colums in a row (SELECT * FROM table WHERE value = 'value') and I need to build a table with this data. Some of the columns may not have values in them, and so I dont want to build a table row for it. I also need to use the column name as the table header. As an example:==============================Column Name || Column Value-----------------||-----------------Column Name || Column Value -----------------||----------------- I hope I have explained myself properly. Any help would be greatly appreciated.
View Replies !
Cannot Open Database While Building
Hi Everyone,I'm facing this problem now.Cannot open database "C:myDB" requested by the login. The login failed.Login failed for user 'myLaptopASPNET'.May i ask how can i resolve?Please feel free to let me know the information you need.Thank You!
View Replies !
A Query Building Question
Hi there, i have a query building question and was hoping that one of you would know the answer. Here is what i need to do :(i am using asp.net and ado.net) I have 1 table where I store thedata, where 5 criteria determine a unique row in this table. Now, this has recently changed as the start date was added. So there potentially can be more than one entry in the table with same 5 criteria, but different start date. I need to retrieve the row with the latest start date (currently active). The problem arises when the users enter less than 5 criteria. In this case the results may not possess same 5 criteria. Say the user searches based on 2 criteria. Then all the rows possessing these 2 ctieria will be returned, but other 3 criteria might differ with the results set. But, i only need the latest start date row for each row. So for example, if i searched on 2 criteria, i got back 4 rows, 2 of which possess the same 5 criteria. But between these 2 i only need to display ONE row to the user - the one with the latest date. How do i build a query? say the table name is tbl, and criteria 1 to 5 fields are called c1 ... c5, and start date field is called start_date. thanks in advance
View Replies !
Problem Building Script With Int
I am currently moving a table from a msSQL DB to a MySQL DB.I am trying to build the bones of an insert statement by adding stringsto a select statement.SELECT '(' + job_referenceno + ', ''' + job_title + ''', ''' +job_category + ''', ''' + job_type + ''', ''' + job_location + ''', '''+ job_duaration + ''', ' + job_salary + ', ''' + job_contactperson +''', ' FROM JobDetails WHERE job_active = 1;This works perfectly until I try and pull an int column from the Table.When I add a column to the string that is INT the query returns 0 rows,with no errors.Can anyone help explain / overcome this?Regards,Tomas
View Replies !
Assistance Building A Query...
I am trying to generate some datasets with some queries...With a given series information, it should return PART_NOs that has STD= 1 and a unique price at that particular 'START', and keeping the'TYPE' in consideration...DB examples below:Main DBIDPART_NOSERIESSTD1A-1A12A-2A13A-3A14D-1D15D-2D0Price DBIDPART_IDTYPESTARTPRICE501X100050511X1000040521Y100060531Y1000050542X100050552X1000040562Y100060572Y1000050582X100090etc.main.ID and Price.PART_ID are paired together.So in an example case, lets say I am querying for SERIES A, with TYPEX. A table should be outputted something likePART_NOA-1100050A-11000040A-3100090Note how it skipped printing A2 because the price is the same as A1.I'm really looking for the SQL code here... I can't get it to filter ondistinct price.SELECT MAIN.PART_NO, PRICING.START, PRICING.PRICEFROM MAIN, PRICINGWHERE (MAIN.SERIES LIKE 'A')AND (MAIN.STD = '1')AND (PRICING.PRICE != '')AND (PRICING.TYPE = 'X')AND (MAIN.ID = PRICING.PART_ID)I've been trying to use GROUP BY and HAVING to get what I need but itdoesn't seem to fit the bill. I guess I'm not terribly clear on how Ican use the SQL DISTINCT command...? If I try and use it in my WHEREstatement it gives me syntax errors, from what I understand you canonly have distinct in the select statement? I'm not sure how tointegrate that into the query to suit my needs.Thanks for any help.
View Replies !
Building A Mailing List
Hi folks, I'm building a DTS package which needs to mail a list of users nightly The mailing list needs to be dynamic so I'm using the dynamic properties tab to populate the To, From etc. fields in an SMTP DTS task. I need to construct a script which will run within the DTS package and build a mailing list file from a table of users in the connected db. The Dynamic properties task will then pull from this data file when populating the 'To' field. The basic select statement is simple (e.g. SELECT email FROM employees WHERE role = 'mgr') however I need the output to be a single line of email addresses separated by commas (e.g. Email1,Email2,Email3....etc). I'm a bit unsure on how to go about doing / writing this. Can anyone help? Thanks, Dave
View Replies !
Sql For Building A Temp Table 'on The Fly'
In DB2 using CTE,I can do WITH ZIPCODES (ZIP) AS (VALUES ( ('10001'), ('10002'), ('10003') ) ) select zip from zipcodes will give zipcodes---------100011000210003 Is it possible to do something similar in SQL Server 2005? My reqmt is - based on user input in the front-end, I want to build a table like this in CTE and continue to use it multiple times. Just for example : WITH ZIPCODES (ZIP) AS (VALUES ( ('10001'), ('10002'), ('10003') ) ) ,city as (select * from citytab where cityzip in (select zip from zipcodes) )select * from city,zipcodes, locationid where loczip in (select zip from zipcodes where zip like '1%') Thanks in advance ...
View Replies !
Building Text Files
Ok, I have an sp that'll build an SAP feed based on parameter input (params control which type of file I want to create). I want to build 9 files in total. I have a table set up with my parameters and output file names. Question 1: Process from DTS I have a DTS package which will build a file based on params and filename from table, pulled with a Dynamic Properties task. How can I iterate through my table of parmas to create the muliple files? Question 2: Process from a stored proc I have a stored proc, from which the interation through values is simple. How can I create and export to the text files from the stored proc? I think I may be having a mental fart on this one. I could create a text linked server dynamically, but I have not played with them much, How to I write to one (create table etc). PS: The file data cannot include cilumn headings TIA - bpd
View Replies !
Building Relationships Between Multiple DB's
During the set-up of my DB's and their tables, I was unable to setup a relationship between a table in one Database and another table in my other Database ( using the diagram ). Maybe my datastructure of multiple databases is not correct, or is their an option to set relations between multiple databases ? PS in the future I planned to have some other databases on different servers.
View Replies !
DTS: Dynamically Building SQL String
I'm moving data from Oracle 7.3 into SQL Server 7.0. Each day I want to load only those transactions that took place that day. To do this I need to either build a parameterized query in the DataPump Task (on the data source side) or build the query dynamically in a preceeding VBScript Task. I have been unable to figure out how to to do either. Can someone please help? Thank you, - Bill
View Replies !
Building Search String ?
The procedure below is for a 3 field wildcard search window. It works fine if you enter a value such as cheese in @product_name. If you enter cheese in @product_name1 or @product_name2 it returns nothing. The string builds OK with values inserted where they should be, obviously there is a problem in the looping back of variable. I've been looking at this so long I'm sure that the obvious solution is staring me in the face, but I just can't see it. Any help would be appreciated. Alter PROCEDURE usp_RobSearchTest ( @product_name nVarChar(100), @product_name1 nVarChar(100), @product_name2 nVarChar(100), @country_id nchar (3), @language_id nchar (2) ) AS DECLARE @Variable nVarChar (100) DECLARE @SQLString NVARCHAR(1000) DECLARE @ParmDefinition NVARCHAR(1000) /* Build the SQL string once. */ SET @SQLString = N'SELECT product_name,unit_price,item_id,refund_price FROM PRODUCT_LISTING WHERE product_name LIKE ''%' + @Product_name + '%'' ORDER BY product_name' /* Specify the parameter format once. */ SET @ParmDefinition = N'@product_name nVarChar (100)' /* Execute the string with the first parameter value. */ SET @Variable = @product_name EXECUTE sp_executesql @SQLString, @ParmDefinition, @product_name = @Variable /* IT WORKS UP TO THIS POINT */ /* Execute the same string with the second parameter value. */ SET @Variable = @product_name1 EXECUTE sp_executesql @SQLString, @ParmDefinition, @product_name1 = @Variable /* Execute the same string with the third parameter value. */ SET @Variable = @product_name2 EXECUTE sp_executesql @SQLString, @ParmDefinition, @product_name2 = @Variable
View Replies !
Sql 7 Enterprise And Node Re-building
Hi all, I have a question about re-creating the sql portion on either node a or b in a clustered situation. Is it possible to do this without affecting the working node and database ? or is the best solution to make a ghosted image of the node after install is complete and if the node fails just get to a point on the affected node where you can copy the image back? Thanks for any feedback. P
View Replies !
Building An Alias From Query
Hi, Does anybody know if it is possible to obtain an alias from a Query without using Dynamic SQL. My Problem Looks like this: CREATE TABLE Source (SourceName varchar(50), SourceAge int) INSERT INTO Source VALUES ('Mary',41); INSERT INTO Source VALUES ('John',22); INSERT INTO Source VALUES ('Tom',15); INSERT INTO Source VALUES ('Bill',55); The Statement should look like This: SELECT A.SourceAge AS A.SourceName FROM Source A; The Result should look like this: Mary John Tom Bill ---------------------------------------- 41 22 15 55 Thanks in advance Raimund
View Replies !
Building/Running A Package.
Hi, I have a solution, and I have a few projects in this solution. Each project has a few packages in them. The problem/question I have is this: when I am working on one of the packages, I have only this package open. When I try to run/test this package, every single package in every project gets opened, recompiled/rebuilt before my current package gets to run. It's very frastrating and time consuming. Is there a way to somehow disable this weird behavior ? Is there a way to just build the package I am currently working on ? Please, help. Thanks, Victor.
View Replies !
Understanding Building And Deployment
#1 Anyone seen any good BLOGs on building solutions and what occurs? BOL is pretty thin in this area. Does it just spin through all the projects in the solution, validate them, copy the packages to their respective in folder, and add them to the manifest?
View Replies !
I Am Practicing By Building A PM Online App
In my Project table. I have a column called projSeqNum (Project Sequence Number) I was with a fellow techie -data modeler- and I think it was just placed with an int data type. I think a 4 length. My question is what would be the correct data type here. There could be 100's of thousands of unique Project ID's so I need something sturdy but not overboard. Any ideas?
View Replies !
Building Unicode Database
I have a problem with Insert and Update Unicode data to database I can send Unicode data using string with N prefix (Ref 001) Below I write some line of my SP But It does not write Unicode data to table, it writes like ???????? How to insert data using n prefix with Declared Variable? How can avoid this problem? Any other method to pass unicode data using SP? Please help me... column UnitName nvarchar Sample Stored Procedure (SP) SP header: CREATE PROCEDURE ltrsp_AddEditUnit @UnitID char(4), @UnitName nvarchar(20), @UtrID int Insert: INSERT INTO ltrtb_Unit (UnitID,UnitName,UtrID) VALUES (@UnitID,@UnitName,@UtrID) Update: --- UPDATE ltrtb_Unit SET UnitName=@UnitName, UtrID=@UtrID WHERE UnitID=@UnitID Ref 001: found a method to insert unicode data to table (Using N' prefix), but I want to add using declared variable INSERT INTO ltrtb_UserLevel (UserLevel,Description) VALUES (1,N'Unicode String') From the URL, http://databases.aspfaq.com/general/why-do-some-sql-strings-have-an-n-prefix.html But I need to know how to pass the data using stored procedure
View Replies !
ASP - Building A Query - Question
Good Afternoon! I have a (hopefully) simple question. I have recently been bumped into an applications developer position. I took a week of ASP training a few months ago, but outside of that my exposure to ASP and SQL has been EXTREMELY limited. I just undertook my first project using these skills. We have an application where I track "Letters". I fill out a form with information regarding the Letter, and save it so there is a record of the letters existence and what its status is. Afterwards, we want to update the status, so I find the saved form, make my edits, and save it. This is where my problem starts. When I first save the letter, I have no problems whatsoever. When I save it a second time after making my edits, I get an error: Microsoft OLE DB Provider for SQL Server error '80040e14' Line 1: Incorrect syntax near 'yearID'. /AuditFlowChart/UpdateAudit.asp, line 206 What I am doing with the ASP is basically created a SQL statement to update the table with the new letter information. I added a line to print out the store proceedure that is being created by the following line Set rsRecordsetObject = objCOMComponentClassObject.ExecuteSQLStatement(spStoredProcedure,vntSQLServerName,dbDatabaseCatalog) I print out spStoredProcedure and I get the following UpdateCorrespondence @p_idCorr = 416, @p_dtOfLetter = '8/7/2008', @p_dtRcvdByRespAtty = '2/19/2007', @p_dtRcvdByAdmin = '2/23/2007', @p_descOfEntity = 'This is a test 2009 letter. Dated 2/8/2007', @p_ClientCode = '01074', @p_MatterCode = '0055', @p_dtDue = '3/15/2007', @p_dtEffective = '4/18/2007', @p_dtYE = '4/8/2007', @p_respAtty = '0330', @p_respAttyFull = 'Abelson, H. Edward', @p_prepParalegal = 'Aldous, Anne Marie', @p_prepAtty = 'Abromowitz, David', @p_prepLitParalegal = 'Allegrini, J. Samuel', @p_dtGivenToParalegal = '4/20/2007', @p_dtGivenToAtty = '2/28/2007', @p_dtMemoCirculated = '2/18/2007', @p_dtRespSentToLitParalegal = '2/10/2007', @p_dtRespSentToPrepAtty = '2/26/2007', @p_dtSentToAccountant = '2/15/2007', @p_memoDSid = 1111111, @p_respLetterDSid = 2222222, @p_sideLetterDSid = 3333333, @p_comments = NULL, @p_yearID = rsLetterDetail1('yearID'), @p_opinion = 1, @p_complete = 0, @p_userID = 'GSSUSO1517' When I run the above thru Query Analyzer, I get the following: Server: Msg 170, Level 15, State 1, Line 26 Line 26: Incorrect syntax near 'yearID'. So now I know where my error lies, but I am not sure where to look next. Does anyone have any ideas? Is there a problem I am not seeing with the query? Any help would be wonderful and much appreciated.
View Replies !
Building SQL Strings From Variables
Disclaimer... I am very new to SQL server! I am trying to create a stored proc. I am passing in some variables which are used as the "data" side of where clause tests, but I also want to pass in a couple of variables to be the variable side of the where clause test, can it be done? i.e. select id from table where a=1 and b=2 order by my_order_field becomes select id from table where a=@data1 and b=@data2 order by @my_variable_field and I call the sp with exex sp 1,2,my_order_field Any clues?
View Replies !
Help Needed In Building SSIS
I am trying to import part of the following file structure into a SQL2005 table The 1st 10 records are not needed (HEADER DATA), neither are the records that are from ‘TRAILER RECORD’ Can anyone assist me in designing a Simple SSIS to import this file to a table The LedgerKey will be from a Global variable Text within <> are for information and NOT contained in the file In this file there are three records to be imported, but in other files thjere can be upwards of 20,000 records Table CREATE TABLE [dbo].[TempTransactions]( [LedgerKey] [varchar](20) NOT NULL, [Licence] [varchar](6) NOT NULL, [DestSortCode] [varchar](6) NOT NULL, [DestAccountNumber] [varchar](8) NOT NULL, [DestAccountName] [varchar](18) NULL, [TransValue] [varchar](11) NOT NULL, [DestBankRef] [varchar](18) NULL, [TransCode] [varchar](2) NOT NULL, [tranref] [int] IDENTITY(1,1) NOT NULL ) ON [PRIMARY] File < HEADER DATA NOT REQUIRED > "Header Data" "Header Data" "Header Data" "Header Data" "Header Data" "Header Data" "Header Data" "Header Data" "Header Data" "Header Data" < HEADER DATA NOT REQUIRED > "11111122222222TEST ACCOUNT 1 BANK REG 0000000020017" "33333344444444TEST ACCOUNT 2 BANK REG 0000000150017" "55555566666666TEST ACCOUNT 3 BANK REG 0000000030017" <FIXED FIELDS LENGTHS 6,8,18,18,11,2 > <TRAILER RECORD NOT REQUIRED > "TRAILER RECORD" "TRAILER RECORD" "TRAILER RECORD" "TRAILER RECORD" "TRAILER RECORD" "TRAILER RECORD" "TRAILER RECORD" "TRAILER RECORD" "TRAILER RECORD" "TRAILER RECORD" "TRAILER RECORD" "TRAILER RECORD" "TRAILER RECORD" "TRAILER RECORD" <TRAILER RECORD NOT REQUIRED >
View Replies !
Building Query Graphically
Hi, I'm using 2005 management studio express. Can I use the graphical query builder to specify tables from more than one database? I know I can do this in sql in the format database..table.column but in the query builder it only shows tables from the current database. Can you get around this? Thanks
View Replies !
Building A Sql Statement In A Stored Proc
Hi All, What i'm trying to do is build a dynamic query where the like clause is the variable bit of the query. What I've done is to create 4 varchar variables of length1000, and a variable to hold the result of the concatenated variables, which is defined as length of 4000. I've checked the length of the resultant query and its comes in at arount the 450 charcter lenght, but when I run the stored proc it truncates the @varfull around the point of the first % sign. I've tried various ways to create this query and it always truncated around the point of the % sign. Am I doing this right? can anyone point me in the right direction on how to do this with a sql statement, it works fine for text strings! Here's part of the code, I create the content of @var3 in a loop based on a string of words passed into the stored proc. Thanks for any help on this! regrads davej @var1 = 'SELECT COUNT(d.item_id) AS Expr1, d.item_id FROM tp_index_details AS d INNER JOIN tp_index ON d.idx_id = tp_index.idx_id '@var2 = 'WHERE (d.idx_id IN (SELECT idx_id FROM tp_index AS i WHERE (item_Text LIKE' @var3 = ''%london%' OR item_Text LIKE '%solicitor%'' @var4 = ' ) AND (subscription_id = 1000))) GROUP BY d.item_id ORDER BY d.item_id DESC' @varfull = @var1+@var2+@var3+@var4
View Replies !
Building Dynamic SQL Query Strings
Let me start by asking that no one try to convince me to use Stored Procs. The examples below are a lot more simplistic then my real world code and it just gets too complicated to try to manage the quantity of SPs that I would need. I have an application that displays a lot of data and I've created a system for users to filter the data using checkboxlist controls, dropdown controls, etc. From this, I have a "core" query that selects the fields that display in my GridView. It has a base Select clause, From clause and Where clause. From this I then add more to the Where clause to apply these filter values. Here's an example "core" query: SELECT Profile.FirstName, Profile.LastName, Project.ProjectNameFROM Profile, ProjectWHERE Profile.ProjectCode = Project.ProjectCode From this if a user want's to only display profiles from NC, they could select that from the CBL and the query would be modified to: SELECT Profile.FirstName, Profile.LastName, Project.ProjectNameFROM Profile, ProjectWHERE Profile.ProjectCode = Project.ProjectCodeAND Profile.State IN ('NC') My code would add the last line above since the user specified that they only wanted NC profiles. This is very simple and I have this already going on with my application. Here's the problem. In order to accommodate all of the various filters, I have to inner join and left join a bunch of various tables. Many times I include tables that have no data to display or filter on and therefore impacts performance. Here's an example: SELECT Profile.FirstName, Profile.LastName, Project.ProjectNameFROM Profile, Project, AgentWHERE Profile.ProjectCode = Project.ProjectCodeAND Profile.AgentID = Agent.AgentID From the query above, I have included the Agent table that holds the agent's contact information. One of my filters allows the user to type in an agents name to find all profiles assigned to it. Here's what that would look like: SELECT Profile.FirstName, Profile.LastName, Project.ProjectNameFROM Profile, Project, AgentWHERE Profile.ProjectCode = Project.ProjectCodeAND Profile.AgentID = Agent.AgentIDAND Agent.Name = 'Smith, John' You can see now that it was necessary to have the Agent table already joined into the query so that when I used the agent name filter, it wouldn't crash out on me. The obvious thing would be to only include the Agent table when searching for an agent name. This is ultimately what I'm looking to do, but I need a solid method to go about doing this. Keep in mind that I currently have 16 tables in my "core" query and many of those are not needed unless the filters call for it. If anyone has any ideas on how to simplify this process I'm selcome to suggestions. We're using SQL 2000, but are looking to upgrade to SQL 2005, if that makes any difference. I know that the way I do table joins is compliant with SQL 2005 and I'm certainly open to suggestions that will make it forward compatible. This app is using .NET 2.0 and written in VB.NET. Thanks for any help!
View Replies !
Help With Parameterized Query Building Dataset
I have a class that works fine using the SQLDataReader but when I try and duplicate the process using a Dataset instead of a SQLDataReader it returnsa a null value. This is the code for the Method to return a datareader public SqlDataReader GetOrgID() { Singleton s1 = Singleton.Instance(); Guid uuid; uuid = new Guid(s1.User_id); SqlConnection con = new SqlConnection(conString); string selectString = "Select OrgID From aspnet_OrgNames Where UserID = @UserID"; SqlCommand cmd = new SqlCommand(selectString, con); cmd.Parameters.Add("@UserID", SqlDbType.UniqueIdentifier, 16).Value = uuid; con.Open(); SqlDataReader dtr = cmd.ExecuteReader(CommandBehavior.CloseConnection); return dtr; This is the code trying to accomplish the same thing with a Dataset instead. public DataSet organID(DataSet dataset) { Singleton s1 = Singleton.Instance(); Guid uuid; uuid = new Guid(s1.User_id); string queryString = "Select OrgID From aspnet_OrgNames Where UserID = @UserID"; SqlConnection con = new SqlConnection(conString); SqlCommand cmd = new SqlCommand(queryString, con); cmd.Parameters.Add("@UserID", SqlDbType.UniqueIdentifier, 16).Value = uuid; SqlDataAdapter adapter = new SqlDataAdapter(); adapter.SelectCommand = cmd; adapter.Fill(dataset); return dataset; } Assume that the conString is set to a valid connection string. The Singlton passes the userid in from some code in the code behind page ...this functionality works as well. So assume that the Guid is a valid entry..I should return a valid dataset but its null.
View Replies !
|
TRACKER
