Using SqlDataAdapter

[W Akthar]

I am trying to use SqlDataAdapter to execute a Stored
Procedure.

The Stored Procedure is as follows

CREATE Procedure SP_GetAddresses FromContactID
@ID int
AS
SELECT
b.StreetAddress 1,
b.StreetAddress 2,
b.StreetAddress 3,
b.Town,
b.PostCode,
b.County,
b.Status
FROM
Names a,
Addresses b,
Add_Ref c
WHERE
a.ID = c.Name_ID
AND
b.ID = c.Address_ID
AND
a.ID = @ID
RETURN
GO


The code to execute his stored procedure is as follows

DataSet ds = new DataSet("Addres sesFromContact" );
// Create a SqlDataAdapter.
SqlDataAdapter myAdapter = new SqlDataAdapter( );
SqlCommand sqlNamesCmd = new SqlCommand
("SP_GetAddress esFromContactID ", _sqlConn);
// Add Parameters to SPROC
SqlParameter prmID = new SqlParameter("@ ID",
SqlDbType.Int, 4);
prmID.Value = id;
sqlNamesCmd.Par ameters.Add(prm ID);

myAdapter.Selec tCommand = sqlNamesCmd;
myAdapter.Fill( ds);

return ds;

When I run this code in ASP.NET I get the following error

Line 1: Incorrect syntax
near 'SP_GetAddresse sFromContactID' .
Description: An unhandled exception occurred during the
execution of the current web request. Please review the
stack trace for more information about the error and
where it originated in the code.

Exception Details: System.Data.Sql Client.SqlExcep tion:
Line 1: Incorrect syntax
near 'SP_GetAddresse sFromContactID' .

Source Error:


Line 562:
Line 563: myAdapter.Selec tCommand = sqlNamesCmd;
Line 564: myAdapter.Fill( ds);

Source File:
c:\inetpub\wwwr oot\contactsand organisationsse rvice\service
1.asmx.cs Line: 564



Can anyone help please???

[Shiva]

Re: Using SqlDataAdapter

Hi,

Set the command type to StoredProcedure as in sqlNamesCmd.Com mandType =
CommandType.Sto redProcedure; before doing the Fill.

"W Akthar" <anonymous@disc ussions.microso ft.com> wrote in message
news:2b8c01c486 b9$91b0eea0$a30 1280a@phx.gbl.. .
I am trying to use SqlDataAdapter to execute a Stored
Procedure.

The Stored Procedure is as follows

CREATE Procedure SP_GetAddresses FromContactID
@ID int
AS
SELECT
b.StreetAddress 1,
b.StreetAddress 2,
b.StreetAddress 3,
b.Town,
b.PostCode,
b.County,
b.Status
FROM
Names a,
Addresses b,
Add_Ref c
WHERE
a.ID = c.Name_ID
AND
b.ID = c.Address_ID
AND
a.ID = @ID
RETURN
GO


The code to execute his stored procedure is as follows

DataSet ds = new DataSet("Addres sesFromContact" );
// Create a SqlDataAdapter.
SqlDataAdapter myAdapter = new SqlDataAdapter( );
SqlCommand sqlNamesCmd = new SqlCommand
("SP_GetAddress esFromContactID ", _sqlConn);
// Add Parameters to SPROC
SqlParameter prmID = new SqlParameter("@ ID",
SqlDbType.Int, 4);
prmID.Value = id;
sqlNamesCmd.Par ameters.Add(prm ID);

myAdapter.Selec tCommand = sqlNamesCmd;
myAdapter.Fill( ds);

return ds;

When I run this code in ASP.NET I get the following error

Line 1: Incorrect syntax
near 'SP_GetAddresse sFromContactID' .
Description: An unhandled exception occurred during the
execution of the current web request. Please review the
stack trace for more information about the error and
where it originated in the code.

Exception Details: System.Data.Sql Client.SqlExcep tion:
Line 1: Incorrect syntax
near 'SP_GetAddresse sFromContactID' .

Source Error:


Line 562:
Line 563: myAdapter.Selec tCommand = sqlNamesCmd;
Line 564: myAdapter.Fill( ds);

Source File:
c:\inetpub\wwwr oot\contactsand organisationsse rvice\service
1.asmx.cs Line: 564



Can anyone help please???

