[2 days left] What’s wrong with your cloud strategy? Learn why multicloud solutions matter with Nimble Storage.Register Now

x
?
Solved

Having Data Fill Problem

Posted on 2006-11-05
21
Medium Priority
?
435 Views
Last Modified: 2007-11-27
In a VB.NET project I have set up a SQL adapter and can successfully Preview the data but when I try to do it in my form with:

SqlDataAdapter1.Fill(DataSet11)

It immediately returns a system error.  Why would this happen?

0
Comment
Question by:John Gates, CISSP
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 7
  • 4
  • +2
21 Comments
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 17876746
Hi dimante,

which error? can you show your code?

Cheers!
0
 
LVL 18

Author Comment

by:John Gates, CISSP
ID: 17876928
it's that simple just this fill command is in a button event.  The error returned is "System Error"  I can preview data no problem from the adapter...
0
 
LVL 18

Expert Comment

by:vbturbo
ID: 17879527


SqlDataAdapter1.Fill(DataSet11, "your_tblName_here")
0
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.

 
LVL 8

Expert Comment

by:doobdave
ID: 17880154
As emoreau mentions, you need to give us a bit more information, and perhaps your code.
Without that, we can only guess what going wrong.

Is the complete error message "System Error" or is there further information there?

I am guessing thhat you are using the Data Access components from the toolbox, given that you say you can 'preview' the data ok.
Is it that those controols have already called the .Fill method, which then causes the error when you call it again?

Please post your code, including the Designere-generated code pertaining to the data access.

Best Regards,

David
0
 
LVL 18

Author Comment

by:John Gates, CISSP
ID: 17885810
Remember you asked for it LOL:

