SQL Pass Varchar(max) Value To A Parameter

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


ADVERTISEMENT

How To Pass Pass The Parameter In SQL Command In SSIS Package

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

How To Pass A GUID String To A Varchar In SQL Stored Procedure

Aug 26, 2007

In my .NET app I have a search user control.  The search control allows the user to pick a series of different data elements in order to further refine your display results.  Typically I store the values select in a VarChar(5000) field in the DB.  One of the items I recently incorporated into the search tool is a userID (GUID) of the person handling the customer.  When I go to pass the selected value to the stored proc  

objUpdCommand.Parameters.Add("@criteria", SqlDbType.VarChar).Value = strCriteria;
I get: Failed to convert parameter value from a String to a Guid.
Ugh!  It's a string, dummy!!!   I have even tried:
objUpdCommand.Parameters.Add("@criteria", SqlDbType.VarChar).Value = new Guid(strCriteria).ToString();
 and

objUpdCommand.Parameters.Add("@criteria", SqlDbType.VarChar).Value = new Guid(strCriteria).ToString("B");
but to no avail.  How can I pass this to the stored proc without regard for it being a GUID in my app?

View 1 Replies View Related

How Can I Pass A Parameter To Use In An IN

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

Pass Parameter To SP

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

How Do I Pass A Parameter To A DTS ?

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

Cannot Pass Parameter Via URL

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

How To Pass Mdx Parameter

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

How To Pass Parameter

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

How Can I Pass NULL To A Parameter

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

Pass A Parameter To A SqlDataSource

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

T-SQL: Is It Possible To Pass A Table As Parameter?

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

Pass Name Of Table As Parameter To UDF

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

Pass A Parameter To A Trigger

Sep 15, 2005

Hello,
 
Is there a way to pass a parameter to a trigger?
Thanks,

View 1 Replies View Related

Pass Through Query With Parameter???

Dec 13, 2000

I have the following function that is run when a person
enters a value, "Cell", in an access form. I am trying to
execute the pass through query in SQL Server but i keep
getting the following message:

"The microsoft jet database engine cannot find the input
table or query 'CellDefs'. Make sure it exists and its name
is spelt correctly."


There is nothing wrong with the table name.
I cant understand what the problem is ?????

Can anyone tell me how to execute a query that passes
a parameter from access to Sql server.

Thanks,
Ruaidhri



Public Function GetData(Cell As String)
Dim dbsCurrent As Database
Dim qd1 As QueryDef
Dim SQL As String
Dim rs As Recordset
Dim qdf As QueryDef
Set dbsCurrent = CurrentDb

Set qd1 = dbsCurrent.CreateQueryDef("")
With qd1
.Connect = "ODBC;DSN=MISChief;SERVER=Janeway;UID=sa;PWD=;DATA BASE=MIS"
SQL = ""
SQL = SQL + "INSERT INTO TableX SELECT A.Name AS CellCode, C.FinderNumber"
SQL = SQL + "FROM (CellDefs AS A INNER JOIN Calls AS B ON "
SQL = SQL + "A.CellDef_id = B.CellDef_id) INNER JOIN Finders AS C ON "
SQL = SQL + "B.Finders_id = C.Finders_id "
SQL = SQL + "WHERE A.Name LIKE '" & Cell & "' "
SQL = SQL + "ORDER BY C.FinderNumber"
.ReturnsRecords = False
.ODBCTimeout = 2400

dbsCurrent.Execute (SQL)
End With
dbsCurrent.Close
End Function

View 1 Replies View Related

Pass IN Clause Of Where As Parameter

Sep 29, 2005

HI

I want to Run a Dynamic SQL in which i need to pass IN parameters.

DECLARE @QUERY varchar(100)
DECLARE @Paremeter varchar(10)
SET @Parameter = '1,2'
SET @QUERY='SELECT * FROM TABLE_NAME WHERE COLUMN_NAME IN (' + @parameter + ')'

But this causes an error that cant convert varchar string to int. Please dont suggest to avoid Dynamic Sql. I have to use it.

View 1 Replies View Related

Pass A Parameter To A Trigger?

Oct 18, 2004

The application developers came up with an interesting requirement that I’d like some help with.

They want to be able to somehow pass a parameter to the trigger that is fired when you do something to a table. The parameter would control what logic is executed within the trigger.

The two solutions that I proposed, but were found unacceptable by them were:
1.Have parameter as a column within the table itself.
2.Don’t use Update, Delete, Insert but instead use a stored procedure.

