|
web
newsgroups
|
|||||||||||||||||||||||
|
|||||||||||||||||||||||
dataadapter and stored procs in design timeHi 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? :P This wizard thingie is driving me nuts. (or maybe i am too old for this) THANKS!!!! SY Lim the desperate .NET newbie. 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 limsy wrote: Show quoteHide quote > 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? :P > This wizard thingie is driving me nuts. (or maybe i am too old for this) > > THANKS!!!! > > > SY Lim the desperate .NET newbie. 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. :P "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. :P (All the new objects is really scary for me. All the DATA family members... @_@) Thanks again. You let me see CODE admist the CHAOS. :P SY Lim. <crfergu***@gmail.com> wrote in message Show quoteHide quote news:1151079925.544763.263250@g10g2000cwb.googlegroups.com... >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 > > > limsy wrote: >> 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? :P >> This wizard thingie is driving me nuts. (or maybe i am too old for this) >> >> THANKS!!!! >> >> >> SY Lim the desperate .NET newbie. > Sy,
I am glad it helped, for your other problem can I absolute not see it without a piece of code. Cor Show quoteHide quote "limsy" <limsy@newsgroups.nospam> schreef in bericht news:%23%23GtWHMmGHA.4164@TK2MSFTNGP03.phx.gbl... > 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. :P > "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. :P > (All the new objects is really scary for me. All the DATA family > members... @_@) > > Thanks again. You let me see CODE admist the CHAOS. :P > > > SY Lim. > > <crfergu***@gmail.com> wrote in message > news:1151079925.544763.263250@g10g2000cwb.googlegroups.com... >>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 >> >> >> limsy wrote: >>> 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? :P >>> This wizard thingie is driving me nuts. (or maybe i am too old for >>> this) >>> >>> THANKS!!!! >>> >>> >>> SY Lim the desperate .NET newbie. >> > > 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. :P Its all good now. :) SY Lim. <crfergu***@gmail.com> wrote in message Show quoteHide quote news:1151079925.544763.263250@g10g2000cwb.googlegroups.com... >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 > > > limsy wrote: >> 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? :P >> This wizard thingie is driving me nuts. (or maybe i am too old for this) >> >> THANKS!!!! >> >> >> SY Lim the desperate .NET newbie. > 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 Show quoteHide quote "limsy" <limsy@newsgroups.nospam> schreef in bericht news:ex9fNZrlGHA.4268@TK2MSFTNGP05.phx.gbl... > 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? :P > This wizard thingie is driving me nuts. (or maybe i am too old for this) > > THANKS!!!! > > > SY Lim the desperate .NET newbie. > > 
Other interesting topics
Trying to understand API calls
Yes/No ComboBox If you connect to Oracle through .NET please help! variable declaration ? Parse text into words? Find out child type in base class call? Source object on right click change progressbar bar color How to password protect a folder or similar Drawing vertical string |
|||||||||||||||||||||||
