errors when filling a datagrid

[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

 

[Miha Markic]

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.

 

[Angelina]

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

 

[Miha Markic]

Hi Angelina,

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)

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.

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

 

[Angelina]

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

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

 

[Miha Markic]

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

 

[angelina]

Thx Miha,

u have rescused me out of trouble again )
It works now )