3 Replies - 3880 Views - Last Post: 16 November 2014 - 02:18 PM Rate Topic: -----

#1 airukraine1   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 20-October 14

Importing User Input from TextBoxes into Local SQL Database - VB 2012

Posted 12 November 2014 - 11:51 PM

Hello,

I want to thank you all in advance for helping me with this issue. I may not have complete understanding or have the code down at all, but I appreciate all the help I can get.

I am fairly basic VB user who is creating a program where users input their Bench Press Weight, Mood, Calories, and Sleep into a database. For right now I just want to input user numeric data into the database. The end goal is to then create correlating graphs that show these data, but that is another topic...

The biggest step right now is figuring out how to import the user data into the Local SQL Server database.
I tried doing DataGridView by following some videos of users who have used that, but it just updates the DataGridView and not my aptly named "dbo.UserData" table when I view it through "Server Explorer."

I know I do not have all the code and I am not asking to do the homework for me, I just have done lots of research and tried different methods but I cannot find a specific code or way to import my User Data into my Local Database.



I have inserted an image of what my WindowsForm User Input Data looks like. I am trying to import all the entered user data (Weight, Sleep, Calories, Mood, etc..) by coding the "Save Data" button.

Posted Image

---




This image is the Server Explorer SQL information and the description of the SQL database.



Posted Image


--------


The code that I am trying to use to code up the button "Save Data" is this ( I might be putting this code in the wrong button):

 Private Sub btn_SaveData_Click(sender As Object, e As EventArgs) Handles btn_SaveData.Click
        Dim myconnect As New SqlClient.SqlConnection
        myconnect.ConnectionString = "Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Users\Name\Documents\Visual Studio 2012\Projects\Project\Project\Real Project\Real Real Project\database\Database1.mdf;Integrated Security=True"

        Dim mycommand As SqlClient.SqlCommand = New SqlClient.SqlCommand()
        mycommand.Connection = myconnect
        mycommand.CommandText = "INSERT INTO UserData (Weight,Mood,Sleep, Calories,User Id) VALUES (@Weight,@Mood,@Sleep,@Calories,@User Id)"
        myconnect.Open()

        Try
            mycommand.Parameters.Add("@Weight", SqlDbType.Decimal).Value = txt_Weight.Text
            mycommand.Parameters.Add("@Mood", SqlDbType.Decimal).Value = trackBar_Mood.Text
            mycommand.Parameters.Add("@Sleep", SqlDbType.Decimal).Value = txt_Sleep.Text
            mycommand.Parameters.Add("@Calories", SqlDbType.Decimal).Value = txt_Calories.Text
            mycommand.Parameters.Add("@User Id", SqlDbType.NChar).Value = ComboBox1.Text
            mycommand.ExecuteNonQuery()
            MsgBox("Success")
        Catch ex As System.Data.SqlClient.SqlException
            MsgBox(ex.Message)
        End Try
        myconnect.Close()



    End Subcode]




"Id" is the Primary Key (PK) which VB.NET should give out automatic key. The "User Id" is selected from a Combo Box that has ONE ID (since the program is made for one user), like "John Doe", and then the user inputs data: Weight, Mood, Sleep, Calories, which the user clicks "Save Data" and that sends the data to the database table "dbo.UserData". For right now, I am just trying to figure out how to send inputted data for Weight, Mood, Sleep, Calories, into the table. There is no other tables, therefore no foreign keys either. Just this one table.

The "Day" row in the image from my table is not a concern now since I will code that later on since it is going to use a Calendar Module for user to pick a day from which he/she enters data. The main concern is getting data into the rows: Weight, Mood, Sleep, Calories.


I apologize if I seem rudimentary in my understanding in connecting user input data into database. Any simple explanation of this with steps will help me out so much in understanding how this works. Thank you again for your patience and help.

Is This A Good Question/Topic? 0
  • +

Replies To: Importing User Input from TextBoxes into Local SQL Database - VB 2012

#2 thecoat   User is offline

  • D.I.C Addict

Reputation: 153
  • View blog
  • Posts: 537
  • Joined: 07-December 13

Re: Importing User Input from TextBoxes into Local SQL Database - VB 2012

Posted 13 November 2014 - 10:42 AM

Are you getting an exception when you click save? Adding the following line will output your exception info to the VS Output window:

       Catch ex As System.Data.SqlClient.SqlException
           MsgBox(ex.Message)
Debug.WriteLine(ex.Message & vbCrLf & ex.StackTrace)
       End Try


I'm not seeing anything in your code that looks horribly wrong, so I suspect the devil is in the details, posting the exception info (message and stack trace) would give more insight.
Was This Post Helpful? 0
  • +
  • -

#3 airukraine1   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 8
  • Joined: 20-October 14

Re: Importing User Input from TextBoxes into Local SQL Database - VB 2012

Posted 16 November 2014 - 12:42 AM

Updates on the code:

