使用ASP.NET关键字'join'附近的语法不正确 [英] Incorrect syntax near the keyword 'join' using ASP.NET

问题描述

请注意我仍然是数据库的初学者，但愿意学习。这一次，我想知道如何从数据库更新数据。我已经尝试过这段代码，但它给了我一个错误关键字'JOIN'附近的语法不正确。请帮我解决这个问题。



关于数据库的信息



表stringInstrumentItem（列brandId是外键并引用表品牌的主键，也称为brandId）：



itemId      brandId     model


1             1  ;             xyz


2             1               abc


3             2               hjk








表品牌（其主要名称为brandId，引用的是表格strinInstrumentItem）：



brandId     name     image


1             Ibanez       xyz.jpg


2             Fender       abc.jpg


3             Gibson       hjk.jpg
< br $> b $ b





主要目标是通过gridview更新表品牌名称列。例如在brandId＃1中，名字等于Ibanez，我想将其改为Jackson。它应该能够在没有异常错误的情况下工作。如何查询这个？



我的尝试：

take note I'm still a beginner in databases but willing to learn. This time, I want to know how to update a data from database. I've tried this code but it is giving me an error "Incorrect syntax near the keyword 'JOIN'". Kindly help me on solving this one.

Info about the database

Table stringInstrumentItem(The column brandId is the foreign key and references the primary key of table brand, which is also named brandId):

itemId     brandId     model

1             1               xyz

2             1               abc

3             2               hjk




Table brand(which has the primary key called brandId that is referencing by the table strinInstrumentItem):

brandId     name     image

1             Ibanez       xyz.jpg

2             Fender       abc.jpg

3             Gibson       hjk.jpg




Main goal is to update the column called name in table brand through my gridview. Like for example in brandId#1, name is equals to Ibanez, and I want to change it to Jackson. It should be able to work without the exception error. How to query this one?

What I have tried:

string queryGuitarItems = "UPDATE stringInstrumentItem JOIN brand ON stringInstrumentItem.brandId = brand.brandId SET stringInstrumentItem.brandId = @brandId IN (SELECT brand.brandId FROM brand WHERE name = @oldBrandName";
    using (SqlConnection connectionGuitarItems = new SqlConnection(ConfigurationManager.ConnectionStrings["musicStoreConnection"].ToString()))
    {
        using (SqlCommand commandGuitarItems = new SqlCommand(queryGuitarItems, connectionGuitarItems))
        {
            List<SqlParameter> parameterGuitarItems = new List<SqlParameter>();
            parameterGuitarItems.Add(new SqlParameter("@brandId", newName.Text));
            parameterGuitarItems.Add(new SqlParameter("@oldBrandName", oldName));

            connectionGuitarItems.Open();
            GetParameterGuitarItems(commandGuitarItems,parameterGuitarItems.ToArray());
            commandGuitarItems.ExecuteNonQuery();
            connectionGuitarItems.Close();
            commandGuitarItems.Parameters.Clear();
        }
    }

推荐答案

查看帖子中的查询，看来你正在尝试更新brandid在品牌StringInstrumentItem表中为oldBrandName。查询应该是这样的..



UPDATE SII SET SII.brandId = b.brandId

FROM stringInstrumentItem SII

内连接品牌b ON SII.brandId = b.brandId

AND b.name = @oldBrandName

looking at the query in your post, it seems you are trying to update brandid in brand StringInstrumentItem table for the oldBrandName. the query should be like this..

UPDATE SII SET SII.brandId = b.brandId
FROM stringInstrumentItem SII
inner join brand b ON SII.brandId = b.brandId
AND b.name = @oldBrandName

我认为您的查询需要看起来像这：

I think your query needs to look something like this:

UPDATE SII SET SII.brandId = @brandId 
FROM stringInstrumentItem SII JOIN brand ON SII.brandId = brand.brandId 
WHERE brand.name = @oldBrandName

但这没有任何意义，因为你已经加入同一brandId，所以也许这就是你所需要的：

But that would make no sense, as you already JOIN on the same brandId, so maybe this is what you need:

UPDATE SII SET SII.brandId = @brandId 
FROM stringInstrumentItem SII, brand
WHERE brand.name = @oldBrandName

这篇关于使用ASP.NET关键字'join'附近的语法不正确的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！