asked on

why do get this error cannot implicitly convert type 'system.data.datatable' to int

I'm get this error when try to get data from DataGridView by using stored procedure.
The stored procedures:

create proc [dbo].[ADD_LAB_RESULTS]
@SAMPLE_ID int,
@ORDER_ID int,
@TESTID int,
@GROUPID int,
@SAMPLE_STATUS int
as
INSERT INTO LAB_RESULTS
([SAMPLE_ID]
,[ORDER_ID]
,[TESTID]
,[GROUPID]
,[SAMPLE_STATUS])
VALUES
(@SAMPLE_ID
,@ORDER_ID
,@TESTID
,@GROUPID
,@SAMPLE_STATUS)

create proc [dbo].[GET_GROUPID]
@TESTID int
as
select groupid from Groups
where Groups.groupid = ''+ @TESTID +''
My C# code when click save button BtnSave:
-------------------------------------------------------------------
this is the parameters void :

public void ADD_LAB_RESULTS(int sample_id, int order_id, int testid, int groupid,int SAMPLE_STATUS)
{
DAL.DataAccessLayer DAL = new DAL.DataAccessLayer();
DAL.open();
SqlParameter[] param = new SqlParameter[5];
param[0] = new SqlParameter("@sample_id", SqlDbType.Int);
param[0].Value = sample_id;

param[1] = new SqlParameter("@order_id", SqlDbType.Int);
param[1].Value = order_id;

param[2] = new SqlParameter("@testid", SqlDbType.Int);
param[2].Value = testid;

param[3] = new SqlParameter("@groupid", SqlDbType.Int);
param[3].Value = groupid;

param[4] = new SqlParameter("@SAMPLE_STATUS", SqlDbType.Int);
param[4].Value = SAMPLE_STATUS;

DAL.ExecuteCommand("ADD_LAB_RESULTS", param);
DAL.close();

}
---------------------------------------------------------------------
private void btnSave_Click(object sender, EventArgs e)
{
for (int a = 0; a < dgvReceive.Rows.Count - 1; a++)
{
ADD_LAB_RESULTS(
Convert.ToInt32(dgvReceive.Rows[a].Cells[5].Value),
Convert.ToInt32(txtOrder.Text),
Convert.ToInt32(dgvReceive.Rows[a].Cells[0].Value),
GET_GROUPID(Convert.ToInt32(dgvReceive.Rows[a].Cells[0].Value.ToString())),
4);
}
I'm want to send testid and get groupid if the test was a group your suggestion please and check attached file
and if there is alternative way to do this method?
cannot-convert-screen.png

Dorababu M

What is GET_GROUPID here

Ziad Abuqasem

ASKER

GET_GROUPID is stored procedure

create proc [dbo].[GET_GROUPID]
@TESTID int
as
select groupid from Groups
where Groups.groupid = ''+ @TESTID +''

and this is the database table :
CREATE TABLE [Groups](
      [groupid] [int] NOT NULL,
      [groupdesc] [nvarchar](50) NOT NULL,
      [Testid] [int] NOT NULL)
I have groups and each group includes some lab tests
I want to get the groupid for the selected test

Dorababu M

You need to store the result that is returning from stored procedure to a variable and pass it to ADD_LAB_RESULTS as you are passing the stored procedure it is giving that error.
ADD_LAB_RESULTS is expecting an input parameter which is of type int

SOLUTION

Dorababu M

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ziad Abuqasem

ASKER

what is this ExecuteScalar
its give me error on the line
string groupId = DAL.ExecuteScalar("GET_GROUPID", param).ToString();
DAL does not contain a definition of ExecuteScalar
in DAL file I created the methods for accessing the database connection , open close the connection and read , write , update and insert method and I call these methods in the program from DAL
So what is the method or definition for ExecuteScalar ?

Dorababu M

The ExecuteScalar() is used for retrieve a single value from Database after the execution of the statement. May be in you DAL you need to write some logic for that which will be similar to ExecuteCommand.

Dorababu M

Sample code

string sqlconnection = "Data Source=.;Initial Catalog=TestDatabase;Integrated Security=true";
SqlConnection con1 = new SqlConnection(sqlconnection);
con1.Open();
SqlCommand cmd1 = new SqlCommand("SELECT Id FROM Test_Json", con1); // Replace this with your stored procedure
Int32 count = Convert.ToInt32(cmd1.ExecuteScalar());

Open in new window

Ziad Abuqasem

ASKER

I'm used the following code but its not return the groupid and its saved 0 in the database
for (int a = 0; a < dgvReceive.Rows.Count - 1; a++)
{
DAL.DataAccessLayer DAL = new DAL.DataAccessLayer();
DAL.open();
SqlParameter[] param = new SqlParameter[1];
param[0] = new SqlParameter("@TESTID ", SqlDbType.Int);
param[0].Value = Convert.ToInt32(dgvReceive.Rows[a].Cells[0].Value.ToString());
string groupId = DAL.SelectData("GET_GROUPID", param).ToString();

int value = 0;
if (!string.IsNullOrWhiteSpace(groupId))
value = Convert.ToInt32(groupId);
DAL.close();
sample.ADD_LAB_RESULTS(Convert.ToInt32(dgvReceive.Rows[a].Cells[6].Value),
Convert.ToInt32(txtOrder.Text),
Convert.ToInt32(dgvReceive.Rows[a].Cells[0].Value),
value,4);

}
------------------------------------------------
I have this code also to read data from the database DAL.selectdata

public DataTable SelectData(string stored_procedure, SqlParameter[] param)
{
SqlCommand sqlcmd = new SqlCommand();
sqlcmd.CommandType = CommandType.StoredProcedure;
sqlcmd.CommandText = stored_procedure;
sqlcmd.Connection = sqlconnection;

if (param != null)
{
for (int a = 0; a < param.Length; a++)
{
sqlcmd.Parameters.Add(param[a]);
}
}
SqlDataAdapter da = new SqlDataAdapter(sqlcmd);
DataTable dt = new DataTable();
da.Fill(dt);
return dt;
}

Ziad Abuqasem

ASKER

the concept I need is
laboratory system
the doctor will request investigations for the patient some investigations 1 test only and some investigation group test
for example :
test id test name test type tests id under group test
50 Sodium 1 test
51 Complete Blood Count (CBC) 7 tests under CBC (1,2,3,4,5,6,7)
now in the procedure ADD_LAB_RESULTS I have the following structure when insert the data I need to check if testid = groupid then insert the tests and group id if the test id != group id then insert test id only and I need it like the following output
test id groupid
50 0
1 51
2 51
3 51
4 51
5 51
6 51
7 51

I have also tests table I insert all the tests the alone test and group tests and another table Groups I add the group and tests under each group.

Dorababu M

Make sure that you are getting data here

 string groupId = DAL.SelectData("GET_GROUPID", param).ToString();
int value = 0;
if (!string.IsNullOrWhiteSpace(groupId))
   value = Convert.ToInt32(groupId);

Open in new window

As you are saying 0 is getting inserted I guess you are not getting data here

Ziad Abuqasem

ASKER

I executed the following code I made one request includes 3 tests
1 test group test
2 test not group test
the data saved as the following

testid groupid
22 0
23 0
24 0 (this test group test but return 0)

SOLUTION

Dorababu M

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ziad Abuqasem

ASKER

I tried this code and I get the error

string groupId = DAL.SelectData("GET_GROUPID", param).Rows[0]["groupid"].ToString();
int value = 0;
if (!string.IsNullOrWhiteSpace(groupId))
value = Convert.ToInt32(groupId);

"there is no row at position 0 "

Dorababu M

Then there are nor rows getting returned from DAL.SelectData("GET_GROUPID", param).

Dorababu M

Are you sure your SP always returns data?

ASKER CERTIFIED SOLUTION

Dorababu M

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ziad Abuqasem

ASKER

i checked the select statement its working and add watch showing the groupid value
can you check the attached file

Ziad Abuqasem

ASKER

not always return data only if the testid = groupid its return data
some tests not group test so it will not return any groupid
groupid-available.png

Dorababu M

Can you show me what you are getting here

DAL.SelectData("GET_GROUPID", param)

Open in new window

Ziad Abuqasem

ASKER

I get the groupid and i send the testid value from datagridview
I fill datagrid view with order informations from the doctor
as I said before
some tests only one test one to one
some tests group test the doctor request group name (CBC) this group test include 7 parameters the lab will do 7 parameters under name CBC so I need to get the groupid and save it because next step I will read this groupid + 7 parameters under this group and the lab technisian will enter the results for those parameters

create proc [dbo].[GET_GROUPID]
@TESTID int
as
select groupid from Groups
where Groups.groupid = ''+ @TESTID +''

Dorababu M

I didn't get you

Ziad Abuqasem

ASKER

I will tell you the full story
when i go to the doctor the doctor will request investigations from medical laboratory
to check the blood , urine , stool and so on
in medical laboratory there is 2 kinds on tests (group test like CBC)
when the doctor request the patient to make this test the patient will go to laboratory and they will take blood from the patient
when the patient will take the result they will give him report result for 7 results
the design i need easy
---------------------------------------------------------------
I made table for laboratory tests contain
testid test name test type
1 CBC group test --- this test the doctor can request it also as group of tests
2 Calcium not group (one test only) ---- this test the doctor can request it as one test
3 WBC not group (one test only) ---- this test under CBC and cannot request it alone
4 HBC not group (one test only) ---- this test under CBC and cannot request it alone
-----------------------------------------------------------
I made another table Groups table tests contains

groupid testid
1 3
1 4
-----------------------------------------------------------------
when the doctor request CBC I want to get groupid which is number 1 + the tests under it which is number 3 & 4
and save it in the ADD_LAB_RESULTS
thats all

Dorababu M

I am still confused what data you are showing in your DataGridView? Is it from Groups table or any other?

Ziad Abuqasem

ASKER

thank you and later on I need some help in another issue