Data validation in c# and SQL server 2000
-
I have a windows application of which I want to validate a survey data that was captured using another software and stored in an MS SQL server 2000 database table. eg. I have three columns , Sex, Age, Marital status if a person is less than 20 years and is reported to be married, I have to fire the message that is the person is too young to be married. I was able to do that in VB 6.0: This is the code snippet that was used in vb 6.0, I had a richtextbox object where I placed all the error messages. ------------------- Dim adoTest As New ADODB.Connection Dim rstTest As New ADODB.Recordset Dim adoSearch As ADODB.Recordset Dim census, i As Integer Private Sub cmdTest_Click() cd.ShowSave rtb.SaveFile cd.FileName End Sub Private Sub Form_Load() adoTest.CursorLocation = adUseClient adoTest.Provider = "MSDASQL.1" adoTest.ConnectionString = "Password=sa;Persist Security Info=True;User ID=sa;Data Source=LSA2006;Initial Catalog=LSA2006" adoTest.Open If adoTest.State = adStateOpen Then MsgBox "Connexion with the server established", vbInformation, "Testing" Else MsgBox "Connexion Failed !", vbInformation, "Testing" End If rstTest.Open "select * from table2", adoTest, adOpenDynamic, adLockOptimistic rtb.Text = "" While Not rstTest.EOF If rstTest!married = 1 And rstTest!age <= 15 Then If rtb.Text = "" Then rtb.Text = rstTest!id Else rtb.Text = rtb.Text & vbNewLine & rstTest!id & " Too young to get married" End If End If If rstTest!age <= 20 And rstTest!education >= 3 Then If rtb.Text = "" Then rtb.Text = rstTest!id Else rtb.Text = rtb.Text & vbNewLine & rstTest!id & " Age not consistent with education" End If End If rstTest.MoveNext Wend End Sub ------------- Can anybody help me how to do this in C#. I have to do data validation quickly:confused: Thank you
phokojoe
-
I have a windows application of which I want to validate a survey data that was captured using another software and stored in an MS SQL server 2000 database table. eg. I have three columns , Sex, Age, Marital status if a person is less than 20 years and is reported to be married, I have to fire the message that is the person is too young to be married. I was able to do that in VB 6.0: This is the code snippet that was used in vb 6.0, I had a richtextbox object where I placed all the error messages. ------------------- Dim adoTest As New ADODB.Connection Dim rstTest As New ADODB.Recordset Dim adoSearch As ADODB.Recordset Dim census, i As Integer Private Sub cmdTest_Click() cd.ShowSave rtb.SaveFile cd.FileName End Sub Private Sub Form_Load() adoTest.CursorLocation = adUseClient adoTest.Provider = "MSDASQL.1" adoTest.ConnectionString = "Password=sa;Persist Security Info=True;User ID=sa;Data Source=LSA2006;Initial Catalog=LSA2006" adoTest.Open If adoTest.State = adStateOpen Then MsgBox "Connexion with the server established", vbInformation, "Testing" Else MsgBox "Connexion Failed !", vbInformation, "Testing" End If rstTest.Open "select * from table2", adoTest, adOpenDynamic, adLockOptimistic rtb.Text = "" While Not rstTest.EOF If rstTest!married = 1 And rstTest!age <= 15 Then If rtb.Text = "" Then rtb.Text = rstTest!id Else rtb.Text = rtb.Text & vbNewLine & rstTest!id & " Too young to get married" End If End If If rstTest!age <= 20 And rstTest!education >= 3 Then If rtb.Text = "" Then rtb.Text = rstTest!id Else rtb.Text = rtb.Text & vbNewLine & rstTest!id & " Age not consistent with education" End If End If rstTest.MoveNext Wend End Sub ------------- Can anybody help me how to do this in C#. I have to do data validation quickly:confused: Thank you
phokojoe
which bit are you haveing problems with? I assume the problem is going to be with getting data from the db and getting your hands on the data. Create a connection Create a command containing your select command assign the connection to the command call executereader() on the command datareader dr = myCommand.ExecuteReader() while (dr.Read()) { do stuff with dr fields } HTH Russ
-
which bit are you haveing problems with? I assume the problem is going to be with getting data from the db and getting your hands on the data. Create a connection Create a command containing your select command assign the connection to the command call executereader() on the command datareader dr = myCommand.ExecuteReader() while (dr.Read()) { do stuff with dr fields } HTH Russ
Yep! Thanks a lot. It worked but to a certain level. I maneged to retrive just 1 record/column before I intoroduce the if statement. I have about 127 records at present, but when I introcude the if statement to compare the values and then display them in the richtextbox, I get the message on the if statement "An unhandled exception of type 'System.FormatException' occurred in mscorlib.dll Additional information: Input string was not in a correct format." this is the code try { string str = "select * FROM table1"; con = new SqlConnection(_connect); cmd = new SqlCommand(); cmd.Connection = con; cmd.CommandType = CommandType.Text; cmd.CommandText = str; // Open the Connection con.Open(); dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); while (dr.Read()) { if (System.Convert.ToInt32(dr[10]) < 20) { rtb.Text = dr[7].ToString(); } } } catch(SqlException ex) { MessageBox.Show(ex.Message); } finally { // Close the Connection object con.Close(); } I am comparing column 10 with the age of the person that is 20 and then display the id of that particular record in textbox which is in column 7. Can you please point where I am doing the wrong thing Cheers!:~
phokojoe
-
Yep! Thanks a lot. It worked but to a certain level. I maneged to retrive just 1 record/column before I intoroduce the if statement. I have about 127 records at present, but when I introcude the if statement to compare the values and then display them in the richtextbox, I get the message on the if statement "An unhandled exception of type 'System.FormatException' occurred in mscorlib.dll Additional information: Input string was not in a correct format." this is the code try { string str = "select * FROM table1"; con = new SqlConnection(_connect); cmd = new SqlCommand(); cmd.Connection = con; cmd.CommandType = CommandType.Text; cmd.CommandText = str; // Open the Connection con.Open(); dr = cmd.ExecuteReader(CommandBehavior.CloseConnection); while (dr.Read()) { if (System.Convert.ToInt32(dr[10]) < 20) { rtb.Text = dr[7].ToString(); } } } catch(SqlException ex) { MessageBox.Show(ex.Message); } finally { // Close the Connection object con.Close(); } I am comparing column 10 with the age of the person that is 20 and then display the id of that particular record in textbox which is in column 7. Can you please point where I am doing the wrong thing Cheers!:~
phokojoe
sounds like it might be a rich text box problem to me. I only tried to use them once and found them too tricky for what i needed. add a multiline textbox to your form and try putting the data in that just to check that all the logic is working. Russ
-
sounds like it might be a rich text box problem to me. I only tried to use them once and found them too tricky for what i needed. add a multiline textbox to your form and try putting the data in that just to check that all the logic is working. Russ
Yep! Thank you for responding. You are great. Actually I have already found where the problem was. I passed the array refence not the actual column name. I changed the dr[0] to dr["columnname"] and it poped up every row that meets the if statement conditon. And in the System.Convert.ToInt32 i changed that to ToInt16 and the condition wokrs fine. However I will aging write if I have a problem because I have to test for many columns against others. Once agin Thank you.
phokojoe