table 0 not found

Mayur Gujrathi

i am calling stored procedure but getting error of table 0 not found
pr_ins_upd_reqdtls is stored procedure name
Dim rowcount As Integer
        Dim ds As DataSet = New DataSet
        Dim cmd As SqlCommand = New SqlCommand
        Dim constr As String = ConfigurationManager.AppSettings("PooledConnectionString")
        Dim sqlcon As SqlConnection = New SqlConnection(constr)
        cmd.Connection = sqlcon
        cmd.CommandType = CommandType.StoredProcedure
        cmd.CommandText = "pr_ins_upd_reqdtls"
        Dim da As SqlDataAdapter = New SqlDataAdapter("pr_ins_upd_reqdtls", sqlcon)
        da.SelectCommand = cmd




        Dim Var_StrLoginame As String = Session("LoginName")
        cmd.Parameters.AddWithValue("@pa_action", "SEARCHCHK")
        cmd.Parameters.AddWithValue("@pa_tholder", ddlselect.Value)
        If (filter.Value = "I") Then
            cmd.Parameters.AddWithValue("@pa_boid", hdfilter.Value)
            cmd.Parameters.AddWithValue("@pa_req_slip_no", "")
            cmd.Parameters.AddWithValue("@pa_req_date", "")
        ElseIf (filter.Value = "S") Then
            cmd.Parameters.AddWithValue("@pa_boid", "")
            cmd.Parameters.AddWithValue("@pa_req_slip_no", hdfilter.Value)
            cmd.Parameters.AddWithValue("@pa_req_date", "")
        ElseIf (filter.Value = "D") Then
            cmd.Parameters.AddWithValue("@pa_boid", "")
            cmd.Parameters.AddWithValue("@pa_req_slip_no", "")
            Dim pdate As Date
            pdate = ClsDate.GetFormatedDate(hdfilter.Value, "DD/MM/YYYY")
            cmd.Parameters.AddWithValue("@pa_req_date", pdate)
        End If
        cmd.Parameters.AddWithValue("@pa_login_name", Var_StrLoginame)
        'parameters not in use
        cmd.Parameters.AddWithValue("@pa_id", 0)
        cmd.Parameters.AddWithValue("@pa_boname", "")
        cmd.Parameters.AddWithValue("@pa_sholder", "")
        cmd.Parameters.AddWithValue("@pa_chk_yn", 0)
        cmd.Parameters.AddWithValue("@pa_rmks", "")
        cmd.Parameters.Add("@pa_error", SqlDbType.VarChar, 8000)
        cmd.Parameters("@pa_error").DbType = DbType.AnsiString
        cmd.Parameters("@pa_error").Direction = ParameterDirection.Output

        da.Fill(ds, "pr_ins_upd_reqdtls")

        If (Not (ds.Tables(0).Rows.Count > 0)) Then
            rowcount = ds.Tables(0).Rows.Count
            'GridView1.Visible = False
            GridView1.DataSource = ds.Tables(0)
            GridView1.DataBind()
            lblsubheader.Text = "Records Found, " & rowcount & " "
        ElseIf ds.Tables(0).Rows.Count > 0 Then

25 Replies

Know the answer? Post it — somebody with the same question will find it here.

omkar mhaiskar15y ago
y did you create so strange query if want take record from only one table.

or also try with only respective qeries if this query is working well then.
tell me.

put normal query in sql studio try with this and tell me.
0
Ankit Nandekar15y ago
i think problem with in ur conditions as u said that one record is Dis_req_Dtls_MAK table show if ur codition is right show data has to come bcoz it is not coming means it is not going inside the condition block.
0
Mayur Gujrathi15y ago
no when i am executing stored procedue as u said it still iving same error
Return Value
0
0
Ankit Nandekar15y ago
when u r executing store procedure this record is coming or not ?i think it is not coming?
0
Mayur Gujrathi15y ago
One record is present in select * from Dis_req_Dtls
0
Ankit Nandekar15y ago
check why select * from Dis_req_Dtls not executing.
0
Mayur Gujrathi15y ago
Hi ankit thanks for reply

in that condition there are again 3 conditions
in only that condition there is select * from Dis_req_Dtls
   IF @pa_tholder = 'A'
                        BEGIN
                            select * from Dis_req_Dtls
