我试图结合两个表给出错误..需要帮助 [英] I am trying to combine two table it's giving error..need help

问题描述

 cmd.CommandText =  选择MAINFILE.facno，max（fdate）为MAINTRAN.fdate，max（fbalance）as MAINTRAN.fbalance MAINTRAN INNER JOIN MAINFILE ON MAINFILE.facno = MAINTRAN.facno& _ 
  其中MAINTRAN.fbankcode ='&   010&  '和MAINTRAN.facno = MAINFILE.facno& _ 
  和MAINTRAN.fdate< ='&格式（prdate，  MM / dd / yyyy）&  '& _ 
  和MAINTRAN.fbranchcode ='&   01&  '& _ 
  按MAINFILE.facno分组 

使用cmd

 dr = .ExecuteReader //错误：' 。'。（sqlexception） 
 

解决方案

首先，不要使用字符串连接来构建SQL查询。这样做可以让你自己接受SQL注入攻击。



其次，你将日期存储为字符串。这是一个很大的问题，因为它不具有文化敏感性，按字符串值排序日期与日期按实际日期值排序不匹配。



最后，套管你'重新使用会伤害我的眼睛，使其难以阅读。 SQL关键字通常是大写的，而列名和其他标识符则不是。



另外，我不相信你可以使用点分表示法创建列别名。我正在考虑你在该声明中的AS条款。表别名应该很短，这样它们就不会淹没你的列名，从而使你的SQL更容易阅读。

选择MAINFILE.facno，max（fdate）为MAINTRAN.fdate 
 

应该是

 SELECT mf.facno，MAX（mf.fdate）AS fdate 
 

在尝试在代码中执行此操作之前，请在SQL查询工具中尝试查询。您会发现在工具中调试比在代码中更快更容易。

我会说你的别名是错误的。没有理由在那里包含表名：

 选择 MAINFILE.facno，max（fdate）  as  MAINTRAN.fdate，... 

您想要的是普通名称，与原始列相同：

 选择 MAINFILE.facno，max（fdate） as  fdate，... 

或者如果你想要整个字符串，你需要使用这样的括号：

 选择 MAINFILE.facno，max（fdate）为 [MAINTRAN.fdate]， ... 

试试这个..

 cmd .CommandText =  选择MAINFILE.facno，max（MAINTRAN.fdate）为MAINTRANfdate，max（MAINTRAN.fbalance）为MAINTRANfbalance来自MAINTRAN INNER JOIN MAINFILE ON MAINFILE.facno = MAINTRAN.facno & _ 
  其中MAINTRAN.fbankcode ='&   010&  '和MAINTRAN.facno = MAINFILE.facno& _ 
  和MAINTRAN.fdate< ='&格式（prdate，  MM / dd / yyyy）&  '& _ 
  和MAINTRAN.fbranchcode ='&   01&  '& _ 
  按MAINFILE.facno分组 

cmd.CommandText = "select MAINFILE.facno,max(fdate) as MAINTRAN.fdate ,max(fbalance) as MAINTRAN.fbalance from MAINTRAN INNER JOIN MAINFILE ON MAINFILE.facno=MAINTRAN.facno" & _
                                          "where MAINTRAN.fbankcode='" & "010" & "' and MAINTRAN.facno=MAINFILE.facno" & _
                                          "and MAINTRAN.fdate<='" & Format(prdate, "MM/dd/yyyy") & "' " & _
                                          "and MAINTRAN.fbranchcode='" & "01" & "' " & _
                                          " group by MAINFILE.facno"

With cmd

dr = .ExecuteReader//error:Incorrect syntax near '.'.(sqlexception)

解决方案

First, do NOT use string concatenation to build SQL queries. You open yourself up to SQL Injection attacks by doing that.

Second, you're storing dates as strings. That's a huge problem because it's not culturally sensitive and sorting of dates by string value does not match the sorting of dates by actual date value.

Lastly, the casing you're using is hurting my eyes, making it difficult to read. SQL keywords are normally capitalized while column names and other identifiers are not.

Also, I don't believe you can create column aliases with a dotted notation. I'm taking about the AS clauses you have in that statement. Table aliases should be short so they don't drown out your column names thereby making your SQL easier to read.

select MAINFILE.facno,max(fdate) as MAINTRAN.fdate

should be

SELECT mf.facno, MAX(mf.fdate) AS fdate

Try your query in an SQL query tool before trying to do it in your code. You'll find it's quicker and easier to debug in the tool than it is in your code.

I would say your aliases are wrong. There is no reason to include the table name there:

select MAINFILE.facno, max(fdate) as MAINTRAN.fdate, ...

What you want is plain name, the same as the original column:

select MAINFILE.facno, max(fdate) as fdate, ...

Or if you want the whole string you need to use brackets like this:

select MAINFILE.facno, max(fdate) as [MAINTRAN.fdate], ...

try this..

cmd.CommandText = "select MAINFILE.facno,max(MAINTRAN.fdate) as MAINTRANfdate ,max(MAINTRAN.fbalance) as MAINTRANfbalance from MAINTRAN INNER JOIN MAINFILE ON MAINFILE.facno=MAINTRAN.facno" & _
                                          "where MAINTRAN.fbankcode='" & "010" & "' and MAINTRAN.facno=MAINFILE.facno" & _
                                          "and MAINTRAN.fdate<='" & Format(prdate, "MM/dd/yyyy") & "' " & _
                                          "and MAINTRAN.fbranchcode='" & "01" & "' " & _
                                          " group by MAINFILE.facno"

这篇关于我试图结合两个表给出错误..需要帮助的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！