Get first row ? [Resolved by DevGrp]

[Pirate]

How can I get it without using SQL Statement (I can get it via SQL ) . Have a look at the attatchment .

[Edneeis]

Huh? What do you mean without a SQL statement? You'll need some form of SQL statement to get the data from the data source.

[hellswraith]

Are you trying to store your database path in the database? If you don't know the path, how would you know were to go to get the path?

[Pirate]

Originally posted by Edneeis
Huh? What do you mean without a SQL statement? You'll need some form of SQL statement to get the data from the data source.

Hmm , right but I don't want something like this SQL st. " SELECT * FROM MYTABLE WHERE COLUMN='value' ".
How would you do it to get the very first row of a table ?

Originally posted by hellswraith
Are you trying to store your database path in the database? If you don't know the path, how would you know were to go to get the path?

I want to save my database file on other partition , in case any problem happened . I don't lose my data . I set this once and I can use it forever . I'm also logging some info about time running my app ..etc . Does this make sense or am I doing stupid ways ?

[Edneeis]

I don't know if it makes sense or not I didn't really get what you are trying to do, BUT...

You can either execute the SQL statement using the SingleRow option or you can use something like the following SQL statement.

SELECT TOP 1 FROM MyTable

[Pirate]

I've just tried this but gives error :
string sql ="SELECT TOP FROM Info_Tab";

This is what I'm doing right now but it shows data field by field . I want the first record only (the whole line from the left to the right ) .

string sql ="SELECT * FROM Info_Tab";
System.Data.DataSet ds=new DataSet();
OpenDB();			
System.Data.OleDb.OleDbDataAdapter adp=new OleDbDataAdapter(sql,MyConnection);
adp.Fill(ds,"Info_Tab");

foreach(DataRow dr in ds.Tables["Info_Tab"].Rows)
{
MessageBox.Show(dr[0].ToString());
}

}

Thanks Edneeis .

[DevGrp]

Use a datareader.

OleDbDataReader dr = myCmd.ExecuteReader();
dr.Read();
MessageBox.Show(dr["column1"].ToString() + dr["column2"].ToString() + dr["column3"].ToString() + dr["column4"].ToString());
dr.Close();

[Edneeis]

Originally posted by Pirate
I've just tried this but gives error :
string sql ="SELECT TOP FROM Info_Tab";

You forgot the 1 after TOP.

When you normally put SELECT * you are telling it to return ALL rows that match the criteria if any but you can specific how many of the records to return. TOP takes the first records returned according to how many you specify. So TOP 1 returns the first record, TOP 10 returns the first ten.

[Pirate]

DevGrp , you solved it . Ok now , I want to overwrite the same record (the first one ) . I don't care wheather I used SQL or not . How plz ?

Edneeis , I did put "1" after Top but gives error in the fill method of dataapapter . I think this means no data gathered . Never mind
Thanks guys .

[DevGrp]

You can override the data.

string sqlString = "Update myTable Set col1 = '" + colVar + "'" + ", col2='" + col2Var + "'" + " Where Last_Id = '" + id + "'";
OleDbCommand cmd = new OleDbComman();
cmd.CommandText = sqlString;
cmd.Connection = myConnection;

myConnection.Open();
cmd.ExecuteNonQuery();
myConnection.Close();

[Pirate]

What is colVar ?? and I think I only need only one record in that table so is it good way to delete the first record and add the new one instead of updating it ?

[Pirate]

This gives me the error "An unhandled exception of type 'System.Data.OleDb.OleDbException' occurred in system.data.dll" on cmd.ExecuteNonQuery(); . What's going wrong here . God , I'm dead tired .

public void SaveInfo1()
{
string sqlString = "Update Info_Tab Set C_LastTimeRunning = '" + this.Add_Txt_Time.Text + "'" + ", C_LastDateRunning='" + this.Add_Txt_Date.Text + "'" + ", C_DbPath='" + this.lbl_db_path.Text + "',"+" Where LastTime_ID ='" + "10" + "'";
			
OleDbCommand cmd = new  OleDbCommand(); 
cmd.CommandText = sqlString;
cmd.Connection = MyConnection;

//MyConnection.Open();
cmd.ExecuteNonQuery();
MessageBox.Show("Done...");
MyConnection.Close();
}

[DevGrp]

colVar is the variable you wan to store in the column of the database. I just used that since I dont know the names of your variables.

[Pirate]

Yea , I figured it out but I'm stuck with updating that specific record . I'm completely out of thoughts .

[DevGrp]

Its not that hard. After you read it the first time, store the column data into variables, then use those variables as keys for the update.

[Pirate]

This code is 70% working . One error only . It's not a code error but the exception returns this "Cannot update 'LastTime_ID' field is not updatable" . I know that because it's autonumbering field.

