Posts by Papa_Don

JX_Man,

I ran your code. It worked, but more importantly, it explained a lot. I'm going to use this.

The next question is: If I want to leave the date field blank, would that be a "NULL", or is there another entry that should be used?

Also, since this is entered into a textbox, is there a date conversion that is required to write this to the data table?

Thanks for the help!!

Don

Group,

I'm thinking I may want to use the Date data type in SQL Server. However I'm still very unclear how to read, write and compare using this kind of data type. I realize this has a time component which is of no importance right now (it will be later though). Can someone help me understand how to use this? Here are some of my question:

1) is there a way to create a "null" or blank date?
2) Visual Basic has the Today.Date commands. I'll need to compare a date created by the user to what will be in the date field of the data table (formated as a date data type). How is this done?
3) If I have the user enter the date manually, in what format does it need to be in? yyyymmdd?

Feel free to elaborate. I clearly don't understand enough about this kind of data type to really ask good questions.

Thanks,

Don

Deceptikon, the world is getting smaller!

Rev. Jim, thanks for the link. I'll peruse it today. Hopefully I'll find some new things. My latest thing is to take an existing part in my inventory, "clone" (as in copy) it with a new part number, then edit the description and any other needed fields to complete the creation of the new part.

I've also got to think about how to create valid inventory locations and then be able to copy parts into those valid locations..... I hope that make sense.

You guys are the best. Thanks again.

Don

Deceptikon,

I had no idea you lived so close by. I live in Marietta as well!

I gather this group is open to guys like me?

Don

Group,

After asking what seems to be a thousand questions, clearly there is so much more to learn about Visual Basic. So this prompts my "Today's Question":

Are there some good online courses or classes at a local college that I can take?

By the way..... I live in the north metro area of Atlanta.

I have taken 2 continuing education courses online through the local college here. Both were very good at introducing Visual Basic programming and gave me a reasonable understanding of how things work. What it didn't do is explain the "how to's" and "why's" of things such as what a user control is and what a module does, and how to write the "background" code that can be used in every form of the program.

To put my question another way, I want greatly to learn more. I've tried to look online for such courses or colleges. But I'm not sure what exactly to look for. Consequently, I'll appreciate greatly your input.

Thanks again for all ya'll do.

Don

Rev. Jim, good point..... I'm working fast and hard to catch up!! I'm such a rookie.... It won't take long!

Stuugie, I was able to get it fixed with no issues. However if you will share your code, I know I'll need it down the road... I've still got multiple tables to build and populate. I've got to believe it will come in handy.

Thanks group. I've gotten a long way as a result of your mentoring...... especially you, Jim.

Don

Rev. Jim.....

Outstanding.... it worked perfectly....

By the way... I did change the nchar to nvarchar for those fields that would vary in character number.

Now...... how did you get so smart!!

Thanks for your help again.

Don

Rev. Jim, I've tried to remove these spaces, but they won't go away. Is this normal? I'll ensure that the data type is nvarchar.

Thanks,

Don

Group,

In reviewing the data in one of my SQL Server tables, I noticed that my nchar or nvarchar fields have additional spaces added to the end of the record. Is there a way to stop this? Should I be using a different data type?

In advance, thanks for your responses.

Don

