VB and SQL Help!!!! Trying to access a SQL Database through OLE DB connection!!!


Using the below code I'm trying to access a SQL database...I'm a newbie but I also have some coding experience...
This code was provided to me as a learning tool and to expand from....
This allows the user to type in a 3 letter combination that stands for a customer and a state that they live in.  For ex.  if the customer does not exist in our system then we add a 3 letter indentifier to them like:  Green Turtle Bay is a customer so we give them a "GTB".....  well they are in the state of Kentucky so we give them a "KY"

...so there customer code is "GTB.KY"

I need to be able to store that identifier and give people the ability to look to see if it exists in the database...If so then the user can continue with the job, if it doesn't then they have to add it to the system..


I have everything working as far as SQL goes....I'm using MSDE and a database called fdmdev with one table called tblcodes  when I run the program I get a error here:

  oConn.Execute ("INSERT INTO tblCodes VALUES ('" & sCode & "')")


If somebody would assist me in trying to understand this that would be great. The goal of this is to create a job tracking program for my boss the associates the 5 digit code mentioned earlier to a specific job number or quote number.


Thank you much






Option Explicit

Private WithEvents m_oCmd As CommandButton



Private Sub Form_Load()

    PopulateForm

End Sub

Private Sub PopulateForm()

    Dim oCbo As ComboBox
    Dim oTxt As TextBox
    Dim oLbl As Label
   
    '// State combobox - contains three states as a sample
    Set oCbo = Me.Controls.Add("VB.ComboBox", "cboState")
    oCbo.Move 550, 100, 700
    oCbo.AddItem "AZ"
    oCbo.AddItem "CA"
    oCbo.AddItem "KY"
    oCbo.ListIndex = 0
    oCbo.Visible = True

    '// Label for State combobox
    Set oLbl = Me.Controls.Add("VB.Label", "lblState")
    oLbl.Move 100, 140, 500
    oLbl.Caption = "State"
    oLbl.Visible = True

    '// Code textbox - for entry of three digit code
    Set oTxt = Me.Controls.Add("VB.Textbox", "txtCode")
    oTxt.Move 2000, 100, 750, 300
    oTxt.Visible = True

    '// Label for Code textbox
    Set oLbl = Me.Controls.Add("VB.Label", "lblCode")
    oLbl.Move 1500, 140, 550
    oLbl.Caption = "Code"
    oLbl.Visible = True
   
    '// Command button
    Set m_oCmd = Me.Controls.Add("VB.CommandButton", "btnCheck")
    m_oCmd.Move 500, 500
    m_oCmd.Caption = "Check Entry"
    m_oCmd.Visible = True

End Sub

Private Sub m_oCmd_Click()

    '// For this sample I'll assume that you have a SQL Server on the local machine with 'sa' account
    '// with no password specified. I'll also assume that you have a table in the 'master' database
    '// called tblCodes, with a single varchar column called "Code".

    Dim oConn As New ADODB.Connection
    Dim oRS As ADODB.Recordset
    Dim sCode As String

    '// First a little bit of validation on the values the user entered
    If Me.Controls("cboState").Text = "" Then
        MsgBox "Please select a state"
        Exit Sub
    End If

    If Len(Me.Controls("txtCode")) <> 3 Then
        MsgBox "Code must be three characters in length"
        Exit Sub
    End If

    '// We passed validation.
   
    '// Build up the string for the Database
    sCode = Me.Controls("cboState").Text & "." & UCase(Me.Controls("txtCode").Text)

    '// Open connection to database
 
oConn.Open "Provider=sqloledb;" & _
           "Data Source=VIPER;" & _
           "Initial Catalog=fdmdev;" & _
           "Integrated Security=SSPI"





    Set oRS = oConn.Execute("SELECT * FROM tblCodes WHERE Code = '" & sCode & "'")

    If oRS.BOF And oRS.EOF Then
        '// The code isn't in the DB, so add it
        oConn.Execute ("INSERT INTO tblCodes VALUES ('" & sCode & "')")
        MsgBox "Code has been added to the database"
    Else
        '// The code is already in the database
        MsgBox "Code already in database"
    End If

    '// Close database connection
    oConn.Close
   
    '// Release objects
    Set oRS = Nothing
    Set oConn = Nothing

End Sub
brian_leightyAsked:
Who is Participating?
 
ShauliConnect With a Mentor Commented:
<<  can you explain more in detail I'm a newbie to this whole database thing >>
When you create a database, you set fields. Each field has a datatype definition, such as varchar or int or tinyint or float etc...
When you set the datatype, you also set the maximum length of the field. For example:
A varchar(8) field is a field for text with the length of 8 characters. Lets say that this filed is for phone numbers, and phone numbers are (US numbers) 13 characters long (212)222-2222) right? so if you declare your filed with the length of 8 characters, then it does not have enough 'space' to include the phone number.
So, how to get around it?
One way is to change the definition of the field. This you can do by running the Enterprise Manager and change the field length in the design mode of the table.
Another way, if you must keep the length of the field as is, is to cut the data in sCode to fit the maximum length of the field. This you can do with code, at your end. Lets take the phone example again, The field length is 8, and sCode length is 13

