?
Solved

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

Posted on 2005-04-21
14
Medium Priority
?
207 Views
Last Modified: 2010-05-02

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
0
Comment
Question by:brian_leighty
  • 4
  • 4
  • 3
  • +3
14 Comments
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 13839155
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
 

Author Comment

by:brian_leighty
ID: 13839474
still get that error
you know what I could be doing wrong
0
 
LVL 19

Expert Comment

by:Shauli
ID: 13839657
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
What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 13839724
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
 

Author Comment

by:brian_leighty
ID: 13840024
The runtime error is:

"String or Binary Data would be truncated"
 

This is the only error that I have received....
0
 
LVL 19

Expert Comment

by:Shauli
ID: 13840043
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
 

Author Comment

by:brian_leighty
ID: 13840369
can you explain more in detail I'm a newbie to this whole database thing
0
 
LVL 52

Expert Comment

by:Carl Tawn
ID: 13840942
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
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 13842140
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
 
LVL 19

Accepted Solution

by:
Shauli earned 2000 total points
ID: 13842273
<<  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
 
LVL 22

Expert Comment

by:JesterToo
ID: 13846308
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
 
LVL 14

Expert Comment

by:Shiju Sasidharan
ID: 13849551
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
 

Author Comment

by:brian_leighty
ID: 13859876
that worked thank you very much

b
0
 
LVL 35

Expert Comment

by:[ fanpages ]
ID: 13862359
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

Featured Post

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

When designing a form there are several BorderStyles to choose from, all of which can be classified as either 'Fixed' or 'Sizable' and I'd guess that 'Fixed Single' or one of the other fixed types is the most popular choice. I assume it's the most p…
You can of course define an array to hold data that is of a particular type like an array of Strings to hold customer names or an array of Doubles to hold customer sales, but what do you do if you want to coordinate that data? This article describes…
As developers, we are not limited to the functions provided by the VBA language. In addition, we can call the functions that are part of the Windows operating system. These functions are part of the Windows API (Application Programming Interface). U…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…
Suggested Courses

829 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question