While creating a module within my program, I created a second Dataset (and it's called DesignDataSet2). I would like to delete it from the database altogether.

I have already removed the one datatable that was within this dataset. I now want to eliminate this completely. How do I go about doing this? I haven't seen a "delete" option when right-clicking that specific dataset.

Help!!

In advance, thanks for the help.

Don

Ok.... It seems I'm on the right track. Correct me if I'm wrong here, but it appears that I could do the following:

Private Sub textBox1_Validating(ByVal sender As Object, _
   ByVal e As System.ComponentModel.CancelEventArgs) Handles textBox1.Validating

   'check to see if the textbox has is correct or not

    If textbox1.Text.Length > 0 Then
        If IsNumeric(textbox.Text) = False
        MessageBox.Show("This field must be blank or a valid special price greater than 0...",
        "Entry Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
        textBox1.Select(0, textBox1.Text.Length)
    Else
        Dim txbx1 as Integer = Convert.ToInt32(texBox1.Text)
    End If
    If textbox1.Text.Length = 0 Then
        Dim txbx1 As String = "NULL"
    End If

Assuming this is correct, when would this event run? As soon as any change in the textbox happens?

The info says that I can't "textBox1.Focus" as it could shut the whole thing down. But does textBox1.Select(0, textBox1.Text.Length) essentially do the same thing? I hope so.

Thanks for the direction. I google this stuff and, for some reason, I never find the links you send.

Don

Group,

I see there are multiple "validation" type events to choose from in the "Declarations" drop-down box. I want to understand these choices better to see if one would work for what I want to do.

I've got several textboxes that need to have either a number entered into them or simply left blank. So I'd like to check during the time the user is inputing to see if what they have entered is correct, and if it's not, send them a message that they need to correct it. As an example:

If IsNumeric(txbSpecialPrice.Text) = False Or txbSpecialPrice.Text = "0" Then
            MessageBox.Show("This field must be blank or a valid special price greater than 0...", "Entry Error", MessageBoxButtons.OK, MessageBoxIcon.Exclamation)
            txbSpecialPrice.Focus()

So this prompted me to wonder if the event of "Validating" or something like it would be the best thing to use:

Private Sub txbCustNoType_Validating(sender As Object, e As System.ComponentModel.CancelEventArgs) Handles txbCustNoType.Validating

If not this, what would the better to check during the run-time? I recognize that the user may not always tab or enter through each field, he/she may opt to use the mouse and go directly a specific box they want to choose. Therefore I think it would be wise to ensure each textbox has the correct kind of information in it.

What are your thoughts on this? Remember, I'm a newbie programmer. If you comment, please explain!

Thanks for your help.

Don

Deceptikon,

I wasn't going to use paramerterized queries..... I thought it was just an "extra step". However I just learned there's a good reason to use them. I think I will.

Thanks for the explainations. It's becoming much clearer to me!

Don

OK. Now this makes sense. I see what you're doing here. If you will, explain what "SpecialCost" is, What a "SqlDbType.Int" is and what is the purpose of the zero in the parentheses. "SpecialCost" really confuses me as I don't know where this came from.

Thanks a bunch. This is begining to make some sense.

Don

deceptikon,

Sorry to appear to be hardheaded. I'm not grasping the meaning of using DBNull.Value. I'm new to coding, so this is rather foreign to me. Ultimately I need to finish this "If/Then" statement to ensure that, if "txbSpecialCost.Text" is empty, then the value of "spclcost" will also be empty. I then want to write the value of this variable to the database as either a numeric value or a null value - if that's possible.

If IsNumeric(txbSpecialCost.Text) = True Then
            Dim spclcost As Integer = Convert.ToInt32(txbSpecialCost.Text)
        Else
            ' I need to say spclcost = DBNull.Value.  But how do I do that?

Can you help me finish this?

Again, sorry to be a pest. I need to ensure that "spclcost" will be accepted by the database column that is assigned to accept a numeric value.

Don

Deceptikon,

Yes, I am writing to the database. But I do need to be prepared for 3 entries into the textbox that could hold the value to be written. In this instance, the database column is set to accept numerical data only, but it will accept "nothing". So I need to plan for that "(textbox1.text = "")" and write it to the datatable as "null", or plan for the errant alpha character that was entered in error and, lastly, a real numerical entry that will be written to the file. To do this I want to take the "Textbox1.Text" and write it as an integer as (in this case) to a variable called "spclcost". The value of "spclcost will be written to the file. That needs to be either a number or DBNull.Value.

So what is the correct syntax to assign DBNull.Value to the variable "spclcost"?

Thanks again for the help.

Don

Does "int32.TryParse()" bring back a True/False answer?

Regarding "DBNull.Value", do I write that as

If txbSpecialCost.Text = "" Then
            Dim spclcost As Integer = DBNull.Value

Thanks for your help.

Don

Group, I've got several textboxes within a form that are set to link to a data column that are set to accept a "null" (blank) value. However, these columns are formated to receive numeric data.

When the user bypasses entering anything within those textboxes (as they should do), what is the correct way to handle the reporting?

I've written it this way (I've not tested it yet) and am wondering if this is acceptable:

        If txbSpecialCost.Text = "" Then
            Dim spclcost As String = ""
        Else
            Dim spclcost As Integer = Convert.ToInt32(txbSpecialCost.Text)
        End If

Hmmm.... This prompts a second question: Is there a way to determine if the characters within "txbSpecialCost" are Alph or Numeric characters? I'm thinking I need to add a message box to redirect them if they enter anything other than a numeric value.

Thanks for helping me with these two questions.

Don

I hate to admit this, but the problem I've had all along with with the part number within the database. It seems when I pushed the part number up, it went up with a bunch of spaces at the end. I did make a change to the code on two ends and got it to run perfectly. At the bottom end, I changed the code there to read:

Dim rdr As SqlDataReader = cmd.ExecuteReader
        Do While rdr.Read()
            txbDesc.Text = rdr(1)
            txbUM.Text = rdr(2)
            txbPrice.Text = rdr(3)
        Loop

When running it step by step, I decided to see if some of the problem may have been with the part number itself. So I tried this at the top:

Dim search1 As String = "'" & string1 & "%" & "'"

Putting that wildcard at the end made all the difference. It runs like a champ.

Rev. Jim, I'm no longer testing this in Windows Authentication mode. I have gone in and established a password for 'sa'. I'm now running this in SQL Server Authentication mode.

Thanks again for everyone's help.

Don

I got it fixed. It had everything to do with

Dim search1 As String = "'%" & string1 & "%'"

I wasn't putting the single quote (') properly.

