Re: MySQL problem with varchar primary key - Database Discussion Boards

do u mean Just taking the databases from 2000 to yukon will work?

I mean that I would expect that to be the case, yes. I'm not saying I've tried it, but they're not going to change T-SQL to make old stuff stop working, surely ?

Christian Graus - Microsoft MVP - C++

How to insert text in MEMO field?
(I'm using Access DB and VS2003)

Hello,

I am currently porting software for my University from MSSql to MySql. I am having some problems and would really appreciate any help you could give.

I am unable to change the core design of the database tables because another student is working on software to process the resulting data, and he is hard coding everything. I have expressed how this is a bad design idea, but to no avail. So I am stuck with the current design.

In this design, the tables use a varchar(8000) as a primary key for relating the tables. This is automagically cast into a TEXT field by MySql, and it of course throws the error that a TEXT field as a primary key must have an index. I went ahead and gave indexes to all those text fields and everything on that part works great.

However, later in the database creation there are some constraint commands put on these tables that use those TEXT fields as the foreign key, and when it sees that this foreign key is a TEXT field it throws an error that a lenght must be specified.

I have no idea (after reading the manual again) how to specify a length in the constraint command. If you know how please let me know, I can post the code if that would help.

Thanks
Tim Reynolds

razialx wrote:
from MSSql to MySql.

Why do you need to do this ? If it's a cost issue, have you looked into MSDE ?

razialx wrote:
In this design, the tables use a varchar(8000) as a primary key for relating the tables.

Your co student is an idiot. I can't think of a worse design, no matter how hard I try. I'd urge you to distance yourself from this cowpat of a project ASAP. A primary key is an index, it needs to be easy to search. A varchar(8000) is about as far from that as you can get.

Christian Graus - Microsoft MVP - C++

Christian Graus wrote:
Your co student is an idiot. I can't think of a worse design, no matter how hard I try.

I bet he gets lots of warning messages too saying that the design makes it possible to create a valid row greater that 8000 bytes which cannot be processed. Since I've never gone nuts like this I've never seen the actual message so I'm not sure what it says, but I'm told it exists.

My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More

Christian Graus wrote:
I can't think of a worse design, no matter how hard I try. I'd urge you to distance yourself from this cowpat of a project ASAP

Ah! I so much agree! Smile | :)

Christian Graus wrote:
A primary key is an index, it needs to be easy to search

I suspect the original designer (designer?) was using some kind of guid as unique key because he/she did not wish to or know how to let the database generate the key.
Sorry I can't give you any practical advise to solve your current problem.

Guys, I know its terrible. I don't have a choice here.

I will explain a little about the project and my role in it...

This other student developed an online questionnaire program for use in teacher evaluations. I did not initiate this project or have any part in its design... I wish I had at this point. This last semester I was in a Windows Programming course, and having developed quite a bit of software in .NET I was allowed to work on a variety of projects in place of creating console based Hello, World! demos. The last project I was placed on was this. The project was already considered 'done' when I started working on it, they just wanted me to convert it from a retail database into an open database (the only open source support I have seen at this university...). When I inquired about this terrible design, I was told it was this way so that question numbers could be represented as strings and be kept unique. By this I mean the data could be described as such:
"Q1" "Did your teacher give you a better understanding of the subject material?" "bool"
"Q1.1" "Yes"
"Q1.2" "No"
Right, well thats all good until you consider just using an auto-incrementing integer as the primary key to reference things and attributing the question names as being unique.

I must also restate that I can NOT change this design, no matter how difficult it is to accept... /sigh.
I can still only plead with you folks and your experience on this, because I must get it working.

If we were to only consider the varchars as being 10 in lenght (because if someone were to show me a question number 8k long, I would quit programming!). Do you know how I could use this as a foreign key in a constraint statement?

If you know for a fact that it will not be possible, tell me so I can try another avenue.

And just to pull the sympathy card on my plight, I have to piece together all SQL statements from 50+ lines of :
strCmd += "...";
strCmd += "...";
...
strCmd += "...";
and so on and so on. Because putting the SQL statements into an editable format would be out of the question.

Thank you for those who have replied thus far, I hope you can help me come to a solution on my problem.

Tim

Hi to you all,
i'm still hardly writing my first web app and i'm actually trying to build some update/delete/insert query, you cas see the function i wrote.
in the second part of my query "Industry" i have to write directly the values in the command string because if a use "?" and parameters i get an exception of "non corrispondence" between parameters.
will anybody help me?

thanks in advance!
Fuel2Run

here is the query function:

Shared Function AddCompany(ByVal actC As CoreLayer.Company) As Boolean
Dim connStr As String = System.Configuration.ConfigurationSettings.AppSettings("connectionString")
Dim myConnection As New OleDbConnection(connStr)
myConnection.Open()

Dim myCommand As OleDbCommand = myConnection.CreateCommand()
Dim myTrans As OleDbTransaction

myTrans = myConnection.BeginTransaction(IsolationLevel.ReadCommitted)
myCommand.Connection = myConnection
myCommand.Transaction = myTrans

Try
' company
myCommand.CommandText = "INSERT INTO P5_companies (company_name, Adress, Town, Country, Notes ) VALUES (?,?,?,?,?)"
'cmd.Parameters.Add("actUser", OleDbType.VarWChar, 15)
'cmd.Parameters(0).Value = TheUser
myCommand.Parameters.Add("company_name", actC.Name)
myCommand.Parameters.Add("Adress", actC.Adress)
myCommand.Parameters.Add("Town", actC.Town)
myCommand.Parameters.Add("Country", actC.Country)
myCommand.Parameters.Add("Notes", actC.Notes)
myCommand.ExecuteNonQuery()

' ID...
myCommand.CommandText = "SELECT @@IDENTITY"
Dim actID As Int32 = CType(myCommand.ExecuteScalar, Int32)

If Not actC.Activities Is Nothing Then
If actC.Activities.Count > 0 Then
' activitites
Dim actA As CoreLayer.Industry
For Each actA In actC.Activities
myCommand.CommandText = "INSERT INTO P5_activities (companyID, industries) VALUES (" & actID & "," & actA.ID & ")"
'myCommand.Parameters.Add("companyID", OleDbType.Integer).Value = actID
'myCommand.Parameters.Add("industries", OleDbType.Integer).Value = actA.ID

myCommand.ExecuteNonQuery()
Next
End If
End If

myTrans.Commit()
Return True
Catch e As Exception
myTrans.Rollback()
Return False

Finally
myConnection.Close()
End Try
End Function

I hav insered a ListBox in the header of a datagrid and i can't find the event "SelectedIndexChanged" of this ListBox .

ENSI TUNISIA

Nizar Abdeljaoued wrote:
i can't find the event "SelectedIndexChanged" of this textbox.

As you can see from the documentation TextBox on both Windows Forms and ASP.NET applications doesn't have a SelectedIndexChanged event:
* System.Windows.Forms.TextBox (events)[^]
* System.Web.UI.WebControls.TextBox (events)[^]

Perhaps if you explained what you were trying to achieve then someone could help you better.

My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More

ah sorry itsn't a textbox but a listbox Sniff | :^)

ENSI TUNISIA

Hello,

In Windows Forms, I have a datagrid that is sortable by clicking on the column headers.

Say 2nd record is already selected and I sort the datagrid.

Now this previously selected row is unselected. At this point how do I get the index to this previously selected row to have it selected again.

The issue I'm referring is that after sorting the dataset still has the same indexing order on the datagrid, meaning, a particular row's index before or after sorting remains intact eventhough after sorting it's position on the datagrid view has changed.

Thank you.

I'm trying to do the same thing.

If you find out a way do it let me know,

Thank you

Two part to this.

Part1 (Save Row ID)
Each time a user selects a row in your DataGrid you need to save the row's unique idenifier.

Part2 (Re select Row based on ID)
After sorting, locate the new location of your selected row using the saved unique row identifier.

