[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 983
  • Last Modified:

VB.NET Create DataSet through coding

I want to create Dataset through coding instead of generate dataset through sqldataadapter

I am using following code in my sqldataapater:

SQL = "select * from userstable where username ='" & TextBox1.Text & "'"
SqlDataAdapter1.SelectCommand.CommandText = SQL

 When I am dragging dataset from toolbar menu to form, it is asking following question:

Typed dataset
Untyped dataset

Please guide which one I have to select and how to code in my form to fill data into the dataset from sqldataadapter.

Rgds. Mehram
0
Mehram
Asked:
Mehram
1 Solution
 
suprapto45Commented:
Hi,

Is this a DataGrid from toolbar?

http://www.vbdotnetheaven.com/Code/Apr2003/003.asp

David
0
 
WelkinMazeCommented:
0
 
SStoryCommented:
An typed dataset is a way that .NET creates a class that maps to the XML of the dataset and allows for Intellisense and strong typing.  That is the only real advantage I know of in using typed instead of untyped.  If you don't already have a typed one, then you could use an untyped one. It really depends on what you are trying to do.
0
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
MehramAuthor Commented:
I am doing following on form load:

Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        'Dim cn As String
        'cn = "workstation id=dliserver;packet size=4096;user id=sa;data source=DLIServer;persist security info=True;initial catalog=Northwind;password=applmgr"
        SqlConnection1.ConnectionString = getConnection()
        SqlConnection1.Open()

        If SqlConnection1.State = ConnectionState.Open Then
            Label1.Visible = True
            Label1.Text = "Connected"
        Else
            Label1.Visible = True
            Label1.Text = "Retry"
        End If

    End Sub

The following code runing on button click

    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        SQL = "select * from userstable where username ='" & TextBox1.Text & "'"
        'MsgBox(SQL)
        SqlDataAdapter1.SelectCommand.CommandText = SQL

  End Sub

Can you guide my how I can create Dataset for this. Just for your knowledge I am using only one table namely "Userstable"

Can you guide me steps to create dataset?
0
 
SStoryCommented:
This is strictly from memory and you may have to play with the syntax
'declare a dataset
Dim ds as new dataset
'fill it via the data adapter
SqlDataAdapter1.Fill(ds,"UsersTable")

BTW,

***JUST A FREE WARNING****

If you just posted your real password; change it IMMEDIATELY.  Also it is not a good idea to use the sa account for anything other than administration.  You should really create another limited user account.  

Even if this is just some toy project you don't care about, it is not a good idea to give admin access to your SQL Server out.
0
 
MehramAuthor Commented:
Sir, I really thanks for your support.

I am using following code on button:
Dim DS As New DataSet
        'Label1.Text = TextBox1.Text
        Dim SQL As String
        SQL = "select * from Userstable where username= '" & TextBox1.Text & "'"
        DAUserP.SelectCommand.CommandText = SQL
        'DataSet11.Tables.Add.TableName = "UsersTable"
        DS.Clear()
        DAUserP.Fill(DS, "Userstable")
        dg.DataSource = DS
        dg.DataBind()

on click it is showing following Error:


Server Error in '/WebApplication3' Application.
--------------------------------------------------------------------------------

Fill: SelectCommand.Connection property has not been initialized.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidOperationException: Fill: SelectCommand.Connection property has not been initialized.

Source Error:


Line 86:         'DataSet11.Tables.Add.TableName = "UsersTable"
Line 87:         DS.Clear()
Line 88:         DAUserP.Fill(DS, "Userstable")
Line 89:         dg.DataSource = DS
Line 90:         dg.DataBind()
 

Source File: c:\inetpub\wwwroot\WebApplication3\WebForm1.aspx.vb    Line: 88

Stack Trace:


[InvalidOperationException: Fill: SelectCommand.Connection property has not been initialized.]
   System.Data.Common.DbDataAdapter.GetConnection(IDbCommand command, String method)
   System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)
   WebApplication3.WebForm1.Button1_Click(Object sender, EventArgs e) in c:\inetpub\wwwroot\WebApplication3\WebForm1.aspx.vb:88
   System.Web.UI.WebControls.Button.OnClick(EventArgs e)
   System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
   System.Web.UI.Page.ProcessRequestMain()

 