Thoughts are welcome.

Thanks.

View 7 Replies View Related

How Could I Pass A Parameter To DTS Package?

Mar 2, 2006

I build a DTS package to get data from Oracle.
The data I want may be yesterday, may be two days ago or may be others. So I need a date parameter to tell the package how to get the data. But I do not know how to do it.

View 1 Replies View Related

Is There Is Away To Pass Parameter To Sql Script

Mar 31, 2004

Is there is a way to pass parameters to pass parameters to sql script during command line exeution with ISQL or Osql

View 11 Replies View Related

UDF - Can Pass A Table As Parameter?

Jan 20, 2014

I have several tables which are all identical in columns and datatypes. I want to create a function which does some calculations on this tables.

Since I just want to create one function I Need to specify the table on which the function should work on. That means i want to pass the table Name as a Parameter to the function.

Is that possible?

View 6 Replies View Related

How To Pass Parameter To DTS Package

Mar 27, 2008

I am new to the SSIS.

For DTS package of sql server 2000, I can call a DTS package from stored procedure. The command is:

dtsrun /E /SMyServer /NMyDTS /Wtrue /A Parameter1:3= 'Test'

Does anyone know, how do I do the similar thing from SSIS environment.

1) How to call a SSIS package from Stored Procedure?
2) How do I pass parameter to the SSIS package?

Thanks everyone.

View 1 Replies View Related

How To Pass A Parameter To A View?

Jul 20, 2005

Hi Everyone,Is there an easy way that I could pass a filter (variable) unto a viewvia stored procedures? I would appreciate your suggestions.Benito

View 3 Replies View Related

Pass A Non-sql Parameter To Report

Jul 2, 2007

I have a value I need to show on every report so I want to create a report parameter. It appears the only way for a report to have a parameter is for it to be a variable in the SQL WHERE clause. This value is not in the database so can't be retrieved from SQL.

How can I pass this value to the report and display it in a textbox in the footer?

Thanks.

View 1 Replies View Related

Pass ToggleState As Parameter

Jun 27, 2007

Hi,

I've developed a matrix report with two row groups. The second is visible or hidden depending on the toggle status of the first. I have set the navigation properties of the detail field to display a report of the underlying detail. I want to pass the current toggle state of the first row group to the details report as a boolean parameter. I this possible? I don't seem to have access to this property within the expression or code editors.

I'd be happy, as an alternative that implies the same thing, to pass the hidden / visible state of the second group.

Thanks,

Ian.

View 1 Replies View Related

Pass A Parameter To SSIS From .NET App

Jul 12, 2006

I'd like to pass a parameter value from a ASP.NET application to an SSIS package. My SSIS package pulls data from SQL and loads it a flat file based on the parameter value.

Is this possible?

View 3 Replies View Related

Pass Value Of Report Parameter From URL

Feb 23, 2007

Hello
I have an ASP.NET application where I can filter data from an SQL DB by certain criterias. Users are now also able to click on a link which redirects them directly to the SQL Server Reporting Services application.
What I'd like to have is a possibility to pass an object (as string) from my ASP.NET app to the textbox of the search criteria from the Reporting Services app.

Example:
- ASP.NET appl.: The user selected a FileId=84 -> then he clicks on the hyperlink to the SQL RS
- The SQL RS appl. opens and the textbox FileId is filled with "84"

it of course isn't a problem to pass the FileId to the hyperlink the user clicks on, but is it possible that the SQL RS appl. can somehow read it from the URL and pass it to the textbox?
Or does someone have another solution?

Thanks for your help!

View 4 Replies View Related

Transact SQL :: Pass Parameter Value To CTE

Jul 29, 2015

In some t-sql 2012 that I am using, I am using the following on 5 separate merge statements.

