dataadapter and stored procs in design time

[limsy]

Hi ppl,
Sorry for asking such a NEWBIE question. I tried looking for answers but
cant find. Maybe its too easy.
I'm used to manual code ADO rather than this .NET wizard and stuff... and i
am really having hard time adjusting. *cry*
I need to fill a dataadapter with data from a stored procedure. I figured
how to create the dataadapter and link it to my database, but my stored proc
needs INPUT parameters. Can i set a default value for my parameters so the
dataadapter can show some data during design-time? Also, i need to dump
this data to a grid.
OR ... how do i do all this with manual code?
This wizard thingie is driving me nuts. (or maybe i am too old for this)

THANKS!!!!


SY Lim the desperate .NET newbie.

 

[crferguson]

I don't blame you about the wizards. I'm a newbie too so take this for
what it's worth.

-- First, I create a class for connecting to the SQL database so I
don't have to type the connection string over and over...

Public Class Global
Public Const ConnectDBase As String =
"Server=servername;Database=databasename;Trusted_Connection=True"
End Class

-- That's just an example; use any connection string that works for you
including password, whatever...
-- Now for the actual retrieving of data using a stored procedure with
a parameter. This example fills a data adapter from the stored
procedure and loads it into a DataTable which, in turn, can be set as a
DataGrid's data source...

Imports System.Data.SqlClient
Public Class Records
'this sets your connection from the previously made Global class,
then creates a SQL command object using the connection, and last a data
adapter using the command object...
Public cnn As New SqlClient.SqlConnection(Global.ConnectDBase)
Public cmd As New SqlClient.SqlCommand("", cnn)
Public da As New SqlDataAdapter(cmd)

Public Function GetMyRecords(ByVal ParameterValue as String) as
DataTable
Dim dt as DataTable = New DataTable

cmd.CommandType = CommandType.StoredProcedure
cmd.CommandText = "st_pro_NameOfMyStoredProcedure"
cmd.Parameters.Add("@ParameterName", sqldbtype.varchar,
20).Value = ParameterValue

dt.TableName = "SomeTableName"
da.Fill(dt)

Return dt
End Function
End Class

-- In the form's Load event you can set your datagrid's datasource to a
datatable made from that Record class. All you'd have to do is pass it
the parameter and you can set that parameter to some default value for
testing if you wish. Something like this:

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As
System.EventArgs) Handles MyBase.Load
Dim strParameter as String = "MyDefaultValue"
Dim r as New Records, dt as DataTable

dt = r.GetMyRecords(strParamter)
Me.DataGrid1.DataSource = dt
End Sub

Hope that helps...

Cory

 

[Cor Ligthert [MVP]]

Limsy,

Between the use of Ado and using the designer lies just using ADONET in
code.

We cannot see what kind of database you use.

Let say it is OleDB than you find here a sample for that.

http://www.vb-tips.com/default.aspx?ID=550279ec-6767-44ff-aaa3-eb8b44af0137

I hope this helps,

Cor

 

[limsy]

Cory,
Thanks for the help.
You hit the problem right on.

ITS SOOOOOO MUCH CLEANER THAN the pile of "code" the Wizard generates!!!!!
(I feel SOOO happy i can finally see code i understand)

THANKS man.

btw... hahaha.. i have a problem.
"Value of type '1-dimensional array of System.Data.DataTable' cannot be
converted to 'System.Data.DataTable'"

From the looks of it, its complaining that i'm trying to put a 1-D array (1
row?) into a multiple row array.
errmm... but aint both the SAME object? (System.Data.DataTable)
Sorry man... I'll be looking for the solution myself... but am hoping you
already have the answer.
(All the new objects is really scary for me. All the DATA family members...
@_@)

Thanks again. You let me see CODE admist the CHAOS.


SY Lim.

 

[limsy]

Sorry ppl ... found my problem. My bad, when i copied and paste your code
....
it got a bit messed up by VS auto-correction thingie.
Its all good now.


SY Lim.

 

[Cor Ligthert [MVP]]

Sy,

I am glad it helped, for your other problem can I absolute not see it
without a piece of code.

Cor