Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 214
  • Last Modified:

SQL error

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
steensommer
Asked:
steensommer
  • 14
  • 13
1 Solution
 
Raynard7Commented:
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
 
Raynard7Commented:
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
 
steensommerAuthor Commented:
Ok but still: I get an error but the data is displayed as it should!
0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

 
Raynard7Commented:
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
 
steensommerAuthor Commented:
Hmm

This was actually a good idea!. The [" & Data & "] was empty: []
0
 
Raynard7Commented:
now you just need to decide what do do with it
0
 
steensommerAuthor Commented:
Yet another problem: Label22.text just showed: Label22
0
 
Raynard7Commented:
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
 
steensommerAuthor Commented:
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
 
steensommerAuthor Commented:
The label22.text shows the uniq patient number
0
 
Raynard7Commented:
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
 
steensommerAuthor Commented:
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
 
Raynard7Commented:
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
 
steensommerAuthor Commented:
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
 
Raynard7Commented:
that is fine - just post if you have any problems.
0
 
steensommerAuthor Commented:
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
 
Raynard7Commented:
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
 
steensommerAuthor Commented:
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
 
Raynard7Commented:
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
 
steensommerAuthor Commented:
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
 
steensommerAuthor Commented:
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
 
Raynard7Commented:
sorry - quite busy today - will get back to you shortly
0
 
Raynard7Commented:
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
 
steensommerAuthor Commented:
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
 
Raynard7Commented:
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
 
steensommerAuthor Commented:
..Off course. You are quite wright. :0)

You have made an excellent work - thank you!

Regards Steen
0
 
Raynard7Commented:
I'm glad I could help
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.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 14
  • 13
Tackle projects and never again get stuck behind a technical roadblock.
Join Now