Testing StoredProc With GUID Parameter
Apr 11, 2002
I am working revising a number of stored procs on a system which has suffered some schema changes.
Sometimes I can test my SP code passing in a guid without a problem. Example below:
Test Command:
EXEC usp_Unit_INSERT
'{74A1BABA-0B76-4436-B6AA-01716B686044}', --unitguid
'36', --91', --UnitNumber (varchar)
10, -- xxHospitalNumber
'testUnknown' --UnitName
Above works fine.
I am testing another similar stored proc and am getting this error:
Server: Msg 8152, Level 16, State 9, Procedure usp_Patient_Info_INSERT, Line 24
String or binary data would be truncated.
The statement has been terminated.
(Line 24 performs an insert to a GUID)
Pertient code portions below.
Can anybody shed any light. I am essentially doing nearly identical things to another Stored Proc which works just fine.
Code below fails with above error, but is virtually identical in how it treats all GUID fields to another which does work fine.
-------------------------------------------------
CREATE PROCEDURE [usp_Patient_Info_INSERT]
@PatientGUID varchar(40),--uniqueidentifier,
@PersonGUIDvarchar(40),--uniqueidentifier ,
@CaseNumberdecimal(10,0),
<< and so forth >>
AS
IF @PatientGUID Is Null
SET @PatientGUID =cast( (newid()) as varchar(40))
INSERT INTO [Patient_Info] (
PatientGUID,
PersonGUID,
CaseNumber,
<< and so forth >>
Values (
cast( @PatientGUID as uniqueidentifier),
cast( @PersonGUID as uniqueidentifier),
@CaseNumber,
<< and so forth >>
View 1 Replies
ADVERTISEMENT
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
Jul 5, 2007
I have a web application. In some instances, I have a need to send guid parameter as null while making a sql query to SQL 2005. My question is as how to handle this null guid on .net and sql side.
Thanks! in advance.
View 2 Replies
View Related
Jan 31, 2007
I am working with SRS 2005 SP1 which no longer has the "ALL" option available on parameters. I am trying to create an "ALL" entry in a picklist so it can be used in a where clause for a dataset. I have a dataset with a union statement that creates a list of CRM usersids and names and an entry with a dummy guid with the name "All". Parameter is defined as a string type, with a dataset providing a list of users (label field) and their corresponding GUID value (value field), along with the an "All" entry.
select systemuserid, fullname
from FilteredSystemUser
Union
Select '00000000-0000-0000-0000-000000000000' as systemuserid, ' All' as fullname
order by fullname
The issue I am running into is implementing logic in another dataset referencing my parameter.
All is fine in the where clause if it is structured "where ownerid in (@Users)" but if I try to add logic to check for the "All" option "where (ownerid in (@Users) or @Users = '00000000-0000-0000-0000-000000000000') it errors out.
How do you impement "All" when you're dealing with a GUID type field? Thanks.
View 3 Replies
View Related
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
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
Jul 13, 2007
Please give me advise ครับ
View 1 Replies
View Related
Feb 4, 2007
I get a execption when i run my code i dont know how to debug sql statements so ya could any one give me adive heres the code public static int CreateMember(string username, string aspApplicationName)
{
int returnvalue = 0;
DateTime dateCreated = DateTime.Now;
// All users are added to users role upon registration.
Roles.AddUserToRole(username, "Users");
String connectionString = ConfigurationManager.ConnectionStrings["SqlConn"].ConnectionString;
SqlConnection conn = new SqlConnection(connectionString);
SqlCommand command = null;
try
{
conn.Open();
command = new SqlCommand("InsertMember", conn);
command.CommandType = CommandType.StoredProcedure;
command.Parameters.Add(new SqlParameter("@AspNetUsername", username));
command.Parameters.Add(new SqlParameter("@AspNetApplicationName", aspApplicationName));
command.Parameters.Add(new SqlParameter("@DateCreated", dateCreated));
SqlParameter sqlParam = command.Parameters.Add("@Id", SqlDbType.Int);
sqlParam.Direction = ParameterDirection.ReturnValue;
command.ExecuteNonQuery();
returnvalue = (int)command.Parameters["@id"].Value;
conn.Close();
}
catch (Exception ex)
{
}
finally
{
if (command != null)
command.Dispose();
if (conn != null)
conn.Dispose();
}
return returnvalue;
} i get a exception at command.ExecuteNonQuery(); and if i dont do int returnvalue = 0; it says i cant use it cause it hasnt be initialized or something like that ALTER PROCEDURE [dbo].[InsertMember] @AspNetUsername nvarchar(256), @AspNetApplicationName nvarchar(256), @DateCreated smalldatetime = getdateASDECLARE @Id int;SET NOCOUNT ON;INSERTINTO [Members] ([AspNetUsername], [AspNetApplicationName],[DateCreated]) VALUES (@AspNetUsername, @AspNetApplicationName,@DateCreated);SET @Id = @@IDENTITYSELECT @Id AS [Id] theres my stored proc any ideas?
View 5 Replies
View Related
Apr 21, 2005
Hallo
I have a normal "Select * from Table" SP that have OUTPUT parameters as well.
Is it possible to obtain the result into a XML format and ALSO obtain the OUTPUT parameters in .Net1.1
Thank you
View 6 Replies
View Related
Jul 9, 2001
Hi,
I'm using SQL Server 2000 as our back end. I'm finding it bit difficult to write StoredProcs manually to be called from my front end. Is there any good Stored Proc generator tool available?
Thanks,
Harish
View 1 Replies
View Related
Mar 8, 2004
Hi all,
I have to execute stored procedures containing
xp_cmdshell and certain system storedprocedures in msdb and master
with a user who is not SA.
(i.e iam able to execute stored procedures when i log as sa,
but any other user cannot run them)
Pls tell how to do this, it is quite urgent.
View 1 Replies
View Related
Mar 24, 2008
Hi someone please help me.
i have a serach page which have 4 textboxes.
passing this textboxes as parameters to storedproc iam searching the value.
filling atleast one textbox should fetch the value.
i have stored proc for searching it using normal column values but i want it do using wildcard search also.
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[search1]
(@val1 varchar(225),
@val2 varchar(50),
@val3 varchar(50),
@val4 varchar(50))
AS
BEGIN
DECLARE @MyTable table (CNo varchar(255))
INSERT @MyTable
Select CNo From customer where
((@val1 IS NULL) or (CNo = @val1)) AND
((@val2 IS NULL) or(LastName = @val2)) AND
((@val3 IS NULL) or(FirstName = @val3)) AND
((@val4 IS NULL) or(PhoneNumber = @val4))
--Now do your two selects
SELECT c.*
FROM customer c
INNER JOIN @MyTable T ON c.CNo = T.CNo
Select r.*
From refunds r
INNER JOIN @MyTable t ON r.CNo = t.CNo
END
I WANT THE SEARCH TO BE DONE FOR WILD CARD CHARACTERS ALSO.
if the user enters lastname s*
using same storedproc can i insert wildcard search.
how can i do that please some one help me.
thanks
renu
View 1 Replies
View Related
Oct 2, 2006
I am working on the login portion of my app and am using my own setup for the moment so that I can learn more about how things work. I have 1 user setup in the db and am using a stored procedure to do the checking for me, here is the stored procedure code:ALTER PROCEDURE dbo.MemberLogin(@MemberName nchar(20),@MemberPassword nchar(15),@BoolLogin bit OUTPUT)ASselect MemberPassword from members where membername = @MemberName and memberpassword = @MemberPassword if @@Rowcount = 0beginselect BoolLogin = 0returnendselect BoolLogin=1/* SET NOCOUNT ON */ RETURNWhen I run my app, I continue to get login failed but no error messages. Can anybody help? Here is my vb code:Dim MemberName As StringDim MemberPassword As StringDim BoolLogin As BooleanDim DBConnection As New Data.SqlClient.SqlConnection(MyCONNECTIONSTRING)Dim SelectMembers As New Data.SqlClient.SqlCommand("MemberLogin", DBConnection)SelectMembers.CommandType = Data.CommandType.StoredProcedureMemberName = txtLogin.TextMemberPassword = txtPassword.TextDim SelectMembersParameter As Data.SqlClient.SqlParameter = SelectMembers.CreateParameter'NameSelectMembersParameter.ParameterName = "@MemberName"SelectMembersParameter.Value = MemberNameSelectMembers.Parameters.Add(SelectMembersParameter)'PasswordDim SelectPasswordParameter As Data.SqlClient.SqlParameter = SelectMembers.CreateParameterSelectPasswordParameter.ParameterName = "@MemberPassword"SelectPasswordParameter.Value = MemberPasswordSelectMembers.Parameters.Add(SelectPasswordParameter)Dim SelectReturnParameter As Data.SqlClient.SqlParameter = SelectMembers.CreateParameterSelectReturnParameter.ParameterName = "@BoolLogin"SelectReturnParameter.Value = BoolLoginSelectReturnParameter.Direction = Data.ParameterDirection.OutputSelectMembers.Parameters.Add(SelectReturnParameter)If BoolLogin = False ThenMsgBox("Login Failed")ElseIf BoolLogin = True ThenMsgBox("Login Successful")End IfEnd SubThank you!!!
View 3 Replies
View Related
Dec 23, 2005
I have three tables that are important here, a 'Plant' table a 'Spindle' table and a 'PlantSpindle' table. The 'PlantSpindle' is comprised of a PlantID and a SpindleID acting as the Primary Key for the table with no other fields.
I have an aspx page that captures the appropriate data to create an entry in the Spindle table. Depending on the user, I will know which plantID they are associated with via a querystring. In my storedproc I insert the data from the webform into the Spindle table but get stuck when I try to also insert the record into the PlantSpindle table with the PlantID I have retrieved via the querystring and the SpindleID of the spindle record the user just created. Basically, I am having trouble retrieving that SpindleID.
Here is what I have in my storedProc (truncated for brevity).
CREATE PROCEDURE [dbo].[InsertSpindle]
@plantID int,
@spindleID int,
@plantHWG varchar(50),
@spindleNumber varchar(50),
@spindleDateInstalled varchar(50),
@spindleDateRemoved varchar(50),
@spindleDurationMonths float(8),
@spindleBearingDesignNumber int,
@spindleArbor varchar(50),
@spindleFrontSealDesign varchar(50),
@spindleFrontBearing varchar(50),
@spindleRearBearing varchar(50),
@spindleRearSealDesign varchar(50),
@spindleNotes varchar(160)
AS
SET NOCOUNT ON
INSERT INTO Spindle
(plantHWG, spindleNumber, spindleDateInstalled, spindleDateRemoved, spindleDurationMonths,
spindleBearingDesignNumber, spindleArbor, spindleFrontSealDesign, spindleFrontBearing,
spindleRearBearing, spindleRearSealDesign, spindleNotes)
VALUES
(@plantHWG, @spindleNumber, @spindleDateInstalled, @spindleDateRemoved, @spindleDurationMonths,
@spindleBearingDesignNumber, @spindleArbor, @spindleFrontSealDesign, @spindleFrontBearing,
@spindleRearBearing, @spindleRearSealDesign, @spindleNotes)
SET @spindleID = (SELECT @@Identity
FROM Spindle)
INSERT INTO PlantSpindle
(plantID, SpindleID)
VALUES
(@plantID, @SpindleID)
I have guessed at a few different solutions but still come up with Procedure 'InsertSpindle' expects parameter '@spindleID', which was not supplied when I execute the procedure.
Any help would be appreciated! thanks!
View 8 Replies
View Related
Jan 26, 2006
When I attempt to update using a stored procedure I get the error 'Incorrect syntax near sp_upd_Track_1'. The stored procedure looks like the following when modified in SQLServer:
ALTER PROCEDURE [dbo].[sp_upd_CDTrack_1]
(@CDTrackName nvarchar(50),
@CDArtistKey smallint,
@CDTitleKey smallint,
@CDTrackKey smallint)
AS
BEGIN
SET NOCOUNT ON;
UPDATE [Demo1].[dbo].[CDTrack]
SET [CDTrack].[CDTrackName] = @CDTrackName
WHERE [CDTrack].[CDArtistKey] = @CDArtistKey
AND [CDTrack].[CDTitleKey] = @CDTitleKey
AND [CDTrack].[CDTrackKey] = @CDTrackKey
END
But when I use the following SQL coded in the gridview updatecommand it works:
"UPDATE [Demo1].[dbo].[CDTrack]
SET [CDTrack].[CDTrackName] = @CDTrackName
WHERE [CDTrack].[CDArtistKey] = @CDArtistKey
AND [CDTrack].[CDTitleKey] = @CDTitleKey
AND [CDTrack].[CDTrackKey] = @CDTrackKey"
Whats the difference? The storedproc executes ok in sql server and I guess that as the SQL version works all of my databinds are correct. Any ideas, thanks, James.
View 2 Replies
View Related
Mar 2, 2000
Hi!
The problem that I'm dealing with is that I can't get recordset from SP, where I first create a temporary table, then fill this table and return recordset from this temporary table. My StoredProcedure looks like:
CREATE PROCEDURE MySP
AS
CREATE TABLE #TABLE_TEMP ([BLA] [char] (50) NOT NULL)
INSERT INTO #TABLE_TEMP SELECT bla FROM ……
SELECT * FROM #TABLE_TEMP
When I call this SP from my ASP page, the recordset is CLOSED (!!!!) after I open it using the below statements:
Set Conn = Server.CreateObject("ADODB.Connection")
Baza.CursorLocation = 3
Baza.Open "Provider=SQLOLEDB.1;Persist Security Info=False;User ID=sa;Initial Catalog=IGOR;Data Source=POP"
Set rs = Server.CreateObject("ADODB.Recordset")
rs.Open "MySP", Conn, , ,adCmdStoredProc
if rs.State = adStateClosed then
response.Write "RecordSet is closed !!!! " ‘I ALLWAY GET THIS !!!!
else
if not(rs.EOF) then
rs.MoveFirst
while not(rs.EOF)
Response.Write rs ("BLA") & " 1 <br>"
rs.MoveNext
wend
end if
end if
Conn.Close
Do you have any idea how to keep this recordset from closing?
Thanks Igor
View 2 Replies
View Related
Feb 5, 2003
Hi,
Is it possible Oracle Stored Proc or Jobs able to schedule in sql job?.
Thanks,
Ravi
View 3 Replies
View Related
Jun 8, 2004
Hi,
I want to e-mail a user when a Stored Proc fails, what is the best way to do this? I was going to create a DTS package or is this too complicated?
Also, the Stored Proc inserts data from one table to another, I would like to use Transactions so that if this fails it rolls back to where it was, I'm not sure of the best way to go about this. Could anyone possibly point me in the right direction? Here's a copy of some of the stored procedure to give an idea of what I am doing:
-- insert data into proper tables with extract date added
INSERT INTO tbl_Surgery
SELECT
SurgeryKey,
GETDATE(),
ClinicianCode,
StartTime,
SessionGroup,
[Description],
SurgeryName,
Deleted,
PremisesKey,
@practiceCode--SUBSTRING(SurgeryKey,PATINDEX('%.%',SurgeryKey)+1, 5)
FROM tbl_SurgeryIn
INSERT INTO tbl_SurgerySlot
SELECT
SurgerySlotKey,
GETDATE(),
SurgeryKey,
Length,
Deleted,
StartTime,
RestrictionDays,
Label,
IsRestricted,
@practiceCode
FROM tbl_SurgerySlotIn
INSERT INTO tbl_Appointment
SELECT
AppointmentKey,
GETDATE(),
SurgerySlotKey,
PatientKey,
Cancelled,
Continuation,
Deleted,
Reason,
DateMade
FROM tbl_AppointmentIn
-- empty input tables
DELETE FROM tbl_SurgeryIn
DELETE FROM tbl_SurgerySlotIn
DELETE FROM tbl_AppointmentIn
Any help would me very much appreciated,
Thanks
View 3 Replies
View Related
Mar 31, 2006
Hi everyone,
I have a storedproc. This proc send back a value. how can i call this storedproc in select from block. Or what is your advise for other ways....
Select *
, (Exec MyStoredProc MyParam) as Field1
From Table1
View 1 Replies
View Related
Mar 19, 2008
Hi
I have a search page which contains 4 fields.Giving input to anyone of the field should display the result in
Parent Gridview.Parent Gridview has button in it .when i click on the button child Gridview should display related
refund details of customer in parent Gridview.
let us think i have two tables like Customer and refunddetails.
Parent Gridview should display Customer details,Child should display corresponding customers refund details.
I need two storedprocs for binding to both Gridviews.
i have first stored proc for Gridview1
set ANSI_NULLS ON
set QUOTED_IDENTIFIER ON
go
ALTER PROCEDURE [dbo].[MyProc]
(@val1 varchar(255),
@val2 varchar(50),
@val3 varchar(50),
@val4 varchar(50))
--@out smallint OUTPUT
AS
select * from customer where
((@val1 IS NULL) or (name = @val1)) AND
((@val2 IS NULL) or(ssn = @val2)) AND
((@val3 IS NULL) or(accountnumber = @val3)) AND
((@val4 IS NULL) or(phonenumber = @val4))
now i need to capture the @val1 from storedproc1 and using that value retrieve the remaining values in refund table.
name is common in both the tables.
i need this because user can search the value using ssn or accountnumber or phonenumber or name.it is not required that user serches using name.Name textbox can be null.
so please someone help me.
View 3 Replies
View Related
Mar 23, 2008
Hi can anyone tell meHow to bind messages in storedproc to lable control in the front end.I have a stored proc which updates the data table.in certain condition update should not take place and a message should be generated that update did not take place.Can anyone tell me how that message can be shown in front endmy taught was to bind it using lable control. But how the messages can come from storedproc to front endcan we do it using dataset binding.Is there any other way please lemme know immediately .Thankyousiri
View 4 Replies
View Related
Mar 22, 2008
Hi
I have a question i hope someone here will solve my problem.
I need a storedproc for checking the duplicates before updating the data.
here i need to pass four parameters to storedprocedure which must be updated with existing row.
but before updating the values. Storedproc should check for 2 colum values in all rows.
if same combination of colum values is present in any other row then the present rows which we tring to update should not be
updated. Can anyone help me with this.
Thankyou verymuch.
View 8 Replies
View Related
Mar 4, 2008
How do I update the OrderGUID column in Table B with Values from OrderGUID column in Table A. I have already populated the OrderGUID column in Table A using NEWSEQUENTIALID(). Now I need to populate the OrderGUID column in Table B with Matching GUID values from the OrderGUID Column in Table A.
Does any one have a script to accomplish this task. thanks
View 4 Replies
View Related
Sep 17, 2007
Hi guys,
I've been doing some LOCAL reports on my current application until recently there's has been a case that I really need to do SERVER reports.
Usually when I design my local reports, I create a XSD file, so I usually have one dataset with multiple tables in it. I just pass the dataset to report with a single procedure call that returns multiple result sets or data table.
From what I understood server reports are binded to database objects only, like stored procedures. Now I used the same stored procedure that I used in my local report to my server report. But the thing is only the first result set in the stored procedure is recognized. Are there anyway that I can bind the server report to a single stored procedure that return multiple result sets?
Thanks.
View 2 Replies
View Related
May 21, 2008
I'm using SQL RS 2005 and have a report where we want the report to run a different stored procedure depending on if a condition is true. I've set my 'command type' to stored proc and can type in the name of a stored procedure. If I type in just one stored procedure's name, it runs fine. But if I try to use a =IIF(check condition, if true run stored proc 1, if false run storedproc 2) then the exclamation (run) button is greyed out. Does anyone know how I can do this? Thanks.
View 3 Replies
View Related
Dec 10, 2007
Hi,
id beg for a hint if our idea of a general dynamic CATCH handler for SPs is possible somehow. We search for a way to dynamically figure out which input parameters where set to which value to be used in a catch block within a SP, so that in an error case we could buld a logging statement that nicely creates a sql statement that executes the SP in the same way it was called in the error case. Problem is that we currently cant do that dynamically.
What we currently do is that after a SP is finished, a piece of C# code scans the SP and adds a general TRY/CATCH bloack around it. This script scans the currently defined input parameters of the SP and generates the logging statement accordingly. This works fine, but the problem is that if the SP is altered the general TRY/CATCH block has to be rebuildt as well, which could lead to inconstencies if not done carefully all the time. As well, if anyone modifies an input param somewhere in the SP we wouldnt get the original value, so to get it right we would have to scan the code and if a input param gets altered within the SP we would have to save it at the very beginning.
So the nicer solution would be if we could sniff the input param values dynamically on run time somehow, but i havent found a hint to do the trick.....
Any tipps would be appreciated...
cheers,
Stefan
View 1 Replies
View Related
Feb 1, 2001
we are currently using id's as primary key and replication is not part of our project.
will this be a problem if we decide to do replication? will microsoft generate an identifier then.
what is the advantages of using GUID now or doing it latter?
View 1 Replies
View Related
May 26, 2006
Hello,
I'm working on a smart client app that has an offline sql express store and needs to work with several types of central databases (support for multiple products - ms sql, DB2 etc)
While trying to put together some offline functionality that needs the user to create records on the offline sql express data store, we've run into the need of being able to uniquely identify records so replicating the data in the offline store back into the primary database should not be a problem.
The data created offline spans many tables and involves several tables with relation ships - FK etc...Clearly not a simple case of store and forward.
We dont want to get into the mess of performing key replacement during a synch job with the server. Thats way too much trouble.
GUID seems like a good choice, but as always we have several stake holders having different opinions. And with databases other than MS SQL we will have to store them as strings.
To cut to the chase - can we not hash a GUID to get an integer while retaining atleast the same likelyhood of producing unique ids ? [no drop]
Thanks,
Aviansh
View 13 Replies
View Related
Apr 13, 2007
I am using a SqlDatasource and need to set a SelectParamter to the ProviderUserKey (The GUID of the user when Profiles are enabled)
Can anyone tell me whether it is possible and How?
I am currently using the session state to store it in and then using the session=... to get the value into the parameter.
Is there a direct way of passing this value into a SelectParameter when using a SqlDataSource?
Thanks in advance.
View 3 Replies
View Related
Sep 25, 2007
Hi Everyone,
I'm trying to create a SQL Delete statement using a string builder and the WHERE clause uses a Guid. Here is the code:stb.Append("DELETE FROM UserRights WHERE UserIDPtr = ");
stb.Append(TargetUserID);The resulting string is: "DELETE FROM UserRights WHERE UserIDPtr = e01549fb-edf5-4668-de8b-b13dd5661a6e"
When I try to do an ExecuteNonQuery() using the string as the CommandText, I get an error.
Invalid column name 'e01549fb',Invalid column name 'edf5',Invalid column name 'de8b',Invalid column name 'b13dd5661a6e'
It is also strange that '4668' did not show up as an invalid column name, but I don't think that is relavent to this issue.
Can someone show me (or point me to an article) about using Guid's in a text string as a SQL command? Thanks in advance!
View 2 Replies
View Related
May 5, 2008
My database is using the membership store for all the user information. I added a tabel "Skills" with 3 fields "SkillID (GUID)" "SeekerID (GUID, This is the UserId)" and "SkillName ( Varchar(MAX))"
on the page i have a ListView setup to display the SkillName fields based on the SeekerID.
The original code was Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
' Get a reference to the currently logged on user Dim CurrentUser As MembershipUser = Membership.GetUser
' Determine the currently logged on user's UserId value
Dim SeekerID As Guid = CType(CurrentUser.ProviderUserKey, Object)
End Subthat kept returning string to GUID conversion errors, so i had to change Dim SeekerID As Guid = CType(CurrentUser.ProviderUserKey, Object) to Dim SeekerID As Object = CType(CurrentUser.ProviderUserKey.ToString(), Object) that appears to be working now. On the same page however, I want to insert records into the table, I tried 2 options both of which have a different problem.
Option 1: Use a textbox (ID = NewSkill) and a button with the following code on it:
Protected Sub SetNewSkill_Click(ByVal sender As Object, ByVal e As System.EventArgs) Dim CurrentUser As MembershipUser = Membership.GetUser
Dim SeekerGUID As Object = CType(CurrentUser.ProviderUserKey, Object)
Dim NewSkill As TextBox = CType(FindControl("NewSkill"), TextBox) Dim connectionString As String = ConfigurationManager.ConnectionStrings("QJSdatabase").ConnectionString()
Dim insertSql As String = "INSERT INTO Skills(SeekerID, SkillName)VALUES(@SeekerGUID, @NewSkill)" Using myConnection As New SqlConnection(connectionString)
myConnection.Open() Dim myCommand As New SqlCommand(insertSql, myConnection)
myCommand.Parameters.AddWithValue("@SeekerGUID", SeekerGUID) myCommand.Parameters.AddWithValue("@NewSkill", NewSkill.Text.Trim())
myCommand.ExecuteNonQuery()
myConnection.Close()
End Using
End Sub
Problem: myCommand.Parameters.AddWithValue("@NewSkill", NewSkill.Text.Trim()) gets outlined and returns the error: Object reference not set to an instance of an object
Option 2: Use a DetailsView linked to a SQLDataSource on the page
<asp:DetailsView ID="DetailsView1" runat="server" Height="50px" Width="210px"
AutoGenerateRows="False" DataSourceID="SqlDataSource2" DefaultMode="Insert"> <Fields>
<asp:BoundField DataField="SkillName" HeaderText="Add Skill:" SortExpression="SkillName" />
<asp:CommandField ShowInsertButton="True" />
<asp:TemplateField InsertVisible="False">
<EditItemTemplate>
<asp:TextBox ID="TextBox1" runat="server"></asp:TextBox>
</EditItemTemplate>
<ItemTemplate>
<asp:Label ID="Label1" runat="server"></asp:Label>
</ItemTemplate>
</asp:TemplateField>
</Fields>
</asp:DetailsView>
<asp:SqlDataSource ID="SqlDataSource2" runat="server" ConnectionString="<%$ ConnectionStrings:database %>"
DeleteCommand="DELETE FROM [Skills] WHERE [SkillID] = @SkillID" InsertCommand="INSERT INTO [Skills] ([SeekerID], [SkillName]) VALUES (@SeekerGUID, @SkillName)"
SelectCommand="SELECT SkillName FROM [Skills]"
UpdateCommand="UPDATE [Skills] SET [SkillName] = @SkillName WHERE [SkillID] = @SkillID">
<DeleteParameters>
<asp:Parameter Name="SkillID" Type="Object" />
</DeleteParameters>
<InsertParameters>
<asp:Parameter Name="SeekerGUID" Type="Object" />
<asp:Parameter Name="SkillName" Type="String" />
</InsertParameters>
<UpdateParameters>
<asp:Parameter Name="SkillName" Type="String" />
<asp:Parameter Name="SkillID" Type="Object" />
</UpdateParameters>
</asp:SqlDataSource>
http://junk.icore-studios.com/junk/Codeissues/postresumeerror.html
The @SeekerGUID is being generated by the same pageload code as the first chunk i gave (I have 2 variables SeekerID is the GUID converted to string to work with the ListView filter and SeekerGUID is the GUID)
Ultimatly getting either option to work would be fine. Though I think the second would be preferable because I think it'd be easier to replicate later on.
Thanks in advance for your time and any help
View 3 Replies
View Related
Apr 17, 2004
Hi,
I am having a hard time updating a database row using a UNIQUEIDENTIFIER. I retrieve the row into a datagrid and then use the GUID as a parameter to a stored procedure, but it doesn't update. If I run the query in SQL Analyser ... it works. Any ideas ? Here's my stored proc ... I tried passing a varchar and doing the conversion in the SP ... no go !! I am using MApplicationBlockD.
CREATE PROCEDURE spScanUpdate
@id varchar (100),
@name varchar (75)
AS
DECLARE @GUID_ID as uniqueidentifier
SELECT@GUID_ID = CAST ( @id as uniqueidentifier )
UPDATE tScan
SET name = @name
WHEREid = @GUID_ID
GO
View 5 Replies
View Related
