Pass A Value
Jul 20, 2005
is it possible to do:
(A)
declare @numberofitems Int
@numberofitems = select max(itemorder)
from store, department, etc.
and pass the @numberofitems to a #tempStore table, like:
(B)
(store, department, @numberofitems,...)
I got itemorder but not the number of items in each department
Alex
--
Sent by 3 from yahoo part from com
This is a spam protected message. Please answer with reference header.
Posted via http://www.usenet-replayer.com/cgi/content/new
View 2 Replies
ADVERTISEMENT
Jul 31, 2006
Hi
We already used Oracle Datasatage Server the following Query statement for Source and Lookup.here there is parameter maping in the SQl Statement . How can achive in SSIS the Folowing Querystatment?
Query 1: (source View Query)
SELECT
V_RDP_GOLD_PRICE.GDR_PRODUCT_ID, V_RDP_GOLD_PRICE.ASSET_TYPE, V_RDP_GOLD_PRICE.PREFERENCE_SEQ, V_RDP_GOLD_PRICE.RDP_PRICE_SOURCE, TO_CHAR(V_RDP_GOLD_PRICE.PRICE_DATE_TIME,'YYYY-MM-DD HH24:MI:SS'), TO_CHAR(V_RDP_GOLD_PRICE.REPORT_DATE,'YYYY-MM-DD HH24:MI:SS'), V_RDP_GOLD_PRICE.SOURCE_SYSTEM_ID
FROM
V_RDP_GOLD_PRICE V_RDP_GOLD_PRICE
WHERE
REPORT_DATE = (select max(report_date) from V_RDP_GOLD_PRICE where source_system_id = 'RM' )
Query 2: (look up )
SELECT
GDR_PRODUCT_ID,
TO_CHAR(MAX(PRICE_DATE_TIME),'YYYY-MM-DD HH24:MI:SS') ,
TO_CHAR(REPORT_DATE,'YYYY-MM-DD HH24:MI:SS')
FROM
V_RDP_GOLD_PRICE
where
GDR_PRODUCT_ID = :1 and
report_date = TO_DATE(:2,'YYYY-MM-DD HH24:MI:SS') AND
PRICE_DATE_TIME BETWEEN TO_DATE(:2,'YYYY-MM-DD HH24:MI:SS') - 7) AND TO_DATE(:2,'YYYY-MM-DD HH24:MI:SS')
GROUP BY GDR_PRODUCT_ID, TO_CHAR(REPORT_DATE,'YYYY-MM-DD HH24:MI:SS')
please anyone give the sample control flow and how to pass the parameter?
Thanks & regards
Jeyakumar.M
View 1 Replies
View Related
Nov 24, 2004
Is it possible from sql to use SAS as an object? And if so how?
I was recently tasked with Data Profiling and sitting next to me is a SAS developer, who just has to run a couple of functions, to do what has taken me days (and I haven't finished!). If I could call out to SAS and run the SAS functions it would be great. I have looked on the net and can see SAS calling out to SQL but not the other way round. Technically I'm probably not up to this but I don't want this to hold me back... so any help would be great.
View 4 Replies
View Related
Sep 26, 2005
okay..
Here is my first post from pass.
I am here on sunday getting ready for the first of 5 days of nothing but SQL Server
Every time i come to this thing i still get excited about the whole community thing.
who else from dbforums is going to be here? i would be interesting to put faces with those names.
in any event i will post interesting tidbits from the event if any come to mind. starting with the microsoft givaway of a copy of std edtion 2005 (when released) to any attendee. you get a coupon and you register a pin on a web site and they will send you a copy upon release. its a nice perk i guess, but msdn and the CTPs kind of take the savoir faire out of it.
oh well.
remember to post your name if you are attending.
View 14 Replies
View Related
Oct 19, 2006
Hey everyone I'm having trouble finding a way to pass a particular paramater, my main goal of this is to create a custom paging and sorting control for the DataList, but we wont worry about all the un-nessesarry code, here is what I am dealing with... Sub Page_Load()
Dim Conn As SqlConnection
Dim Query As String
Dim SqlComm As SqlCommand
Dim myDataReader As SqlDataReader
' Define connection object
Conn = New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
' Define query to retrieve main category values
Query = "SELECT [productMainCategory] FROM [User_Products] WHERE [productMainCategory] = @productMainCategory"
' Define command object
SqlComm = New SqlCommand(Query, Conn)
' Open connection to database
Conn.Open()
' Create DataReader
myDataReader = SqlComm.ExecuteReader()
' Iterate through records and add to array list
While myDataReader.Read()
IDList.Add(myDataReader("productMainCategory"))
End While
' Close DataReader and connection objects
myDataReader.Close()
myDataReader = Nothing
Conn.Close()
Conn = Nothing
' If page has not been posted back, retrieve first page of records
If Not Page.IsPostBack Then
Paging()
End If
End Sub
The Entire Point to this bit of code is to open a connection to the database find the category productsMainCategory, count all the fields with the Name 3D Art --> and save that number into an Array List where I will be using it later on in my code......The big old problem here is that it counts every single field in the productsMainCategory, Columb.........I need to figure out how to pass it a parameter so that it only counts my 3D Art --> fields, I have tried using this bit of code but nothing SqlComm.Parameters("@productMainCategory").Value = "3D Art -->"Does anyone have any ideas of how I might go about donig this?Thank You..
View 4 Replies
View Related
Mar 15, 2007
I cannot remember how to pass a value to a stored procedure. I would work this through but I am really running out of time, any help greatly appreciated. This is my stored procedure and I need to pass CompanyID from the code behind page in for the stored procedure @C_ID value.
PROCEDURE dbo.EditCompanyInfo @C_ID int, @CS_CompanyName nchar(100), @CS_City nchar(50) AS UPDATE tblCompanyInfo_Submit SET CS_CompanyName = @CS_CompanyName, CS_City = @CS_City WHERE C_ID = @C_ID RETURN
This is my aspx. page:
<asp:Content ID="Content2" ContentPlaceHolderID="MainContent" Runat="Server" > <asp:TextBox ID="TextBox1" runat="server"> </asp:TextBox> <br /> <asp:TextBox ID="TextBox2" runat="server"> </asp:TextBox> <br /> <asp:TextBox ID="TextBox3" runat="server"></asp:TextBox><br /> <asp:DetailsView ID="DetailsView1" runat="server" DataSourceID="SqlDataSource2" Height="50px" Width="125px"> <Fields> <asp:CommandField ShowEditButton="True" ShowInsertButton="True" /> </Fields> </asp:DetailsView> <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:ConnectionString2 %>" InsertCommand="CompanyInfoSubmit" InsertCommandType="StoredProcedure" OnInserted="SqlDataSource2_Inserted" SelectCommand="SELECT CS_CompanyName, CS_City FROM tblCompanyInfo_Submit WHERE C_ID = @CompanyID " UpdateCommand="EditCompanyInfo" UpdateCommandType="StoredProcedure"> <UpdateParameters> <asp:Parameter Name="CS_CompanyName" Type="String" /> <asp:Parameter Name="CS_City" Type="String" /> </UpdateParameters> <InsertParameters> <asp:Parameter Direction="ReturnValue" Name="ReturnValue" Type="Int32" /> <asp:Parameter Name="CS_CompanyName" Type="String" /> <asp:Parameter Name="CS_City" Type="String" /> </InsertParameters> </asp:SqlDataSource> <br /></asp:Content>
CODE BEHIND:
public partial class aaatest : System.Web.UI.Page{ int CompanyID; protected void Page_Load(object sender, EventArgs e) { if (!Page.IsPostBack) { DetailsView1.ChangeMode(DetailsViewMode.Insert); TextBox1.Text = "insert"; TextBox3.Text = Convert.ToString(CompanyID); } } protected void SqlDataSource2_Inserted(object sender, SqlDataSourceStatusEventArgs e) { { foreach (System.Data.SqlClient.SqlParameter param in e.Command.Parameters) { string RValue = Server.HtmlEncode(param.Direction.ToString()); if ( RValue == "ReturnValue" && Page.IsPostBack) { TextBox1.Text = Server.HtmlEncode(param.Value.ToString()); TextBox2.Text = "Return"; CompanyID = Convert.ToInt16(TextBox1.Text); TextBox3.Text = Convert.ToString(CompanyID); } } } }}
View 1 Replies
View Related
Aug 3, 2007
I have a stored procedure that I want to pass in a number of months and use it in my where caluse. I need to minus the number of months that as passed in by @TaskMonths.
But it is minusing days not months.
@TaskMonths intWHERE (tblTasks.Caller = @Caller) AND (tblTasks.DueDate BETWEEN CONVERT(varchar, GETDATE() - @TaskMonths, 101) AND GETDATE()) I apprecaite any help.
View 2 Replies
View Related
Oct 31, 2007
I'm trying to pass a comma delimited list of numbers to a parameter to use in an IN of my Where clause.
Dim MySqlParamSelected As New SqlParameter("@Selected", SqlDbType.Int) Cmd.Parameters.Add(MySqlParamSelected) MySqlParamSelected.Value = Session("intSelected")
WHERE tblSelected.Selected_ID IN (@Selected)
It will work if I only pass it one number (e.g. 78), but when I pass it more than one (e.g. 78,79) it fails.
Here is the error:
Msg 119, Level 15, State 1, Line 4Must pass parameter number 7 and subsequent parameters as '@name = value'. After the form '@name = value' has been used, all subsequent parameters must be passed in the form '@name = value'.
Does anyone know how I can correctly pass multiple numbers to use in my IN?
View 11 Replies
View Related
Jan 29, 2008
How do i pass a parameter to a stored proc using this command:
object SqlHelper.ExecuteScalar (SqlConnection connection, string spName, params object[] parameterValues)
let's say that my parameter name is @ID and the value is IDval ..
thanx
View 2 Replies
View Related
Oct 20, 2000
Good morning one and all,
Does any1 know if when A SQL pass through query is executed in access (to a SQL srv dbase) wether or not access will wait until that call is completed before running other steps (in an macro say). I don't think it does but would appreciate a more definitive answer.
TIA for any and all help
Gurmi
View 6 Replies
View Related
Oct 3, 2001
Anybody know if the PASS Conference in Orlando is going to happen or not? The website www.sqlpass.org is in a holding pattern saying that the conference is "POSTPONED," but I would have thought a post about the intention to resched or cancel the gathering would be in order.
Anyone know what's up?
View 1 Replies
View Related
May 19, 2004
Hi Guys: I am trying to get a top parameter dynamically but it does not seem to work
SELECT TOP
(SELECT COUNT(DISTINCT RegionID) AS COUNT
FROM Sales)
*
FROM Sales
but when I hard code the value it works
SELECT TOP
2
*
FROM Sales
any ideas...Thanks
View 6 Replies
View Related
Aug 8, 2007
Hello,
I placed a post regarding this issue previously but no success. So I thought I explain everything properly this time in a new post. Thanks
I have created a stored procedure which passes variables to the ssis package and then executes the package.
The two variables inside the ssis package are @FileName and @ConnectionPath
As you can see from the below stored procedure, xp_cmdshell is used to execute the package.
If only the first variable is used in the package and the @connectionPath variable is hardcoded inside the package then package runs fine.
Problem is in this particular call as you see below because @ConnectionPath is included.
The output of print is:
dtexec /f d:sysapplCEMSSISImportsTradesBaseProfiles2.dtsx /set Package.Variables[User::FileName].Properties[Value];"d:ApplDataCEMWorkingTempprofiles.csv"
/set Package.Variables[User::ConnectionPath].Properties[Value];"Data Source=servername1instancename1, 2025;Initial Catalog=CounterpartyExposure;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;"
Error is:
Error: 2007-08-08 08:46:01.29
Code: 0xC0202009
Source: BaseProfiles2 Connection manager "CounterpartyExposure"
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for ODBC Drivers" Hresult: 0x80004005 Description: "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".
End Error
if only the output is run in the query analyser then the error is:
The identifier that starts with 'Data Source=gblond088sjyMSQL_curves_DEV1, 2025;Initial Catalog=CounterpartyExposure;Provider=SQLNCLI.1;Integrated Security=SSPI' is too long. Maximum length is 128.
/*********************************************************************************
uspCEMTradeExecutePackage2 'd:sysapplCEMSSISImportsTradesStaticMappingOverride.dtsx',
'StaticMappingOverride.csv',
'Data Source=servername1instancename1, 2025;Initial Catalog=CounterpartyExposure;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;'
*********************************************************************************/
ALTER procedure [dbo].[uspCEMTradeExecutePackage2]
@FullPackagePath varchar(1000),
@FileName varchar(500),
@ConnectionPath varchar(1000)
as
declare @returncode int
declare @cmd varchar(1000)
declare @FilePath varchar(1000)
declare @FullFilePath varchar(1000)
set @FilePath = 'd:ApplDataCEMWorkingTemp'
set @FullFilePath = @FilePath + @FileName
print ' ----------- ' + @FileName
set @cmd = 'dtexec /f ' + @FullPackagePath + ' /set Package.Variables[User::FileName].Properties[Value];"' + @FullFilePath + '"'
set @cmd = 'dtexec /f ' + @FullPackagePath +
' /set Package.Variables[User::FileName].Properties[Value];"' + @FullFilePath + '"
/set Package.Variables[User::ConnectionPath].Properties[Value];"' + @ConnectionPath + '"'
print @cmd
set nocount on
begin try
exec @returncode = master..xp_cmdshell @cmd
end try
begin catch
exec @LastGoodVersionSP
DECLARE @msg nvarchar(200)
SET @msg = ('Error during execute package')
EXECUTE uspErrorReporter @msg
end catch
set nocount off
View 3 Replies
View Related
Dec 27, 2007
I have created this sp to and i cant figure out how to use the top 3 as a paramter. I want to be able to use any # like top1,2,3,4,5 etc. I not sure how to compose the syntax i have my sp below.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
-- =============================================
-- Author:<Author,,Name>
-- Create date: <Create Date,,>
-- Description:<Description,,>
-- =============================================
ALTER PROCEDURE [dbo].[sp_moveBackupTables]
@serverName varchar(50) --Parameter
AS
BEGIN
declare @tableName varchar(50) --Variable
--Declare Cursor
DECLARE backup_Cursor CURSOR FOR
select name from adventureworksdw.dbo.sysobjects
where name like 'MyUsers_backup_%' and xtype = 'U'
and name not in(select top 3 name from adventureworksdw.dbo.sysobjects
where name like 'MyUsers_backup_%' and xtype = 'U' order by name desc)
OPEN backup_Cursor
--Move to initial record in the cursor and set variable(s) to result values
FETCH NEXT FROM backup_Cursor
INTO @tableName
--Loop through cursor records
WHILE @@FETCH_STATUS = 0
BEGIN
--dynamically build create table
Declare @SQL varchar(2000)
Set @SQL = 'CREATE TABLE ' + @serverName + '.dbo.' + @tableName + '(
[Id] [numeric](18, 0) NOT NULL,
[Field1] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NOT NULL,
[Field2] [numeric](18, 0) NOT NULL,
[Field3] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Field4] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
[Field5] [nchar](10) COLLATE SQL_Latin1_General_CP1_CI_AS NULL,
CONSTRAINT [PK_1' + @tableName + '] PRIMARY KEY CLUSTERED
(
[Id] ASC
)WITH (IGNORE_DUP_KEY = OFF) ON [PRIMARY]
) ON [PRIMARY]'
EXEC(@SQL)
--Insert values into new dynamically created table
Declare @backupTableRecords varchar(1000)
Set @backupTableRecords = 'Insert into ' + @serverName + '.dbo.' + @tableName + ' SELECT * FROM AdventureWorksDW.dbo.'+ @tableName
exec(@backupTableRecords)
--Drop old table
Declare @dropTable varchar(200)
set @dropTable = 'drop table adventureworksdw.dbo.' + @tableName
exec(@dropTable)
--Move to the next record in the Cursor and set variable(s) value(s)
FETCH NEXT FROM backup_Cursor INTO @tableName
END
CLOSE backup_Cursor
DEALLOCATE backup_Cursor
END
View 2 Replies
View Related
Nov 7, 2007
HiWe still use SqlServer 2000 and DTSs.My question is: Can I pass a parameter to a DTS and if yes then how ?We would like to plan our screen like this.The user will input a parameter on the screen/form and then the DTS willbe activated using the value inputed.We do have how to work around it if parameters can't be passed but itwould make things a lot easier and straightforward if we could.Thanks !David Greenberg
View 2 Replies
View Related
Aug 8, 2007
Hello,
I placed a post regarding this issue previously but no success. So I thought I explain everything properly this time in a new post. Thanks
I have created a stored procedure which passes variables to the ssis package and then executes the package.
The two variables inside the ssis package are @FileName and @ConnectionPath
As you can see from the below stored procedure, xp_cmdshell is used to execute the package.
If only the first variable is used in the package and the @connectionPath variable is hardcoded inside the package then package runs fine.
Problem is in this particular call as you see below because @ConnectionPath is included.
The output of print is:
dtexec /f d:sysapplCEMSSISImportsTradesBaseProfiles2.dtsx /set Package.Variables[User::FileName].Properties[Value];"d:ApplDataCEMWorkingTempprofiles.csv"
/set Package.Variables[User::ConnectionPath].Properties[Value];"Data Source=servername1instancename1, 2025;Initial Catalog=CounterpartyExposure;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;"
Error is:
Error: 2007-08-08 08:46:01.29
Code: 0xC0202009
Source: BaseProfiles2 Connection manager "CounterpartyExposure"
Description: SSIS Error Code DTS_E_OLEDBERROR. An OLE DB error has occurred. Error code: 0x80004005.
An OLE DB record is available. Source: "Microsoft OLE DB Provider for ODBC Drivers" Hresult: 0x80004005 Description: "[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified".
End Error
if only the output is run in the query analyser then the error is:
The identifier that starts with 'Data Source=gblond088sjyMSQL_curves_DEV1, 2025;Initial Catalog=CounterpartyExposure;Provider=SQLNCLI.1;Integrated Security=SSPI' is too long. Maximum length is 128.
/*********************************************************************************
uspCEMTradeExecutePackage2 'd:sysapplCEMSSISImportsTradesStaticMappingOverride.dtsx',
'StaticMappingOverride.csv',
'Data Source=servername1instancename1, 2025;Initial Catalog=CounterpartyExposure;Provider=SQLNCLI.1;Integrated Security=SSPI;Auto Translate=False;'
*********************************************************************************/
ALTER procedure [dbo].[uspCEMTradeExecutePackage2]
@FullPackagePath varchar(1000),
@FileName varchar(500),
@ConnectionPath varchar(1000)
as
declare @returncode int
declare @cmd varchar(1000)
declare @FilePath varchar(1000)
declare @FullFilePath varchar(1000)
set @FilePath = 'd:ApplDataCEMWorkingTemp'
set @FullFilePath = @FilePath + @FileName
print ' ----------- ' + @FileName
set @cmd = 'dtexec /f ' + @FullPackagePath + ' /set Package.Variables[User::FileName].Properties[Value];"' + @FullFilePath + '"'
set @cmd = 'dtexec /f ' + @FullPackagePath +
' /set Package.Variables[User::FileName].Properties[Value];"' + @FullFilePath + '"
/set Package.Variables[User::ConnectionPath].Properties[Value];"' + @ConnectionPath + '"'
print @cmd
set nocount on
begin try
exec @returncode = master..xp_cmdshell @cmd
end try
begin catch
exec @LastGoodVersionSP
DECLARE @msg nvarchar(200)
SET @msg = ('Error during execute package')
EXECUTE uspErrorReporter @msg
end catch
set nocount off
View 1 Replies
View Related
Jul 11, 2007
Using SSIS foreach loop I get the files names inside a folder on the network.
How do I pass this variable i.e. file name to a stored procedure?
Thanks
View 9 Replies
View Related
May 22, 2008
I see a lot of posts asking for help with this type of problem but cannot find an answer that works. After 24 hours of trying, using examples from the Microsoft documentation, it's been utterly impossible to pass a parameter to a report via URL.
To make sure I'm not fighting more than one problem at the same time, I've stepped away from the production report I was trying to produce and created a simple report with a parameter named "p1". The only field on this test report is a textbox that displays the value of the parameter "p1". If I set the report up so that the user is queried for a value for "p1", the report works as expected -- the parameter is echoed back on the report.
The same thing can't be done via the URL. If I follow the docs and tack something like &rs:Command=Render&p1=hello onto the URL, I'm prompted for a value and/or told 'p1 is missing a value', depending on the visibility and default options I set for the parameter in the report definition.
I've tried every possible combination of settings I can think of for a parameter in the report definition, and every single one fails. Is this a task that's beyond RS abilities?
View 1 Replies
View Related
Oct 26, 2007
Hi,
Any body can help me on passing parameters in to an MDX query?
sample :
SELECT NON EMPTY { [Measures].[Gross Profit], [Measures].[Sales Amount] } ON COLUMNS, NON EMPTY { ([Employee].[Employee Department].[Employee].ALLMEMBERS ) } DIMENSION PROPERTIES MEMBER_CAPTION, MEMBER_UNIQUE_NAME ON ROWS FROM [Adventure Works] where [Employee].[Department Name].[engineering] CELL PROPERTIES VALUE, BACK_COLOR, FORE_COLOR, FORMATTED_VALUE, FORMAT_STRING, FONT_NAME, FONT_SIZE, FONT_FLAGS
instead of engineering i want to pass a parameter Department.
I created another dataset for it
WITH MEMBER [Measures].[ParameterCaption] AS '[Employee].[Department Name].CURRENTMEMBER.MEMBER_CAPTION' MEMBER [Measures].[ParameterValue] AS '[Employee].[Department Name].CURRENTMEMBER.UNIQUENAME' MEMBER [Measures].[ParameterLevel] AS '[Employee].[Department Name].CURRENTMEMBER.LEVEL.ORDINAL' SELECT {[Measures].[ParameterCaption], [Measures].[ParameterValue], [Measures].[ParameterLevel]} ON COLUMNS , [Employee].[Department Name].ALLMEMBERS ON ROWS FROM [Adventure Works]
Parameter is populating perfectly but how i edit my MDX to accept that parameter ?
Thanks in Advance...
View 6 Replies
View Related
Dec 1, 2007
hi,
I am new to sql server reporting services. When i pass more than 30 parameters ,my report is showing parameters which is not a desired one even though i am making showparameterPrompts property to false.
can any one tell me how to pass more no of parameters to the Report.
Advance thanks
Srinivas Govada
View 1 Replies
View Related
Sep 5, 2007
Hi Friends,
I have two reports, one master report & one child report. If I click a patricular value in Territory column, It should take me to the child report where only rows which matches with the territory should be displayed. Since I'm new to SSRS, Kindly some guide me.
Please note that, in the child report I should not get any dropdown boxes for selection.
Thanks in advance
Naveen
View 2 Replies
View Related
Dec 11, 2006
How can I pass NULL to a parameter, if now entry is made in the textbox?
Dim KeywordParam As New SqlParameter("@Keyword", Me.KeyWordText.Text)
MyCommand.Parameters.Add(KeywordParam)
View 1 Replies
View Related
Feb 28, 2007
I'm trying to pass my SqlDataSource a parameter that is defined in the code-behind file for the same page. I've tried the method below but it does not work. Is there a better way?SubmitForm.ascx page: <asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ connection string...... %>" SelectCommand="sp_CourseMaterialShipment_GetCourses" SelectCommandType="StoredProcedure"> <SelectParameters> <asp:Parameter DefaultValue="<% ProgramID %>" Name="programID" Type="Int32" /> </SelectParameters></asp:SqlDataSource> SubmitForm.ascx.vb page:Private ProgramID as string = "25"Public ReadOnly Property ProgramID() As String Get Return _ProgramID End GetEnd Property ThanksJason
View 2 Replies
View Related
Jul 7, 2007
Hi, I have a SQL table with a column field type varchar(max). It stores large amounts of text (actually HTML). I am trying to write an insert statement but cant figure out how to pass varchar(max) value to a parameter. I am using VB. The value is being passed from a formview control label. I get the following error: "Conversion from string "@manualtext" to type 'Integer' is not valid." from this line: dbComm.Parameters.Add("manualtext", SqlDbType.NText, "@manualtext") Here is what I have so far: ' Get HTML text file from formview control label Dim t As Label = CType(Me.FormView2.FindControl("manualtextlabel"), Label) ' get record number from querystring Dim param = CType(Request.QueryString("record"), String) ' connect to DB Dim myconnection As SqlConnection myconnection = New SqlConnection() myconnection.ConnectionString = _ ConfigurationManager.ConnectionStrings("MYCMSConnectionString").ConnectionString ' SQL statement Dim strSQL As String = "INSERT INTO dataentry " & _ "(project, manualtext) VALUES (@project, @manaultext)" Dim dbComm As New SqlCommand(strSQL, myconnection) ' create and pass value to parameters dbComm.Parameters.Add("project", SqlDbType.NVarChar, 50, "@project") dbComm.Parameters("project").Value = param.ToString() dbComm.Parameters.Add("manualtext", SqlDbType.NText, "@manualtext") dbComm.Parameters("manualtext").Value = t.Text Try myconnection.Open() dbComm.ExecuteNonQuery() Catch ex As Exception Response.Write(ex.Message) Response.End() Finally If myconnection.State = ConnectionState.Open Then myconnection.Close() End If End Try ' **************** Dim newloc = "viewdetail.aspx?record=" + param Response.Redirect(newloc) End Sub Thanks for the help.
View 4 Replies
View Related
Jul 11, 2007
VB ASP.NET 2.0
How do I get the Authenticated UserName passed to a Select Command of a data control ?
I'm not clear on how to get the logged in username from User.Identity.Name as a string or how to pass it to the Select Command of a control.
I've had some success with SelectParameters / ControlParameter's in the Master Control context, but otherwise I don't understand how to create a Parameter for use with the SQL.
I want to show the user data from a database, based on who is logged in .
Help, greatly appreciated.
Chris
View 8 Replies
View Related
Jul 24, 2007
How can I get the UserID to pass to the sql statement? The date is being passed but nothing else
Thank you.
My code:Public Function GetEmployeeByID(ByVal Today As Date, ByVal UserID As String) As SqlDataReaderDim dtToday As DateTime
dtToday = DateTime.Today
'Create connectionDim con As New SqlConnection(_connectionString)
'Create commandDim cmd As SqlCommand = New SqlCommand()With cmd
.Connection = con
.CommandText = "SELECT UserID FROM ATTTblAttendance WHERE UserID = @UserID And Today = " & dtToday" -->The UserID no showing up here
.CommandType = CommandType.Text
'Add Parameters.Parameters.AddWithValue("@UserID", UserID)
.Parameters.AddWithValue("@Today", Today)
End With
'Return DataReader
con.Open()Return cmd.ExecuteReader()
End Function
View 15 Replies
View Related
Sep 22, 2007
I have know this error but have completely forgotten how to take care of it. How do I pass the integer values to the SQL statement?
ERROR: Syntax error converting the nvarchar value 'Label' to a column of data type int.
WHERE (LinkInfo.L_State = @State) AND (LinkInfo.CG_ID > @CatIDLow) AND (LinkInfo.CG_ID > @CatIDHigh)
<SelectParameters> <asp:ControlParameter ControlID="TextBox1" Name="State" PropertyName="Text" /> <asp:ControlParameter ControlID="Label2" DefaultValue="0" Name="CatIDLow" PropertyName="Text" /> <asp:ControlParameter ControlID="Label3" DefaultValue="1899" Name="CatIDHigh" PropertyName="Text" /></SelectParameters>
CODE BEHIND
public partial class TopandBottomSelect : System.Web.UI.Page{ int CG_IDlower; int CG_IDupper; protected void Page_Load(object sender, EventArgs e) { } protected void RadioButtonList1_SelectedIndexChanged(object sender, EventArgs e) { if (RadioButtonList1.SelectedIndex == 0) { CG_IDlower = 0; CG_IDupper = 1900; Label1.Text = Convert.ToString(CG_IDlower); Label2.Text = Convert.ToString(CG_IDupper); } else { CG_IDlower = 1899; CG_IDupper = 3000; Label1.Text = Convert.ToString(CG_IDlower); Label2.Text = Convert.ToString(CG_IDupper); } GridView1.DataBind(); }}
Thank you
View 3 Replies
View Related
Dec 7, 2007
Can a table be passed as parameter to a T-SQL procedure or function?One can define a temporary table within a procedure, and the system then manages its lifetime, keeps it separate from other instances executing the same procedure, etc: - CREATE PROCEDURE dbo.name (@Parameter1, @Parameter2) AS BEGIN DECLARE @name TABLE (column list) etcPerfect: but I need to be able to have another [recursive] procedure process this table. How do I make it available to the second procedure? If I write EXEC procedure @Namean error message "Must declare the variable '@Name'" is produced. Within the 2nd procedure, an attempt to name a table within the parameter list results in another error message, "Incorrect syntax near the keyword 'TABLE'". If I can't pass it as argument/parameter, then how do you process a temporary table in another procedure? Do I pass a cursor to it? Or is it quite impossible?Regards, Robert Barnes.
View 6 Replies
View Related
Dec 10, 2007
Hi
I have a stored procedure which will do some thing like this
create proc xxx
(
@useralias varchar(32)
)
set @useralias = '''' + @useralias + '''';
select * from users where useralias = @useralias
EX: Select * from users where useralias = 'Santhosh'. But, when i give the query like this
EXec xxx santhosh
It gives the error message ...no column by name santhosh or Invalid column. So, i thought i will pass the same from the UI
EX:
string str = "'" + santhos + "'".
But, the probem with this is, it is not giving any results.
So, how do i get around with this prob?
Thanks!
Santhosh
View 2 Replies
View Related
Mar 25, 2008
I am trying to do the following. I am capturing the logged in username from Windows Authentication then stripping the domain name using the following codeProtected Sub SqlDataSource1_Selecting(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceSelectingEventArgs) Handles SqlDataSource1.Selecting Dim Bareusername e.Command.Parameters("@strusername").Value = My.User.Name Bareusername = My.User.Name.Replace("MyDomain", "") e.Command.Parameters("@LoginName").Value = Bareusername End Sub I use the derived value as the where clause in a select query Select * from Master Where AccountName = @LoginName. This allows a user to only see their own information in DetailsView1. This is working as desired. I have another column in the Master table named ID. This ID is the key that I need to reference to allow users to see and edit data in other tables. So I would like to be able to do something like Select ID from Master where AccountName = @LoginName and be able to pass this value to a Select statement against another table(Awards) for a DetailsView2 on the same page. Alternatively the value that I am trying to query is in the first row of DetailsView1. Is it possible to access the value in that field and pass it in the query for DetailsView2? This would be simpler if possible.I am not sure how to go about this and would appreciate any guidance that you could provide. I have found a lot on the web regarding this based on a selected value but not through the method that I am trying to use. Thanks in advance,Ken
View 4 Replies
View Related
Apr 12, 2008
Hi Friends,
Is it possible to pass the name of table to the User Defined Function in SQL and use it inside the function.
Any help would be appreciated.
Fazal.
View 4 Replies
View Related
Jun 4, 2008
hi i am using formview
i have a date field textbox which selects date from ajax calender
now my point is when i make this field blank
at that time in my gridview rather then displaying the date field blank
it shows me the value 1 Jan 1900
this is the default date of sqlserver which it returns when we leave the field ''
now when i pass the value null it automatically takes it as ''
in my class file my function is
public object checkdate(TextBox txtdate) { if (txtdate.Text == "") { return null; } else { return txtdate.Text; } }
in my cs file i wrote object checkemptydate = obj.checkdate(nextvaccinationdate);
string values = patientID + ",'" + vaccination + "',convert(varchar,'" + vaccinationdate + "',106)," + checkemptydate + ",'" + remarks + "'";
note that i even tried this too
string values = patientID + ",'" + vaccination + "',convert(varchar,'" + vaccinationdate + "',106)," + null + ",'" + remarks + "'";
and
string values = patientID + ",'" + vaccination + "',convert(varchar,'" + vaccinationdate + "',106),convert(varchar,'" + checkemptydate + "',106),'" + remarks + "'";
but nothing is working it passes '' not null
how could i solve this problem?
shweta
View 2 Replies
View Related
Jun 17, 2008
Hello,
I have two controls on a page. One is for cost and other for date.
Both the fields are not mandatory.
User can leave them blank and submit the page.
The problem is when page is submitted values are passed to a stored procedure but if user hasn't entered anything in these fields
then what should i pass to the store procedure.
data type in store procedure are
money for cost and datetime for date
Vinod Suri
View 3 Replies
View Related
