我用C#连接到SQL Server。现在,我必须做3个选项。一个是执行一个过程,它工作得很好,第二个是检索一些数据,这也是完成的,最后一个选项是选择一个特定的国家,这是由用户选择的,并显示位于该国家的餐厅的平均分数。
现在,当我测试它时,该查询在SSMS上工作。然而,当我尝试像其他两个选项一样运行它时,它给了我一个连接错误。
下面的代码是第二个选项的C#代码:
public static void showStatistics()
{
string connetionString = "Data Source=DESKTOP-RV4VR9I;Initial
Catalog=Assignment;User ID=Jake;Password=1234;Integrated Security=True";
SqlConnection conn = new SqlConnection(connetionString);
SqlCommand mySQL = new SqlCommand("BEGIN TRANSACTION SET
TRANSACTION ISOLATION LEVEL READ COMMITTED SELECT TOP 1 country, score,
year FROM main.[stats] ORDER BY [Statistics Generated] DESC COMMIT
TRANSACTION", conn);
try
{
conn.Open();
SqlDataReader dataReader = mySQL.ExecuteReader();
string holderCountry = "";
string holderScore = "";
string holderYear = "";
while (dataReader.Read())
{
holderCountry += dataReader["country"];
holderScore += dataReader["score"];
holderYear += dataReader["year"];
}
Console.WriteLine("\nCountry: "+holderCountry);
Console.WriteLine("Score: " + holderScore);
Console.WriteLine("Year: " + holderYear+"\n");
conn.Close();
}
catch (Exception e)
{
Console.WriteLine("Can not open connection ! ");
}
}
下一段代码是给我一个错误的代码:
public static void showCountryAverage(string countryChoice)
{
string connetionString = "Data Source=DESKTOP-RV4VR9I;Initial
Catalog=Assignment;User ID=Jake;Password=1234;Integrated Security=True";
SqlConnection conn = new SqlConnection(connetionString);
SqlCommand mySQL = new SqlCommand("BEGIN TRANSACTION SET
TRANSACTION ISOLATION LEVEL READ COMMITTED SELECT c.country,
AVG(CAST(re.score AS DECIMAL(8, 6))) FROM main.country c JOIN
main.restaurant r ON(c.countryId = r.countryId) JOIN main.review re
ON(r.restaurantId = re.restaurantId) WHERE c.country = "+countryChoice+"
GROUP BY c.country COMMIT TRANSACTION", conn);
try
{
conn.Open();
SqlDataReader dataReader = mySQL.ExecuteReader();
string holderCountry = "";
while (dataReader.Read())
{
holderCountry += dataReader["country"];
holderCountry += dataReader["AVG(CAST(re.score AS
DECIMAL(8, 6)))"];
}
Console.WriteLine("\nCountry: " + holderCountry);
conn.Close();
}
catch (Exception e)
{
Console.WriteLine("Can not open connection ! ");
}
}
谢谢!
这就是错误
意大利System.Data.SqlClient.SqlException (0x80131904):无效的列名‘
’。
在System.Data.SqlClient.SqlConnection.OnError(SqlException异常时,布尔值为breakConnection,操作为1 wrapCloseInAction)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection, Action
1 wrapCloseInAction)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject runBehavior,SqlCommand cmdHandler,SqlDataReader dataStream,BulkCopySimpleResultSet bulkCopyHandler,TdsParserStateObject stateObj,Boolean& dataReady) at System.Data.SqlClient.SqlDataReader.TryConsumeMetaData() at System.Data.SqlClient.SqlDataReader.get_MetaData() at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds,runBehavior,String,Boolean,Boolean,System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior,RunBehavior runBehavior,Boolean returnStream,Boolean async,Int32超时,任务&任务,Boolean asyncWrite,Boolean inRetry,SqlDataReader ds,Boolean describeParameterEncryptionRequest) at asyncWrite cmdBehavior,RunBehavior runBehavior,Boolean returnStream,String method,TaskCompletionSource`1完成,Int32超时,任务&任务,Boolean& usedCache,Boolean asyncWrite,Boolean ) at,,Boolean,字符串方法) System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior行为,字符串方法) C:\Users\User\Desktop\000SWD6.1B\2ndSemester\Database 2\Assignment\Database_Number_6\Database_Number_6\Program.cs:line 133中的System.Data.SqlClient.SqlCommand.ExecuteReader() at Database_Number_6.Program.showCountryAverage(String countryChoice)错误号:207,状态:1,类:16
发布于 2019-05-20 23:37:29
无效列名意大利的问题是由将字符串意大利连接到sql命令引起的。在SQL中,如果你想表达一个常量字符串值,你应该把它放在单引号中,就像‘意大利’。如果没有引号,sql解析器引擎会认为您希望将country列中的值与意大利列中的值进行比较。因此,添加引号可以解决这个问题,但这是一个众所周知的解析错误的来源(如果值包含自己的单引号怎么办?我甚至没有开始谈论日期)更糟糕的是,这种方法提供了一种通过Sql Injection入侵数据库的简单方法。
创建需要代码值的查询的唯一正确方法是使用参数
SqlCommand mySQL = new SqlCommand(@".....
SELECT c.country,
....
FROM main.country c
....
WHERE c.country = @country
GROUP BY .....", con);
mySql.Parameters.Add("@country", SqlDbType.NVarChar).Value = countryCode;
....
https://stackoverflow.com/questions/56223787
复制相似问题