#Region " Windows Form Designer generated code "

    Public Sub New()
        MyBase.New()

        'This call is required by the Windows Form Designer.
        InitializeComponent()

        'Add any initialization after the InitializeComponent() call

    End Sub

    'Form overrides dispose to clean up the component list.
    Protected Overloads Overrides Sub Dispose(ByVal disposing As Boolean)
        If disposing Then
            If Not (components Is Nothing) Then
                components.Dispose()
            End If
        End If
        MyBase.Dispose(disposing)
    End Sub

    'Required by the Windows Form Designer
    Private components As System.ComponentModel.IContainer

    'NOTE: The following procedure is required by the Windows Form Designer
    'It can be modified using the Windows Form Designer.  
    'Do not modify it using the code editor.
    Friend WithEvents SqlConnection1 As System.Data.SqlClient.SqlConnection
    Friend WithEvents SqlDataAdapter1 As System.Data.SqlClient.SqlDataAdapter
    Friend WithEvents Button1 As System.Windows.Forms.Button
    Friend WithEvents SqlSelectCommand1 As System.Data.SqlClient.SqlCommand
    Friend WithEvents SqlInsertCommand1 As System.Data.SqlClient.SqlCommand
    Friend WithEvents DataSet11 As ORStudentCreate.DataSet1
    <System.Diagnostics.DebuggerStepThrough()> Private Sub InitializeComponent()
        Me.SqlConnection1 = New System.Data.SqlClient.SqlConnection
        Me.SqlDataAdapter1 = New System.Data.SqlClient.SqlDataAdapter
        Me.Button1 = New System.Windows.Forms.Button
        Me.SqlSelectCommand1 = New System.Data.SqlClient.SqlCommand
        Me.SqlInsertCommand1 = New System.Data.SqlClient.SqlCommand
        Me.DataSet11 = New ORStudentCreate.DataSet1
        CType(Me.DataSet11, System.ComponentModel.ISupportInitialize).BeginInit()
        Me.SuspendLayout()
        '
        'SqlConnection1
        '
        Me.SqlConnection1.ConnectionString = "workstation id=ROCKET;packet size=4096;user id=sa;data source=""3dserv\SQL20053D"";" & _
        "persist security info=False;initial catalog=DI"
        '
        'SqlDataAdapter1
        '
        Me.SqlDataAdapter1.InsertCommand = Me.SqlInsertCommand1
        Me.SqlDataAdapter1.SelectCommand = Me.SqlSelectCommand1
        Me.SqlDataAdapter1.TableMappings.AddRange(New System.Data.Common.DataTableMapping() {New System.Data.Common.DataTableMapping("Table", "ADST", New System.Data.Common.DataColumnMapping() {New System.Data.Common.DataColumnMapping("STATUS", "STATUS"), New System.Data.Common.DataColumnMapping("SCHOOLNUM", "SCHOOLNUM"), New System.Data.Common.DataColumnMapping("LASTNAME", "LASTNAME"), New System.Data.Common.DataColumnMapping("FIRSTNAME", "FIRSTNAME"), New System.Data.Common.DataColumnMapping("MIDDLENAME", "MIDDLENAME"), New System.Data.Common.DataColumnMapping("PERMNUM", "PERMNUM"), New System.Data.Common.DataColumnMapping("BIRTHDATE", "BIRTHDATE"), New System.Data.Common.DataColumnMapping("PRNTGUARD", "PRNTGUARD"), New System.Data.Common.DataColumnMapping("MAILADDR", "MAILADDR"), New System.Data.Common.DataColumnMapping("CITY", "CITY"), New System.Data.Common.DataColumnMapping("ZIPCODE", "ZIPCODE"), New System.Data.Common.DataColumnMapping("STATE", "STATE"), New System.Data.Common.DataColumnMapping("TELEPHONE", "TELEPHONE"), New System.Data.Common.DataColumnMapping("BIRTHPLACE", "BIRTHPLACE"), New System.Data.Common.DataColumnMapping("HOMELNG", "HOMELNG"), New System.Data.Common.DataColumnMapping("PRIMARYLNG", "PRIMARYLNG"), New System.Data.Common.DataColumnMapping("DSTLINK", "DSTLINK")})})
        '
        'Button1
        '
        Me.Button1.Location = New System.Drawing.Point(256, 400)
        Me.Button1.Name = "Button1"
        Me.Button1.TabIndex = 1
        Me.Button1.Text = "Button1"
        '
        'SqlSelectCommand1
        '
        Me.SqlSelectCommand1.CommandText = "SELECT STATUS, SCHOOLNUM, LASTNAME, FIRSTNAME, MIDDLENAME, PERMNUM, BIRTHDATE, PR" & _
        "NTGUARD, MAILADDR, CITY, ZIPCODE, STATE, TELEPHONE, BIRTHPLACE, HOMELNG, PRIMARY" & _
        "LNG, DSTLINK FROM SASI.ADST WHERE (STATUS = '')"
        Me.SqlSelectCommand1.Connection = Me.SqlConnection1
        '
        'SqlInsertCommand1
        '
        Me.SqlInsertCommand1.CommandText = "INSERT INTO SASI.ADST(STATUS, SCHOOLNUM, LASTNAME, FIRSTNAME, MIDDLENAME, PERMNUM" & _
        ", BIRTHDATE, PRNTGUARD, MAILADDR, CITY, ZIPCODE, STATE, TELEPHONE, BIRTHPLACE, H" & _
        "OMELNG, PRIMARYLNG, DSTLINK) VALUES (@STATUS, @SCHOOLNUM, @LASTNAME, @FIRSTNAME," & _
        " @MIDDLENAME, @PERMNUM, @BIRTHDATE, @PRNTGUARD, @MAILADDR, @CITY, @ZIPCODE, @STA" & _
        "TE, @TELEPHONE, @BIRTHPLACE, @HOMELNG, @PRIMARYLNG, @DSTLINK); SELECT STATUS, SC" & _
        "HOOLNUM, LASTNAME, FIRSTNAME, MIDDLENAME, PERMNUM, BIRTHDATE, PRNTGUARD, MAILADD" & _
        "R, CITY, ZIPCODE, STATE, TELEPHONE, BIRTHPLACE, HOMELNG, PRIMARYLNG, DSTLINK FRO" & _
        "M SASI.ADST"
        Me.SqlInsertCommand1.Connection = Me.SqlConnection1
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@STATUS", System.Data.SqlDbType.VarChar, 1, "STATUS"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@SCHOOLNUM", System.Data.SqlDbType.VarChar, 3, "SCHOOLNUM"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@LASTNAME", System.Data.SqlDbType.VarChar, 40, "LASTNAME"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@FIRSTNAME", System.Data.SqlDbType.VarChar, 20, "FIRSTNAME"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@MIDDLENAME", System.Data.SqlDbType.VarChar, 13, "MIDDLENAME"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@PERMNUM", System.Data.SqlDbType.VarChar, 12, "PERMNUM"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@BIRTHDATE", System.Data.SqlDbType.DateTime, 8, "BIRTHDATE"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@PRNTGUARD", System.Data.SqlDbType.VarChar, 35, "PRNTGUARD"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@MAILADDR", System.Data.SqlDbType.VarChar, 35, "MAILADDR"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@CITY", System.Data.SqlDbType.VarChar, 24, "CITY"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@ZIPCODE", System.Data.SqlDbType.VarChar, 10, "ZIPCODE"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@STATE", System.Data.SqlDbType.VarChar, 2, "STATE"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@TELEPHONE", System.Data.SqlDbType.Decimal, 9, System.Data.ParameterDirection.Input, False, CType(15, Byte), CType(0, Byte), "TELEPHONE", System.Data.DataRowVersion.Current, Nothing))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@BIRTHPLACE", System.Data.SqlDbType.VarChar, 20, "BIRTHPLACE"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@HOMELNG", System.Data.SqlDbType.VarChar, 3, "HOMELNG"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@PRIMARYLNG", System.Data.SqlDbType.VarChar, 3, "PRIMARYLNG"))
        Me.SqlInsertCommand1.Parameters.Add(New System.Data.SqlClient.SqlParameter("@DSTLINK", System.Data.SqlDbType.Decimal, 5, System.Data.ParameterDirection.Input, False, CType(7, Byte), CType(0, Byte), "DSTLINK", System.Data.DataRowVersion.Current, Nothing))
        '
        'DataSet11
        '
        Me.DataSet11.DataSetName = "DataSet1"
        Me.DataSet11.Locale = New System.Globalization.CultureInfo("en-US")
        '
        'Form1
        '
        Me.AutoScaleBaseSize = New System.Drawing.Size(5, 13)
        Me.ClientSize = New System.Drawing.Size(576, 459)
        Me.Controls.Add(Me.Button1)
        Me.Name = "Form1"
        Me.Text = "Form1"
        CType(Me.DataSet11, System.ComponentModel.ISupportInitialize).EndInit()
        Me.ResumeLayout(False)

    End Sub