Example Part 1

// set selected row's id in view state
IEnumerator enu = ((DataView)DataGrid1.DataSource).GetEnumerator();
int index = 0;
while( index <= DataGrid1.SelectedIndex )
{
    enu.MoveNext();
    index++;
}

DataRowView rowView = enu.Current as DataRowView;
DataRow row = rowView.Row;
ViewState["item"] = (int)row["ID"];

Example Part 2

DataView sortView = dataSet1.MyTable.DefaultView;

// numberDiv is a static int
if( ( numberDiv % 2 ) == 0 )
{
	sortView.Sort = e.SortExpression + " ASC";
}
else
{
	sortView.Sort = e.SortExpression + " DESC";
}
numberDiv++;

// set new sort into ViewState
ViewState["sort"] = sortView.Sort;

// set sorted dataview
DataGrid1.DataSource = sortView;

// bind data
DataGrid1.DataBind();

// here reselected your row
// check that you have an item selected
if( ViewState["item"] != null )
{
    int id = (int)ViewState["item"];
    int index = -1;
    IEnumerator enu = sortView.GetEnumerator();
    while(enu.MoveNext())
    {	
        index++;	
        DataRowView rowView = enu.Current as DataRowView;	
        DataRow row = rowView.Row;	
        int rowID = (int)row["ID"];	
        if(rowID == id)
        {
            break; 
        }
    }
    DataGrid1.SelectedIndex = index;
}

Hope this helps.

My thanks to minhpc_bk

This works on an SQL2000 Db, but when I try to covert it to an Ole connections using an AccessDb I get the following error:

System.Data.OleDb.OleDbException: Operation must use an updateable query.

My code:

//Create the connection object
string DbPath= Server.MapPath("Player.mdb");
string ConnStrg="Provider=Microsoft.Jet.OLEDB.4.0;"+
@"Data Source="+DbPath+";";
OleDbConnection cnn= new OleDbConnection(ConnStrg);
DataSet dsPlayer= new DataSet();
OleDbDataAdapter daPlayer=new OleDbDataAdapter();

//Create a OleDbCommand to select data
OleDbCommand cmdSelect=cnn.CreateCommand();
cmdSelect.CommandType=CommandType.Text;
cmdSelect.CommandText="SELECT FirstName, LastName FROM PlayerID";

//Create an object to insert data
OleDbCommand cmdInsert=cnn.CreateCommand();
cmdInsert.CommandType=CommandType.Text;
cmdInsert.CommandText="INSERT INTO PlayerID (FirstName, LastName) " +
"VALUES(@FirstName, @LastName)";
cmdInsert.Parameters.Add("@FirstName", OleDbType.VarChar,50, "FirstName");
cmdInsert.Parameters.Add("@LastName", OleDbType.VarChar,50, "LastName");
cmdInsert.Parameters["@FirstName"].SourceVersion=DataRowVersion.Original;

//Setup the DataAdapter and fill the DataSet
daPlayer.SelectCommand=cmdSelect;
daPlayer.InsertCommand=cmdInsert;
daPlayer.Fill(dsPlayer, "Player");

//Create a new DataRow
DataRow dr= dsPlayer.Tables["Player"].NewRow();

//Set Values
dr[0]=txtFirstName.Text;
dr[1]=txtLastName.Text;
//And append the new row to the DataTable
dsPlayer.Tables["Player"].Rows.Add(dr);
//Save back to the database
daPlayer.Update(dsPlayer, "Player");
}

Any help or suggestion are greatly appreciated.

Jerry

Most people are willing to pay more to be amused than to be educated--Robert C. Savage, Life Lessons
Toasty0.com
Ladder League (beta)
My Grandkids

i had the same problem some time back when working with access database. Check whether you have given only read permission in the database. If you have given only read permission then you can only use select statement, if you use insert statement. you will get this error.

leo
Kathmandu, Nepal

Hi all, having a very frustrating problem:
I'm reading a field from a SQL Anywhere (ODBC) database into a .Net DataSet. The type in the database is "time", but this is read into the DataSet as a .Net TimeSpan type instead of a DateTime. No big deal, except I can't figure out how to convert this within the dataset (I'm exporting straight to XML, so I really can't read the data out into .Net types and do the conversions - it has to stay in the DataSet).
I've tried:
- creating a DataColumn with type String and forcing it into that
- above, with DateTime instead of String
- using the Convert function in DataColumn.Expression; this seems like the most promising, but it says that the type doesn't implement IConvertible (even though the documentation for Convert says that it CAN convert a TimeSpan to a string)

I don't care if it's a string or a DateTime, it just can't be a TimeSpan because it prints in a really ugly fasion (11:01 is PT11H01M) and I can't figure out how to format it nicely. Any help is appreciated, thanks!

Hi all,

How can i generate auto Number in SQL Server 2000,like AutoNumber Datatype in MsAccess....

Thanx in advance

Sajjad

When you create your table set the column to be an IDENTITY column like this:

CREATE TABLE [dbo].[Categories] (
	[CategoryID] [int] IDENTITY (1, 1) NOT NULL ,
	[CategoryName] [nvarchar] (15) COLLATE Latin1_General_CI_AS NOT NULL ,
	[Description] [ntext] COLLATE Latin1_General_CI_AS NULL ,
)

The numbers in the brackets after IDENTITY refer to the seed and increment and can be omitted. If you don't specify the numbers the default behaviour will be equivalent to IDENTITY(1,1)

Does this help?

My: Blog | Photos | Next SQL Presentation
WDevs.com - Open Source Code Hosting, Blogs, FTP, Mail and More

I created a user login page associated with an address book. In order to retrieve the address book the user must enter the username and password. How can i code the login button for authentication.
Below is the code that i used but there is an error message that points to ExecuteReader. Error: "ExecuteReader requires an open and available connection. The connection current state is closed."

I am using the OleDbDataAdapter, OleDbDataReader, OleDbConnection, and the OleDbCommand. What is it that I am doing wrong. I also created a MS Access table.

Dim mypath = Application.StartupPath & "\password.mdb"
Dim Password = ""
Dim conn As New OleDbConnection("Provider=Microsoft.Jet.OLEDB.4.0;Data Source=" & mypath & ";Jet OLEDB:Database Password=" & Password)
Dim cmd As OleDbCommand

Private Sub btnLogin_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnLogin.Click
Dim sql = "SELECT Username,Password FROM Password WHERE Username = '" & txtUsername.Text & "' AND Password = '" & txtPassword.Text & "'"

cmd = New OleDbCommand(sql, conn)
Dim dr As OleDbDataReader = cmd.ExecuteReader
If conn.State <> ConnectionState.Open Then
conn.Open()
End If

Try
conn.Open()

Catch ex As InvalidOperationException
MsgBox(ex.Message)

End Try
Try
If dr.Read = False Then
MessageBox.Show("Authentication Failed...")
Else
MessageBox.Show("Login Successful...")

End If
Catch ex As Exception
MsgBox(ex.Message)

End Try

If conn.State <> ConnectionState.Closed Then
conn.Close()

End If

Dim form As New Form2
form.Show()

bravo659

first of all : the actual reason of this problem that you must close the reader before you clos the connection ,
And also it seems you opened the connection twice in :
If conn.State <> ConnectionState.Open Then
conn.Open()
End If
And :
Try
conn.Open()

Catch ex As InvalidOperationException
MsgBox(ex.Message)

End Try

hope that helps.

I didn't even noticed that I opened it twice, thanks for pointing it out to me. So then, it is not the ExecuteReader, is that I used the conn.Open twice that disallow the disconnect from the reader, it just tries to open twice without closing the reader.
Thanks for your response. Let me try and will post again whether it works or not. Been working on this for 3 months. LOL.

bravo659

Nevermind, but try also to close the reader before closing the connection .

I finally fixed the problem. But I still need more to learn about programming in vb.net.
Thanks for your reply.

bravo659