Thanks for everyones help!!

Don

Group,

I'm stuggling to get values from an sql database table into several textboxes. I'm not getting an error, but I'm also not getting anything to show up in these textboxes. Can you offer some thoughts as to what I need to do to fix this?

Private Sub btnUpdateOrder_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnUpdateOrder.Click

        Dim con As New SqlConnection
        Dim cmd As New SqlCommand
        Dim string1 As String = txbPartNo.Text.Trim
        Dim search1 As String = "'" & string1 & "'"

        Try
            con.ConnectionString = ("Data Source=DON-PC;Initial Catalog=DataDesignSolutions;Integrated Security = True;User ID=DON-PC;Password=be67011;")
            cmd.Connection = con
            cmd.CommandText = "SELECT [INV-DESCRIPTION], [INV-UNIT-MEASURE], [INV-SELL-PRICE-1] FROM INVENTORY WHERE [INV-PART-NUMBER] LIKE " & search1
            con.Open()
            Dim rdr As SqlDataReader = cmd.ExecuteReader
            While rdr.Read()
                txbDesc.Text = Convert.ToString(rdr("[INV-DESCRIPTION]"))
                txbUM.Text = Convert.ToString(rdr("[INV-UNIT-MEASURE]"))
                txbPrice.Text = Convert.ToString(rdr("INV-SELL-PRICE-1"))
                rdr.Close()
            End While
        Catch ex As Exception
            MessageBox.Show("Error while retrieving records on table..." & ex.Message, "Load Records")
        Finally
            con.Close()
        End Try

    End Sub

Thanks for your assistance,

Don

Group,

I found my error. It was in how I was combining my wildcard with the inputs from the textbox. I've been writing

Dim search1 As String = string1 & "%"

However I have learned that SQL reads different from VB.net. So I needed it to write it this way:

Dim search1 As String = "'" & string1 & "%" & "'"

That has been a hard lesson!

Rev. Jim, thanks for the tutorial and the addition information. Scudzilla, thanks for your input as well. This has been a great learning experience.

Don

So if I'm understanding you correctly, I can write the command line as:

cmd.CommandText = "SELECT [INV-PART-NUMBER], [INV-DESCRIPTION] FROM INVENTORY where [INV-PART-NUMBER] like @search"

(is the syntax correct - like @search"? Or should it be like" @search?

and later:

cmd.Parameters.AddWithValue("@search", search1)

Again, I need for that command line to recognize that I'm using the wildcard.

Don

Then I have to ask - where is (or was) "@lname" and "@phone" defined? Is it something that is done via a Dim Statement? i.e.

    Dim @lname As String = txbLastName.Text

Or is it done with

    cmd.Parameters.AddWithValue("@lastName", txtLastName.Text)

In what I'm doing, I (assume) that I want to do the same.... or do I? My current search statement says:

        Dim string1 As String = frmOrderEntry2.txbPartNo.Text
        Dim search1 As String = string1 & "%"
        cmd.CommandText = "SELECT [INV-PART-NUMBER], [INV-DESCRIPTION] FROM INVENTORY where [INV-PART-NUMBER] like " & search1

Here, I'm using a wildcard as I want the user to be able to type in the first characters of a part number such that listview will report any part number that begins with those characters. In my test case, I'm typing "AO". I want to search the part number database for parts that begin with "AO" (AO%) and return any that it finds. In my test database, there are two examples that should be reported.

So, to further clarify the questions, should I be using the following in this manner:

        cmd.Parameters.AddWithValue("@search", search1)

I hope I'm making sense!!

Don

Group,

I've read through the tutorial regarding Listviews and SQL's (http://www.daniweb.com/software-development/vbnet/code/445801/avoiding-sql-injection-attacks). Thanks for the article. It was very imformative.

So I thought I might try this with the project I'm doing. I really like how these listview look and what they can do. But I realize I need to know more and understand it better. So.... my first question:

The code in the example reads:

cmd.CommandText = "SELECT au_lname,au_fname,phone " _
                        & "  FROM authors                 " _
                        & " WHERE au_lname like @lastName " _
                        & "   AND phone    like @phone    " _
                        & " ORDER BY au_lname             "

It appears to me that what you are doing here is two things: 1) you are creating your search criteria and, 2) you are defining to "au_last" to named "@lastName" and the "phone (number) to be named "@phone". I am also presuming you want the list to order it alphabetically by last name. Can you confirm that my assumptions are true? If not, I need more clarification.

