Failed To Convert Parameter Value From A String To A Int32.(stored Procedures)

Jun 20, 2007

i had writen a stored procedure for inserting vlaues frma form into sql but iam getting this error can any pls help me

Failed to convert parameter value from a String to a Int32

my stored procedureALTER PROCEDURE sp_addcustomer



(@customerid as int,

@fname as char(10),@lname as char(10) ,

@companyname as char(10),@email as nvarchar(50),

@address as char(10),@city as char(10),

@zip as char(10),@country as char(10),

@phone as nvarchar(50),@state as char(10)

)



ASinsert into customer(customerid ,fname ,lname ,companyname, email , address ,city,zip,country,phone,state)

values(@customerid ,@fname ,@lname ,@companyname,@email,@address,@city,@zip,@country,@phone,@state)

RETURN

 

CODE IN C# IS

 SqlConnection cn = new SqlConnection(Session["conn"].ToString());

cn.Open();SqlCommand cmd = new SqlCommand();

cmd.Connection = cn;cmd.CommandType = CommandType.StoredProcedure;cmd.CommandText = "sp_addcustomer";

 

//SqlParameter p1, p2, p3, p4, p5, p6, p7, p8, p9, p10,p11;cmd.Parameters.Add(new SqlParameter("@customerid",SqlDbType.Int));

cmd.Parameters["@customerid"].Value =CustomerID.Text;cmd.Parameters.Add(new SqlParameter("@fname", SqlDbType.Char));

cmd.Parameters["@fname"].Value = fname.Text;cmd.Parameters.Add(new SqlParameter("@lname", SqlDbType.Char));

cmd.Parameters["@lname"].Value = lname.Text;cmd.Parameters.Add(new SqlParameter("@companyname", SqlDbType.Char));

cmd.Parameters["@companyname"].Value = companyname.Text;cmd.Parameters.Add(new SqlParameter("@email", SqlDbType.NVarChar));

cmd.Parameters["@email"].Value = email.Text;cmd.Parameters.Add(new SqlParameter("@address", SqlDbType.Char));

cmd.Parameters["@address"].Value = address.Text;cmd.Parameters.Add(new SqlParameter("@city", SqlDbType.Char));

cmd.Parameters["@city"].Value = city.Text;cmd.Parameters.Add(new SqlParameter("@zip", SqlDbType.NVarChar));

cmd.Parameters["@zip"].Value = zip.Text;cmd.Parameters.Add(new SqlParameter("@country", SqlDbType.Char));

cmd.Parameters["@country"].Value = Country.Text;cmd.Parameters.Add(new SqlParameter("@phone", SqlDbType.NVarChar));

cmd.Parameters["@phone"].Value = Phone.Text;cmd.Parameters.Add(new SqlParameter("@state", SqlDbType.Char));cmd.Parameters["@state"].Value = state.Text;

cmd.ExecuteNonQuery();

cn.Close();

Response.Redirect("main.aspx?st=Employee added successfully");

 

View 4 Replies


ADVERTISEMENT

Failed To Convert Parameter Value From A String To Int32

Apr 15, 2012

i am trying to save data from unbound datagridview but i get the error " format exception unhandled- failed to convert parameter value to a int32" the following is my code:

Private Sub btnPlace_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnPlace.Click
' Modify the following code to correctly connect to your SQL Server.
Dim Connection As New SqlConnection("Server=.SQLEXPRESS;Initial Catalog=Store Management System;Integrated Security=SSPI ")

[code]...

View 1 Replies View Related

Failed To Convert Parameter Value From A String To A Guid?

Jul 3, 2007

 Hi all,I have setup my database that have 3 columns:1. Primary Key2. UserId (UniqueIdentifier, Guid I guess) - I set it up so I can insert the value from the Membership table (UserId) for some relationship.3.  Another Foreign key with just an int. I tried to build a DAL, ran a test and received this error: "Failed to convert parameter value from a String to a Guid." Before I setup my UserId to be UniqueIdentifier and let it be just an Int, I don't have any problem retrieving data. Here is the SELECT query that I built with the DataSet:SELECT     aspnet_Users.UserId, t_music.MUSIC_TITLEFROM       t_user_viewedJOIN        aspnet_Users ON aspnet_Users.UserId = t_user_viewed.UserId JOIN       t_music ON t_music.MUSIC_PK = t_user_viewed.MUSIC_PK_FKWHERE   aspnet_Users.UserId = @UserId Any help would be greatly appreciated,Kenny. 

View 9 Replies View Related

Failed To Convert Parameter Value From A String To A Datetime

Nov 1, 2007

Hi I am having this problem. I have created a stored proc that uses dynamic sql. Now this works fine when I use it in Query Analyzer.

Now when I try to use this in Reporting services it gives me the error:
'
Failed to generate fields for the query
Check the query syntax or click refresh on query toolbar.
Failed to convert parameter value from a string to a datetime
'

My sotred proc is as:

<quote>
-------------------------------------------------------------------------------------------------------------------------------------

CREATE PROCEDURE [dbo].[rptDetailedAuditsIssued]

(

@param1 varchar(50) ,

@param2 varchar(50),

@param3 varchar(50),

@param4 datetime ,

@param5 datetime



)

AS

begin

declare @strSQL varchar(500)

set @strSQL = 'SELECT * FROM v_DetailedAuditsIssued WHERE '

if @param1 <> ''

Begin

set @strSQL = @strSQL + 'strRegion = ' + @param1 + ' AND '

End



if @param2 <> ''

Begin

set @strSQL = @strSQL + ' strTaxTypeCode = ' + @param2 + ' AND '

End



if @param3 <> ''

Begin

set @strSQL = @strSQL + ' strAuditType = ' + @param3 + ' AND '

End

set @strSQL = @strSQL + ' (dtmIssuedDate BETWEEN '''

+ CONVERT(nvarchar(30), @param4, 101) + ''' AND '''

+ CONVERT(nvarchar(30),@param5, 101) + ''')'



exec (@strSQ



end
--------------------------------------------------------------------------------------------------------------------------
</quote>

When I run


rptDetailedAuditsIssued '','','','1/1/2000','1/1/2001'in query editor it runs fine. But gives me that error every time I run it in SSRS. I think I got this problem but in earlier cases I used to set the param values to null in Repservices and it worked but its not working this time. This time it stops complaining but does generate any fields.

View 1 Replies View Related

Failed To Convert Parameter Value From String To Guid

Aug 1, 2007

Hi All,

I can run the report if I write following in the query and run it as text in report designer's data tab it works fine.

exec abc '9B95363B-F82D-4E55-AD89-2AD928AC981F',NULL,NULL,'07/03/2006',07/08/2006'

But when I am trying to run it as stored procedure as follow
abc
and I assign the same value for the parameter in Define Query Parameter dialog box. it gives following error.

"
an error occured while executing the query.
Failed to convert parameter value from string to guid.

Additiona Information :

failed to convert parameter value from a string to a guid.(system.data)
"

Thanks for help.

View 8 Replies View Related

Failed To Convert Parameter Value From A String To A Guid Error

Feb 10, 2008

Hi, i have some problems passing a guid parameter to a stored procedure; the code is below and the error i get is;
 Failed to convert parameter value from a String to a Guid
  conn = new SqlConnection(ConfigurationManager.ConnectionStrings["LocalSqlServer"].ConnectionString);
cmd = new SqlCommand("spInsKeyswap_history", conn);
cmd.CommandType = CommandType.StoredProcedure;
conn.Open();

MembershipUser mu = Membership.GetUser(User.Identity.Name);
guid gdUserID = mu.ProviderUserKey;

cmd.Parameters.Add("@user_title", SqlDbType.VarChar, 50);
cmd.Parameters.Add("@first_name", SqlDbType.VarChar, 20);
cmd.Parameters.Add("@last_name", SqlDbType.VarChar, 50);
cmd.Parameters.Add("@email", SqlDbType.VarChar, 50);
cmd.Parameters.Add("@birthday", SqlDbType.VarChar, 15);
cmd.Parameters.Add("@alternate_number", SqlDbType.VarChar, 50);
cmd.Parameters.Add("@msisdn", SqlDbType.VarChar, 50);
cmd.Parameters.Add("@call1", SqlDbType.VarChar, 50);
cmd.Parameters.Add("@call2", SqlDbType.VarChar, 50);
cmd.Parameters.Add("@call3", SqlDbType.VarChar, 50);
cmd.Parameters.Add("@new_sim_msidn", SqlDbType.VarChar, 50);
cmd.Parameters.Add("@dealer_id", SqlDbType.UniqueIdentifier);
cmd.Parameters.Add("@status_code", SqlDbType.Int);
cmd.Parameters.Add("@support_id", SqlDbType.Int);
cmd.Parameters.Add("@return_value", SqlDbType.Int);

cmd.Parameters["@user_title"].Value = txtTitle.Text.ToString();
cmd.Parameters["@first_name"].Value = txtFirstName.Text.ToString();
cmd.Parameters["@last_name"].Value = txtLastName.Text.ToString();
cmd.Parameters["@email"].Value = txtEmailAddress.Text.ToString();
cmd.Parameters["@birthday"].Value = txtBirthday.Text;
cmd.Parameters["@alternate_number"].Value = txtAlternate.Text.ToString();
cmd.Parameters["@msisdn"].Value = txtNew.Text.ToString();
cmd.Parameters["@call1"].Value = txtCall1.Text.ToString();
cmd.Parameters["@call2"].Value = txtCall2.Text.ToString();
cmd.Parameters["@call3"].Value = txtCall3.Text.ToString();
cmd.Parameters["@new_sim_msidn"].Value = txtOld.Text.ToString();
//get logged in users user_id from membership
cmd.Parameters["@dealer_id"].Value = gdUserID;
cmd.Parameters["@status_code"].Value = 1;
cmd.Parameters["@support_id"].Value = 0;


cmd.Parameters["@return_value"].Direction = ParameterDirection.ReturnValue;
cmd.ExecuteNonQuery();

int returnValue = (int)cmd.Parameters["@return_value"].Value; 
 
 

View 1 Replies View Related

Sqlhelper Class Failed To Convert Sql Parameter To A String Error

Oct 17, 2007

I'm getting this error and I'm not really sure what is wrong.  I'm using the sqlhelper class which was writen by microsoft, so I figure the code is right.
 Here is code of me passing in the strings.  Yes I tried puttinga .ToString() on the end of the sqlparameters.
public class LoginSelectData : DataAccessBase{private EndUser _enduser;public LoginSelectData(){    StoredProcedureName = StoredProcedure.Name.VERIFYUSER.ToString();}public DataSet Get(){DataSet ds;LoginSelectDataParameters _loginselectedataParameters = new LoginSelectDataParameters(this.Enduser);DataBaseHelper dbhelper = new DataBaseHelper(StoredProcedureName);ds = dbhelper.Run(base.ConnectionString, _loginselectedataParameters.Parameters);return ds;
}public EndUser Enduser{get { return _enduser; }set { _enduser = value; }}
}
public class LoginSelectDataParameters{private EndUser _enduser;private SqlParameter[] _parameters;public LoginSelectDataParameters(EndUser userinfo){this.Enduser = userinfo;
Build();
}
 private void Build(){
SqlParameter[] parameters = {new SqlParameter("@username",this.Enduser.Username),new SqlParameter("@password",this.Enduser.Password)
};
Parameters = parameters;}public SqlParameter[] Parameters{get { return _parameters; set { _parameters = value; }
}public EndUser Enduser{get { return _enduser; }set { _enduser = value; }}
}

View 4 Replies View Related

Convert Int32 To Int64

Feb 21, 2008

hi
i am doing union of two queries and getting result. as we know datatypes should match

for query1 it has int 64

now how do i convert int32 to int 64. i am just putting dummy values in quey2 to match query1

View 1 Replies View Related

How To Convert GUID GuidConverter ConvertTo Int32

Feb 15, 2007

Hi,
Can anyone help me figure out how to convert from type GUID to int32 format which will be the format to store in sqlserver database?
I have retrieved the UserId of a particular user in the current users profile (which is also a field in a row of data  in table aspnet_Users) using this method:
MembershipUser user = Membership.GetUser(CreateUserWizard1.UserName);
and now would like to cast the following
user.ProviderUserKey type int32.
I thought the way to go was to use a GuidConverter  by doing such as below by first assigning the user.ProviderUserKey in the _UserID variable and then calling:
TypeConverter converter = TypeDescriptor.GetConverter(_UserID);
Int32 newUserId = (Int32)converter.ConvertTo(_UserID, typeof(Int32));
But it blows up!  any ideas?
thanks
~M

View 4 Replies View Related

How Do I Convert A String To An Integer For A Parameter?

Dec 14, 2006

I want to convert a string into an interger so that my parameter can get one value, and have a seperate matrix list the value before the value selected.

My parameter is year. The user picks the year. And i want the crashcounts for the year displayed in the matrix. Then i have another matrix with a dataset similiar. I want this seperate Matrix/Dataset to display the previous year.

SO if the user selects 2004 from the dropdown. 2004 is displayed in the first matrix, and 2003 is displayed in the second matrix. The year attribute is in string format, and i cant change it in the cube. So i was told it could be converted in reporting services. with this



=CStr(CInt(Parameters!CrashStatisticalYear.Value)-1)



Question is, where do i put this...and how come when i put it in the parameters expression for the 2nd matrix, i get an error datatype messege? HELP !

View 2 Replies View Related

I Want To Convert Sql Stored Procedures To Dll

Jul 2, 2007

can anyone help me and excuse my terrible english. i want to convert sql stored procedures to dll.

View 1 Replies View Related

Stored Procedures, Parameter, Tablename

Jun 20, 2006

Hello,

I would like to pass into a stored procedure the name of a table. Following code does not work.
USE pubs
GO
CREATE PROC test1
@tableName varchar(40)
AS
SELECT * FROM @tableName
EXEC test1 @tableName='authors'

I would appreciate it very much if someone could help me with this.

Thanks

View 5 Replies View Related

Search For String In Stored Procedures

Aug 22, 2001

Ever needed to find a stored procedure with a specific string in it? You can pretty this up as a stored procedure and pass it a parm or cut and paste it into query analyzer.

select name from sysobjects
where id = (select id from syscomments
where text like '%like%')


Edit:

The above works only for a single hit. For multiple hits, this works

select name from sysobjects as A
join syscomments as B
on (text like '%cursor%')
where A.id = B.id

Live and learn,

Cat



Edited by - cat_jesus on 08/22/2001 10:09:49

Edited by - cat_jesus on 08/22/2001 10:10:29

View 15 Replies View Related

Convert A Date Stored As A String Into A Datetime

May 28, 2007

Hello forum,
Is it possible to convert a date stored as a string into a datetime with integration services 2005? My attempts with the €œdata conversion€? fail. The string type form of the date is €˜yyyy-mm-dd€™ and the desired result for use in a Union All is €˜dd/mm/yyyy 12:00:00AM.€™ This outcome is needs so that match on the date can populate a fact table, as the results are coming from two different databases.
All advice/help welcomed.
Ian

View 4 Replies View Related

Parameter Stored Procedures And Access Listboxes...Help!!!

Apr 22, 2001

I have upsized a complex Access 97 application to SQL Server 7, and want to substantially preserve the user interface design by using an Access 2000 project (*.adp) as the front end.

Most of the original Access queries accepted their parameters from screen form objects, and this works well using SQL 7 stored procedures as form recordsources.

However, Access 2000 does not seem to provide a way to tell a listbox or combo box how to pass a parameter value from a screen form object to its SQL 7 stored procedure rowsource.

These parametized listboxes are critical to the interface design.
Please help!!!

View 1 Replies View Related

Get Stored Procedures Parameter Names And Types...

Jun 1, 2006

Sorry if I haven't choose appropriate forum for this question.

I have MSSQL05 beta. I know how to list all stored procedures in selected database (everything is in localhost). I need to list parameter names and types for selected stored procedure(s).
How can I do that or anything that can return parameter names and types?

It's windows application.

View 3 Replies View Related

Stored Procedures, String Concatenation In Parameters

Jun 12, 2000

I guess I'm the only one with this problem -- couldn't find anything on it in the back questions. Maybe it's a weird problem. :)

Anyway, although I'm not new to SQL, I am a bit new to stored procedures, and MS SQL Server 7. (I've been using mySQL, decent, but doesn't have many features ... )

I used some ASP and stored procedure code from 4guysfromrolla.com for session tracking through SQL Server.

I've modified most of the stored procedures so that they actually work. :)

The tables it uses are simple:

sessions: sessionid (uniqueidentifier), date_stamp (datetime), sessionipaddr(varchar(50))

sessionvalues: sessionid (uniqueidentifier), sessionvalname (varchar(100)), sessionvaldata (varchar(8000))

To answer some questions before they're asked: It's a resume database, and does need to be able to store 8000 characters at a shot. (I'm hoping 8000 is as large as it gets for this particular field.)

There's only one problem now: One of the stored procedures enters information into the sessionvalue field of the table. However, much of our data contains apostrophes ('), and we need to be able to store them. I thought that modifying the execute statement would do it, something like:

EXECUTE sessiondata '{EC8131F6-409A-11D4-8E88-00A0C9E4F36E}', 'ExpWorkDescs', 'Here' + CHAR(39) + "s some data"

This doesn't work. Indeed, even if the concatenation worked, CHAR(39) doesn't in this context.

Then I thought I'd be really clever, and try a trick from mySQL:

EXECUTE sessiondata '{EC8131F6-409A-11D4-8E88-00A0C9E4F36E}', 'ExpWorkDescs', 'Here's some data'

Naturally, that one didn't work, either. (That was a long shot, admittedly!)

This is mission-critical. Not only apostrophes, but quotes and other punctuation marks must be able to be transferred. Anyone know a way to do it?

View 3 Replies View Related

Having Difficulty With FormView, Stored Procedures And Insert Parameter

Feb 17, 2005

I setup the databse and Visual Web Developer to use
stored procedures when the insert command is used. The
database also uses the field UserName that I pass using a
SessionParameter within the InserParameter block from the
Membership.GetUser.Username from the aspnet_ tables.

My difficulty is when using the "Formview" as the body to
with which to insert (I wanted the design versatility of
FormView) I get an error: "system.formatExpression: Input
string was not in a correct format" when I leave the
textboxes empty and invoke the insert command. I first
assumed that the bound textboxes are not being converted
correctly when left blank, so I used the
ConvertEmptyStringToNull=True, with no success. I then
coupled that with the Type=[correct format] still with
the same error. Alas, my final attempt was to set
defaults in the "ALTER PROCEDURE" within the stored
procedure itself.

Any help would be most appreciated. Thanks.
.

View 2 Replies View Related

Parameter Passing - Table Name & Column Name In Stored Procedures

Mar 9, 2001

How can a Stored Procedure use a variable table name and column name ?

The statement :-

SELECT @columnname FROM @tablename

gives error "Line 5: Incorrect syntax near '@tablename'."

I am passing the parameters 'tablename' and 'columnname' into the stored procedure, (in order to select a variable column from a variable table).

If I hard-code the tablename, then I get a column of output with just the name of the column I was trying to list, e.g. surname,

i.e. SELECT @columnname FROM employeetable gives :-

surname
surname
surname
surname
surname
surname


How can I get the procedure to accept a variable table name and column name ?


I thought one way to do it would be to use the 'EXEC' statement :-

SELECT @sql = 'SELECT '+ @columnname +' FROM '+@tablename
EXEC(@sql)

but this gives error : "Incorrect syntax near the keyword 'FROM' "

although the following table works :-

SELECT @sql = 'SELECT surname FROM '+@tablename
EXEC(@sql)

The problem seems to be mainly with variable column names.

Also, we don't really want to use the EXEC statement because it will not be compiled and will be slower. (Does anyone know by how much slower ?)


Any advice would be appreciated.

Kind regards,
Ian.
(ian.mitchell@sds.no)

View 1 Replies View Related

Replication System Stored Procedures Parameter Defaults ?

Oct 27, 2005

Hi there

View 5 Replies View Related

Execute SQL Task - Parameter Mapping For Stored Procedures

Dec 17, 2007

Hi ALL,

I have a Execute SQL Task to execute a stored procedure. It has no input and output parameters.

The stored procedure is defined as follows:


set ANSI_NULLS ON

set QUOTED_IDENTIFIER ON

go

CREATE PROCEDURE [dbo].[SetSLATimePriority]

AS

BEGIN

DECLARE @PriorityID tinyint,@MAXPriorityID tinyint

DECLARE @Priority NVARCHAR(MAX), @SLATime int

SET @PriorityID=1

SET @MAXPriorityID=0

SELECT @MAXPriorityID=MAX([Priority ID]) FROM [MTD Dashboard].[dbo].[Priority]

SET NOCOUNT ON;

WHILE @PriorityID<=@MAXPriorityID

BEGIN

SELECT @Priority= [Priority] FROM [MTD Dashboard].[dbo].[Priority]

WHERE [Priority ID]=@PriorityID

SELECT @SLATime= [SLA Time in hours] FROM [MTD Dashboard].[dbo].[Priority]

WHERE [Priority ID]=@PriorityID

UPDATE [MTD Dashboard].[dbo].[Remedy Dump-Filtered]

SET [SLA Time] = @SLATime WHERE [Priority] like @Priority

SET @PriorityID=@PriorityID+1

END

END


The Properties of Execute SQL Task are set as follows:

Result Set: None
Connection Type: OLEDB
SQL Source Type: Direct Input
SQL Statement: EXEC ? = [dbo].[SetSLATimePriority]
IsQueryStoredProcedure: True
ByPassPrepare: False

Parameter Mapping:

Variable Name : User::IntValue
Direction: ReturnValue
Data Type: Long
ParameterName: 0

I am getting the following error, when I run this package.

[Execute SQL Task] Error: Executing the query "EXEC ? = [dbo].[SetSLATimePriority]" failed with the following error: "Invalid parameter number". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.

I am not able to figure out, where exactly the problem is.. Can some one please help me out?

View 3 Replies View Related

Using Stored Procedures/parameters When WHERE String Has Optional Conditions

Sep 15, 2005

I've created a search page in my asp.net app that allows the user to enter optional parameters to narrow down the result set. It looks something like:Find all parts where:   manuafacturer:    <dropdownlist>ANY | manufacturer 1 |... </dropdownlist>   model:               <dropdownlist>ANY | model 1 |... </dropdownlist>   cost:                  between <textbox> and <textbox> dollarsCurrently I create the SQL command on the fly building the WHERE based on what the user selects. For example if in the form above they select    manufacturer = manufacturer1   model = ANY   cost = between 10 and 15the WHERE string is    ... WHERE manufacturer='manufacturer1' AND cost BETWEEN 10 AND 15Since the user doesn't care about model I leave it out of the WHERE. OK so here is my question. I want to move my queries to strored procedures however I'm not sure how to create the query since it changes based on what the user enters. Using the example above I'm assuming I can create one query with 4 parameters however what value would I use for ANY?    parameter1 (manufacturer) = "manufacturer1"   parameter2 (model)  = ???   parameter3 (price low) = 10   parameter4 (proce high) = 15I see there is an ANY operator in T-SQL but it doesn't look like the right thing to use. Should I use LIKE '%'? Seems that using LIKE would result in addition overhead.ThanksSimon

View 2 Replies View Related

Stored Procedures, Error Says Expects Parameter @Lname, Which Was Not Supplied.

Jun 21, 2007

this error comes up when I run a form to supmit information to the DB. .
 
"Procedure or function 'aspnet_Users_CreateUser' expects parameter '@Lname', which was not supplied"
I have it declared in the procedure. so why is it telling me it was not supplied.
Here is what I have:
 
ALTER PROCEDURE [dbo].aspnet_Users_CreateUser
    @ApplicationId   uniqueidentifier,
    @UserName   nvarchar(256),
    @IsUserAnonymous  bit,
    @LastActivityDate DATETIME,
  
    @Fname varchar(50) OUTPUT,
    @Lname  nvarchar(50) OUTPUT,
    @Address nvarchar(50) OUTPUT,
    @City  varchar(50) OUTPUT,
    @State  nchar(2) OUTPUT,
    @Zip  nchar(5) OUTPUT,
@RefNum  nchar(10) OUTPUT,@UserId  uniqueidentifier OUTPUTASBEGIN     IF( @UserId IS NULL )        SELECT @UserId = NEWID()    ELSE    BEGIN        IF( EXISTS( SELECT UserId FROM dbo.aspnet_Users                    WHERE @UserId = UserId ) )            RETURN -1    ENDINSERT dbo.aspnet_Users (ApplicationId, UserId, UserName, LoweredUserName, IsAnonymous, LastActivityDate, Fname, Lname, Address, City, State, Zip, RefNum)    VALUES (@ApplicationId, @UserId, @UserName, LOWER(@UserName), @IsUserAnonymous, @LastActivityDate, @Fname, @Lname, @Address, @City, @State, @Zip, @RefNum)     RETURN 0END
Thanks in advance

View 3 Replies View Related

Code Inside! --&> How To Return The @@identity Parameter Without Using Stored Procedures

Oct 30, 2005

Hi.here is my code with my problem described in the syntax.I am using asp.net 1.1 and VB.NETThanks in advance for your help.I am still a beginner and I know that your time is precious. I would really appreciate it if you could "fill" my example function with the right code that returns the new ID of the newly inserted row. 
Public Function howToReturnID(ByVal aCompany As String, ByVal aName As String) As Integer
'that is the variable for the new id.Dim intNewID As Integer
Dim strSQL As String = "INSERT INTO tblAnfragen(aCompany, aName)" & _                                    "VALUES (@aCompany, @aName); SELECT @NewID = @@identity"
Dim dbConnection As SqlConnection = New SqlConnection(connectionString)Dim dbCommand As SqlCommand = New SqlCommand()dbCommand.CommandText = strSQL
'Here is my problem.'What do I have to do in order to add the parameter @NewID and'how do I read and return the value of @NewID within that function howToReturnID'any help is greatly appreciated!'I cannot use SPs in this application - have to do it this way! :-(
dbCommand.Parameters.Add("@aFirma", aCompany.Trim)dbCommand.Parameters.Add("@aAnsprAnrede", aName.Trim)
dbCommand.Connection = dbConnection
TrydbConnection.Open()dbCommand.ExecuteNonQuery()
'here i want to return the new ID!Return intNewID
Catch ex As Exception
Throw New System.Exception("Error: " & ex.Message.ToString())
Finally
dbCommand.Dispose()dbConnection.Close()dbConnection.Dispose()
End Try
End Function

View 7 Replies View Related

Search For A Table/string Within Job Steps, Dts, Database, Stored Procedures Etc

May 16, 2007

Hi All :A couple of tables have been identified to be deleted. My job is tofind if it is at all used.On searching the web, i found a proc to search for a string within alldatabases in a server.using system sproc : sp_msforeachdbit searches for a string inviews, sprocs, functions, check constraints, defaults, foreign key,scalar function, inlined tablefunction, primary key, 'Replicationfilter stored procedure, System table, Table function, Trigger, 'Usertable, 'UNIQUE constraint''Extended stored procedure'So it is pretty extensive. But i dont think it is covering the codewithin execsqltasks in DTS, and tsql code within JOB STEPS. Those arethe two more places where code exists in my server.If any of you have done so in the past, do let me know if there is asystem stored proc or code that you have written, to do the samethanksRSLink to the above procedurehttp://www.sql-server-performance.c...ase_objects.asp

View 1 Replies View Related

SQL 2012 :: Parameter Error When Executing A Package With Built In Stored Procedures

Jul 23, 2014

I am using Excel VBA to run a stored procedure which executes a package using the built-in SQL Server stored procedures. The VBA passes two values from excel to the stored proc., which is then supposed to pass these "parameters" to the package to use as a variable within the package.

@Cycle sql_variant = 2
WITH EXECUTE AS 'USER_ACCOUNT' - account that signs on using windows authentication
AS
BEGIN
SET NOCOUNT ON;
declare @execution_id bigint

[code]....

When I try to execute the package, from SQL Server or Excel using the Macro I built, I get the following error:"The parameter '[User::Cycle]' does not exist or you do not have sufficient permissions." I have given the USER_ACCOUNT that runs executes the stored procedure permission to read/write to the database and the SSIS project folder.

View 4 Replies View Related

How To Assign String Value To TEXT Output Parameter Of A Stored Procedure?

Jul 23, 2005

Hello,I am currently trying to assign some string to a TEXT output parameterof a stored procedure.The basic structure of the stored procedure looks like this:-- 8< --CREATE PROCEDURE owner.StoredProc(@blob_data image,@clob_data text OUTPUT)ASINSERT INTO Table (blob_data, clob_data) VALUES (@blob_data, @clob_data);GO-- 8< --My previous attempts include using the convert function to convert astring into a TEXT data type:SET @clob_data = CONVERT(text, 'This is a test');Unfortunately, this leads to the following error: "Error 409: Theassignment operator operation cannot take a text data type as an argument."Is there any alternative available to make an assignment to a TEXToutput parameter?Regards,Thilo

View 1 Replies View Related

SIMPLE Command To Convert String To Number? Not CAST Or CONVERT.

Aug 15, 2006

Dear Experts,Ok, I hate to ask such a seemingly dumb question, but I'vealready spent far too much time on this. More that Iwould care to admit.In Sql server, how do I simply change a character into a number??????In Oracle, it is:select to_number(20.55)from dualTO_NUMBER(20.55)----------------20.55And we are on with our lives.In sql server, using the Northwinds database:SELECTr.regionid,STR(r.regionid,7,2) as a_string,CONVERT(numeric, STR(r.regionid,7,2)) as a_number,cast ( STR(r.regionid) as int ) as cast_to_numberFROM REGION R1 1.00112 2.00223 3.00334 4.0044SELECTr.regionid,STR(r.regionid,7,2) as a_string,CONVERT(numeric, STR(r.regionid,7,2) ) as a_number,cast (STR(r.regionid,7,2) as numeric ) as cast_to_numberFROM REGION R1 1.00112 2.00223 3.00334 4.0044Str converts from number to string in one motion.Isn't there a simple function in Sql Server to convertfrom string to number?What is the secret?Thanks

View 4 Replies View Related

Table Names In Stored Procedures As String Variables And Temporary Table Question

Apr 10, 2008

How do I use table names stored in variables in stored procedures?




Code Snippetif (select count(*) from @tablename) = 0 or (select count(*) from @tablename) = 1000000





I receive the error 'must declare table variable '@tablename''

I've looked into table variables and they are not what I would require to accomplish what is needed.
After browsing through the forums I believe I need to use dynamic sql particuarly involving sp_executesql. However, I am pretty new at sql and do not really understand how to use this and receive an output parameter from it(msdn kind of confuses me too). I am tryin got receive an integer count of the records from a certain table which can change to anything depending on what the user requires.




Code Snippet

if exists(Select * from sysobjects where name = @temptablename)
drop table @temptablename




It does not like the 'drop table @temptablename' part here. This probably wouldn't be an issue if I could get temporary tables to work, however when I use temporary tables i get invalid object '#temptable'.

Heres what the stored procedure does.
I duplicate a table that is going to be modified by using 'select into temptable'
I add the records required using 'Insert into temptable(Columns) Select(Columns)f rom TableA'
then I truncate the original table that is being modified and insert the temporary table into the original.

Heres the actual SQL query that produces the temporary table error.




Code Snippet
Select * into #temptableabcd from TableA

Insert into #temptableabcd(ColumnA, ColumnB,Field_01, Field_02)
SELECT ColumnA, ColumnB, Sum(ABC_01) as 'Field_01', Sum(ABC_02) as 'Field_02',
FROM TableB
where ColumnB = 003860
Group By ColumnA, ColumnB

TRUNCATE TABLE TableA

Insert into TableA(ColumnA, ColumnB,Field_01, Field_02)
Select ColumnA, ColumnB, Sum(Field_01) as 'Field_01', Sum('Field_02) as 'Field_02',
From #temptableabcd
Group by ColumnA, ColumnB




The above coding produces

Msg 208, Level 16, State 0, Line 1

Invalid object name '#temptableabcd'.

Why does this seem to work when I use an actual table? With an actual table the SQL runs smoothly, however that creates the table names as a variable problem from above. Is there certain limitation with temporary tables in stored procedures? How would I get the temporary table to work in this case if possible?

Thanks for the help.


View 6 Replies View Related

Stored Procedure Dbo.SalesByCategory Of Northwind Database: Enter The Query String - Query Attempt Failed. How To Do It Right?

Mar 25, 2008

Hi all,
In the Programmability/Stored Procedure of Northwind Database in my SQL Server Management Studio Express (SSMSE), I have the following sql:


USE [Northwind]

GO

/****** Object: StoredProcedure [dbo].[SalesByCategory] Script Date: 03/25/2008 08:31:09 ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

CREATE PROCEDURE [dbo].[SalesByCategory]

@CategoryName nvarchar(15), @OrdYear nvarchar(4) = '1998'

AS

IF @OrdYear != '1996' AND @OrdYear != '1997' AND @OrdYear != '1998'

BEGIN

SELECT @OrdYear = '1998'

END

SELECT ProductName,

TotalPurchase=ROUND(SUM(CONVERT(decimal(14,2), OD.Quantity * (1-OD.Discount) * OD.UnitPrice)), 0)

FROM [Order Details] OD, Orders O, Products P, Categories C

WHERE OD.OrderID = O.OrderID

AND OD.ProductID = P.ProductID

AND P.CategoryID = C.CategoryID

AND C.CategoryName = @CategoryName

AND SUBSTRING(CONVERT(nvarchar(22), O.OrderDate, 111), 1, 4) = @OrdYear

GROUP BY ProductName

ORDER BY ProductName

////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
From an ADO.NET 2.0 book, I copied the code of ConnectionPoolingForm to my VB 2005 Express. The following is part of the code:

Imports System.Collections.Generic

Imports System.ComponentModel

Imports System.Drawing

Imports System.Text

Imports System.Windows.Forms

Imports System.Data

Imports System.Data.SqlClient

Imports System.Data.Common

Imports System.Diagnostics

Public Class ConnectionPoolingForm

Dim _ProviderFactory As DbProviderFactory = SqlClientFactory.Instance

Public Sub New()

' This call is required by the Windows Form Designer.

InitializeComponent()

' Add any initialization after the InitializeComponent() call.

'Force app to be available for SqlClient perf counting

Using cn As New SqlConnection()

End Using

InitializeMinSize()

InitializePerfCounters()

End Sub

Sub InitializeMinSize()

Me.MinimumSize = Me.Size

End Sub

Dim _SelectedConnection As DbConnection = Nothing

Sub lstConnections_SelectedIndexChanged(ByVal sender As Object, ByVal e As EventArgs) Handles lstConnections.SelectedIndexChanged

_SelectedConnection = DirectCast(lstConnections.SelectedItem, DbConnection)

EnableOrDisableButtons(_SelectedConnection)

End Sub

Sub DisableAllButtons()

btnAdd.Enabled = False

btnOpen.Enabled = False

btnQuery.Enabled = False

btnClose.Enabled = False

btnRemove.Enabled = False

btnClearPool.Enabled = False

btnClearAllPools.Enabled = False

End Sub

Sub EnableOrDisableButtons(ByVal cn As DbConnection)

btnAdd.Enabled = True

If cn Is Nothing Then

btnOpen.Enabled = False

btnQuery.Enabled = False

btnClose.Enabled = False

btnRemove.Enabled = False

btnClearPool.Enabled = False

Else

Dim connectionState As ConnectionState = cn.State

btnOpen.Enabled = (connectionState = connectionState.Closed)

btnQuery.Enabled = (connectionState = connectionState.Open)

btnClose.Enabled = btnQuery.Enabled

btnRemove.Enabled = True

If Not (TryCast(cn, SqlConnection) Is Nothing) Then

btnClearPool.Enabled = True

End If

End If

btnClearAllPools.Enabled = True

End Sub

Sub StartWaitUI()

Me.Cursor = Cursors.WaitCursor

DisableAllButtons()

End Sub

Sub EndWaitUI()

Me.Cursor = Cursors.Default

EnableOrDisableButtons(_SelectedConnection)

End Sub

Sub SetStatus(ByVal NewStatus As String)

RefreshPerfCounters()

Me.statusStrip.Items(0).Text = NewStatus

End Sub

Sub btnConnectionString_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnConnectionString.Click

Dim strConn As String = txtConnectionString.Text

Dim bldr As DbConnectionStringBuilder = _ProviderFactory.CreateConnectionStringBuilder()

Try

bldr.ConnectionString = strConn

Catch ex As Exception

MessageBox.Show(ex.Message, "Invalid connection string for " + bldr.GetType().Name, MessageBoxButtons.OK, MessageBoxIcon.Error)

Return

End Try

Dim dlg As New ConnectionStringBuilderDialog()

If dlg.EditConnectionString(_ProviderFactory, bldr) = System.Windows.Forms.DialogResult.OK Then

txtConnectionString.Text = dlg.ConnectionString

SetStatus("Ready")

Else

SetStatus("Operation cancelled")

End If

End Sub

Sub btnAdd_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnAdd.Click

Dim blnError As Boolean = False

Dim strErrorMessage As String = ""

Dim strErrorCaption As String = "Connection attempt failed"

StartWaitUI()

Try

Dim cn As DbConnection = _ProviderFactory.CreateConnection()

cn.ConnectionString = txtConnectionString.Text

cn.Open()

lstConnections.SelectedIndex = lstConnections.Items.Add(cn)

Catch ex As Exception

blnError = True

strErrorMessage = ex.Message

End Try

EndWaitUI()

If blnError Then

SetStatus(strErrorCaption)

MessageBox.Show(strErrorMessage, strErrorCaption, MessageBoxButtons.OK, MessageBoxIcon.Error)

Else

SetStatus("Connection opened succesfully")

End If

End Sub

Sub btnOpen_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnOpen.Click

StartWaitUI()

Try

_SelectedConnection.Open()

EnableOrDisableButtons(_SelectedConnection)

SetStatus("Connection opened succesfully")

EndWaitUI()

Catch ex As Exception

EndWaitUI()

Dim strErrorCaption As String = "Connection attempt failed"

SetStatus(strErrorCaption)

MessageBox.Show(ex.Message, strErrorCaption, MessageBoxButtons.OK, MessageBoxIcon.Error)

End Try

End Sub

Sub btnQuery_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnQuery.Click

Dim queryDialog As New QueryDialog()

If queryDialog.ShowDialog() = System.Windows.Forms.DialogResult.OK Then

Me.Cursor = Cursors.WaitCursor

DisableAllButtons()

Try

Dim cmd As DbCommand = _SelectedConnection.CreateCommand()

cmd.CommandText = queryDialog.txtQuery.Text

Using rdr As DbDataReader = cmd.ExecuteReader()

If rdr.HasRows Then

Dim resultsForm As New QueryResultsForm()

resultsForm.ShowResults(cmd.CommandText, rdr)

SetStatus(String.Format("Query returned {0} row(s)", resultsForm.RowsReturned))

Else

SetStatus(String.Format("Query affected {0} row(s)", rdr.RecordsAffected))

End If

Me.Cursor = Cursors.Default

EnableOrDisableButtons(_SelectedConnection)

End Using

Catch ex As Exception

Me.Cursor = Cursors.Default

EnableOrDisableButtons(_SelectedConnection)

Dim strErrorCaption As String = "Query attempt failed"

SetStatus(strErrorCaption)

MessageBox.Show(ex.Message, strErrorCaption, MessageBoxButtons.OK, MessageBoxIcon.Error)

End Try

Else

SetStatus("Operation cancelled")

End If

End Sub
///////////////////////////////////////////////////////////////////////////////////////////////////////////////////////
I executed the code successfully and I got a box which asked for "Enter the query string".
I typed in the following: EXEC dbo.SalesByCategory @Seafood. I got the following box: Query attempt failed. Must declare the scalar variable "@Seafood". I am learning how to enter the string for the "SQL query programed in the subQuery_Click(ByVal sender As Object, ByVal e As EventArgs) Handles btnQuery.Click" (see the code statements listed above). Please help and tell me what I missed and what I should put into the query string to get the information of the "Seafood" category out.

Thanks in advance,
Scott Chang

View 4 Replies View Related

Oracle Stored Procedures VERSUS SQL Server Stored Procedures

Jul 23, 2005

I want to know the differences between SQL Server 2000 storedprocedures and oracle stored procedures? Do they have differentsyntax? The concept should be the same that the stored proceduresexecute in the database server with better performance?Please advise good references for Oracle stored procedures also.thanks!!

View 11 Replies View Related

Stored Procedures 2005 Vs Stored Procedures 2000

Sep 30, 2006

Hi,



This Might be a really simple thing, however we have just installed SQL server 2005 on a new server, and are having difficulties with the set up of the Store Procedures. Every time we try to modify an existing stored procedure it attempts to save it as an SQL file, unlike in 2000 where it saved it as part of the database itself.



Thank you in advance for any help on this matter



View 1 Replies View Related

All My Stored Procedures Are Getting Created As System Procedures!

Nov 6, 2007



Using SQL 2005, SP2. All of a sudden, whenever I create any stored procedures in the master database, they get created as system stored procedures. Doesn't matter what I name them, and what they do.

For example, even this simple little guy:

CREATE PROCEDURE BOB

AS

PRINT 'BOB'

GO

Gets created as a system stored procedure.

Any ideas what would cause that and/or how to fix it?

Thanks,
Jason

View 16 Replies View Related







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