Extract Parameters From SQL Statement
Jul 20, 2005
Hei,
I'm currently trying to write a program in C# that will allow users to
parametrize their queries.
For instance, I have a query like this:
SELECT * FROM Customers Where Region = @Region AND Gender > @Gender
How can I extract the Parameters names without using String
manipulation (which is not perfect since sql statements can also
contain '@' in LIKE clauses for example)
I tried this way
Dim comm As New OleDbCommand(SqlStatement, myCon)
comm.Prepare()
MsgBox(comm.Parameters.Count.ToString())
comm.Dispose()
myCon.Close()
but this always return me 0 (zero)
Do you have any idea on how to solve this generic problem?
Regars
Philippe Graca
View 3 Replies
ADVERTISEMENT
Jan 25, 2008
I am stuck on how to syntactically retrieve an output value (@ProdCount) from a stored procedure. The SPROC works fine: the value of @ProdCount appears correctly in the output window. However, I can't retrieve it in the DAL handler (value remains 0). Does anyone have an idea on how to properly extract the return value. TIA for any pointers.SPROC (abridged): ALTER PROCEDURE and_Store_GetProductsByProdCatID_SortPage (@ProdCatID INT,...@ProdCount INT OUTPUT
)
ASSELECT @ProdCount=(SELECT COUNT(*) FROM and_StoreProduct WHERE ProdCatID= @ProdCatID)DECLARE @SQL nvarchar(4000)SET @SQL = 'WITH tmpProd AS ( SELECT ROW_NUMBER() etc.. )SELECT ProdID, etc..FROM tmpProdWHERE Row BETWEEN etc..ORDER BY etc..'
EXECUTE(@SQL)RETURNDAL handler (abridged): Public Overloads Shared Function GetProductListByCatID(ByVal ProdCatID As Integer, ..., ByVal ProdCount As Integer) As List(Of Product) Dim productList As List(Of Product) = New List(Of Product) Try
Using con As New SqlConnection(ConfigurationManager.ConnectionStrings("conAnders").ConnectionString) Dim cmd As SqlCommand = New SqlCommand("and_Store_GetProductsByProdCatID_SortPage", con) cmd.CommandType = CommandType.StoredProcedure cmd.Parameters.AddWithValue("@ProdCatID", ProdCatID) '...
cmd.Parameters.AddWithValue("@ProdCount", ProdCount) ' Output parm. Add dummy value.
Dim objProduct As Product 'Temp Product.
con.Open()
Using myReader As SqlDataReader = cmd.ExecuteReader(CommandBehavior.CloseConnection) While myReader.Read() objProduct = New Product() With objProduct .ProdID = myReader.GetInt32(myReader.GetOrdinal("ProdID")) 'etc..
End With
productList.Add(objProduct)
End While Dim tmp As Object = cmd.Parameters("@ProdCount").Value ' <-- Not updated
myReader.Close() End Using End Using Catch ex As Exception Throw ' Pass up the error.
End Try Return productList End Function
View 8 Replies
View Related
Feb 12, 2015
I tried to extract the information in a field
Samples of the data:
"Current Date updated ( 12/31/2015 -> 12/31/2016 )"
"Status updated ( Open -> Should be Closed )"
From example 1 ==> need outputs of " 12/13/2015" and "12/31/2016";
example 2 --> need outputs of "Open" and "Should be Closed"
These are what all records look like:
1. It contains the word, "updated".
2. There is a space after the last character of updated. Then, there is a space before open parenthesis. Then, there is a space before getting the required word. Example: "updated ( xxxxx" (xxxxx is a required extract word.)
3. The entire word(s) should be captured before the "->" sign (after the open parenthesis)
4. The last part is to captured everything after the "->" but before the close parenthesis ")"
View 1 Replies
View Related
Jul 23, 2005
Is there a way in SQL Server 2000 to extract data from a table, such thatthe result is a text file in the format of "Insert Into..." statements, i.e.if the table has 5 rows, the result would be 5 lines of :insert into Table ([field1], [field2], .... VALUES a,b,c)insert into Table ([field1], [field2], .... VALUES d, e, f)insert into Table ([field1], [field2], .... VALUES g, h, i)insert into Table ([field1], [field2], .... VALUES j, k, l)insert into Table ([field1], [field2], .... VALUES m, n, o)Thanks in advance
View 5 Replies
View Related
Oct 7, 2006
Suppose I have a table with relations (REL). In this table there are relations between parent en child, so the columns are: Rel_Id, parent_Id, child_Id. Example:
1, A, B
2, A, C
3, A, D
4, B, E
5, C, F
6, G, H
7, G, I
8, H, J
I need a query which returns all relations from REL that are in the same tree as the input unit.
In the example, giving D as input unit, it should return relations 1, 2, 3, 4 and 5, because A, B, C, E and F are (in)directly related to D and belong therefore to the same tree.
Giving H as input, it should return relations 6, 7 and 8.
(with a tree, I mean that a parent can have 0, 1 or more children and a child belongs at most one parent.)
Thanks for your help in advance!
View 11 Replies
View Related
Aug 24, 2005
I have a sql that I want to execute with LIKE and parameters:I tried several options outlined that I found at http://aspnet101.com/aspnet101/tutorials.aspx?id=10%20but they all seem to return 0 records. When I try and execute my statement in Enterprise manager, it works fine.Code Snippet:...Dim sql as stringsql = "SELECT * FROM tblName WHERE First LIKE '%' + @fname + '%' AND Last LIKE '%' + @lname + '%'"Dim param(1) as sqlParametersqlParams(0) = New SqlParameter("@fname", SqlDbType.VarChar, 50)sqlParams(0).Value = Trim(fname.text)sqlParams(1) = New SqlParameter("@lname", SqlDbType.VarChar, 50)sqlParams(1).Value = Trim(lname.text)...Can anyone tell me if there is anything wrong with my code above?
View 8 Replies
View Related
May 27, 2008
Hi,
I have an issue with an OLE DB command and parameters. The statement I want to run starts with a WITH statement - when I take out the parameter it runs fine. When I take out the WITH statement and leave the parameter in it runs fine. When I run the statement in SSMS it runs fine.
With both the WITH statement and the parameter in it gives the error 'Syntax error, permission violation, or other nonspecific error'.
Has anyone seen this before?
View 2 Replies
View Related
Dec 7, 2006
The SQL for a dataset in ASP.NET 2.0 is as follows....
SELECT DISTINCT StudentData.StudentDataKeyFROM Student2Roster INNER JOIN StudentData ON Student2Roster.StudentDataRecID = StudentData.StudentDataRecIDWHERE (Student2Roster.ClassRosterRecID IN (@ClassRosterRecIDs)
ClassRosterRecIDs are giuds
At design time i use 'b2cf594d-908b-4c0c-a67f-6364899a4d42', '2e0b3472-d3f0-4a54-94af-bfc0a99525d9' as the parameter and it works in the designer but not at runtime.
At runtime I get the error Conversion failed when converting from a character string to uniqueidentifier.
If I leave the quotes off and only use 1 value like b2cf594d-908b-4c0c-a67f-6364899a4d42 it works.How can I use multiple guids as an IN parameter like 'b2cf594d-908b-4c0c-a67f-6364899a4d42', '2e0b3472-d3f0-4a54-94af-bfc0a99525d9'?
Thanks
View 2 Replies
View Related
Jul 12, 2007
Hi all of you,
I'd like to know how could I add parameters to CREATE TABLE from a Execute Sql Task.
Is it possible?
TIA
View 3 Replies
View Related
Jul 3, 2007
I have a report that is using a union statement to pull in data from two identical tables except that one is for current month, the other for archived data.
What I want to do is prompt the user once for a date and use the value to select from the right table. Since a sales date can only exist in one of the tables, one union will work, the other not.
But the report in prompting me for a parameter for each query....which is in Informix and the prompt is this: "?"
Is there anyway to force both halves of the query to see this as one parameter so the user is only prompted once?
Thanks
View 2 Replies
View Related
Aug 21, 2007
hello all, im trying to run a select statement using a parameter, but am having extreme difficulties. I have tried this about 50 different ways but i will only post the most recent cause i think that im the closest now than ever before ! i would love any help i can get !!!
Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs)
Dim pageID As StringpageID = Request.QueryString("ID")
TextBox13.Text = pageID 'Test to make sure the value was stored
SqlDataSource1.SelectParameters.Add("@pageID", pageID)
End Sub
....
<asp:SqlDataSource ID="SqlDataSource1" runat="server" ProviderName=System.Data.SqlClient ConnectionString="Data Source=.SQLEXPRESS;Initial Catalog=software;Integrated Security=True;User=something;Password=something" 'SelectCommand="SELECT * FROM Table1 WHERE [ClientID]='@pageID' ></asp:SqlDataSource>
The error that i am getting, regardless of what i put inside the ' ' is as follows:
"Conversion failed when converting the varchar value '@pageID' to data type int."
anyone have any suggestions ?
View 2 Replies
View Related
Jun 13, 2008
When I debug my code I see the string going into the parameter correclty, but the the delete statement doesnt work and I'm not sure why. Does this look ok? // Set up SqlCommand, connection to db, sql statement, etc.
SqlCommand DeleteCommand = new SqlCommand();
DeleteCommand.Connection = DBConnectionClass.myConnection;
DeleteCommand.CommandType = CommandType.Text;
// Store Primary Key photoID passed here from DeleteRows_Click
// in a parameter for DeleteCommand
SqlParameter DeletePrimaryKeyParam = new SqlParameter();
DeletePrimaryKeyParam.ParameterName = "@PhotoID";
DeletePrimaryKeyParam.Value = photoID.ToString();
// Insert new parameter into command object
DeleteCommand.Parameters.Add(DeletePrimaryKeyParam);
// Delete row, open connection, execute, close connection
DeleteCommand.CommandText = "Delete From Photo_TBL where PhotoID IN (@PhotoID)";
Response.Write(DeleteCommand.CommandText);
// DeleteCommand.Connection.Close();
DeleteCommand.Connection.Open();
DeleteCommand.ExecuteNonQuery();
DeleteCommand.Connection.Close();
View 9 Replies
View Related
Sep 30, 2015
I have a table :
Person(Id bigint , FirstName nvarchar(50) , LastName nvarchar(50),Address nvarchar(100))
and I have a sp for search data in "Person" table:
CREATE PROCEDURE SelectPerson
@First nvarchar(50) = NULL,
@Last nvarchar(50) = NULL,
@Address nvarchar(100) = NULL
[Code] ....
And there exists 500 record in Person.
I execute SelectPerson by this code:
"
DECLARE@return_value int
EXEC@return_value = [dbo].[SelectPerson ]
@First = 'ام',
@Last = NULL,
@Address= NULL
SELECT'Return Value' = @return_value
GO
"
but result don't has any rows.why?
View 9 Replies
View Related
May 30, 2006
Trying to insert into a history table. Some columns will come fromparameters sent to the store procedure. Other columns will be filledwith a separate select statement. I've tried storing the select returnin a cursor, tried setting the values for each field with a separateselect. Think I've just got the syntax wrong. Here's one of myattempts:use ESBAOffsetsgoif exists(select * from sysobjects where name='InsertOffsetHistory' andtype='P')drop procedure InsertOffsetHistorygocreate procedure dbo.InsertOffsetHistory@RECIDint,@LOB int,@PRODUCT int,@ITEM_DESC varchar(100),@AWARD_DATE datetime,@CONTRACT_VALUE float,@PROG_CONT_STATUS int,@CONTRACT_NUMBER varchar(25),@WA_OD varchar(9),@CURR_OFFSET_OBL float,@DIRECT_OBL float,@INDIRECT_OBL float,@APPROVED_DIRECT float,@APPROVED_INDIRECT float,@CREDITS_INPROC_DIRECT float,@CURR_INPROC_INDIRECT float,@OBLIGATION_REMARKS varchar(5000),@TRANSACTION_DATE datetime,@AUTH_USERvarchar(150),@AUTHUSER_LNAMEvarchar(150)asdeclare@idintinsert into ESBAOffsets..HISTORY(RECID,COID,SITEID,LOB,COUNTRY,PRODUCT,ITEM_DESC,AWARD_DATE,CONTRACT_VALUE,PROG_CONT_STATUS,CONTRACT_TYPE,FUNDING_TYPE,CONTRACT_NUMBER,WA_OD,PM,AGREEMENT_NUMBER,CURR_OFFSET_OBL,DIRECT_OBL,INDIRECT_OBL,APPROVED_DIRECT,APPROVED_INDIRECT,CREDITS_INPROC_DIRECT,CURR_INPROC_INDIRECT,PERF_PERIOD,REQ_COMP_DATE,PERF_MILESTONE,TYPE_PENALTY,PERF_GUARANTEE,PENALTY_RATE,STARTING_PENALTY,PENALTY_EXCEPTION,CORP_GUARANTEE,BANK,RISK,REMARKS,OBLIGATION_REMARKS,MILESTONE_REMARKS,NONSTANDARD_REMARKS,TRANSACTION_DATE,STATUS,AUTH_USER,PMLNAME,EXLD_PROJ,COMPLDATE,AUTHUSER_LNAME)values(@RECID,(Select COID from ESBAOffsets..Offsets_Master where RECID = @RECID),(Select SITEID from ESBAOffsets..Offsets_Master where RECID = @RECID),@LOB,(Select COUNTRY from ESBAOffsets..Offsets_Master where RECID =@RECID),@PRODUCT,@ITEM_DESC,@AWARD_DATE,@CONTRACT_VALUE,@PROG_CONT_STATUS,(Select CONTRACT_TYPE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select FUNDING_TYPE from ESBAOffsets..Offsets_Master where RECID =@RECID),@CONTRACT_NUMBER,@WA_OD,(Select PM from ESBAOffsets..Offsets_Master where RECID = @RECID),(Select AGREEMENT_NUMBER from ESBAOffsets..Offsets_Master where RECID= @RECID),@CURR_OFFSET_OBL,@DIRECT_OBL,@INDIRECT_OBL,@APPROVED_DIRECT,@APPROVED_INDIRECT,@CREDITS_INPROC_DIRECT,@CURR_INPROC_INDIRECT,(Select PERF_PERIOD from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select REQ_COMP_DATE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select PERF_MILESTONE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select TYPE_PENALTY from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select PERF_GUARANTEE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select PENALTY_RATE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select STARTING_PENALTY from ESBAOffsets..Offsets_Master where RECID= @RECID),(Select PENALTY_EXCEPTION from ESBAOffsets..Offsets_Master where RECID= @RECID),(Select CORP_GUARANTEE from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select BANK from ESBAOffsets..Offsets_Master where RECID = @RECID),(Select RISK from ESBAOffsets..Offsets_Master where RECID = @RECID),(Select REMARKS from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select OBLIGATION_REMARKS from ESBAOffsets..Offsets_Master whereRECID = @RECID),@MILESTONE_REMARKS,@NONSTANDARD_REMARKS,@TRANSACTION_DATE,(Select STATUS from ESBAOffsets..Offsets_Master where RECID = @RECID),@AUTH_USER,(Select PMLNAME from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select EXLD_PROJ from ESBAOffsets..Offsets_Master where RECID =@RECID),(Select COMPLDATE from ESBAOffsets..Offsets_Master where RECID =@RECID),@AUTHUSER_LNAME)select@@identity idgogrant execute on InsertOffsetHistory to publicgo
View 1 Replies
View Related
Nov 20, 2007
Hi there,
I am trying to use the ADO technology within MS Access 2000. Basically I'd like to use parameters in a command object to insert a new record and get its newly inserted ID.
But instead of it it returns error:
Run-time error '-2147217900 (80040e14)
Must declare the scalar variable "@ii_file"
Isn't this varaible (and all the rest of variables) declared by setting a parameter ".Parameters.Append .CreateParameter("@ii_file", adVarChar, adParamInput, 255, Me.cbo_ii)
"?
I'd like to avoid using stored procedures in order to create the whole SQL statement from the client side.
Thanks!
Darek
Public Sub save_import()
Dim rs As ADODB.Recordset, cmd As ADODB.Command, rec_affected As Long
Set cmd = New ADODB.Command
With cmd
.ActiveConnection = ado_conn 'an existing connection
.CommandType = adCmdText
.CommandText = "insert into import_main (ii_file, id_file, oi_file, od_file, folder, import_desc, id_client,basis_of_study, id_is, project_leader, urisk_model_basis, as_at_date, extent_benchamark) " & _
"values (@ii_file, @id_file, @od_file, @od_file, @folder, @import_desc, @id_client, @basis_of_study, @id_is, @project_leader, @urisk_model_basis, @as_at_date, @extent_benchmark) " & _
"select @id_im = @@identity"
.Parameters.Append .CreateParameter("@id_im", adInteger, adParamOutput)
.Parameters.Append .CreateParameter("@ii_file", adVarChar, adParamInput, 255, Me.cbo_ii)
.Parameters.Append .CreateParameter("@id_file", adVarChar, adParamInput, 255, Me.cbo_id)
.Parameters.Append .CreateParameter("@oi_file", adVarChar, adParamInput, 255, Me.cbo_oi)
.Parameters.Append .CreateParameter("@od_file", adVarChar, adParamInput, 255, Me.cbo_od)
.Parameters.Append .CreateParameter("@folder", adVarChar, adParamInput, 1000, Me.txt_folder)
.Parameters.Append .CreateParameter("@import_desc", adVarChar, adParamInput, 255, Me.txt_import_desc)
.Parameters.Append .CreateParameter("@id_client", adBigInt, adParamInput, Me.cbo_client)
.Parameters.Append .CreateParameter("@basis_of_study", adVarChar, adParamInput, 255, Me.txt_basis_of_study)
.Parameters.Append .CreateParameter("@id_is", adSmallInt, adParamInput, Me.cbo_status)
.Parameters.Append .CreateParameter("@project_leader", adVarChar, adParamInput, 255, Me.txt_project_leader)
.Parameters.Append .CreateParameter("@urisk_model_basis", adVarChar, adParamInput, 1000, Me.txt_urisk_model)
.Parameters.Append .CreateParameter("@as_at_date", adDate, adParamInput, CDate(Me.txt_as_at_date))
.Parameters.Append .CreateParameter("@extent_benchmark", adVarChar, adParamInput, 1000, Me.txt_extent_benchmark)
.Parameters.Append .CreateParameter("@id_im", adInteger, adParamOutput)
.Execute rec_affected, , adExecuteNoRecords
If rec_affected > 0 Then
Me.cbo_import = .Parameters.Item("@id_im")
End If
End With
End Sub
View 3 Replies
View Related
Feb 23, 2007
In an OLE DB Source in an SSIS package, we are having difficulties using multiple parameters in a SQL statement.
Using a single '?' works fine, but I've read that when you want to map more than 1 parameter you should use 'Parameter0, Parameter1, etc'.
The problem is that when we use Parameter0 and Parameter1 and then try to map it, it says that the query contains no parameters.
Can anyone help with the correct way to use multiple parameters in a SQL query that's part of an OLE DB Source task?
Thanks,
Mike
View 15 Replies
View Related
Sep 29, 2005
Hi to allI wish to be able to have a standard select statement which hasadditional fields added to it at run-time based on suppliedparameter(s).iedeclare @theTest1 nvarchar(10)set @theTest1='TRUE'declare @theTest2 nvarchar(10)set @theTest2='TRUE'selectp_full_nameif @theTest1='TRUE'BEGINother field1,ENDif @theTest2='TRUE'BEGINother field2ENDfrom dbo.tbl_GIS_personwhere record_id < 20I do not wish to use an IF statement to test the parameter for acondition and then repeat the entire select statement particularly asit is a UNIONed query for three different statementiedeclare @theTest1 nvarchar(10)set @theTest1='TRUE'declare @theTest2 nvarchar(10)set @theTest2='TRUE'if @theTest1='TRUE' AND @theTest2='TRUE'BEGINselectp_full_name,other field1,other field2from dbo.tbl_GIS_personwhere record_id < 20ENDif @theTest1='TRUE' AND @theTest2='FALSE'BEGINselectp_full_name,other field1from dbo.tbl_GIS_personwhere record_id < 20END......if @theTest<>'TRUE'BEGINselectp_full_namefrom dbo.tbl_GIS_personwhere record_id < 20ENDMake sense? So the select is standard in the most part but with smallvariations depending on the user's choice. I want to avoid risk ofbreakage by having only one spot that the FROM, JOIN and WHEREstatements need to be defined.The query will end up being used in an XML template query.Any help would be much appreciatedRegardsGIS Analyst
View 2 Replies
View Related
May 1, 2007
Is there a way to retreive the SQL Statement with the values from the parameters merged together? I know how to retreive the SQL Select Statement and the parameters separately but I need to retreive the final SQL.
For example:
SELECT name FROM employee WHERE id = @id
I would like to retreive from SQLDataSource
SELECT name FROM employee WHERE id = 1
Thank you
View 4 Replies
View Related
Mar 9, 2004
I have a user login scenario where I would like to make sure that they not only exist in the user table, but also make sure there account is "verified" and "active". I'm trying to return 3 output parameters. UserID, verified, active. Is this possible?
Do I need just a select statement to do this? What is the difference between the output and select statements?
Thanks in advance.
View 1 Replies
View Related
Jul 13, 2015
I need to run a different select statement based on the value of a parameter. Visual studio is not liking how I am formatting it.
SELECT mc.CtrlID, mc.PrtfID, mc.PrtfTyp, mc.City, mc.STATE, mc.U_O_AND_M, mc.CHBBal, mc.IRG_Number, mc.PrtfName, mc.LnID, mc.UPB, mc.UPB_Master_Loan, aa.Prtf_FNMA_FHLMC_Other , aa.ReportingSummar
FROM vw_XXX_GIAC mc
INNER JOIN vw_XXX_InvestorMatrix aa on mc.CtrlID = aa.CtrlID
WHERE
case when
@PortfolioID=1 THEN aa.Prtf_FNMA_FHLMC_Other ='FNMA'
@PortfolioID=2 THEN aa.Prtf_FNMA_FHLMC_Other ='FHLMC'
@PortfolioID=3 THEN aa.Reporting Summar ='FLOW'
END
View 2 Replies
View Related
May 18, 2008
I am having trouble returning the correct record with my stored procedure.
my problem is that i don't know how to structure the sql statement to do the following:
given a set of records that have the same loankey, i need to
1. find the record that has most recent date (lockExprDt)
2. for all records with that date, find the highest Lock Number (LockNo)
3. for the all the records with that date and that LockNo, find the highest extension number (Ext)
currently my sql statement returns a record that has the most recent date. i don't now how to write the sql to further define my query to return the record that has the most recent date with highest lock number, and finally the highest extension number.
any suggestions as to what i am doing wrong. below is my slq statement. please note that i need to add the sql that will query for the max LockNo, and max Ext. Any help is greatly appreciated. thx!
select a.loankey, a.lockrate, a.investor, a.price, a.ext, a.cost, a.lockno, a.lockstatus , CASE WHEN CONVERT(CHAR(8),a.lockdate,10)='12:00:00 AM' THEN NULL ELSE CONVERT(CHAR(8),a.lockdate,10) END as 'LockDate' , CASE WHEN CONVERT(CHAR(8),b.lockExprDt,10)='12:00:00 AM' THEN NULL ELSE CONVERT(CHAR(8),b.lockExprDt,10) END as 'LockExprDt' , Case WHEN CONVERT(CHAR(8),b.lockExprDt,10)>= CONVERT(CHAR(8),GETDATE(),10) THEN datediff(day, CONVERT(CHAR(8),GETDATE(),10), CONVERT(CHAR(8),b.lockExprDt,10)) ELSE NULL END as 'Days'
from cfcdb..locktable ainner join (select loankey, max(lockExprDt) as lockExprDtfrom cfcdb..locktablegroup by loankey) bON a.loankey = b.loankey AND a.lockExprDt = b.lockExprDt
where a.loankey = @LoanKey
View 6 Replies
View Related
Apr 6, 2007
I have a table which has a field called Org. This field can be segmented from one to five segments based on a user defined delimiter and user defined segment length. Another table contains one row of data with the user defined delimiter and the start and length of each segment. e.g.
Table 1
Org
aaa:aaa:aa
aaa:aaa:ab
aaa:aab:aa
Table 2
delim
Seg1Start
Seg1Len
Seg2Start
Seg2Len
Seg3Start
Seg3Len
:
1
3
5
3
9
2
My objective is to use SSIS and derive three columns from the one column in Table 1 based on the positions defined in Table 2. Table 2 is a single row table. I thought perhaps I could use the substring function and nest the select statement in place of the parameters in the derived column data flow. I don't seem to be able to get this to work.
Any ideas? Can this be done in SSIS?
I'd really appreciate any insight that anyone might have.
Regards,
Bill
View 23 Replies
View Related
Oct 26, 2007
Hello,
Is there any way to run a parameterized statement against SQL when you have an unknown ammount of inputs.
Instead of doing the following:
FOR i as integer = 0 to topValuestrWhere &= " OR myColName='"& myArr(i) &"'"
NEXT
wash up the string and submit this to SQL, I want to do it with parameters. I'm calling a sproc in SQL and I would prefer just to send in a string like "1,4,42',45" in a parameter split that in SQL and run my query.The query would be something like "...WHERE myColname=1 OR myColname=2 OR myColName=3" I only want to declare one parameter and not use string concatenation as described above.It doesn't matter if there's a split function in SQL or not, as long as it solves my problem in an efficient manner. Cheers!/Eskil
View 3 Replies
View Related
Sep 21, 2006
HiI am using SQL 2005, VB 2005I am trying to insert a record using parameters using the following code as per MotLey suggestion and it works finestring insertSQL; insertSQL = "INSERT INTO Issue(ProjectID, TypeofEntryID, PriorityID ,Title, Area) VALUES (@ProjectID, @TypeofEntryID, @PriorityID ,@Title, @Area)"; cmdInsert SqlCommand; cmdInsert=new SqlCommand(insertSQL,conn); cmdInsert.Parameters.Add("@ProjectID",SqlDbType.Varchar).Value=ProjectID.Text; My query is how to detail with dates my previous code wasinsertSQL += "convert(datetime,'" + DateTime.Now.ToString("dd/MM/yy") + "',3), '";I tried the code below but the record doesn't save?string date = DateTime.Now.ToString("dd/MM/yy"); insertSQL = "INSERT INTO WorkFlow(IssueID, TaskID, TaskDone, Date ,StaffID) VALUES (@IDIssue, @IDTask, @TaskDone, convert(DateTime,@Date,3),@IDStaff)"; cmdInsert.Parameters.Add("IDIssue", SqlDbType.Int).Value = IDIssue.ToString();cmdInsert.Parameters.Add("IDTask",SqlDbType.Int).Value = IDTask.Text;cmdInsert.Parameters.Add("TaskDone",SqlDbType.VarChar).Value = TaskDoneTxtbox.Text;cmdInsert.Parameters.Add("Date",SqlDbType.DateTime).Value = date;cmdInsert.Parameters.Add("IDStaff",SqlDbType.Int).Value = IDStaff.Text;Could someone point to me in the right direction?Thanks in advance
View 3 Replies
View Related
Mar 12, 2008
Hi all,
From the "How to Call a Parameterized Stored Procedure by Using ADO.NET and Visual Basic.NET" in http://support.microsft.com/kb/308049, I copied the following code to a project "pubsTestProc1.vb" of my VB 2005 Express Windows Application:
Imports System.Data
Imports System.Data.SqlClient
Imports System.Data.SqlDbType
Public Class Form1
Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
Dim PubsConn As SqlConnection = New SqlConnection("Data Source=.SQLEXPRESS;integrated security=sspi;" & "initial Catalog=pubs;")
Dim testCMD As SqlCommand = New SqlCommand("TestProcedure", PubsConn)
testCMD.CommandType = CommandType.StoredProcedure
Dim RetValue As SqlParameter = testCMD.Parameters.Add("RetValue", SqlDbType.Int)
RetValue.Direction = ParameterDirection.ReturnValue
Dim auIDIN As SqlParameter = testCMD.Parameters.Add("@au_idIN", SqlDbType.VarChar, 11)
auIDIN.Direction = ParameterDirection.Input
Dim NumTitles As SqlParameter = testCMD.Parameters.Add("@numtitlesout", SqlDbType.Int)
NumTitles.Direction = ParameterDirection.Output
auIDIN.Value = "213-46-8915"
PubsConn.Open()
Dim myReader As SqlDataReader = testCMD.ExecuteReader()
Console.WriteLine("Book Titles for this Author:")
Do While myReader.Read
Console.WriteLine("{0}", myReader.GetString(2))
Loop
myReader.Close()
Console.WriteLine("Return Value: " & (RetValue.Value))
Console.WriteLine("Number of Records: " & (NumTitles.Value))
End Sub
End Class
//////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
The original article uses the code statements in pink for the Console Applcation of VB.NET. I do not know how to print out the output of ("Book Titles for this Author:"), ("{0}", myReader.GetString(2)), ("Return Value: " & (RetValue.Value)) and ("Number of Records: " & (NumTitles.Value)) in the Windows Application Form1 of my VB 2005 Express. Please help and advise.
Thanks in advance,
Scott Chang
View 29 Replies
View Related
Oct 29, 2013
I have a SSRS report with four parameters,and I want to be able to enter information for two of the parameters and run the report opposed to all four of them. However, when I select allow blanks and only select the parameters that I want to run the report by, the report come back blank..Essentially, I want to be able to the run report by different parameters without having to enter information for all parameters at the same time.
View 2 Replies
View Related
Apr 27, 2006
Hi,
I have an app in C# that executes a query using SQLCommand and parameters and is taking too much time to execute.
I open a SQLProfiler and this is what I have :
exec sp_executesql N' SELECT TranDateTime ... WHERE CustomerId = @CustomerId',
N'@CustomerId nvarchar(4000)', @CustomerId = N'11111
I ran the same query directly from Query Analyzer and take the same amount of time to execute (about 8 seconds)
I decided to take the parameters out and concatenate the value and it takes less than 2 second to execute.
Here it comes the first question...
Why does using parameters takes way too much time more than not using parameters?
Then, I decided to move the query to a Stored Procedure and it executes in a snap too.
The only problem I have using a SP is that the query can receive more than 1 parameter and up to 5 parameters, which is easy to build in the application but not in the SP
I usually do it something like
(@CustomerId is null or CustomerId = @CustomerId) but it generate a table scan and with a table with a few mills of records is not a good idea to have such scan.
Is there a way to handle "dynamic parameters" in a efficient way???
View 1 Replies
View Related
Mar 7, 2007
Hello:
I just recently bumped into this problem and I think I know what's causing it. This is the setup:
Report Parameters: FromDate, ToDate, DivisionalOffice, Manager, SalesRep
dsCalendarEvents Parameters: FromDate.Value, ToDate.Value, DivisionalOffice.Value,
dsDivisions Parameters: N/A
dsManager Parameters: DivisionalOffice.Value
dsSalesRep: DivisionalOffice.Label
When I query the ReportServices WS and scan the parameter dependencies for SalesRep it says there are four dependencies: FromDate, ToDate, DivisionalOffice and Manager!!!
If I change "dsSalesRep" to use "DivisionalOffice.Value" the ReportingServices WS parameter dependency scan returns only one dependency for "SalesRep" parameter!!!( This is the correct behavior )
Has anybody seen this behavior and more importantly, is there a work around?
Regards,
View 3 Replies
View Related
Feb 17, 2015
I have the following xml:
<MachineRepostitoryDS xmlns="http://Testing.com/Deployment/MachineRepostitoryDS.xsd">
<Components>
<Environment>QA400</Environment>
<HostName>HostTesting</HostName>
<Component>EventProcessorService</Component>
<CompStatus>OK. 17 Testing assemblies found</CompStatus>
<Version>4.0.24.0</Version>
<Path>D:TestingEventProcessor.WinService</Path>
</Components>
I would like to extract the version value from it (in the case above it's- 4.0.24.0).
View 3 Replies
View Related
Mar 15, 2007
Hello All,
I have a table that has the first name and last name together in one field (stupid I know). I want to extract the first name from the field and copy it into another field names, well, firstname. I figure it might be possible since there is a comma after the last name. But how can I do this?
So for:
STRNAME
-------------
adams, john
-------------
washington, michael
-------------
I want it to be like this
STRNAME | STRFIRSTNAME
---------------------------
adams | john
---------------------------
washington | michael
So I guess there would be two parts to the statement:
1. First I need to select or trim the strname field to the comma. BUT I don't want to just trim it- I want to copy it.
2. The next thing I need to do is to copy the lastname (the part that I trimmed) into a new field which I created called lastname.
Any help would be greatly appreciated!
View 10 Replies
View Related
Nov 14, 2007
I posted this a while ago with no luck and I'm going to try it again:
I have a column called strlastname with contains the firstname and last name of a client. I want to separate out the first name and last name into into two separate fields. I figured maybe I can do it easier if I copied the column into the table. So now I have a column called strfirstname that contains the first name and the last name and a colmn called strlastname that contains the first name and last name. The lastname is seperated from the first name with a comma like this:
doe, john
any guesses?
View 1 Replies
View Related
Apr 17, 2008
Hello I have seperated 1 name 2 name and 3 names. useing conditional split searching spaces in between names.
but while taking only one name in dervied colum what should i enter to get it.
i tried the same expression that i used in 2 namees and 3 names for first_name.
but its giving me error is there adifferent way to get one name.
le tme know
View 6 Replies
View Related