INSERT INTO tblCodes VALUES ('" & Left(sCode, 8) & "')"
'                                                    ^^^^^^^^^^

Of course by using it taht way, you are going to lose some of the data. So I think the first option is preferred :)

Hope that is clear,

S
0
 
[ fanpages ]IT Services ConsultantCommented:
Hi,

Your SQL statement should be:

INSERT INTO tblCodes ( Code ) SELECT "<code>"


Hence, your statement should be:

oConn.Execute ("INSERT INTO tblCodes ( Code ) SELECT '" & sCode & "'")

BFN,

fp.
0
 
brian_leightyAuthor Commented:
still get that error
you know what I could be doing wrong
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

 
ShauliCommented:
oConn.Execute "INSERT INTO pr_tblCodes " _
                        & "(field1, field2,.....) VALUES (" _
                        & "'" & valueforfield1 & "', " _
                        & "'" & valueforfield2 & "');"

INSERT INTO must have all fields name in the table listed as shown above, and the values, corresponding to the fileds order, follow.

S
0
 
[ fanpages ]IT Services ConsultantCommented:
Hi,

You didn't actually say what your "error" was.

Please could you transpose this to a further reply?

It may also be helpful to summarise the columns in your [tblCodes] table; in case there are any required columns that you are not defining values for (and are being set to NULL making the whole record invalid).

The SQL statement I posted earlier will work on a table that just has a column named "Code"; regardless of how many other columns exist, so long as there is no enforced integrity or validation that rejects the record for other reasons.

The actual error message(s) reported to you [in both the case that prompted you to raise this E-E question], and the subsequent attempt, will prove useful in this respect.

Thanks.

BFN,

fp.
0
 
brian_leightyAuthor Commented:
The runtime error is:

"String or Binary Data would be truncated"
 

This is the only error that I have received....
0
 
ShauliCommented:
The value to insert is longer than the field size. Lime: "ABCDEFG" while the field is varchar(5).
Either increase the field size or truncate at the front end to match or to be smaller than the field size.

S
0
 
brian_leightyAuthor Commented:
can you explain more in detail I'm a newbie to this whole database thing
0
 
Carl TawnSystems and Integration DeveloperCommented:
INSERT INTO tblCodes VALUES ('" & sCode & "')" should work fine.  

You don't need to specify a list of field names if you are providing values for all fields. The error is saying that the value in sCode is longer than the maximum length allowed for the field in the database.
0
 
[ fanpages ]IT Services ConsultantCommented:
Hi Brian,

The message relates to the fact that the value of the 'sCode' you are trying to insert exceeds the maximum number of characters that the column (field) supports in the [tblCodes] table.

Please review (in the table design mode) the data type of this field, and if it is not longer enough to hold all the longest value of "sCode" you are attempting to write into the table, then increase the (text) field size.

BFN,

fp.

0
 
JesterTooCommented:
This mans that to contain the 3-character code plus the "."  plus the 2-character state abbreviation that your column definition must be a char or varchar of at least 6 bytes,

I think you may have another problem, however, with your design... just because someone enters "GTB" and "KY" and you find it in the table this might not necessarily mean you have the right customer... how will you handle another customer whose name might have the same 3-character abbreviation?  A better solution in your VB program would be to create a dropdown list of all the customers (name and code) then allow the user to pick one of them rather than forcing them to memorize the customer codes.  That way, upi are free to use alternate codes for multiple customers which would otherwise have the same code and the user won't be confused (and your app won't be retrieving/updating data for the wrong customer).  Note that to support an alternate customer-code algorithm, you might need to enlarge the column in the table to accomodate something like "GTB001.KY".

HTH,
Lynn
0
 
Shiju SasidharanAssoc Project ManagerCommented:
Can u please check the field size allowed for the field "Code" ?
or replace insertion portion with this code
'=====================
   Set oRS = oConn.Execute("SELECT * FROM tblCodes WHERE Code = '" & sCode & "'")
   
    If oRs.BOF And oRs.EOF Then
        '// The code isn't in the DB, so add it
        If Len(sCode) > oRs.Fields("Code").DefinedSize Then
            MsgBox sCode & " - Code is Lengthy and Cannot be inserted", vbExclamation
        Else
            oConn.Execute ("INSERT INTO tblCodes(Code) VALUES ('" & Replace(sCode, "'", "''") & "')")
            MsgBox "Code has been added to the database"
        End If
    Else
        '// The code is already in the database
        MsgBox "Code already in database"
    End If
'============================

;-)
Shiju
0
 
brian_leightyAuthor Commented:
that worked thank you very much

b
0
 
[ fanpages ]IT Services ConsultantCommented:
You're very welcome, but... sorry, 'b', what worked?

Please could you indicate your acceptance of one/more suggestions during your closure of this question?

Thank you.

BFN,

fp.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.