--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET Version:1.1.4322.573


Please guide what i do now?
and how to bind primary key in dataset?
Rgds. Mehram
0
 
MehramAuthor Commented:
Sir, I really thanks for your support.

I am using following code on button:
Dim DS As New DataSet
        'Label1.Text = TextBox1.Text
        Dim SQL As String
        SQL = "select * from Userstable where username= '" & TextBox1.Text & "'"
        DAUserP.SelectCommand.CommandText = SQL
        'DataSet11.Tables.Add.TableName = "UsersTable"
        DS.Clear()
        DAUserP.Fill(DS, "Userstable")
        dg.DataSource = DS
        dg.DataBind()

on click it is showing following Error:


Server Error in '/WebApplication3' Application.
--------------------------------------------------------------------------------

Fill: SelectCommand.Connection property has not been initialized.
Description: An unhandled exception occurred during the execution of the current web request. Please review the stack trace for more information about the error and where it originated in the code.

Exception Details: System.InvalidOperationException: Fill: SelectCommand.Connection property has not been initialized.

Source Error:


Line 86:         'DataSet11.Tables.Add.TableName = "UsersTable"
Line 87:         DS.Clear()
Line 88:         DAUserP.Fill(DS, "Userstable")
Line 89:         dg.DataSource = DS
Line 90:         dg.DataBind()
 

Source File: c:\inetpub\wwwroot\WebApplication3\WebForm1.aspx.vb    Line: 88

Stack Trace:


[InvalidOperationException: Fill: SelectCommand.Connection property has not been initialized.]
   System.Data.Common.DbDataAdapter.GetConnection(IDbCommand command, String method)
   System.Data.Common.DbDataAdapter.FillFromCommand(Object data, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, Int32 startRecord, Int32 maxRecords, String srcTable, IDbCommand command, CommandBehavior behavior)
   System.Data.Common.DbDataAdapter.Fill(DataSet dataSet, String srcTable)
   WebApplication3.WebForm1.Button1_Click(Object sender, EventArgs e) in c:\inetpub\wwwroot\WebApplication3\WebForm1.aspx.vb:88
   System.Web.UI.WebControls.Button.OnClick(EventArgs e)
   System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument)
   System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument)
   System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData)
   System.Web.UI.Page.ProcessRequestMain()

 


--------------------------------------------------------------------------------
Version Information: Microsoft .NET Framework Version:1.1.4322.573; ASP.NET Version:1.1.4322.573


Please guide what i do now?
and how to bind primary key in dataset?
Rgds. Mehram
0
 
SStoryCommented:
Just from memory, and based on your above comments, I think you need to do something like the following.

SqlDataAdapter1.SelectCommand=new SQLCommand(SQL,Sqlconnection1)

The command object has no database connection associated with it.  That is the problem.
0
 
MehramAuthor Commented:
Sir I am checking and reverting soon
0
 
MehramAuthor Commented:
Not runing:

Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click
        Dim DS As New DataSet
        Dim SQL As String
        SQL = "select * from Userstable where username= '" & TextBox1.Text & "'"
        DAUserP.SelectCommand = New SQLCommand(SQL, Sqlconnection1)
        DS.Clear()
        DAUserP.Fill(DS, "Userstable")
        dg.DataSource = DS
        dg.DataBind()
end sub

in above command it is showing error on SQLCommand
0
 
SStoryCommented:
Ok.
Assuming
        SQLConnection1 is connecting to SQL Server
        DAUserP is a (new SQLDataAdapter) which could have been defined like:

        Dim DAuserP As New SqlDataAdapter(SQL, sqlconnection1)

I don't know why it isn't working.

What error are you getting on the SQLCommand?
If it is still about the SQLConnection object then you probably have a problem with it.  Where are you declaring it?
Just on the form? or in Code?
If in code be sure it is like this
dim SQLConnection1 as new SQLConnection(<put your connection string here>)

0

Featured Post

Concerto's Cloud Advisory Services

Want to avoid the missteps to gaining all the benefits of the cloud? Learn more about the different assessment options from our Cloud Advisory team.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now