problem in inserting date in sql server 7 through insert query

hello myself avinash
i am developing on application having vb 6 as front end and sql server 7
as back end.
when i use insert query to insert data in table then the date value of
that query is going as 01/01/1900
my query is as follows

StrSql = "Insert Into
SalesVoucher(TransactionID,VoucherNo,VoucherDate,D ebitTo,CreditTo,TotalAmt,Discount,ModAmt,ModWt,Oth er,Othertype,TaxPerc,TaxAmt,NetAmt,Advance,Narrati on,Haste)"
StrSql = StrSql & " Values(" & txtTransactionID.text & "," &
txtChallanno.text & ",'" & Format(txtChallanDate.Value, "dd/mm/yyyy") &
"'," & AccCode & ",'" & IIf((Category = "Gold"), 36, 38) & "',"
StrSql = StrSql & vsAmountDesc.ValueMatrix(RowAmountArr(0),
2)
& "," & vsAmountDesc.ValueMatrix(RowAmountArr(2), 2) & "," &
val(txtModTotal.caption) & "," & val(TxtModWt.caption) & ","
StrSql = StrSql & vsAmountDesc.ValueMatrix(RowAmountArr(1),
2)
& ",'" & vsAmountDesc.TextMatrix(RowAmountArr(1), 1) & "','" &
vsAmountDesc.TextMatrix(RowAmountArr(4), 1) & "'," &
vsAmountDesc.ValueMatrix(RowAmountArr(4), 2) & ","
StrSql = StrSql & vsAmountDesc.ValueMatrix(RowAmountArr(3),
2)
+ val(txtModTotal.caption) & "," & val(txtAdvance.text) & ",'-'," &
IIf(Trim(txtHaste.text) <> "", RetriveAccountCode(Trim(txtHaste.text)),
0)
& ")"

and its output is

Insert Into
SalesVoucher(TransactionID,VoucherNo,VoucherDate,D ebitTo,CreditTo,TotalAmt,Discount,ModAmt,ModWt,Oth er,Othertype,TaxPerc,TaxAmt,NetAmt,Advance,Narrati on,Haste)
Values(18,1831,'07/04/2004',150,'36',11000,0,0,0,-10,'','1.00',109.9,11100,0,'-',0)

in above query though i used cdate to voucherdate value still it save in
database as 01/01/1900 though here it shows right date
plz help me its a very big issue for me & i really just fed of this
problemYou might try running a Profiler trace to capture the actual statement
executed by SQL Server and check for any triggers that might change the
value. Note that SQL Server will interpret an empty string as 1900-01-01.

--
Hope this helps.

Dan Guzman
SQL Server MVP

"avinash" <pawar_avinash@.rediffmail.com> wrote in message
news:9c11bb7aacaeeddcf230738468fd3b72@.localhost.ta lkaboutdatabases.com...
> hello myself avinash
> i am developing on application having vb 6 as front end and sql server 7
> as back end.
> when i use insert query to insert data in table then the date value of
> that query is going as 01/01/1900
> my query is as follows
> StrSql = "Insert Into
> SalesVoucher(TransactionID,VoucherNo,VoucherDate,D ebitTo,CreditTo,TotalAmt,Discount,ModAmt,ModWt,Oth er,Othertype,TaxPerc,TaxAmt,NetAmt,Advance,Narrati on,Haste)"
> StrSql = StrSql & " Values(" & txtTransactionID.text & "," &
> txtChallanno.text & ",'" & Format(txtChallanDate.Value, "dd/mm/yyyy") &
> "'," & AccCode & ",'" & IIf((Category = "Gold"), 36, 38) & "',"
> StrSql = StrSql & vsAmountDesc.ValueMatrix(RowAmountArr(0),
> 2)
> & "," & vsAmountDesc.ValueMatrix(RowAmountArr(2), 2) & "," &
> val(txtModTotal.caption) & "," & val(TxtModWt.caption) & ","
> StrSql = StrSql & vsAmountDesc.ValueMatrix(RowAmountArr(1),
> 2)
> & ",'" & vsAmountDesc.TextMatrix(RowAmountArr(1), 1) & "','" &
> vsAmountDesc.TextMatrix(RowAmountArr(4), 1) & "'," &
> vsAmountDesc.ValueMatrix(RowAmountArr(4), 2) & ","
> StrSql = StrSql & vsAmountDesc.ValueMatrix(RowAmountArr(3),
> 2)
> + val(txtModTotal.caption) & "," & val(txtAdvance.text) & ",'-'," &
> IIf(Trim(txtHaste.text) <> "", RetriveAccountCode(Trim(txtHaste.text)),
> 0)
> & ")"
> and its output is
> Insert Into
> SalesVoucher(TransactionID,VoucherNo,VoucherDate,D ebitTo,CreditTo,TotalAmt,Discount,ModAmt,ModWt,Oth er,Othertype,TaxPerc,TaxAmt,NetAmt,Advance,Narrati on,Haste)
> Values(18,1831,'07/04/2004',150,'36',11000,0,0,0,-10,'','1.00',109.9,11100,0,'-',0)
> in above query though i used cdate to voucherdate value still it save in
> database as 01/01/1900 though here it shows right date
> plz help me its a very big issue for me & i really just fed of this
> problem|||hi avinash
i have come across such problems frequently. i would advise u
to set the date format within dtpicker control u are using. Check
properties for the control and set the format to custom. then set the
mask.
thats it.

Regards
Debashishsql