Using SqlDataAdapter

[W Akthar]

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

The Stored Procedure is as follows

CREATE Procedure SP_GetAddressesFromContactID
@ID int
AS
SELECT
b.StreetAddress1,
b.StreetAddress2,
b.StreetAddress3,
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("AddressesFromContact");
// Create a SqlDataAdapter.
SqlDataAdapter myAdapter = new SqlDataAdapter();
SqlCommand sqlNamesCmd = new SqlCommand
("SP_GetAddressesFromContactID", _sqlConn);
// Add Parameters to SPROC
SqlParameter prmID = new SqlParameter("@ID",
SqlDbType.Int, 4);
prmID.Value = id;
sqlNamesCmd.Parameters.Add(prmID);

myAdapter.SelectCommand = 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_GetAddressesFromContactID'.
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.SqlClient.SqlException:
Line 1: Incorrect syntax
near 'SP_GetAddressesFromContactID'.

Source Error:


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

Source File:
c:\inetpub\wwwroot\contactsandorganisationsservice\service
1.asmx.cs Line: 564



Can anyone help please???

 

[Shiva]

Hi,

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

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

The Stored Procedure is as follows

CREATE Procedure SP_GetAddressesFromContactID
@ID int
AS
SELECT
b.StreetAddress1,
b.StreetAddress2,
b.StreetAddress3,
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("AddressesFromContact");
// Create a SqlDataAdapter.
SqlDataAdapter myAdapter = new SqlDataAdapter();
SqlCommand sqlNamesCmd = new SqlCommand
("SP_GetAddressesFromContactID", _sqlConn);
// Add Parameters to SPROC
SqlParameter prmID = new SqlParameter("@ID",
SqlDbType.Int, 4);
prmID.Value = id;
sqlNamesCmd.Parameters.Add(prmID);

myAdapter.SelectCommand = 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_GetAddressesFromContactID'.
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.SqlClient.SqlException:
Line 1: Incorrect syntax
near 'SP_GetAddressesFromContactID'.

Source Error:


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

Source File:
c:\inetpub\wwwroot\contactsandorganisationsservice\service
1.asmx.cs Line: 564



Can anyone help please???

 

[Miha Markic [MVP C#]]

Hi,

For the start, try setting:
sqlNamesCmd.CommandType = CommandType.StoredProcedure;

 

[W Akthar]

Brilliant!!

Thanks!