Now the second question: What does these lines of code do?

        cmd.Parameters.AddWithValue("@lastName", txtLastName.Text)
        cmd.Parameters.AddWithValue("@phone   ", txtPhone.Text)

Some of you may have responded to some questions about populating data within a DataGridView. Having now seen how the listview looks, I really like it much better..... and it seems you can do more with it.

I look forward to the answers. I'd like to try to use these snippets as code within the project I'm working.

Thanks for all …

Mr. Waddell,

I don't follow you.

your databinding is wrong, the dataset will contain a table with the data retrieved from the query run in your data adapter

I've added the binding as you suggested (albeit since my last post, I've started all over building the DataGridView from scratch. So some names have changed). So my latest iteration looks like this:

Private Sub frmPopup_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load

        Me.INVENTORYTableAdapter.Fill(Me.DataDesignSolutionsDataSet2.INVENTORY)

        Dim string1 As String = frmOrderEntry2.txbPartNo.Text
        Dim string2 As String = string1 & "*"
        txbString1.Text = string2
        Dim search1 As String = string1 & "%"
        Dim sqlquery As String
        Dim con As New OleDb.OleDbConnection
        con.ConnectionString = "Data Source=Don-PC;Initial Catalog=DataDesignSolutions;Integrated Security = True;User ID=DON-PC;Password=be67011"
        sqlquery = "SELECT [INV-PART-NUMBER], [INV-DESCRIPTION] FROM [INVENTORY] where [INV-PART-NUMBER] like " & search1
        con.Open()
        Dim ds As DataSet = New DataSet
        ds.Clear()
        Dim adapter As New OleDb.OleDbDataAdapter
        adapter.SelectCommand = New OleDb.OleDbCommand(sqlquery, con)
        dgvInventory.DataSource = ds.Tables(0)
        dgvInventory.Refresh()

    End Sub

Now the DataGridView is BLANK. Nothing comes back.... With and without the last statement. Is it possible the statement "dgvInventory.DataSource = ds.Tables(0)" is in the wrong place?

And I thought Visual Basic was going to be easy!! LOL!!

Thanks for helping!

Don

Ravi,

I'm trying to follow your recommendation. I've written it as follows:

dgvInventory.BindingSource1.DataSource = "INVENTORY"

dgvInventory is the name of my DataGridView table
INVENTORY is the name of the database table

If this is what you meant for me to do, it doesn't work. I'm getting errors as soon as I write the code.

Since my last posting, I've gone back and started all over and added some important controls. This includes the table adapter, the table adapter manager, etc. I'm now getting results to come back with the following code:

    Private Sub frmPopup_Load(sender As System.Object, e As System.EventArgs) Handles MyBase.Load

        Me.INVENTORYTableAdapter.Fill(Me.DataDesignSolutionsDataSet2.INVENTORY)

        Dim string1 As String = frmOrderEntry2.txbPartNo.Text
        Dim string2 As String = string1 & "*"
        txbString1.Text = string2
        Dim search1 As String = string1 & "%"

        Dim con As New OleDb.OleDbConnection
        con.ConnectionString = "Data Source=Don-PC;Initial Catalog=DataDesignSolutions;Integrated Security = True;User ID=DON-PC;Password=be67011"
        Dim sqlquery = "SELECT [INV-PART-NUMBER], [INV-DESCRIPTION] FROM [INVENTORY] where [INV-PART-NUMBER] like " & search1
        con.Open()
        Dim ds As DataSet = New DataSet
        ds.Clear()
        Dim adapter As New OleDb.OleDbDataAdapter
        adapter.SelectCommand = New OleDb.OleDbCommand(sqlquery, con)

    End Sub

Unfortunately, it is bringing every item that I have in my test database. My intent is to have the table list the items that begin with "AO". My database table only contains two of those item types.

Thoughts?

Thanks again for your help.

Don

Ravi,

I don't know how to "bind" the DataGridView. Is that done programatically?

Ravi,

I've made some changes to the code and it now looks like this:

        Dim string1 As String = frmOrderEntry2.txbPartNo.Text
        Dim string2 As String = string1 & "*"
        txbString1.Text = string2
        Dim search1 As String = string1 & "%"

        Dim con As New OleDb.OleDbConnection
        con.ConnectionString = "Data Source=Don-PC;Initial Catalog=DataDesignSolutions;Integrated Security = True;User ID=DON-PC;Password=be67011"
        con.Open()
        Dim ds As DataSet = New DataSet
        Dim adapter As New OleDb.OleDbDataAdapter
        Dim sql As String

        sql = "SELECT [INV-PART-NUMBER], [INV-DESCRIPTION] FROM INVENTORY where [INV-PART-NUMBER] like " & search1
        adapter.SelectCommand = New OleDb.OleDbCommand(sql, con)
        adapter.Fill(ds)
        dgvPartDescription.DataSource = ds.Tables(0)