[Miha Markic [MVP C#]]

Re: Using SqlDataAdapter

Hi,

For the start, try setting:
sqlNamesCmd.Com mandType = CommandType.Sto redProcedure;

--
Miha Markic [MVP C#] - RightHand .NET consulting & development
miha at rthand com

https://www.rthand.com

"W Akthar" <anonymous@disc ussions.microso ft.com> wrote in message
news:2b8c01c486 b9$91b0eea0$a30 1280a@phx.gbl.. .[color=blue]
>I am trying to use SqlDataAdapter to execute a Stored
> Procedure.
>
> The Stored Procedure is as follows
>
> CREATE Procedure SP_GetAddresses FromContactID
> @ID int
> AS
> SELECT
> b.StreetAddress 1,
> b.StreetAddress 2,
> b.StreetAddress 3,
> b.Town,
> b.PostCode,
> b.County,
> b.Status
> FROM
> Names a,
> Addresses b,
> Add_Ref c
> WHERE
> a.ID = c.Name_ID
> AND
> b.ID = c.Address_ID
> AND
> a.ID = @ID
> RETURN
> GO
>
>
> The code to execute his stored procedure is as follows
>
> DataSet ds = new DataSet("Addres sesFromContact" );
> // Create a SqlDataAdapter.
> SqlDataAdapter myAdapter = new SqlDataAdapter( );
> SqlCommand sqlNamesCmd = new SqlCommand
> ("SP_GetAddress esFromContactID ", _sqlConn);
> // Add Parameters to SPROC
> SqlParameter prmID = new SqlParameter("@ ID",
> SqlDbType.Int, 4);
> prmID.Value = id;
> sqlNamesCmd.Par ameters.Add(prm ID);
>
> myAdapter.Selec tCommand = sqlNamesCmd;
> myAdapter.Fill( ds);
>
> return ds;
>
> When I run this code in ASP.NET I get the following error
>
> Line 1: Incorrect syntax
> near 'SP_GetAddresse sFromContactID' .
> Description: An unhandled exception occurred during the
> execution of the current web request. Please review the
> stack trace for more information about the error and
> where it originated in the code.
>
> Exception Details: System.Data.Sql Client.SqlExcep tion:
> Line 1: Incorrect syntax
> near 'SP_GetAddresse sFromContactID' .
>
> Source Error:
>
>
> Line 562:
> Line 563: myAdapter.Selec tCommand = sqlNamesCmd;
> Line 564: myAdapter.Fill( ds);
>
> Source File:
> c:\inetpub\wwwr oot\contactsand organisationsse rvice\service
> 1.asmx.cs Line: 564
>
>
>
> Can anyone help please???
>[/color]

[W Akthar]

Re: Using SqlDataAdapter

Brilliant!!

Thanks![color=blue]
>-----Original Message-----
>Hi,
>
>Set the command type to StoredProcedure as in[/color]
sqlNamesCmd.Com mandType =[color=blue]
>CommandType.St oredProcedure; before doing the Fill.
>
>"W Akthar" <anonymous@disc ussions.microso ft.com> wrote[/color]
in message[color=blue]
>news:2b8c01c48 6b9$91b0eea0$a3 01280a@phx.gbl. ..
>I am trying to use SqlDataAdapter to execute a Stored
>Procedure.
>
>The Stored Procedure is as follows
>
>CREATE Procedure SP_GetAddresses FromContactID
> @ID int
>AS
>SELECT
>b.StreetAddres s1,
>b.StreetAddres s2,
>b.StreetAddres s3,
>b.Town,
>b.PostCode,
>b.County,
>b.Status
>FROM
>Names a,
>Addresses b,
>Add_Ref c
>WHERE
>a.ID = c.Name_ID
>AND
>b.ID = c.Address_ID
>AND
>a.ID = @ID
>RETURN
>GO
>
>
>The code to execute his stored procedure is as follows
>
>DataSet ds = new DataSet("Addres sesFromContact" );
>// Create a SqlDataAdapter.
>SqlDataAdapt er myAdapter = new SqlDataAdapter( );
>SqlCommand sqlNamesCmd = new SqlCommand
>("SP_GetAddres sesFromContactI D", _sqlConn);
>// Add Parameters to SPROC
>SqlParameter prmID = new SqlParameter("@ ID",
>SqlDbType.In t, 4);
>prmID.Value = id;
>sqlNamesCmd.Pa rameters.Add(pr mID);
>
>myAdapter.Sele ctCommand = sqlNamesCmd;
>myAdapter.Fill (ds);
>
>return ds;
>
>When I run this code in ASP.NET I get the following error
>
>Line 1: Incorrect syntax
>near 'SP_GetAddresse sFromContactID' .
>Description: An unhandled exception occurred during the
>execution of the current web request. Please review the
>stack trace for more information about the error and
>where it originated in the code.
>
>Exception Details: System.Data.Sql Client.SqlExcep tion:
>Line 1: Incorrect syntax
>near 'SP_GetAddresse sFromContactID' .
>
>Source Error:
>
>
>Line 562:
>Line 563: myAdapter.Selec tCommand = sqlNamesCmd;
>Line 564: myAdapter.Fill( ds);
>
>Source File:
>c:\inetpub\www root\contactsan dorganisationss ervice\servic[/color]
e[color=blue]
>1.asmx.cs Line: 564
>
>
>
>Can anyone help please???
>
>
>.
>[/color]