table 0 not found

Question

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

omkar mhaiskar · Answer

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.

Ankit Nandekar · Answer

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.

Mayur Gujrathi · Answer

no when i am executing stored procedue as u said it still iving same error Return Value 0

Ankit Nandekar · Answer

when u r executing store procedure this record is coming or not ?i think it is not coming?

Mayur Gujrathi · Answer

One record is present in select * from Dis_req_Dtls

Ankit Nandekar · Answer

check why select * from Dis_req_Dtls not executing.

Mayur Gujrathi · Answer

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

Ankit Nandekar · Answer

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 (046) 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.

Mayur Gujrathi · Answer

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

Mayur Gujrathi · Answer

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

Ankit Nandekar · Answer

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.

Mayur Gujrathi · Answer

Yes i did it now output is Return value 0 and message is 1 row affected now what to do

Mayur Gujrathi · Answer

Omkar what u have posted require open and available connection

Ankit Nandekar · Answer

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.

Mayur Gujrathi · Answer

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)

omkar mhaiskar · Answer

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(); } }

Ankit Nandekar · Answer

Mayur check what data u r getting into ur dataset by using breakpoint.

Mayur Gujrathi · Answer

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 (046) 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

omkar mhaiskar · Answer

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.

Ankit Nandekar · Answer

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.

Mayur Gujrathi · Answer

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

Suthish Nair · Answer

Duplicate post again !

Amit Choudhary · Answer

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 .

Mayur Gujrathi · Answer

hi i did like this but not working Dim da As SqlDataAdapter = New SqlDataAdapter(select * from Dis_req_Dtls_MAK cmd)

Amit Choudhary · Answer

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.