[VBS.NET] what's wrong in my code cannot insert record

Discussion in 'Mixed Languages' started by carmz, Oct 23, 2012.

  1. carmz

    carmz MDL Senior Member

    Nov 29, 2011
    285
    7
    10
    #1 carmz, Oct 23, 2012
    Last edited by a moderator: Apr 20, 2017
    Hi,I just want to ask some help with you,can you please tell me what is wrong with my code why it cannot insert record to the database.:worthy:


    Code:
    Imports System.Data.SqlClient
    
    Public Class Form1
        Private m_con As New SqlConnection
        Private m_DA As SqlDataAdapter
        Private m_CB As SqlCommandBuilder
        Private m_dataTable As New DataTable
        Private m_rowPosition As Integer = 0
    
    
        Private Sub Form1_FormClosed(ByVal sender As Object, ByVal e As System.Windows.Forms.FormClosedEventArgs) Handles Me.FormClosed
            m_con.Close()
            m_con.Dispose()
    
        End Sub
    
        Private Sub Form1_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
    
            m_con.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\book.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
            m_con.Open()
    
            m_DA = New SqlDataAdapter("select * from book", m_con)
            m_CB = New SqlCommandBuilder(m_DA)
    
            m_DA.Fill(m_dataTable)
    
        End Sub
    
        Private Sub BtnExit_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnExit.Click
            Me.Close()
    
        End Sub
    
        Private Sub BtnAdd_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles BtnAdd.Click
           
         
            m_DA.Fill(m_dataTable)
            Dim adNewRow As DataRow = m_dataTable.NewRow()
    
            adNewRow("FIRSTNAME") = "George"
            adNewRow("LASTNAME") = "Carlin"
            m_dataTable.Rows.Add(adNewRow)
            m_DA.Update(m_dataTable)
    
            m_rowPosition = m_dataTable.Rows.Count - 1
    
        End Sub
    End Class
    
    
    
    
    
     
  2. Josh Cell

    Josh Cell MDL Developer

    Jan 8, 2011
    3,519
    7,097
    120
    #2 Josh Cell, Oct 23, 2012
    Last edited by a moderator: Apr 20, 2017
    Do you've created the database and table with the "book" name on your SQL Express instance? This code looks good for me, but you can change the connection string for:

    Code:
    "Data Source=.\SQLEXPRESS;Initial Catalog=book;Integrated Security=True"
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  3. carmz

    carmz MDL Senior Member

    Nov 29, 2011
    285
    7
    10
    #3 carmz, Oct 23, 2012
    Last edited by a moderator: Apr 20, 2017
    (OP)
    Hi Josh thank you so much for the reply, I thought no one hears me...yeah i already make the database
    f2.jpg f.jpg

    but i am confuse why is it outside in the bin folder there is an instance of the database,and then i i looked at in the bin folder there is also...can you please help me on this.:worthy:
     
  4. Josh Cell

    Josh Cell MDL Developer

    Jan 8, 2011
    3,519
    7,097
    120
    Do you've the Microsoft SQL Management Studio for work with your SQL Instance installed on your computer?
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  5. carmz

    carmz MDL Senior Member

    Nov 29, 2011
    285
    7
    10
    I don't know that josh,how can check that?
     
  6. carmz

    carmz MDL Senior Member

    Nov 29, 2011
    285
    7
    10
    yes i installed it.

    s.jpg
     
  7. Josh Cell

    Josh Cell MDL Developer

    Jan 8, 2011
    3,519
    7,097
    120
    Exists two differences from Managed SQL and Unmanaged SQL.

    An Managed SQL is encapsuled and emulated by an Network Instance on your computer, on this case is the .\SQLEXPRESS.

    Unmanaged SQL is a uncapsulated SQL database that is stored as file on your application directory. In example is the book.mdf.

    You have to decide what's the best for your solution. If your application needs of Online Database Connection, an Managed SQL Instance is needed.

    In your application I have seen two connections, the ".\SQLEXPRESS" and "book.mdf", it isn't correct.

    I've coded an simply example of Unmanaged Database in VB.NET using DataSet:

    http://www.datafilehost.com/download-29085124.html

    It will locally save your data in a .mdf file.

    [​IMG]
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  8. carmz

    carmz MDL Senior Member

    Nov 29, 2011
    285
    7
    10
    Hi josh, thank you for your effort,I will try to look this...more power to you always.
     
  9. carmz

    carmz MDL Senior Member

    Nov 29, 2011
    285
    7
    10
    Done downloading...:biggrin:...Okay i will see this
     
  10. carmz

    carmz MDL Senior Member

    Nov 29, 2011
    285
    7
    10
    I've seen now, but i am confuse where did you put your connection string?
     
  11. carmz

    carmz MDL Senior Member

    Nov 29, 2011
    285
    7
    10
    #11 carmz, Oct 23, 2012
    Last edited: Oct 23, 2012
    (OP)
    Hi josh, can you please show me some steps in making the .mdf or the database.maybe i get wrong in selecting the database.

    First i click the Data > Add new data source on the menu tab.


    Second I choose the data source type there are 4 option namely. Database, Service, Object ,sharepoint

    I click the Database and then the next button.

    Then i choose Database Model 2 option namely Dataset,Entity Data Model

    Then i choose the Dataset click next and etc...

    That's how i created my database.
     
  12. Josh Cell

    Josh Cell MDL Developer

    Jan 8, 2011
    3,519
    7,097
    120
    #12 Josh Cell, Oct 23, 2012
    Last edited by a moderator: Apr 20, 2017
    App.config says:

    Code:
    <?xml version="1.0" encoding="utf-8" ?>
    <configuration>
        <configSections>
        </configSections>
        <connectionStrings>
            <add name="DatabaseApplication.My.MySettings.DbForAppConnectionString"
                connectionString="Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\DataBase\DbForApp.mdf;Integrated Security=True;User Instance=True"
                providerName="System.Data.SqlClient" />
        </connectionStrings>
        <startup>
            <supportedRuntime version="v4.0" sku=".NETFramework,Version=v4.0,Profile=Client" />
        </startup>
    </configuration>
    This connection string is needed by DataSet.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  13. carmz

    carmz MDL Senior Member

    Nov 29, 2011
    285
    7
    10
    #13 carmz, Oct 23, 2012
    Last edited by a moderator: Apr 20, 2017
    (OP)

    Okay, so in my application in the Form_Load do i need to remove the connection string?because i have the connection string in the app.config.
     
  14. Josh Cell

    Josh Cell MDL Developer

    Jan 8, 2011
    3,519
    7,097
    120
    #14 Josh Cell, Oct 23, 2012
    Last edited: Oct 23, 2012
    Please note that you're currently working with Manual M$-SQL Connection using Namespace Classes.

    I'm working with .NET Objects such as DataSets / Bindings / Sources.

    You have to learn how this objects works to configure it, is really 'simply' but very complicated because the number of connected objects.

    If you can, please post your source to I'd a look into it.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  15. carmz

    carmz MDL Senior Member

    Nov 29, 2011
    285
    7
    10

    You mean i am going to make application. with Datasets example.?

    I thought the one that i posted is using the Dataset...but josh the one that i posted,is my first application in database. I am still getting to know on the Dataset...and i am not yet familiar in Bindings,.
     
  16. Josh Cell

    Josh Cell MDL Developer

    Jan 8, 2011
    3,519
    7,097
    120
    I know of.. But you'd have to learn the DataSet also, I've posted one example for you.

    I said that your code looks fine for me, maybe you don't have created the 'book' table on your database, please post your full source of this project as me to I'd a look into it.
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  17. carmz

    carmz MDL Senior Member

    Nov 29, 2011
    285
    7
    10

    josh, by the way when i choose the connection i used the Microsoft SQL Database File. is this correct?
     
  18. Josh Cell

    Josh Cell MDL Developer

    Jan 8, 2011
    3,519
    7,097
    120
    My connection string that I've posted is valid for Local SQLEXPRESS instance. Isn't your case.

    See my last post..
     
    Stop hovering to collapse... Click to collapse... Hover to expand... Click to expand...
  19. carmz

    carmz MDL Senior Member

    Nov 29, 2011
    285
    7
    10
    #19 carmz, Oct 23, 2012
    Last edited by a moderator: Apr 20, 2017
    (OP)

    Hi Josh, It's working now i edit the connection

    Code:
    
    m_con.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=|DataDirectory|\book.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True" m_con.Open()
    
    to this

    Code:
     m_con.ConnectionString = "Data Source=.\SQLEXPRESS;AttachDbFilename=" & _
           "C:\Documents and Settings\Jemz\My Documents\Visual Studio 2010\Projects\Book\Book\book.mdf;" & _
           "Integrated Security=True;Connect Timeout=30;User Instance=True"
    
    But that is all my source code i have only make 2 textbox and 2 buttons, i just want to try this database, I just replace my textbox to names so that i can see if my code can insert.
     
  20. carmz

    carmz MDL Senior Member

    Nov 29, 2011
    285
    7
    10