So you understand what I'm doing (or trying to do):

a) "string1" retrieves the users entry from a form called "frmOrderEntry2" which was entered in the the "txbPartNo" textbox.

b) "string2" adds the asterisk to the end of the users entry for display purposes only and is displayed in the textbox called "txbString1".

c) "search1" adds the percent sign so that it can be read as a "begin as" for the search within the database.

d) "dgvPartDescription" is the name of the DataGridView control within the form.

Please review the above code and see what I'm missing. I'm still getting nothing in the grid.

Thanks again for your help.

Don

Group,

I've got part of my code started to populate the DataGridView with the data I need to return. When testing it, I have this long pause and no returned data. I finally have to kill it via <Control><Alt><Delete>. I've tried reading through mulitple websites to find some information to connect and fill the table, but I don't think I'm understanding it correctly. Hopefully you can help.

What I ultimately want to to:

1) Search the database for part numbers that begin with "AO" (my test database has two examples in it)
2) Return these two examples to the grid.

        Dim connstr = "Data Source=Don-PC;Provider=SQLOLEDB;Initial Catalog=DataDesignSolutions;Integrated Security = True;User ID=DON-PC;Password=be67011"
        Dim search1 As String = "'%" & string1 & "%'"
        Dim sqlquery = "SELECT [INV-PART-NUMBER], [INV-DESCRIPTION] FROM INVENTORY where [INV-PART-NUMBER] like " & search1
        Dim connection As SqlConnection = New SqlConnection(connstr)
        connection.Open()
        Dim command As SqlCommand = connection.CreateCommand()
        command.CommandText = sqlquery
        Dim reader As SqlDataReader = command.ExecuteReader()
        reader.Read()
        Me.DataDesignSolutionsDataSet1BindingSource.DataSource = DataDesignSolutionsDataSet1
        connection.Close()

Hopefully you'll see what I'm missing (or have wrong) and help me fix it.

I remind you, I'm a newbie programmer. This is my first attempt to using the DataGridView.

Thanks,

Don

After MORE searching, I found the answer. Through SQL Server Management 2008, I changed it using the following code:

ALTER LOGIN [sa] WITH PASSWORD = ‘be67011’
GO
ALTER LOGIN [sa] WITH
CHECK_POLICY = OFF,
CHECK_EXPIRATION = OFF;

Hopefully, others will find this useful.

Don

Rev Jim,

I do want to change the password of the 'sa' login for the SQL Server Authentication. The database was installed using the Windows Authentication. However I want to change that. I've tried everything that I've read to this point, including trying to change the "Mapped Credentials" and changing the default database to "DATADESIGNSOLUTIONS". No luck. So clearly I'm missing something to change and save the password.

I'm at a loss. I appreciate any help you can give.

Thanks,

Don

Rev Jim, I've fixed the issue regarding the name and location of the database. Unfortunately I'm having trouble establishing a sql database password in the user 'sa'. I've sent a new question out regarding it. If you can help, please do.

Don

Group,

I'm trying to change the "sa" password to my SQL Server Database via the 2008 SQL Server Manager Express. I have followed every instruction dillegently and can't seem to "complete" the change. Each time when trying to login with the SQL Server Authentication, I get the error message that it has failed. Clearly I am not saving the new password successfully.

Hopefully some of you have had a similar problem and have figured out how to overcome it. I certainly will appreciate any help you can offer.

Thanks for your assistance.

Don

Hello Group,

I'm beginning to understand the connections required to return information from your database. I'm now trying to return multiple lines from the database that have the same "prefix" within the part number. As my example, I have two records within my database that both start with "AO0025". I need to return both examples to two combo boxes. Here is what my code looks like now:

        Dim connstr = "Data Source=.;AttachDbFilename=C:\Users\Don\Documents\DataDesignSolutions.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
        Dim search1 As String = "'%" & txbxCustomerName.Text & "%'"
        Dim sqlquery = "SELECT [ARC-CUSTOMER-NAME], [ARC-CUSTOMER-NUMBER] FROM [AR_CUSTOMERS] where [ARC-CUSTOMER-NAME] like " & search1
        Dim connection As SqlConnection = New SqlConnection(connstr)
        connection.Open()

        Dim command As SqlCommand = connection.CreateCommand()
        command.CommandText = sqlquery
        Dim reader As SqlDataReader = command.ExecuteReader()
        reader.Read()
        frmPopup.Show()
        frmPopup.Controls.Add(cbxPartNumber)
        frmPopup.Controls.Add(txbPartDesc)
        cbxPartNumber.Visible = True
        txbPartDesc.Visible = True
        cbxPartNumber.Text = reader.GetString(0)
        txbPartDesc.Text = reader.GetString(1)
        connection.Close()