0
Ankit Nandekar15y ago
As u said that u r trying to fetching data which is in bellow condition right
f @pa_action ='SEARCHCHK'
begin

IF @pa_tholder = 'P'
BEGIN
select * from Dis_req_Dtls_MAK
where deleted_ind IN (0,4,6)
AND CREATED_BY <> @PA_LOGIN_NAME
AND req_slip_no LIKE CASE WHEN @pa_req_slip_no = '' THEN '%' ELSE @pa_req_slip_no END
AND Boid LIKE CASE WHEN @pa_boid = '' THEN '%' ELSE @pa_boid END
AND case when @pa_req_date = '' then '0' else req_date end = case when @pa_req_date = '' then '0' else @pa_req_date end
END

IF @pa_tholder = 'R'
BEGIN
select * from Dis_req_Dtls_MAK
where deleted_ind = 3
AND req_slip_no LIKE CASE WHEN @pa_req_slip_no = '' THEN '%' ELSE @pa_req_slip_no END
AND Boid LIKE CASE WHEN @pa_boid = '' THEN '%' ELSE @pa_boid END
AND case when @pa_req_date = '' then '0' else req_date end = case when @pa_req_date = '' then '0' else @pa_req_date end
END

IF @pa_tholder = 'A'
BEGIN
select * from Dis_req_Dtls
where deleted_ind = 1
AND req_slip_no LIKE CASE WHEN @pa_req_slip_no = '' THEN '%' ELSE @pa_req_slip_no END
AND Boid LIKE CASE WHEN @pa_boid = '' THEN '%' ELSE @pa_boid END
AND case when @pa_req_date = '' then '0' else req_date end = case when @pa_req_date = '' then '0' else @pa_req_date end
END
end

But in this coditions u r selecting data from Dis_req_Dtls table but this data not coming when u r executing ur storeprocedur may be problem is in ur if conditons bcoz u r not going into if block thats why select * from Dis_req_Dtls not working and u r not getting values from this table so please check u r conditions once again.
0
Mayur Gujrathi15y ago
it is not in procedure but in result window where this sp executed it is not in sp this thing
i am reapeting again

DECLARE    @return_value int,
        @pa_error varchar(8000)

EXEC    @return_value = [citrus_usr].[pr_ins_upd_reqdtls]

at end like this

SELECT    @pa_error as N'@pa_error'

SELECT    'Return Value' = @return_value

0
Mayur Gujrathi15y ago
Ankit
at top of stored procedure it is like this

DECLARE    @return_value int,
        @pa_error varchar(8000)

EXEC    @return_value = [citrus_usr].[pr_ins_upd_reqdtls]

at end like this

SELECT    @pa_error as N'@pa_error'

SELECT    'Return Value' = @return_value

after executing stored procedure as u said i come with result

Return Value
   0
@pa_error
null

i think return value means no of rows and it is coming 0 after doing this
is'nt it please reply now what to do
0
Ankit Nandekar15y ago
u r not getting any table as a output means ur select query within a storeprocedure not called .thats why u r getting this error bcoz in ur storeprocedure no select statment executing and u r trying to display blank table into gridview bcoz u dont have nay data in dataset it is showing "Cannot find table 0." error. show please check every condition in store procedure why ur select query not executing.
0
Mayur Gujrathi15y ago
Yes i did it now output is Return value 0 and message is 1 row affected
now what to do
0
Mayur Gujrathi15y ago
Omkar what u have posted require open and available connection
0
Ankit Nandekar15y ago
ok do one thing in ur sqlserver run ur storeprocedure by Rightclicking on Storeprocedure u can see Execute Storeprocedure option is there and try to pass data manualy(which is ur parameter value which u r passing through form) into there value column and if u r passing null value to any field then just checkpass null value checkbox and click on ok check u r getting any data or not if u r not getting then problem with ur storeprocedure.and if u r getting any table value there then check it that value is comming in dataset or not i think that may be problem is with ur storeprocedure.
0
Mayur Gujrathi15y ago
Ankit currently i am totally blank and also nervous please tell me how to do that
i am filling dataset like this
da.Fill(ds, "pr_ins_upd_reqdtls")
0
omkar mhaiskar15y ago
Ok mayur
this is the sample code for to get values in the datatable. do not use same as it is. just create your code in following manner. this code is in c# just convert it in vb.net.
datatable dt=new datatable();
try
{
connectionString = ConfigurationManager.ConnectionStrings["testApplication"].ConnectionString;//connection string.
                _connection = new SqlConnection(connectionString); //connection name
                 _command = new SqlCommand();//sql command
                _connection.Open();//connection open
                _command.CommandText = pName;
                _command.CommandType = CommandType.StoredProcedure;
                _command.Connection = _connection;


                 //add your command parameter here.

                dt.Load(_command.ExecuteReader());
}
catch
{

}
finally
{
_command.Dispose();
                _connection.Close();
}
}
0
Ankit Nandekar15y ago
Mayur check what data u r getting into ur dataset by using breakpoint.
0
Mayur Gujrathi15y ago
i am going on this condition in my stored procedure

