Linking the SQL Server to a form and populating the Textboxes with data

[Papa_Don]

Hello Group!

I've created a fairly large database with approximately 170 columns. I now want to begin writing data to that database table and saving the data. I'm using a form I'm calling "frmInventoryMasterSetup". I've also created the individual textboxes that I want to link to the columns for saving. I now want to type data into the individual textboxes and have that information stored in the database.

To make your answer simple, lets use 3 of my fields. The sql database is called "DDdatabase.mdf". The table is called "InventoryMaster". I'm listing the textbox name and the database field name that I want to link together:

txbInvLocation connects to IN-LOCATION
txbInvPartNumber connects to IN-PART-NUMBER
txbInvDescription connects to IN-DESCRIPTION

What steps and code will I need to write to allow me to connect and write to this table?

I know some of you will want to ask why I didn't use "datagridview". I do want to use this form in some other ways and needed to format it this way to do it.

In advance, thanks for your assistance!

Don

[TnTinMN]

Don,

Do all of those 170 columns define a single record? If so, I suspect based on your past references to excel that you have a lot of redundant data in there. Since you are defining the database, it behooves you to ensure that it is well designed before you start coding any application to interface with it. I suggest that you take a look at these articles.

http://en.wikipedia.org/wiki/Database_design

http://en.wikipedia.org/wiki/Database_normalization

[Papa_Don]

TnTinMn,

I have thought this out very well! Every database field is important. I should share that some of these column heading will be used by other programs within the system to store information such as total sales dollars sold, total units sold, etc. However when manually entering a part number, there will be a lot of like data that will be stored in which I will enter a default value into the field at code level.

To further answer your question, one part number will have 170 "cells" of information within the one "row". Some of this will be blank at the time of creation because it will be filled later by other forms/programs/code.

I have written the following code. But I'm getting a "Object reference not set to an instance of an object" error. I've no idea what this means as I'm not getting any kind of error message at the time of the build.

Imports System.Data.SqlClient
Imports System.Configuration

Public Class frmInventoryMasterSetup

    Private Sub frmInventoryMasterSetup_Load(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles MyBase.Load
        Dim tdate As String = Today.Date
        txbInvMasterTodaysDate.Text = tdate

    End Sub

    Private Sub btnInvMasterSave_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnInvMasterSave.Click
        Dim loc1 As Integer
        loc1 = Integer.Parse(txbInvLocation.Text)
        Dim year1 As String = Today.Year
        Dim month1 As String = Today.Month
        Dim day1 As String = Today.Day
        Dim yyyymmdd As String = year1 & month1 & day1
        Dim indate As Integer = Integer.Parse(yyyymmdd)
        Dim objcon As SqlConnection
        Dim objcmd As SqlCommand

        Try

            objcmd = New SqlClient.SqlCommand("insert into InventoryMaster(IN-LOCATION,IN-PART-NUMBER,IN-DESCRIPTION)values('" & loc1 & "','" & txbInvPartNumber.Text & "','" & txbInvDescription.Text & "')", objcon)
            objcon.Open()
            Dim i As Integer = objcmd.ExecuteNonQuery()
            If (i > 0) Then
                MsgBox("Inventory saved successfully!")
            Else
                MsgBox("Failed to  save Inventory!")
            End If
            objcon.Close()
        Catch ex As Exception
            MsgBox(ex.Message)
        End Try
    End Sub
End Class

For the purpose of my testing, the other fields will remain blank for the time being.

Any thoughts?

Thanks for the help.

Don

[Reverend Jim]

Just out of morbid curiousity, what 170 pieces of information could you possibly have that could not be broken down into sub-tables? I strongly recommend you reconsider your database design. Your queries are going to be horrendously difficult to code and maintain. It takes a lot less effort to change a database at design time than after it goes into service. And it this is for an assignment I can guarantee a poor grade.

[TnTinMN]

Don,

When I stated redundant information, I did not mean within a given row.

Since it appears that you want to create some type inventory system, I would assume that you wish to store the supplier information of a given part somewhere. This is an example of what Jim mentioned could be stored in a sub-table and then your record would just store a vendor ID code instead of all vendor info in each record. But this is all a guess at this point.

I find pictures to be a lot easier to understand. Take look at this site for example database layouts.

http://www.databaseanswers.org/data_models/

As far as your current code problem, I see that you are using objCon before you have created an instance of the connection. Thus far you you only defined the variable.

I suggest you place these statements at the top of your code, they will allow the IDE to flag more potential errors for you.

Option Strict On
Option Infer Off

[Papa_Don]

Rev Jim, TnTinMN

In the few minutes that I've take to look at your links, you make some valid points. I'll have to rethink my database design to ensure it's simple enough to do some reporting with. You should know that I worked a lot with a Unix driven database. My design is somewhat similar to it. However accessing the databases (and there were probably 30 or so different ones) for the purposes of reporting was actually very easy. And there were multiple ways of displaying the data. I generated hard paper reports for the owner whereas I prefered EXCEL based reports that I could put on my desktop. I was hoping this sql server would allow similar reporting options.

Thanks again for your input gentlemen!

Don