I have updated the code to work with my Data entry form which was Image 1 from my first post.
The code is coded inside the "Save Data" button:

 Private Sub btn_SaveData_Click(sender As Object, e As EventArgs) Handles btn_SaveData.Click
        Dim decWeight As Decimal
        Dim decMood As Decimal
        Dim decSleep As Decimal
        Dim decCalories As Decimal

        Decimal.TryParse(txt_Weight.Text, decWeight)
        Decimal.TryParse(txt_Mood.Text, decMood)
        Decimal.TryParse(txt_Sleep.Text, decSleep)
        Decimal.TryParse(txt_Calories.Text, decCalories)

        If String.IsNullOrWhiteSpace(txt_Weight.Text) Or IsNumeric(txt_Weight.Text) Then
            MsgBox("Please enter a numeric value only!")
            txt_Weight.Clear()
            txt_Weight.Focus()
        ElseIf String.IsNullOrWhiteSpace(txt_Mood.Text) Or IsNumeric(txt_Mood.Text) Then
            MsgBox("Please enter a numeric value only!")
            txt_Mood.Clear()
            txt_Mood.Focus()
        ElseIf String.IsNullOrWhiteSpace(txt_Calories.Text) Or IsNumeric(txt_Calories.Text) Then
            MsgBox("Please enter a numeric value only!")
            txt_Calories.Focus()
            txt_Calories.Focus()
        ElseIf String.IsNullOrWhiteSpace(txt_Sleep.Text) Or IsNumeric(txt_Sleep.Text) Then
            MsgBox("Please enter a numeric value only!")
            txt_Sleep.Focus()
            txt_Sleep.Focus()


        Else

            Dim myconnect As New SqlClient.SqlConnection
            myconnect.ConnectionString = "Data Source=(LocalDB)\v11.0;AttachDbFilename=C:\Users\User\Documents\Visual Studio 2012\ Projects\Project\Project\Real Project\Real Project\database\Database1.mdf;Integrated Security=True"

            Dim mycommand As SqlClient.SqlCommand = New SqlClient.SqlCommand()
            mycommand.Connection = myconnect
            mycommand.CommandText = "INSERT INTO UserData (Weight,Mood,Sleep, Calories) VALUES (@Weight,@Mood,@Sleep,@Calories)"
            myconnect.Open()


            Try
                'mycommand.Parameters.Add("@User Id", SqlDbType.NChar).Value = ComboBox1.Text
                mycommand.Parameters.Add("@Weight", SqlDbType.Decimal).Value = decWeight
                mycommand.Parameters.Add("@Mood", SqlDbType.Decimal).Value = decMood
                mycommand.Parameters.Add("@Sleep", SqlDbType.Decimal).Value = decSleep
                mycommand.Parameters.Add("@Calories", SqlDbType.Decimal).Value = decCalories

                mycommand.ExecuteNonQuery()
                MsgBox("Success")
            Catch ex As System.Data.SqlClient.SqlException
                MsgBox(ex.Message)
            End Try
            myconnect.Close()


        End If


--


Basically, once the user clicks "Save Data", and there are no blank text-boxes, the data is sent to the database. I have confirmed it works and all data gets updated inside my "dbo.UserData" database.



My question now: I need to code this in its own class. The class I have named is where the code snippet for sending information to the database will go, (where Weight, Mood, Sleep, Calories gets sent to). What I have done is copy this code into the new class I created, "UserSaveDataClass". The code doesn't work because its supposed to be a function since it is a class, that will be called when user clicks "Save Data", but can anyone help me make this code snippet into a function for this class so that I can use it in my Windows Form, instead of coding everything in "Save Data" button? Thanks!



Another question, was to figure out how to code the Calendar Module "Days", so that the user picks a certain day, say Monday the 4th, and this allows the user to write his information inside the text-boxes, and then click "Save Data"? For example, my program requires that the user enters 3 or more days of his Bench Press information for that week. Therefore, the user will select THREE (3) days minimum and for each day selected on the calendar, will enter his information in the text-boxes. After clicking "Save Data" button, the information, including the day, will be sent to the database. I already have gotten my Weight, Calories, Sleep, Mood, to work, but can anyone help me with adding code for the Calendar Module days?

This is what it looks like now:

Posted Image
Was This Post Helpful? 0
  • +
  • -

#4 thecoat   User is offline

  • D.I.C Addict

Reputation: 153
  • View blog
  • Posts: 537
  • Joined: 07-December 13

Re: Importing User Input from TextBoxes into Local SQL Database - VB 2012

Posted 16 November 2014 - 02:18 PM

View Postairukraine1, on 16 November 2014 - 12:42 AM, said:

My question now: I need to code this in its own class. The class I have named is where the code snippet for sending information to the database will go, (where Weight, Mood, Sleep, Calories gets sent to). What I have done is copy this code into the new class I created, "UserSaveDataClass". The code doesn't work because its supposed to be a function since it is a class, that will be called when user clicks "Save Data", but can anyone help me make this code snippet into a function for this class so that I can use it in my Windows Form, instead of coding everything in "Save Data" button? Thanks!



Creating a class that serves as a data structure with a save function at the base isn't all that complicated. You simply create properties for each of the data elements:

    Private _weight As Decimal
    Public Property Weight() As Decimal
        Get
            Return _weight
        End Get
        Set(ByVal value As Decimal)
            _weight = value
        End Set
    End Property


You can then create a save method in the class and instead of referencing textbox properties you can then use the properties of the object, pretty much using your existing save code:

mycommand.Parameters.Add("@Weight", SqlDbType.Decimal).Value = _weight


Your save button on your form would change to creating an instance of the class, setting the properties (possibly via a constructor that accepts them as parameters) and calling it's save method. The question is what do you gain, and what further objectives do you want to pursue with this class. Don't get me wrong, separating your business logic (data validation) into a separate layer (class) is a very good idea, but not for the sake of it's self, but for what you can then do with it. The first avenue you'd probably want to pursue is data binding where you bind properties of your class to the texbox input so you don't have to code explicitly to assign the values. However then the question becomes how you want to handle invalid input, for instance if you bind the above property "Weight" to a text box, the user can enter letters and the property expects decimals which results in a runtime error. You could change the property to text, and use validation on it's set method to make sure the incoming text can be converted to decimal and if not set an error state on the object or message the user etc. You could implement the IDataErrorInfo which helps with data binding etc. It all really depends a bit on what your goal with the class besides simply implementing it are.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1