Currently I've made provisions for 1 combobox and 1 textbox to list the partnumber and the part description. I know I'll need to add another combobox and textbox to show the second record. But how do I return both records?

In advance, thanks for your help!

Don

Lola,

You are my hero!! This worked perfectly!!

Thanks!

I may have found the error. I have made the following correction:

                Dim search1 As String = "'%" & txbPartNo.Text & "%'"
                Dim sqlquery = "SELECT INV-PART-NUMBER, INV-DESCRIPTION FROM INVENTORY where INV-PART-NUMBER like " & search1

The latest error I'm getting says,
"Invalid Column Name 'INV'
Invalid Column Name 'PART'
Invalid Column Name 'NUMBER'
Invalid Column Name 'INV'
Invalid Column Name 'PART'
Invalid Column Name 'NUMBER' "

My assumption is that it doesn't like the hyphen which is part of the table column. Or is it something else? I'm incredibly confused. HELP!!

Thanks,

Don

I'm trying to read and return info from an SQL database. To ensure you have the details, here's the basic info:

The database is called "DataDesignSolution"
The table is called "INVENTORY"

I'm trying to read the columns "INV-PART-NUMBER" and "INV-DESCRIPTION".

I've written the following code hoping to SEARCH through the "INV-PART-NUMBER" column for a part that begins with "AO", return that value to a checkbox called "cbxPartNo" along with the description information from the same row and from the column "INV-DESCRIPTION:

                Dim connstr = "Data Source=.;AttachDbFilename=C:\Users\Don\Documents\DataDesignSolutions.mdf;Integrated Security=True;Connect Timeout=30;User Instance=True"
                Dim sqlquery = "SELECT INV-PART-NUMBER, INV-DESCRIPTION FROM INVENTORY where INV-PART-NUMBER like'%" & Me.txbPartNo.Text & "%'"
                Dim connection As SqlConnection = New SqlConnection(connstr)
                connection.Open()

                Dim command As SqlCommand = connection.CreateCommand()
                command.CommandText = sqlquery
                Dim reader As SqlDataReader = command.ExecuteReader()
                reader.Read()
                Console.WriteLine(reader.GetString(0), reader.GetString(1))
                connection.Close()
                Console.Read()

                cbxPartNo.Text = reader.GetString(0)
                txbDesc.Text = reader.GetString(1)

I'm getting an error that says "Incorrect Syntax near 'AO'" ("AO" is the first characters of the part number I'm trying to return. I know there is one part number in the table that beings with these characters). I'm not sure what is causing this. Does anyone have any thoughts as to why I'm getting an error. Is my connection string wrong in some manner? If it is a syntax error, what is it?

In advance, thanks for your help.

Don

Adam, I haven't got that far in my thought process. However, the company has always used a secure private network to handle the data communications. Thus the reason I haven't given a lot of thought to it. With that said, there will be logins and passwords that will confirm access to the database by the program.

With respect to bin-locations, you are correct. Given the amount of total inventory the company has, they could have more than 4 bin-locations. Again, it's making more sense to me to put bin-location as a column on a table so that it won't be limited. It could sit along side the lot/shade/serial numbers.

Keep your thoughts and ideas flowing. I really want this to help the company.

Don

Adam,

For what it's worth, those 30 MISC fields are meant to be user defined and are HIGHLY unlikely to be used as a search involving quantities sold. It is meant to store data such as "color", "size", "Series Name", etc. The company uses these fields to aid in a online price lookup for their customers. As it is used today, the customer can seach by an individual part number (if they know it). If they don't, they have a series of dropdown boxes that starts with "Series Name". After they choose a series name, another dropdown box lists sizes and another allows a choice of color. After making their choices, the product number, the description, packaging info, pricing, weight, etc., comes back in the form of a tables.

If I understand, you're suggesting that I break this one table into three. The first table would include "static" info like part number, description, warehouse location, pricing, weight, etc. A second table would include the part number and/or the Item ID (as a link to the first table) and those fields that would change as the daily, monthly and yearly updates are done such as number of units sold, the value of those units sold, etc.

You mention the third being for sales statistics. Something I haven't mention that I've been pondering has to do with Lot/Serial/Shade. I come from a background in the ceramic tile and stone industry. One of the continuing problems in that realm has to do with …