#End Region

   
    Private Sub Button1_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button1.Click





    End Sub


    Private Sub Form1_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles MyBase.Load
        SqlDataAdapter1.Fill(DataSet11)
    End Sub
End Class


This results in the system error as soon as the page loads.  The SQL server is SQL 2005.  The version of VB.NET is 1.1 sp1
Please advise.
0
 
LVL 70

Expert Comment

by:Éric Moreau
ID: 17886061
try a simpler query like this:
Me.SqlSelectCommand1.CommandText = "SELECT STATUS, SCHOOLNUM, LASTNAME, FIRSTNAME FROM SASI.ADST WHERE (STATUS = '')"
0
 
LVL 18

Author Comment

by:John Gates, CISSP
ID: 17886267
That was actually not the problem.  The problem involved saving the password for the connection.  Do you know how to save it so that it is not readable in compiled sourcecode?
0
 
LVL 4

Expert Comment

by:darkpriest
ID: 17886313
Here's a hint, go grab yourself a copy of vs2005 express and run it with .net 2.0 all your problems will magically dissapear.  Then you can encrypt your web.config which stores the passwords for connection strings which have saved me alot of time :)

0
 
LVL 4

Assisted Solution

by:darkpriest
darkpriest earned 800 total points
ID: 17886321
On a more serious note,

