Error when getting data from the database. VWDE/SQL/Asp.Net/C#

I get the error:
Index out of range exception was unhandled by user code.
At the line:
Label4.Text += reader["RelativeLN"].ToString();

The two lines above it:
Label4.Text += reader["Relation"].ToString() + ": ";
Label4.Text += reader["RelativeFN"].ToString();

will read just fine if I comment out that third line. I don'tunderstand why this could be happening. There is data in the db for theRelativeLN column and it has the same data type as the previous twocolumns.

private void getRelatives()
{
//Define database connection
SqlConnection conn = new SqlConnection(
"Server=localhost\\SqlExpress;Database=MyDB;" + "Integrated Security=True");
//Create command
SqlCommand comm = new SqlCommand("SELECT Relation, RelativeFN FROMRelativeTable WHERE RelativeTable.UserID IN (SELECT UserID FROM UsrTblWHERE UserName = @.usrnmeLbl)", conn);
comm.Parameters.AddWithValue("@.usrnmeLbl", usrNmeLbl.Text);
conn.Open();
SqlDataReader reader = comm.ExecuteReader();
while (reader.Read())
{
Label4.Text += reader["Relation"].ToString() + ": ";
Label4.Text += reader["RelativeFN"].ToString();
Label4.Text += reader["RelativeLN"].ToString();
}
}

I'm hoping someone can provide me some info on what could possibly be going wrong.
Thank you so much in advance.

Hello my friend,

There may be data in the database for it but you are not including this column in your query.

Your query starts with: SELECT Relation, RelativeFN FROM RelativeTable

Change it to start with: SELECT Relation, RelativeFN, RelativeLN FROM RelativeTable

Kind regards

Scotty

|||

OMG, thank you so much. I have been finagling around with this code so much I missed my changes.

THANK YOU THANK YOU!

Error when calling stored procedure

I am trying to execute a store procedure from ASP/VB but it fails with the message:

Incorrect syntax near 'InitProject'.

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: Incorrect syntax near 'InitProject'.

Source Error:

Line 24: cmd.Parameters("@.ProjectId").Value = 3Line 25: cn.Open()Line 26: cmd.ExecuteNonQuery()Line 27: cn.Close()Line 28: End Sub

Here is my code:

'Execute the InitProject stored procedure
'Create the connection from the string in the web.config file
Dim cn As SqlConnection = New SqlConnection(ConfigurationManager.ConnectionStrings("SMARTConnectionString").ConnectionString)
'I want to execute InitProject stored procedure
Dim cmd As SqlCommand = New SqlCommand("InitProject", cn)
'With the parameter @.ProjectId = 3
cmd.Parameters.Add(New SqlParameter("@.ProjectId", Data.SqlDbType.Int))
cmd.Parameters("@.ProjectId").Direction = Data.ParameterDirection.Input
cmd.Parameters("@.ProjectId").Value = 3
cn.Open()
'But this fails
cmd.ExecuteNonQuery()
cn.Close()

And my stored procedure is defined as:

[dbo].[InitProject] @.ProjectId int
-- Add the parameters for the stored procedure here
AS
BEGIN
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;

-- Insert statements for procedure here
insert into MATERIAL ( PROJECT_ID, SECTION_ID, CATEGORY_ID, ROOM_ID, ITEM_ID )
select @.ProjectId, SECTION_ID, CATEGORY_ID, ROOM_ID, ITEM_ID
from MATERIAL_TEMPLATE
END

The store procedure works fine when I do

exec InitProject 3

in sql query.

I have been away from .NET but try putting commandtype = storedproc. I dont remember the syntax but you could figure it out. It would go before adding the parameters.|||

Like this:

Dim cmd As SqlCommand = New SqlCommand("InitProject", cn)

cmd.CommandType = CommandType.StoredProcedure

.....

|||

Thank you very much for your help. It works like this:

'I want to execute InitProject stored procedure
Dim cmd As SqlCommand = New SqlCommand("InitProject", cn)
cmd.CommandType = Data.CommandType.StoredProcedure
'With the parameter @.ProjectId = 3
cmd.Parameters.Add(New SqlParameter("@.ProjectId", Data.SqlDbType.Int))
cmd.Parameters("@.ProjectId").Direction = Data.ParameterDirection.Input
cmd.Parameters("@.ProjectId").Value = 3
cn.Open()
'Now it works
cmd.ExecuteNonQuery()
cn.Close()

I don't understand why I need to prefix withData. when others don't need.

|||

Hi

Hierarchy : System.Data.CommandType.StoredProcedure

You just import namespace System so you need to prefix with Data

If you import namespace System.Data you don't need that.

Hope it helps.