errors when filling a datagrid

  • Thread starter Thread starter Angelina
  • Start date Start date
A

Angelina

Hi,

Im in need of your help once again. I am trying to run a
stored procudure to fill a datagrid. However i keep
encountering the following error:

An unhandled exception of
type 'System.Data.SqlClient.SqlException' occurred in
system.data.dll
Additional information: System error.

the code stops on this line:
da.Fill(ds, "CaraEquip")


here is my full code. has anyone got any idea what i am
doing wrong???

Private Sub RBtnEquipYes_CheckedChanged(ByVal sender As
System.Object, ByVal e As System.EventArgs) Handles
RBtnEquipYes.CheckedChanged

Dim Cn As New SqlConnection("data
source=PARAGLAPTOP\VSdotNET;initial
catalog=CaravanDBLapSQLServ;integrated
security=SSPI;persist security info=False;workstation
id=PARAGLAPTOP;packet size=4096")
Dim cmdCaraEquip As New SqlCommand("SPSelCaraEquip", Cn)
cmdCaraEquip.CommandType =
CommandType.StoredProcedure

cmdCaraEquip.Parameters.Add("@CaraName",SqlDbType.Char,
50)

cmdCaraEquip.Parameters("@CaraName").Value =
CBoxDisCaraNames.SelectedItem

da = New SqlDataAdapter("SPSelCaraEquip", Cn)

ds = New DataSet("CaraEquip")
da.Fill(ds, "CaraEquip")

DataGridEquip.DataSource = ds.Tables(0)

Dim TSCaraEquip As New DataGridTableStyle()
TSCaraEquip.MappingName = "CaraEquip"

Dim TCEquipName As New DataGridTextBoxColumn()
TCEquipName.MappingName = "Equip_Name"
TCEquipName.HeaderText = "Equipment Name"
TCEquipName.Width = 150
TCEquipName.ReadOnly = True
TSCaraEquip.GridColumnStyles.Add(TCEquipName)

DataGridEquip.TableStyles.Add(TSCaraEquip)

DataGridEquip.CaptionText = "Select the meals you wish to
book:"

If RBtnEquipYes.Checked = True Then DataGridEquip.Visible
= True
 
Hi Angelina,

You have either problem with you connection (try calling Cn.Open() before
calling Fill method) or stored procedure.
You might wrap the Fill method into try/catch (Exception ex) block and see
what ex tells you.
 
Hi Miha,

I have placed the fill method into a try/catch block and
i have recieved 2 error messages. The first message
states..
Procedure 'SPSelCaraEquip' expects
parameters '@CaraEquip', Which was not supplied.

Im not sure why im gettin this message as i have declared
and passed the parameters in my code (see my code below)

The second error message i get is...
An unhandled exception of
type 'System.IndexOutOfRangeException' occurred in
system.data.dll
Additional information: Cannot find table 0.


here is my code.... can anyone help me out?


Dim Cn As New SqlConnection("data
source=PARAGLAPTOP\VSdotNET;initial
catalog=CaravanDBLapSQLServ;integrated
security=SSPI;persist security info=False;workstation
id=PARAGLAPTOP;packet size=4096")
Cn.Open()
Dim cmdCaraEquip As New SqlCommand("SPSelCaraEquip", Cn)
cmdCaraEquip.CommandType = CommandType.StoredProcedure

cmdCaraEquip.Parameters.Add("@CaraName",SqlDbType.Char,
50)
cmdCaraEquip.Parameters("@CaraName").Value =
CBoxDisCaraNames.SelectedItem

da = New SqlDataAdapter("SPSelCaraEquip", Cn)
ds = New DataSet("CaraEquip")

Try
Cn.Open
da.Fill(ds, "CaraEquip")
Catch ex As Exception
MsgBox(ex.Message)
Finally
Cn.Close()
End Try

DataGridEquip.DataSource = ds.Tables(0)

Dim TSCaraEquip As New DataGridTableStyle()
TSCaraEquip.MappingName = "CaraEquip"

Dim TCEquipName As New DataGridTextBoxColumn()
TCEquipName.MappingName = "Equip_Name"
TCEquipName.HeaderText = "Equipment Name"
TCEquipName.Width = 150
TCEquipName.ReadOnly = True
TSCaraEquip.GridColumnStyles.Add(TCEquipName)

Dim TCEquipInv As New DataGridTextBoxColumn()
TCEquipInv.MappingName = "Equip_Inv_No"
TCEquipInv.HeaderText = "Equipment No"
TCEquipInv.Width = 100
TCEquipInv.ReadOnly = False
TSCaraEquip.GridColumnStyles.Add(TCEquipName)

DataGridEquip.TableStyles.Add(TSCaraEquip)

If RBtnEquipYes.Checked = True Then DataGridEquip.Visible
= True
 
Hi Angelina,


Angelina said:
Hi Miha,

I have placed the fill method into a try/catch block and
i have recieved 2 error messages. The first message
states..
Procedure 'SPSelCaraEquip' expects
parameters '@CaraEquip', Which was not supplied.

Im not sure why im gettin this message as i have declared
and passed the parameters in my code (see my code below)
Click to expand...

No, you didn't. You did pass @CaraName parameter, but not @CaraEquip.
The second error message i get is...
An unhandled exception of
type 'System.IndexOutOfRangeException' occurred in
system.data.dll
Additional information: Cannot find table 0.
Click to expand...

Well, that's why you continue the flow even after you've recived the
exception (no datatable was created so there is no Tables(0)).
 
Hi Miha,

Im sorry but i made a typo in my last message. The first
error message actually stated that the
parameter '@CaraName' is not provided. (I think i
wrote '@CaraEquip' in my last post by accident!!- That was
well spotted thou :o) )

Any idea why it is saying i have not provided this
parameter when i have (or at least i think i have!) in my
code??
Is my coding correct? I have checked the SPROC and it
works fine in access when i run it. The problem lies in
the code i provided in my last post.

thx
 
Hi Angelina,

You are not using your command.
The following line creates a dataadapter with *new* select command:
da = New SqlDataAdapter("SPSelCaraEquip", Cn)

da = new SqlDataAdapter(cmdCaraEquip)

should work a bit better ;-)
 
Back
Top