USING
(select LKC.comboID,LKC.lockID,LKC.seq,A.lockCombo2,A.schoolnumber,LKR.lockerId
from
[LockerPopulation] A
JOIN TST.dbo.School SCH ON A.schoolnumber = SCH.type

[Code] ...

What is different, is the value of LKC.seq = 1 as listed below. I am using 5 separate ctes and the only value that changes is the LKC.seq number being a value between 1 and 5. Thus can you pass a parameter value to the CTE that I just listed above? If so, show t-sql to accomplish this goal?

View 6 Replies View Related

Pass Parameter For Subscription

Oct 17, 2007

Hi
I want to create subscription for the report.
I have 1 date parameter. and i want the value of the date parameter as today s date.
How can i set value of the parameter as today's date.

I have tried using

getdate
today
now
getutcdate

didnt work.

any other option.

I need to create subscription either from report manager or from management studio

View 1 Replies View Related

Pass Multi Value As Parameter

Jun 1, 2007

Hi All,

Is it possible to pass a multi value parameter from one report to another using the "jump to report" feature?

ie;



I have report A that has a mutli value parameter (para1). This report has a link with the action property set to jump to another report(Report B). Report B requires the same multivalue parameter. I have tries setting the parameter in the interface as well as trying the join function. Nothing seems to pass the values correctly.



regards

View 3 Replies View Related

How To Pass Parameter Value To Report From Url

Jul 17, 2007

I have a report in SSRS 2005. I wish to pass a parameter called nIndex from the calling WinApp.



http://localhost/Reports/Pages/Report.aspx?ItemPath=%2fMy-Reports%2fPrimarySuppliers&rs:Command=Render&rc: parameters=false&nIndex=82896



even tried the following url:

http://localhost/Reports/Pages/Report.aspx?ItemPath=%2fMy-Reports%2fPrimarySuppliers&nIndex=82896



I tried to use the above url. The report doesn't pick the correct value of nIndex that I am passing as query string.



In Report Parameters page: nIndex is defined as a string parameter, marked as hidden, that allows nulls, and the default value is null.



I catch the command execution from SQL Profiler, and I always see the original value of 1, even though I am now passing 82896. I had initially given 1 as default value, when testing, now I need to pass this new value to get the latest data. I have built and deployed the report several times since then.

Any help is highly appreciated. Thanks.

View 3 Replies View Related

Can't Pass Parameter - RsAccessDenied

Feb 24, 2006

I am having a problem passing a parameter. When it gets to the last line of code where I set the parameters, I get this error. Can anyone help?


The permissions granted to user 'NT AUTHORITYNETWORK SERVICE' are insufficient for performing this operation. (rsAccessDenied)



Here is my code:


ReportParameters(0) = New ReportParameter("ProjectKey", 262)
rv.ShowParameterPrompts = False
rv.ServerReport.ReportServerUrl = u.ConvertFromString("http://localhost/ReportServer/")
rv.ServerReport.ReportPath = "/Test/Duplicate_MIU_Report"
rv.ServerReport.SetParameters(ReportParameters)

View 7 Replies View Related

Pass Parameter To SSIS

Feb 25, 2007

I have a SSIS package that imports (appends) data to a SQL table from an ODBC source. This package is run from within a VS project.

I'd like to pass a parameter to this package to limit the records it imports. I have seen some discussion of this but I need a detailed step-by-step or a good working example. Specifically:

1. How to get the parameter to the package to begin with. The VB function that runs the SSIS only passes parameters to the stored procedure that executes the SSIS.

2. How to refer to the parameter value in the SQL statement of the data reader object in the SSIS package. Instead of saying 'Select * from table1' it needs to say 'Select * from table1 WHERE field1 = @passedparam'

View 5 Replies View Related

Pass User Name As A Parameter In A Query

May 13, 2007

just getting started with my first db driven web project...
I am using a MySql database with membership and roles and got that working fine. I have tables containing details of courses that users are enrolled on etc. and want to display a list of courses for the user that is signed in, so he can continue lessons on the one of his choice.
How do I pass the users name to the database query for a DataList control. So far I have tried lots of variations of the following:
<asp:SqlDataSource ID="dsCourses" runat="server" ConnectionString="<%$ ConnectionStrings:xxx %>"    ProviderName="<%$ ConnectionStrings:xxx.ProviderName %>"    SelectCommand="SELECT c.CourseName FROM courses c, enrolments e        WHERE c.CourseID=e.CourseID AND e.Username='<% =User.Identity.Name %>'"></asp:SqlDataSource>
<asp:DataList ID="DataList1" runat="server" DataSourceID="dsCourses">    <ItemTemplate>        <asp:HyperLink ID="HyperLink1" runat="server" NavigateUrl="Lesson.aspx">'<%# Eval("CourseName") %>'</asp:HyperLink><br />    </ItemTemplate></asp:DataList>&nbsp;</p>
However, the <% =User.Identity.Name %> doesn't work here (but it does elsewhere on the page) - not sure why?? The code works fine if I hard code a user name into the select statement.
Suggestions pleeeeeese!!
 

View 1 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved