SQL列以“0”作为值

I have two tables. One is members table with columns member id, member first name, member last name. I have another table guest passes with columns guest pass id, member id and issue date.

我有两张桌子。一个是具有列成员id,成员名字,成员姓氏的成员表。我有另一个表客人传递列客人传递ID,会员ID和发布日期。

I have a list view that will display guest passes details, like member name and issue date. I have two textboxes. Those are for entering member name and issue date.

我有一个列表视图,将显示访客通行证详细信息,如成员名称和发布日期。我有两个文本框。这些是用于输入会员名称和发布日期。

Member name textbox is auto complete textbox that is working fine.

成员名称文本框是自动完成的文本框,工作正常。

But the problem is when I am entering the name that is not in member table. At this time it will accept and display a blank field in list view in member name column and member id is stored as "0" in guest pass table.

但问题是当我输入不在成员表中的名称时。此时,它将在成员名称列的列表视图中接受并显示空白字段,并且成员标识在客户传递表中存储为“0”。

I don't want to display the member name empty blank and I don't want to store "0" in guest pass table.

我不想显示成员名称空白,我不想在访客通行证表中存储“0”。

This is the insert statement:

这是insert语句:

sql2 = @"INSERT INTO guestpasses(member_Id,guestPass_IssueDate)";
sql2 += " VALUES(";
sql2 += "'" + tbCGuestPassesMemberId.Text + "'";
sql2 += ",'" + tbIssueDate.Text + "'";

guestpassmemberId = memberid

Is there any validation that needs to be done? Can anyone give suggestions on this please? This is the auto complete textbox statement:

有没有需要做的验证?有人可以就此提出建议吗?这是自动完成的文本框声明:

sql = @"SELECT member_Id FROM members WHERE  concat(member_Firstname,'',member_Lastname) ='" + tbMemberName.Text+"'";
dt = GetData(sql, mf);
if (dt != null)
{
   if (dt.Rows.Count > 0)
   {
       tbCGuestPassesMemberId.Text  = Convert.ToInt32(dt.Rows[0]  ["member_Id"]).ToString();
   }
}

Can anyone help me on this? Is there any type of validation with SQL query?

谁可以帮我这个事? SQL查询是否有任何类型的验证?

2 个解决方案

#1

The problem you have is that tbCGuestPassesMemberId.Text is always going to have a value, whether it gets set or not. I would suggest putting the first section of code in an if statement as a hack around this, as I guess your member IDs start at 1 anyway?

你遇到的问题是tbCGuestPassesMemberId.Text总是会有一个值,无论它是否被设置。我建议将第一段代码放在if语句中作为hack围绕这个,因为我猜你的成员ID无论如何从1开始?

if (tbCGuestPassesMemberId.Text != 0)
{
  sql2 = @"INSERT INTO guestpasses(member_Id,guestPass_IssueDate)";
  sql2 += " VALUES(";
  sql2 += "'" + tbCGuestPassesMemberId.Text + "'";
  sql2 += ",'" + tbIssueDate.Text + "'";
}

#2

You do not have to supply a value for memberID if that is an *auto_increment* field. MySql inserts that number for you. If, on the other hand, you require the value to be displayed prior to actually writing that member/guest data to the data store, then there are several ways to achieve that.

如果是* auto_increment *字段,则不必为memberID提供值。 MySql为您插入该号码。另一方面,如果在实际将该成员/客户数据写入数据存储之前需要显示该值,则有几种方法可以实现。