How To Pass variable of type UNIQUEIDENTIFIER to stored procedure using sqlhelper.cs/vb

Question

0.00/5 (No votes)

See more:

SQL-Server-2005

, +

How to pass variable of type UNIQUEIDENTIFIER to stored procedure
using sqlhelper.cs/vb

i am getting this error
The type initializer for 'DAL.SqlHelper' threw an exception

.aspx.cs code

C#

ObjSave.ID = LblId.Text;
            ObjSave.ClientName = TxtClientName.Text;
            ObjSave.Email = TxtEmail.Text;
            ObjSave.Contact = TxtContact.Text;
            ObjSave.Comment = TxtComment.Text;
            ObjSave.CreatedBy = GetUserId();
            ObjSave.AddUpdateDelete();

and this is my BAL.cs code

C#

System.Data.SqlClient.SqlParameter[] _SqlParam = null;
            try
            {
                _SqlParam = new System.Data.SqlClient.SqlParameter[6];
                _SqlParam[0] = new System.Data.SqlClient.SqlParameter("@ID", _ID);
                _SqlParam[1] = new System.Data.SqlClient.SqlParameter("@ClientName", _ClientName);
                _SqlParam[2] = new System.Data.SqlClient.SqlParameter("@Email", _Email);
                _SqlParam[3] = new System.Data.SqlClient.SqlParameter("@Contact", _Contact);
                _SqlParam[4] = new System.Data.SqlClient.SqlParameter("@Comment", _Comment);
                _SqlParam[5] = new System.Data.SqlClient.SqlParameter("@CreatedBy", _CreatedBy);
                SqlHelper.ExecuteDataset("AddUpdateLeads", _SqlParam);
                return true;
            }
            finally
            {
                _SqlParam = null;
            }

i think micro.application block is not much useful den
our traditional method for CRUD functionality

Posted 23-Oct-13 1:29am

Smart_Mohit

Updated 23-Oct-13 1:36am

v2

Add a Solution

2 solutions

Solution 1

Your _ID should be a Guid.NewGuid(), which is equivalent to UNIQUEIDENTIFIER in SQL

Posted 23-Oct-13 1:40am

Shameer V V

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

S. M. Ahasan Habib · Accepted Answer · 2013-10-23T02:07:00

Solution 2

Application code:

C#

Guid _ID = Guid.NewGuid();
 _SqlParam[0] = new System.Data.SqlClient.SqlParameter("@ID", _ID);

database stored procedure:

SQL

create proc AddUpdateLeads
(
  @Id uniqueidentified
)
as
begin
--your sql
end

if your sp like

SQL

create proc AddUpdateLeads
(
  @Id varchar(50)
)
as
begin
--your sql
end;

then your application code like

C#

Guid _ID = Guid.NewGuid();
 _SqlParam[0] = new System.Data.SqlClient.SqlParameter("@ID", _ID.ToString());

Posted 23-Oct-13 2:07am

S. M. Ahasan Habib

Comments

Smart_Mohit 23-Oct-13 8:19am

thank you sir for ur valuable reply
actly i am saving the guid value in createdby column its datatype is
uniqueidentifier

private Guid GetUserId()//membership database
{
MembershipUser Usr = Membership.GetUser(User.Identity.Name);
Guid id = (Guid)Usr.ProviderUserKey;
return id;
}
the error is thrown by sqlhelper.cs
if i am passing all this variable to procedure in a
tradional way then its work,but my requirement is i have to pass it
through sqlparam via sqlhelper
i google also but their is no solution Frown | :( Frown | :( Frown | :(
if this error is solved my rest of the module will be developed very rapidly
this problem is occured after i added the membership database to the
application

S. M. Ahasan Habib 23-Oct-13 8:50am

i tried with this and it is working for me!
my database part:
--my table
create table MyTestTable(Id uniqueidentifier, Name varchar(50));
go
--my sp
create proc testsp
(
@id uniqueidentifier
,@Name varchar(50)
)
as
begin
insert into MyTestTable(Id, Name) values(@Id, @Name);
end
go

and my application code

string spName = "testsp";
var _ID = Guid.NewGuid();
SqlParameter[] spParams = new SqlParameter[2];
spParams[0] = new SqlParameter("Id", _ID);
spParams[1] = new SqlParameter("Name", "A");
var connString = ConfigurationManager.ConnectionStrings["DbConnection"].ConnectionString;

using (var conn = new SqlConnection(connString))
{
conn.Open();
using (SqlCommand cmd = conn.CreateCommand())
{
cmd.CommandType = CommandType.StoredProcedure;
cmd.CommandText = spName;
cmd.Parameters.AddRange(spParams);
cmd.ExecuteNonQuery();
}
}
I used raw ado.net and it is working for me. I do not know about your SqlHelper class and its method ExecuteDataset. If anyway you can not find any solution you use raw ado.net code in that area only just like i show it here.