I have ...
CREATE PROC InvoiceDeleteExample2
(
@InvoiceList1 VARCHAR(255),
@InvoiceList2 VARCHAR(255) = '',
...
@InvoiceListN VARCHAR(255) = ''
)
AS
....
GO
I would like....
CREATE PROC InvoiceDeleteExample2
(
SELECT * FROM table;
)
AS
....
GO
I hope help... thx
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 RelatedWhere 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)
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
I am using the following code in my SQL stmt in my OLE BD Source stmt:
WHERE ICINVENTORY.ICINVLastChgAt > ? AND ICINVENTORY.ICINVLastChgAt <= DATEADD(mi,?,?)
My parameters are as follows:
0 - User:LastSalesLoadDate DateTime variable
1 - User:Load Interval Int16 (or Int32)
2 - User:LastSalesLoadDate
When I try to close the program I get the following error:
"Argument data type datetime is invalid for argument 2 of dateadd function. If I can't use a datetime data type for the date time part of the dateadd, what can I use?
The exact same code runs without error in an EXECUTE SQL task.
Thanks.
how to solve this problem ....
I want to select te sum of nvarchar column and this error always happen
what is the solution this is my query
"SELECT Datein AS Date,SUM(Durm) AS DurationMin, COUNT (*) AS 'Number of Hits' FROM Rayyan_Log WHERE (DNIS = '"+DropDownList1.SelectedItem.Text +"') AND (Datein between '"+TextBox2.Text+"' and '"+TextBox3.Text+"') GROUP BY Datein ORDER BY Datein"
How can I make it work when I need to pull out a field which is text type using a stored procedure? Please help!!!Thanks
I am getting the following error
Error 409: The assignment operator operation could not take a text data type as an argument
===========my sp=================================
CREATE PROCEDURE [dbo].[sp_SelectABC]
(@a varchar(50) output,
@b text output
)
AS
set nocount on
select @a=name, @b= description from ABC
GO
In my front end, I have a procedure in which the operator selects multiple tickets. I have in mind to create a stored procedure which accepts its input parameter and plugs it into an IN() clause in an Update statement, something along these lines:
Update TicketInventory Set SalesID = 12345 Where TicketInventoryID IN( myInputParam );
Can I pass an array of Integers into a stored proc and have it work like this? Alternatively, I could pass a comma-delimited string containing the Integers. Could I plug that into the IN()? Or is there is yet another way to accomplish this?
TIA,
Arthur
Can you pass an array into a stored proc or do we have to use indiviual parameters.
If we can, any code exmaple woudl be great.
Cheers
Chris
I keep running into a problem with the debugger freezing up. I'm trying to pull results from a query into an array (not more than 10 rows)
cmd.ExecuteReader();
while (rdr.Read() == true) {
letter = rdr[0].ToString();
i += 1;
}
if I comment out the "letter = rdr[0].ToString();" portion of code, the project runs fine. With that code there, I can not step into the proc from the SQL test script. No errors are raised. VS just freezes.
I have a problem using 3 tables. The first recordset is my own invention (that means a very simple one). It reads product features from a table:
Set Rsx = Server.CreateObject("ADODB.RecordSet")
sSQL= "SELECT * from prodfeatures"
Rsx.Open sSQL, sDSN, adOpenStatic, adLockReadOnly, adCmdText
'then I read the recordset into arrays like this:
While Not Rsx.eof
i=i+1
mte(i)=Rsx("id")
' every id has a corresponding featurename
mfnavn(mte(i))=Rsx("featurename")
Rsx.movenext
Wend
'this part works very well
Now comes the difficult part that the SQLTeam figured out (this combines an order base with an itemorder base):
Set Rs = Server.CreateObject("ADODB.RecordSet")
sSQL="select oitems.catalogid,oitems.features, sum(oitems.numitems) as SumOfItems from oitems Inner Join Orders On Orders.orderid = oitems.orderid Where Orders.oshippeddate =" & dDate & " AND Orders.orderid <> 1 group by oitems.catalogid,oitems.features"
Rs.Open sSQL, sDSN, adOpenStatic, adLockReadOnly, adCmdText
'Then I write the content of the products with product features - the complexity of the sql is due to a use of many other operations:
While Not Rs.eof
response.write Rs("catalogid") & "features:" & Rs("features") & "Name:" & mfnavn(rs("features"))
Rs.movenext
Wend
The query runs, and starts well, writing 3 lines:
239 features:221 Name:Standard bil max 5m lang og 1,9 m høy
240 features:270 Name:Liggestol (gratis)
240 features:271 Name:C1 Enkeltseng i delt lugar u. bildekk. Kun vask
but it is then aborted with this message:
Microsoft VBScript runtime error '800a000d' Type mismatch: 'Rs(...)'
I know that the problematic part is the array
mfnavn(rs("features"))
...anyone with experience in this?
Hello All,I have a scenario in which my stored procedure has to return fewvariables with their value and also the collection. Now in SQL their isno such as array, so the best is to return the table in place of.I am execting the stored procedure by having sql command in place.and created the various parameters(variables) those i need the valuesof and secondly wondering how should i be creating the parameter as atable returntype.Any help on this would be a million worth useful.I can excerpt the code if required.RegardsSandesh Kadam
View 1 Replies View RelatedHi - I'm using Session variables to store information (sort of webshop).
The sessions are a mix of the usual straightforward strings, wich are no problem - but I also have a DataTable which I store in a session variable, and I also have an array, which I store in a session variable eg. session("day")(x) where x is the item in the array.
I want to give my users the ability to store the items they've selected in my database, but have no idea of what type of field I should use for the datatable and array session variable. Should I use a TEXT field, or is there another more appropriate one'?
I'm using SQL Server 2000.
Thanks for any help,
Mark
Hi,
I was migrating from Oracle to SQL Server 2005 using SSMA (SQL Server Migration Assistant) but i`ve found an issue, i can´t find how to fix it. In my stored procedure in PL/SQL exists this lines:
TYPE T_ARRAY_COL IS VARRAY (1000 ) OF VARCHAR2 (50);
A_COLUMNS T_ARRAY_COL := T_ARRAY_COL();
Somebody know how can i simulate this data type ARRAY. I was reading http://msdn.microsoft.com/msdnmag/issues/04/02/TSQLinYukon/ but some things are not clear for me.... please help me, give me one hand.
Thank you
David
Hi,
We're importing data from a progress db. Some of the columns contain arrays or delimited values which represent accounting periods.
Currently I'm passing the arrays row by row to a stored procedure which parses and inserts each value as a row for the applicable accounting period, it works but is very slow.
Any Ideas?
Thanks
Emilio
Is there any array data type in SQL Server 7.0. I am using VB 6.0 with ADO 2.1. I am populating a MSFlexGrid with values that I pass to SQL Server one at a time and insert into the database. What I would like to do is pass the entire contents of the Grid at once to a stored procedure and let SQL do the processing so my routine is not going back and forth to the client. I did not find any documentation on any array data types in SQL. What is my best approach to this problem?
Thanks,
Dan Collins
Is there a special field type I should be using, instead of a NVARCHAR for storing comma separated words?
I could, construct an array from the field value... but not sure if this would be the "right" way to do it?
Any one know about it?
View 9 Replies View RelatedHi, I was wondering if there is a method (other than BULK INSERT) to insert a (C++) application level array into the database, I have a variant type array populated with values that I want to insert, perhaps using ADO objects in quick time!
hi
i am getting an error with my code, it says 'value of type byte canot be converted to 1 dimensional array of byte' do you know why and how i can correct this error, the follwoing is my code.
can anyone help me correct the error and let me know ow to solve it
thanks for any help givenPublic Sub ProcessRequest(ByVal context As HttpContext) Implements IHttpHandler.ProcessRequestDim myConnection As New Data.SqlClient.SqlConnection("ConnectionString")
myConnection.Open()
Dim sql As String = "Select Image_Content from ImageGallery where Img_Id=@ImageId"Dim cmd As New Data.SqlClient.SqlCommand(sql, myConnection)cmd.Parameters.Add("@imgID", Data.SqlDbType.Int).Value = context.Request.QueryString("id")
cmd.Prepare()Dim dr As Data.SqlClient.SqlDataReader = cmd.ExecuteReader()
dr.Read()
context.Response.ContentType = dr("imgType").ToString()context.Response.BinaryWrite(CByte(dr("imgData"))) ----- this is the line with the error
End Sub
All,
I am using Reporting Services 2005. One of my reports is getting the following error when I try to export to Excel. It will export to .CSV though.
"Destination array was not long enough. Check destIndex and length, and the array's lower bounds."
Any suggestions would be greatly appreciated. Please copy me at machelle.a.chandler@intel.com.
Machelle
I have a stored procedure that has a paramter that accepts a string of values. At the user interface, I use a StringBuilder to concatenate the values (2,4,34,35,etc.) I would send these value to the stored procedure. The problem is that the stored procedure doesn't allow it to be query with the parameter because the Fieldname, "Officer_UID" is an integer data type, which can't be query against parameter string type.
What would I need to do to convert it to an Integer array?
@OfficerIDs as varchar(200)
Select Officer_UID From Officers Where Officer_UID in (@OfficerIDs)
Thanks
I am attempting to create a stored procedure that will launch at report runtime to summarize data in a table into a table that will reflect period data using an array type field. I know how to execute one line but I am not sure how to run the script so that it not only summarizes the data below but also creates and drops the table.
Any help would be greatly appreciated.
Current Table
Project | Task | Category | Fiscal Year | Fiscal Month | Total Hours
---------------------------------------------------------------------------------------------------------
Proj 1 | Task 1 | Cat 1 | 2007 | 01 | 40
Proj 1 | Task 1 | Cat 2 | 2007 | 02 | 20
Proj 1 | Task 1 | Cat 3 | 2007 | 03 | 35
Proj 1 | Task 1 | Cat 1 | 2008 | 01 | 40
Proj 1 | Task 1 | Cat 2 | 2008 | 02 | 40
Proj 1 | Task 1 | Cat 3 | 2008 | 03 | 40
Proposed Table
Project | Task | Category | Fiscal Month 01 | Fiscal Month 02 | Fiscal Month 03 | Fiscal Year
---------------------------------------------------------------------------------------------------------------------------------------------------
Proj 1 | Task 1 | Cat 1 | 40 | 0 | 0 | 2007
Proj 1 | Task 1 | Cat 2 | 0 | 20 | 0 | 2007Proj 1 | Task 1 | Cat 3 | 0 | 0 | 35 | 2007
Proj 1 | Task 1 | Cat 1 | 40 | 0 | 0 | 2008
Proj 1 | Task 1 | Cat 2 | 0 | 40 | 0 | 2008
Proj 1 | Task 1 | Cat 3 | 0 | 0 | 40 | 2008
Thanks,
Mike Misera
I am trying to create a page that adds users to a MS SQL database. In doing so, I have run into a couple errors that I can't seem to get past. I am hoping that I could get some assistance with them.
Error from SQL Debug:
---
Server: Msg 295, Level 16, State 3, Procedure AdminAddUser, Line 65
[Microsoft][ODBC SQL Server Driver][SQL Server]Syntax error converting character string to smalldatetime data type.
---
Error from page execution:
---
Exception Details: System.Data.OleDb.OleDbException: Error converting data type varchar to numeric.
Source Error:
Line 77: cmd.Parameters.Add( "@zip", OleDbType.VarChar, 100 ).Value = Request.Form("userZip")
Line 78:
Line 79: cmd.ExecuteNonQuery()
---
Below is what I currently have for my stored procedure and the pertinent code from the page itself.
Stored Procedure:
---
CREATE PROCEDURE dbo.AdminAddUser( @username varchar(100),
@password varchar(100),
@email varchar(100),
@acct_type varchar(100),
@realname varchar(100),
@billname varchar(100),
@addr1 varchar(100),
@addr2 varchar(100),
@city varchar(100),
@state varchar(100),
@country varchar(100),
@zip varchar(100),
@memo varchar(100) )
AS
BEGIN TRAN
--
-- Returns 1 if successful
-- 2 if username already exists
-- 0 if there was an error adding the user
--
SET NOCOUNT ON
DECLARE @error int, @rowcount int
--
-- Make sure that there isn't already a user with this username
--
SELECT userID
FROM users
WHERE username=@username
SELECT @error = @@ERROR, @rowcount = @@ROWCOUNT
IF @error <> 0 OR @rowcount > 0 BEGIN
ROLLBACK TRAN
RETURN 2
END
--
-- Set expiration date
--
DECLARE @expDate AS smalldatetime
IF @acct_type = "new_1yr" BEGIN
SET @expDate = DATEADD( yyyy, 1, GETDATE() )
END
IF @acct_type = "new_life" BEGIN
SET @expDate = DATEADD( yyyy, 40, GETDATE() )
END
DECLARE @paidCopies AS decimal
SET @paidCopies = 5
--
-- Add this user to the database
--
IF @acct_type <> "new" BEGIN
INSERT INTO users (userName, userPassword, userEmail, userJoinDate,
userPaidCopies, userExpirationDate, userLastPaidDate,
userBname, userRealName, userAddr1, userAddr2, userCity,
userState, userCountry, userZip, userMemo )
VALUES (@username, @password, @email, GETDATE(), @realname, @billname,
@paidCopies, @expDate, GETDATE(), @addr1, @addr2, @city, @state, @country, @zip, @memo )
SELECT @error = @@ERROR, @rowcount = @@ROWCOUNT
IF @error <> 0 OR @rowcount < 1 BEGIN
ROLLBACK TRAN
RETURN 0
END
END
IF @acct_type = "new" BEGIN
INSERT INTO users (userName, userPassword, userEmail, userJoinDate,
userBname, userRealName, userAddr1, userAddr2, userCity,
userState, userCountry, userZip, userMemo )
VALUES (@username, @password, @email, GETDATE(), @realname, @billname,
@addr1, @addr2, @city, @state, @country, @zip, @memo )
SELECT @error = @@ERROR, @rowcount = @@ROWCOUNT
IF @error <> 0 OR @rowcount < 1 BEGIN
ROLLBACK TRAN
RETURN 0
END
END
COMMIT TRAN
RETURN 1
GO
---
Page Code:
---
Sub AddUser(Sender as Object, e as EventArgs)
Dim db_conn_str As String
Dim db_conn As OleDbConnection
Dim resultAs Int32
Dim cmdAs OleDbCommand
db_conn_str = "Provider=SQLOLEDB;Data Source=localhost;Initial Catalog=xxxxx; User ID=xxxxx; PWD=xxxxx;"
db_conn = New OleDbConnection( db_conn_str )
db_conn.Open()
cmd = new OleDbCommand( "AdminAddUser", db_conn )
cmd.CommandType = CommandType.StoredProcedure
cmd.Parameters.Add( "result", OleDbType.Integer ).Direction = ParameterDirection.ReturnValue
cmd.Parameters.Add( "@username", OleDbType.VarChar, 100 ).Value = Request.Form("userName")
cmd.Parameters.Add( "@password", OleDbType.VarChar, 100 ).Value = Request.Form("userPass")
cmd.Parameters.Add( "@email", OleDbType.VarChar, 100 ).Value = Request.Form("userEmail")
cmd.Parameters.Add( "@acct_type", OleDbType.VarChar, 100 ).Value = Request.Form("userEmail")
cmd.Parameters.Add( "@realname", OleDbType.VarChar, 100 ).Value = Request.Form("userRealName")
cmd.Parameters.Add( "@billname", OleDbType.VarChar, 100 ).Value = Request.Form("userBname")
cmd.Parameters.Add( "@addr1", OleDbType.VarChar, 100 ).Value = Request.Form("userAddr1")
cmd.Parameters.Add( "@addr2", OleDbType.VarChar, 100 ).Value = Request.Form("userAddr2")
cmd.Parameters.Add( "@city", OleDbType.VarChar, 100 ).Value = Request.Form("userCity")
cmd.Parameters.Add( "@state", OleDbType.VarChar, 100 ).Value = Request.Form("userState")
cmd.Parameters.Add( "@country", OleDbType.VarChar, 100 ).Value = Request.Form("userCountry")
cmd.Parameters.Add( "@memo", OleDbType.VarChar, 100 ).Value = Request.Form("userMemo")
cmd.Parameters.Add( "@zip", OleDbType.VarChar, 100 ).Value = Request.Form("userZip")
cmd.ExecuteNonQuery()
(...)
---
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
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.
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?
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 RelatedHi, 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 RelatedHi 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();
}
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.
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.
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