9 Replies - 890 Views - Last Post: 01 August 2014 - 08:15 AM Rate Topic: -----

#1 Cheddar0811   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 31-July 14

Am i crazy or will this sql statement not work?

Posted 01 August 2014 - 07:03 AM

I am trying to update a table based on a job number in vb.net windows form applications. I have typed quite a few sql statements but for some reason I CAN NOT get this thing to run. Can I get a few extra sets of eyes on this?

 
 Using conn1 As New SqlConnection(connstring)
                conn1.Open()
                Using comm2 As SqlCommand = New SqlCommand("Select (Shear) from production.dbo.[Floor Cell Jobs\Shears] where JobNum = JobNumber Update Production.dbo.tblFCOrdered (ShearNumber)", conn1)
                    comm2.ExecuteReader()
                End Using
            End Using



Is This A Good Question/Topic? 0
  • +

Replies To: Am i crazy or will this sql statement not work?

#2 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 14768
  • View blog
  • Posts: 59,038
  • Joined: 12-June 08

Re: Am i crazy or will this sql statement not work?

Posted 01 August 2014 - 07:11 AM

why is the table name "Floor Cell Jobs\Shears"?

where JobNum = JobNumber

Where did this "jobnumber" column come from?

Update Production.dbo.tblFCOrdered (ShearNumber)

Why are you jamming a second statement in with the first?

Where is this "sheernumber" column coming from?

Why does the update not have a 'WHERE' clause?

Why does the UPDATE not use the typical 'SET <col1name> = <value1>, <col2name> = <val2>' structure?
Was This Post Helpful? 0
  • +
  • -

#3 Cheddar0811   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 31-July 14

Re: Am i crazy or will this sql statement not work?

Posted 01 August 2014 - 07:24 AM

The table name has spaces because the last engineer named it that, I did not set the table up. the jobnumber column comes from the table "FCOrdered." Sorry, my statements are reversed. I was trying to do something wack because I cant get this thing to run. The update code comes from an online site called W3 schools.
check this:
http://www.w3schools...into_select.asp

I am trying to do something like this except with an update statement.
Was This Post Helpful? 0
  • +
  • -

#4 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 14768
  • View blog
  • Posts: 59,038
  • Joined: 12-June 08

Re: Am i crazy or will this sql statement not work?

Posted 01 August 2014 - 07:27 AM

Then you are doing something terribly wrong. You can just have two independent sql statements and hope to mash them together.

I am not fully certain what you are doing, but I do know you cannot make those two work in the way you are trying.
Was This Post Helpful? 0
  • +
  • -

#5 Cheddar0811   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 31-July 14

Re: Am i crazy or will this sql statement not work?

Posted 01 August 2014 - 07:34 AM

Maybe this would work better?

Try
            Using conn1 As New SqlConnection(connstring)
                conn1.Open()
                Using comm1 As SqlCommand = New SqlCommand("Insert Into Production.dbo.tblFCOrdered (JobNumber, LineNumber, OrderedBy, FloorNumber, DateOrdered) Select Shear from production.dbo.[Floor Cell Jobs\Shears] where JobNum=JobNumber (@Job, @Line, @Name, @Floor, getdate())", conn1)
                    With comm1.Parameters
                        .AddWithValue("@Job", CBJob1.SelectedValue)
                        .AddWithValue("@Line", ComboBoxLine.Text)
                        .AddWithValue("@name", CBName.SelectedValue)
                        .AddWithValue("@floor", Trim(TBFloor1.Text))
                    End With
                    comm1.ExecuteReader()
                End Using
                conn1.Close()
            End Using
            Using conn1 As New SqlConnection(connstring)
                conn1.Open()
                Using comm2 As SqlCommand = New SqlCommand("", conn1)
                    comm2.ExecuteReader()
                End Using



Never mind the bottom code, its supposed to be commented out.

I am trying to insert a row into the table "FCOrdered", I want to insert, LineNumber, DateOrdered, OrderedBy, FloorNumber, JobNumber, and ShearNumber. The user will input everything except the date, which is automaticaly recorded, and the ShearNumber. The ShearNumber can be pulled from another table that loads the job numbers, so how does one double up on an INSERT statement by having a user input their data and pull data from another table at the same time??
Was This Post Helpful? 0
  • +
  • -

#6 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 14768
  • View blog
  • Posts: 59,038
  • Joined: 12-June 08

Re: Am i crazy or will this sql statement not work?

Posted 01 August 2014 - 07:38 AM