public void SaveInfo1(string TableStr)
{
string str1=this.Add_Txt_Time.Text ;
string str2=this.Add_Txt_Date.Text ;
string str3=this.lbl_db_path.Text ;

string sqlString = "UPDATE " + TableStr + " SET LastTime_ID='10'," + "C_LastTimeRunning='" + str1 + "'," 		
+ "C_LastDateRunning='" + str2 + "'," 
+ "C_DbPath='" + str3 + "'" + " WHERE LastTime_ID='10'";						
System.Data.OleDb.OleDbCommand cmd = new OleDbCommand (sqlString,MyConnection); 				
OpenDB();
try 
{
cmd.ExecuteNonQuery();
MessageBox.Show("Done...");
}
catch (Exception x)
{
MessageBox.Show(x.Message);
}
MyConnection.Close();
}

How can I solve this problem ?

[Edneeis]

Just don't set the value for that field:

public void SaveInfo1(string TableStr)
{
string str1=this.Add_Txt_Time.Text ;
string str2=this.Add_Txt_Date.Text ;
string str3=this.lbl_db_path.Text ;
 
string sqlString = "UPDATE " + TableStr + " SET C_LastTimeRunning='" + str1 + "',"      
+ "C_LastDateRunning='" + str2 + "'," 
+ "C_DbPath='" + str3 + "'" + " WHERE LastTime_ID='10'";                        
System.Data.OleDb.OleDbCommand cmd = new OleDbCommand (sqlString,MyConnection);                 
OpenDB();
try 
{
cmd.ExecuteNonQuery();
MessageBox.Show("Done...");
}
catch (Exception x)
{
MessageBox.Show(x.Message);
}
MyConnection.Close();
}

[Pirate]

What else than fu king error . I'm going crazy . Been trying for the whole day and night and can't seem to get it to work .
The error says " Data type mismatch in criteria expression" . I don't believe this . I'm saving string data type to text fields . What am I missing here ?

[Edneeis]

On second look you have quotes around the autofield number in the WHERE clause.
Change:
" WHERE LastTime_ID='10'";

To

" WHERE LastTime_ID=10";

[Pirate]

No , it's not that one . When I changed it to what you suggest it gives another error . Look the attachment .

[Pirate]

and when I change it back to

" WHERE LastTime_ID='10'";

gives ...see the new attachment . image number 1 only shows the sql string after filled with values . number 2 is the error .

[DevGrp]

Use parameters. Makes it easier to find errors in your select strings.

Try this

public void SaveInfo1(string TableStr)
{
    string str1=this.Add_Txt_Time.Text ;
    string str2=this.Add_Txt_Date.Text ;
    string str3=this.lbl_db_path.Text ;

    string sqlString = "UPDATE " + TableStr + " SET C_LastTimeRunning = ?, C_LastDateRunning = ?, C_DbPath = ? WHERE LastTime_ID =10";        
    System.Data.OleDb.OleDbCommand cmd = new OleDbCommand (sqlString,MyConnection);
    
    cmd.Parameters.Add("@lastTimeRunning", str1);
    cmd.Parameters.Add("@lastDateRunning", str2);
    cmd.Parameters.Add("@dbPath", str3);
    
    OpenDB();
    
    try 
    {
        cmd.ExecuteNonQuery();
        MessageBox.Show("Done...");
    }
    catch (Exception x)
    {
        MessageBox.Show(x.Message);
    }
    finally
    {    
        MyConnection.Close();
    }
} 

[ggprogram]

Originally posted by Pirate
The error says " Data type mismatch in criteria expression" . I don't believe this . I'm saving string data type to text fields . What am I missing here ?

I am Updating a date field in an Access table and getting the same error, but only in certain cases. If I key 06/10/2003 into a text box, convert it to a date and update the table, it works fine. If I key in 06/09/2003 or 06/9/2003 or an Day less than 10, I get the mismatch in criteria expression error. Its misleading because you would think it is referring to your WHERE clause, but in my case it is NOT. The WHERE clause has nothing to do with the date field.

[Pirate]

DevGrp , you did it again . . Can you tell me what's wrong with my code ?

Thank you so much .

[Pirate]

ggprogram , usually I get that error when it's really data type error . why don't you save the date as string type and change field data type to text . This would save you punch of errors and it's easy to convert it back to date data type .

[ggprogram]

Originally posted by Pirate
why don't you save the date as string type and change field data type to text .

Thanks - that works fine and probably makes things much more portable. But, it sure is strange that it would accept 06/10/2003 and not 06/09/2003! Same code using the text field works fine!

G

[Pirate]

Originally posted by ggprogram
But, it sure is strange that it would accept 06/10/2003 and not 06/09/2003!

It's .