To clarify some things, by "Location" I did mean "Warehouse Location" which is expressed as a number. I did play a bit more and learned I could duplicate part numbers if I assigned an ID_Key that could also equate to the warehouse location. As part of the item record (or part number), "Bin-Location"(s) are also included such that the item can be assigned to a specific spot within the warehouse.

The database Schema is as follows:

Name Policy Health State
INVENTORY_KEY (PK, nchar(32), not null)
INV-PART-NUMBER (nchar(32), not null)
INV-LOCATION (numeric(3,0), not null)
INV-DIVISION (numeric(3,0), not null)
INV-VENDOR-PART-NUMBER (nchar(32), not null)
INV-DESCRIPTION (nvarchar(100), not null)
INV-VENDOR-NUMBER (numeric(10,0), null)
INV-ALT-VENDOR-NUMBER (numeric(10,0), null)
INV-CATEGORY-CLASS (nchar(4), not null)
INV-PRICE-CAT-CLASS (nchar(4), not null)
INV-SALES-CAT-CLASS (nchar(4), not null)
INV-MISC-CAT-CLASS (nchar(4), null)
INV-CATEGORY (nchar(2), not null)
INV-CLASS (nchar(2), not null)
INV-CLASSIFICATION (nchar(1), not null)
INV-DEPOSIT-REQUIREMENT-FLAG (nchar(1), not null)
INV-DEPOSIT-PCNT-REQUIREMENT (numeric(4,1), null)
INV-COMMISSION-PCNT (numeric(4,1), null)
INV-UNIT-MEASURE (nchar(4), not null)
INV-UM-BREAK-1-NAME (nchar(4), null)
INV-UM-BREAK-1-QTY (numeric(14,4), null)
INV-UM-BREAK-2-NAME (nchar(4), null)
INV-UM-BREAK-2-QTY (numeric(14,4), null)
INV-UM-BREAK-3-NAME (nchar(4), null)
INV-UM-BREAK-3-QTY (numeric(14,4), null)
INV-UM-BREAK-4-NAME (nchar(4), null)
INV-UM-BREAK-4-QTY (numeric(14,4), null)
INV-UM-BREAK-5-NAME (nchar(4), null)
INV-UM-BREAK-5-QTY (numeric(14,4), null)
INV-WEIGHT (numeric(14,4), null)
INV-WEIGHT-NAME (nchar(4), null)
INV-CURRENT-SELL-PRICE-DATE (numeric(8,0), null)
INV-SELL-PRICE-1 (numeric(12,2), null)
INV-SELL-PRICE-2 (numeric(12,2), null)
INV-SELL-PRICE-3 (numeric(12,2), null)
INV-SELL-PRICE-4 (numeric(12,2), null)
INV-SELL-PRICE-5 (numeric(12,2), null)
INV-DATE-SETUP (numeric(8,0), null)
INV-DELETE-DATE (numeric(8,0), …

Group,

I'm discovering that the SQL Server tables don't like identical (in this case) Part Numbers, even though one of the columns will have different information in it (in this instance, it is the "Location"). FYI.... I'm using SQL Server 2008 Express for my testing.

With this said, I'm trying to develope a inventory database table to hold the part number, description and other pertinent information for a company that has multiple warehouse locations. For obvious reasons, every location will have the same items available, which means duplicate part numbers.

Here are my questions:

1) Does this mean I need to create a different table for every location (this doesn't seem wise as I don't know how many location will exist as the company grows in the future)?

2) Can I create a data table to "define" the warehouses, the ID numbers, etc.?

3) if so, where do I store the dynamic (changing) data for each part number/location for things like selling price, number of units sold, total value sold, etc.?

4) Must I break apart the existing INVENTORY data table and create "sub-tables" the would house the dynamic (potentially changing) data?

5) or is the simple "fix" to this setting the Primary Key on the "Location" column of the INVENTORY data table?

I apologize for asking what is probably a very simple question. I'm new to programming and I'm even newer to the world of datatables. Therefore I appreciate your insight, knowledge and opinions.

Don

As state previously, I'm a newbie at coding in VB and have much to learn. I'll appreciate your input and knowledge!

I'm creating a Order Entry form. I have the database in place with some "test" items in the in the table. I now want to begin creating a new order using this data. Here's what I'd like to do:

I'd like to either

1) to enter a portion of the part number into the PrtNumb textbox, either click or F5, and have a "popup" box (another form?) come up that has any part number that has the matching characters in it that was typed onto the part number textbox. Then I'd like to be able to drag it from this "popup" box into part number textbox on the order entry form. As an example, I know there are multiple part numbers that begin with "AO0025" (there are probably 25 items). In the "PrtNumb" textbox, I want to type in "AO0025" and the click a button or hit the F5 key. Then a popup box will appear with the 25 items that have "AO0025" as the prefix of the whole item number. I'd then like to drag one of those 25 items into the "PrtNumb" textbox on the Order Entry form and have it populate those fields on the Order Entry form that includes "Unit of Measure", "Price", etc. Then I'll I (or the user) will need to do is to enter the quantity that will be required.