Insert Into Production.dbo.tblFCOrdered (JobNumber, LineNumber, OrderedBy, FloorNumber, DateOrdered) 
Select Shear 
from production.dbo.[Floor Cell Jobs\Shears] 
where JobNum=JobNumber (@Job, @Line, @Name, @Floor, getdate())"




This fails for multiple reasons. The chief being a SQL INSERT statement requires you to list out the columns, and provide input for said columns.. in the order they are listed.

Example:
INSERT INTO <table name> (<col1>, <col2>,<col3>,etc)
VALUES (<val1>, <val2>, <val3>, etc)


INSERT INTO <table name> (<col1>, <col2>,<col3>,etc)
SELECT <colA>
,<colb>
,<colc>
,etc
FROM <table name2>
WHERE <condition> 



I see no column listed for 'shear'.

You should spend more time reading about the structure of SQL queries.

Example:
http://technet.micro...=sql.90%29.aspx
http://msdn.microsof...y/ms187731.aspx
http://msdn.microsof...y/ms174335.aspx
http://msdn.microsof...y/ms177523.aspx
Was This Post Helpful? 0
  • +
  • -

#7 Cheddar0811   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 31-July 14

Re: Am i crazy or will this sql statement not work?

Posted 01 August 2014 - 07:42 AM

Here it is, I am receiving an error that says " 'JobNumber' is not a recognized function name." Here is where I added the shear column name.

 Try
            Using conn1 As New SqlConnection(connstring)
                conn1.Open()
                Using comm1 As SqlCommand = New SqlCommand("Insert Into Production.dbo.tblFCOrdered (ShearNumber, JobNumber, LineNumber, OrderedBy, FloorNumber, DateOrdered) Select Shear from production.dbo.[Floor Cell Jobs\Shears] where JobNum=JobNumber (@Job, @Line, @Name, @Floor, getdate())", conn1)
                    With comm1.Parameters
                        .AddWithValue("@Job", CBJob1.SelectedValue)
                        .AddWithValue("@Line", ComboBoxLine.Text)
                        .AddWithValue("@name", CBName.SelectedValue)
                        .AddWithValue("@floor", Trim(TBFloor1.Text))
                    End With
                    comm1.ExecuteReader()
                End Using
                conn1.Close()
            End Using
        Catch ex As Exception
            MsgBox("Problem with Sql connection in porition A, please contact an engineer!")
            MsgBox(ex.ToString)
        End Try

Was This Post Helpful? 0
  • +
  • -

#8 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 14768
  • View blog
  • Posts: 59,038
  • Joined: 12-June 08

Re: Am i crazy or will this sql statement not work?

Posted 01 August 2014 - 07:50 AM

Again - reread the structure for a MSSQL statement. You cannot just dump things at the back end and expect the code to make some sort of logical leap. If you are doing an INSERT then you certainly need things in the order of the columns presented.

Instead of this:
Select Shear from production.dbo.[Floor Cell Jobs\Shears] where JobNum=JobNumber (@Job, @Line, @Name, @Floor, getdate())


put things in the INSERT column order like this

Select Shear,@Job, @Line, @Name, @Floor, getdate() from production.dbo.[Floor Cell Jobs\Shears] where JobNum=JobNumber



where JobNum=JobNumber

Next - the error about "JobNumber". Where is "JobNumber" magically coming from?
Was This Post Helpful? 0
  • +
  • -

#9 Cheddar0811   User is offline

  • New D.I.C Head

Reputation: 0
  • View blog
  • Posts: 11
  • Joined: 31-July 14

Re: Am i crazy or will this sql statement not work?

Posted 01 August 2014 - 08:10 AM

JobNumber is a column from the table "FCOrdered." I am trying to populate the JobNumber column from the JobNum column from another table. My original idea was to have an INSERT statement to populate all the user entered data and then use an UPDATE sql command to pull the shear number based on the jobNumber. The Jobnumber is a primary key.
Was This Post Helpful? 0
  • +
  • -

#10 modi123_1   User is online

  • Suitor #2
  • member icon



Reputation: 14768
  • View blog
  • Posts: 59,038
  • Joined: 12-June 08

Re: Am i crazy or will this sql statement not work?

Posted 01 August 2014 - 08:15 AM

Quote

JobNumber is a column from the table "FCOrdered."JobNumber is a column from the table "FCOrdered." I am trying to populate the JobNumber column from the JobNum column from another table


That jumble of words is beginning to hurt my head. Look.. you cannot just throw in column names you pulled from the air. If you want to do add a new row then why would it make sense to think you can reference a column from the table you think you are trying to insert into?

INSERT adds a new row. You should furnish new-ish data to it.
UPDATE modifies an existing row.
Was This Post Helpful? 0
  • +
  • -

Page 1 of 1