Solved

SQL error

Posted on 2006-10-29
27
203 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
  • 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
 
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
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
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 250 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

Threat Intelligence Starter Resources

Integrating threat intelligence can be challenging, and not all companies are ready. These resources can help you build awareness and prepare for defense.

Join & Write a Comment

A while ago, I was working on a Windows Forms application and I needed a special label control with reflection (glass) effect to show some titles in a stylish way. I've always enjoyed working with graphics, but it's never too clever to re-invent …
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.
Sending a Secure fax is easy with eFax Corporate (http://www.enterprise.efax.com). First, Just open a new email message.  In the To field, type your recipient's fax number @efaxsend.com. You can even send a secure international fax — just include t…
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…

757 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

Need Help in Real-Time?

Connect with top rated Experts

20 Experts available now in Live!

Get 1:1 Help Now