2) to …

Call off the dogs..... I finally figured this one out on my own. I should have been using the "SQL Server Native Client 10" all along.

Gusano, Thanks for the links and opinion. After reading, I have to agree. At this point, I don't see much use for it.

I'm trying to use the Import/Export Wizard to import data from an Excel File to the SQL Database.

First, know that I'm trying to run it as an "Administrator". I've had issues because it wouldn't let me log in. So I'm trying it this way.

I'm following the steps as follows:

Choose a Data Source: I'm chosing the Excel file located on my desktop.
Choose a Destination: I chose ".Net Framework Data Provider for SQLServer (checked the properties of the file and it confirmed it was a .Net Framework).

At this point a box appears with multiple options (Advance, Context, Initialization, etc.). It appears that much of this is filled out for you. I would assume the important stuff for me to do is the options for Source.

I chose:

Attach Db Filename: This directs you to the specific database you need to upload to.
Context Connection: False
Data Source: In my case, I only had one place to choose: DON-PC
Failover Source: Same as above
Initial Catalog: I'm unsure as to what to enter here. My assumption would be the name I've given to the database (I've called it "DataSolution"). So I've entered that in.
MultiSubnetFailover: False
User Instance: False

I'm getting a "Login Failed for User. (Microsoft SQL Server Error, 18456)"

I've gotten this error every time regarless of how I was logged on. I've looked up the error message on the internet and see varying responses to the issue. Most …

Group,

In the classes on VB I took were some training on creating Parent and Child Forms. What never was clearly explained is why we would need to use such related forms.

I ask this as I'm now working on a group of forms that will be the "order entry" module. There are three parts: The 1st screen allows the user to choose the customer to bill, define if it's a new order or existing and to decide if it's a cash sale, on-account sale or a credit memo. The second part allows the user to key in the part numbers and enter the quantities. The last one closes the sale and allows monies to be posted if it is a cash sale. Because of the three "related" forms, I began to wonder if this would be an instance where I should have a Parent form and it's two Children.

So, the primary questions are: What is the benefits of using such a related form? Are there negatives to using this? What situations would best warrant the use of a Parent/Child Form?

In advance, thanks for you input. I look forward to your responces.

Don

Jorge, Thanks. That was the step I was missing. I wasn't right clicking "Databases" and attaching. Duh!! My only excuse is..... my dog ate my homework!

Thanks again.

Don

Here we go again. I've installed SQL Server 2008. I can't seem to find my database I've created (and there are two tables so far) that were created through Visual Studio 2010 Express (I'm coding in VB). If it helps, I've determined that the database is a ".Net Framework Data Provider for SQLServer" (this according to the properties of the file). I know where the file is: "C:\Users\Don\MyDocuments\BusinessData.mdf". When loging in to the server, it seems to have all the answers correct: Server Type: Database Engine, Server Name: Don-PC\Don (this one I can't change. It is done for me). When I connect, the Object Explorer shows "Don-PC(SQL Server 10.0.5500 - Don-PC\Don). I've right clicked the server and clicked the "Properties". I've typed in the above path and that of the Log file.

Am I doing something wrong?

Thanks group,

Don

Rev. Jim,

Forgive me for not following. I previously worked with a UNIX based system and wrote reports to pull detail from the databases. The system had the the ability to drop the output into an EXCEL spreadsheet. In that, I would pull the CustomerNo, CustomerName, OrderNo, LineNo, PartNo, Description, QuantitySold, Price, Cost, etc. When it displayed the output via EXCEL, every cell had data in it. In a multi-line order, CustomerNo, CustomerName, OrderNo were each filled and were identical. The difference in these multi-lines were the LineNo, PartNo, Description, QuantitySold, etc. Because of the way "DataGridView" in SQL displayed (it looks like a spreadsheet), my assumption was that the data was saved the same way in SQL.

With this said though, I have designed my database (with Open and Closed Orders) as a database of just "Header" information - that is the OrderNo, CustomerNo, CustomerName, etc. There is a separate database for the "Line Information" with LineNo, PartNo, Description, QuanityOrdered (or Shipped), Price, etc. The link between the two would be the OrderNo.

It seems to me what you are saying is that I can save the data once for "header" info and save the Line information with each order line. Am I getting that correct?

I really wish the instructor had spent more time with databases, setting them up, writting to them, etc. It sure would have made this project much easier.

Jim, thanks again for your help.

Don