Me.SqlConnection1.ConnectionString = "workstation id=ROCKET;packet size=4096;user id=sa;data source=""3dserv\SQL20053D"";" & _
        "persist security info=False;initial catalog=DI"

Wont be viewable on deployment, so you dont to hide it unless you have other developers that you dont want seeing it.
0
 
LVL 18

Author Comment

by:John Gates, CISSP
ID: 17886548
It will if you view the source code in a hex editor...  Unfortunately for this app I have to do it in 1.1 so that's the way it goes 8(  

I am still having problems as my dataset will not create an update statement which I am confused about also...
0
 
LVL 18

Accepted Solution

by:
vbturbo earned 1200 total points
ID: 17888100
Checkout this tutorial on working with dataset's. Gives you multiple posibilyties with a minimum of code effort and code lines.

http://www.homeandlearn.co.uk/NET/nets12p9.html

And here from the begining

http://www.homeandlearn.co.uk/NET/nets12p4.html

vbturbo
0
 
LVL 4

Expert Comment

by:darkpriest
ID: 17903425
Hex Editor thats true, i guess never really thought of that since i encrypt all my connection strings anyway > :)

I have some old projects in 1.1, and i mean some really old ones but with some basic adapters i used to specify the parameters like this since your doing a Fill im under the indication that you have already set the parameters.


'----------- Porition of Code
   
        Dim dtScanner As DataTable
        Dim dtScannerRow As DataRow

        dtScanner = Scannerds1.Tables("tblWebOrders")

        SCANDA.SelectCommand.Parameters(0).Value = "True"
        Scannerds1.Tables("tblWebOrders").Clear()
        SCANDA.Fill(Scannerds1, "tblWebOrders")

       
'---------------------------

This code was just something quick and simple it sets a parameter to a value, up in your code you have parameters being used.

You need to set each one, since i haven't seen any code where you have set these values for your select command, so incase you haven't set your parameters hope that helps.
0
 
LVL 18

Author Comment

by:John Gates, CISSP
ID: 17903536
What I am not understanding is when I set up a SQLDataAdapter and attempt to configure it it will not generate the update, insert, or delete code it returns an error.  The user for now is sa so permissions on the sql2005 database are not the issue.  Why is .NET refusing to generate the above functions?
0
 
LVL 4

Expert Comment

by:darkpriest
ID: 17903567
Just for testing sake, im about to go into a meeting could you try using a OLE adapter.  I should be back in about 2 hrs or so.

0
 
LVL 4

Expert Comment

by:darkpriest
ID: 17903569
One thought though, is there a primary key in the table?  Usually tables will not allow generation of updates if there is no primary key.
0
 
LVL 4

Expert Comment

by:darkpriest
ID: 17904023
Im back briefly,

Had a chance to try the ole or check the primary key?
0
 
LVL 18

Expert Comment

by:vbturbo
ID: 17904736
Hi

As darkpriest  states you need to define primary key.

Here is a small simplyfied ole code you can test out ! allthough if implemented in a larger solution then watch the scope for the commandbuilder      


        Dim pk(0) As DataColumn'define a primarykey collum
        Dim  cbSqlDataAdapter1 As OleDb.OleDbCommandBuilder 'define the command builder
        Dim  sId As Integer 'variabel to hold the found primarykey in the dataset
        Dim  sSql As String
        Dim  TmpStr As String
        Dim  oDatarow As DataRow
-----------------------------------------------------

        m_cn.Open() 'open the connection
        DataSet11.Clear()

        'fill the dataset and setup the dataadapter
        sSql = "SELECT Priority,Id,Firmname,Mailaddress,Fname,Lname,Katagori FROM tblname"
        SqlDataAdapter1.SelectCommand = New OleDb.OleDbCommand(sSql, m_cn)
        SqlDataAdapter1.Fill(DataSet11, "tblname")

        DataSet11.Tables("tblname").Columns("Id").AutoIncrement = True
        pk(0) = DataSet11.Tables("tblname").Columns("Id")   '<--- the name collumname of your primary key
        DataSet11.Tables("tblname").PrimaryKey = pk
        m_cn.Close()

 ----------------------------------------------------  
       ' do the UPDATE to the datasource ! all the modifications made to the dataset   "insert , update , delete"

        Public Sub updatetblname()
        cbSqlDataAdapter1 = New OleDb.OleDbCommandBuilder(SqlDataAdapter1)
        SqlDataAdapter1.Update(DataSet11, "tblname")

        End Sub

