Noob: StoredProcedure, Null For Parameters?
Jun 27, 2006
I know it's unbelievable, but i didn't ever use SP before. Sorry for this noobish question, don't beat me for this - please ;-)
In my SELECT Query i like to have some (WHERE) paramteters le's say: ID, NAME and AGE. I allways programmicali generate a WHERE statement in relation to which parameters I really get, because not allwasy i get all the parameters together. This is fine for "normal" Tables.
How does this work with SP? If I don't get NAME, do I set the SP-Parameter NAME to null to unconsider it? Or do I have to make some IF statements in the SP to check, if NAME has some value?
Thanks for a short hint!
View 6 Replies
ADVERTISEMENT
Mar 18, 2008
Hi,
I do have a stored procedure with parameters inside. I just want to ignore those parameters that are NULL so that my WHERE clause will not execute them anymore. Here's my code:
CREATE PROCEDURE SEARCHPATIENT
@patnCode varchar(10) = NULL,
@patnSurname varchar(20) = NULL,
@patnGivenName1 varchar(20) = NULL
AS
SELECT....
FROM ...
WHERE patnCode=@patncode AND
patnSurname =@patnSurname AND
patnGivenName1 =@patnGivenName1
BUT...
If the user passes @patnSurname as NULL, I got an SQL error message:
"There was an error executing the query.. Timeout expired... ". I was hoping I could write an WHERE clause where IT WILL ONLY EXECUTE those parameters WITH VALUE and IGNORE those NULLs.
Can someone help me on this?
Can I use IF THEN ELSE statement inside the WHERE clause?
Can I use CASE STATEMENT inside the WHERE clause?
Thanks in advance.
Joseph
View 3 Replies
View Related
May 8, 2008
Hi,
I want to know how to write stored procedure with parameters. And i want to compare this parameters.
I have DropDownList and RadioButtonList in my Web Application.
How to write Procedure passing this Two control parameters.(DropDown and RadioList).
In RadioButtonList having 5 selections.
If selection 1 happens
-- some condition
if selection2 hapens
-- some condition
similarly 3,4,5
------------------
How to write conditions in storeprocedure.
Please help me i am not having exp on storedprocedure.
Thanks
View 1 Replies
View Related
Apr 26, 2007
How do you configure a multi-value parameter so it will allow the user to not enter the parameter?
I'm using integer multi-value parameters. I can't set the parameter to allow null, and when I preview the report and leave the parameter blank, it tells me to enter at least one value. I tried it with string parameters, and the report just doesn't run in preview if you don't enter any values, even if you choose "allow blank".
How do you specify a multi-value parameter to allow "empty" or "null"?
View 5 Replies
View Related
Aug 23, 2007
I am using a SQLDataSource with Stored Procedures. The Select, Insert and Update all work well. However I cannot get the delete to work. My stored procedures are tested and verified and the parameter names are the same as the source columns. When I try to run the delete an error that the stored procedure expects the parameter @locationStationId, however this value passes properly for the Update command?!? I tried to change the parameter to original_locationStationID to pass the original value, however this result in Null being passed for the parameter.
I cannot understand why this works for Update and passes the location ID, but will not work for DELETE. Can anyone shed any light onto the matter?
Thanks.OldValuesParameterFormatString="original_{0}" UpdateCommand="spUpdateLocation" UpdateCommandType="StoredProcedure"
DeleteCommand="spDeleteLocation" DeleteCommandType="StoredProcedure">
<DeleteParameters>
<asp:Parameter Name="locationStationId" Type="String" />
</DeleteParameters>
<InsertParameters>
<asp:Parameter Name="locationStationId" Type="String" />
<asp:Parameter Name="locationType" Type="String" />
<asp:Parameter Name="locationName" />
<asp:Parameter Name="division" Type="String" />
</InsertParameters>
View 3 Replies
View Related
Apr 25, 2007
I'm new to SQL Server, so if I'm doing anything stupid don't bemean :)I have a procedure that I use to return data based on optionalparameters. It works fine, except when the underlying data contains anull on one if the fields being searched.My system uses a default wildcard for all parameters, so this excludessuch records. I need a way to add in " OR fldName IS NULL " where theparameter is empty or '%'. I've looked at using CASE WHEN, but itdoesnt seem to like SQL Keywords being part of the WHEN clause.I'd hate to have to resort to executing concatonated strings made fromIF and ELSE statements. Just too messy and not at all pretty!Any Ideas? Here's what I've got:ALTER PROCEDURE [dbo].[procFindUnits]@strUnitIDnvarchar = '%',@strProjectNamenvarchar = '%',@strAddressnvarchar = '%',@strTenancynvarchar = '%',@strTenurenvarchar = '%'ASBEGINSET NOCOUNT ON;SELECTtblUnits.strUnitID,tblProjects.strProjectName,qryAddresses.Address_OneLine,lkpTenancyTypes.strTenancyType,lkpTenureTypes.strTenureTypeFROM tblUnits INNER JOINtblProjects ON tblUnits.intProjectID = tblProjects.intProjectIDLEFT OUTER JOINlkpTenancyTypes ON tblUnits.intTenancyType =lkpTenancyTypes.intTenancyType LEFT OUTER JOINlkpTenureTypes ON tblUnits.intTenureType =lkpTenureTypes.intTenureTypeID LEFT OUTER JOINqryAddresses ON tblUnits.strUnitID = qryAddresses.strUnitIDWHERE(tblUnits.strUnitID LIKE @strUnitID)AND (tblProjects.strProjectName LIKE @strProjectName)AND (qryAddresses.Address_OneLine LIKE @strAddress)AND (lkpTenancyTypes.strTenancyType LIKE @strTenancy)AND (lkpTenureTypes.strTenureType LIKE @strTenure)END
View 9 Replies
View Related
Dec 1, 2007
Report Parameters:
When I check "Allow null value" shouldn't <null> be in my dropdown list?
I am doing Microsoft Report in Visual Studio 2005 and all I am trying to do is allow null value combine with available values from a query.
View 10 Replies
View Related
Jan 19, 2008
i have a form where filter information is going to be keyed/selected.
the data from that form is copied into a struct regardless of whether each field has data in it or not (eg firstname may have been entered but not lastname,
so the struct would contain firstname = 'john' and lastname = null)
i then want to build an sql statement (not using stored procedures but just direct sql) from this struct data but obvioulsy can't just have
a statement that says 'select ID from theTable where firstname='john' AND lastname=''" as this won't bring back the right info, if anything at all.
i normally build the statements like this
Code Block
string sql = "select ID from MembersTemp where FirstName = '{0}'";
SqlCommand cmd = new SqlCommand(string.Format(sql, FirstName), _con);
_con.Open();
SqlDataReader rdr = cmd.ExecuteReader();
List<int> result = new List<int>();
while (rdr.Read())
{
etc....
so really, what's the best way of building an sql statement where some search parameters won't hold a value and therefore need ignoring.
also, whilst i'm here, i use viz studio 2005 and sql server 2005 (express??), so that does mean i'm using TSQL as well??
View 10 Replies
View Related
Oct 29, 2007
I have created a report with a stored proc which takes a parameter whose default value is null. Now when I run my stored proc in Enterprise Manager without any params I get 4 rows otherwise with proper parameter I get 2 rows.
Now I set the params value to allow for Null and blank values. When I run the report if I provide parameter value it works fine but if i try to run it without any param it shows an empty report while the stored proc running without params shows 4 rows.
I have earlier sucessfully created reports where stored procs had 4 params out of which 3 where defaulted to null. And so I set the allow nulls, Allow Blanks values to be true for those params in Rep services. So when I selected value for 1st param and ran the report I got the desired report. But this time I have only one param which can be null. But its not working.
Thanks in advance.
View 1 Replies
View Related
May 30, 2006
I am using version 9.00.2047.00 SP1 of Visual Studio 2005.
Using ADO.NET, I have been unable to get the Execute SQL task to successfully return the value of an output parameter defined as varchar or nvarchar when the value is null. No other data types seem to have this problem, including the sql_variant data type.
Here is the stored procedure I am calling:
create proc spx
@in int = null output,
@vc nvarchar(10) = null output,
@dt datetime = null output
as
select
@in = null,
@vc = null,
@dt = null
return
The variables to which the three output parameters return their values have a data type of Object. The task runs fine when the integer or datetime parameters are used, and the variables can be identified as null using IsDBNull. But as soon as the nvarchar (or varchar) parameter is included, the task fails with this message:
"The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Parameter 1 ("@vc"): Data type 0xE7 has an invalid data length or metadata length."
I have seen a couple of postings that sound similar to this problem, but so far I have found no resolution. Any advice would be much appreciated.
Thanks,
Ron Rice
View 11 Replies
View Related
Oct 4, 2007
Hi,
I have a CLR function that throws an error if one of the parameters is NULL. Am I using the IsNullable tag correctly or am I supposed to do this another way? The function simply formats decimal values using .NET culture information and returns a string. Thanks very much for any help. -- Erik
[Microsoft.SqlServer.Server.SqlFunction(IsDeterministic=true, DataAccess=DataAccessKind.None)]
[return: SqlFacet(MaxSize = 30, IsNullable=true)]
public static string FormatGeneralDecimal([SqlFacet(Precision = 28, Scale = 8, IsNullable = true)] SqlDecimal sqlDc,
[SqlFacet(MaxSize = 10)] string cultureName)
{
string result = null;
if (!sqlDc.IsNull)
{
CultureInfo ci = CultureInfo.CreateSpecificCulture(cultureName);
result = sqlDc.Value.ToString("G", ci);
}
return result;
}
It works great unless I call it with a NULL value for sqlDc, in which case I get this:
select [dbo].[FormatGeneralDecimal](NULL, 'de-DE')
Msg 6522, Level 16, State 2, Line 1
A .NET Framework error occurred during execution of user-defined routine or aggregate "FormatGeneralDecimal":
System.Data.SqlTypes.SqlNullValueException: Data is Null. This method or property cannot be called on Null values.
System.Data.SqlTypes.SqlNullValueException:
at System.Data.SqlTypes.SqlDecimal.ToDecimal()
at System.Data.SqlTypes.SqlDecimal.get_Value()
at MyFunctions.FormatGeneralDecimal(SqlDecimal sqlDc, String cultureName)
View 1 Replies
View Related
Apr 7, 2008
Hi
I am trying to view my reporting service web site for the first time,
but I've got this Error message "StringStartsWith can't accept null parameters"
I am using Widows Vista and
Report Manager and Report Server Web site has been set to Default web Site and
Windows Server Identity.BuiltIn Account has been set to Local System and
Web Service Identity.ReportServer and Report Manager has been set to Classic .Net App Pool.
Can anybody help me on this issue.
Best Regards
View 3 Replies
View Related
Apr 18, 2007
I have an event:
Private Sub SqlDataSourceIncome_Deleted(ByVal sender As Object, ByVal e As System.Web.UI.WebControls.SqlDataSourceStatusEventArgs) Handles SqlDataSourceIncome.Deleted
Dim command As SqlClient.SqlCommand
command = e.Command
If command.Parameters("@nReturnCode").Value <> 0 Then
DROPDEAD()
End If
That fires from:
<DeleteParameters>
<asp:Parameter Name="nDeletebyId" Type="Int64" />
<asp:Parameter Name="nOtherId" Type="Int64" />
<asp:Parameter Direction="Output" Name="nReturnCode" Type="Int64" />
<asp:Parameter Direction="Output" Name="nReturnId" Type="Int64" />
</DeleteParameters>
End Sub
When I:
GridViewIncome.DeleteRow(GridViewIncome.SelectedRow.RowIndex)
But nReturnCode is ALWAYS NULL... I even did a stored procedure that just:
ALTER PROCEDURE [dbo].[sp_nDeletebyId]
@nReturnCode bigint output,
@nReturnId bigint output AS
SET @nReturnCode = 0
SET @nReturnId = 0
And STILL got nothing but the NULLS... the insert & update stuff works fine, with identical code... it's just the DELETED event that I can't seem to knock. Has anyone seen this before? The above sample stored proc did return 0 when executed one the server...
and, BTW, the row is deleted!
Chip Kigar
View 2 Replies
View Related
May 12, 2015
I'm trying to have a default or null value in the dropdown list of the parameters on SSRS report. The dataset is bound with the Dynamics-AX 2009 AOT query. In the screen shot below you can see that I need a show All option in the dropdown list.
View 7 Replies
View Related
Sep 20, 2006
Hey. I need to substitute a value from a table if the input var is null. This is fine if the value coming from table is not null. But, it the table value is also null, it doesn't work. The problem I'm getting is in the isnull line which is in Dark green color because @inFileVersion is set to null explicitly and when the isnull function evaluates, value returned from DR.FileVersion is also null which is correct. I want the null=null to return true which is why i set ansi_nulls off. But it doesn't return anything. And the select statement should return something but in my case it returns null. If I comment the isnull statements in the where clause, everything works fine. Please tell me what am I doing wrong. Is it possible to do this without setting the ansi_nulls to off??? Thank you
set ansi_nulls off
go
declare
@inFileName VARCHAR (100),
@inFileSize INT,
@Id int,
@inlanguageid INT,
@inFileVersion VARCHAR (100),
@ExeState int
set @inFileName = 'A0006337.EXE'
set @inFileSize = 28796
set @Id= 1
set @inlanguageid =null
set @inFileVersion =NULL
set @ExeState =0
select Dr.StateID from table1 dR
where
DR.[FileName] = @inFileName
AND DR.FileSize =@inFileSize
AND DR.FileVersion = isnull(@inFileVersion,DR.FileVersion)
AND DR.languageid = isnull(@inlanguageid,null)
AND DR.[ID]= @ID
)
go
set ansi_nulls on
View 3 Replies
View Related
Feb 12, 2008
Hi all,
I want to learn about sp with examples,so can any one give me the best urls regarding this.and i want to write single sp for all like (insert,delete and update)in a single sp.Please guide me.
Thank You
View 1 Replies
View Related
Aug 27, 2004
Hi
I use the next StoredProcedure in Access, Inserts Users to tblUsers:
INSERT INTO tblUsers ( UserName, Password, RetypePassword, Email, Comments )
VALUES (@UserName, @Password, @RetypePassword, @Email,@Comments);
How do i have to write it in SQL?
Thank's.
View 1 Replies
View Related
Mar 12, 2008
Hi
I am New to Sql Server. Now i have to write two stored procedures.
Here are my requirements. If any one help please.
1)People often ask me to do this as well... change the name of the underwriter. For Mortgage Network underwriters (different than MGIC underwriters) all you need to do is:
Update mnetwork..unw_Nola set underwriter='<underwriters username>' where LoanID='<LoanID>'
So, I need you to write a stored procedure that will do exactly that. If you don't enter a loanID or username, the stored procedure should tell you that it can't complete the task and why. Also, the list of underwriters can be found in:
select LoginName from mnetwork..unw_LoginLookup where UnderwriterName = '<Name on the email>'
So for this one, you would select where underwriterName='John Brennan'.
For most usernames, it's just first initial last name (jbrennan in this case). You can make the stored procedure to both, if you want. If you enter an underwriter name, then it will translate to the loginname. If you enter the login name, it will just use that. You don't have to do all of that if you don't want. Just make sure the procedure verifies that the username is correct (in the table) and that is enough.
Tables for this storedprocedure:
Table Name:mnetwork..unw_Nola
Table Fields:LoanID,ConditionSet,Status,Revised,RevisedBy,PDFNOLA,MonthlyIncome,DocExpDate,Notes,
Underwriter,rowguid,ApprovedDate.MovedToCentera,FK_UserID,RecordDate
TablName:mnetwork..unw_LoginLookup
Table Fields: [LoginLookupID], [LoginName], [UnderwriterName], [FirstName], [LastName], [Title], [Signature], [Address1], [Address2], [Addr1], [Addr2], [City], [State], [Zip], [Phone], [Phone2], [Fax], [Email], [DefaultSet], [rowguid], [FK_UserID], [RecordDate], [Createdby], [LastUpdated], [UpdatedBy]
2)1. Block this loan
2. Grant me access to this blocked loan.
So, I need you to write either one or two stored procedures that will accomplish the following:
When a loan needs to be blocked, it needs to be added to the mnetwork..sec_BlockedLoans table
When a person needs access to that loan, their username needs to be added to the mnetwork..sec_LoanAccess table.
Tables:
1) SELECT [LoanID], [Username], [Grantor], [GrantDate] FROM [mnetwork].[dbo].[sec_LoanAccess]
2) SELECT [LoanID], [Added] FROM [mnetwork].[dbo].[sec_BlockedLoans
any one can help to write these stored procedure.
Thanks,
JT
View 1 Replies
View Related
Jun 27, 2006
I am using SQL Server 2005 now and I have a table with following columns.
ID, FirstName, LastName, Email
"ID" is the primary key (int) and is set auto generated (1 increment)
I have a StoredProcedure to insert a new record.
CREATE PROCEDURE Candidate_Create @FName nvarchar(255), @LName nvarchar(255), @Email nvarchar(255)ASINSERT INTO Candidate (FirstName, LastName, Email)VALUES (@FName, @LName, @Email)GO
I want the ID to be returned as the same time when a new record is inserted, how can I do it ? Is it possible ?
View 3 Replies
View Related
Aug 22, 2007
Hi,I'm wodering if it's possible (and the correct syntax) to make a JOIN between a Table and a SP's result. This is my code, but it goes in error in the EXEC:1 SET ANSI_NULLS ON
2 GO
3 SET QUOTED_IDENTIFIER ON
4 GO
5 -- =============================================
6 -- Author:Luca de Angelis
7 -- Create date: 22/08/2007
8 -- Description:Inserimento dei dati contabili nella tabella di log
9 -- =============================================
10 CREATE PROCEDURE dbo.InsertIntoLog_dati_contabili
11 -- Add the parameters for the stored procedure here
12 @id_gestore tinyint
13 AS
14 SET NOCOUNT ON
15 BEGIN TRANSACTION
16 INSERT INTO CEL_log_dati_contabili(numero_telefonico, anno, mese, id_gestore, id_tipo_log)
17 SELECT CEL_traffico_temp.numero_telefonico
18 , CEL_traffico_temp.anno
19 , CEL_traffico_temp.mese
20 , @id_gestore as id_gestore
21 , 1
22 FROM CEL_traffico_temp
23 INNER JOIN
24 (EXEC dbo.CEL_SimConGestoreNoFilePeriodo @id_gestore, CEL_traffico_temp.anno + CEL_traffico_temp.mese) AS tabella
25 ON CEL_traffico_temp.numero_telefonico = tabella.numero_telefonico
26
27 INSERT INTO CEL_log_dati_contabili(numero_telefonico, anno, mese, id_gestore, id_tipo_log)
28 SELECT CEL_traffico_temp.numero_telefonico
29 , CEL_traffico_temp.anno
30 , CEL_traffico_temp.mese
31 , @id_gestore as id_gestore
32 , 2
33 FROM CEL_traffico_temp
34 INNER JOIN
35 (EXEC CEL_SimNelFileNoGestore @id_gestore) AS tabella
36 ON CEL_traffico_temp.numero_telefonico = tabella.numero_telefonico
37
38 INSERT INTO CEL_log_dati_contabili(numero_telefonico, anno, mese, id_gestore, id_tipo_log)
39 SELECT CEL_traffico_temp.numero_telefonico
40 , CEL_traffico_temp.anno
41 , CEL_traffico_temp.mese
42 , @id_gestore as id_gestore
43 , 3
44 FROM CEL_traffico_temp
45 INNER JOIN
46 EXEC CEL_SimNelFileNoUtente @id_gestore AS tabella
47 ON CEL_traffico_temp.numero_telefonico = tabella.numero_telefonico
48
49 IF @@error <> 0
50 BEGIN
51 ROLLBACK TRANSACTION
52 END
53 ELSE
54 BEGIN
55 COMMIT TRANSACTION
56 END
Help me please...
View 2 Replies
View Related
May 14, 2008
Visual Studio 2008 Code VB
I'm trying to create a stored procedure that will update a database table. I want to make sure that duplicate records are not inserted into the Database Table, so I used IF NOT EXISTS . With the below code I can update the table, however, you can not add additional rows to the table.
Could someone tell me what is wrong, or how to fix it?
Thanks! losssoc ALTER PROCEDURE dbo.CaseDataInsert
@ReportType varchar(50),@CreatedBy varchar(50),
@OpenDate smalldatetime,@Territory varchar(10),
@Region varchar(10),@StoreNumber varchar(10),
@StoreAddress varchar(200),@TiplineID varchar(50),
@Status varchar(50),@CaseType varchar(200),
@Offense varchar(200)
AS
BEGIN
IF NOT EXISTS(SELECT ReportType,CreatedBy,OpenDate,Territory,Region,StoreNumber,StoreAddress,TiplineID,Status,CaseType,Offense FROM CaseData)INSERT CaseData(ReportType, CreatedBy,OpenDate,Territory,Region,StoreNumber,StoreAddress,TiplineID,
Status,CaseType,Offense)VALUES(@ReportType,@CreatedBy,@OpenDate,@Territory,@Region,@StoreNumber,@StoreAddress,@TiplineID,
@Status,@CaseType,@Offense)
END
View 12 Replies
View Related
Jun 11, 2005
To all,
I looked at the MS-SQL pubs sample database and execute the example
stored procedure reptq2 and I got 17 results set back. Where can I find
an example using Visual Studio DataGrid or any means to get all these
results from this SP.
Thanks,
Frank
View 3 Replies
View Related
Apr 16, 2006
Hi
public static void ExecuteStoredProcedure(string SPName, ref ArrayList Parameters) { object result=null; ADODB.Connection Connection = new ADODB.Connection(); ADODB.Command Command = new ADODB.Command(); Command.ActiveConnection = Connection; Command.CommandText = SPName; Command.CommandType = CommandTypeEnum.adCmdStoredProc;
if (Parameters != null) { for (int i = 0; i < Parameters.Count; i++) { Command.Parameters.Append(Parameters[i]); } }
try { Connection.Open(ConnectionString, "", "", 0); Command.Execute(out object RecordAffected, ref object parameters, int options ) ;//the second parameter what mean? how set it? } catch (Exception ex) { throw ex; } finally { Connection.Close(); }
}
Thanks
View 2 Replies
View Related
Mar 23, 2008
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[usp_CustomerDetails]
(@Number varchar(30),
@Name varchar(30),
@City varchar(20),
@SSN varchar(20),
@CustomerID int)
AS
BEGIN
IF NOT EXISTS (SELECT * FROM CustomerDetails WHERE Name = @Name AND Number = @Number)
BEGIN
UPDATE CustomerDetails SET Number = @Number,Name = @Name,City=@City,SSN = @SSN where CustomerID = @CustomerID
END
ELSE
BEGIN
print 'CANNOT UPDATE'
END
END
This my storedproc.
My problem is when i select customerID = 1 to update and if the same row having name = @name and number =@number
Then the update should take place.
but if any other row other than CustomerID=1 having name=@name and number=@number
Then the update is should not take place.
but The above stored procedure is not working like that.
so please some one help me with this.
Thankyou
Ramya.
View 4 Replies
View Related
May 11, 2008
Hello ,
When I read about Stored Procedure , I read this Topic
"
First, after SQL Server parses and compiles a stored procedure, it caches
the execution plans in its procedure cache.
I want to know what does it mean about ProcedureCache ??????
another question is :
what is the situations when SqlServer doesnot resuse the StoredProcedure in the ProcedureCache and it must recomplie it again ???
thanks
View 4 Replies
View Related
Dec 21, 2005
In VB, I have this code...
lSQL = "SELECT * FROM OPENQUERY(liorder,'SELECT a.KF_ORDER_NO AS OrdNo, f.KU_NAME AS Customer, " & _
"a.KF_ORDER_POS AS Pos, a.KF_SCHEIB_NR AS Pane, a.KF_QTY AS Qty, d.BREITE*d.HOEHE/1000*a.KF_QTY AS SQM, " & _
"a.KF_QTY*d.SUM_NETTO AS Val, a.KF_FERT_QTY AS Done, d.BREITE*d.HOEHE/1000*a.KF_FERT_QTY AS DoneSQM, " & _
"a.KF_FERT_QTY*d.SUM_NETTO AS DoneVal FROM LIORDER.AUF_KOPF c, LIPROD.KAPA_AUS_FERT a, LIORDER.KUST_ADR f, " & _
"LIORDER.AUF_POS d WHERE (f.KU_VK_EK = 0) AND (a.KF_SCHR_NR = 12) AND (c.AUF_NR = a.KF_ORDER_NO) AND " & _
"(c.KUNR = f.KU_NR) AND (a.KF_ORDER_NO = d.AUF_NR) AND (a.KF_ORDER_POS = d.AUF_POS) AND (f.KU_NAME IS NOT NULL) " & _
"GROUP BY a.KF_ORDER_NO, f.KU_NAME, a.KF_ORDER_POS, a.KF_SCHEIB_NR, a.KF_QTY, d.BREITE, d.HOEHE, a.KF_FERT_QTY, d.SUM_NETTO')"
Set RS = New ADODB.Recordset
RS.Open lSQL, DB
Do While Not RS.EOF
Select Case RS!Pane
Case "0" Or "1"
glassSQL = "SELECT IDNR, GL_BEZ FROM OPENQUERY(liorder, 'SELECT a.IDNR, a.GL_BEZ FROM LIORDER.GLAS_DATEN a, LIORDER.AUF_POS b " & _
"WHERE a.IDNR = b.GLAS1 AND b.AUF_NR = " & RS!OrdNo & " AND b.AUF_POS = " & RS!Pos & "')"
Set glassRS = New ADODB.Recordset
glassRS.Open glassSQL, DB
SQL = "INSERT INTO PendingLamination (OrdNo, Customer, Pos, Code, GlassDesc, Qty, SQM, Val, Done, DoneSQM, DoneVal) " & _
"VALUES ('" & RS!OrdNo & "', '" & Apostrophe(RS!Customer) & "', '" & RS!Pos & "', '" & glassRS!IDNR & "', '" & glassRS!GL_BEZ & _
"', '" & RS!Qty & "', '" & RS!SQM & "', '" & RS!Val & "', '" & RS!Done & "', '" & RS!DoneSQM & "', '" & RS!DoneVal & "')"
DB.Execute SQL
Case "2"
glassSQL = "SELECT IDNR, GL_BEZ FROM OPENQUERY(liorder, 'SELECT a.IDNR, a.GL_BEZ FROM LIORDER.GLAS_DATEN a, LIORDER.AUF_POS b " & _
"WHERE a.IDNR = b.GLAS2 AND b.AUF_NR = " & RS!OrdNo & " AND b.AUF_POS = " & RS!Pos & "')"
Set glassRS = New ADODB.Recordset
glassRS.Open glassSQL, DB
SQL = "INSERT INTO PendingLamination (OrdNo, Customer, Pos, Code, GlassDesc, Qty, SQM, Val, Done, DoneSQM, DoneVal) " & _
"VALUES ('" & RS!OrdNo & "', '" & Apostrophe(RS!Customer) & "', '" & RS!Pos & "', '" & glassRS!IDNR & "', '" & glassRS!GL_BEZ & _
"', '" & RS!Qty & "', '" & RS!SQM & "', '" & RS!Val & "', '" & RS!Done & "', '" & RS!DoneSQM & "', '" & RS!DoneVal & "')"
DB.Execute SQL
Case "3"
glassSQL = "SELECT IDNR, GL_BEZ FROM OPENQUERY(liorder, 'SELECT a.IDNR, a.GL_BEZ FROM LIORDER.GLAS_DATEN a, LIORDER.AUF_POS b " & _
"WHERE a.IDNR = b.GLAS3 AND b.AUF_NR = " & RS!OrdNo & " AND b.AUF_POS = " & RS!Pos & "')"
Set glassRS = New ADODB.Recordset
glassRS.Open glassSQL, DB
SQL = "INSERT INTO PendingLamination (OrdNo, Customer, Pos, Code, GlassDesc, Qty, SQM, Val, Done, DoneSQM, DoneVal) " & _
"VALUES ('" & RS!OrdNo & "', '" & Apostrophe(RS!Customer) & "', '" & RS!Pos & "', '" & glassRS!IDNR & "', '" & glassRS!GL_BEZ & _
"', '" & RS!Qty & "', '" & RS!SQM & "', '" & RS!Val & "', '" & RS!Done & "', '" & RS!DoneSQM & "', '" & RS!DoneVal & "')"
DB.Execute SQL
End Select
ProgressBar1.Value = ProgressBar1.Value + 1
If ProgressBar1.Value = 800 Then
ProgressBar1.Value = 0
End If
RS.MoveNext
Loop
...and I'm planning to use the same flow of logic in the SQL Server Agent using T-SQL. Is there any way I can do it?
View 6 Replies
View Related
Feb 9, 2004
Hi experts,
I'm using MS SQL 2000. The closest i could get to having pl/sql within ms sql was tru its stored procedures feature.
I created a new stored procedure within the built-in Northwind database. I pasted the following inside:
-----------------------------------------------------------------
declare
vname Employees.firstname%TYPE;
begin
SELECT firstname INTO vname FROM Employees
WHERE firstname = 'Nancy';
dbms_output.put_line ('Name is ' || vname);
EXCEPTION
when NO_DATA_FOUND then
dbms_output.put_line ('no data found');
when TOO_MANY_RECORDS then
dbms_output.put_line ('too many records');
END;
-----------------------------------------------------------------
I checked the syntax and i get some error about the employees table.
Error 155: 'Employees' is not a recognized cursor option
Any idea?
Thanks..
View 1 Replies
View Related
Aug 25, 2005
Mike writes "I have a string, >!~[99DC# 4.12$2251.% 1.63& 3.58'2150.(-2050.>/~]
and I would like to extract parts of it and send it to a SQL database. here is the layout and data that should be in each column. The Data column will be filled in automatically, but once that happens I would like the other columns to get their data from the Data column.
Data = >!~[99DC# 4.12$2251.% 1.63& 3.58'2150.(-2050.>/~]
Display_Address = 99
CE = 4.12
TPK = 2251
Si= 1.63
C = 3.58
TPL = 2150
TPS = 2050
Can anyone tell me how to do this?"
View 1 Replies
View Related
Jun 7, 2007
/* hi people im doing a task and im not sure about some things. could u check if what im doing is good plz.*/
The task is:
Produce a list of all female employees who earn more than the average salary of the male employees in the company. Display employee number, first name and last name.
The table: emp
Attributes: EMPNO, FIRSTNAME, LASTNAME, SEX, SALARY
What i came up with:
selectempno, firstname, lastname
fromemp
whereavg(salary)>any(selectavg(salary)
fromemp
wheresex='M')
group by sex
havingsex = 'F'
View 3 Replies
View Related
Aug 7, 2007
Here is my code to connect, any help would be appreciated. I am a total noob at this.
############### Code
<?php
$host="localhost"; // Host name
$username=""; // Mysql username
$password=""; // Mysql password
$db_name="orderofb_test"; // Database name
$tbl_name="orderofb_test.members"; // Table name
// Connect to server and select databse.
mysql_connect("$host", "$username", "$password")or die("cannot connect");
mysql_select_db("$db_name")or die("cannot select DB");
// username and password sent from signup form
$myusername=$_POST['myusername'];
$mypassword=$_POST['mypassword'];
$sql="SELECT * FROM $tbl_name WHERE username='$myusername' and password='$mypassword'";
$result=mysql_query($sql);
// Mysql_num_row is counting table row
$count=mysql_num_rows($result);
// If result matched $myusername and $mypassword, table row must be 1 row
if($count==1){
// Register $myusername, $mypassword and redirect to file "login_success.php"
session_register("myusername");
session_register("mypassword");
header("location:login_success.php");
}
else {
echo "Wrong Username or Password";
}
?>
This is the error message I receive.
############### Code
Warning: mysql_connect() [function.mysql-connect]: Access denied for user 'nobody'@'localhost' (using password: NO) in /home/orderofb/public_html/php/checklogin.php on line 11
cannot connect
I changed all the permissons to 777 and I still receive this error.
$tbl_name="orderofb_test.members"; // Table name
Is this the correct table name? Here is a pic of my phpmyadmin
http://mnetcs.com/thumb/storage/b3583134.JPG
View 2 Replies
View Related
Mar 12, 2007
Hi everyone!I tried to set my SqlDataSource's SelectCommandType to be a stored procedure. However the SqlDataSource failed to cache it. But if I just copy paste my stored procedure's content to my SqlDataSource's SelectCommand property, the cache just works. Is "StoredProcedure" as the "SelectCommandType" is not supported when caching the data? Or am I missing something here? Please help.
View 1 Replies
View Related
Feb 9, 2008
I am trying to write a function for some source to make a call out to and fill a RadioButtonList. I am running into a few problems though that I need assistance on. (I am new to DataSets)
Here is the function to fill the RBL:
1 Private Function GetDataSet(ByVal QuestionID As Integer, ByVal QuestionType As Integer, ByVal LocaleID As Integer, ByVal GroupingNum As Integer) As DataSet
2 Dim cnn As New SqlConnection(ConfigurationManager.ConnectionStrings("ConnectionString").ConnectionString)
3 Dim cmd As New SqlCommand
4 cmd.CommandText = "usp_responses_sel"
5 cmd.CommandType = Data.CommandType.StoredProcedure
6
7 ' Fill usp_ with Parameters
8 cmd.Parameters.AddWithValue("QuestionID", QuestionID)
9 cmd.Parameters.AddWithValue("LocaleID", LocaleID)
10 cmd.Parameters.AddWithValue("GroupingNum", GroupingNum)
11
12 Dim da As New SqlDataAdapter
13 da.SelectCommand = cmd
14 Dim ds As New DataSet
15 da.Fill(ds, "response")
16 Return ds
17 End Function
So my issue is with line 15 [da.Fill(ds, "response")]. I pulled this function from somewhere else and am trying to tailor it to my needs. However, I do not understand what I need to do with this line and it keeps bombing out. I thought this references the DB Table but in my case, the SP has several tables joined together. Is this how I reference it from the calling source code? Please assist.
Also, I am having problems understanding the binding process from the calling source. Here is my code that calls the function:1 Dim ds As DataSet = GetDataSet(CType(e.Item.DataItem("question_id").ToString, Integer), QuestionTypeID.Value, intLocale, 2)
2 rblResponses2.DataSource = ds
3 rblResponses2.DataBind()
What do I need to do with it from here and how can I work with it after it's bound?
Thanks
View 5 Replies
View Related
Feb 26, 2008
I'm not getting any error, but I'm not seeing any updates to my database. Here is the code below:
The click event:
protected void btnModify_Click(object sender, EventArgs e) { UpdateRecord(Convert.ToInt32(ViewState["HostNameID"])); }
The method:
private void UpdateRecord(int HostNameID) { try { // TODO // - Call stored procedure to update database table HostName // The storedprocedure is modifyHost using (SqlConnection cn = new SqlConnection(ConfigurationManager.AppSettings["ConnectionString"])) { SqlCommand cmd = new SqlCommand("modifyHost", cn); cmd.CommandType = CommandType.StoredProcedure; cmd.Parameters.AddWithValue("@pDesc", txtDeviceDescription.Text.Trim()); cmd.Parameters.AddWithValue("@pSerial", txtSerial.Text.Trim()); cmd.Parameters.AddWithValue("@pSmc", ddlSMC.SelectedItem.Text); cmd.Parameters.AddWithValue("@pID", ViewState["HostNameID"]); cmd.Parameters.AddWithValue("@pMilliSecs", "lastUpdated"); cn.Open(); cmd.ExecuteNonQuery(); } } catch (SqlException err) { lblmsgError.Visible = true; lblmsgError.Text += "<br><b> btn_Delete_SQL_Error </b>" + err.Message; } }
The Storedprocedure:
ALTER PROCEDURE dbo.modifyHost(@pDesc nvarchar(50),@pSerial nvarchar(50),@pSmc nvarchar(50),@pID bigint,@pMilliSecs nvarchar(50))AS UPDATE dbo.HostNameSET Description = @pDesc, DeviceSerialNum = @pSerial, SMCContact = @pSmc, lastUpdated = @pMilliSecsWHERE (HostNameID = @pID)
Is there anything I missed???
View 5 Replies
View Related
