Update Query using two tables asp.net vb

Question

Please help me with this update query. I COMPLETELY understand how redundant this is but I NEED it. Upon users creating a User account which only contains the ID, Username, Password and CustomerID I must also have a record in Customers create itself but null everything and the User.User_Customer_ID must update to the newly created Customer.Customer_ID

I have everything working but this pesky update query. The ONLY thing it needs to do is change User.User_Customer_ID to Customer.Customer_ID I managed to story the User.User_ID in an integer called UserID and Customer.Customer_ID in an integer called CustomerID I have tried a million different ways with no success

Here is what one of the many queries I have tried.

1. UPDATE [User]
   SET User_Customer_ID = Customer.Customer_ID
   FROM Customer
   WHERE [User].User_ID = '16'
   AND Customer.CUstomer_ID = '19'
  ( ? would be the integer UserID and CustomerID )

Here is how it looks before it is inserted into the SQL command in VB

   UpdateUserCustomerID = "UPDATE [User] SET User_Customer_ID = Customer.Customer_ID FROM Customer WHERE ([User].User_ID ='" & UserID & "' AND Customer.Customer_ID = '" & CustomerID & "')"

Access Database / ASP.Net / VB

Table:

Customer:

Customer_ID
Customer_First_Name
Customer_Last_Name
Customer_Street_Address
Customer_City
Customer_State
Customer_Zip
Customer_Email

User:

User_ID
User_UserName
User_Password
User_Customer_ID

Fionnuala · Accepted Answer · 2012-06-13 12:45:56Z

2

It seems pretty unlikely to me that the IDs are text fields (columns), if they are number fields, the quotes are one of the the problems.

As far as I can see, this:

"UPDATE [User] SET User_Customer_ID = Customer.Customer_ID FROM Customer WHERE ([User].User_ID ='" & UserID & "' AND Customer.Customer_ID = '" & CustomerID & "')"

Should be:

"UPDATE [User] SET User_Customer_ID = " & CustomerID _
& " WHERE ([User].User_ID = " & UserID

answered Jun 13, 2012 at 12:45

Fionnuala

91.4k7 gold badges121 silver badges155 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Update Query using two tables asp.net vb

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related