Still celebrating National IT Professionals Day with 3 months of free Premium Membership. Use Code ITDAY17

x
?
Solved

SQL error

Posted on 2006-10-29
27
Medium Priority
?
211 Views
Last Modified: 2010-04-23
HI

VB .Net windows project.
The following code placed in a textbox makes an error when opening the form:

Error:
"Cannot use empty object or column names. Use a single space if necessary. Line 1: Incorrect syntax near: ''.

Code:
 Private Sub TextBox415_TextChanged(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles TextBox415.TextChanged
        Dim Data As String
        Data = ComboBox1.Text
        Dim PatientID As String = Label22.Text
        Dim ID As String = TextBox415.Text

        Try
            Dim Connection As New SqlConnection(Conn)
            Connection.Open()

            Dim Command As New SqlCommand("UPDATE [" & Data & "] Set P1 = '" & ID & "' WHERE [Patient nr] = '" & PatientID & "'", Connection)
            Command.ExecuteNonQuery()

            Connection.Close()
            Connection = Nothing

        Catch ex As Exception
            ' if any other error type occurs or the attempts are
            ' too many, do the following.
            MsgBox(ex.Message)

        End Try
    End Sub

...if the code is placed in "Textbox425_leave" the fault disapears but then the text isn't updated in the SQL database.

Regards Steen
0
Comment
Question by:steensommer
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 14
  • 13
27 Comments
 
LVL 35

Expert Comment

by:Raynard7
ID: 17827949
Hi,

this must be occuring becuase i'm guessing that you are populating the form after you have loaded - and when you do so not all the values for data, id and patient id are set - so some are just "" or empty which is what is giving you the error, particularly the ComboBox1.Text.

You would be woth checking to make sure that the value called Data is not empty - and if it is not saving.

On the bigger picture I'd advise against having this automatically update your data - a button to click to save changes would not present this problem.
0
 
LVL 35

Expert Comment

by:Raynard7
ID: 17827950
in effect your sql is saying

UPDATE [] Set P1 = '' Where [Patient nr - ''

if they are empty - as you can see an empty object being the table name
0
 
LVL 1

Author Comment

by:steensommer
ID: 17827980
Ok but still: I get an error but the data is displayed as it should!
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 35

Expert Comment

by:Raynard7
ID: 17827995
how about instead of trying to update the database just put

"UPDATE [" & Data & "] Set P1 = '" & ID & "' WHERE [Patient nr] = '" & PatientID & "'"

into a messagebox

I'm sure you will see that it is not set most of the time - especially on loading you form - or if the combo box is not set to anything.

if it is showing correct SQL then you have another problem - but I doubt that this is the case
0
 
LVL 1

Author Comment

by:steensommer
ID: 17828014
Hmm

This was actually a good idea!. The [" & Data & "] was empty: []
0
 
LVL 35

Expert Comment

by:Raynard7
ID: 17828015
now you just need to decide what do do with it
0
 
LVL 1

Author Comment

by:steensommer
ID: 17828020
Yet another problem: Label22.text just showed: Label22
0
 
LVL 35

Expert Comment

by:Raynard7
ID: 17828021
Well,

what is the text of Label22 ??
how does it appear on your form?

I'm guessing that this event is being triggered before you have popluated data into these fields (text boxes and labels)

maybe you should review your code and add the on change event delegation to be at the end of form initilisation - this hopefully will solve the problem.
0
 
LVL 1

Author Comment

by:steensommer
ID: 17828031
Just to describe what I doing:

From the Headform: FrmPatientliste I have several textboxes: Some of the boxes contains numbers which it uniq for an induvidual.
When right-clicking the number I can start the menssioned form (FrmMedicin) (from an Contextmenustrip).
0
 
LVL 1

Author Comment

by:steensommer
ID: 17828034
The label22.text shows the uniq patient number
0
 
LVL 35

Expert Comment

by:Raynard7
ID: 17828055
at one point does it show the relevant number?

hi - i'm not sure without looking at everything but it obviosly is not being set by the time the textchange is selected
the other thing I would bear in mind is you will get plenty of traffic to the server doing it this way
if I was writing a long word many updates would happen - also you are not validating the data which means that your tables may not necessarily exist.

having "Textbox425_leave"  does it ever fire? and if so where does it get to - can you put a break point on it? this seems more sensibile if you do not want a button.

incidentally - if you try having a button that has this code in it - does this actually work?
0
 
LVL 1

Author Comment

by:steensommer
ID: 17828253
The reason why I tryed the textbox_Change was because the textbox_leave didn't work as it should.
The label22.text was just a stupid way of catching the uniq patient number from the Mainform to FrmMedicin.

But maybe a button would be a better solution - anyway it wood mean less code and minor traffic to the SQL server.
0
 
LVL 35

Expert Comment

by:Raynard7
ID: 17828274
just an idea,

well a button is quick to test if the right data is displayed for the sql statement in a msg box as suggested above - if this works I'd think this may be the fastest way of doing this - and the way I would have done it anyway
0
 
LVL 1

Author Comment

by:steensommer
ID: 17828307
Thank you.

You will off cause get all the points. But if it's allright with you I will wait just a little longer to try getting the code to work :0)

Steen
0
 
LVL 35

Expert Comment

by:Raynard7
ID: 17828326
that is fine - just post if you have any problems.
0
 
LVL 1

Author Comment

by:steensommer
ID: 17828345
Thanks - I have one more question - at least ;0)

The form contains more than 100 controls: textboxes and checkboxes
Is there any shortcut to get this better:

 Private Sub Button23_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles Button23.Click
        Dim PatientID As String = Label22.Text

        Try
            Dim Connection As New SqlConnection(Conn)
            Connection.Open()

            Dim Command As New SqlCommand("UPDATE [" & Data & "] Set P1 = '" & TextBox415.Text & "', P2 = '" & TextBox414.Text & "' ................WHERE [Patient nr] = '" & PatientID & "'", Connection)
            Command.ExecuteNonQuery()

            Connection.Close()
            Connection = Nothing

        Catch ex As Exception
            ' if any other error type occurs or the attempts are
            ' too many, do the following.
            MsgBox(ex.Message)

        End Try
    End Sub
0
 
LVL 35

Expert Comment

by:Raynard7
ID: 17828357
I'd do two things,

Firstly I'd stop using names like Button23 and LAbel22 and give them descriptive names,

additionally - I'd put the SQL command data into a separate string - ie

            Dim strSql as string = "UPDATE [" & Data & "] Set P1 = '" & TextBox415.Text & "', P2 = '" & TextBox414.Text & "' WHERE [Patient nr] = '" & PatientID & "'"
            Dim Command As New SqlCommand(strSql, Connection)
            Command.ExecuteNonQuery()
0
 
LVL 1

Author Comment

by:steensommer
ID: 17828482
Ok

This also counts for textboxes and checkboxes I guess!

When having a very long code it is possible to go to next line and continue the code here using a:  _
but I can't get it to work!
0
 
LVL 35

Expert Comment

by:Raynard7
ID: 17831514
Should work  ie you should be able to do

Dim strSql as string

strSql = "UPDATE [" & Data & "] Set P1 " & _
             "= '" & TextBox415.Text & "', P2 = '" & +
             TextBox414.Text & "' WHERE [Patient nr] = '" & _
             PatientID & "'"
0
 
LVL 1

Author Comment

by:steensommer
ID: 17832020
Once more thank you :0)
I'm still workning on renaming all the controls - it takes quit a while but I guess it's worth it!
0
 
