CREATE TABLE `users`
(
`ID` int(11) NOT NULL auto_increment,
`username` varchar(20) character set utf8 default NULL,
`password` varchar(20) character set utf8 default NULL,
PRIMARY KEY (`ID`), KEY `username_password` (`username`,`password`)
)
得到表users
可我在asp.net中用ODBC来写,事先也去mysql下载mysql for odbc控件
Imports System.Data
Imports System.Data.Odbc
Partial Class _Default
Inherits System.Web.UI.Page
Dim mycon As New OdbcConnection("DRIVER={MySQL ODBC 5.1 Driver};SERVER=localhost;DATABASE=mysql;UID=root;PWD=jacklin;") Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim mycmd As New OdbcCommand("select * from users", mycon)
mycon.Open()
gv1.DataSource = mycmd.ExecuteReader
gv1.DataBind()
mycon.Close()
End Sub Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim upcmd As New OdbcCommand("insert into users (username,password) values (@a,@b)", mycon)
mycon.Open()
With upcmd
.Parameters.Add("@a", OdbcType.VarChar)
.Parameters.Add("@b", OdbcType.VarChar)
.Parameters("@a").Value = txtname.Text
.Parameters("@b").Value = txtpwd.Text
End With
upcmd.ExecuteNonQuery()
mycon.Close()
End Sub
End Class
数据库可以正常显示,可新插入的数据在MYSQL中全成了NULL了,在SQL2000中正常!为什么?是VS2008不支持MYSQL?还是别的问题?还有MYSQL中username与password编码换成UTF8了,不是latin1!有没有高手帮我看看
(
`ID` int(11) NOT NULL auto_increment,
`username` varchar(20) character set utf8 default NULL,
`password` varchar(20) character set utf8 default NULL,
PRIMARY KEY (`ID`), KEY `username_password` (`username`,`password`)
)
得到表users
可我在asp.net中用ODBC来写,事先也去mysql下载mysql for odbc控件
Imports System.Data
Imports System.Data.Odbc
Partial Class _Default
Inherits System.Web.UI.Page
Dim mycon As New OdbcConnection("DRIVER={MySQL ODBC 5.1 Driver};SERVER=localhost;DATABASE=mysql;UID=root;PWD=jacklin;") Protected Sub Page_Load(ByVal sender As Object, ByVal e As System.EventArgs) Handles Me.Load
Dim mycmd As New OdbcCommand("select * from users", mycon)
mycon.Open()
gv1.DataSource = mycmd.ExecuteReader
gv1.DataBind()
mycon.Close()
End Sub Protected Sub Button1_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles Button1.Click
Dim upcmd As New OdbcCommand("insert into users (username,password) values (@a,@b)", mycon)
mycon.Open()
With upcmd
.Parameters.Add("@a", OdbcType.VarChar)
.Parameters.Add("@b", OdbcType.VarChar)
.Parameters("@a").Value = txtname.Text
.Parameters("@b").Value = txtpwd.Text
End With
upcmd.ExecuteNonQuery()
mycon.Close()
End Sub
End Class
数据库可以正常显示,可新插入的数据在MYSQL中全成了NULL了,在SQL2000中正常!为什么?是VS2008不支持MYSQL?还是别的问题?还有MYSQL中username与password编码换成UTF8了,不是latin1!有没有高手帮我看看
我的环境XP+VS2008+MSSQL2000+MYSQL5
Dim upcmd As New OdbcCommand("insert into users ('"+txtname.Text+"','"+txtpwd.text+"')",mycon)就可以了
虽然没有数据类型验证,但也能添加记录了!现把它公布出来,有同类问题的朋友一起参考!
{
public sealed class MySqlParameter : DbParameter, IDbDataParameter, IDataParameter, ICloneable
{
public MySqlParameter();
public MySqlParameter(string parameterName, MySqlDbType dbType);
public MySqlParameter(string parameterName, object value);
public MySqlParameter(string parameterName, MySqlDbType dbType, int size);
public MySqlParameter(string parameterName, MySqlDbType dbType, int size, string sourceColumn);
public MySqlParameter(string parameterName, MySqlDbType dbType, int size, ParameterDirection direction, bool isNullable, byte precision, byte scale, string sourceColumn, DataRowVersion sourceVersion, object value); public override DbType DbType { get; set; }
[Category("Data")]
public override ParameterDirection Direction { get; set; }
[Browsable(false)]
public override bool IsNullable { get; set; }
[Category("Data")]
public MySqlDbType MySqlDbType { get; set; }
[Category("Misc")]
public override string ParameterName { get; set; }
[Category("Data")]
public byte Precision { get; set; }
[Category("Data")]
public byte Scale { get; set; }
[Category("Data")]
public override int Size { get; set; }
[Category("Data")]
public override string SourceColumn { get; set; }
public override bool SourceColumnNullMapping { get; set; }
[Category("Data")]
public override DataRowVersion SourceVersion { get; set; }
[Category("Data")]
[TypeConverter(typeof(StringConverter))]
public override object Value { get; set; } public override void ResetDbType();
public override string ToString();
}
}
MySqlParameter[] parms = new MySqlParameter[2];//有两个参数
parms[0] = new MySqlParameter("?lnNo", lnNo);
parms[1] = new MySqlParameter("?KeyID", KeyID);
可以加进去~如果要是按阁下那样写的话,如果在页面控件有类似于 " ' "的东西,数据库执行起来还是有困难的吧,愚见愚见,请多指教