TSQL - Using More Than One Argument In LIKE Function

Aug 19, 2007

Hi guys,

I am using the LIKE function combined with a CASE WHEN to change a long list of words, but the list is too long...
Is there any posibility to insert more than one argument into one like function...?
Any other good ideas?
Below an example of the code I am using..

Thanks in advance,
Aldo.




Code Snippet
Case
WHEN JurnalTrans.DESCRIPTION LIKE '%myArgument01%' THEN 'Result'
WHEN JurnalTrans.DESCRIPTION LIKE '%myArgument02%' THEN 'Result'
WHEN JurnalTrans.DESCRIPTION LIKE '%myArgument03%' THEN 'Result'
WHEN JurnalTrans.DESCRIPTION LIKE '%myArgument04%' THEN 'Result'
ELSE ''
END AS 'Result'


View 2 Replies


ADVERTISEMENT

Argument Data Type Varchar Is Invalid For Argument 3 Of Convert Function

Jan 25, 2013

Where did i do wrong in conversion

original query
dateadd(hour, datediff(hour,CONVERT(VARCHAR(19),B.CreateDate,111 ),B.CreateDate)

I tried to use convert(varchar(50),Datediff,21)

Below is the exact code..

convert(varchar(50),dateadd(hour, datediff(hour,CONVERT(VARCHAR(19),B.CreateDate,111 ),B.CreateDate),21)

View 10 Replies View Related

Argument Data Type Text Is Invalid For Argument 1 Of Replace Function.

May 14, 2008



Hi There,

Could someone please tell me why I am getting the above error on this code:

select (replace
(replace
(replace
(replace (serviceType, 'null', ' ')
, '<values><value>', ' ')
, '</value><value>', ',')
, '</value></values>', ' '))
from credit


serviceType (text,null)

Thanks,
Rhonda

View 1 Replies View Related

Procedure Or Function Has Too Many Argument Specified.

May 27, 2008

 Hey, friends, i have a problem on my bank transaction page now: Procedure or function has too many argument specified.people can log in by their usernames, 1 username can have many accounts, there are 2 account types:'c', 's'. after people login, they are only allowed to withdraw money from their own account: here is some of my codes:  1 else if (DropDownList3.SelectedItem.Text == "withdraw")2 {3 SqlConnection sqlCon = new SqlConnection("Data Source=bandicoot.cs.rmit.edu.au;Initial Catalog=shuli;User ID=test;Password=test");4 SqlCommand cmd = new SqlCommand("usp_withdraw", sqlCon);5 string spResult = "";6 cmd.CommandType = CommandType.StoredProcedure;7 cmd.Parameters.Add("@username", SqlDbType.NVarChar).Value = Session["username"].ToString();8 cmd.Parameters.Add("@AccountFrom", SqlDbType.VarChar).Value = TextBox3.Text;9 cmd.Parameters.Add("@Amount", SqlDbType.Decimal).Value = TextBox5.Text;10 cmd.CommandType = CommandType.StoredProcedure;11 sqlCon.Open();12 spResult = cmd.ExecuteScalar().ToString();13 if (string.Compare(spResult, "Execute successfully") == 0)14 {15 SqlDataSource with = new SqlDataSource();16 with.ConnectionString = ConfigurationManager.ConnectionStrings["shuliConnectionString"].ToString();17 18 with.InsertCommand = "insert into Transactions(TransactionType,AccountNumber,DestAccount,Amount,Comment,ModifyDate) values ('W','" + TextBox3.Text + "','" + TextBox3.Text + "','" + TextBox5.Text + "','" + TextBox2.Text + "','" + DateTime.Now.ToLocalTime() + "')";19 int inertRowNum = with.Insert();20 Server.Transfer("~/deposit_withdraw_confirm.aspx");21 }22 else23 {24 Server.Transfer("~/deposit_withdraw_error.aspx");25 }26
  here is my store procedure set ANSI_NULLS ONset QUOTED_IDENTIFIER ONgo-- =============================================-- Author:        <Author,,Name>-- Create date: <Create Date,,>-- Description:    <Description,,>-- =============================================ALTER Procedure [dbo].[usp_withdraw](    @AccountFrom Varchar(20),    @Amount Decimal(10,2))AsDECLARE @ReturnMsg AS VARCHAR(20)DECLARE @balance AS Decimal(10,2)DECLARE @username AS nvarchar(50)SELECT @balance = balanceFROM Account,LoginWHERE   Account.CustomerID=Login.CustomerID AND Login.UserID= @username and AccountNumber = @AccountFromIF (    SELECT AccountType     FROM Account, Login    Where  Account.CustomerID=Login.CustomerID AND Login.UserID= @username and AccountNumber = @AccountFrom    ) ='c'BEGIN     BEGIN TRAN        IF (@balance - @Amount) > 199                                    BEGIN            UPDATE Account            SET balance= balance-@Amount            WHERE AccountNumber=@AccountFrom                  And (balance - @Amount) > 199                                            SET @ReturnMsg = 'Execute successfully'            COMMIT TRAN        END        ELSE        BEGIN            SET @ReturnMsg = 'Execute with error'            ROLLBACK TRAN                  END    SELECT @ReturnMsg     ENDIF (    SELECT AccountType     FROM Account    Where AccountNumber = @AccountFrom    ) ='s'BEGIN     BEGIN TRAN        IF (@balance - @Amount) > 0                                    BEGIN            UPDATE Account            SET balance= balance-@Amount            WHERE AccountNumber=@AccountFrom                  And (balance - @Amount) > 0                                            SET @ReturnMsg = 'Execute successfully'            COMMIT TRAN        END        ELSE        BEGIN            SET @ReturnMsg = 'Execute with error'            ROLLBACK TRAN                  END    SELECT @ReturnMsg     ENDI think the problem is in Line 7cmd.Parameters.Add("@username", SqlDbType.VarChar).Value = Session["username"].ToString();this is to get the current login user's uername.  

View 4 Replies View Related

Error In Function Argument

Apr 17, 2007

I have a table with over 11,000 records and I need to do a find and replace using SET and Where conditions. Basically I have one column in the table called RealAudioLink. It contains entries like: wkdy20070416-a.rm and wkdy20070416-b.rm and conv20070416.rm.

I need the select statement to find all wkdy entries and replace those characters with Weekday. I also need it to find all dashes and small a's and b's and replace with null or nothing. Then I need it to insert a capital letter A or B in the
wkdy20070416-a.rm filename so that when it's all said and done that entry would read:

WeekdayA20070416.rm
WeekdayB20070416.rm
Conversation20070416.rm

Here is the code I am working with. It needs help. I'm close but I'm not knowledgeable with using SET or with removing dashes and inserting capital letters all in the same select statement.



Code Snippet

UPDATE T_Programs_TestCopy
(SET RealAudioLink = REPLACE(RealAudioLink, '-a', '')
AND
(SET RealAudioLink = REPLACE(RealAudioLink, 'wkdy', 'WeekdayA')
WHERE (RealAudioLink LIKE 'wkdy%'))

I've never done anything like this before so I would be very appreciative of any assistance with the select statement. I am reading up on it but it would be great to get another perspective from a more experienced sql developer.

Thanks

View 1 Replies View Related

SQL Problem (The MAX Function Requires 1 Argument(s).)

Mar 9, 2008

Hey Guys,
I'm having problems with my SQL statement and Im wondering if anyone could help?  My code is below.
The Label6 is filled up using a previous SQL Statement, and when Ive had a fiddle around it is using the KnowledgeID It works in reading in the date, it just doesnt appear to want to work for the second statement as I am getting the error shown in the subject.
Thanks in advance =)
C# Codeprotected void Button2_Click(object sender, EventArgs e)
{KnowledgeID = Convert.ToInt32(Label5.Text);
string path = Server.MapPath(" ") + "\X_Drive\";FileLocation = path + Convert.ToString(FileUpload1.FileName);SqlConnection myConnection = new SqlConnection(myConnectionString);
 SqlCommand myCommand2 = new SqlCommand("select * from Knowledge WHERE KnowledgeID = '" + KnowledgeID + "'", myConnection);
myConnection.Open();
SqlDataReader myReader = myCommand2.ExecuteReader();if (myReader.HasRows)
{while (myReader.Read())
{Add = myReader["DateAdded"].ToString();
}
myReader.Close();
}
else
{
myConnection.Close();
}
 SqlTransaction trans = myConnection.BeginTransaction();
{
try
{
 SqlCommand myCommand3 = new SqlCommand("Select ISNULL(MAX(Version,0)+1 FROM Archive WHERE KnowledgeID = "+KnowledgeID+"",myConnection);
myCommand3.Transaction=trans;int nextVersion =(int)myCommand3.ExecuteScalar();if (File != null)
{myCommand3 = new SqlCommand("INSERT INTO Archive (FixName, Description, Location, DateAdded, DateArchived, Version, KnowledgeID) SET (@FixName,@Description,@File,@Add,@AddDate,@SAPPS,@Version,'" + KnowledgeID + "')", myConnection);
}
else
{myCommand3 = new SqlCommand("INSERT INTO Archive (FixName, Description, DateAdded, DateArchived, Version, KnowledgeID) SET (@FixName,@Description,@Add,@AddDate,@SAPPS,@Version,'" + KnowledgeID + "')", myConnection);
}myCommand3.Parameters.AddWithValue("@FixName", TextBox1.Text);
myCommand3.Parameters.AddWithValue("@Description",TextBox2.Text);myCommand3.Parameters.AddWithValue("@File",FileLocation);
myCommand3.Parameters.AddWithValue("@Add",Add);myCommand3.Parameters.AddWithValue("@AddDate",AddDate);myCommand3.Parameters.AddWithValue("@Versions",nextVersion);
myCommand3.ExecuteNonQuery();
trans.Commit();
}catch (Exception ex)
{
//TextBox2.Text = ex.Message;
trans.Rollback();
myConnection.Close();
}
//}

View 5 Replies View Related

[OT?] TSQL Function

May 9, 2008

Hi,
I need some pointers, I am trying to create a SQL function which will check to see if a column in a table will allow null values to be inserted.
I've tried searching the net but to no avail.
Kind Regards
 

View 2 Replies View Related

TSQL Function

Jun 20, 2008

Hey guys,
I have been trying to get a function to work but have been having a hard time. The tables that are used are a translation and validation tables. But the translation can be from different tables. What i would like the function to do is take in a 'description of a type' and a language code and send back the translated version. The issue is that the table is dynamic and there is my issue with my function.

ALTER FUNCTION [dbo].[Trans]
(
@Trans_description AS NVarchar(200),
@languageid AS nvarchar(10)
)
RETURNS nvarchar(50)
AS
BEGIN
DECLARE
@return AS nvarchar(50),
@TABLENAME AS nvarchar(50),
@sql AS nvarchar(100)
set @TableName = (select top(1) listtable from validationType where validationtypeDesc = @Trans_description)

SET @return =
(SELECT TOP (1) ValidationDesc FROM [translationvalidationlist] WHERE validationpartyid IN
(SELECT validationpartyid FROM @TableName WHERE validationtypePartyid IN
(select validationtypepartyid FROM validationtype WHERE validationtypeDesc = @Trans_description
))AND LANGUAGEID = @languageid)


RETURN @return



END

Is there anyway to do a @return = exec(@sql) of course sql would be the current @return. Right now i get the error Must declare the table variable "@TableName".


Thank you so much for all your help.

View 4 Replies View Related

TSQL - Using Sum Function

Sep 18, 2007

Hi guys,
I am looking for a solution to the problem described below (SQL SERVER 2000).
Thanks in advance for any help,
Aldo.

The script below:



Code Snippet
SELECT
Data.[Invoice No] AS 'InvoiceNo',
[JurnalTrans.REF2] AS 'JurnalTrans.REF2',
[JurnalTransMoves.SUF] AS 'JurnalTransMoves.SUF',
[RowIDDataLevel] AS 'RowIDDataLevel',

CASE WHEN RowIDDataLevel = 1 THEN CAST(Paid AS int) ELSE '0' END AS 'Paid' ,

CASE WHEN RowIDDataLevel = 1 THEN CAST([JurnalTransMoves.SUF] - [Paid] AS int) ELSE '0' END AS 'OutstandingBalance'

FROM CTE AS Data
INNER JOIN (SELECT [Invoice No], SUM([JurnalTransMoves.SUF]) AS Paid FROM CTE GROUP BY [Invoice No]) AS SUMSUF ON SUMSUF.[Invoice No] = DATA.[Invoice No]

ORDER BY [Accounts.ACCOUNTKEY], Data.[Invoice No], [RowIDDataLevel]

Gives these results:

Invoice No JurnalTrans.REF2 JurnalTransMoves.SUF RowIDDataLevel Paid OutstandingBalance

----------- ---------------- ---------------------- -------------- ----------- ------------------

5752 NULL 1155 1 1810 -655

5752 2032 400 2 0 0
5752 2033 155 3 0 0

5752 2034 100 4 0 0

5754 NULL 1732.5 1 2482 -750

5754 2035 750 2 0 0

16687 NULL 2555.8 1 2555 0


Where:
1810 (Paid) = 1155 + 400 + 155 + 100

2482 = 1732 + 750
and so on...

What I need is to get:
instead of 1810 ==> 655 = 400 + 155 + 100
instead of 2482 ==> 750
and so on...

In order to do that, I tried inserting "WHERE RowIDDataLevel Not In (1) " in the line:


Code Snippet
INNER JOIN (SELECT [Invoice No], SUM([JurnalTransMoves.SUF]) AS Paid FROM CTE WHERE RowIDDataLevel Not In (1) GROUP BY [Invoice No]) AS SUMSUF ON SUMSUF.[Invoice No] = DATA.[Invoice No]

and then getting the following:

InvoiceNo JurnalTrans.REF2 JurnalTransMoves.SUF RowIDDataLevel Paid OutstandingBalance

----------- ---------------- ---------------------- -------------- ----------- ------------------

5752 NULL 1155 1 655 500

5752 2032 400 2 0 0

5752 2033 155 3 0 0

5752 2034 100 4 0 0

5754 NULL 1732.5 1 750 982

5754 2035 750 2 0 0


That it is almost what I need, but not good enough because it makes the line marked in red to disappear because of RowIDDataLevel =1
16687 NULL 2555.8 1 2555 0
to disappear.




View 3 Replies View Related

Greatest Function In TSQL

Aug 20, 2007

Hi All,
I need to find the highest of two numbers and lowest of the two numbers.
I read on the web that Greatest and least functions in sql can help me do that.
but when i use
greatest in my query it gives me this error

GREATEST is not a recognized built-in function name.
all I have is

declare @first int, @second int
set @first='12'
set @second='14'
select GREATEST(@first,@second)

Can somebody point me in the right direction.
Thanks so much

View 8 Replies View Related

DLookup Function In TSQL

May 24, 2008



Hi all,


Access has a DLookup function that allow you to look up a value in another table based on a criteria, how is this functionality achieved in TSQL?


Thanks


Regards
Melt

View 7 Replies View Related

Problem With Round Function In TSQL

Feb 13, 2008

Trying below instructions

create table t

(


indexvalue float

)
go

insert t

values (109.1)

insert t

values (109.3)

insert t

values (109.5)

insert t

values (109.9)

go
select *

from t


select sum (indexvalue) / count (*)

from t


select round (sum(indexvalue)/count (*), 1) -- this line is result of round (109.45) shows 109.4 that is incorrect
from t --why round function doesn't work correctly in this select


select round (109.45, 1) -- but this line results 109.5 that is correct


View 3 Replies View Related

Argument Not Specified For Parameter 'arguments' Of Public Function Select(arguments As System.Web.DatasourceSelect Arguments As Collections Ienumerable

Mar 25, 2007

I have an SqlDataSource control on my aspx page, this is connected to database by a built in procedure that returns a string dependent upon and ID passed in.
 I have the followinbg codewhich is not complet, I woiuld appriciate any help to produce the correct code for the code file
 Function GetCategoryName(ByVal ID As Integer) As String          sdsCategoriesByID.SelectParameters("ID").Direction = Data.ParameterDirection.Input          sdsCategoriesByID.SelectParameters.Item("ID").DefaultValue = 3          sdsCategoriesByID.Select() <<<< THIS LINE COMES UP WITH ERROR 1End Function
ERROR AS FOLLOWS
argument not specified for parameter 'arguments' of public function Select(arguments as System.Web.DatasourceSelect Arguments as Collections ienumerable
 
Help I have not got much more hair to loose
Thanks Steve

View 5 Replies View Related

TSQL Function To Return Numeric Value Of Non Numeric Field

Jul 20, 2006

I need to replace Access Val() functions with similiar function in sql.

i.e. Return 123 from the statement: SELECT functionname(123mls)

Return 4.56 from the satement: SELECT functionname(4.56tonnes)

Any one with ideas please

Thanks

George


View 1 Replies View Related

Equivalent Tsql For Sql Server 2000 Is Needed [from Sql Server 2005 Only Tsql]

Nov 19, 2007

Can anyone please give me the equivalent tsql for sql server 2000 for the following two queries which works fine in sql server 2005

1
-- Full Table Structure

select t.object_id, t.name as 'tablename', c.name as 'columnname', y.name as 'typename', case y.namewhen 'varchar' then convert(varchar, c.max_length)when 'decimal' then convert(varchar, c.precision) + ', ' + convert(varchar, c.scale)else ''end attrib,y.*from sys.tables t, sys.columns c, sys.types ywhere t.object_id = c.object_idand t.name not in ('sysdiagrams')and c.system_type_id = y.system_type_idand c.system_type_id = y.user_type_idorder by t.name, c.column_id


2
-- PK and Index
select t.name as 'tablename', i.name as 'indexname', c.name as 'columnname' , i.is_unique, i.is_primary_key, ic.is_descending_keyfrom sys.indexes i, sys.tables t, sys.index_columns ic, sys.columns cwhere t.object_id = i.object_idand t.object_id = ic.object_idand t.object_id = c.object_idand i.index_id = ic.index_idand c.column_id = ic.column_idand t.name not in ('sysdiagrams')order by t.name, i.index_id, ic.index_column_id

This sql is extracting some sort of the information about the structure of the sql server database[2005]
I need a sql whihc will return the same result for sql server 2000

View 1 Replies View Related

Argument Not Specified For Parameter

Apr 6, 2008

Hi,I'm working with visual studio 2008 and a sql server database. I'm also a bit of a beginner. I've written a stored procedure as below. My aim is to try and retrieve the data from field 'caption' through a paramater being passed.
I'm using the function as below to call this procedure through objectdatasource.
i'm getting the following error:
Compiler Error Message: BC30455: Argument not specified for parameter 'caption' of 'Public Sub New(id As Integer, count As Integer, caption As String, ispublic As Boolean)'.Source Error:





Line 98: Using reader As SqlDataReader = command.ExecuteReader()
Line 99: Do While (reader.Read())
Line 100: Dim temp2 As New Album(CStr(reader("Caption")))
Line 101: list2.Add(temp2)
Line 102: Loop
Any help will be apreciated, a lot of my code is copy and pasted and i don't fully understand every line which is where i assume the error is being made!
Thanks 
 
FUNCTIONSPublic Shared Function GetAlbumName(ByVal AlbumID As Integer) As Generic.List(Of Album)
Using connection As New SqlConnection(ConfigurationManager.ConnectionStrings("Personal").ConnectionString)Using command As New SqlCommand("GetAlbumName", connection)
command.CommandType = CommandType.StoredProcedurecommand.Parameters.Add(New SqlParameter("@AlbumID", AlbumID))
connection.Open()Dim list2 As New Generic.List(Of Album)()
Using reader As SqlDataReader = command.ExecuteReader()Do While (reader.Read())Dim temp2 As New Album(CInt(reader("AlbumID")), CStr(reader("Caption")))
list2.Add(temp2)
Loop
End UsingReturn list2
End Using
End Using
End Function
 
SQL STORED PROCEDURE
ALTER PROCEDURE GetAlbumName@AlbumID int
AS
SELECT
*FROM [Albums]WHERE [Albums].[AlbumID] = @AlbumID
 
 
RETURN

View 2 Replies View Related

Argument Not Specified For Parameter

Apr 8, 2008

I try to replace the following statement : CmdPuzzle.Parameters.Append CmdPuzzle.CreateParameter("@Length",adTinyInt,adParamInput,,6)
 with:
Dim retLengthParam As New SqlParameter("@Length", SqlDbType.TinyInt, , 6)
it highlighted the retLengthParam saying:
Argument not specified for parameter 'size' of 'Public Sub New(parameterName As String, dbType As System.Data.SqlDbType, size As Integer, sourceColumn As String)
Logic:


set that the input is only allowed 6 integer.

View 3 Replies View Related

How Do I Specify More Than I Argument In A Called SP?

Jan 24, 2007

CREATE PROCEDURE sp_getT
@m1 int ,
@txn int ,
@Pan varchar(50) ,
@Act varchar(50) OUTPUT,
@Bal Decimal(19,4) OUTPUT,
@CBal Decimal(19,4) OUTPUT
AS

declare @pBal money, @pCbal money, @pAct money
SET NOCOUNT ON


IF @m1 = 200
BEGIN
IF @txn = 31
BEGIN
exec ChkBal @Pan, @pBal output, @pCbal output, @pAct out
END
END

SET @Act = @pAct
SET @Bal = cast(@pBal as Decimal(19,4))
SET @CBal = cast(@pCBal as Decimal(19,4))

return @Act
return @Bal
return @CBal

the above code returns this error message

"Server: Msg 8144, Level 16, State 2, Procedure CheckBalance, Line 0
Procedure or function ChkBal has too many arguments specified."


How do i specify all the arguments i want in the called procedure?

View 14 Replies View Related

Optional Argument In UDF?

Jan 27, 2004

Is it possible to define an argument as optional for a UDF? I have a financial calculation that may or may not require a defined date range depending on the status of an individual item. Is there a way to avoid requiring the date range where it's not necessary?

View 10 Replies View Related

Sqldatasource1.Select([Argument?])

Oct 8, 2007

Hi, nice to meet you all. I need to return a parameter value from a store procedure, i already done insert(), update(), delete() to return parameter back. Just only select() can't return parameter and it need argument, I try to write in Sqldatasource1.Select(Argument.empty), it can't work and return null. How should i solve the problem? Or I wirte the wrong argument? Can anyone help me? Thank in advance.

View 5 Replies View Related

'column' Argument Cannot Be Null.

Apr 14, 2008

Hi i get the above error whenever i try and run my page, what i am trying to do is embed a repeater within a datalist, here is my code;public void Page_Load(object sender, EventArgs e)
{string strID = Request.QueryString["id"];
SqlConnection conn = new SqlConnection(ConfigurationManager.ConnectionStrings["streamConnectionString"].ConnectionString);SqlCommand command = new SqlCommand("stream_Users", conn);
command.CommandType = CommandType.StoredProcedure;command.Parameters.Add("@userID", SqlDbType.Int).Value = Request.QueryString["id"];
SqlDataAdapter cmd1 = new SqlDataAdapter(command);
//Create and fill the DataSet.DataSet ds = new DataSet();
cmd1.Fill(ds, "userName");
//Create a second DataAdapter for the Titles table.SqlDataAdapter cmd2 = new SqlDataAdapter("select * from UserSpecialties", conn);
cmd2.Fill(ds, "specialty");
//Create the relation bewtween the Authors and Titles tables.ds.Relations.Add("myrelation",
ds.Tables["userName"].Columns["userID"],ds.Tables["specialtyName"].Columns["userID"]);
//Bind the Authors table to the parent Repeater control, and call DataBind.DataList1.DataSource = ds.Tables["userName"];
Page.DataBind();
//Close the connection.
conn.Close();
}

View 2 Replies View Related

Argument Not Specified For Parameters Error

Nov 18, 2004

hello, I am doing some tutorials to learn SQL reporting services. One of the tutorials is using asp to call a web services and blah blah etc...
Basically the report opens with two calenders and an execute button. the user would pick two dates and then hit execute.

the code is this:

Private Sub cmdExecute_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles cmdExecute.Click
Dim report As Byte() = Nothing

'Create an instance of the Reporting Services
'Web Reference
Dim rs As localhost.ReportingService = New localhost.ReportingService
'Create the credentials that will be used when accessing
'Reporting Services. This must be a logon that has rights
'to the Axelburg Invoice-Batch Number Report.
'***Replace "LoginName", "Password", and "Domain" with
'the appropriate values. ***
rs.Credentials = New _
System.Net.NetworkCredential("Administrator", _
"password", "localhost")
rs.PreAuthenticate = True

'The Reporting Services virtual path to the report.
Dim reportPath As String = _
"/Galactic Delivery Services/Axelburg/Invoice-Batch Number"

' The rendering format for the report.
Dim format As String = "html4.0"

'The devInfo string tells the report viewer
'how to display with the report.
Dim devInfo As String = _
"<DeviceInfo>" + _
"<Toolbar>False</Toolbar>" + _
"<Parameters>False</Parameters>" + _
"<DocMap>True</DocMap>" + _
"<Zoom>100</Zoom>" + _
"</DeviceInfo>"

'Create an array of the values for the report parameters
Dim parameters(1) As localhost.ParameterValue
Dim paramValue As localhost.ParameterValue _
= New localhost.ParameterValue
paramValue.Name = "StartDate"
paramValue.Value = calStartDate.SelectedDate
parameters(0) = paramValue
paramValue = New localhost.ParameterValue
paramValue.Name = "EndDate"
paramValue.Value = calEndDate.SelectedDate
parameters(1) = paramValue

'Create variables for the remainder of the parameters
Dim historyID As String = Nothing
Dim Credentials() As localhost.DataSourceCredentials = Nothing
Dim showHideToggle As String = Nothing
Dim encoding As String
Dim mimeType As String
Dim warnings() As localhost.Warning = Nothing
Dim reportHistoryParameters() As _
localhost.ParameterValue = Nothing
Dim StreamIDs() As String = Nothing

Dim sh As localhost.SessionHeader = _
New localhost.SessionHeader
rs.SessionHeaderValue = sh

Try
'Execute the report.
report = rs.Render(reportPath, format, historyID, _
showHideToggle, encoding, mimeType, _
reportHistoryParameters, warnings, _
StreamIDs)

sh.SessionId = rs.SessionHeaderValue.SessionId

'Flush any pending responce.
Response.Clear()

'Set the Http headers for a PDF responce.
HttpContext.Current.Response.ClearHeaders()
HttpContext.Current.Response().ClearContent()
HttpContext.Current.Response.ContentType = "text/html"
' filename is the default filename displayed
'if the user does a save as.
HttpContext.Current.Response.AppendHeader( _
"Content-Disposition", _
"filename=""Invoice-BatchNumber.HTM""")

'Send he byte array containing the report
'as a binary response.

HttpContext.Current.Response.BinaryWrite(report)
HttpContext.Current.Response.End()

Catch ex As Exception
If ex.Message <> "Thread was being aborted." Then
HttpContext.Current.Response.ClearHeaders()
HttpContext.Current.Response.ClearContent()
HttpContext.Current.Response.ContentType = "text/html"
HttpContext.Current.Response.Write( _
"<HTML><BODY><H1>Error</H1><br><br>" & _
ex.Message & "</BODY></HTML>")
HttpContext.Current.Response.End()

End If
End Try


End Sub
End Class


This is the area underlined as failing:
report = rs.Render(reportPath, format, historyID, _
showHideToggle, encoding, mimeType, _
reportHistoryParameters, warnings, _
StreamIDs)

The errors all pretty much go like this:
c:inetpubwwwrootAxelburgFrontEndReportFrontEnd.aspx.vb(95): Argument not specified for parameter 'ParametersUsed' of 'Public Function Render(Report As String, Format As String, HistoryID As String, DeviceInfo As String, Parameters() As localhost.ParameterValue, Credentials() As localhost.DataSourceCredentials, ShowHideToggle As String, ByRef Encoding As String, ByRef MimeType As String, ByRef ParametersUsed() As localhost.ParameterValue, ByRef Warnings() As localhost.Warning, ByRef StreamIds() As String) As Byte()'.

I searched around, but didn't really understand what these errors mean. Any help is greatly appreciated.

View 1 Replies View Related

Argument Not Specified For Parameter Error

Jan 16, 2005

I am trying to extract a value out of a cookie and then use that as a parameter to a SQL Select...Where function but I am getting the Argument not specified for parameter error. I assume the value from the cookie is not in the variable that I created but I could be wrong.

What is the proper method for populating a variable from a cookie and using that value as part of a SQL Select....Where function?

After I get a value out of the Select...Where function, I need to use the dataset results in a DropDownList as well as perform the DataBind().

here's some of the code:



Private Sub Page_Load(ByVal sender As System.Object, ByVal e As System.EventArgs)
if Not Page.IsPostBack then
dim Code as String
Code = Server.HtmlEncode(Request.Cookies("UCodeCookie")("Code"))
label1.Text = Code
GetPropertyCodes(Code)
ddlPropertyCode.DataBind()
end if
End Sub

Function GetPropertyCodes(ByVal Code As String) As System.Data.DataSet
Dim connectionString As String = "server='(local)'; trusted_connection=true; database='master'"
Dim dbConnection As System.Data.IDbConnection = New System.Data.SqlClient.SqlConnection(connectionString)

Dim queryString As String = "SELECT [Property_Details_db].[Prop_Code] FROM [Property_Details_db] WHERE ([Property_Details_db].[Code] = @Code)"
Dim dbCommand As System.Data.IDbCommand = New System.Data.SqlClient.SqlCommand
dbCommand.CommandText = queryString
dbCommand.Connection = dbConnection

Dim dbParam_code As System.Data.IDataParameter = New System.Data.SqlClient.SqlParameter
dbParam_code.ParameterName = "@Code"
dbParam_code.Value = Code
dbParam_code.DbType = System.Data.DbType.String
dbCommand.Parameters.Add(dbParam_code)

Dim dataAdapter As System.Data.IDbDataAdapter = New System.Data.SqlClient.SqlDataAdapter
dataAdapter.SelectCommand = dbCommand
Dim dataSet As System.Data.DataSet = New System.Data.DataSet
dataAdapter.Fill(dataSet)

Return dataSet
End Function



Thanks for your help.

Chris

View 13 Replies View Related

[|-)] Error: Stored Procedure Has Too Many Argument....

Feb 11, 2008

Hello frend...i have a problem with my application when i'm trying to delete a certain data from gridview...i have a store procedure that already create in my mssql server...and in datasource i'm using command that i've already created in my mssql server...the problem is i dont know how i can send my value to the parameter in datasource.....and one more thing what is exaclty the error with 'Store procedure has <my function> too many argument" occur? Please help me....so i paste my code below to easier and detect my problem....(sorry my english are no good)....
1) This is my code in asp.net
<asp:UpdatePanel ID="UpdatePanel1" runat="server">
<ContentTemplate>
<asp:SqlDataSource ID="SqlDSRole" runat="server" ConnectionString="<%$ ConnectionStrings:PVMCCon %>"
DeleteCommand="Roles_delete"
DeleteCommandType="StoredProcedure"
SelectCommand="Roles_view"
SelectCommandType="StoredProcedure">
<DeleteParameters>
<asp:Parameter Name="roleName" Type="String" />
</DeleteParameters>
</asp:SqlDataSource>
<asp:GridView ID="GridView1" runat="server"
AutoGenerateColumns="False" CellPadding="4" DataKeyNames="Role_application_id"
DataSourceID="SqlDSRole" ForeColor="#333333" GridLines="None" PageSize="4">
<FooterStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<Columns>
<asp:CommandField ShowDeleteButton="True" />
<asp:TemplateField HeaderText="Role Application Id" Visible="False">
<ItemTemplate>
<asp:Label ID="Label1" runat="server" Text='<%# Eval("Role_application_id") %>'></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:Label ID="roleApplicationIdLabel" runat="server" Text='<%# Eval("Role_application_id") %>'></asp:Label>
</EditItemTemplate>
<AlternatingItemTemplate>
<asp:Label ID="Label3" runat="server" Text='<%# Eval("Role_application_id") %>'></asp:Label>
</AlternatingItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Role Name" >
<ItemTemplate>
<asp:Label ID="roleName" runat="server" Text='<%# Eval("Role_name") %>' CssClass="LabelInfo" ></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="roleNameTxt" runat="server" Text='<%# Bind("Role_name") %>' Width="200px"></asp:TextBox>
</EditItemTemplate>
<AlternatingItemTemplate>
<asp:Label ID="Label5" runat="server" Text='<%# Eval("Role_name") %>' CssClass="LabelInfo"></asp:Label>
</AlternatingItemTemplate>
</asp:TemplateField>
<asp:TemplateField HeaderText="Role Description">
<ItemTemplate>
<asp:Label ID="Label6" runat="server" Text='<%# Eval("Role_description") %>' CssClass="LabelInfo"></asp:Label>
</ItemTemplate>
<EditItemTemplate>
<asp:TextBox ID="roleDescriptionTxt" runat="server" Height="30px" Text='<%# Bind("Role_description") %>'
TextMode="MultiLine" Width="300px"></asp:TextBox>
</EditItemTemplate>
<AlternatingItemTemplate>
<asp:Label ID="Label7" runat="server" Text='<%# Eval("Role_description") %>' CssClass="LabelInfo"></asp:Label>
</AlternatingItemTemplate>
</asp:TemplateField>
</Columns>
<RowStyle BackColor="#F7F6F3" ForeColor="#333333" />
<EditRowStyle BackColor="#E0E0E0" />
<SelectedRowStyle BackColor="#E2DED6" Font-Bold="True" ForeColor="#333333" />
<PagerStyle BackColor="#284775" ForeColor="White" HorizontalAlign="Center" />
<HeaderStyle BackColor="#5D7B9D" Font-Bold="True" ForeColor="White" />
<AlternatingRowStyle BackColor="White" ForeColor="#284775" />
</asp:GridView>
</ContentTemplate>
</asp:UpdatePanel>
2) And this is my Store procedure that i created in mssql server
Store Procedure: Roles_view
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[Roles_view]
AS
BEGIN
SET NOCOUNT ON;
SELECT *
FROM Role
END
Store Procedure: Roles_delete
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go

ALTER PROCEDURE [dbo].[Roles_delete]
@roleName varchar(50)
AS
BEGIN
SET NOCOUNT ON;
DELETE FROM Role
WHERE Role_name LIKE @roleName
END
 3) The problem is when i'm trying to delete my certain data, the message box appear and say "Procedure or function Roles_delete has too many argument specified".what should i do????anabosy please help me..

View 5 Replies View Related

DTS: Using Vbsript How Do You Pass An Argument To A Package?

Feb 8, 2002

The following vbscript executes the specified package,
however, I need to pass the package some arguments and
don't know how.

Set oPackageToExecute = CreateObject("DTS.Package")
oPackageToExecute.LoadFromSQLServer "SQLSERVER", , , TSSQLStgFlag_UseTrustedConnection, , , , "Package Name"
oPackageToExecute.Execute
oPackageToExecute.Uninitialize()
Set oPackageToExecute = Nothing

Any help is greatly appreciated,
Martin

View 5 Replies View Related

Argument To Support Database Upgrade From 80 To 90

Mar 16, 2007

I have to build a coherent argument for why we should fix the application to run at compt level 90 instead of just leaving it at 80.

Looking at . . .

Differences Between Lower Compatibility Levels and Level 90
http://msdn2.microsoft.com/en-us/library/ms178653.aspx

. . . I can't find anything further that tells my why it is in our best interest to do so other than 'Microsoft won't help you until you do'.

Anyone have any good bullet points on this?

View 6 Replies View Related

Proc Argument Type Array

Jul 20, 2005

I have ...CREATE PROC InvoiceDeleteExample2(@InvoiceList1 VARCHAR(255),@InvoiceList2 VARCHAR(255) = '',...@InvoiceListN VARCHAR(255) = '')AS....GOI would like....CREATE PROC InvoiceDeleteExample2(SELECT * FROM table;)AS....GOI hope help... thx

View 1 Replies View Related

Custom Code...For The Sake Of Argument

Oct 3, 2006

Is it possible to write custom code that achieves the same thing as an aggregate function?

ex: Calculating the sum of a group? (with out using the sum( field!one.value) function)

if I had a group that grouped on name.


Name Number
header Fields!FirstName.Value ************
details Fields!Number.Value
footer

Can i write a custom code function (or functions) that will get the sume of the numbers in that group.

Thanks in advance.

View 7 Replies View Related

Ignore NULL Argument In SELECT

Aug 14, 2007

I have a stored procedure which accepts 3 arguments that are used in the WHERE clause of a SELECT Statement

CREATE PROCEDURE [dbo].[MyProcedure] @argYear INT, @argMonth INT, @argDay INT

AS

SELECT *

FROM MyData

WHERE
MyData.Year = @argYear AND MyData.Month = @argMonth AND MyData.Day = @argDay


The problem that I am having is @argDay is an "optional" argument. If @argDay is NULL then I want to basically ignore the "AND MyData.Day = @argDay" condition.

Is there an easier way to do this than:

IF @argDay is NULL

SELECT *


FROM MyData


WHERE

MyData.Year = @argYear AND MyData.Month = @argMonthELSE

SELECT *


FROM MyData


WHERE

MyData.Year = @argYear AND MyData.Month = @argMonth AND MyData.Day = @argDay

END IF

Thanks

View 7 Replies View Related

Passing Table Names As An Argument In A Procedure

May 7, 2008

hai <br>
<p>i have a procedure where in which i want to access a specific table whose name will be passed into procedure as argument. Here is the peice of code i have made.</p>
CREATE PROCEDURE outputtable @tablename nchar(10) AS<br>
begin<br>
.........................<br>
insert into @tablename (classno) values (@cno)<br>
.................................<br>
END<br>
 
<p> I get the following error message :</p>
Msg 1087, Level 15, State 2, Procedure................<br>
Must declare the table variable "@tablename".<br>
 <p>
Is there anyway i can get around this one.   I will be grateful if anyone can help me</p>

View 5 Replies View Related

Table Type Argument As OUTPUT In PROC

Jun 1, 2013

how can i define table type argument as OUTPUT in PROC and don't READONLY? if it's not possible, is there another way to do same this?

View 1 Replies View Related

SQL 2012 :: Argument Daily Transaction Log Shrink?

Jul 7, 2015

For a few days now I have a discussion with a colleague about shrinking the transaction log as a daily maintenance job on an OLTP database. The problem is I cant figure out a way to convince her she is doing something really wrong. Its not the first discussion.. Maintenance Plans.

She implemented this "solution" with a lot of customers as a solution against VLFs fragmentation and huge transaction log sizes. My thoughts about doing this is very clear and I have used the following arguments without success to convince her:

- To solve too many VLFs you have to focus on the actual size of the transaction log and the autogrowth settings in combination with regularly transaction log backups. Check the biggest transaction and modify the transaction log size based on this. Not use shrinking as a solution for solving many VLFs.

- Shrinking the transaction log file on a daily basis that is disk I/O intensive. When the transaction log file is too small for new transactions, the transaction log needs to grow and this will cause disk I/O, this can cause performance problems.

- It looks unprofessional.

These steps are used every morning at 6:00 AM and a transaction log backup is made every 30 minutes.

Step 1
DBCC SHRINKFILE (N'' , 0, TRUNCATEONLY);
go

Step 2
ALTER DATABASE
MODIFY FILE (NAME = N'', SIZE = 4098MB);
GO

My main purpose is making sure the customers have the best possible configuration and I cant accept this is being implemented. Are there any more arguments available for this issue?

View 2 Replies View Related

An Argument Against Running Or Developing In SQL Compatibility 8.0 Mode

Oct 29, 2007



Hi,

I've been scouring the forums and the web for anything that would substantiate an argument for database application developers to keep developing in SQL 8.0 when we have migrated to SQL 2005.

I read somewhere that compatibility 8.0 mode is an intern stepping stone for migrating from 8 to 9, but it seems 2005 will handle and run compat 8.0 databases just fine.

So am finding it really diffucult to substantiate my argument against running 2005 with all databases in compatibility 8 mode indefinitly.

Any suggestions or links that could help me with this.

Joe

View 2 Replies View Related







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