LVL 1

Author Comment

by:steensommer
ID: 17838113
Hi again

I'm trying to gather all the code in one button. BUT how do I code the Checkbox.
I have used the following code in each checkbox which works fine but I guess there must be a better way?

   Private Sub CheckBox241_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles ChkUdlP15D6.Click
        Dim Data As String
        Data = ComboBox1.Text

        Dim PatientID As String = LblHCV.Text
        Dim ID As String
        Dim Connection As New SqlConnection(Conn)
        Connection.Open()
        If ChkUdlP15D6.Checked = False Then
            ID = 0
        Else
            ID = 1
        End If
        Dim Command As New SqlCommand("UPDATE [" & Data & "] Set U15D6 = " & ID & " WHERE [Patient nr] = '" & PatientID & "'", Connection)
        Command.ExecuteNonQuery()

        Connection.Close()
        Connection = Nothing
    End Sub

Steen
0
 
LVL 35

Expert Comment

by:Raynard7
ID: 17838159
sorry - quite busy today - will get back to you shortly
0
 
LVL 35

Expert Comment

by:Raynard7
ID: 17840222
Hi,

I'm not sure about everything that you are doing - however if you are doing all these updates why not just use a datagrid?

What you have looks allright - however I still would advocate that all these updates being done on changes is not good one save button would be easier to code and for the user to understand.

Additionally you mention that you have this code for each check box.  I'd create a function that takes the data to be updated  and the fields to save them in and just reference this as a delegate for each box.  Then you have less places to call this data.

Ie all you need to pass in this example is the value of ChkUdlP15D6 and the field to change.
0
 
LVL 1

Author Comment

by:steensommer
ID: 17840374
Well I have made a save button - that Updates all the textboxes! Wasn't that what you meant?

Ups as a novice I'm not sure how to make such a Function - can you help or guide?

Steen
0
 
LVL 35

Accepted Solution

by:
Raynard7 earned 1000 total points
ID: 17840389
Hi,

I dont really know about your whole project - what you are doing and what you are trying do do - but you have one sub that is fired when there is an on click

what you could do is have something like

public sub updateData(fieldToUpdate as string, valueToUpdateWith as boolean)
    'put your update queries etc inside here - using the values fieldToUpdate and the value inside your query
end sub

and then call updateData("P15D6", ChkUdlP15D6.Checked)

I really think if you need more information about this you ask another question - your issue with the sql error is really finished
0
 
LVL 1

Author Comment

by:steensommer
ID: 17840407
..Off course. You are quite wright. :0)

You have made an excellent work - thank you!

Regards Steen
0
 
LVL 35

Expert Comment

by:Raynard7
ID: 17840421
I'm glad I could help
0

Featured Post

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Well, all of us have seen the multiple EXCEL.EXE's in task manager that won't die even if you call the .close, .dispose methods. Try this method to kill any excels in memory. You can copy the kill function to create a check function and replace the …
Calculating holidays and working days is a function that is often needed yet it is not one found within the Framework. This article presents one approach to building a working-day calculator for use in .NET.
In this brief tutorial Pawel from AdRem Software explains how you can quickly find out which services are running on your network, or what are the IP addresses of servers responsible for each service. Software used is freeware NetCrunch Tools (https…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…
Suggested Courses

688 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