if @pa_action ='SEARCHCHK'
                begin

                        IF @pa_tholder = 'P'
                        BEGIN
                            select * from Dis_req_Dtls_MAK
                            where deleted_ind IN (0,4,6)
                            AND CREATED_BY <> @PA_LOGIN_NAME
                            AND req_slip_no LIKE CASE WHEN @pa_req_slip_no = '' THEN '%' ELSE @pa_req_slip_no END
                            AND Boid LIKE CASE WHEN @pa_boid = '' THEN '%' ELSE @pa_boid END
                            AND case when @pa_req_date = '' then '0' else req_date end = case when @pa_req_date = '' then '0' else @pa_req_date end
                        END

                        IF @pa_tholder = 'R'
                        BEGIN
                            select * from Dis_req_Dtls_MAK
                            where deleted_ind = 3
                            AND req_slip_no LIKE CASE WHEN @pa_req_slip_no = '' THEN '%' ELSE @pa_req_slip_no END
                            AND Boid LIKE CASE WHEN @pa_boid = '' THEN '%' ELSE @pa_boid END
                            AND case when @pa_req_date = '' then '0' else req_date end = case when @pa_req_date = '' then '0' else @pa_req_date end
                        END

                        IF @pa_tholder = 'A'
                        BEGIN
                            select * from Dis_req_Dtls
                            where deleted_ind = 1
                            AND req_slip_no LIKE CASE WHEN @pa_req_slip_no = '' THEN '%' ELSE @pa_req_slip_no END
                            AND Boid LIKE CASE WHEN @pa_boid = '' THEN '%' ELSE @pa_boid END
                            AND case when @pa_req_date = '' then '0' else req_date end = case when @pa_req_date = '' then '0' else @pa_req_date end
                        END
                end

@omkar can you give code for using data table
0
omkar mhaiskar15y ago
Dear Mayur

for trial and error basic use datatable instead of dataadaptor. then you will get perticullar table info at a time.

then you will get what happen exacatly there.
0
Ankit Nandekar15y ago
Mayur i think there is a problem in ur storeprocedur check wheather it is written for select data from table or not mainly this error occure when we r not fetching any data from table like we r writing insert or update query in storeprocedure no select query and with the use of dataset we r trying to display it into gridview or dataview please check ur store procedure.
0
Mayur Gujrathi15y ago
I did it but is again giving error
table 0 not found

@ suthish sir
while posting it first time problem was occured , and posted it again without wathing wheather it is posted or not
0
Suthish Nair15y ago
Duplicate post again !
0
Amit Choudhary15y ago
Dim da As SqlDataAdapter = New SqlDataAdapter(cmd);
da.Fill(ds, "pr_ins_upd_reqdtls")

this will surely work.. keep the order of the statements same.. just put the Initialization with its call to FILL method .

0
Mayur Gujrathi15y ago
hi i did like this but not working

Dim da As SqlDataAdapter = New SqlDataAdapter("select * from Dis_req_Dtls_MAK", cmd)
0
Amit Choudhary15y ago
Hi,

Please put a check before the statement "If (Not (ds.Tables(0).Rows.Count > 0)) Then" for ds.Tables(0) is not null.

also update this statement..
Dim da As SqlDataAdapter = New SqlDataAdapter("pr_ins_upd_reqdtls", sqlcon) pass Command object instead of sqlcon here..

Thanks.

0