--------------------------------------------------------

Example ...

       'loop through the dataset collum (6) to see if a match is found to conditon = TmpStr

       TmpStr = "some condition"

        For Each oDatarow In DataSet11.Tables("tblname").Rows

            If oDatarow.Item(6) = TmpStr Then
                sId = oDatarow.Item(1)'the collum with primary
                DataSet11.Tables("tblname").Rows.Find(sId)' find the particular row with that primary
                oDatarow.Delete()
            End If
        Next

        updatetblname()' call the update sub
----------------------------------------------------
vbturbo
0
 
LVL 18

Author Comment

by:John Gates, CISSP
ID: 17905782
I guess I should state the entire scope of what I am doing.  I am working against a database that CANNOT be modified in any way.  If there is no primary key, that is how it must remain and in the table in question that is true.  So if I hand code the connection etc is that still using ADO.NET?  I thought the point of .NET was to ease database programming by allowing you to drag components to the designer..  If I have to hand code it all that's fine but it seems to me that .NET is not all it is cracked up to be.  If I hand code this (Even without a primary key) I will successfully be able to add and update records so why won't the designer just warn about it, but still create the code?  
0
 
LVL 18

Expert Comment

by:vbturbo
ID: 17907526
Hi

I belive that when using the designer to generate your " update delete " statements it would need a primary key to create such statements/methods for you,
although it can create "select and insert " statements under your mentioned problem description.
But without an unique identifier (pk) you wount be able to acomplish that.
It would be fair to say that , if there is a ambiguos possibility then the designer wount create your desired request. Such thing doesn't exist in any progamming langues.

And yes off course you hand write code to work around/acomplish that, what the designer wount do for you.
Also you seem to know that designers are a bit limited in some regards.

I personally belive there is benefits and disadvantece useing ado.net "most benefits"
The greatest one in my humble opinion is useing dataset's , you can make any manipulation to without makeing/addressing a connection request to the database
until you are done with all your data manipulation and then in the END, do one final request to the database namely ...updateing all the modifications you made
to the dataset


Answer to your question
>>>so why won't the designer just warn about it, but still create the code?  
As mentioned above it creates the insert and select methods for you and you would also have seen 2 warnings (yellow once)
SO that why the designer still creates the code for you.(thogh it is limited)





0
 
LVL 18

Author Comment

by:John Gates, CISSP
ID: 17907551
Well with that said I will just hand code the connections etc and not drag and drop.  Thanks for your input!

-D-
0
 
LVL 4

Expert Comment

by:darkpriest
ID: 17911167
what vbturbo said ;)
0

Featured Post

[Webinar] Lessons on Recovering from Petya

Skyport is working hard to help customers recover from recent attacks, like the Petya worm. This work has brought to light some important lessons. New malware attacks like this can take down your entire environment. Learn from others mistakes on how to prevent Petya like worms.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Since .Net 2.0, Visual Basic has made it easy to create a splash screen and set it via the "Splash Screen" drop down in the Project Properties.  A splash screen set in this manner is automatically created, displayed and closed by the framework itsel…
It’s quite interesting for me as I worked with Excel using vb.net for some time. Here are some topics which I know want to share with others whom this might help. First of all if you are working with Excel then you need to Download the Following …
We’ve all felt that sense of false security before—locking down external access to a database or component and feeling like we’ve done all we need to do to secure company data. But that feeling is fleeting. Attacks these days can happen in many w…
Are you ready to place your question in front of subject-matter experts for more timely responses? With the release of Priority Question, Premium Members, Team Accounts and Qualified Experts can now identify the emergent level of their issue, signal…
Suggested Courses

656 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question