Click here to Skip to main content
15,867,330 members
Please Sign up or sign in to vote.
0.00/5 (No votes)
See more:
I Have Converted my database from Access To SQL .As Sql Doesn't accept format() so it shows an error of invalid Syntax near #. How can I Solve this problem. Please help me.

This is my Code.

Private Sub LoadGrid()

Da = New SqlDataAdapter("SELECT StudentAccountS.StClass, StudentAccountS.StAdmNo, StudentAccountS.StName, StudentAccount.Amount, StudentAccountS.Remark, StudentAccountS.ReceiptNo, StudentAccount.TransactionID,StudentAccount.Perticular FROM StudentAccountS INNER JOIN StudentAccount ON StudentAccountS.SSID = StudentAccount.SSID where " &
"(StudentAccountS.Dated = #" & Format(SelDate, "MM/dd/yyyy") & "#)", Conn)

GridDT = New DataTable
Da.Fill(GridDT)
End Sub
Posted
Updated 4-Sep-15 5:41am
v2

Fix the SQL Injection[^] vulnerability in your code, and you'll fix this problem at the same time:
VB.NET
Da = New SqlDataAdapter("SELECT StudentAccountS.StClass, StudentAccountS.StAdmNo, StudentAccountS.StName, StudentAccount.Amount, StudentAccountS.Remark, StudentAccountS.ReceiptNo, StudentAccount.TransactionID,StudentAccount.Perticular FROM StudentAccountS INNER JOIN StudentAccount ON StudentAccountS.SSID = StudentAccount.SSID where (StudentAccountS.Dated = @Dated)", Conn)

Da.SelectCommand.Parameters.AddWithValue("@Dated", SelDate)

GridDT = New DataTable
Da.Fill(GridDT)


Everything you wanted to know about SQL injection (but were afraid to ask) | Troy Hunt[^]
How can I explain SQL injection without technical jargon? | Information Security Stack Exchange[^]
Query Parameterization Cheat Sheet | OWASP[^]
SQL injection attack mechanics | Pluralsight [^]
 
Share this answer
 
Comments
Member 10899600 6-Sep-15 3:21am    
This Query is not working in between case

DefaultStr = "" &
"SELECT StudentAccount.Dated, StudentAccountS.StAdmNo, StudentAccountS.StClass, " &
"StudentAccountS.StName, StudentAccount.Perticular, StudentAccount.Amount,StudentAccount.Remark,StudentAccount.PayMode,TransactionID " &
"FROM (StudentAccount LEFT OUTER JOIN StudentAccountS ON StudentAccount.SSID = StudentAccountS.SSID) " &
"WHERE (StudentAccount.Debit) and (StudentAccount.Dated Between " &
"#" & Format(DateFrom, "MM/dd/yyyy") & "# AND #" & Format(DateTo, "MM/dd/yyyy") & "#)"


Select Case SIndex
Case 0
SelStr = " AND (StudentAccount.PayMode = '" & OptionStr & "') Order By StudentAccount.Dated"
Case 1
SelStr = " AND (StudentAccount.Perticular = '" & OptionStr & "') Order By StudentAccount.Dated"
Case 2, 3
SelStr = " AND (StudentAccount.TransType = '" & filterStr & "') Order By StudentAccount.Dated"
Case Else
SelStr = Nothing
End Select

Da = New SqlDataAdapter(DefaultStr & SelStr, Conn)
Ds = New DataSet
Da.Fill(Ds)
Richard Deeming 14-Sep-15 8:11am    
Never use string concatenation to pass parameters! Use a properly parameterized query, as I showed you in my answer.

Not only will a properly parameterized query avoid a major security vulnerability in your code, it will also fix the problem that you're experiencing.
In SQL you would use the CONVERT[^] function.
VB
Da = New SqlDataAdapter("SELECT StudentAccountS.StClass, StudentAccountS.StAdmNo, StudentAccountS.StName, StudentAccount.Amount, StudentAccountS.Remark, StudentAccountS.ReceiptNo, StudentAccount.TransactionID,StudentAccount.Perticular FROM StudentAccountS INNER JOIN StudentAccount ON StudentAccountS.SSID = StudentAccount.SSID where " &
"(StudentAccountS.Dated = #" & CONVERT(VARCHAR(10), SelDate, 101) & "#)", Conn)
 
Share this answer
 
Comments
Richard Deeming 4-Sep-15 14:05pm    
A rare miss from the QA master!

Not only did you miss the SQL injection vulnerability, you posted VB.NET code which won't compile. CONVERT is a SQL function, not a .NET method.
Member 10899600 6-Sep-15 3:47am    
Showing an error generate a method Convert.

This content, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)



CodeProject, 20 Bay Street, 11th Floor Toronto, Ontario, Canada M5J 2N8 +1 (416) 849-8900