Asp.Net News
Free Tech Magazines
Asp.Net Web Hosting
Suggest Us
Link to Us
Feeds Subscription
Questions & Answers
Tips & Tricks
 insert with multiple condiction
Posted by syazwani on  12/5/2010 11:27:32 PM Category: VB.NET
Add to my favorites
Email to friend

I need help....

i have one problem in my programming, im using

i want to insert data into database, using multiple condiction (if else statement)...
before it save, it check wheather the matrix no and date already save in database or not.

the condiction is in one date only one report is allow, this is only one student
this is my code im use:

Protected Sub btn_save_Click(ByVal sender As Object, ByVal e As System.EventArgs) Handles btn_save.Click

Dim srcAdd As New SqlDataSource()
srcAdd.ConnectionString = ConfigurationManager.ConnectionStrings("ConnectionString").ToString()

'Dim objAdpt As SqlDataAdapter
Dim objDt As DataTable
Dim objDv As DataView

srcAdd.SelectCommand = "Select * From Report Where NoMatrix = ' " + Txt_NoMatrix.Text + " ' And Date = ' " + Txt_Date.Text + " ' "

objDt = New DataTable()
objDv = srcAdd.Select(DataSourceSelectArguments.Empty)
objDt = objDv.ToTable()

If (objDt.Rows.Count > 0) Then
MsgBox("SAVE FAILED! You alredy use this date !", MsgBoxStyle.OkOnly, "RESULT")

srcAdd.InsertCommandType = SqlDataSourceCommandType.Text
srcAdd.InsertCommand = "INSERT INTO Report ( Date, Report, NoMatrix ) Values (@Date, @Report, @NoMatrix)"
srcAdd.InsertParameters.Add("Date", Txt_Date.Text)
srcAdd.InsertParameters.Add("Report", Txt_Report.Text)
srcAdd.InsertParameters.Add("NoMatrik", Txt_NoMatrix.Text)

Dim rowsAffected As Integer = 0

rowsAffected = srcAdd.Insert()
Catch ex As Exception

srcAdd = Nothing
End Try
End If

End Sub

* for date im use ajax tools -->calendar extender (format :dd/MM/yyyy)

that code is work but it don't work like what i want...this is output using the code:

no matrix | date | report
a001 |30/11/2010 | try.....
a001 |1/12/2010 | just try...
a001 |1/12/2010 |my report...

but this output is what i want :

no matrix | date | report
a001 |30/11/2010 | try.....
a001 |1/12/2010 | just try...
a002 |1/12/2010 |my report...

one student can't use the same date..
but another student can use the same date...

i'm just have 2 day to solve this problm...
thank you for ur help...

Post Your Reply Post New Question

 Viewer's Reply
Insert with Multiple Conditions using DataTable & SQLDataAdaptor
Answered By Moderator1 on 12/6/2010 6:50:46 AM

There is something wrong with your SelectCommand. Follow the below syntax

sql = "Select * From Report Where NoMatrix=@NoMatrix And Date=@Date";
SqlDataAdapter da = new SqlDataAdapter(sql, cnstr);
da.SelectCommand.Parameters.Add("@NoMatrix", SqlDbType.VarChar, 12).Value = txt_NoMatrix.Text;
da.SelectCommand.Parameters.Add("@Date", SqlDbType.DateTime).Value = txt_Date.Text;
DataTable dt = new DataTable();

if (dt.Rows.Count > 0)
MsgBox("Record already Exists....");
Post Your Reply If you still have problem, post it again under this reply for better response.

Sponsored by
Easing the Migration to Microsoft SQL Server 2005
Building a Web Application Security Program
The PHP Anthology: 101 Essential Tips, Tricks & Hacks, 2nd Edition - Free 207 Page Preview!